MYSQL user experience (9) ---- joint Index Note: Index (Name, Age) indicates the establishment of a joint Index on the Name and Age columns because the Index has a crucial impact on the database query performance, below are some of my summary and experiences: one query can only use one index at a time: selectnamefromuserwherenameplantegganda
MYSQL user experience (9) ---- joint Index Note: Index (Name, Age) indicates the establishment of a joint Index on the Name and Age columns because the Index has a crucial impact on the database query performance, below are some of my summary and experiences: one query can only use one index at a time: select name from user where name = 'plantegg 'and
MYSQL experience (9) ---- joint Indexing
Note: Index (Name, Age) indicates that a joint Index is created on the Name and Age columns.
As the index has a crucial impact on the database query performance, the following are my summary and experiences:
One query can only use one Index at a time: select name from user where name = 'plantegg 'and age> 35. If Index (name); Index (age) is used, the MySQL query optimizer automatically selects an index for use;
Which index does MySQL select? mysql> show index from photo;
+ ------- + ------------ + ------------------------ + -------------- + --------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+ ------- + ------------ + ------------------------ + -------------- + --------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
| Photo | 0 | PRIMARY | 1 | photo_id | A | 237871 | NULL | BTREE |
| Photo | 1 | index_random | 1 | random | A | 237871 | NULL | YES | BTREE |
| Photo | 1 | FK_photo_profile_id | 1 | profile_id | A | 237871 | NULL | BTREE |
| Photo | 1 | FK_photo_temp_photo_id | 1 | temp_photo_id | A | 237871 | NULL | YES | BTREE |
| Photo | 1 | FK_photo_album_id | 1 | album_id | A | 237871 | NULL | YES | BTREE |
+ ------- + ------------ + ------------------------ + -------------- + --------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
The larger the Cardinality, the finer the candidate score of the index (the BTree index is used by default );
You can also try Force Index to Force a certain Index to see if MySQL is faster to query (if so, you need to Analyze yourTable, mySQL recalculates your Cardinality to help you select the INDEX correctly)
Analyze the Explain results carefully: Pay attention to the results of Extra, Key, Rows, and Select_type!
Be careful when querying Group by, order by, and so on. Basically, such a query will show: Using where; Using temporary; Using filesort
Be careful when associating indexes. indexes (Name, Age) cannot be used if where name = 'pp 'or where age = 25; where name = 'pp 'and age> 25 can use indexes; where name = 'pp' order by age can use indexes; where name> 'pp 'order by age cannot use indexes, but where name> 'pp 'order by name, age can use the index, please pay attention to the difference; order by name asc age desc will not be able to use the index!
The index is helpful only when it is added to the memory. If the index is too large, it cannot be put into the memory! Random Aceess Disk is also required to access the index, which is slower than no index!
When using the select statement, you do not need to use the select * Statement, that is, the columns that need to take only those columns (those that do not have performance advantages for Hibernate). For example, in the Index (Name) select * from user where name like 'pp % 'and select name from user where name like 'pp %' have different performance, if there are 10000 results that match the record (the User table has a total of 1 billion records), the previous query may take 2 minutes (assuming your system has 100 IOPS per second) the next query may only take 0.01 seconds! Because the first query needs to retrieve the 10000 records distributed everywhere from the hard disk, it is enough for the second query to get the Name directly from the index in the memory! The Using Index is displayed in Extra during the next explain.
Always be alert to Random Access to disks. The performance difference between sequential read/write and random access is N orders of magnitude (your OS and Dish Cache are amazing when sequential read/write is complete) if you are interested in this issue, we recommend that you use C to write a test program. During random read/write, fseek will continue, similarly, you should read and write data to the memory in sequence instead of fseek. In the memory, you should discard the places where the disk should go to fseek. you should understand what I mean!
After 5.0.27, MYSQL supports set profling = 1. This will analyze in detail the time consumed by each step of your SQL statement.
If order by works with limit + index, you will be pleasantly surprised.
# Mysql code
