MySQL index and mysql INDEX OPTIMIZATION

MySQL index and mysql INDEX OPTIMIZATION

I. Why should I use indexes?

Optimize queries to reduce the number of table rows scanned. For example, the function of indexing is the same as that of querying the Xinhua Dictionary.

Ii. Index types

1. indexes are implemented in the storage engine, rather than on the server layer. Therefore, the indexes of each storage engine are not necessarily the same, and not all storage engines Support all index types.

2. If you are using a composite index (that is, multiple columns), you must meet the "leftmost prefix" rule, which is related to the storage method of the index B-tree.

Reference: leftmost prefix Principle

Iii. Index Length

The desc or explain command can view the index's key_len:

+ ---- + ------------- + ------- + ------ + --------------- + ---------- + --------- + ---------------------------- + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ---------- + --------- + ---------------------------- + ------- +
| 1 | SIMPLE | cry | ALL | NULL | 64733 | NULL |
| 1 | SIMPLE | zd | ref | cry_brzd | 153 | hospitalinfection. cry. ZYID | 1 | NULL |
+ ---- + ------------- + ------- + ------ + --------------- + ---------- + --------- + ---------------------------- + ------- +

Key_len is: 153

Gbk is a single character encoded in two bytes, while utf8 is a character encoded in three bytes. The author's database is UTF-8 encoded, and the field type for index creation is varchar (50): So, the length is 50*3 + 3 = 153. 4. Index deficiency 1. Reduces the table UPDATE speed, such as INSERT, UPDATE, and DELETE. When updating a table, MySQL not only needs to save data, but also stores the index file. 2. index files that occupy disk space when an index is created. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly. 3.... 5. Others 1. mysql does not use indexes in some queries, even if an index is created:

SELECT * FROM mytable WHERE username like 'admin % '-- no index is used

SELECT * FROM mytable WHEREt Name like '% admin' --- the index is used.

2. mysql functions are used for the query column, or not in and <> operators are used.

3. Full-text search indexes can be built on text or varchar fields, and match... against is used. If you want to match Chinese characters, you need to use a word divider, or use the pingyin4j class library to convert Chinese characters to Pinyin and then match etc.

4 ,......