Mysql之索引

Mysql

Mysql之索引

一句话：心中有B树，索引就不难！

一、索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引（二级索引/辅助索引）。

按照作用字段个数进行划分，分成单列索引和联合索引（组合索引）。

1、聚簇索引

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表 。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表 。
- 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。
B+树的 叶子节点 存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新

2、非聚簇索引

与聚簇索引索引基本类似，但是叶子节点存储的为主键id，所以需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据（回表操作）

3、全文索引

索引使用FULLTEXT修饰的就是全文索引

创建了一个给title和body字段添加全文索引的表

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE = INNODB ;

全文索引用match+against方式查询

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

注意点
1. 使用全文索引前，搞清楚版本支持情况；
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

二、索引新特性（8.0）

1、降序索引

在MySQL 5.7版本中虽然设置了索引为降序，但是索引仍然是默认的升序，8.0中设置了降序索引，B+Tree在构建索引过程中便使用到倒序构建

注意点
设置了降序的索引，升序是无法使用的

2、隐藏索引

① 简介

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持 隐藏索引（invisible indexes）只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引，测试对业务影响不大的情况下，再将索引删除。

② 创建隐藏索引

只需要在创建索引语句后面加上INVISIBLE或者VISIBLE即可将索引设置可见索引或者隐藏索引

#在建表语句上直接添加
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
); 

#使用CREATE INDEX...ON... 方式添加
CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;

#使用ALTER TABLE...ADD... 方式添加
ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;

#切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; 
 #切换成非隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE;

③ 使隐藏索引对查询优化器可见

隐藏索引默认对查询优化器不可见

# 查询优化器对于可见索引的状态
select @@optimizer_switch \G；
============
use_invisible_indexes=off
===========

#设置优化器对可见索引的状态
set session optimizer_switch="use_invisible_indexes=on";
============
use_invisible_indexes=on
===========

三、索引的创建原则

1、字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2、WHERE 查询条件的字段

SELECT、UPDATE 、DELETE后面的WHERE查询条件使用索引可以大大提高查询效率

如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

3、经常GROUP BY和ORDER BY的列

对于经常GROUP BY和ORDER BY的列应当使用索引

可以对GROUP BY和ORDER BY的字段使用组合索引

4、DISTINCT 字段需要创建索引

5、多表 JOIN 连接操作时创建索引需谨慎

连接表的数量尽量不要超过 3 张
对 WHERE 条件创建索引
对用于连接的字段创建索引
连接字段在多张表中的类型必须一致，否则会引起类型隐式转换，引起索引失效

6、使用字符串前缀创建索引

如果一个字段的长度较长，在构建索引数时就会引起该索引占用较大磁盘，可以使用字符串前缀创建索引

Alibaba《Java开发手册》
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

可以使用下方公式

count(distinct left(列名, 索引长度))/count(*)

示例

select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

7、区分度高(散列性高)的列适合作为索引

例如性别字段就不适合创建索引，大于33%就适合建立索引

8、使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

9、数据量小的表最好不要使用索引

在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的，因为创建索引带来的效果不明显，还需要额外维护索引结构。

10、避免对经常更新的表创建过多的索引

索引可以提高查询效率，但是update操作，需要索引重建

11、删除不再使用或者很少使用的索引

可以使用如下命令查看哪些索引不在使用

四、索引优化

1、最左匹配原则

注意：联合索引（a，b，c），a，ab，ac，abc都可以走索引

a：走a索引
ab：走ab索引
ac：走a索引
abc：走abc索引

示例

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

#以下情况都可以走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30；
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId = '123'；
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

2、计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 
EXPLAIN SELECT id, stuno, NAME FROM student WHERE SUBSTRING(NAME, 1,3)='abc';

注意：该种情况可以走索引
EXPLAIN SELECT SQL_NO_CACHE name FROM student WHERE student.name LIKE '%abc';
原因使用了覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引

3、范围条件引起后面的列索引失效

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

