MySQL调优

调优的目的？让吞吐量更大，响应速度更快。

关于数据库优化，我们从以下5个维度进行。

一.优化表结构

表结构尽量遵循三范式的原则，在进行多表查询的时候，必要时可以采用反范式化进行优化。

什么叫范式？

• 在关系型数据库中，关于数据表设计的基本原则，规则就称为范式。

一共有6大范式，知道前5个就行。满足了高级范式,就一定满足低级范式。比如满足第三范式,就一定满足第1,2范式。

• 第一范式：确保每一个字段保证"原子性",不能被拆分。比如有一个字段叫"个人信息",它就可以拆分为地址,年龄,姓名等。就不满足第一范式

• 第二范式：确保表中的每一条记录,都有唯一的标识(主键)。所有的非主键字段,必须完全依赖主键。不能部分依赖。
  注意：这里的主键是联合主键。比如下图：姓名，年龄依赖球员编号；比赛时间，比赛场地依赖比赛编号；只有得分全部依赖

• 第三范式：确保非主键之间是相互独立的,不能产生依赖。下图就不满足

• 巴斯范式(BCNF)：3NF的增强版，在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖的关系。相当于主键中也产生了依赖关系,就不满足巴斯范式。比如下图

• 第四范式：一个表中只有一对1:多的关系。如果一个表中有多个1：多的关系就不满足第四范式。

•  第五范式：也叫完美范式，了解有这么个东西就行

范式的优缺点？

• 优点：消除数据冗余
• 缺点：降低查询效率，因为范式越高，设计出来的数据表就越多，就需要很多的关联查询。

反范式化？

• 是一种空间换时间的优化手段。因为我们遵循业务优先原则，可以通过在表中增加冗余字段来提高数据库的读性能。
• 当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采用反范式化进行优化。

数据库的设计原则？三少一多

• 数据表的个数越少越好
• 数据表中的字段个数越少越好
• 数据表中联合主键的字段个数越少越好
• 使用主键和外键越多越好。这里是指外键关系越多，就可以重复的利用数据，而不是指在表中建立好多外键。

数据库表建模的工具?

• PowerDesigner

二.优化逻辑查询

关联查询优化：最好"被驱动表加索引"

• 外连接：一般驱动表是全表查询(就算添加索引也是index),被驱动表是索引查询。(也就是说最好给被驱动表添加索引，驱动表加不加都行)
  • 如：student是驱动表，book是被驱动表   ：EXPLAIN SELECT * FROM student LEFT JOIN book ON 域名 = 域名；
• 内连接：谁是驱动表谁是被驱动表由优化器决定,优化器满足"小表驱动大表"。(2个都加索引,让优化器自己决定;如果只加 一个索引,优化器肯定选择加索引的作为被驱动表)

优化器中join 的原理？

• 不加索引
  • Simple Nested-Loop Join(简单嵌套循环连接) 

• • Block Nested-Loop Join(块嵌套循环连接)：不再逐条获取驱动表的数据，而是一块一块的获取，引入join buffer缓存区

• • Hash Join：MySQL8.0之后默认使用hash join。可以做大数据集连接。
• 加索引
  • Index Nested-Loop Join(索引嵌套循环连接)

 子查询优化：

• 子查询执行效率不高，使用关联查询(join)代替子查询。
• 效率不高的原因：
  • 查询的过程中需要建立一个临时表，查询完毕，再撤销临时表。消耗性能
  • 临时表都不会使用索引

 排序优化：

• 前提知识：MySQL支持2中排序方式，所以优化也是从这俩方面考虑
  • index排序：b+树的叶子节点就是按照排序进行的，使用索引直接就可以保证有序性
  • FileSort排序：将需要排序的数据加载到内存中，然后进行排序。
• 尽量使用索引完成order by排序。如果where 和order by 后面的列相同就用单索引，不同就用联合索引。
• 对FileSort进行调优
  • 前提知识：FileSort有2种算法
    • 双路排序(慢)：进行俩次磁盘扫描，第一次只加载需要排序的列到sort_buffer，进行排序。然后根据排序好的列，第二次从磁盘读取其他的列。
    • 单路排序(快)：一次性将所有列的数据加载到sort_buffer中，进行排序。
  • 提高sort_buffer_size：不管哪种算法，提高这个内存值肯定加大效率。
  • 提高max_length_for_sort_data：这个参数就是一个界限，需要返回的列总长度大于这个值就使用双路，小于这个值就使用单路。

group by优化：

• group by 优化的方法和order by一样。

分页查询优化：

• 如果像下图查询的情况极端，尽量的使用表中其他字段的索引。

其他的优化

• exists和in：小表驱动大表

• count(*)和count(1)和count(具体字段)的效率
  • count(*)和count(1)没有本质区别，执行时间基本一样
  • count(具体字段)的时候，尽量使用占用空间少的二级索引。因为二级索引存储的信息相比聚簇索引要少很多。count(*)和count(1) 系统会自动选择占用空间少的二级索引进行统计。　　
  • innodb的count()是O(n)级别的，MyISAM是O(1)级别的。
• 关于select *
  • 尽量使用什么字段就指出来，不要使用select *。因为会加载很多没用的列。
  • 无法使用覆盖索引
• 关于Limit 1：
  • 如果是全表扫描，加上Limit 1。找到数据就不会再继续查找了，加快查找效率
  • 如果是唯一索引，找到数据也不会继续查找了，Limit 1 就不管用了。

 三.优化物理查询(索引)

• 选择适合做索引的字段(MySQL索引讲)
• 哪些情况导致索引失效(MySQL索引讲)
• 使用覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引。(也就是需要的列恰好都在索引的叶子节点上存储，不需要回表)
  • 好处：无需回表；可以把随机IO变成顺序IO加快查询效率(利用到索引都是顺序IO，因为索引就是有顺序的)。
• 使用索引下推：如图

• • 索引下推就用在一些and查询语句中，本来通过非聚集索引zipcode查询出来数据，要进行回表，但是如果查出来100条，分别对这100条进行回表就很浪费性能，icp就是先不进行回表，使用后面的条件进行过滤，过滤完毕之后比如剩下10条，对这10条进行回表就行了。
  • set optimizer_switch = \'index_condition_pushdown=on\'     //开启索引下推

四.使用缓存

对于热点数据可以使用redis或者Memcached作为缓存，减少数据库的压力

五.库级优化

 后期补上

今天是除夕，祝大家新年快乐！

 寄语：放弃可以找到一万个理由,坚持只需要一个信念!