MySQL 索引

索引存储结构

InnoDB 的 B+ 树

每一个索引在 InnoDB 里面对应一棵 B+ 树。B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

为何可以降低 IO ?

InnoDB 叶子节点，数据页是双向，单条数据之间单向。

InnoDB 索引一次读多少？

这个 16kb 是 innodb 默认的页大小，为什么会有这个概念呢，因为当涉及到数据库读写的时候，规定数据库每次读写都是以 16k 为单位的，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

计算 IO 次数

我们每次 IO 都是读取数据到内存中进行一些计算。当我们遍历 主键索引的B+树 查找数据的时候， IO 次数是近似于 B+ 树的层数 -1，因为根节点是一直在内存中的。

基本上可以理解为，每次 io 都是在树的一层查找符合的 id 范围的页数据，通过对比页里面的最大最小主键来确定下层的查找范围。

MyISAM 的 B+ 树

MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

使用这个存储引擎，每个 MyISAM 在磁盘上存储成三个文件。

frm 文件：存储表的定义数据
MYD 文件：存放表具体记录的数据
MYI 文件：存储索引

B 树和 B+ 树

B 树示例：

B+ 树示例：

B 和 B+ 树的区别在于，B+ 树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

B+ 树的优点在于：

由于 B+ 树在内部节点上不好含数据信息，因此在内存页中能够存放更多的 key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子几点上关联的数据也具有更好的缓存命中率。
每次都需要查询到叶子节点，查询性能稳定
B+ 树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而 B 树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有 B+ 树好。

但是 B 树也有优点，其优点在于，由于 B 树的每一个节点都包含 key 和 value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

回表

回到主键索引树搜索的过程，我们称为回表。

select * from T where k between 3 and 5

覆盖索引

ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

select ID from T where k between 3 and 5

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

假设某个表有如下主键和索引：

PRIMARY KEY (`emp_no`),
KEY `i_hire_date` (`hire_date`)

那么语句 explain select * from employees where hire_date > '1990-01-14'; 的 Extra 是 Using where，表示没有用上索引；而语句 explain select emp_no from employees where hire_date > '1990-01-14'; 的 Extra 是 Using where; Using index，用上了索引，可见 SELECT 的字段位于索引字段中对于性能是多么的重要。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

上述联合索引对于如下查询请求，也是可以满足的：

查到所有名字是“张三”的人
查的是所有名字第一个字是“张”的人

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，如何安排索引内的字段顺序 ?

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
考虑空间占用问题

索引下推

索引：联合索引（name, age）。

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

前缀索引

直接创建完整索引，这样可能比较占用空间；使用前缀索引 (定义字符串的一部分作为索引)，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，但会增加查询扫描次数，并且不能使用覆盖索引。

mysql> alter table SUser add index index1(email);

mysql> alter table SUser add index index2(email(6));

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

mysql> select 
  count(distinct left(email,4)) as L4,
  count(distinct left(email,5)) as L5,
  count(distinct left(email,6)) as L6,
  count(distinct left(email,7)) as L7,
from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

优化语句步骤

通过 EXPLAIN 分析 SQL 执行计划

假设现在我们使用 EXPLAIN 命令查看当前 SQL 是否使用了索引，先通过 SQL EXPLAIN 导出相应的执行计划如下：

下面对图示中的每一个字段进行一个说明。

id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
partitions：访问的分区表信息。
type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
possible_keys：可能使用到的索引。
key：实际使用到的索引。
key_len：当前使用的索引的长度。
ref：关联 id 等信息。
rows：查找到记录所扫描的行数。
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息。

type 具体示例

system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。

eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。

ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。

range：索引范围扫描，比如，<，>，between 等操作。

index：索引全表扫描，此时遍历整个索引树。

ALL：表示全表扫描，需要遍历全表来找到对应的行。

Extra 解释

Using where：表示并没有用上索引
Using where; Using index：表示用上了索引
Using index condition：表示用上了索引下推

通过 Show Profile 分析 SQL 执行性能

上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。

Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。以下是相关命令的注释：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示CPU的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS：显示swap交换次数的相关开销信息

Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。

获取到 Query_ID 之后，我们再通过 Show Profile for Query ID 语句，就能够查看到对应 Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了：

左连接、右连接、内连接、外连接

