查看“索引：索引使用、索引失效”的源代码

[[category:MySQL]]

== 关于 ==
关于索引的基础知识（索引结构：B+树，聚集索引与辅助索引，复合索引，全文索引，索引前缀，回表，排序索引构建：索引的批量加载，索引下推 等），笔记中已有深入了解。


以下记录对“索引使用”、“索引失效”的情况进行说明：
# 什么情况下使用索引？
# 范围查询是否使用索引？
# 什么情况下索引失效？
# 如何进行索引优化？

=== 补：覆盖索引===
覆盖索引（covering index）：索引包含查询检索到的所有列（查询字段、条件列）。【即：索引覆盖了所有需要的列】
: 即：'''非主键索引包含所需列，而无需回表。'''


* 覆盖索引其实是数据库'''查询优化器的一种机制'''，而非索引算法或索引实现。
* 覆盖索引是建立在'''已有索引'''基础之上的；而要利用覆盖索引进行优化，常常通过'''复合索引'''。
* 即从非主键索引中就能得到所需的数据列，而不需要查询主键索引中的记录，'''避免了回表'''产生的树搜索所带来的I/O操作，从而提升性能。
* 使用覆盖索引 InnoDB 比 MyISAM 效果更好：InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。【？】


'''注意：'''以下情况，执行计划不会选择覆盖查询：
# select 选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。【索引未全面覆盖】
# where 条件中不能含有对索引进行 like 的操作。【？不使用索引？】

==== 示例 ====
有表如下：
<syntaxhighlight lang="mysql" highlight="">
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `I_name` (`name`)
) ENGINE=InnoDB;

INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);
</syntaxhighlight>
对应索引结构如下：
: [[File:覆盖索引：示例_1.jpg|600px]]


对于如下查询：
: <syntaxhighlight lang="mysql" highlight="">
SELECT age FROM student WHERE name = '小李'；
</syntaxhighlight>
# 通过其普通索引，查询如下：【未使用“覆盖索引”优化】
## 在“name”索引树上找到“小李”对应的节点，获取其“id”为“03”；
## 从主索引树上找到“id”为“03”的叶子节点；【回表】
## 在叶子节的数据中，获取字段命为“age”的值“12”；
#: 以上查询，未使用“覆盖索引”，需通过回表在主索引上才能得到“age”值。
# 删除其“name”索引，并以“name”和“age”两个字段建立联合索引：【建立复合索引，以使用“覆盖索引”优化】
#: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);
</syntaxhighlight>
#: 其复合索引结构如下：
#: [[File:覆盖索引：示例_2.jpg|600px]]
#: 通过复合索引，查询流程如下：【使用复合索引】
## 在“name，age”索引树上找到“小李”对应的节点，并在该节点的索引值中获取其“age”为“12”；
#: 以上查询，直接从索引中得到“age”值，而并未回表。
#:【“name，age”索引，包括了条件中的“name”字段和查询结果中的“age”字段】


判断“覆盖优化”，通过查询执行计划：
: [[File:覆盖索引：示例_3.jpg|600px]]
如上，在 explain 的 extra 列可以看到“'''using index'''”的信息。

=== 补：Fast Index Creation【？？？】 ===
在 MySQL 5.5 之前，对于索引的添加或者删除，每次都需要创建一张'''临时表'''，然后导入数据到临时表，接着删除原表，如果一张大表进行这样的操作，会非常的耗时，这是一个很大的缺陷。


InnoDB存储引擎从1.0.x版本开始加入了一种“Fast Index Creation”（快速索引创建）的索引创建方式：
: 每次为创建索引的表加上一个'''S锁'''（共享锁），在创建的时候，不需要重新建表，删除辅助索引只需要更新内部视图，并将辅助索引空间标记为可用，所以，这种效率就大大提高了。

==== “Fast Index Creation”与“排序索引构建”？ ====
“Fast Index Creation”是描述修改索引时，表的情况；
“排序索引构建”是描述索引构建方式：批量加载，而非单个索引插入；

