MySQL索引背后的数据结构及算法原理( 七 )

2025-12-25 MySQL索引

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
EXPLAIN SELECT * FROM employees.titlesWHERE emp_no < '10010'AND title='Senior Engineer'AND from_date BETWEEN '1986-01-01' AND '1986-12-31';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table| type| possible_keys | key| key_len | ref| rows | Extra|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1 | SIMPLE| titles | range | PRIMARY| PRIMARY | 4| NULL |16 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range 。同时，用了“between”并不意味着就是范围查询，例如下面的查询：
EXPLAIN SELECT * FROM employees.titlesWHERE emp_no BETWEEN '10001' AND '10010'AND title='Senior Engineer'AND from_date BETWEEN '1986-01-01' AND '1986-12-31';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table| type| possible_keys | key| key_len | ref| rows | Extra|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1 | SIMPLE| titles | range | PRIMARY| PRIMARY | 59| NULL |16 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。
情况七：查询条件中含有函数或表达式。很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如：
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| id | select_type | table| type | possible_keys | key| key_len | ref| rows | Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1 | SIMPLE| titles | ref| PRIMARY| PRIMARY | 4| const |1 | Using where |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。再如：
EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table| type | possible_keys | key| key_len | ref| rows| Extra|+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|1 | SIMPLE| titles | ALL| NULL| NULL | NULL| NULL | 443308 | Using where |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。
索引选择性与前缀索引既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。
第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。
另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

推荐阅读

上一篇：昆仑雪菊胎菊冲泡方式,昆仑雪菊茶的冲泡步骤

下一篇：火焰参茶的功效,生津止渴功效