目录
一、定位慢查询
项目中,开发环境中mysql配置慢查询日志了(slow_query_log为开启慢查询日志的开关,1为开启,0为关闭。long_query_time为慢查询的阈值时间,单位为秒,默认情况下为10秒)。
定位到查询较慢的sql,通过mysql的explan分析工具,分析慢查询
二、sql执行计划
1、通过key、key_len字段判断查询过程中是否命中索引,避免索引失效的情况。
key:当前sql命中的索引
key_len:命中索引的大小
2、通过type字段判断sql是否有优化控件
判断sql性能,性能从好到差为:null、system(查询系统表)、const(主键查询)、eq_ref(主键索引或唯一索引)、ref(索引查询)、range(范围查询)、index(索引树查询)、all(全盘扫描)
3、通过extra建议判断,此次查询是否出现回表查询的情况,考虑是否通过添加索引或者修改查询字段来优化sql
using index condition: 进行了回表查询
三、索引
1、存储引擎
innodb和myisam引擎
innodb为行锁定,支持事务处理和外键约束等功能,能够保证数据的完整性和一致性。InnoDB叶子节点存储的是整个数据行所有的数据。
myisam为表级锁定和高速读取。MyISAM的索引和数据是分开存储的,因此索引查找的时候,MyISAM的叶子节点存储的是数据所在的地址,而不是数据。
2、索引底层数据结构
索引底层数据结构为B+树结构。非叶子节点只用于索引,叶子节点为双向链表的结构,存储数据。
3、聚簇和非聚簇索引
聚簇索引:数据和索引都存储在叶子节点。只有一个
非聚簇索引:叶子结点只存储数据的主键
回表查询:通过非聚蔟索引查询到索引的主键。但sql的查询字段未在索引的范围内,则需要再次到聚簇索引查询到整行数据。这个过程叫回表。
4、什么叫覆盖索引
覆盖索引指的是查询的时候使用了索引,且返回的列在索引中可以全部找到。
未覆盖索引则会触发回表查询
5、超大分页怎么处理
先分页查询id字段,再子查询查询指定id的数据。
6、索引创建原则
1、数据量大且查询频繁的表
2、常用于查询条件、排序、分组的字段
3、尽量联合索引
4、控制索引数量
5、字段区分度高
7、索引失效场景
违背了联合索引的最左匹配的原则
字段类型不同,int和string类型,查询时发生了强制类型转换
计算和函数导致索引失效
or的查询条件存在非索引列等
%开头的like查询
四、sql优化经验
1、表结构优化、选择正确的数据类型
2、索引优化、索引创建原则
3、sql查询优化,避免索引失效,避免select *查询
4、主从复制,读写分离
5、分库分表
五、事务相关
1、事务特性
ACID: 一致性、隔离性、原子性、持久性
2、隔离级别
读未提交、读已提交、可重复读(默认隔离级别)、序列化
事务问题:脏读、不可重复度、幻读
3、MVCC
通过数据库中的隐藏字段,db_trx_id(事务id)、db_roll_ptr(回滚指针)记录修改的版本信息
回滚指针指向undo log日志。undo log日志回滚日志、记录数据的老历史版本,形成内部的版本链,从而支持事务的并发读写。
除此之外,通过readviewl快照读的机制,用于逻辑判断当前事务应该获取哪个版本的数据。其中可重复读是在事务开启的时候就生成一次readview,读已提交则是在每次读取数据的时候都生成readview。
redolog: 记录数据页的物理变化,用于宕机时同步数据。