mysql知识点（自用）

目录

一、定位慢查询

二、sql执行计划

三、索引

1、存储引擎

2、索引底层数据结构

3、聚簇和非聚簇索引

4、索引创建原则

5、索引失效场景

四、sql优化经验

五、事务相关

1、事务特性

2、隔离级别

3、MVCC

六、主从同步原理

七、分库分表

---

一、定位慢查询

项目中，开发环境中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: 记录数据页的物理变化，用于宕机时同步数据。

六、主从同步原理

七、分库分表