Question 1: What is the difference between a char and a varchar?

Varchar is variable length and char is fixed length. If your content is fixed size, you’ll get better performance.

Question 2: What is the difference between TRUNCATE and DELETE?

The DELETE command deletes a row, or rows, from a table, and the TRUNCATE command permanently deletes each row from the table.

Question 3: What is a trigger? What are the triggers in MySQL?

A trigger is a piece of code that is automatically executed when an event is triggered. Before Insert2.After Insert3.Before Update4.After Update5.Before Delete6

Question 4. What is the difference between FLOAT and DOUBLE?

  • FLOAT data can store up to 8 decimal digits and account for 4 bytes in memory.
  • Data of type DOUBLE can store up to 18 decimal digits and occupy 8 bytes of memory.

Q5: How to obtain the current date in MySQL?

Question 6: How to query the NTH highest salary?

Int (0), char(16), vARCHar (16), datetime, text

Int (0) indicates that the data type is int, and the length is 0. Char (16) indicates that the data type is fixed length, and the length is 16. Varchar (16) indicates that the data type is variable length, and the length is 16. Store up to 65535 bytes of data)

Q8: Please explain the difference between InnoDB and MyISAM

  1. InnoDB supports transactions, MyISAM does not;
  2. InnoDB data is stored in shared table Spaces, MyISAM data is stored in files;
  3. InnoDB supports row-level locking, MyISAM only supports table locking;
  4. InnoDB supports crash recovery, MyISAM does not;
  5. InnoDB supports foreign keys, MyISAM does not;
  6. InnoDB does not support full-text indexing, MyISAM does.

Question 9: Features of the InnoDB engine

1, Insert buffer 2, double write 3, adaptive hash index (AHI)4, read ahead

Question 10: Please list more than three table engines

InnoDB, MyISAM, Memory

Question 11: Explain the difference between varchar and text

  1. Varchar can specify the number of characters, but text cannot be specified. Internal storage vARCHar is the actual number of characters stored +1 byte (n <=255) or 2 bytes (n>255), text is the actual number of characters +2 bytes.
  2. The text type cannot have default values.
  3. Varchar can create an index directly. Text specifies the number of characters before creating an index. Varchar queries are faster than text queries, and text indexes are almost useless when both indexes are created.
  4. The text query requires the creation of temporary tables.

Question 12: the meaning of 50 in VARCHar (50)

Varchar (50) and (200) take up the same amount of space to store Hello, but the latter will consume more memory when sorting because order by COL uses fixed_length (the same with the memory engine).

Question 13: What is 20 in int(20)

ZEROFILL: indicates the length of the display character. It does not affect the internal storage, but only the number of zeros before it when ZEROFILL is defined

Question 14: What is the impact of index, primary key, unique index, and joint index on MySQL database performance?

  • A table can have only one primary key index, but can have multiple unique indexes.
  • A primary key index must be a unique index. A unique index is not a primary key index.
  • Primary keys can impose referential integrity constraints on foreign keys to prevent data inconsistencies.
  • Joint index: A group of columns to create an index that can cover multiple columns. (also called composite index, composite index)
  • Foreign key indexes: Only InnoDB tables can use foreign key indexes to ensure data consistency, integrity, and cascading operations.
  • Full text index: The full text index of MySQL can only be used for MyISAM, and can only be used for Full text retrieval of English.

15. What should I pay attention to when creating a MySQL federated index?

Follow the prefix rule

Question 16: When the column value is NULL, does the query use an index?

In MySQL, NULL columns are indexed. Of course, if you plan to index a column, try to avoid setting it to nullable. MySQL has a hard time optimizing queries that reference nullable columns, which complicates indexes, index statistics, and values.

SELECT * FROM users WHERE YEAR(adddate) < 2019; *

No, because MySQL doesn’t use indexes whenever a column involves an operation.

Question 18: MyISAM index implementation?

MyISAM storage engine uses B+Tree as the index structure, and the data field of the leaf node stores the address of the data record. MyISAM indexes are also called non-clustered indexes to distinguish them from InnoDB’s clustered indexes.

