Mysql调优

MYSQL调优

1.性能监控

在五版本时使用 show profile语句显示sql运行的详细时间，如果之前运行了多条sql 通过 show profile query +queryid可以查询具体第几条的查询时间。

show profile +上图参数可以查看不同的数据。

show profile在之后更新的版本中可能被舍弃，使用performance schema 代替

这个功能一般是被开启的，我们可以通过 show variables like 'performance_schema';查看是否开启。

2.schema与数据类型优化

最小的一般最优秀：使用数据类型时在不超过取值范围内的情况下用最小的数据类型是比较快的。

避免使用null:因为会导致索引失效。

char:固定长度（性别，加密后的密码）。

varchar:灵活长度

整数类型的长度是没什么用的，因为底层使用的储存空间不同所以在数据库中长度限制不了取值。

使用枚举代替某些有对应关系的字符串，枚举底层是用数字存储的。取值与插入数据时有关，从一开始计算。使用数字或字符都能查询到数据。

 

大小和占用空间的区别：在磁盘内存储空间都是由一个个4k大小的空间来组成的，这也是为什么我们新装一个盘要进行4K对其的原因，这样可以提升磁盘查找速度，比如一个4.5K的文件实际大小为4.5k但其实占用空间为8k因为一个基础空间装不下所以使用两个，所以占用空间为8K.

三范式：为了减少数据冗余，但是有时候为了方便操作还是要必要的进行冗余。

第一范式：保证列不可分（原子性）。

第二范式：不能有传递依赖。

第三范式：表里边的值依赖于主键。

缓存衍生值就是多加一个列来保存数据，这次需要区分新增一个列并且更新的消耗大还是count(*)这样一个操作消耗大。

 

 

非聚簇索引：索引和数据不放在一起的。

聚簇索引：数据文件和索引文件放在一起的。

适当拆分：

数据库的拆分分为垂直拆分和水平拆分，垂直是按业务拆分（比如外卖项目的按地区拆分），水平是按数据数量进行拆分。（按数据进行拆分，比如按步长进行拆分）

3.执行计划 （语句前加上explain就可以看执行计划）

id:id 越大表示执行顺序越靠前，一样的id由上到下来执行的。

select-type:查询类型表示执行的哪一种查询。

table：查的那个表，有可能是临时表或者union合并的表。

type:访问的类型代表查询的速度，访问的类型有很多，效率从最好到最坏依次是：

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

possible_keys

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

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

explain select * from emp;

extra

包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;
​
--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;
​
--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;
​
--using where:使用where进行条件过滤
explain select * from t_user where id = 1;
​
--using join buffer:使用连接缓存，情况没有模拟出来
​
--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;

 

 

mysql的memery引擎是用的hash表储存的，hash表就是数组加链表的形式，然后要给hash表里面放数据的话首先要进行一个散列算法，而散列算法最基本的就是一个取模运算一个大小为8 的模型取出0-7的，然后取得值就加在下面链表上，但是hash表是有限制的，需要加载进缓存才能加快查询速度，而且hash表查询是一个等值查询，在数据库查找中一般都是范围查找，所以不太合适作为索引。

二叉树存储数据会造成树的节点太深，太深就会导致每次的io次数增加所以不能用。

avl树（平衡树）：最短子数和最长字数高度差不能超过一，插入数据的时候需要进行旋转的操作。数据越多那每次插入就会有一个一到n次的旋转操作。所以这个数据类型是插入删除效率比较低的，查询效率比较高的。

红黑树：是在平衡树上再进化来的版本，最长的子树和最短的子树长度不超过两倍就可以了，任何一条单分支不能连续出现两个红色的节点以及每一条分支上的黑色节点都是相等的，通过加入变色的操作来减少旋转的操作，做出了一个效率的平衡。

 

b树的格式是在树中均匀分布键值数据和指针，因为mysql读取数据是磁盘预读每次读16K的数据（大约4页），如果存上更多数据的话就会增长树的高度导致效率不行。

b+树是innodb的储存数据的格式，他是由B树进化过来的，在非叶子节点去除了date，这样就可以存更多的数据，防止树太高。

b*树是在非叶子节点中相邻的节点都有一个指针指向对方。

innodb和myisam都是用b+树储存，但是

innodb是聚簇索引，在索引里面查的数据是最终的数据。

myisam是非聚簇索引，在索引里面查的数据不是最终的数据，而是数据所在的地址。

在8里面就没有查询缓存了，因为这个缓存效率非常低，需要经常更新。

回表：就是普通索引在查询的时候通过b+树获取的数据并不是最终数据，而是主键的索引，然后通过主键索引的b+树查询到最终的数据。这个过程叫回表。

覆盖索引：就是通过普通索引查询主键数据的时候，不需要再通过主键的索引查询数据了，因为已经查到了，而省略了查省下数据的时间，这叫覆盖索引。

最左匹配：在组合索引中会先匹配左边的列，如果左边的不能先匹配上那索引无效。

索引下推：在组合索引中以前的mysql的老版本没有索引下推，把以前在server层过滤数据的过程拿到了储存引擎中。相当于在存储引擎中处理了两个列的数据，而不是像以前一样先去除其中一个列的数据然后再对另一个列的数据来匹配。

4.通过索引进行优化