MySQL索引优化（超详细）

橘子味汽水ღ

已于 2023-06-08 22:02:16 修改

阅读量5.5k

点赞数 15

分类专栏： MySQL 文章标签： mysql 数据结构 b树性能优化数据库

于 2023-05-10 17:51:19 首次发布

本文链接：https://blog.csdn.net/weixin_55816781/article/details/130606468

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Mysql索引优化

1 索引介绍

1.1 什么时MySQL的索引

MySQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。
MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
简单理解为“排好序的可以快速查找数据的数据结构”。

1.2 索引数据结构

1.⼆叉树查找

我们都学过⼆分查找，如果我们⼀开始在插⼊数据就将数据按顺序排列，那查找效率就会⼤⼤提⾼。将数据按照顺序⼆叉树排列好每次查询就可以使⽤⼆分法即从根节点出发，查询效率就增加了

但是如果删除了⼀些数据⽐如0，6，1，3，4删除了那么右边的数据就有变成了线性的了，查询效率就会有所浪费

2 平衡二叉树

如果有这样⼀棵树左右⼦树⾼度差不超过 1，⽽且也满⾜顺序排列就可以继续⾼效下去，然后平衡⼆叉树就出现了。每当删除⼀个节点都应该发⽣相应的节点位置转换反转保证⼆叉树的平衡。

说了这么多我们⽆⾮就是想让查找效率变高，从线性的 O（n）到 O (logn），好像还有疑问为什么不⽤更⾼效的 Hash 地址法来查找呢？这样可以降到 O（1），答案是在查询过程中我们不仅有等值查询还有范围查询模糊查询，使⽤ Hash 存储其位置的不确定性，如果要查询范围我们就要遍历全表。⽽⼆叉树只要遍历左右节点。

3.B树

由于平衡⼆叉树的⼆叉特点，它每⼀个节点最多只有 2 个叉，假设有 100000 个数据，那么树的深度将会变得特别深，⽽每次⽐较就是拿⽐较的树和节点上的数在内存⽐较，所以每⽐较⼀次就是⼀次 IO 操作就下降⼀层，层数越多时间就越久。所以B树就来了，他是多叉平衡树，每个节点维护了多个⽐较范围（即⼦节点）

这样就降低了⾼度，每个圆圈可以理解为⼀⻚，16kb的数据. 所以他的每个节点都存储数据就会造成每个结点的分叉数减少，⽽且会造成先靠近根节点的先查到，靠近叶⼦结点的后查到。同样范围查找也会出现多次回退到⽗节点在到另⼀个兄弟节点的低效率问题。

4.B+树

我们改造⼀下 B树为 B + 树，每个⾮叶⼦节点只存索引，真实数据都存在叶⼦节点，这样⾮叶⼦节点的空间单个数据空间减少数量即分叉就可以增⼤。每次查询⽆论如何必须遍历到叶⼦节点才会结束，这样深度⼜减少了，同时我们把每个叶⼦结点⽤双向链表连接起来，范围查询就更快。

1.3 索引优势

提高数据检索的效率，降低数据库的Io成本。
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

1.4 索引劣势

索引实际上也是一张表，保存了主键和索引的字段，并且指向实体表的记录，所以索引也索引实际上也是一张表，保存了主键和索引的字段，并且指向实体表的记录，所以索引也是需要占用空间的。在索引大大提高查询速度的同时，却会降低表的更新速度，在对表进行数据增删改的同时，MySQL 不仅要更新数据，还需要保存一下索引文件。每次更新添加了的索引列的字段，都会去调整因为更新带来的减值变化后的索引的信息。

1.5 索引使用场景

哪些情况需要创建索引:

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引(where后面的语句)

3.查询中与其它表关联的字段，外键关系建立索引

4.多字段查询下倾向创建组合索引

5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6.查询中统计或者分组字段

那些情况不推荐建立索引：

1.表记录太少

2.经常增删改的表

3.where条件里用不到的字段

1.6 索引分类

1.6.1 主键索引

1.表中的列设定为主键后，数据库会自动建立主键索引。

2.单独创建和删除主键索引语法:

创建主键索引语法:! alter table表名add primary key (字段);

删除主键索引语法: alter table表名drop primary key;

1.6.2 唯一索引

1．表中的列创建了唯一约束时，数据库会自动建立唯一索引。

2．单独创建和删除唯一索引语法:

创建唯一索引语法: alter table表名add unique索引名(字段);

或create unique index索引名on表名(字段);

删除唯一索引语法: drop index索引名on表名;

1.6.3 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引。

1．建表时可随表一起建立单值索引

2．单独创建和删除单值索引:

创建单值索引: alter table 表名 add index索引名(字段);

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

删除单值索引: drop index索引名on表名;

1.6.4 复合索引

即一个索引包含多个列

1．建表时可随表一起建立复合索引

2．单独创建和删除复合索引:

创建复合索引: create index 索引名 on 表名(字段1,字段2);

或alter table表名 add index索引名(字段,字段2);

删除复合索引: drop index索引名 on 表名;

案例

-- 创建customer表并创建四种索引
create table customer(
	id int(10) auto_increment,
	customer_no varchar(20),
	customer_name varchar(20),
	primary key(id),
	unique idex_customer_no (customer_no),
	key idx_customer_name (customer_name),
	key indx_customer_no_name (customer_no,customer_name)
);

单独创建

-- 创建主键索引
alter table customer add primary key(id);
-- 删除主键索引
alter table customer drop primary key;

-- 创建唯一索引
alter table table customer add unique idx_customer_no (customer_no);
-- 删除唯一索引
drop index idx_customer_no on customer;

-- 创建单值索引
alter table customer add index idx_customer_name(customer_name);
-- 删除单值索引
drop index idx_customer_name on customer;

-- 创建复合索引
alter table customer add index idx_customer_no_name(customer_no,customer_name);
-- 删除复合索引
drop index idx_customer_no_name on customer;

1.7 索引测试

-- 创建存储过程
create procedure insert_person( in max_num int(10))
begin
	declare i int default 0;
	set autocommit = 0;
	repeat
	set i = i +1;
	insert into person (PID,PNAVE,PSEX,PAGE ,SAL) values (i,concat('test ' ,floor(rand()*1000)),I(RANID()0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));
	until i = max_num
	end repeat;
	commit;
end;

-- 调用存储过程（30000000）即插入三百万条数据
call insert_person(30000000);


-- 不使用索引，根据pName进行查询（10s+)
select * from person where pname = 'test222';

