mysql-索引

一、索引

回表：

B+ 树索引主要可以分为两种索引，聚集索引和非聚集索引。

聚集索引：也就是平常我们说的主键索引，在 B+ 树中叶子节点存的是整行数据。

非聚集索引：也叫二级索引，也就是一般的普通索引，在 B+ 树中叶子节点存的是主键的值。

如果直接用主键查找，用的是聚集索引，能找到全部的数据。

如果我们是用非聚集索引查找，如果索引里不包含全部要查找的字段，则需要根据索引叶子节点存的主键值，再到聚集索引里查找需要的字段，这个过程也叫做回表。

如果用到了回表，就需要二次查询的过程，效率肯定更慢，很简单，回表是因为要查询的字段在非聚集索引里没有，所以在满足需求的情况下，我们尽量使非聚集索引里有要查询的索引字段。所以在查询时，可以尽量用聚集索引来查（也就是用主键来查询），或者根据业务需求，建好的索引，满足索引查询字段。但是实际业务中，很难建立一个索引就能满足所有查询要求，所以，正常情况，回表也没事，只要能用到索引也能大大加快查询速度。

 

索引覆盖：
MySQL 官网，类似的说法出现在 explain 查询计划优化章节，即 explain 的输出结果 Extra 字段为 Using index 时，能够触发索引覆盖。只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。

如何实现索引覆盖？将被查询的字段，建立到联合索引里去。

create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> index idx_age (age)
    -> )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，age 字段是普通索引（二级索引）

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如：select * from user where id = 1;

回表过程：
如：select * from user where age = 30;

1. 先通过普通索引 age=30 定位到主键值 id=1
2. 再通过聚集索引 id=1 定位到行记录数据

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
无需回表例如：select id,age from user where age = 10;
explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引,无需回表

回表例如：select id,age,name from user where age = 10;
explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询
为了实现索引覆盖，需要建组合索引idx_age_name(age,name)
explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。

 

 最左前缀原则：

最左前缀原则是发生在联合索引上，即最左优先，在检索数据时从联合索引的最左边开始匹配使用联合索引时，从左到右匹配索引

假设在列(a,b,c)上创建了索引，那么索引可以用来搜索a、ab、abc三种字段组合，相当于建立了a、ab、abc三个索引（但事实上只建立了一个B+树）。如果字段组合不构成索引的前缀，就不能使用该索引，例如b、bc等。

对于联合索引(col1,col2,col3)，查询语句SELECT col1,col2,col3 FROM test WHERE col2=2;是否能够触发索引？
Yes
这是因为要查询的列恰好是联合索引的各个列。所以该联合索引也是覆盖索引。只要是覆盖索引就不管最左前缀匹配原则，都会走索引。

 

 

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

SELECT * FROM student WHERE name='某某某'

　　搜索引擎只能扫描整个表的每一行，并依次对比判断name的值是否等于“某某某”。我们知道，单纯的内存运算是很快的，但从磁盘中取数据到内存中是相对慢的，当表中有大量数据时，内存与磁盘交互次数大大增加，这就导致了查询效率低下。

常见的从逻辑上，索引可以区分和使用场景：

1. 主键索引：用于唯一标识表中的记录，通常由自增整数或GUID等方式生成。主键索引的优点在于查询速度快，同时还可以保证表中记录的唯一性。因此，在设计表结构时，应尽可能地选择一个合适的主键。主键索引是一种特殊的唯一索引，不允许值重复或者值为空
2. 唯一索引：用于保证表中记录的唯一性。和主键索引类似，唯一索引也可以提高查询速度。通常，对于不适合作为主键的字段，可以使用唯一索引来确保数据的唯一性。
3. 普通索引：最常见的一种索引类型，用于加速对数据的查找。在选择普通索引时，需要根据查询语句中使用的列来决定。通常情况下，查询语句中经常出现的列和WHERE子句中使用的列都应该建立索引。需要注意的是，如果某个列的取值非常少，那么建立索引可能不是很有用，反而会浪费空间。普通索引允许在定义索引的列中插入重复值和空值
4. 复合索引：用于同时对多个列进行查找。复合索引可以避免MySQL中的回表操作，从而提高查询速度。需要注意的是，复合索引的建立顺序需要根据实际查询场景进行决定。
5. 全文索引：用于对文本内容进行全文搜索。全文索引可以对包含文本内容的列进行查找，例如文章的标题、正文和标签等。：全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。

 

索引的优点如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

索引的缺点：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

 

索引失效场景

1、条件字段原因

• 单字段有索引，WHERE条件使用多字段（含带索引的字段）例如 SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句，如果name有索引而addr没索引，那么SQL语句不会使用索引。
• 多字段索引，违反最佳左前缀原则。例如，student表如果建立了(name,addr,age)这样的索引，WHERE后的第一个查询条件一定要是name，索引才会生效。

