创建表和索引
-- 建表语句
CREATE TABLE test (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
c1 VARCHAR (10),
c2 VARCHAR (10),
c3 VARCHAR (10),
c4 VARCHAR (10),
c5 VARCHAR (10)
);
-- 插入数据
INSERT INTO test(c1,c2,c3,c4,c5) VALUES
('a1','a2','a3','a4','a5'),
('b1','b2','b3','b4','b5'),
('c1','c2','c3','c4','c5'),
('d1','d2','d3','d4','d5'),
('e1','e2','e3','e4','e5');
-- 创建索引
CREATE INDEX idx_test_c1234 ON test(c1,c2,c3,c4);
查看索引
mysql> show index from test;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| test | 1 | idx_test_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test | 1 | idx_test_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
一般正常使用情况,索引有效
mysql> explain select * from test where c1='a1';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 33 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where c1='a1' and c2='a2';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where c1='a1' and c2='a2' and c3='a3';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 99 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='a4';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 132 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
案例分析
1. 查询时索引列的位置在where语句后面交换
-- 在执行下方语句时,查询优化器(`MySQL Query Optimizer`)会自动优化,将SQL语句达到最佳的查询效果。
-- 它将where后面的查询语句顺序调整到了和索引建立的顺序一样。
-- 但是最好还是怎么健索引,就用什么样的顺序来写,这样就避免了一些自动调整损耗。
mysql> explain select * from test where c1='a1' and c2='a2' and c4='a4' and c3='a3';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 132 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where c4='a4' and c3='a3' and c2='a2' and c1='a1';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 132 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
2. 使用范围查询
-- 范围之后全失效,只用到了c1,c2,c3 的索引,c3变成了范围查询也用到了,之后的索引并没有用到
mysql> explain select * from test where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | idx_test_c1234 | idx_test_c1234 | 99 | NULL | 1 | 20.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- 用到了c1,c2,c3,c4的索引,因为自动优化后,将c3排到的c4前面,c4变成了范围查询也用到了
mysql> explain select * from test where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | idx_test_c1234 | idx_test_c1234 | 132 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3. 使用排序
-- 使用了c1,c2的索引,c4因为前面的断开了,所以没有用到,c3严格来说也用到了,用于排序而不是查找
mysql> explain select * from test where c1='a1' and c2='a2' and c4='a4' order by c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 20.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- 和上方一致,因为上方的c4已经没有用到索引了,跟它关系不大
mysql> explain select * from test where c1='a1' and c2='a2' order by c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- 出现了Using filesort,因为前面的c1,c2的索引虽然用到了,但是没有c3,索引断开了,如果从c4排序,mysql只能变为文件内排序
-- 如果出现这样的情况,查询的效率会大大的降低
mysql> explain select * from test where c1='a1' and c2='a2' order by c4;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+---------------------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
-- 索引的c1被用到了查询,c2和c3被用到了排序,无filesort
mysql> explain select * from test where c1='a1' and c5='a5' order by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 33 | const | 1 | 20.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
-- 出现了Using filesort,建立的索引是1234,它没有按照顺序来,32颠倒了
mysql> explain select * from test where c1='a1' and c5='a5' order by c3,c2;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 33 | const | 1 | 20.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
-- 查询和排序都是按照顺序,没有问题
mysql> explain select * from test where c1='a1' and c2='a2' order by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- 和上方差不多,跟c5没有太大关系
mysql> explain select * from test where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 20.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
-- 一般情况下,order by 的顺序只要没有跟索引建立的顺序一样,都会产生filesort
-- 但是排序字段如果已经是一个常量了,后面的排序会将它忽略掉
mysql> explain select * from test where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 66 | const,const | 1 | 20.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
4. 使用分组
-- 使用到了一个c1用于查询,c2和c3用于分组
mysql> explain select * from test where c1='a1' and c4='a4' group by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 33 | const | 1 | 20.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- 出现了 Using temporary 和 Using filesort!
-- group by基本上都需要进行排序,如果索引顺序错乱,会导致产生临时表
mysql> explain select * from test where c1='a1' and c4='a4' group by c3,c2;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c1234 | idx_test_c1234 | 33 | const | 1 | 20.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
建议
- 对于单键索引,尽量选择针对当前 Query 过滤性更好的索引
- 在选择组合索引的时候,当前 Query 中过滤性最好的字段在索引字段顺序中,位置越靠前越好
- 在选择组合索引的时候,尽量选择可以能够包含当前 Query 中的 Where 字句中更多字段的索引
- 尽可能通过分析统计信息和调整 Query 的写法来达到选择合适索引的目的