== 索引类型 ==
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
# '''主键索引'''：即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；【InnoDB 中即“'''聚簇索引'''”】
#: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
</syntaxhighlight>
# '''唯一索引'''：用来建立索引的列的值必须是唯一的，'''允许空值'''；【只允许一个（行） NULL】
#: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；
</syntaxhighlight>
#* 唯一索引中使用的列应设置为“NOT NULL”，因为在创建唯一索引时，会将多个空值视为重复值。【？】 
# '''普通索引'''：用表中的普通列构建的索引，没有任何限制；
#: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE 'table_name' ADD INDEX index_name('col')；
</syntaxhighlight>
# '''全文索引'''：用非二进制的大文本对象（char、varchar、text）的列构建的索引；【“倒排索引”设计】
#: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
</syntaxhighlight>
# '''组合索引'''：用多个列组合构建的索引，这多个列中的值不允许有空值
#: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；
</syntaxhighlight>


辨析：
* “'''最左前缀'''”原则：把最常用作为检索或排序的列放在最左，依次递减。
** mysql 会从左向右匹配直到遇到不能使用索引的条件（>、<、!=、not、like模糊查询的%前缀）才停止匹配；
**: 组合索引相当于建立了“col1”、“col1，col2”、“col1，col2，col3”三个索引，而“col2”、“col3”、“col2，col3”或“col1，col3”是不能使用索引的。
* “'''索引前缀'''”（前缀索引）：在使用组合索引的时候可能因为列名长度过长而导致索引的 key 太大，导致效率降低，在允许的情况下，可以只'''取列的前几个字符作为索引'''。
*: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；
</syntaxhighlight>
*: 表示使用“col1”列的前4个字符和“col2”列的前3个字符作为索引。

== 索引操作 ==
# 创建：
## “CREATE TABLE”创建表时，指定相应索引。
## “CREATE INDEX”创建索引（普通索引、UNIQUE索引）。【“CREATE INDEX”不能用于创建 PRIMARY KEY 索引】
##: <syntaxhighlight lang="mysql" highlight="">
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
</syntaxhighlight>
## “ALTER TABLE”修改表，可用于表中添加索引（普通索引、UNIQUE索引、PRIMARY KEY索引）。
##: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
</syntaxhighlight>
# 删除：
## “DROP INDEX”删除索引。
##: <syntaxhighlight lang="mysql" highlight="">
DROP INDEX index_name ON talbe_name
</syntaxhighlight>
## “ALTER TABLE”修改表，可用于表中删除索引（普通索引、UNIQUE索引）。
##: <syntaxhighlight lang="mysql" highlight="">
ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY
</syntaxhighlight>
# 查看：
#: <syntaxhighlight lang="mysql" highlight="">
mysql> show index from tblname;

mysql> show keys from tblname;
</syntaxhighlight>

== explain执行计划中的索引 ==
explain 命令用来查看 select 语句执行计划，确认该 SQL 语句有没有使用索引，是否做全表扫描，是否使用覆盖索引等：
<syntaxhighlight lang="mysql" highlight="">
id   select_type   table   type   possible_keys   key   key_len   ref   rows   Extra
</syntaxhighlight>
* “select_type”：SELECT 类型；
* “type”：联接类型；
*: <syntaxhighlight lang="mysql" highlight="">
All | index | range | ref | eq_ref | const | system | null 
</syntaxhighlight>
* “possible_keys”：可能的索引选择；
* “key”：实际选择的索引；
* “key_len”：所选索引的长度；
* “ref”：索引参考的列；
* “rows”：预计读取的行数；
* “extra”：额外信息；
*: <syntaxhighlight lang="mysql" highlight="">
Using filesort | Using temporary | Using where | Using index condition | Using index 
</syntaxhighlight>
*# “Using index”：使用了'''覆盖索引'''，速度很快，限于查询字段都位于同一个索引中的场景
*# “Using index condition”：表示使用了ICP优化（Index Condition Pushdown，'''索引条件下推''')，能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数
*# “Using where”：表示在存储引擎检索行后mysql服务器再进行过滤
*# “Using filesort”：返回结果前需要做一次'''外部排序'''（内存或硬盘），速度慢应该尽量避免
*# “Using temporary”：在对查询结果排序时会使用一个'''临时表'''，速度慢

