MYSQL性能调优详细整理
2021-03-15 14:04:56 2 举报AI智能生成
MYSQL性能调优完全实践整理
作者其他创作
大纲/内容
MYSQL
表数据设计三范式
第一范式：保证列的原子性，保证列不可再分。
比如：某个字段是电话号码，而这个电话号码其实可以再分为手机号或者座机
比如：某个字段是电话号码，而这个电话号码其实可以再分为手机号或者座机
第二范式：唯一性 ；一个表只说明一个事物;
比如：一个表里冗余了其他事物的数据，一般采取分成两个表然后外键关联
比如：一个表里冗余了其他事物的数据，一般采取分成两个表然后外键关联
第三范式：每列都与主键有直接关系，不存在传递依赖;
比如：还是表冗余的问题
比如：还是表冗余的问题
索引
什么是索引？
索引是一种高效查询的排好序的数据结构
索引的数据结构是什么？
B+tree
多路平衡查询树
叶子节点包含所有的索引元素
父节点针对多个元素把落到区间内的索引作为子节点的元素
B+tree为什么高效率？
首先查找是在内存中操作的
1、首先从根节点开始把节点上的索引数据一次性IO读取到内存中，然后二分查找的方式进行查询
2、通过索引指向的子节点指针进行递归查找
2、通过索引指向的子节点指针进行递归查找
其次树的高度小，IO执行次数少
1、假如一个索引的类型是bigint，8个字节；索引存储的指针，6个字节
2、B+tree的最小存储单元是页，然后页的大小是16kb，
那么一个索引页能够存储的索引元素为16kb/14b=1170个索引元素
3、也就是说如果树的高度是3的话，那么叶子节点个数是1170^2
4、然后叶子节点还存储着data数据，如果是innoDB是聚合索引，所以一行数据
（主键索引，非主键索引data存储的是主键id）都在data中，
5、假如一行数据data为1kb。那一个节点（索引页）上可以大约放16个元素（索引+data），
相当于可以存储1170^2*16条数据，大概是2千万的数据，
2、B+tree的最小存储单元是页，然后页的大小是16kb，
那么一个索引页能够存储的索引元素为16kb/14b=1170个索引元素
3、也就是说如果树的高度是3的话，那么叶子节点个数是1170^2
4、然后叶子节点还存储着data数据，如果是innoDB是聚合索引，所以一行数据
（主键索引，非主键索引data存储的是主键id）都在data中，
5、假如一行数据data为1kb。那一个节点（索引页）上可以大约放16个元素（索引+data），
相当于可以存储1170^2*16条数据，大概是2千万的数据，
查找到读取到数据需要3次IO
叶子节点双向指针，范围查找和比较更加快捷
刚刚提到了innodb，那么mysql都有什么搜索引擎？分别介绍一下？
innodb
聚合索引
文件存储结构
文件存储结构是两个文件.frm文件结构和.ibd索引&数据文件
（frm=form结构；ibd=index bind data）
（frm=form结构；ibd=index bind data）
针对主键的创建有什么要求吗？可以不创建主键吗？
innodb使用的时候，可以不创建主键吗？
mysql会隐试的创建rowid
为什么主键建议使用整型呢？
因为整型方便比较大小，string性能差，而且string占用空间大
为什么主键要自增？
如果不是自增的话，节点上的索引会存在分裂平衡调整
主键索引的叶子节点存储数据
非主键索引的叶子节点存储主键
为什么？因为冗余数据会带来额外开销，数据一致性等；还有空间浪费
回表
回表查询就是通过非主键索引查询数据的时候会去主键索引表再去查询实际数据
联合索引
最左前缀原则排好序的索引树
前导列
MyISAM
非聚合索引
文件存储结构
三个文件.frm文件结构；.MYI索引文件；.MYD数据文件
叶子节点存储磁盘文件地址指针
查询的时候哪个搜索引擎快呢？
针对innodb，主要看查询过程是否有回表动作，
另外就是在针对回表的时候，查询数据量是否很大，
如果太大的话，mysql内部优化会直接进行全表扫描。这样的情况就不如MyISAM快
另外就是在针对回表的时候，查询数据量是否很大，
如果太大的话，mysql内部优化会直接进行全表扫描。这样的情况就不如MyISAM快
其他数据结构类比
为什么不用二叉搜索树?
二叉就意味着树的高度太高，另外就是排好序的数据会单边增长，时间复杂度O（n）
为什么不用红黑树？
高度高
Hash表呢？
存储和等值查询的效率虽然都很高
范围查找和排序就不好搞了
Btree
核心特点就是非叶子节点上也存在data，数据大的话，能存储的索引数据就会少
索引最大长度768
超过就最左前缀法则截取
如何做索引性能调优？
explain（执行计划）工具都有些什么字段？分别代表什么意思？
id
大的优先执行，null最后执行，相同顺序执行
type
const
查询结果是<=1条数据
system
子查询是const，外边的查询就是system
eq_ref
主键或者唯一索引关联查询
ref
联合索引的一部分进行查询
range
使用索引范围查找
index
扫描全部索引
all
全表扫描
key
显示哪个索引
key_len
索引长度
Extra
Using index
使用了覆盖索引
Using where
未使用覆盖索引
Using index condition
前导列范围查找，而且部分列未覆盖索引
Using temporary
这种情况需要优化，使用临时表了
Using filesort
排序没有走索引，文件排序
Using join buffer
笛卡儿积，需要优化
针对索引优化有哪些操作？
5.6版本以后有索引下推。什么是索引下推?
联合索引情况下，索引树上进行查找
联合索引，采用前导列
Order by也要遵循最左前缀法则，否则Extra字段中就会是Using filesort
单路排序
读取出来的数据总大小超过了sort_buffer就转双路
双路排序
两次IO，性能慢，第二次是回表查询
索引上不要使用函数，date默认会有日期转换函数，解决办法是范围查找
字符串要加引号
联合索引，前面的查询条件如果是范围查找，后面的索引会失效。
尽量使用覆盖索引，不要select *
！=和<>这种不等于会全表扫描
解决办法就是覆盖索引就会走索引
like通配符开头不走索引
解决办法就是覆盖索引
范围查询不一定走索引
尽量避免使用in or 这样的查询mysql内部优化，如果数据量大就可能全表扫描
索引字段的范围查找，不一定走索引，查询结果数据量大
解决办法，分页查询
in是里面驱动外面的
exist是外面的驱动里面的
避免对null进行判断，null的判断不走索引
分页查询的优化点
如果是连续自增的话就where id > 90000 limit 5这样
不这样：limit 90000,5;这样，查询的是900005
非主键的排序
select * …… ORDER BY name limit 90000,5
（这样的问题是没有覆盖索引，可能会全表所以优化为下面的）
（这样的问题是没有覆盖索引，可能会全表所以优化为下面的）
select * from …… inner join (select id from order by name limit 90000,5）
（利用回表查询其他数据）
（利用回表查询其他数据）
join关联查询的优化点
关联字段加索引
小表驱动大表
默认mysql内部会分析优化
如果提前知道大小的话，就用straight_join，左驱右
count 的优化点
索引字段强烈不能为null
count非主键索引且可空的的时候null统计不进去
count非主键索引要比主键索引快
（因为叶子节点读值）
（因为叶子节点读值）
count 1更快，不读值
count * 也走的二级索引（非主键）也不读值
事务
事务有几种特性？
ACID：原子性、一致性、隔离性、持久性
并发场景下事务面临的问题?
因为并发场景下db数据是公共数据。会面临的四类问题：
“一个写三个读”的问题
“一个写三个读”的问题
更新丢失
数据被盖了
脏读
读到了未提交数据
不可重复读
读到了已提交数据
幻读
读到了新增的数据
上面的问题怎么解决，通过4种事务隔离级别
读未提交
读已提交
可重复读
默认
MVCC（多版本并发控制机制）
可串行化
高可用
分库分表
0 条评论
下一页
