MySQL优化

1_优化思路

1_1.应急调优思路

针对突然的业务卡顿,无法进行正常的业务处理!需要马上解决的场景!

1).show processlist(查看链接session状态)

2).explain(分析查询计划),show index from table(分析索引)

3).通过执行计划判断,索引问题(有没有,合不合理)或者语句本身问题

4).show status like '%lock%';(查询锁状态)

5).SESSION_ID;(杀掉有问题的session)

1_2.常规调优思路

针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了.

1).查看slow log,分析slow log,分析出查询慢的语句

2).按照一定优先级,进行一个一个的排查所有慢查询

3).分析top sql ,进行explain调试,查看语句执行时间.

4).调整索引或者语句本身

2_查询优化

2_1.MySQL查询执行流程

MySQL中增删改语句一般不会出现性能问题,所以我们优化的重心就是解决查询的优化问题,所以我们要先了解查询语句的执行流程,才能对症下药.

1).客户端将查询发送到服务器;

2).服务器检查查询缓存,如果有,就从缓存中返回结果,没有就进行下一步

3).服务器解析sql语句,进行预处理

4).查询优化器优化查询

5).生成执行计划,执行引擎调用存储引擎API执行查询

6).服务器将结果发送回客户端

批注 2020-08-09 191107.jpg

2_2.查询优化

2_2_1.慢查询日志

show variables like 'slow_query%'; //查看慢查询日志开启情况

show variables like 'long_query_time'; //查看慢查询日志参数设置情况

批注 2020-08-09 193601.jpg

开启慢查询日志

批注 2020-08-09 193014.jpg

log-slow-queries:指定慢查询日志生成路径

long_query_time:5表示查询超过5秒才记录

慢查询分析工具 mysqldumpslow

常用参数

批注 2020-08-09 193930.jpg

示例

按照时间排的top 5个SQL语句

mysqldumpslow -s t -t 5 /data/mysqldata/slow-query.log

按照时间排序且含有'like'的top 5个SQL语句

mysqldumpslow -s t -t 5 -g "like" /data/mysqldata/slow-query.log

2_2_2.EXPLAIN分析查询

EXPLAIN可以显示MySQL如何使用SQL执行计划.

使用方法,在select语句前面加上Explain就可以了:

EXPLAIN select * from student

批注 2020-08-09 200201.jpg

结果说明

1、id：这是SELECT的查询序列号

2、select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二个或后面的SELECT语句

DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT：UNION的结果。

SUBQUERY：子查询中的第一个SELECT

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的

4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

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 将遍历全表以找到匹配的行

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，

就不再搜索了

Range checked for each

Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

3_索引优化

3_1.索引类型

1.唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

主键一定是唯一索引

2.普通索引

最基本的索引，它没有任何限制，用于加速查询。

3.主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

4.组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

创建方法:

ａ.　建表的时候一起创建

CREATE TABLE mytable ( id int(11) , name VARCHAR(32) , INDEX index_mytable_id_name (id,name) );

b. 建表后，直接创建索引

CREATE INDEX index_mytable_id_name ON mytable(id,name);

c. 修改表结构

ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

5.全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。

它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

创建方法：

ａ. 建表的时候一起创建

CREATE TABLE article ( id int(11) NOT NULL AUTO_INCREMENT , title char(250) NOT NULL , contents text NULL , create_at int(10) NULL DEFAULT NULL , PRIMARY KEY (id), FULLTEXT (contents) );

b. 建表后，直接创建索引

CREATE FULLTEXT INDEX index_article_contents ON article(contents);

c. 修改表结构

ALTER TABLE article ADD FULLTEXT INDEX index_article_contents　(contents);

6.聚集索引

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB普通索引的叶子节点存储主键值。

3_2.索引的存储结构类型

1.B+Tree

MySQL中索引的默认使用方案

非叶子节点不存储data,只存储索引

叶子节点包含所有索引字段

叶子节点之间用指针链接,提高区间访问的性能

不同的存储引擎其索引的存储结构稍有不同，比如：

MyISAM：非聚簇索引，data里存的是指向数据地址的指针，所以有三个文件（.frm .MYI .MYD）
InnoDB：聚簇索引，data里就放的是数据，所以有两个文件(.frm .idb)

2.hash

hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码和指针。因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。

限制

使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
hash索引不能用于外排序
只支持等值查询，包括=、IN、<=>。不支持范围查询
当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）

3_3.索引的使用

虽然索引能够为查找带来速度上的提升,但是也会对性能有一些损失.

索引会增加写操作的成本
太多的索引会增加查询优化器的选择时间
索引会占用额外的存储空间

使用索引的场景

主键自动建立的唯一索引;
经常作为查询条件在where或者order by 语句后面出现的列要建立索引
作为排序的列要建立索引
查询中与其他表关联的字段,外键关系建议索引
高并发条件下倾向建立组合索引
用于聚合函数的列可以建立索引,例如使用count(name),name列就要建立索引
一张表的索引个数最好不要大于5个

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

3_4.回表查询

普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？

通常情况下，需要扫码两遍索引树。

（1）id为PK，聚集索引，叶子节点存储行记录；

（2）name为KEY，普通索引，叶子节点存储PK值，即id；

例如: select * from t where name ='lisi';

批注 2020-08-09 212638.jpg

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

解决方法:组合索引

组合索引底层B+树除叶子节点外,每个节点都会组合索引里的每个索引

3_5.禁用索引

虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

因此,对于使用索引的表,每插入一条数据,数据库都会对插入的记录建立索引.如果插入大量数据,建立索引会降低数据插入的速度.所以,再批量插入之前可以禁用索引,插入完成后,再开启索引,这样统一建议索引,就减少了一条一条维护索引所带来的性能损失.

禁用table_name索引的语句:

ALTER TABLE table_name DISABLE KEYS

开启索引语句:

ALTER TABLE table_name ENABLE KEYS

MySQL优化,看这一篇就够了