MySQL系列:索引

1 索引

MySQL索引是一种数据结构，提高数据查询速度，降低数据库I/O成本，MySQL在300万行数据左右性能开始逐渐下降.
以id作为索引，索引与数据结构如下图所示，
由图可知，索引与数据是映射关系，通过索引可以找到数据。

不使用索引查询id=4的数据：
需要遍历所有数据，磁盘扫描，磁道寻址耗时最长。

使用B-Tree索引查询id=4的数据：
树结构遍历，无需遍历所有数据，耗时相对较少。

1.1 为什么使用索引

提高数据查询速度.

1.2 SQL执行慢的原因

序号	描述
1	硬件问题,网络速度慢,内存不足,I/O吞吐量小,磁盘空间已满等
2	没有索引或索引失效
3	数据过多(分库分表)
4	服务器未调优及各参数配置

2 索引优缺点

2.1 优点

序号	描述
1	大大减少服务器需要扫描的数据量,如B-Tree索引,不会扫描全表
2	帮助服务器避免排序和临时表,B-Tree索引顺序存储数据,使用即排序
3	将随机I/O变成顺序I/O,B-Tree索引顺序存储数据
4	通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
5	大大提高数据检索速度
6	加速表和表之间的连接
7	使用分组和排序语句进行数据检索时,索引可显著减小查询中分析和排序时间
8	查询过程中,使用优化隐藏器,提高系统性能

InnoDB引擎所有MySQL列类型(字段类型)都可以被索引,也可以给任意字段设置索引;
结果:加快数据查询速度;

2.2 缺点

序号	描述
1	创建和维护索引耗费时间,且随着数据量的增加,创建和维护成本增加
2	索引占用磁盘空间,数据表中的数据有最大上限设置,若有大量索引,索引文件可能比数据更快达到上限值
3	当对表中的数据进行增删改时,索引需要动态维护,降低数据维护速度

3 使用原则

序号	场景	使用索引	部分索引	不使用索引	备注
	数据表经常更新		√		避免过多索引,仅对经常查询的字段创建索引
	表数据量小			√	数据量较小,查询全部数据花费的时间可能比遍历索引的时间短,此时的索引不起优化作用
	字段数据类型少的列			√	如性别字段,只有两种数据,不建索引,若值种类较多,使用索引

4 索引种类

4.1 单列索引

每个索引中只包含一个列(字段),一个表中可以有多个单列索引;

序号	索引	描述
1	普通索引	基本索引类型,基本无限制,允许在定义索引的列中插入重复的值或空值,为了提高查询速度
2	唯一索引	索引列中的值必须是唯一的,允许空值
3	主键索引	特殊的唯一索引,不允许有空值,MySQL中主键为默认索引

4.2 组合索引

数据表中多个列上创建索引,顺序使用,从左到右,不可跳着使用索引,最左前缀规则,如多个索引(id,name,sex),顺序依次为id,name和sex,查询走索引:(id,name,sex)和(id,name),查询不走索引:(id,sex),sex,(sex,name).

4.3 全文索引

只有MyISAM存储引擎可用,只能在CHAR,VARCHAR和TEXT类型字段上使用全文索引,全文索引,即在某段文字中,通过关键字查询该行数据.

4.4空间索引

对空间数据类型字段建立的索引,MySQL中空间数据类型有四种:GEOMETRY,POINT,LINESTRING和POLYGON,创建空间索引时,使用SPATIAL关键字,MyISAM引擎创建空间索引的列,为NOT NULL.

5 索引方式

5.1 聚簇索引

索引项的排序方式和数据表中数据记录排序方式一致(如字典拼音目录按照A~ Z排序,汉字也是按照A~Z的拼音排列),每张表只能有一个聚簇索引(聚簇索引叶子页包含索引项和整个行数据).
聚簇索引不是单独的索引类型,而是一种数据存储方式,即BTree索引+记录的数据行.
存储方式：索引+数据；
可以通过索引直接定位到数据，无需回表。

• 优点

序号	描述
1	将相关数据保存在一起,减少磁盘I/O
2	数据访问更快,避免全表扫描
3	使用覆盖索引扫描的查询可以直接使用叶结点中的主键值

• 缺点

序号	描述
1	最大限度地提高了I/O密集型应用的性能,但如果数据全放内存中,访问顺序就没有那么重要了,聚簇索引也没有特别的优势了
2	插入速度严重依赖插入顺序
3	更新聚簇索引的代价很高
4	插入新行或主键更新需要移动时,可能面临页分裂,当行的主键要求必须插入到某个已满的页中时,存储已经会将该页分裂成两个页面来容纳该行,这是一次页分裂操作,页分裂会导致数据表占用更多的磁片空间
5	二级索引即普通索引,在其叶子节点包含了引用行的主键列

5.2 非聚簇索引(辅助索引)

非聚簇索引也称辅助索引，非聚簇索引的逻辑顺序与磁盘上的物理存储顺序不同，一个数据表中可以有多个非聚簇索引，叶子页不包含行记录的所有数据，叶子页除了包含键值，还存储指向行数据的聚簇索引键的标签(存储的为行号)，非聚簇索引搜索两次，通过键值查询到行号,通过行号定位行数据。
存储方式：索引与数据单独存储，索引映射数据。

回表：
通过索引获取数据映射后，需要再次通过数据映射定位数据，即回表。

6 索引失效的情况

序号	索引失效
1	like以%开头无效,以%结尾有效
2	or语句前后没有同时使用索引,当or左右查询字段只有一个是索引,索引失效
3	组合索引,不是第一列索引,跳列查询
4	数据类型出现隐式转化,如varchar不加单引号可能自动转换为int型,使用索引无效,全表扫描
5	在索引列上使用IS NULL或IS NOT NULL,索引是不会索引空值的
6	索引字段上使用not,<>,!=
7	对索引字段进行计算操作,字段上使用函数
8	全表扫描速度比索引速度快时,mysql会使用全表扫描,索引无效

7 索引分析工具

EXPLAIN

---

参考文献
[1]https://blog.csdn.net/qq_36906627/article/details/86634518
[2]https://blog.csdn.net/Xin_101/article/details/102580278
[3]https://www.jianshu.com/p/0d6c828d3c70
[4]https://www.cnblogs.com/Aiapple/p/5693239.html
[5]https://www.cnblogs.com/nov5026/p/11210078.html
[6]https://blog.csdn.net/u013308490/article/details/83001060
[7]https://www.cnblogs.com/wdss/p/11186411.html
[8]https://www.cnblogs.com/qixidi/p/10260180.html