EXPLAIN+MySQL调优（分库分表）
2021-02-26 15:51:49 2 举报AI智能生成
EXPLAIN+MySQL调优（分库分表）
作者其他创作
大纲/内容
explain
id：查询的序列号
select_type：查询的类型，主要是区别普通查询、联合查询、子查询之类的复杂查询
type：访问类型，保证查询至少达到range级别，最好能达到 ref
possible_keys：可能用到的索引
key：实际用到的索引
key_len：索引中使用的字节数（越短越好）
ref：显示索引的哪一列被使用了（最好是个常数）
rows：找到所需记录需要读取的行数（越少越好）
Extra
Using filesort：使用了文件排序，不好
Using temporary：使用了临时表，非常耗性能
Using index：使用了【覆盖索引】，效果不错
Using where｜impossible where：前者用到了where条件，后者没有
MySQL调优
优化器
基于成本的优化
成本
IO成本
CPU成本
单表查询优化
基于索引统计数据的成本计算
多表连接的成本
基于规则的优化
条件简化
外连接消除
子查询优化
IN 子查询优化
ANY/ALL 子查询优化
转为max()、min()查询
[NOT]EXISTS 子查询优化
创建时优化
设计要合理，比如使用最合适的数据类型和长度保存数据
合理的创建索引
多建联合索引
查询时优化
优化经验
SQL优化
阿里规约
order by
尽可能在索引列上完成排序操作，根据最佳左前缀法则，否则会产生filesort
内部原理
优化策略
order by 时不要使用 select *，容易把sort_buffer占满
增大 sort_buffer_size 参数的设置
增大 max_length_for_sort_data 参数的设置
group by
group by 的优化和order by大体相同
group by 实质是先排序后进行分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 参数的设置+增大 sort_ buffer_ size 参数的设置
where 高于 having，能写在 where 限定的条件就不要写 having 了
id用完
bigint
如果有可能用尽，则一开始应该创建成8个字节的bigint
表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误
row_id
没设置主键的时候，InnoDB会自动创建一个长度为6个字节的row_id
row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据
thread_id
系统保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量
thread_id_counter 定义的大小是 4 个字节，因此达到 2^32-1 后，它就会重置为 0，然后继续增加
分库分表
在架构设计时做分库，根据不通的业务模块进行划分，业务之间不会影响性能
单表超过500万时读写性能明显下降，此时需要考虑分表
主键ID不是使用的自增ID，而是使用单独数据表分批生成
路由策略
根据主键ID对机器数量取模
采用分组+切片来解决高并发数据量过大的问题
触发条件
分库原则
分表原则
分表规范（阿里规约）
0 条评论
下一页
