Msyql statistical performance

Index design:

1. The index design should be based on the actual statistical requirements, mainly reflected in the requirements of order by and group

2. Too many requirements should not lead to too many indexes: Key base1 (...), key base2 (...),... too many indexes will lead to insert, and update is quite time-consuming.

3. The index field should be the public prefix of all types of order by and group by fields, rather than too many indexes. For example, if group by a, B, c, d; group by A, B, C, E, the index should be key base (A, B, C ). instead of creating two indexes, key base1 (A, B, C, D), key base2 (A, B, C, E)

4. DESC/explain may find: Using where; using temporary; using filesort. Optimization may be considered. Of course, the data is too large, and the group by field cannot be set as an index or avoid using temporary. However, designing indexes properly will also improve the efficiency of using temporary.

5. The design of indexes should be simplified and reasonable, without confusion

6. The core of MySQL is indexing.

 

Data insertion:

1. mysqlimport actually calls load data... it is more efficient than insert. Therefore, you should use mysqlimport instead of insert

2. If possible, it is convenient to use the awk script to block files.

 

Others:

1. Summarize, think, and learn every day

2. Do not stick to your work. expand it.