mysql组合索引中最左前缀匹配原理
来源:互联网 发布:好用的防晒霜推荐知乎 编辑:程序博客网 时间:2024/06/05 05:33
最左前缀原理与相关优化
高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关。
这里先说一下联合索引的概念。在上文中,我们都是假设索引只引用了单个的列,实际上,MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组<a1, a2, …, an>,其中各个元素均为数据表的一列。另外,单列索引可以看成联合索引元素数为1的特例。
以employees.titles表为例,下面先查看其上都有哪些索引:
SHOW INDEX FROM employees.titles;+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+| titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE || titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE || titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE || titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
从结果中可以到titles表的主索引为<emp_no, title, from_date>,还有一个辅助索引<emp_no>。这样就可以专心分析索引PRIMARY的行为了。
全列匹配
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
很明显,当按照索引中所有列进行精确匹配(这里精确匹配指“=”或“IN”匹配)时,索引可以被用到。这里有一点需要注意,理论上索引对顺序是敏感的,但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引,例如我们将where中的条件顺序颠倒,仍然可以用到索引:
EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
效果是一样的。
最左前缀匹配
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
当查询条件精确匹配索引的左边连续一个或几个列时,如<emp_no>或<emp_no, title>,所以可以被用到,但是只能用到一部分,即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一列前缀。
查询条件用到了索引中列的精确匹配,但是中间某个条件未提供,这样就不能用到组合索引了。
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
此时索引使用情况和情况二相同,因为title未提供,所以查询只用到了索引的第一列,而后面的from_date虽然也在索引中,但是由于title不存在而无法和左前缀连接,因此需要对结果进行扫描过滤from_date(这里由于emp_no唯一,所以不存在扫描)。如果想让from_date也使用索引而不是where过滤,可以增加一个辅助索引<emp_no, from_date>,此时上面的查询会使用这个索引。
- mysql组合索引中最左前缀匹配原理
- mysql组合索引最左前缀
- mysql 前缀索引
- 认识mysql前缀索引
- MySQL 前缀索引
- Mysql前缀索引
- mysql前缀索引
- mysql前缀索引
- MYSQL前缀索引
- mysql前缀索引
- Mysql前缀索引
- mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合
- mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合
- mysql前缀索引及其选择
- MySQL前缀索引长度方法
- MySQL前缀索引和索引选择性
- mysql前缀索引的索引选择性
- MySQL前缀索引和索引选择性
- 模板与泛型编程
- 二叉树反向遍历
- 【C++】用模板实现顺序表
- 图解 Android 事件分发机制
- 法线贴图(Normal Mapping)
- mysql组合索引中最左前缀匹配原理
- Django微信抢票大作业总结
- 二叉树中序遍历线索化
- Add to List 392. Is Subsequence
- Linux下SPI驱动的移植和应用程序的测试
- 非递归层次遍历查二叉树深度
- LeetCode90 Subsets II
- c++函数学习
- spring boot+logback+JdbcTemplate打印sql日志