-- 给pname建立索引
alter table person add index idx_pname(PNAME);

-- 再次查询(0.01s+)
select * from person where pname = 'test222';

2 性能分析

2.1 MySQL常见瓶颈

SQL中对大量数据进行比较，关联，排序，分组时CPU的瓶颈。

实例内存满足不了缓存数据或排序等需要,导致产生大量的物理I0。查询数据时扫描过多数据，导致查询效率低。

2.2 Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的,可以用来分析查询语句或是表的结构的性能瓶颈。

其作用:

表的读取顺序
那些索引能够使用
数据读取操作的操作类型
那些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

EXPLAIN关键字使用起来比较简单：explain + SQL语句

– 创建四张测试表

CREATE TABLE t1(
	id INT(10) AUTO_INCREMENT,
	content VARCHAR(100),
	PRIMARY KEY(id)
);

CREATE TABLE t2(
	id INT(10) AUTO_INCREMENT,
	content VARCHAR(100),
	PRIMARY KEY(id)
);

CREATE TABLE t3(
	id INT(10) AUTO_INCREMENT,
	content VARCHAR(100),
	PRIMARY KEY(id)
);

CREATE TABLE t4(
	id INT(10) AUTO_INCREMENT,
	content VARCHAR(100),
	PRIMARY KEY(id)
);

-- 每张表中添加一条数据(拼接一个以t{{num}}__开头的1-1000的字符串)
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));

INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));

INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));

INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

2.3 Explain 重要字段

2.3.1 id

id相同时，执行顺序是从上往下

explain select * from t1,t2,t3 where t1.id = t2.id = t3.id;

id不同时，id的序号会递增，id的值越大优先级越高，则先被执行

explain select t1.id from t1 where t1.id in
(select t2.id from t2 where t2.id in
(select t3.id from t3 where t3.id = 1)
);

id相同和不同都存在时，id相同的可以理解为一组，从上往下执行，所有组中，id值越大，优先级越高
```
explain select t2.* from t2,(select * from t3) as s3 where s3.id = t2.id; 
```

2.3.2 select_type

SIMPLE :简单的select查询，查询中不包含子查询或者UNION
```
explain select * from t1;
```
PRIMARY：查询中若包含任何复杂的子部分，最外层的查询则会被标记为PRIMARY
```
explain select *from (select t1.content from t1) as s1;
```
DERIVED：在FROM列表中包含的子查询表被标记为DERIVED，MySQL会递归执行这些子查询，把结果放到临时表中
```
explain select *from (select t1.content from t1) as s1;
```
SUBQUERY：在SELECT或者WHERE列表中包含了子查询

2.3.3 table：查询⽤到的表名

2.3.4 Type

★连接的类型，常⻅的类型有（性能从好到差）

system：存储引擎能够直接知道表的⾏数（如 MyISAM）并且只有⼀⾏数据
```
explain select * from (select t1.id from t1 where id = 1) t;
```
const：通过索引⼀次找到，通常在⽤主键或唯⼀索引时出现
```
 explain select id from t1 where id = 1;
```

eq_ref：⽤主键或唯⼀索引字段作为连接表条件

explain select t1.*,t2.*
from t1 
join t2
on t1.id = t2.id;

ref：⽤普通索引的字段作为连接表条件

-- 创建普通索引
alter table t1 add index idx_t1_content(content);
-- 进行查询
explain select *from t1 where t1.content = 'abc';

range：对索引列进⾏范围查询

explain select * from t2 where t2.id > 1;

index：利⽤索引扫描全表

-- 因为我们前面给t1的content字段建立了普通索引
-- 加上主键id为主键索引，所有为查询的所有字段都有索引
explain select * from t1;

all：全表扫描

