MySQL调优
网络异常，图片无法展示
|
调优的目的？让吞吐量更大，响应速度更快。
关于数据库优化，我们从以下5个维度进行。
一.优化表结构
表结构尽量遵循三范式的原则，在进行多表查询的时候，必要时可以采用反范式化进行优化。
什么叫范式？
- 在关系型数据库中，关于数据表设计的基本原则，规则就称为范式。
一共有6大范式，知道前5个就行。满足了高级范式,就一定满足低级范式。比如满足第三范式,就一定满足第1,2范式。
- 第一范式：确保每一个字段保证"原子性",不能被拆分。比如有一个字段叫"个人信息",它就可以拆分为地址,年龄,姓名等。就不满足第一范式
- 第二范式：确保表中的每一条记录,都有唯一的标识(主键)。所有的非主键字段,必须完全依赖主键。不能部分依赖。
注意：这里的主键是联合主键。比如下图：姓名，年龄依赖球员编号；比赛时间，比赛场地依赖比赛编号；只有得分全部依赖
- 第三范式：确保非主键之间是相互独立的,不能产生依赖。下图就不满足
- 巴斯范式(BCNF)：3NF的增强版，在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖的关系。相当于主键中也产生了依赖关系,就不满足巴斯范式。比如下图
- 第四范式：一个表中只有一对1:多的关系。如果一个表中有多个1：多的关系就不满足第四范式。
- 第五范式：也叫完美范式，了解有这么个东西就行
范式的优缺点？
- 优点：消除数据冗余
- 缺点：降低查询效率，因为范式越高，设计出来的数据表就越多，就需要很多的关联查询。
反范式化？
- 是一种空间换时间的优化手段。因为我们遵循业务优先原则，可以通过在表中增加冗余字段来提高数据库的读性能。
- 当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采用反范式化进行优化。
数据库的设计原则？三少一多
- 数据表的个数越少越好
- 数据表中的字段个数越少越好
- 数据表中联合主键的字段个数越少越好
- 使用主键和外键越多越好。这里是指外键关系越多，就可以重复的利用数据，而不是指在表中建立好多外键。
数据库表建模的工具?
- PowerDesigner
二.优化逻辑查询
关联查询优化：最好"被驱动表加索引"
- 外连接：一般驱动表是全表查询(就算添加索引也是index),被驱动表是索引查询。(也就是说最好给被驱动表添加索引，驱动表加不加都行)
- 如：student是驱动表，book是被驱动表 ：EXPLAIN SELECT * FROM student LEFT JOIN book ON student.card = book.card；
- 内连接：谁是驱动表谁是被驱动表由优化器决定,优化器满足"小表驱动大表"。(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作为缓存，减少数据库的压力