== “索引使用” ==
什么情况下，使用索引：
# '''匹配全值'''。
#: 对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件；
# '''匹配值的范围查询'''。【！！！】
#: 对索引的值能够进行范围查找；
# '''匹配最左前缀'''。（组合索引）
#* 最左匹配原则可以算是 MySQL 中 B-Tree 索引使用的首要原则。
# '''匹配列前缀'''。
#: 仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找；
# '''仅对索引进行查询'''。
#: 当查询的列都在索引的字段中时，查询的效率更高，'''所以应该尽量避免使用“select *”'''，需要哪些字段，就只查哪些字段；
# '''索引匹配部分精确，而其他部分进行范围匹配'''。
# 如果列名是索引，那么使用“'''column_name is null'''”就会使用索引。
#: <syntaxhighlight lang="mysql" highlight="">
explain select * from t_index where a is null \G
</syntaxhighlight>
# 经常出现在关键字“'''order by'''”、“'''group by'''”、“'''distinct'''”后面的字段。
# 在“'''union'''”等集合操作的结果集字段。
# '''经常用作表连接的字段'''。
# 考虑使用'''索引覆盖'''，对数据很少被更新，如果用户经常值查询其中你的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描变为索引的扫描。

=== 范围查询是否使用索引？ ===
对于范围查询【>，<，>=，<=，!=（<>），not，in（not in），between】，其中：
# '''正向查询'''（<、<=、=、>、>=、BETWEEN、IN）是可以使用索引的；
# '''负向查询'''（not ，not in， not like ，<> ,!= ,!> ,!< ） 不会使用索引；
* '''范围查询之后的条件不再使用索引'''。

注意：
* 对于连续的数值，能用“between”就不要用“in”了。
*: <syntaxhighlight lang="mysql" highlight="">
select id from t where num in(1,2,3)
select id from t where num between 1 and 3
</syntaxhighlight>
* 很多时候（子查询时）用“exists”代替“in”是一个好的选择。【虽然“exists”并不走索引，但子查询中可能用到索引】
*: <syntaxhighlight lang="mysql" highlight="">
select num from a where num in(select num from b)
select num from a where exists(select 1 from b where num=a.num)
</syntaxhighlight>

== “索引失效” ==
索引失效情况：
# MySQL 估计全表扫描比索引开销更小。
## 表太小；
## 结果数据记录占了表总记录的较大比例；
# 不符合“最左前缀”原则。
# 索引列类型不匹配。
## '''类型不匹配'''（包括“varchar”等类型的大小不匹配）；
##* 在“join”操作中（需要从多个数据表提取数据时），mysql只有在'''主键和外键的数据类型相同时才能使用索引'''，否则及时建立了索引也不会使用。
## 数据类型出现'''隐式转换'''（如：字符串值未使用单引号）；
#: <syntaxhighlight lang="mysql" highlight="">
--（age ：varchar）
where age = 30      -- 使用索引
where 'age' = '30'   -- 使用索引
where 'age' = 30    -- 不使用索引
</syntaxhighlight>
# 索引列为表达式：
## 索引列参与'''计算'''；
##: <syntaxhighlight lang="mysql" highlight="">
where a = 10 - 1    -- 使用索引
where a + 1 = 10    -- 不使用索引
</syntaxhighlight>
## 索引列上使用了'''函数'''；
##: <syntaxhighlight lang="mysql" highlight="">
where DAY(column) = ...
</syntaxhighlight>
# “'''like'''”操作以“'''%'''”为前缀。（不恰当的模糊匹配）
#* “like %keyword”：可以通过'''翻转函数'''优化。
# 用“'''or'''”分割开的条件，如果“or”前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
# '''负向查询'''（not ，not in， not like ，<> ,!= ,!> ,!< ） 。

== 索引优化 ==
=== 存储引擎对索引的优化 ===
* 见：“'''[[MySQL 优化：优化 SQL 语句：优化 SELECT 语句]]'''”

==== Multi-Range Read（MRR 多范围读取） ====
MySQL 5.6 开始支持，这种优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。
* 这种优化适用于 '''range'''、'''ref'''、'''eq_ref''' 类型的查询。


Multi-Range Read 优化的好处：
* 让数据访问变得较为顺序。
* 减少缓冲区中页被替换的次数。
* 批量处理对键值的查询操作。


