MYSQL索引优化

谁的青春不迷茫g

已于 2023-01-30 10:59:30 修改

阅读量230

点赞数 2

分类专栏： mysql 笔记文章标签： mysql 数据库 Powered by 金山文档

于 2023-01-30 10:44:33 首次发布

本文链接：https://blog.csdn.net/weixin_46448867/article/details/128796943

版权

笔记同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

1 索引介绍

1.1什么是MySQL的索引

MySQL官方对于索引的定义：索引是帮助MySQL高效获取数据的数据结构。

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

简单理解为“排好序的可以快速查找数据的数据结构”。

2 索引数据结构

MySQL中常用的的索引数据结构有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存储引擎)等等。

2.1索引优势

提高数据检索的效率，降低数据库的IO成本。

通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

2.2索引劣势

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

2.3索引使用场景

哪些情况需要创建索引：

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

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

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

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

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

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

哪些情况不推荐建立索引:

1.表记录太少

2.经常增删改的表

3.Where条件里用不到的字段不建立索引

2.4 索引分类

2.4.1主键索引

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

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

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

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

2.4.2唯一索引

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

单独创建和删除唯一索引语法：

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

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

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

2.4.3单值索引

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

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

单独创建和删除单值索引：

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

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

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

2.4.4 复合索引

即一个索引包含多个列：

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

单独创建和删除复合索引：

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

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

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

3 性能分析

3.1 MySQL常见瓶颈

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

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

3.2 Explain

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

表的读取顺序

哪些索引可以使用

数据读取操作的操作类型

那些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

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

3.3 Explain重要字段名

3.3.1 id

select查询的序列号，表示查询中执行select子句或操作表的顺序。

id相同时，执行顺序由上至下。

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

id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

3.3.2 select_type

SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。

PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。

DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY: 在SELECT或WHERE列表中包含了子查询。

3.3.3 table

显示这一行的数据是关于哪张表的。

3.3.4 type

访问类型排序:

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

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

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

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

Range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

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

All：Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般来说，最好保证查询能达到range级别，最好能达到ref。

3.3.5 possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则改索引将会被列出来，但不一定会被查询实际使用上。

3.3.6 key

查询中实际使用的索引，如果为NULL，则没有使用索引

3.3.7 ref

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

3.3.8 rows

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

3.3.9 extra

一些常见的重要的额外信息：

Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。

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

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

Using where：表示使用了where过滤。

4 查询优化

4.1索引失效

创建索引:

alter table students add index idx_sname_age_score(sname,age,score);

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

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

 explain select * from students where left(sname,2) = "小明";

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

explain select * from students where sname="小明" and age > 22 and score = 100;

// 解释: score列的索引会失效

Mysql在使用不等于时无法使用索引会导致全表扫描。

explain select * from students where sname!="小明";

is null可以使用索引，但是isnot null无法使用索引。

explain select * from students where sname is not null;

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

explain select * from students where sname like "%明";

字符串不加单引号索引会失效。

explain select * from students where sname = 123;

使用or连接时索引失效。

explain select * from students where sname="小明" or age = 22;

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

4.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'),
(1, 1, 3, 3, '3', '3');

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

#1.建立索引
alter table article add index idx_ccv(category_id,comments,views);
#2.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;
#3.重新创建索引
drop index idx_ccv on article;
alter table article add index idx_cv(category_id,views);
#4.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;

// 解释: comments最初是索引列中的一员，因为索引范围右边的索引不生效，所以把comments从索引列表中移除

4.3 关联查询优化

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)
);
 
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)));

#1.联表查询
explain select *
from class
left join book
on class.card = book.card;
#2.建立索引
alter table book add index idx_card(card);
#3.测试
explain select *
from class
left join book
on class.card = book.card;

// 连表查询时可以把数据量小的当作主表，因为主表会被全部扫描，这样提高查询效率

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

4.4 排序优化

尽量避免使用Using FileSort方式排序。

order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。

where子句中如果出现索引范围查询会导致order by索引失效。

const: 是代表常量的意思

4.5 分组优化

和排序优化一样

alter table students add index idx_sas(sname,age,score);
explain select count(*),sname 
from students 
where sname="小明" and age > 22
GROUP BY score;

// score 索引失效