2、<>、NOT、in、not exists

　　当查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。否则，索引定位困难（结合我们查字典的例子去理解），执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

3、查询条件中使用OR

　　如果条件中有or，即使其中有条件带索引也不会使用(因此SQL语句中要尽量避免使用OR)。要想使用OR，又想让索引生效，只能将OR条件中的每个列都加上索引。

4、查询条件使用LIKE通配符

　　SQL语句中，使用后置通配符会走索引，例如查询姓张的学生（SELECT * FROM student WHERE name LIKE '张%'），而前置通配符(SELECT * FROM student WHERE name LIKE '%东')会导致索引失效而进行全表扫描。

5、索引列上做操作（计算，函数，（自动或者手动）类型装换）

　　有以下几种例子：

• 在索引列上使用函数：例如select * from student where upper(name)='ZHANGFEI';会导致索引失效，而select * from student where name=upper('ZHANGFEI');是会使用索引的。
• 在索引列上计算：例如select * from student where age-1=17;

6、在索引列上使用mysql的内置函数，索引失效

　　例如，SELECT * FROM student WHERE create_time

7、索引列数据类型不匹配

　　例如，如果age字段有索引且类型为字符串（一般不会这么定义，此处只是举例）但条件值为非字符串，索引失效，例如SELECT * FROM student WHERE age=18会导致索引失效。

8、索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引

　　B-tree索引IS NULL不会使用索引，IS NOT NULL会使用，位图索引IS NULL、IS NOT NULL都会使用索引。

最后，对索引的使用做一个总结吧：

1. 索引有利于查询，但不能随意加索引，因为索引不仅会占空间，而且需要在写库时进行维护。
2. 如果多个字段常常需要一起查询，那么在这几个字段上建立联合索引是个好办法，同时注意最左匹配原则。
3. 不要在重复度很高的字段上加索引，例如性别。
4. 避免查询语句导致索引失效，哪些情况会导致索引失效请见前文。

 

创建索引

student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4),查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。
CREATE INDEX index_addr ON student(address(4));

student 表中的 name 和 address 字段上建立名为 index_na 的索引
CREATE INDEX index_na ON tb_student(name,address);

在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，
系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。（应用最左前缀原则）

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

• <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
• <表名>：指定要创建索引的表名。
• <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
• <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
• ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

普通索引：CREATE INDEX index_name ON student (name)
唯一索引：CREATE UNIQUE index_name ON student (name)

ALTER TABLE 语句也可以在一个已有的表上创建索引

• 主键索引：ALTER TABLE student ADD PRIMARY KEY (name);
• 唯一索引：ALTER TABLE student ADD UNIQUE INDEX index_name(name);
• 普通索引：ALTER TABLE student ADD INDEX index_name(name);

查看索引

SHOW INDEX FROM <表名>

删除索引
DROP INDEX <索引名> ON <表名>

explain 分析SQexplain 分析SQL的执行计划

需要重点关注type、rows、filtered、extra。

type由上至下，效率越来越高
ALL 全表扫描
index 索引全扫描
range 索引范围扫描，常用语<,<=,>=,between,in等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null MySQL不访问任何表或索引，直接返回结果
虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";
如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

Extra
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

EXPLAIN select * from adminlog 

id	select_type	table	partitjons	type	possible_keys	key	key_len	ref	row	filtered	Extra
1	SIMPLE	adminlog		ALL					2	100

 

如何优化索引？

在使用索引的过程中，还需要注意一些优化技巧，以保证查询性能的最大化。

1. 避免过度索引：过多的索引会增加数据库的存储和维护成本，同时也会影响数据库的性能。因此，在建立索引时，应该根据实际查询场景进行决定，尽量避免过度索引。
2. 建立复合索引：对于经常需要同时查询多个列的语句，建立复合索引可以有效地提高查询效率。需要注意的是，复合索引的建立顺序需要根据实际查询场景进行决定。
3. 使用覆盖索引：覆盖索引是指查询语句只需要使用索引就可以获取需要的数据，而不需要回表操作。覆盖索引可以减少MySQL的I/O操作，从而提高查询效率。
4. 避免使用函数在索引列上进行计算：在查询语句中，应尽量避免在索引列上使用函数进行计算，因为这样会使MySQL无法使用索引，而需要进行全表扫描。
5. 定期维护索引：定期维护索引可以清理无用的索引，以保证数据库的正常运行。例如，可以使用OPTIMIZE TABLE语句来对表进行优化，以清理无用的索引和碎片。

 

sql怎样查看有没有命中索引 - 知乎 (zhihu.com)

Mysql索引：图文并茂，深入探究索引的原理和使用 - 知乎 (zhihu.com)

干货 一文教你如何进行MySQL索引优化 - 知乎 (zhihu.com)

MySQL 的覆盖索引与回表 - 知乎 (zhihu.com)