MySQL 学习笔记——索引_mysql生成的索引对象是在哪-CSDN博客

本文链接：https://blog.csdn.net/qq_25274377/article/details/123285398

索引简介

索引是帮助 MySQL 高效获取数据的数据结构，一般存储在磁盘的文件中

索引优缺点

优点

提高数据查询的效率，降低数据库的 IO 开销
通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

缺点

索引需占用一定的磁盘空间
索引会降低更新表的效率，如对表进行增删改时，需要同时对索引文件进行维护

索引结构

MySQL 的索引在存储引擎层实现，不同的存储引擎有不同的索引结构

索引结构	说明	InnoDB	MyISAM	Memory
B+Tree 索引	使用最频繁的一种索引，基于 B+Tree	支持	支持	支持
Hash 索引	基于哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询	不支持	不支持	支持
R-Tree 索引（空间索引）	特殊索引类型，主要用于地理空间数据类型，使用较少	不支持	支持	不支持
Full-Tree 索引（全文索引）	通过建立倒排索引，快速匹配文档	5.6 以后支持	支持	不支持

B+Tree 索引

为什么要使用 B+Tree 作为索引的数据结构？

数据结构中，哈希表与树相比，哈希表的平均时间复杂度为O(1) ，而二叉树的平均时间复杂度为O(log(n))，显然哈希表更有优势，但对于数据库来说，更多的是需要范围查询而不是单条记录，树在范围查询时效率更高

二叉排序树在极端情况下，会转变为链表，效率降低

平衡二叉树或红黑树在数据量较大的时候，树的高度会比较大，IO 操作仍然效率较低，且没有利用到磁盘 IO 的预读能力。操作系统和磁盘之间一次数据交换是以页为单位的，一页大小为 4K，即每次 IO ，系统会将 4K 数据加载到内存。但是，在二叉树每个节点中只保存一个关键字，一个数据区，两个子节点的引用，并不能够填满 4K 的内容，对于 B-Tree 和 B+Tree ，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 IO 就能完全载入

B+Tree 相较于 B-Tree 的优点

对于 B+Tree ，数据只存在于叶子节点，这样每个节点（相同的一页大小）存储的数据会变多，从而存储大量数据时树的深度会比 B-Tree 小很多，提高效率
对于 B+Tree ，增删节点时，效率更高，因为 B+Tree 的叶子节点包含所有数据，并以有序的双向链表存储，这样可以提高增删效率，且范围查询效率更高，对于 B-Tree 只能进行中序遍历
对于 B+Tree，查询效率更加稳定，因为 B+Tree 的每次查询过程中，都需要遍历从根节点到叶子节点的某条路径，所有数据的查询路径长度相同，所以每一次的查询效率相同

Hash 索引

与 HashMap 原理相似，只支持对等比较（=、in），不支持范围查询（between、>、<），无法通过索引完成排序，但查询效率高，通常只需要一次检索（不出现哈希碰撞）

InnoDB 在适当的场景下会自动根据 B+ 索引构建 hash 索引，称为自适应 hash 索引

索引分类

基本类型

主键索引

当表中某一列被设置为主键时，该列会自动创建主键索引，主键索引只能有一个

alter table 表名 add primary key(字段名)

唯一索引

当某一列被设置为 unique 时，会自动为该列创建一个唯一索引

主键创建后一定包含一个唯一索引，唯一性索引并不一定是主键
唯一索引列允许空值，主键索引列不允许空值，
可以创建多个唯一索引

alter table 表名 add unique(字段名)

普通索引

不附加任何限制条件

alter table 表名 add index 索引名(字段名)

按存储形式分

聚簇索引

聚簇索引的叶子节点中存放的就是整张表的行记录数据（数据起始地址），也将聚集索引的叶子节点称为数据页，这个特性决定了数据也是索引的一部分，一张表只能有一个聚簇索引，也一定会存在一个聚簇索引

聚簇索引选取规则

若表存在主键，默认主键索引是聚簇索引
若表不存在主键，使用第一个唯一索引作为聚簇索引
若表不存在主键和唯一索引，InnoDB 会自动生成一个 rowid 作为隐藏的聚簇索引

非聚簇索引

非聚簇索引也称二级索引、辅助索引，非聚簇索引的叶子节点中存放的是对应的主键，而非全部的行记录数据，一张表可以有多个非聚簇索引

非聚簇索引访问数据通常需要二次查找

首先通过非聚簇索引找到主键值，
再通过主键值扫描聚簇索引，找到对应的行记录数据

这一操作称为回表

按字段个数分

单列索引

单列索引中，一个索引包含单个字段，当条件中包含两个索引列时，只会有一个生效

组合索引

组合索引也称联合索引，一个索引包含多个字段，尽量使用联合索引，设计得当以避免回表查询

索引语法

创建索引

create [unique | fulltext] index 索引名 on 表名(字段1, 字段2, ……)

查看索引

show index from 表名

删除索引

drop index 索引名 on 表名

SQL 性能分析

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位秒，默认 10 秒）的所有 SQL 语句

MySQL 慢查询日志默认没有开启，需要在配置文件中进行配置

