MySQL的索引详细介绍(全网最详细！！！)

大家好，欢迎来到IT知识分享网。

1.什么是索引

索引是帮助MySQL高效获取数据的数据结构
MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据,这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。

1.1 索引的数据结构

1.1.1 Hash表

在java中的HashMap，TreeMap是Hash表结构，以键值对的方式存储数据，使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash在等值查询效率高，时间复杂度是O(1),但是，不支持范围快速查找，范围查找时只能通过扫描全表方式

不适合经常需要查找和范围查找的数据库索引使用

1.1.2 二叉查找树

二叉树存储的值是以key-value结构存储

二叉树特点：每个节点最多有2个分叉，左小右大。
可以保证每次查找减少IO次数，但是会出现一直向右下插，或者左下插入，比如，接下来要插入的数据是18，20，22等 这样就非常难受。
这种数据结构不稳定

1.1.3 平衡二叉树

平衡二叉树采用的是二分法的思维，它最主要的特征是树的左右两个子树的层级最多相差1，
它的时间复杂度是O(log2 n)

存在问题：
当插入的数据过多时，树会很高，这样每层节点的数量就会很多。每个节点读取，都对应这一次磁盘的IO操作，这样，查询性能就会很差。

1.1.4 B树

所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

1.1.5 B+树

使用B+树构建索引，B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。
B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点
中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中
；

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中
B+树的特点：
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好
是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储
（关键字）数据的数据层；
4.更适合文件索引系统；

2.索引的优缺点

优点：

• 提高数据的检索效率，降低数据库的IO成本
• 通过索引对数据进行排序，降低了数据排序的成本，降低了CPU的消耗
• 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
• 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多

缺点：

• 索引会占据磁盘的空间
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

3.索引的使用场景

哪些情况需要创建索引：
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引(where后面的语句)
3.查询中与其他表关联的字段，外键关系建立索引
4.多字段查询下倾向创建组合索引
5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6.查询中统计或者分组字段

哪些情况不需要创建索引：
1.表记录太少
2.经常增删改的表
3.where条件里用不到的字段不建立索引

4.索引的分类

4.1 主键索引

• 表中的列设定为主键后，数据库会自动建立主键索引，索引列中的值必须是唯一的，不允许有空值
• 单独创建和删除主键索引的语法
• 创建：alter table 表名 add primary key(字段)
• 删除：alter table 表名 drop primary key

CREATE TABLE users ( id INT AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY (id) ); 

在这个例子中，id列被定义为主键，MySQL会自动为这个列创建一个主键索引。

4.2 唯一索引

• 表中的列创建了唯一约束时，数据库会自动建立唯一索引，索引列中的值必须是唯一的，但是允许为空值。
• 单独创建和删除唯一索引的语法：
• 创建：alter table 表名 add unique 索引名(字段)或
  create unique index 索引名 on 表名(字段)
  
• 删除：drop index 索引名 on 表名

CREATE TABLE users ( id INT AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY (id), UNIQUE INDEX `unique_email` (email) ); 

在这里，email列有一个唯一索引，确保每个电子邮件地址在表中是唯一的。

4.3 单值索引(单列索引)

• 创建：alter table 表名 add index 索引名(字段)
  或 create index 索引名 on 表名(字段)
  
• 删除：drop index 索引名 on 表名

4.4 复合索引(组合索引)

• 创建：create index 索引名 on 表名(字段1，字段2)
  或 alter table 表名 add index 索引名(字段1，字段2)
  
• 删除：drop index 索引名 on 表名

CREATE INDEX idx_name_date ON events (name, date); 

这个例子创建了一个组合索引，包含events表的name和date两个列，可以加速同时涉及这两个字段的查询。

4.5 普通索引

最基本的索引类型，没有唯一性的限制，主要用于提高查询性能。一个表可以有多个普通索引。

CREATE INDEX idx_username ON users (username); 

这个例子为users表的username列创建了一个普通索引，用来加速基于username的查询

4.6 全文索引

专门用于全文搜索的索引。只有在MyISAM和InnoDB（从MySQL 5.6版本开始支持）存储引擎的CHAR、VARCHAR或TEXT类型列上才能创建全文索引。

