mysql调优

【前言】：

需要先了解B+树的数据结构 ，所以说《数据结构与算法》真的是非常重要的基础课，B+树是在BST、AVL、M-way Search Tree 之后诞生的B树的基础上出现的一种数据结构，不了解B+树的结构，联合索引的优化就无从谈起，死记硬背规则是不行的，如果理解了结构就能很容易理解索引优化。

【口诀】：

全函左范不勾符，UUID like or , OrderBy limit 。——记为1～12
（1）：全值匹配；
（2）：函数表达式；
（3）：最佳左前缀；
（4）：范围查询放最后；
（5）：不等于号要慎用；
（6）：勾选《不是NULL》；
（7）：字符类型单引号；
（8）：主键顺序插；
（9）：like放‘前缀%’；
（10）：union all 优化；
（11）：索引列和orderby子句保持一致；
（12）：利用主键索引。
//后来想了一想，发现前面的汉字部分可以用谐音记忆——“不服示范左勾拳”， 啊哈哈哈。

【架构调优】：

• 硬件和OS调优
• MySQL调优
• 架构调优
  像金字塔一样顶端成本越来越高 ， 但底端的效果越来越好。（如果笔者有幸能走到那一天的话，再回过头来补充相关的内容。（–未完待续）

【字段优化】：

【范式化与反范式化】：

反范式化就是违反范式化设计：

1、为了性能和读取效率而适当的违反对数据库设计范式的要求；

2、为了查询的性能，允许存在部分（少量）冗余数据

换句话来说反范式化就是使用空间来换取时间。
【范式化和反范式化的比较】：
1、范式化的更新操作通常比反范式化要快（字段较少）。
2、当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
3、范式化的表通常更小，所以占据的内存更少。
4、范式化设计的缺点是通常需要关联，稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多。
5、复杂一些的查询语句也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

【字段优化基本原则】：

• 越小越好
  主键对应聚簇索引，所以越小越好，其他字段也是，《计算机组成原理》中更小的字段，更少的磁盘、内存、CPU高速缓存、CPU的处理周期更少。
• 尽量简单
  整型 优于 字符串 ， 因为字符集和校对规则 会比整型更复杂。
• 尽量避免NULL
  有NULL的列会使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间。

【其他的字段优化规则】：

【unsigned】：

无符号unsigned存储正数 ， 存储的空间会多一倍。

【实数与decimal】：

计算机的CPU是由加法器、减法器、除法器 等组成的。不支持decimal类型，往往要进行一个转换，所以在效率上来讲肯定没有——float和double这两种要更高效,计算机的CPU是支持浮点数的计算。

【实战实数】：

【Q】：
数据量特别大 ， 要用实数存储，要确保精度，还要确保效率，应该怎么办呢？
【A】：
使用BigInt。
在数据量比较大的而且要求精度时，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和 DECIMAL精确计算代价高的问题。

【blob和text单独成列】：

//一般来说是要求——单独成一列。（不要和其它基本类型放到一个表里，对查询优化是不友好的。）
blob和text会单独地做一个对象处理。存储引擎进行存储的时候，数据量太大了。会专门用一个外部的存储区域来进行存储。

需要1～4个字节去存放（blob或text）的指针 ，用这个指针去指向 外部存储中实际上很大的那一块儿区域，所以它在处理的时候还是相当麻烦的。

• 他俩的差别：

blob存的是二进制( 它没有所谓的排序规则和字符集 )，text里面是有排序规则和字符集的。

• 大字段单独成一张子表

因为大字段对索引不太友好。内部会有一个 “合成索引” 的东西。需要对它单独处理，单独进行索引的优化。
//一般用前缀索引，即取大字段的前几个字符为内容放到B plus树里。

【字符串列】：

• 选择varchar

//最大长度 比 平均长度 大很多；

• 选择char

//一些定长的，如： 密码，MD5 ， hash值。
但如果选择varchar的话 ， MySQL会分配固定大小的内存来保存从磁盘中拿出来的值，实际内存中开辟空间的时候，它也不知道varchar的长度具体是多少，所以有可能划的更大。（所以，可能会更消耗内存。）
//所以从性能的角度上考虑使用Char更优；

【枚举enum】：

使用枚举可以节约空间。

【datetime和timestamp】：

【相同点】：