慢查询日志位置

windows下：C:\ProgramData\MySQL\MySQL Server 5.5\data\localhost-slow.log
linux下：/var/lib/mysql/localhost-slow.log

在实际开发中，可能一些 SQL 的业务逻辑很简单，但执行时间相对较高，但还没有达到慢查询日志的时间阈值，此时该 SQL 实际上性能也较为低下，但没有被记录

查看慢查询日志是否开启

show variables like 'slow_query_log'

查看慢查询时间阈值

show variables like 'long_query_time'

临时开启慢查询日志

set global slow_query_log = on

临时关闭慢查询日志

set global slow_query_log = off

临时设置慢查询时间阈值

set long_query_time = 时间/秒

永久开启慢查询日志和设置时间阈值

slow_query_log=1
long_query_time=时间/秒

Explain

通过 explain 或 desc 可以对一条 select 语句进行解释，查看执行计划，从而分析性能瓶颈

explain/desc select ...

索引使用规则

对于索引失效，都可以根据索引的底层原理进行思考，对于组合索引，比如对 a、b、c 三个字段建立索引，实际上是先按照 a 进行排序，a 相同时按照 b 排序，以此类推，比如最左前缀法则，如果不存在 a 字段的条件，显然无法进行 b 和 c 的比较，因为索引树首先是依据 a 字段建立的

最左前缀法则

当索引建立在多个列上（组合索引），为了查询时索引生效，需要遵循最左前缀法则

最左前缀法则指查询的条件应该从索引的最左列字段开始，并且不跳过索引中的列，若跳过某一列，则索引部分失效（跳过列的后面的字段索引失效）

如 student 表中，为 name、age、address 三个字段建立组合索引，情况如下

索引均生效

select * from student where name = "xiaoming" and age = 3 and address = "china"
select * from student where name = "xiaoming" and age = 3
select * from student where name = "xiaoming"

索引失效

select * from student where age = 3 and address = "china"

字段 name 的索引生效，而字段 address 的索引失效

select * from student where name = "xiaoming" and address = "china"

除此之外，条件的顺序不影响索引的生效，只要相应条件存在即可，若将上述 SQL 改成

select * from student where address = "china" and name = "xiaoming" and age = 3

索引仍然会生效，或是如下 SQL

select * from student where address = "china" and name = "xiaoming"

仍然是字段 name 的索引生效，而字段 address 的索引失效

使用范围查询会使得索引部分失效（该字段后面的字段索引失效）（不一定失效，因为此时索引是非聚簇索引，范围查询只能根据索引查出主键信息，仍然需要回表，如果存储引擎计算出走全表扫描更快，则索引失效，或者说，如果不是 select * ，不需要回表时，仍然走索引）

当一个字段既是联合索引的最左列，又在该字段建立了单列索引，则默认情况下，会选择走单列索引

索引列运算

在索引列上进行运算，索引会失效，如下

select * from student where substring(name, 1, 3) = "asd"

另外，where name like "%asd" 索引失效，而 where name like "asd%" 索引生效

字符串引号

当索引列为一个字符串，做条件判断时，不加引号，索引会失效，如下

select * from student where name = 123

模糊匹配

当使用 like 进行模糊匹配时，尾部模糊匹配，索引不会失效，头部模糊匹配，索引会失效

select * from student where name like "%asd"  # 失效
select * from student where name like "asd%"  # 生效

or 连接条件

使用 or 连接的两个条件，若 or 前面的条件的列有索引，而后面没有索引，索引会失效

如下，name 有索引而 age 没有索引，那么字段 name 的索引也会失效

select * from big where name = 'xiaoming' or age = 3

自动评估

当一个查询经过 MySQL 评估后，使用索引比全表扫描更慢，则不使用索引

人为指定索引

select 字段列表 from 表名 [use|ignore|force] index(索引1, 索引2, ...) [where 条件]

use index()，指定希望 MySQL 去参考的索引，让 MySQL 不再考虑使用其他可用的索引
ignore index()，让 MySQL 忽略一个或多个索引
force index()，强制 MySQL 使用一个索引

覆盖索引

非聚簇索引的叶子节点中存放的是对应的主键，而非全部的行记录数据，如果一个查询语句中，需要的信息能够在非聚簇索引中找到，则不需要再次扫描聚簇索引树（不需要回表），也就是说，这种索引覆盖所有需要查询的字段的值，我们称这种索引为覆盖索引

如下，age 建立了索引，显然此时不需要回表

select id, age from student where age = 3

如果 select 的字段列表中新增一个 name，则需要回表

select id, age, name from student where age = 3

前缀索引

当字段类型为 varchar 或 text 等很长的字符串时，索引会变得很大，浪费磁盘 IO，降低效率

前缀索引指的是将字符串的一部分前缀建立索引，从而大大节约索引空间，提高效率

create index 索引名 on 表名(字段名(前缀长度))

前缀长度可以根据索引的选择性来选择，也就是说，取至少前多少个字符做索引，可以使得索引均不重复，通过如下 SQL 进行计算

select count(distinct substring(address, 1, 前缀长度)) / count(*) from student