CREATE FULLTEXT INDEX ft_idx_content ON articles (content); 

在这个例子中，articles表的content列上创建了一个全文索引，用于全文搜索。

4.7 空间索引

用于空间数据类型的索引，如MySQL的地理数据类型GEOMETRY。这种索引类型主要用于空间数据查询，只有MyISAM存储引擎支持空间索引。

CREATE SPATIAL INDEX sp_idx_location ON parks (location); 

在这里，parks表的location列是一个地理数据类型，创建了一个空间索引，用于地理位置查询。

4.8 前缀索引

对于文本类的长字段，可以创建前缀索引来提高索引效率，只索引字段的前面一部分字符。

CREATE INDEX idx_text_prefix ON articles (content(100)); 

在这个例子中，articles表的content列创建了一个前缀索引，只索引了内容的前100个字符。

4.9 覆盖索引

SELECT username, email FROM users WHERE username = 'john_doe'; 

如果有一个索引：

CREATE INDEX idx_username_email ON users (username, email); 

这个索引就是一个覆盖索引，因为它包含了查询所需的所有字段username和email（无论是返回的数据还是过滤个条件都是索引字段），查询可以直接使用索引来获取数据，不需要访问表的其他部分。

5.索引测试

create table person( pid int (11) auto_increment , pname varchar(50) , psex varchar(10), page int(11), sal decimal(7,2), primary key(pid) ); 

6.性能分析

6.1 Mysql常见瓶颈

6.2 Explain

6.3 explain重要字段

6.3.1 id

先从t1表里去，再去t2表里取，最后再去t3表里取

id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，则先被执行。

id相同和不同都存在时，id相同的可以理解为一组，从上到下顺序执行，所有组中，id值越大，优先级越高越先执行
derived是s3能够临时表里查出的数据

6.3.2 select_type

PRIMARY：查询中若包含任何复杂的子部分，最外查询则被标记为Primary
DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，Mysql会递归执行这些子查询，把结果放在临时表里。
最外面的查询会被标记为primary，子查询被标记为derived

SUBQUERY：在select或where列表中包含了子查询。

6.3.3 type

All：将遍历全表，以找到匹配的行

System：表里只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个可以忽略不计。

Const：表示通过索引一次就找到了，const用于比较primary key(主键索引) 或者unique(唯一索引)。因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能把该查询转换成一个常量

eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描(常用在连表查询)

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件得行，所以他应该属于查找和扫描得混合体。

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。(使用索引进行范围查询)

index：index和all的区别在于index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小，也就是说虽然all和index都是读全表，但是all读的是存数据的表，index读取的是索引表，all从硬盘中读取数据，index 是从索引中读取的

6.3.4 possible_key

6.3.5 key

索引失效

key_len:

使用索引的字节数，数越大 代表使用索引越充分

6.3.6 ref

6.3.7 rows

用索引查询

6.3.8 extra

Using temporary：MySQL在对查询结果排序时使用临时表，常见于排序order by 和分组查询group by

Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错

Using where：表示使用了where过滤

7.查询优化

7.1 索引失效

1.合最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
正常符合法则时：

不符合时：

is not null 不能使用索引

通配符在后面：索引不失效

建议：
1.对于单值索引，尽量选择针对当前查询字段过滤性更好的索引。
2.对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。
3.对于组合索引，尽量选择能够包含在当前查询中where子句中更多字段的索引。
4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

7.2 排序优化

7.3 优化案例

7.3.1 单表查询优化

优化前：

优化：(建索引，发现comment是范围查询，导致views索引失效)

把comment踢出索引，优化成功

7.3.2 关联查询优化

内连接时，mysql.会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。
优化前：(被驱动表是book)

7.3.3 分组优化

优化后：

8.慢查询日志

8.1 简介

MysQL 的慢查询日志是MySQL提供的一种日志记录，他用来记录在MysQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些sQL超出了我们最大忍耐时间值。

8.2 使用

show variables like '%slow_query_log%' 

开启日志：

set global slow_query_log =1 

设置时间：

set global long_query_log =1 

查看时间：

show variables like 'long_query_time%' 

查看超时的sql记录日志：Mysql的数据文件夹下：

注意:非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来—定的性能影响。