​ 它俩都只精确到秒；（默认情况下存储只精确到秒）

【不同点】：

​ 时间跨度；

datetime----- 1001 ～ 9999

timestamp---- 1970 ～ 2038
1
2
3

//并且：timestamp是和时区有关系的。

【如何精确到更小的粒度呢？】：

用bigint / string 将格式切换一下。

【命名规范】：

【表名】：

业务名称_表的作用

【为何要小写】：

Linux系统中是区分大小写的，表和字段必须全部是《小写》。

【索引优化】：

【limit优化】：

limit 10000 , 10; //这种方式Type——ALL ， 会走全表扫描。
【优化思路】：
让SQL语句利用上聚簇索引 ， 改写成 “where id > n ” , 这就需要前段点击《more》按钮来确定主键id的数量，即利用主键索引来优化。

【函数表达式】：

【表达式】：

【表结构介绍】：
student表自动创建的主键索引，id设置成了主键 ， where 中只涉及到主键。

EXPLAIN select id,NAME,age from student where id+1=2;     --主键参与了表达式
1

//由于主键参与了表达式所以type为ALL ， 即进行了全表扫描。

EXPLAIN select id,NAME,age from student where id = 1;     --主键没有参与表达式
1

【日期函数】：

【先看表结构和索引】：

EXPLAIN select birthday from emp WHERE birthday > '1999/01/01';
1

//可以看到走了index , index 和ALL的区别在于，index是走了索引树，而ALL是全表扫描。

EXPLAIN select * from emp
where
	YEAR(birthday) = YEAR( DATE_SUB( NOW(),INTERVAL 1 YEAR ) );
1
2
3

//由于birthday字段参与了日期函数 ， 所以没有走索引，Type为ALL，即全表扫描。
【总结日期函数】：
最好是能够把日期范围给定死。

【全值匹配】：

搜索的条件 和 索引的列 要一致。这种情况就称之为全值匹配。
//where语句后的查询条件的顺序对结果是没有影响的。————查询优化器会进行一个调整。
即：
索引 （a , b , c）

where a=1 and b=2 and c=3 ;
where c=3 and a=1 and b=2 ;
where a=1 and c=3 and b=2 ;
1
2
3

//这三句都一样。
【数据表结构】：

EXPLAIN SELECT * FROM zuoqz WHERE a=1 AND b=2 AND c=3;

EXPLAIN SELECT * FROM zuoqz WHERE b=2 AND a=1 AND c=3;

EXPLAIN SELECT * FROM zuoqz WHERE c=3 AND a=1 AND b=2;
1
2
3
4
5

【三句的结果都相同】：

【最佳左前缀】：

【表结构】：
仍是上图的zuoqz。 索引——（ a , b , c ）

[位于中间]:

EXPLAIN  select  *  from  zuoqz  where  b=3;
1

//ALL

[用第一个隔N]:

EXPLAIN  select  *  from  zuoqz  where  a=1 AND c=3;
1

//可以发现只用了第一列的索引。

【范围条件放最后】：

• 左边的列是精确查找 ，则右边的列可进行范围查找 。

• 中间有范围查询会导致后面的列全部失效，无法充分利用联合索引。

【不等于要慎用】：

只要使用了不等于就会走全表扫描。

【勾上《不是NULL》】：

is not null 的时候 ， 索引的长度越长 ， 说明索引利用的越充分。

【字符类型加引号】：

字符类型的列如果不加引号 ， 索引失效 ， 就会变成全表扫描。

【模糊查询】：

like ‘abc%’ --由联合索引在B+ 树中的结构所致，此处应该有图说明。
//对于没有排好序的数据，宁愿走全表扫描。

【OR关键字】：

使用union all 可以利用上索引。

【order by 排序】：

索引的列尽量和order by 子句的列保持一致。
（ a , b ,c ）

order by a,b,c

order by c,b,a
1
2
3

//这样是没问题的。(顺序和倒序相比就是多一个操作步骤而已)

【尽可能按主键顺序插入行】：

【为什么不用UUID做主键？】：
（1）可读性差；
（2）B+树主键索引空间更大。一方面是主键长 ， 另一方面是不能按照顺序去生成，它是随机的，B+树结点的分裂会导致大量的数据移动。
//顺序生成主键非常关键。