SQL优化
分析SQL执行效率（上）
如何定位慢 SQL:
一种方法是查看慢查询日志
一般分为四步：开启慢查询日志、设置慢查询阀值、确定慢查询日志路径、确定慢查询日志的文件名。
mysql> set global slow_query_log = on;
mysql> set global long_query_time = 1;
mysql> show global variables like “datadir”;
mysql> show global variables like “slow_query_log_file”;另一种方法是 show process 查看正在执行的 SQL
通过 explain 分析慢 SQL，explain 会返回很多字段，其中 select_type、type、key、rows、Extra 是重点关注项。
select_type 查询类型：显示本行是简单还是复杂查询
type 本次查询的表连接类型
key 实际选择的索引
rows 预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
Extra 附加信息
分析SQL执行效率（下）
- explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
- profile：可以清楚了解到SQL到底慢在哪个环节；
- trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。
order by、group by优化
MySQL 的两种排序方式：通过有序索引直接返回有序数据；通过 Filesort 进行排序
建议优先考虑索引排序。
而Filesort又分为两种： 内存排序；磁盘文件排序
优先考虑内存排序。
Filesort 有三种排序模式：
< sort_key, rowid >
< sort_key, additional_fields >
< sort_key, packed_additional_fields >
order by 语句的优化，这个是本节的重点：
- 通过添加合适索引
- 去掉不必要的返回字段
- 调整参数：主要是 max_length_for_sort_data 和 sort_buffer_size
- 避免几种无法利用索引排序的情况
最后说到 group by 语句的优化，如果只要分组，没有排序需求的话，可以加 order by null 禁止排序。
分页查询优化
本节讲到了两种分页查询场景的优化：
- 根据自增且连续主键排序的分页查询优化
其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录 - 查询根据非主键字段排序的分页查询优化
join 语句优化
- NLJ 算法的思想：一次一行循环地从 驱动表 中读取行，根据关联字段在 被驱动表 里取出满足条件的行，然后取出两张表的结果合集。如果在 被驱动表 中这个关联字段没有索引，都会对 被驱动表 做一次全表扫描，成本是非常高的。
- BNL 算法的思想：将驱动表一次性放入join buffer中，与被驱动表一条条关联
- explain 分析 join 语句时，在第一行的就是驱动表；选择 t2 做驱动表的原因：如果没固定连接方式（比如没加 straight_join）优化器会优先选择小表做驱动表。所以使用 inner join 时，前面的表并不一定就是驱动表。（潜在意思：外连接驱动表是确定的！）
- 被驱动表的关联字段有索引，使用 Nested-Loop Join(简称：NLJ) ；没索引，使用 Block Nested-Loop Join(简称：BNL)
优化关联查询：
- 关联字段添加索引；让 BNL变成 NLJ 或者 BKA，可以提高 join 的效率。
- 小表做驱动表
优化数据导入
- 一次插入多行的值；
- 关闭自动提交，多次插入数据的 SQL 一次提交；
- 调整参数，innodb_flush_log_at_trx_commit 和 sync_binlog 都设置为0（当然这种情况可能会丢数据）。
MySQL索引
索引基础
B+ Tree 原理
- 数据结构
B Tree 是一颗查找树，并且所有叶子节点位于同一层；B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现。 - 操作
查找：在根节点进行二分查找，找到一个 key 所在的指针，然后在指针所指向的节点递归查找，直到找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
插入删除操作会破坏平衡树的平衡性，因此在进行插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性。 - 与红黑树的比较
平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以 B+ Tree 的树高非常小。
磁盘寻道的次数与树高成正比（why?），寻道需要移动制动手臂，耗时较多。
B+Tree 索引
因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。
InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
- InnoDB 中 B+ 树索引分为聚簇索引和辅助索引；聚簇索引是按照每张表的主键构造一颗 B+ 树。
- 在数据库中，B+ 树的高度一般都在 2 ~ 4 层，所以查找某一行数据最多只需要 2 到 4 次 IO。而没索引的情况，需要逐行扫描，明显效率低很多，这也就是为什么添加索引能提高查询速度。
索引优化
- 应该避免隐式转换，不建议对条件字段做运算及函数操作；
- like查询不能以%开头；
- 多列索引时，让选择性最强的索引列放在前面；
- 对于特大型的表，建立和维护索引的代价将会随之增长，通常使用分区技术。
范围查询时，包含的数据比例不能太大
普通索引和唯一索引
- 有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。
- 数据修改时，普通索引优于唯一索引，因为普通索引可以用 Change Buffer，并且 RR 隔离级别下，出现死锁的概率比唯一索引低。
- 查询数据时，两者性能差别不大。
哪些情况需要添加索引？
- 数据检索时在条件字段添加索引
- 聚合函数对聚合字段添加索引
- 对排序字段添加索引
- 关联查询在关联字段添加索引
MySQL锁
封锁粒度
MySQL 中提供了两种封锁粒度：行级锁以及表级锁。
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。
但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。
在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。
封锁类型
- 读写锁
- 互斥锁（Exclusive），简写为 X 锁，又称写锁。
- 共享锁（Shared），简写为 S 锁，又称读锁。
- 意向锁（在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。）
意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：- 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
- 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。
封锁协议
三级封锁协议
- 一级封锁协议
事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题 - 二级封锁协议
在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以解决读脏数据问题 - 三级封锁协议
在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁（同一事务内多次读取）。可以解决不可重复读的问题
两段锁协议
加锁和解锁分为两个阶段进行。
多版本并发控制
Multi-Version Concurrency Control, MVCC 是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。
基本思想
在封锁一节中提到，加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。
在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。
脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。
版本号
- 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
- 事务版本号 TRX_ID ：事务开始时的系统版本号。
Undo 日志
MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。
例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。
1 | INSERT INTO t(id, x) VALUES(1, "a"); |
因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。
INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。
ReadView（算法过程）
MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。
在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：
- TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
- TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
- TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
- 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
- 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。
在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。
快照读与当前读
- 快照读
MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。 - 当前读
MVCC 会对数据库进行修改的操作（INSERT、UPDATE、DELETE）进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。
MySQL的一些其它经验
常用函数
- 时间格式化处理： select DATE_FORMAT(NOW(),’%Y-%m-%d’)
- 字符串转时间： select STR_TO_DATE(‘2020-05-20 00:00:00’,’%Y-%m-%d %H:%i:%s’)
- 计算两个时间的天数差： DATEDIFF(NOW(),ow.post_date)
- 分组拼接： group_concat()
- 字段判空： IFNULL( ,0)
一些sql
1 | show tables; show tables from database_name; -- 显示当前数据库中所有表的名称。 |