MYSQL调优分析

Mysql 调优

1. 排除缓存干扰

临时的直接再命令行执行

set global query_cache_size=0

set global query_cache_type=0

永久的修改配置文件my.cnf ,添加下面的配置即可。

query_cache_type=0

query_cache_size=0

2. 查看执行计划 explain Sql 解释

https://www.cnblogs.com/tufujie/p/9413852.html

 

3. 避免回表查询

在这里需要提到两种索引模式

聚集索引

普通索引

聚集索引和普通索引的差距

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

 

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

 

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

 

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

 

画外音：所以PK查询非常快，直接定位行记录。

 

 

避免回表的方案

索引覆盖（避免回表）

如果在我们建立的索引上就有我们需要的字段，就不需要回表了

借用图2进行描述 select  id from t where name=’lisi’

 

由上图可见普通索引上已经包含了id,与name信息所以不用回表操作。

联合索引

在索引覆盖的介绍中我们知道了避免回表需要我们将select的字段信息包含在索引表中。那么多字段查询如何避免呢，显而易见那就是联合查询。

借用图1的表结构 select id，name, sex from t where name =’lisi’

避免回表我们需要建立索引（name, sex）

1. 最左配原则

同样借用图1的表结构 此时索引为 (sex,name)

我们需要 selec * from where name =’lisi’

这时我们需要怎么办呢？创建一个字段为name的索引吗？

答案当然是否定的我们可以更改索引字段的顺序 （name，sex）如此我们也能使用到联合索引获取id，然后回表查到行数据。

最左配原则总结来说就是将需要查询的条件尽量放置的坐标用来给其他查询使用。

1. 普通索引与唯一索引的区别

已知mysql是以页为读取单位，每页的大小为16K 四个块区为一页 四个扇区为一块。

同样我们以图1 的表结构来进行分析

Select * from t where name =’lisi’

 

在这里我们设置了两种情况

1. 创建唯一索引 name

这里我们在搜索索引树的时候第二次io中读取的页中不止只有ls这一条数据，当数据搜索到ls这一条数据的时候就停止继续查找后面的数据，然后回表获得该行下的所有数据

1. 创建普通索引 name

当第二次io的时候搜索到ls这条数据的时候由于不是唯一索引所以数据库会完整的查询该页下所有的数据。

总结

在查找的时候普通索引跟唯一索引没有多大的区别就是在对页数据进行查找的时候一个是全量查找，一个是找到一条就回表。

区别较大的是更新操作

这里需要引入一个新的名词changebuffer

 

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

changebuffer主要用于写多读少的场景

6.前缀索引

mysql是支持前缀索引的，也就是说你可以定义一个字符串的索引长度，默认的如果你创建的一个索引没定定义前缀索引的长度那么就是取全量。需要注意的是使用前缀索引的时候无法避免回表因为你使用的前缀索引没办法保证就是该字段下的全部数据。

7.索引下推

在前面的描述中我们介绍了不少从我们自身对sql的优化方式。接下来我们介绍mysql的一个自身优化方式，索引下推。

同样以图1 表结构sql来进行分析

Select * from t where name like ‘肖%’ and sex  kile’m%’ and level like ’A%’

这时我们需要如何优化呢 在mysql 5.6之前我们可以创建普通索引（name） 之后通过拿到id通过id进行回表操作拿到 sex ，level 判断sex like ‘m’  and level like’A’

Mysql 5.6之后推出了索引下推这个概念

这是个啥意思呢？意思就是可以通过创建联合索引 （name, sex， level）在第一次进行索引查询的时候就判断 sex like ‘m’  and level like’A’避免了回表再判断sex 与 level 是否满足条件。

总结来说在联合索引中将函数式的判断不断下推给下一个字段进行判断。

 

1. 数据库索引页的设置

1. 主键id，我们采用bigint，8字节
2. 一条数据大小1KB

第一层
一个页16K，每一个索引键的大小8字节（bigint）+6字节（指针大小），因此第一层可存储16*1024/14=1170个索引键。

第二层
第二层只存储索引键，能存储多少个索引键呢？1170（这么多个页，有第一层延伸的指针）1170(每页的索引键个数，跟第一步计算一致)=1368900
如果第二层存储数据呢？1170（这么多个页，有第一层延伸的指针）16(16KB的页大小/1KB的数据大小)=18720，也就是能存储一万多条数。

第三层
直接看三层能存储多少数据？1170*1170*16=21902400，是不是很强大，此处应该有掌声和鲜花，3次IO就可以查询到2千多万左右的数据，也就是这么大的数据量如果通过主键索引来查找是很快.