在 MySQL 中，JOIN、CROSS JOIN 和 INNER JOIN 是等价的。INNER JOIN 和 , 是等价的，都是从笛卡尔积中挑选出满足 ON 条件的，即 A 表和 B 表的交集。

RIGHT JOIN 和 LEFT JOIN 工作方式类似，但是为了让你的 SQL 在多个数据库中有良好的兼容性，推荐优先使用 LEFT JOIN。

LEFT JOIN 不满足 ON 条件的会保留左边那张表的数据，右边表数据直接显示 NULL。

FULL OUTER JOIN 表示两个表组合在一起，左右不相匹配时使用空值替换，求的也就是 A 表和 B 表的并集。MYSQL 不支持这种语法，但是可以使用 UNION 达到效果。

有张图比较形象：

经典场景 SQL 写法

部门工资最高的员工

Employee 表：
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+

Department 表：
+----+----------+
| Id | Name     |
+----+----------+

使用 JOIN 和 IN：

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

使用 LEFT JOIN 和 IN:

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
LEFT JOIN
    Department ON Employee.DepartmentId = Department.Id 
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
AND Department.name is not null
;

语句优化改写示例

最左前缀匹配

CREATE INDEX idx_a_b ON t1(a, b, c);

WHERE a = ? -- √
WHERE a = ? AND b = ? -- √
WHERE a = ? AND c = ? -- 仅仅用上了 a 索引

范围查找

WHERE ... BETWEEN ... AND ...
> -- √
< -- √
IN (...) -- IN 不属于范围查找的范畴

JOIN/ON/USING 列

-- ON、USING 确保存在索引

对于 JOIN 的优化，用数据量小的表去驱动数据量大的表，这样可以减少内循环个数，也就是被驱动表的扫描次数。用来进行 join 的字段要加索引，会触发 INLJ 算法，如果是主键的聚簇索引，性能最优。如果无法使用索引，那么注意调整 join buffer 大小，适当调大些；减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

WHERE

CREATE INDEX idx_a_b_c ON t1(a, b, c);

--              ↓ (索引只会用上 a, b 索引)
WHERE a = ? AND b < 5000 AND c > 10000;
--                        ↓ (索引用上 a,b,c 索引)
WHERE a = ? AND b = ? AND c > 10000;

ORDER BY

CREATE INDEX idx_a_b_c ON t1(a, b, c);

ORDER BY a, b, c; -- 确定值
WHERE a = ? AND b = ? ORDER BY c; -- 确定值

-- 确保使用索引排序，如果没有用上，那么 EXPLAIN 会出现 filesort
-- 增加 sort_buffer_size：每个排序线程缓冲区大小

GROUP BY 避免排序

-- ORDER BY NULL 避免排序
GROUP BY cluster_id ORDER BY NULL LIMIT 10;

IN

-- MySQL 会排序 IN 列表、然后二分查找定位数据
-- IN 列表不易过长，200 个以内

-- IN 转化为多个 = 查询，例如
SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b);

-- 程序实现
SELECT id FROM table_b;
SELECT id FROM table_a WHERE id = ?;

UNION

-- UNION 语句默认是移除重复记录的， 需要用到排序操作
-- 尽量使用 UNION ALL

LIKE

-- 《MySQL 管理之道》
LIKE 'xxx%' -- 可以用上索引
LIKE '%xxx%' -- 不可以用上索引

OR 语句

-- 《MySQL 管理之道》

-- OR 条件不会用上索引
SELECT * FROM USER WHERE name = 'd' OR age = 41;

-- 改为 UNION ALL
SELECT * FROM USER WHERE name = 'd'
UNION ALL
SELECT * FROM USER WHERE age = 41;

索引失效

字段使用函数
类型转换
模糊查询
不使用组合索引的首个字段当条件
范围查询量比较大的时候，超过 20%

-- （1）字段使用函数，索引失效
--                                      ↓ (采用了函数)
SELECT create_time FROM t1 WHERE DATE(create_time) = curdate();

SELECT create_time FROM t1 WHERE
-- ↓ (WHERE 后面的字段并未采用函数)
create_time > DATE_FORMAT(CURDATE, '%Y-%m-%d');

-- （2）类型转换，索引失效
--                                        ↓ (应该加上引号)
SELECT * FROM player_info WHERE name = 104515967;
SELECT * FROM player_info WHERE name = '104515967';

-- （3）取出的数据量超过表中的数据 20% 的时候，索引失效
-- MYSQL 认为全表扫描更快
SELECT * FROM employees WHERE hire_date > '1989-06-02';

