Mysql 索引

索引

0.引用

数据库除了存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 当我们在查找数据的时候，就可以在这些数据结构上实现高级查找算法，快速查找到我们想要的数据，这种数据结构就是索引。

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度， 如对表进行INSERT、UPDATE、DELETE时，效率降低。

针对于这张表的age字段建立了索引，假设索引的数据结构就是二叉树（实际并不是，而是一种比二叉树更高效的数据结构），那么也就意味着，会对age这个字段建立一个二叉树的索引结构。此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

2.索引结构

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	支持	支持	不支持

特别说明：MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

3.分类

在MySQL数据库中，将索引的具体类型主要分为以下几类：

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个	index/KEY
全文索引	全文索引查找的是文本中的关键词，而不是比 较索引中的值 类型CHAR、VARCHAR或TEXT类型	可以有多个	FULLTEXT

空间索引

MySQL 的空间索引通常支持 GEOMETRY、POINT、LINESTRING、POLYGON 等空间数据类型。其他基本数据类型（如整数、字符串）不能创建空间索引。

不支持 NULL 值：在空间索引中，字段不能包含 NULL 值。如果存在 NULL 值，则创建空间索引时会失败。

而在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级（非聚集）索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级（非聚集）索引的叶子节点下挂的是该字段值对应的主键值。

当我们执行上述的SQL语句：

由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最 终找到10对应的行row。
最终拿到这一行的数据，直接返回即可。
这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

由于存在回表查询，我们通过聚集索引查询值的方式要比通过二级索引查询值的方式快很多。因为走聚集索引，可以直接返回数据。 而走二级索引，需要先获取id值，然后再查询聚集索引获取值要慢许多。
————————————————

4.创建索引

索引创建要遵从最左前缀法则

CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名 ON table_name (字段1,字段2,... );

-- 为用户表的姓名name字段创建名为idx_user_name的唯一索引
CREATE UNIQUE INDEX idx_user_name ON user(name);

-- 为用户表的手机号phone字段创建名为idx_user_phone的普通索引
CREATE INDEX idx_user_phone ON user(phone);

-- 为用户表的profession,age,status字段创建名为idx_user_pro_age_stae的联合索引-不回表查询
CREATE INDEX idx_user_pro_age_sta ON user(profession,age,status);

最左原则例子

create index 
query_index_0 
on 
jzo2o-orders-1.orders_0
(orders_status asc, user_id asc, display asc, sort_by desc); 

explain
SELECT id
FROM orders_0
WHERE (orders_status = 0 AND user_id = 1716346406098296832 AND display = 1 and sort_by<1698924600022)
ORDER BY sort_by DESC
LIMIT 10

5.索引失效情况

1.最左前缀法则

1.如果索引了多列（联合索引），要遵守最左前缀法则。

注：在where条件中索引最左列存在即可，在order by多字段排序中索引最左列必须为排序条件最左列。

2.范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

explain select * from user where profession = '软件工程' and age > 30 and status = '0';

根据长度**key_len我们可以知道：联合索引生效了，但是只有profession与age的索引生效了，status字段的索引没有生效。**

值得注意的是：当范围查询使用>= 或 <= 时，则不会产生影响。 因此，在业务允许的情况下，进行范围查询时，为了避免索引失效，我们可以使用类似于 >= 或 <= 这类的范围查询，代替使用> 或 <。

3.索引列运算

当我们在索引列上进行运算操作， 索引也将失效。

explain  select  *  from  user  where  substring(phone,10,2) = '15'

4.字符串不加引号

当我们对添加索引的字符串类型字段进行操作时，如果字符串不加引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

• 例如 phone 为 varchar类型字段

5.模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

• 我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效

6.or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

• 例如我们先删除age字段上的联合索引，再进行or连接查询

我们发现当or连接的条件，左右两侧字段都有索引时，索引才会生效~

7.数据分布影响-索引使用情况

如果MySQL评估使用索引比全表扫描更慢，则不使用索引。

• 例如，下面使用相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样

这是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

6.索引使用优化

1.SQL提示

当我们的字段存在多个索引时，MySQL会进行评估自动选择一个索引进行使用。我们也可以借助于SQL提示指定MySQL使用哪个索引。

• use index ：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估，即或许不会遵从建议）

explain select * from 表名 use index(索引名) where 查询条件....;

• force index ： 强制使用索引

• ignore index ：忽略指定的索引

2.覆盖索引 聚集索引 介绍

在需求允许的情况下，尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *的使用。

创建索引-防止回表查询 执行器返回

Extra	含义
Using where; Using Index	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需 要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

聚集索引查询

覆盖索引

• 根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索 引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。

回表查询

多查询一个gender字段，由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相 对较差一点。

3.前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候这些字段值会非常大（例如一段很长的文本），如果直接对此建立索引，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

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

有时候我们难以确定应该创建多长的索引，这时可以根据索引的选择性来决定。选择性是指不重复的索引值（基数）和数据表的记录总数的比值， 索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

-- 可以直接计算比值
select  count(distinct 字段名) / count(*) from 表名;

-- 也可以使用函数截取部分数据进行计算
 select  count(distinct substring(字段名,开始位置,结束位置)) / count(*) from 表名;

前缀索引的查询流程

• 对于类似身份证号、微信小程序openId这些，其长度比较长，但是其前几位区分度并不大，很显然不能直接创建前缀索引，于此，我们可以在数据库中对其倒序存储，再计算索引选择性创建合适长度的索引

select openId from user where openId=reverse('xxxxxx')

7.索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引

。
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

在复合业务场景的情况下，尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询