Question 19: What is the difference between MyISAM index and InnoDB index?

  1. InnoDB index is clustered index, MyISAM index is non-clustered index.
  2. InnoDB’s primary key index is very efficient because its leaf nodes store rows.
  3. The leaf node of the MyISAM index stores the row data address, which needs to be addressed again to get the data back.
  4. InnoDB leaf nodes that are not primary key indexes store primary key and other indexed column data, so overwriting indexes can be very efficient when querying.

Alter table A(id,sex,par, C1,c2); alter table A(id,sex,par, C1,c2); alter table B(ID,age,c1,c2); alter table A(age>50)

Question 21: What do you know about associated query statements in MySQL?

Six associated queries1. CROSS JOIN 2. INNER JOIN 3. LEFT JOIN/RIGHT JOIN 4. UNION query (UNION and UNION ALL) 5. FULL JOIN 6. CROSS JOIN

Inner joins fall into three categories

  1. ON A.id=B.id
  2. Unequal connection: ON A.id > B.id
  3. SELECT * FROM A T1 INNER JOIN A T2 ON t1.id = t2.pid

LEFT JOIN/RIGHT JOIN

  1. LEFT OUTER JOIN: select * from LEFT OUTER JOIN; select * from LEFT OUTER JOIN; select * from LEFT OUTER JOIN; select * from LEFT OUTER JOIN; select * from LEFT OUTER JOIN; select * from LEFT OUTER JOIN
  2. Select * from RIGHT OUTER JOIN; select * from RIGHT OUTER JOIN; select * from RIGHT OUTER JOIN; select * from RIGHT OUTER JOIN; select * from RIGHT OUTER JOIN

UNION query (UNION and UNION ALL)

  1. The result before the UNION is the benchmark. It should be noted that the number of columns in the joint query should be equal, and the same rows of records will be merged
  2. If UNION ALL is used, duplicate rows will not be merged. 3

FULL JOIN

MySQL does not support full JOIN. 2. LEFT JOIN and UNION can be used together with RIGHT JOIN

Nested query

It is not efficient to use the result of one SQL statement as the condition of another SQL statement

The problem solving method

According to the test question to figure out the relationship between the results of the table and the multiple tables, according to the results you want to think about the use of that kind of association, usually write out the columns to query, and then analyze these columns belong to which tables, before considering the use of associated query

Question 22. What is the difference between a UNION and a UNION ALL?

  1. If you use UNION ALL, duplicate rows are not merged
  2. Efficiency of UNION is higher than that of UNION ALL

Question 23: how can you query 200 entries from 50000 to 50200 in the fastest way, with 600 million entries in table A and 300 million entries in table B associated with the foreign key TID?

1. If table A’s TID is self-increasing and continuous, the IDS of table B are indexes

2. If the TID of table A is not continuous, then overwrite index is required. TID is either a primary key or secondary index. Table B ID also needs to have an index.

Problem 24: Copy table (source table name: A target table name: b)

