MySQL性能优化，索引优化

北海怪兽Monster

已于 2022-07-19 23:29:43 修改

阅读量203

点赞数

分类专栏：数据库文章标签： mysql 性能优化数据库

于 2022-07-18 23:18:36 首次发布

本文链接：https://blog.csdn.net/m0_48639280/article/details/125861635

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

优化需求：

稳定性和业务可持续性，比性能更加重要！！！

sql性能优化：
SQL及索引 > 数据库表结构 > 系统配置 > 硬件

数据库表结构：分库分表，读写分离，存储引擎，表设计
Sql及索引： sql语句，索引使用

explain 获取查询语句的执行计划

数据库层面的优化：

应急调优思路：

针对突然的业务办理卡顿，无法正常的进行业务处理，需要立马解决的场景。
1、 show processlist （查看链接session状态）
2、 explain （分析查询计划），show index from table（分析索引）
3、通过执行计划判断，索引问题（有没有，合不合理）或者语句本身问题
4、 show status like ‘%lock%’ ; 查询锁的状态
5、 SESSION_ID; #杀掉有问题的session

常规调优思路：

针对业务周期性卡顿，不是那么着急的
1、查看 slowlog，分析slowlog，分析出查询慢的语句
2、按照一定优先级，进行一个一个的排查所有慢语句
3、分析 top sql，进行explain 调试，查看语句执行时间
4、调整索引或语句本身

查询优化

1、MySQL查询流程

① 客户端将查询发送到服务器；
② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
③ 服务器解析，预处理。
④ 查询优化器优化查询
⑤ 生成执行计划，执行引擎调用存储引擎API执行查询
⑥服务器将结果发送回客户端。
在这里插入图片描述
查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是
否命中查询缓存中的数据，如果命中缓存直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

语法解析和预处理器
MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。
查询优化器
语法书被校验合法后由优化器转成查询计划，一条语句可以有很多种执行方式，最后返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
查询执行引擎
在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。最常使用的也是比较最多的引擎是MyISAM引擎和InnoDB引擎。mysql5.5开始的默认存储引擎已经变更为innodb了。

SQL调优

sql是我们和数据库交流最重要的部分，所以我们在调优的时候，需要花费的大量时间就在sql调优上面。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

慢查询日志开启

在配置文件my.cnf（Linux）或my.ini（windows）中在[mysqld]一行下面加入两个配置参数
这样我们就可以知道哪些sql执行过程慢，可以进行优化

log‐slow‐queries=/data/mysqldata/slow‐query.log
long_query_time=5

log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；
long_query_time=5中的5表示查询超过五秒才记录；
还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。

慢查询分析

我们可以通过打开log文件查看得知哪些SQL执行效率低下，从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

进入log的存放目录，运行：

[root@mysql_data]# mysqldumpslow slow‐query.log
Reading mysql slow query log fromslow‐query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s)Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;

mysqldumpslow命令

/path/mysqldumpslow ‐s c ‐t 10/database/mysql/slow‐query.log

这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回
的记录数来排序，ac、at、al、ar，表示相应的倒叙
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

例如：

/path/mysqldumpslow ‐s r ‐t 10/database/mysql/slow‐log

得到返回记录集最多的10个查询。

/path/mysqldumpslow ‐s t ‐t 10 ‐g “leftjoin” /database/mysql/slow‐log

得到按照时间排序的前10条里面含有左连接的查询语句。

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

EXPLAIN

EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮助开发人员写出更优化的查询语句。使用方法，在select语句前加上
Explain就可以了：

EXPLAIN SELECT * FROM products

在这里插入图片描述

1) id
SELECT识别符。这是SELECT查询序列号。这个不重要
2) select_type
表示SELECT语句的类型。
1. simple:简单select（不使用union或子查询）。
2. primary:最外面的select。
3. union:union中的第二个或后面的select语句。
4. dependent union:union中的第二个或后面的select语句，取决于外面的查询。
5. union result:union的结果。
6. subquery:子查询中的第一个select。
7. dependent subquery:子查询中的第一个select，取决于外面的查询。
8. derived:导出表的select（from子句的子查询）。
3) table
显示这查询的数据是关于哪张表的。
4) type
区间索引，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别,最好能达到ref。

system：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：数据表最多只有一个匹配行，因为只匹配一行数据，所以很快
eq_ref：mysql手册是这样说的:“对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY”。eq_ref可以用于使用=比较带索引的列。
ref：查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECTprimary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROMsingle_table WHERE some_expr)
range：只检索给定范围的行,使用一个索引来选择行。
index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）
5) possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。
6) key
实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。
7) key_len
最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。
8) ref
显示使用哪个列或常数与key一起从表中选择行。
9) rows
显示MySQL认为它执行查询时必须检查的行数。
10) Extra
执行状态说明，该列包含MySQL解决查询的详细信息Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
sing index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为
index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for groupby表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

索引优化

在经常查询的字段上添加索引，索引和存储数据的物理地址直接联系，所以查询数据很快。但是会占用内存和cpu资源。

通过上面的对比测试可以看出，索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。

对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。

索引的目的在于提高查询效率，大家可以回忆之前学习的全文检索技术。类似使用字典，如果没有目录（索引），那么我们要从字典的第一个字开始查询到最后一个字才能有结果，可能要把字典中所有的字看一遍才能找到要结果，而目录（索引）则能够让我们快速的定位到这个字的位置，从而找到我们要的结果。

索引类型

主键索引 PRIMARY KEY

唯一索引 UNIQUE

普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构

组合索引 INDEX

索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

KEY `num` (`number`,`name`) USING BTREE

组合索引在使用的时候：前面的索引可以单独使用，后面的索引不可以单独使用。（前面后面。就是你声明时候的顺序）
比如单独使用number作为查询条件，索引生效，如果单独使用name作为查询条件，索引不会生效。一起使用不区分先后顺序。

全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

索引的存储结构

B树索引
加快查询数据，适合范围查找。
使用的场景：

全值匹配的查询，例如根据订单号查询 order_sn=‘98764322119900’
联合索引时会遵循最左前缀匹配的原则,即最左优先
匹配列前缀查询，例如：order_sn like ‘9876%’
匹配范围值的查找，例如：order_sn > ‘98764322119900’
只访问索引的查询

哈希索引
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

在进行等值查询的时候，还是很快的，只需要进行一次哈希算法就可以查询到数据，

但是：
Hash索引仅仅只能满足“=”,“IN"查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；(例如组合索引，只能一起使用)
Hash索引在任何时候都不能避免表扫描；（不同的结果有可能hash值是一样的，那么我们只能查出全部hash值相同的，在进行值比对，性能就低下）
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

用的少，面试问的也少
Full-text全文索引
Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE%word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本和之后InnoDB存储引擎开始支持全文索引。
在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

索引使用情况

使用索引的场景

主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向建立组合索引；
用于聚合函数的列可以建立索引，例如使用count(number)时，number列就要建立索引

不适用索引的情况

有大量重复的列不单独建立索引
表记录太少不要建立索引，因为没有太大作用。
不会作为查询的列不要建立索引

北海怪兽Monster

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL性能优化，索引优化

优化需求稳定性和业务可持续性，比性能更加重要！！！sql性能优化SQL及索引>数据库表结构>系统配置>硬件数据库表结构分库分表，读写分离，存储引擎，表设计Sql及索引sql语句，索引使用explain获取查询语句的执行计划。.........
复制链接

扫一扫

专栏目录