MySQL联合索引最左匹配范例
2023-09-27 14:26:42 时间
MySQL联合索引最左匹配范例 参考文章:http://blog.jobbole.com/24006/ 创建示例表。 示例表来自MySQL官方文档: https://dev.mysql.com/doc/employee/en/ CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, PRIMARY KEY (emp_no,title,from_date) ) ; 导入测试数据 load_titles.dump https://github.com/datacharmer/test_db 索引情况 mysql> show index from titles; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comme +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------ | titles | 0 | PRIMARY | 1 | emp_no | A | 0 | NULL | NULL | | BTREE | | titles | 0 | PRIMARY | 2 | title | A | 0 | NULL | NULL | | BTREE | | titles | 0 | PRIMARY | 3 | from_date | A | 0 | NULL | NULL | | BTREE | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------ 3 rows in set (0.00 sec) 情况一.WHERE条件中全部列都在索引中 不管WHERE的顺序如何执行计划都可以完整用到索引。 mysql> desc SELECT * FROM titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26'; +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | titles | NULL | const | PRIMARY | PRIMARY | 159 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' AND emp_no='10001' AND from_date='1986-06-26'; +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | titles | NULL | const | PRIMARY | PRIMARY | 159 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' AND title='Senior Engineer' AND emp_no='10001'; +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | titles | NULL | const | PRIMARY | PRIMARY | 159 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='10001' AND title in('Senior Engineer','Staff','Assistant Engineer') AND from_date='1986-06-26'; +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 159 | NULL | 3 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no in('10061','10062','10063','10064') AND title in('Senior Engineer','Staff','Assistant Engineer') AND from_date='1986-06-26'; +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 159 | NULL | 12 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date>='1986-06-26'; +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 159 | NULL | 1 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 情况二.WHERE条件和ORDER BY中全部列都在索引中,索引最左列在WHERE条件中, 不管ORDER BY是ASC还是DESC,执行计划都可以完整用到索引。 mysql> desc SELECT * FROM titles WHERE emp_no='10001' AND title='Senior Engineer' order by from_date; +----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 156 | const,const | 1 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='10001' AND title='Senior Engineer' order by from_date desc; +----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 156 | const,const | 1 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='10001' order by title,from_date; +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> desc SELECT * FROM titles WHERE emp_no='10001' order by title,from_date desc; +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+ | 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using filesort | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='10001' order by title desc,from_date desc; +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 情况三.当WHERE条件和ORDER BY中全部列都在索引中,索引最左列不在WHERE条件中而是在ORDER BY中 执行计划会走index。 mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' order by emp_no,from_date; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' order by emp_no desc,from_date desc; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' order by emp_no,title; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' order by emp_no desc,title desc; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' AND from_date='1986-06-26' order by emp_no; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' AND title='Senior Engineer' order by emp_no; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' AND from_date='1986-06-26' order by emp_no desc; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' AND title='Senior Engineer' order by emp_no desc; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 情况四.当WHERE条件无过滤,索引列全在ORDER BY中 1.当order by 中ASC和DESC顺序和索引一致执行计划会走INDEX,如果不一致则会走全表扫描。 mysql> desc SELECT * FROM titles order by emp_no,title,from_date; +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+ | 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> desc SELECT * FROM titles order by emp_no,title desc,from_date desc; +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | titles | NULL | ALL | NULL | NULL | NULL | NULL | 441772 | 100.00 | Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles order by emp_no desc,title,from_date; +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | titles | NULL | ALL | NULL | NULL | NULL | NULL | 441772 | 100.00 | Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles order by emp_no,title desc,from_date desc; +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | titles | NULL | ALL | NULL | NULL | NULL | NULL | 441772 | 100.00 | Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)
相关文章
- 阿里三面:MySQL回表的性能伤害有多大?
- 阿里面试官:设计个MySQL的Hash索引吧?
- mysql:索引原理与慢查询优化
- MySQL 普通索引、唯一索引和主索引
- java实现mysql数据库的备份及还原
- Linux 操作MySQL常用命令行(转)
- window 下忘记了mysql 密码的解决方法
- 面试官:谈谈 MySQL 联合索引生效、失效的条件?
- 一次 MySQL 索引面试,被面试官怼的体无完肤!
- 基于spring boot注解的mysql通用数据加载方案的简单实现
- MySQL索引原理及慢查询优化-来自美团网的技术blog(写的深入浅出)
- Mysql 数据字典
- mysql数据库性能优化(包括SQL,表结构,索引,缓存)
- Ubuntu 12.04上安装MySQL并运行
- 2023-03-10 mysql-innodb-mvcc-分析
- 我说MySQL联合索引遵循最左前缀匹配原则,面试官让我回去等通知
- MySQL索引:回表、索引覆盖,最左匹配原则、索引下推
- mysql索引失效的情况
- 开发人员不得不知的MySQL索引和查询优化
- mysql索引分类以及创建使用
- MySQL 5.6.26几种安装包的区别
- mysql 数据操作。唯一索引
- MYSQL如何导出存储过程和触发器?
- 15个必知的Mysql索引失效场景,别再踩坑了