Question 25: Student(S#,Sname,Sage,Ssex) Student table SC(S#,C#,score) Teacher table query Student(S#,Sname,Sage,Ssex) Student table

Question 26: Pick out 10 pieces of data randomly

Question 27: Please briefly describe the methods to optimize the execution efficiency of SQL statements in the project. In what aspects, how to analyze the performance of SQL statements?

1.

Find a method to analyze SQL statements that is slow

Stretch test points:

  1. Optimize data access during queries
  2. Optimize long and difficult query statements
  3. Optimize specific types of query statements

How can I find the cause of slow query

Record and analyze slow query logs. Do not directly open the slow query logs for analysis because it wastes time and energy. You can use pt-query-digest to analyze the logs

Use the show profile

The use of show status

Show Status returns some counters, and show Global Status looks at all counts at all server levels and sometimes from those counts you can guess which operations are more expensive or time-consuming

show processlist

Observe if a large number of threads have abnormal states or characteristics

Most frequently asked MySQL interview question # 5 — every developer should know

Use explain

Analyze a single SQL statement

Optimize data access during queries

  1. Too much data is accessed and query performance deteriorates
  2. Determine if the application is retrieving more data than it needs, perhaps too many rows or columns
  3. Verify that the MySQL server is not parsing a large number of unnecessary rows
  4. Avoid the following SQL statement errors
  5. Query data that is not needed. Solution: Use limit to resolve
  6. Multi-table association returns all columns. Solution: Specify column names
  7. Always return all columns. Workaround: Avoid using SELECT *
  8. Query the same data repeatedly. Workaround: You can cache the data and read the cache directly next time
  9. Whether additional records are being scanned. Workaround: Use Explain for analysis. If you find that the query needs to scan a large amount of data but only returns a small number of rows, you can optimize it by using the following techniques:
  • Using an index override scan, all columns are placed in the index so that the storage engine does not need to go back to the table for the corresponding row to return the result.
  • Change the database and table structure and modify the data table paradigm
  • Rewrite the SQL statement so that the optimizer can execute the query in a better way.

Optimize long and difficult query statements

  1. One complex query or multiple simple queries
  2. Internally MySQL can scan millions of rows of data in memory per second. In comparison, responding to data to the client is much slower. It is good to use as small a query as possible, but sometimes it is necessary to split a large query into several smaller ones.
  3. Segmentation queries
  4. Divide a large query into multiple small identical queries
  5. Deleting 10 million data at a time costs more than deleting 10, 000 and pausing for a while.
  6. Decompose associated queries to make caching more efficient.
  7. Performing a single query can reduce lock contention.
  8. The association at the application layer makes it easier to split the database.
  9. Query efficiency will be greatly improved.
  10. Fewer queries for redundant records.

Optimize specific types of query statements

  1. Count (*) ignores all columns and counts all columns. Do not use count(column name)
  2. In MyISAM, count(*) without any WHERE conditions is very fast.
  3. When there are WHERE conditions, MyISAM’s count count is not necessarily faster than other engines.
  4. You can use explain to query for approximations and replace count(*) with approximations
  5. Add summary tables
  6. Use the cache

Optimizing associated query

  1. Determines whether there is an index in the ON or USING clause.
  2. Ensure that GROUP BY and ORDER BY are only columns in one table so that MySQL can use indexes.

Optimized subquery

  1. Use associative queries instead
  2. Optimize GROUP BY and DISTINCT
  3. These two types of query data can be optimized using indexes, which are the most effective optimization methods
  4. In associative query, the use of identity column group is more efficient
  5. ORDER BY NULL (GROUP BY);
  6. WITH ROLLUP super aggregation that can be moved to application processing

Optimize LIMIT paging

  1. If the LIMIT offset is large, the query efficiency is low
  2. You can record the maximum ID of the last query. The next query is performed based on this ID

Optimizing UNION queries

The efficiency of UNION ALL is higher than that of UNION

Optimize the WHERE clause

Problem solving method for this kind of examination questions, to illustrate how to locate inefficient SQL statements first, and then according to the SQL statements may be the cause of the inefficient do screening, from the first index, if the index is no problem, considering the above several aspects, the problem of data access, long difficult problem of query sentences or some specific types of optimization problems, one by one to answer.

Question 28: What are some methods of SQL statement optimization?

1. To optimize the query, avoid full table scan as far as possible, and first consider creating indexes on the columns involved in WHERE and ORDER by.

2. Avoid null values in the WHERE clause. Otherwise, the engine will abandon the use of index and perform full table scan.

3. Avoid using it in where clauses! = or <> otherwise the engine will abandon the index and perform a full table scan.

4. Avoid the use of OR in the WHERE clause to join conditions, otherwise the engine will abandon the use of index and perform full table scan, such as:

5. Use in and not in with caution, otherwise it will cause a full table scan.

Select id from t where name like ‘% li %’ select id from t where name like ‘% li %’

7. If you use parameters in the WHERE clause, it will also cause a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the choice of an access plan until run time; It must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is unknown and therefore cannot be used as an input for index selection. The following statement will perform a full table scan:

8. Expression operations on fields in the WHERE clause should be avoided as much as possible. This will cause the engine to abandon the use of indexes and perform a full table scan. Such as:

9. Avoid functional manipulation of fields in the WHERE clause, which will cause the engine to abandon indexes and perform a full table scan. Such as:

10. Do not perform functions, arithmetic operations, or other expression operations to the left of the “=” in the WHERE clause, or the system may not use the index properly.

Conclusion:

I hope these common Mysql questions can help you in your job search, and also help you sort out some answers and more interview questions for you. I hope you can also use them in your interview.