从原理上理解MySQL的优化建议

我们这里可以发现两个特点：

1、自增的数据插入影响的范围永远只有最右的子树，要么直接在子树插入节点，要么就是子树分裂，影响其父节点。

2、除了最右子树，其他子树的节点都是满的。

上面两个特点有什么影响呢？我们根据前面 B+ 树索引示意图可以知道，每个点都是一个磁盘块，操作每个节点相当于进行一次 IO,由于每次插入影响的节点只有最右子树，那么磁盘 IO 的范围就可控；最重要一点是除最右子树，其他子树的节点都是满的，这种情况，叶子节点数据的物理连续性会更好， 根据局部性原理,查询性能也会更高。

非自增 Id 插入情况：

非自增id

非自增 Id 插入特点对比自增 Id 插入我们很容易就能知道：

1、插入影响节点不可控，无法预知。

2、每个子树都存在叶子节点不满的情况。

按照之前的分析思路，我们也就知道了非自增 Id 插入有什么性能劣势了。由于插入数据影响节点不可控，导致节点分裂的情况就会更频繁，节点分裂也是 IO 操作，性能自然受到影响。子树的叶子节点不满，会导致叶子节点物理连续性不好。最后如果我们是UUID的话，Id 过长，会占用节点空间，每个页能存储的节点变少，页分裂变多，性能也会受到影响。这也是为什么建议使用自增主键的原因。

为什么不要使用 select * 查询

我们经常听到查询表，只要查询自己想要的字段，不需要的字段就不要查询，严禁使用 select *，我们能想到很直观的理由就是，数据库要帮你翻译成每个字段名去查询，接着查询多余的字段会占用内存，带宽等资源。这确实是一个理由，而且这个理由很重要，但是我这里想说的是另外一个原因，覆盖索引。我之前的一篇索引文章也介绍了覆盖索引，感兴趣的同学可以点击这里。覆盖索引的意思是指查询使用联合索引覆盖了要查询的字段，这样数据库不用去进行回表，从而减少IO,提高性能。

这里我用MySQL官方给的示列数据进行一个实验，数据地址下载可以点击这里。

我选择 employees 表数据进行演示，默认数据是没有联合索引的，我们加上一个联合索引：

---employee表结构-----------
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   | MUL | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
复制代码

ALTER TABLE employees.employees add Index `idx_first_name_last_name` (first_name,last_name);
复制代码

查看该表索引情况：show index from employees.employees;

YxQ4yQ.png

表已经成功创建了first_name 和 last_name的符合索引，我们开启 profiles 监控 SQL 执行的情况。

SET SESSION profiling = 1;
复制代码

然后分别执行以下SQL

SELECT first_name,last_name  FROM employees.employees WHERE first_name='Eric';
SELECT *  FROM employees.employees WHERE first_name='Eric';
复制代码

查看Profiles；

profiles

这里我们看到使用 select * 比 select 字段慢了4倍左右，为什么会这样呢？我们看看执行计划。

执行计划

我们看到两者的执行计划几乎一样，只有 Extra 有区别，使用字段的 Extra 显示 using index，这就告诉你只使用索引就找到了想要的数据，因为你的索引就是用 first_name 和 last_name 建立的， 而 select * 它还需要查询 gender和hire_date字段（主键字段不用额外查，辅助索引指向的就是主键）,所以它不能不进行回表查询其他字段，性能差异也是这里。

总结

本文从原理上分析了我们日常的两点建议，为什么建议使用自增主键？为什么不建议使用 select * 查询？其实主要最终的原因还是和索引相关，既然我们用索引来提高我们的效率就要充分利用它，下面是知识点总结：

1、B+ 树查询的效率高低是受其树高影响，树的高度越低，查询IO次数越少，性能相对也就越高。

2、执行计划的类型由好到差依次是：system > const > eq_ref > ref > full_text > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > all 。

3、自增主键的好处就是连续，插入维护的成本相对较低，同时子树的叶子节点大部分是满节点，物理连续性好，查询性能更优。

4、UUID 主键长度过长，导致单个子节点存储的主键变少，更平凡的出发页分裂，影响性能，这也是为什么建议索引不要太长的原因。

5、覆盖索引是很好的优化技巧，可以让查询直接通过索引返回数据，而不用回表，减少IO，提升性能。

参考

1、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

2、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

3、http://blog.codinglabs.org/articles/theory-of-mysql-index.html