In 和 Exists 效率

--                        ↓ (查出 B 表所有 ID 并放到内存)
--             B 表数据量大，不适合用 in 语句
--             in 语句适合，B 表数据量比 A 表数据量小的情况
select * from A where id in (select id from B);
--                       ↓ （不缓存 exist 结果集）
--             B 表数据量大，适合用 exist 语句，因为执行 O(A) 次
--   通常情况，exist 效率高于 in，in 不走索引
--
--   EXISTS 用于检查子查询是否至少会返回一行数据，
--   该子查询实际上并不返回任何数据，
--   而是返回值 True 或 False
select * from A where exists (select 1 from B where A.id=B.id);

子查询优化分页查询

通常我们是使用 + 合适的 order by 来实现分页查询，这种实现方式在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能将会非常得糟糕。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性能就越差。

这是因为我们在使用 LIMIT 的时候，偏移量 M 在分页越靠后的时候，值就越大，数据库检索的数据也就越多。例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，最后返回 10 条记录。也就是说将会有 10000 条记录被查询出来没有被使用到。

select * from `demo`.`order` order by order_no limit 10000, 20;

通过 EXPLAIN 分析可知：该查询使用到了索引，扫描行数为 10020 行，但所用查询时间为 0.018s，相对来说时间偏长了。

以上分页查询的问题在于，我们查询获取的 10020 行数据结果都返回给我们了，我们能否先查询出所需要的 20 行数据中的最小 ID 值，然后通过偏移量返回所需要的 20 行数据给我们呢？我们可以通过索引覆盖扫描，使用子查询的方式来实现分页查询：

select * from `demo`.`order` where id > (select id from `demo`.`order` order by order_no limit 10000, 1)  limit 20;

通过 EXPLAIN 分析可知：子查询遍历索引的范围跟上一个查询差不多，而主查询扫描了更多的行数，但执行时间却减少了，只有 0.004s。这就是因为返回行数只有 20 行了，执行效率得到了明显的提升。

using filesort

-- （1）
-- 只有 pid 一个索引是不行的，会存在 filesort
-- 必须建立联合索引 (pid, change_date) 才可以
SELECT * FROM t1 WHERE pid = 12345 ORDER BY change_date;

-- （2）多个字段排序，一个降序，一个升序也会出现 filesort
-- 将 ASC 改为 DESC 即可。顺序必须一致
SELECT * FROM t1 WHERE pid = 12345 
ORDER BY change_date DESC, delta_rmb ASC;

表设计优化

存储手机号

char(11); -- 一般字符集是 utf8，utf8 占用 3 个字节，11 * 3 = 33 字节
bigint(20); -- 宽度 20，只占用 8 字节，所以推荐 bigint

IP 地址

INET_ATON(); -- IP 地址转为数字
INET_NTOA(); -- 数字转为 IP 地址

INT(11) UNSIGNED; -- 必须采用 UNSIGNED 否则会溢出

增加/删除/更新字段

-- VARCHAR(5) -> VARCHAR(10)
-- 参考自《MySQL 管理之道》
-- 注意此种方法对 DECIMAL 类型无效
-- (1) 创建临时表
CREATE TABLE T1_TMP(
  	ID INT,
  	NAME VARCHAR(10)
);
-- (2) 替换 .frm 表结构文件
-- 锁定表，防止表被打开
FLUSH TABLES WITH READ LOCK;
cp /usr/local/mysql/data/book/t1_tmp.frm
	/usr/local/mysql/data/book/t1.frm
-- (3) 测试
UNLOCK TABLES;
INSERT INTO t1 VALUES (2, 'zhaokun');
-- (4) 观察表结构
SHOW CREATE TABLE t1\G
-- (5) 如果有主从同步，那么 slave 也需要执行，否则会报错

反范式设计

反范式化是针对范式化而言的，在前面介绍了数据库设计的范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。

索引选择

(a, b) 和 (b, a)

table 建立了两个联合索引 (a, b) 和 (b, a)，下面语句走哪一个索引？

SELECT a, b FROM table WHERE a = x AND b = x;

都有可能。以成本分析器统计结果为准，哪个区分度高就走哪一个。如果区分度差不多，那就走建表语句中靠前的索引。可以通过 explain format = json 查看详细的查询成本统计结果。