MySQL 知识整理

思维导图

思维导图链接

https://www.yuque.com/docs/share/a54bf660-4b7f-44c1-8115-7562705fc623?# 《MySQL 知识整理》

思维导图内容

• MySQL

• SQL 优化

• 最大化利用索引

• 联合索引

• 最左匹配原则

• 覆盖索引

• 即，要查询的字段均在一个联合索引内，可以解决回表的问题，

• 回表

• 使用非主键索引查询时，会先根据普通索引查出主键索引，再根据主键索引查出其他内容。

• 慎用 join

• 让缓存更高效

• 方便数据拆分，更容易高性能和高扩展

• 对先查出来的 id 进行升序排序，再进行 where in，查询效率比 join 的随机关联效率更高

• 单张表可减少锁的竞争

• 非用不可

• ON/USING 字句中的列上有索引

• 任何的 GROUP BY 和 ORDER BY 中的表达式只包含一个表的列

• 索引列不能参与运算

• 索引参与运算后，会导致索引失效

• 例如：from_unixtime(create_time) = ‘2014-05-29’ 可以优化成：create_time = unix_timestamp(‘2014-05-29’)

• 尽可能避免全表扫描

• 切分 SQL

• 一个大的 SQL 拆分为多个小SQL，所有的 SQL 都走索引

• 谨慎使用临时表

• MySQL 的临时表没有索引，一般性能较低

• 可以通过 explain 查看 Extra 列的结果，出现 Using temporary 则表示使用了临时表

• 优化分页 LIMIT

• LIMIT offset, row 在 offset特别大的时候，会导致性能很慢

• 利用索引的 WHERE ORDER BY + LIMIT ，因为主要是 offset 的问题，所以每次利用上次已经取出来的最大值进行筛选，就可以避免 offset 过大的问题

• 减少无效数据查询

• 对于 select * 保持谨慎

• COUNT(col) 不包含 NULL 数据，可以使用 COUNT(*)

• explain

• 使用 SQL_NO_CACHE 用于指定不使用缓存，可以校验修改是否有效

• 索引N叉树(N一般为1200)

• 聚集索引(主键索引)

• 存放着主键及行记录的全部信息

• 单列索引

• 组合索引

• 最左前缀原则

• 索引(a,b,c)，相当于建了a、(a,b)、(a,b,c)

• 覆盖索引

• 即：需要查询的内容及条件都在索引中，故不要回表即可查询到想到的内容

• 唯一索引

• 全文索引

• 可用于字符串模糊匹配，性能远高于 LIKE + %，但是可能存在精度问题

• 使用方式：MATCH(col) ANAINST(’’)

• 自然语言全局搜索

• 布尔全文所搜MATCH(content) ANAGINST(‘XXX*’ in boolean mode);

• • 必须包含该词
• • 必须不包含该词

• 提高该词的相关性，查询的结果靠前

• < 降低该词的相关性，查询的结果靠后

• (*)星号 通配符，只能接在词后面

• 优缺点

• 优点：提高查询效率，索引的选择：列的区分度越高越好

• 缺点：降低了插入修改效率，并且增大了空间占用。一般：一个表的索引数控制在5个左右，一个组合索引控制在 5 个字段以下

• 存储引擎

• 存储

• 表定义(*.frm)MySQL 8 已废弃

• 数据、索引(*.ibd)

• 隔离级别

• READ UNCOMMITED：使用查询语句是不会加锁，可能读到未提交的行(Dirty Read)

• READ COMMITED：只对记录加锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）

• REPEATABLE READ：多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读；MySQL 的默认级别，但是它通过 Next-Key 锁也能够在某种程度上解决幻读的问题。

• 幻读：事务1第一次查询 id 为 1 的记录不存在，事务2这是插入了 id 为1的数据，然后事务2紧接着插入 id 为1的数据，发现插入失败，即：查询不出来，但是实际存在。

• SERIALIZABLE：InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题。

• 锁的算法

• Record Lock : 记录锁，是加到索引记录上的锁，如果当前过滤条件是索引则在索引记录上加锁，否则锁定整个表？

• Gap Lock：间隙锁；对索引记录的一段连续区域上锁；

• 当使用类似：SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE; 的语句时，就会组织其他事务向表中插入 id = 15 的记录，因为整个范围都被间隙锁锁定了

• 只在事务级别为 RR(读重复) 时才会产生

• 唯一索引只有在锁住多条记录或者一条不存在的记录时才会产生间隙锁；普通索引不管是锁住的单条，还是多条，都会产生间隙锁。

• Next-Key Lock：是记录锁和记录前的间隙锁的结合

• 数据存储原理

• 持久化

• binlog

• MySQL 层面的日志，记录了数据库系统所有的更新操作，主要是用来实现数据库恢复和主从复制。

• redolog

• InnoDB 存储引擎的日志，用于实现事务的持久性，即当事务提交时，必须先将该事务的所有操作写到磁盘上的 redo log file 进行持久化，也就是 Write Ahead Log (WAL)。

• 日志持久化：innodb_flush_log_at_trx_commit 参数

• 0：由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。

• 1：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。

• 2：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

• undolog

• binlog 和 undolog 一致性问题

• 在事务提交时，采用两阶段提交；redo log 记录事务 Prepare，bin log 写入并持久化，redo log 增加 commit 标签 。

• 崩溃恢复：如果 redo log 没有 commit 标签，并且 binlog 最后的 事务 ID 也不是此 ID 则，回滚事务；如果 binlog 中已写入，则以 binlog 为准，重新写入 commit 标签  。

参考链接

MySQL索引原理及慢查询优化

MySQL的SQL性能优化总结

SQL优化最干货总结 - MySQL（2020最新版）

『浅入浅出』MySQL 和 InnoDB

MySQL事务和持久化原理

MySQL 之全文索引

MySQL的锁机制 - 记录锁、间隙锁、临键锁