explain select * from t1 where content = '132';

2.3.5 possible_keys

显示可能应用在这张表的索引，一个或者多个，查询涉及到的字段上如果存在索引，则该索引将会被列出来，但不一定会用到，因为存在索引失效的情况

-- 索引失效了，所以key没有使用到主键索引
explain select * from t2 where id is not null;

;

2.3.6 key

★查询中实际使用的索引，如果为null，则表示没有使用索引

2.3.7 ref

当使⽤索引等值查询时，与索引作⽐较的列或常量

-- t2，t3二个表的id为主键索引，查询t3的数据时用到t2的id字段
-- 所以ref为 db_test.t2.id
explain select t2.*,t3.* from t2,t3 where t2.id = t3.id;

2.3.8 row

★预计扫描的⾏数，值越⼤，查询性能越差

-- 前面的插入的三百万数据的person表
explain select * from person where sal = 111;

2.3.9 Extra

★：有关查询执⾏的其他信息

using index：使⽤覆盖索引，不⽤回表查询
using where：使⽤ where ⼦句来过滤结果集
using temporary：使⽤到临时表来存储中间结果，可能会导致性能问题
using filesort：查询需要进⾏⽂件排序操作，可能会导致性能问题
using index condition：先根据能⽤索引的条件获取符合条件的数据⾏，然后在根据其他条件去过滤数剧

3 查询优化

3.1 索引失效

-- 建立一张学生表
drop table if exists students;
CREATE TABLE students (
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
	sname VARCHAR(24) COMMENT '学生姓名',
	age INT COMMENT '年龄',
	score INT COMMENT '分数',
	time TIMESTAMP COMMENT '入学时间'
 );

-- 插入四条数据
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小黑',23,70,now());

-- 创建sname，age，score的组合索引
alter table students add index idx_sname_age_score(sname,age,score);

-- 索引成功(查询索引字段越多，用到的索引越充分，查询越快）
explain select * from students where sname = '小明' and age = 22 and score = 100;
explain select * from students where sname = '小明' and age = 22;
explain select * from students where sname = '小明';

最佳左前缀匹配原则：如果索引了多例，要遵循最左前缀原则，查询从最左前列开始并且不跳过索引中的列

-- 索引失败
explain select * from students where age = 22 and score = 100;
explain select * from students where age = 22;

-- 以下查询都只用到了sname索引
explain select * from students where sname = '小明';
explain select * from students where sname = '小明' and score = 22;

不在索引列上做任何计算、函数操作，会导致索引失效从而转向全表扫描

-- 函数操作
explain select * from students where left(sname,2) = '小明';

-- 不做任何计算
explain select * from students where sname;

存储引擎中不能使用索引中范围条件右边的列

-- 二个查询都只使用到了snam和age索引
explain select * from students where sname = '小明' and age > 22 and score = 100;
explain select * from students where sname = '小明' and age = 22;

MySQL在使用不等于和is not null无法使用索引

-- 使用了 !=
explain select * from students where sname != '小明';
-- 使用了 is not null;
explain select * from students where sname is not null;

like以通配符开头会使索引失效导致全表扫描

-- 索引成功，不是以通配符开头
explain select * from students where sname like '小%';
-- 索引失效，以通配符开头
explain select * from students where sname like '%明';

字符串字段不加单（双）引号

-- 字符串字段sname没有加单（双）引号
explain select * from students where sname =123;

使用or连接索引失效

-- 使用or连接索引失效
explain select * from students where sname = '小明' or score = 22;

建议:

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

3.2 单表优化

-- 创建文章表
CREATE TABLE IF NOT EXISTS article (
id INT(10) PRIMARY KEY AUTO_INCREMENT,author_id INT(10)NOT NULL,
category_id INT(10) NOT NULL,views INT (10)NOT NULL,
comments INT(10) NOT NULL,
title VARBINARY(255) NOT NULL,content TEXT NOT NULL
);

-- 插入测试数据
INSERT INTO article(author_id,category_id,views,comments,title,content) VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

案例

1.查询category_id为1的，且comments大于1的情况下，views最多的id和author_id的信息

-- 普通查询，没有使用到索引
explian select id,author_id
from article 
where category_id = 1 and commnts >1 order by view desc limit 1;

2.建立索引

-- 建立文章id，文章和内容的联合索引
alter table article add index idx_ccv(category_id,comments,views );

3.再次查询

-- 以为comments使用了范围查询，所以view没有使用索引查询
explian select id,author_id
from article 
where category_id = 1 and commnts >1 order by view desc limit 1;

4.再次优化

-- 删除组合索引中的comment字段
drop index idx_ccv on article;
alter table article add index idx_cv(category_id,views);

5.再次测试

explian select id,author_id
from article 
where category_id = 1 and commnts >1 order by view desc limit 1;

3.3 关联查询优化

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

-- 创建class表和book表
CREATE TABLE IF NOT EXISTS class (
id INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS book (
bookid INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (bookid)
);

-- 给class和book分别插入十条随机数据
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

1.连表查询

-- card没有建立索引，二张表走的都是全表查询
explain select *
from class
left join book
on class.card = book.card;