可以使用参数“optimizer_switch”中的标记来控制是否开启 Multi-Range Read 优化。下面的方式将设置为总是开启状态：
<syntaxhighlight lang="mysql" highlight="">
SET @@optimizer_switch='mrr=on,mrr_cost_based=off';
</syntaxhighlight>

==== Index Condition Pushdown（ICP 索引条件下推） ====
MySQL 5.6 开始支持，不支持这种方式之前，当进行索引查询时，首先我们先根据索引查找记录，然后再根据 where 条件来过滤记录。然而，当支持 ICP 优化后，MySQL 数据库会在取出索引的同时，判断是否可以进行 where 条件过滤，也就是将 where 过滤部分放在了存储引擎层，大大减少了上层SQL对记录的索取。
* ICP 支持 '''range'''、'''ref'''、'''eq_ref'''、'''ref_or_null''' 类型的查询，当前支持 MyISAM 和 InnoDB 存储引擎。


可以使用下面语句开启ICP：
<syntaxhighlight lang="mysql" highlight="">
set @@optimizer_switch = "index_condition_pushdown=on"
</syntaxhighlight>
复制代码或者关闭：
<syntaxhighlight lang="mysql" highlight="">
set @@optimizer_switch = "index_condition_pushdown=off"
</syntaxhighlight>


复制代码当开启了 ICP 之后，在执行计划 Extra 可以看到“'''Using index condition'''”提示。

=== like模糊查询的优化 ===
模糊查询的三种情况：
# “'''like keyword%'''”：索引有效。
# “'''like %keyword'''”：索引失效，但可以通过'''翻转函数'''优化。
#: <syntaxhighlight lang="mysql" highlight="2">
where i.C_LCN_NO like '%245'                -- 使用索引
where reverse(i.C_LCN_NO) like reverse('%245')    -- 使用索引
</syntaxhighlight>
# “'''like %keyword%'''”：索引失效，且不能通过翻转函数优化。


模糊查询的替代方式：
: 使用下面的函数来进行模糊查询，如果出现的位置 > 0，则表示包含该字符串。查询效率比 like 要高。
# “'''LOCATE(substr,str)'''”：
#: 返回子串 substr 在字符串 str 中第一次出现的位置，没有则返回 0。
#: <syntaxhighlight lang="mysql" highlight="">
SELECT *
FROM t_blog t
WHERE LOCATE("xxx",t.field) > 0
</syntaxhighlight>
# “'''POSITION(substr IN str)'''”：
#: 返回子串 substr 在字符串 str 中第一次出现的位置，没有则返回 0。【position 是 locate 的别名】
#: <syntaxhighlight lang="mysql" highlight="">
SELECT *
FROM t_blog t
WHERE POSITION("xxx" IN t.field) > 0
</syntaxhighlight>
# “'''INSTR(str,substr)'''”：
#: 返回子串 substr 在字符串 str 中第一次出现的位置，没有则返回 0。【与 LOCATE() 形式相同，参数顺序相反】
#: <syntaxhighlight lang="mysql" highlight="">
SELECT *
FROM t_blog t
WHERE INSTR(t.field,"xxx") > 0
</syntaxhighlight>

== 索引设计原则 ==
# 最左前缀匹配原则。
# “'''='''”和“'''in'''”可以乱序。
#: 比如“a = 1 and b = 2 and c = 3 ”建立“(a,b,c)”索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；
# 尽量选择'''区分度高的列作为索引'''。【区分度：count(distinct col)/count(*)】
# 索引列不能参与计算，保持列“干净”。
#: 比如“from_unixtime(create_time) = '2014-05-29'”就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成“create_time = unix_timestamp(’2014-05-29’)”；
# 尽量的扩展索引，而不要新建索引。
# 定义有外键的数据列一定要建立索引。
# 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
# 对于定义为“'''text'''”、“'''image'''”和“'''bit'''”的数据类型的列不要建立索引。
#: 因为这些列的数据量要么相当大，要么取值很少。
# 对于经常存取的列避免建立索引。


* 依据索引使用、索引失效的情况，在使用中进行优化。
* '''mysql查询只使用一个索引'''，因此如果“where”子句中已经使用了索引的话，那么“order by”中的列是不会使用索引的。

== 相关问题 ==
# “[https://database.51cto.com/art/201907/599614.htm 我以为我对MySQL索引很了解，直到我遇到了阿里的面试官]”