虽然使用到了idx_age_classId_name 索引，但是实际只使用了age和classId组合索引，因为存在>的存在，name字段就无法使用索引了

改进方案：将范围条件写在最后面

CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

4、不等于(!= 或者<>)索引失效

CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

注意：索引覆盖的情况下，是可以走索引的

EXPLAIN SELECT SQL_NO_CACHE name FROM student WHERE student.name <> 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE name FROM student WHERE student.name != 'abc' ;

5、is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

6、OR 前后存在非索引的列，索引失效

CREATE INDEX idx_age ON student(age);

# age有索引，classid 没有索引，会引起索引失效,classid 加上索引就可以了
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

7、order by时不limit，索引失效

CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效，因为优化器发现需要将整个表的数据都查询出来，所以直接全部查出来，在内存中排序的
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

8、order by时顺序错误，索引失效

CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

#顺序不同不走索引
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

注意：该种情况下可以走索引：索引可以倒序查询
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

9、EXISTS 和 IN 的区分

遵循小表驱动大表

# IN 适合A表大，B表小
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

# EXISTS 适合A表小，B表大
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

10、**COUNT(1)与COUNT(*)与COUNT(xxx)效率**

COUNT(1)与COUNT(*)没有区别
MylSAM存储引擎，有一个meta信息存储了row_count值，效率较快，InnoDB 存储引擎为计算出来的
在InnoDB引擎中
- 如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为二级索引对应的B+Tree数更小，查询更快，
- 对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行
  统计。
- 如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

五、索引下推ICP

1、概念

索引下推(ICP)，又叫索引条件下推（lndex Condition Pushdown），是一种在存储引擎层使用索引过滤数据的优化方式。

ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

示例：

CREATE  INDEX idx_age_name_classid ON student (age,name,classid);

EXPLAIN  SELECT * FROM student WHERE age = 15 AND name like '%张三';

2、开启/关闭ICP

默认是开启状态的，一般无需改动

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

#打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on' ;

3、ICP的使用条件

只能用于二级索引(secondary index)
explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
ICP可以用于MyISAM和InnnoDB存储引擎
MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
当SQL使用覆盖索引时，不支持ICP优化方法。

如果觉得文章对你有用，请随意赞赏

mysql

Mysql之索引

http://blog.peakliu.top/archives/1690815486445

作者

知识星光

发布于

2023-07-31

更新于

2023-08-02

许可协议

CC BY 4.0

Mysql之索引

Mysql之索引

一、索引的分类

1、聚簇索引

2、非聚簇索引

3、全文索引

二、索引新特性（8.0）

1、降序索引

2、隐藏索引

① 简介

② 创建隐藏索引

③ 使隐藏索引对查询优化器可见

三、索引的创建原则

1、字段的数值有唯一性的限制

2、WHERE 查询条件的字段

3、经常GROUP BY和ORDER BY的列

4、DISTINCT 字段需要创建索引

5、多表 JOIN 连接操作时创建索引需谨慎

6、使用字符串前缀创建索引

7、区分度高(散列性高)的列适合作为索引

8、使用最频繁的列放到联合索引的左侧

9、数据量小的表最好不要使用索引

10、避免对经常更新的表创建过多的索引

11、删除不再使用或者很少使用的索引

四、索引优化

1、最左匹配原则

2、计算、函数、类型转换(自动或手动)导致索引失效

3、范围条件引起后面的列索引失效

4、不等于(!= 或者<>)索引失效

5、is null可以使用索引，is not null无法使用索引

6、OR 前后存在非索引的列，索引失效

7、order by时不limit，索引失效

8、order by时顺序错误，索引失效

9、EXISTS 和 IN 的区分

10、COUNT(1)与COUNT(*)与COUNT(xxx)效率

五、索引下推ICP

1、概念

2、开启/关闭ICP

3、ICP的使用条件

作者

发布于

更新于

许可协议

评论

10、**COUNT(1)与COUNT(*)与COUNT(xxx)效率**