Note: index (name,age) indicates that a federated index is established on the Name,age two column
because indexes have a critical impact on database query performance, here are some of my summary and experience:
a query can use only one index at a time: select name from user where name= ' Plantegg ' and age>35 if Index (name); At Index (age), the MySQL query optimizer automatically chooses a lasso to use;
MySQL chooses which index, so to see: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 | NULL | | BTREE | |
| photo | 1 | Index_random | 1 | Random | A | 237871 | NULL | NULL | YES | BTREE | |
| photo | 1 | fk_photo_profile_id | 1 | profile_id | A | 237871 | NULL | NULL | | BTREE | |
| photo | 1 | fk_photo_temp_photo_id | 1 | temp_photo_id | A | 237871 | NULL | NULL | YES | BTREE | |
| photo | 1 | fk_photo_album_id | 1 | album_id | A | 237871 | NULL | NULL | YES | BTREE | |
+-------+------------+------------------------+--------------+---------------+-----------+-------------+--- -------+--------+------+------------+---------+
the larger the cardinality, the finer the index candidates are (by default, the Btree index);
You can also try force index to enforce an index to see if it's faster for MySQL to query (if that's the case, you need to analyze yourtable, MySQL recalculates your cardinality to help him to choose Index correctly)
careful analysis of the results of explain: focus on extra,key,rows,select_type results!
Be careful with the group by and order by in the query, basically such queries will appear when explain: Using where; Using temporary; Using Filesort
The federated index should be used with caution when index (name,age), if where name= ' PP ' can use the index, where age=25 cannot use indexes; where name= ' pp ' and age>25 can use the index; where name = ' PP ' order by age can use index; where name> ' PP ' ORDER by age cannot use index, but where name> ' pp ' ORDER by NA Me,age can use the index, please pay close attention to the difference; ORDER BY name ASC Age DESC will not be able to use the index!
indexes are only useful for your queries when they are added to memory, and indexes that are too large to fit into memory lose their meaning! When accessing the index, you also need the random aceess disk, which is slower than no index!
Select can not be used without a select *, that is, which columns are required to take only those columns (Hibernate is not good for performance), such as: At index (Name), select * from user where Name lik E ' pp% ' and select name from the user where name like ' pp% ' the performance is very varied, if there are 10,000 records of the results (the user table total 1 billion records) the previous query may take 2 minutes (assuming your system 100 per second IOPS) After a query may take only 0.01 seconds! Because the previous query is going to take out the 10,000 records scattered all over the hard disk, the next query takes name directly from the in-memory index! After a query you explain, you will see the using Index in extra.
always be wary of random access to the disk, sequential read and write and random access performance difference is n magnitude (in order to read and write when your OS, Dish Cache this time) if you are interested in this question to write a test program, Random Read and write the time constantly fseek , correspondingly the same function you do not fseek but through the sequential read and write into memory, in memory to throw away those that should be fseek by the disk, you should understand what I mean!
after 5.0.27, MySQL supports set profling=1, which allows you to analyze your SQL statements in detail each step of the time consumed
If you have a limit + index match at the time of order by, you will be pleasantly surprised.
MySQL federated index SQL index using
