MySq索引及性能优化

最新推荐文章于 2024-06-23 14:54:01 发布

9点睡

最新推荐文章于 2024-06-23 14:54:01 发布

阅读量36

点赞数

文章标签：性能优化 mysql

本文链接：https://blog.csdn.net/qq_51238820/article/details/132723367

版权

通用的优化方案

索引优化: 添加适当索引(index）(重点) 在适当的业务场景给适当的字段添加适当的索引
Sql优化: 写出高质量的sql，避免索引失效 (重点)
设计优化: 表的设计合理化(符合3NF，有时候要进行反三范式操作-冗余字段)（重点）
配置优化: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
架构优化：主从复制、读写分离、分库分表(重点)
硬件优化: 服务器的硬件优化内存 CPU 磁盘

索引的底层实现

MySql底层的数据结构主要是基于Hash 和 B+Tree

从结构树看,树的高度就决定了查询I/O数据的次数

B+Tree

特点:

非叶子节点不存储data,只存储key,可以增大度
叶子节点不存储指针
顺序访问指针，提高区间访问能力

MyISAM 索引实现

索引特点：非聚簇索引
   采用B+Tree 作为数据结构
   MyISAM 索引文件和数据文件是分离的(非聚簇)
   叶子节点存储的是数据的磁盘地址
   非主键索引和主键索引类似

InnoDB索引实现

索引特点：
   采用B+Tree 作为数据结构
   数据文件本身就是索引文件 (聚簇索引)
   表数据文件本身就是按照B+Tree组织的一个索引结构文件
   聚集索引-叶节点包含了完整的数据记录
   非主键索引的叶子节点指向主键

索引的分类

0.主键索引：mysql中表必须维护一个B+tree索引树，如果在表中没有指定主键列，数据库会通过一个隐藏列作为索引字段构建B+tree
1.普通索引index :加速查找
2.唯一索引:
   主键索引：primary key ：加速查找+约束（不为空且唯一）
   唯一索引：unique：加速查找+约束（唯一）
3.联合索引（组合索引）--》联合主键索引联合唯一索引联合普通索引
满足最左匹配原则
   -primary key(id,name):联合主键索引
   -unique(id,name):联合唯一索引
   -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好(一般不推荐使用大文本字段，比如text)。

索引的操作

索引字段的选择

1.字段的辨识度要高-唯一性比较高（一般企业中辨识度达到75%左右比较考虑）；

2.条件字段优先考虑加索引(where on)

3.在order by group by后的字段可考虑加索引

4.对于回表查询，如果联合字段唯一性强，则可构建联合索引，避免频繁回表查询（随机I/O）导致查询过慢问题；

5.对于索引优化，如果查询中存在热点数据，则数据不经常变化，则考虑缓存(本地缓存|远程缓存)

索引失效的原因？

1.查询条件没有用索引；
2.只要对索引字段进行处理，都会导致索引失效;

3.左侧模糊匹配查询

4.数据频繁的增删：频繁的增删数据，导致叶子节点分裂与合并，重新构建的过程，索引会失效

5.or关键字可能导致索引失效

6.联合索引不遵循最左匹配原则

7.查询的列为空；--》为空的列对应的行不参与索引的构建！

如何在生产环境下定位有问题的SQL语句?

1.用户反馈

2.德鲁伊连接池可视化界面查看

3.自己增加AOP判断时间

4.数据库支持慢查询日志

慢查询日志

Explain (执行计划)

使用方法:使用Explain关键字放到sql语句前

参数详解

id（重要）

id值相同
执行顺序由上到下

id不同
如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行

id相同不同,同时存在
可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行

select_type

SIMPLE: 简单select查询,查询中不包含子查询或者UNION
PRIMARY: 查询中若包含任何复杂的子查询,最外层查询则被标记为primary
SUBQUERY: 在select或where中包含了子查询
DERIVED: 在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当 UNION
若第二个select出现的union之后,则被标记为union
若union包含在from子句的子查询中,外层select将被标记为deriver
UNION RESULT: 从union表获取结果select,两个UNION合并的结果集在最后