Summary of the slow query analysis caused by the MySQL prefix index
- 2020-05-17 06:46:25
- OfStack
The front-end time is related to 1 project of DB, and alanc feedback has 1 query. Using index is many times slower than not using index, which is a bit ruined. So I followed up 1, and I looked at the different results of 2 queries using explain.
The result of a query that does not use an index is as follows. The actual query compares the speed blocks.
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010066 | |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
Using the index order by, the results were surprisingly slow.
mysql > explain select * from rosterusers order by username limit 10000,3 ;
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010087 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
The difference is that Extra using index queries becomes Using filesort. Using an external file for sorting. This is slow, of course.
But the data table is indexed on username. Why would you want Using filesort instead?
Look at 1 data table definition. Is a table of an open source chat server ejabberd. At first glance, the primary key i_rosteru_user_jid is the joint index of username and jid, so the index should be available when order by username is used.
Close inspection suddenly found that its primary key definition, not the definition of the full primary key name, but with a 75 length description, slightly 1 leng, the original use of the prefix index, rather than the entire field is an index. (I don't remember InnoDB supporting this yet, but I guess it was added in some version after 4.0.) prefix index is a way to index the first N bytes in a data field.
After discovering this problem, we began to suspect that the slow query was related to this index. The primary use of prefix indexes is sometimes in the field process, while many of the indexes supported by MySQL are of limited length.
First without order by limit this query, nature is likely to be associated with a primary key, because MySQL INNODB actual operation is depend on the primary key (even if you don't have to build, the system also can have a default), and limit this query, use a primary key can be speed up, (rows explain return should be a reference), even though I didn't see any document clear instructions about this problem, But the results returned by the limit query, which never takes order by with it, pretty much prove it.
But when we use order by username, we want to use the username sort, not the username (75) sort, but the actual index is a prefix index, not a full field index. So it turns out that order by doesn't have access to indexes at all. (I also added the mandatory index i_rosteru_user_jid to the SQL statement.) In fact, username does not even use 75 fields in the table, let alone the 250 length defined. It's all self-inflicted. Since this is the table of other products, we cannot change it, so we have to make do with the query without sorting for the time being.
Conclusion:
The & # 8226; Prefix indexing is not a panacea, it does help us index a long field. It also results in the inability to use prefix indexes on sort (order by, group by) queries.
The & # 8226; Any time you have an DB Schema definition, it is a priority to properly plan your field length and field type.
The result of a query that does not use an index is as follows. The actual query compares the speed blocks.
mysql> explain select * from rosterusers limit 10000,3 ;
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010066 | |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
Using the index order by, the results were surprisingly slow.
mysql > explain select * from rosterusers order by username limit 10000,3 ;
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010087 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
The difference is that Extra using index queries becomes Using filesort. Using an external file for sorting. This is slow, of course.
But the data table is indexed on username. Why would you want Using filesort instead?
Look at 1 data table definition. Is a table of an open source chat server ejabberd. At first glance, the primary key i_rosteru_user_jid is the joint index of username and jid, so the index should be available when order by username is used.
CREATE TABLE `rosterusers` (
`username` varchar(250) NOT NULL,
`jid` varchar(250) NOT NULL,
UNIQUE KEY `i_rosteru_user_jid` (`username`(75),`jid`(75)),
KEY `i_rosteru_jid` (`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Close inspection suddenly found that its primary key definition, not the definition of the full primary key name, but with a 75 length description, slightly 1 leng, the original use of the prefix index, rather than the entire field is an index. (I don't remember InnoDB supporting this yet, but I guess it was added in some version after 4.0.) prefix index is a way to index the first N bytes in a data field.
After discovering this problem, we began to suspect that the slow query was related to this index. The primary use of prefix indexes is sometimes in the field process, while many of the indexes supported by MySQL are of limited length.
First without order by limit this query, nature is likely to be associated with a primary key, because MySQL INNODB actual operation is depend on the primary key (even if you don't have to build, the system also can have a default), and limit this query, use a primary key can be speed up, (rows explain return should be a reference), even though I didn't see any document clear instructions about this problem, But the results returned by the limit query, which never takes order by with it, pretty much prove it.
But when we use order by username, we want to use the username sort, not the username (75) sort, but the actual index is a prefix index, not a full field index. So it turns out that order by doesn't have access to indexes at all. (I also added the mandatory index i_rosteru_user_jid to the SQL statement.) In fact, username does not even use 75 fields in the table, let alone the 250 length defined. It's all self-inflicted. Since this is the table of other products, we cannot change it, so we have to make do with the query without sorting for the time being.
Conclusion:
The & # 8226; Prefix indexing is not a panacea, it does help us index a long field. It also results in the inability to use prefix indexes on sort (order by, group by) queries.
The & # 8226; Any time you have an DB Schema definition, it is a priority to properly plan your field length and field type.