按照这些优化技巧来写 SQL，连公司 DBA 也鼓掌称赞！
刚毕业的我们，都以为使用 MySQL 是非常的简单的，无非都是照着 【select from where group by order by】 这个格式套来套去；从来不会关注 SQL 的耗费时长，更不会关注查询的性能。
但是当用户量上来了，表数据不断暴增，导致我们以前写的 SQL 的查询时间越来越长，最后还被 DBA 和领导疯狂吐槽一波。那么，此时我们是不是应该学习一下如何去优化我们的烂 SQL 呢？
下面，我将从多方面去深入讲解如何优化 SQL 。
一、索引优化
索引的数据结构是 B+Tree，而 B+Tree 的查询性能是比较高的，所以建立索引能提升 SQL 的查询性能。
1、建立普通索引
对经常出现在 where 关键字后面的表字段建立对应的索引。
2、建立复合索引
如果 where 关键字后面常出现的有几个字段，可以建立对应的 复合索引。要注意可以优化的一点是：将单独出现最多的字段放在前面。
例如现在我们有两个字段 a 和 b 经常会同时出现在 where 关键字后面：
select from t where a = 1 and b = 2; * Q1 \
也有很多 SQL 会单独使用字段 a 作为查询条件：
select from t where a = 2; * Q2 \
此时，我们可以建立复合索引 index(a,b)。因为不但 Q1 可以利用复合索引，Q2 也可以利用复合索引。
3、最左前缀匹配原则
如果我们使用的是复合索引，应该尽量遵循 最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
假如此时我们有一条 SQL ：
select * from t where a = 1 and b = 2 and c > 3 and d = 4;
那么我们应该建立的复合索引是：index(a,b,d,c) 而不是 index(a,b,c,d)。因为字段 c 是范围查询，当 MySQL 遇到范围查询就停止索引的匹配了。
大家也注意到了，其实 a,b,d 在 SQL 的位置是可以任意调整的，优化器会找到对应的复合索引。
还要注意一点的是：最左前缀匹配原则不但是复合索引的最左 N 个字段；也可以是单列（字符串类型）索引的最左 M 个字符。
例如我们常说的 like 关键字，尽量不要使用全模糊查询，因为这样用不到索引；
所以建议是使用右模糊查询：select * from t where name like '李%'（查询所有姓李的同学的信息）。
4、索引下推
很多时候，我们还可以复合索引的 索引下推 来优化 SQL 。
例如此时我们有一个复合索引：index(name,age) ，然后有一条 SQL 如下：
select * from user where name like '张%' and age = 10 and sex = 'm';
根据复合索引的最左前缀匹配原则，MySQL 匹配到复合索引 index(name,age) 的 name 时，就停止匹配了；然后接下来的流程就是根据主键回表，判断 age 和 sex 的条件是否同时满足，满足则返回给客户端。
但是由于有索引下推的优化，匹配到 name 时，不会立刻回表；而是先判断复合索引 index(name,age) 中的 age 是否符合条件；符合条件才进行回表接着判断 sex 是否满足，否则会被过滤掉。
那么借着 MySQL 5.6 引入的索引下推优化 ，可以做到减少回表的次数。
5、覆盖索引
很多时候，我们还可以 覆盖索引 来优化 SQL 。
情况一：SQL 只查询主键作为返回值。
主键索引（聚簇索引）的叶子节点是整行数据，而普通索引（二级索引）的叶子节点是主键的值。
所以当我们的 SQL 只查询主键值，可以直接获取对应叶子节点的内容，而避免回表。
情况二：SQL 的查询字段就在索引里。
复合索引：假如此时我们有一个复合索引 index(name,age) ，有一条 SQL 如下：
select name,age from t where name like '张%';
由于是字段 name 是右模糊查询所以可以走复合索引，然后匹配到 name 时，不需要回表，因为 SQL 只是查询字段 name 和 age，所以直接返回索引值就 ok 了。
6、普通索引
尽量 使用普通索引 而不是唯一索引。
首先，普通索引和唯一索引的查询性能其实不会相差很多；当然了，前提是要查询的记录都在同一个数据页中，否则普通索引的性能会慢很多。
但是，普通索引的更新操作性能比唯一索引更好；其实很简单，因为普通索引能利用 change buffer 来做更新操作；而唯一索引因为要判断更新的值是否是唯一的，所以每次都需要将磁盘中的数据读取到 buffer pool 中。
7、前缀索引
我们要学会巧妙的使用 前缀索引，避免索引值过大。
例如有一个字段是 addr varchar(255)，但是如果一整个建立索引 [ index(addr) ]，会很浪费磁盘空间，所以会选择建立前缀索引 [ index(addr(64)) ]。
建立前缀索引，一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低，只要出生地一样，前面好多个字符都是一样的；这样的话，最不理想时，可能会扫描全表。
前缀索引避免不了回表，即无法使用覆盖索引这个优化点，因为索引值只是字段的前 n 个字符，需要回表才能判断查询值是否和字段值是一致的。
怎么解决？
倒序存储：像身份证这种，后面的几位区分度就非常的高了；我们可以这么查询：
select field_list from t where id_card = reverse('input_id_card_string');
增加 hash 字段并为 hash 字段添加索引。
8、干净的索引列
索引列不能参与计算，要保持索引列“干净”。
假设我们给表 student 的字段 birthday 建立了普通索引。
下面的 SQL 语句不能利用到索引来提升执行效率：
select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';
我们应该改成下面这样：
select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');
9、扩展索引
我们应该尽量 扩展索引，而不是新增索引，一个表最好不要超过 5 个索引；一个表的索引越多，会导致更新操作更加耗费性能。
二、SQL 优化
1、Order By 优化
order by 后面的字段尽量是带索引的，这样能避免使用 sort_buffer 进行排序。
假如有一条 SQL，根据生日查询所有学生的信息：select * from student order by birthday desc;
那么为了提升 SQL 的查询性能，我们可以为 birthday 字段建立索引：
CREATE INDEX index_birthday ON student(birthday);
select 后面不要带上不必要的字段，因为如果单行长度太长导致查询数据太多，MySQL 会利用 rowid 排序来代替全字段排序，这样会导致多了回表的操作。
如果我们只是查询学生的姓名、年龄和生日，千万不要写 select *;
而是只查询需要的字段：select name, age, birthday from student order by birthday desc;
2、Join 优化
在使用 join 的时候，应该让小表做驱动表。小表：总数据量最小的表
使用 join 语句，最好保证能利用被驱动表的索引，不然只能使用 BNL（Block Nested-Loop Join）算法，还不如不用。
启用 BKA（Batched Key Access） 算法，使得 NLJ 算法也能利用上 join_buffer，被驱动表可以批量查询到符合条件的值，然后可以利用 MMR（Multi-Range Read） 的顺序读盘特性来提升回表效率。
如果一定要用 join，而且被驱动表没有索引可以使用，那么我们可以利用临时表（create temporary table xx(...)engine=innodb;）来让 BNL 算法转为 BKA 算法，从而提升查询性能。
join_buffer 是一个无序数组，所以每次判断都需要遍历整个 join_buffer。我们可以在业务端实现 hash join 来提升 SQL 的执行速度。
3、Group By 优化
如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null。
尽量让 group by 过程用上表的索引，不但不需要临时表，还不需要额外的排序。
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表。
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
4、OR 优化
在 Innodb 引擎下 or 关键字无法使用组合索引。
假设现在关于订单表有一条 SQL ：
select id，product_name from orders where mobile = '12345678900' or user_id = 6;
一般我们为了提升上面 SQL 的查询效率，会想着为字段 mobile 和 user_id 建立一个复合索引 index(mobile,user_id)；
可是我们使用 explain 可以发现执行计划里面并没有提示到使用复合索引，所以 or 关键字无法命中 mobile + user_id 的组合索引。
那么我们可以分别为两个字段建立普通索引，然后采用 union 关键字，如下所示：
(select id，product_name from orders where mobile = '12345678900')
union
(select id，product_name from orders where user_id = 6);
此时 mobile 和 user_id 字段都有索引，查询才最高效。
5、IN 优化
in 关键字适合主表大子表小，exist 关键字适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了，可以尝试改为 join 查询。
假设我们现在有一条 SQL ，要查询 VIP 用户的所有订单数据：
select id from orders where user_id in (select id from user where level = 'VIP');
我们可以发现不会有任何关于索引的优化，所以我们可以采用 join查询，如下所示：
select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';
此时被驱动表应该是 user，那么可以利用到 user 表的主键索引，即可以使用 BKA 算法来提升 join 查询的性能。
6、Like 优化
like 用于模糊查询，但是如果是全模糊查询，将不能命中对应字段的索引。
假设现在关于学生表有一条 SQL：
SELECT name,age,birthday FROM student WHERE name like '%张%';
使用 explain 可以发现执行计划提示查询未命中索引。
因为本来需求就是查询姓张的所有同学信息，所以没必要使用全模糊查询，使用右模糊查询即可。
换成下面的写法：
SELECT name,age,birthday FROM student WHERE name like '张%';
但是产品经理一定要前后模糊匹配呢？全文索引 FULLTEXT 可以尝试一下，但是 MySQL 的全文索引不支持中文查询的。
所以说 Elasticsearch 才是终极武器！
三、数据表设计优化
1、数据类型：应该选择更简单或者占用空间更小的类型。
整型选择：可以根据长度选择 tinyint、smallint、medium_int，而不是直接使用 int。
字符串选择：能确定字符串长度的，尽量使用 char 类型，而不是变长的 varchar 类型。
浮点型选择：精度要求比较高的使用 decimal 而不是 double；也可以考虑使用 BIGINT 来保存，小数位保存可以使用乘以整百来解决。
日期选择：尽量使用 timestamp 而不是 datetime。
2、避免空值：
NULL 值依然会占用空间，并且会使索引更新更加复杂，更新 NULL 时容易发生索引分裂的现象。
可以使用有意义的值来代替 NULL 值，例如 “none” 字符串等等。
3、超长字符串：
一般超长字符串，varchar 难以存储，我们一般会使用 text 类型。
但是 text 类型的字段尽量避免放在主表中，而是抽出来在子表里，用业务主键关联。
最后
到此结束，如果大家还有更好的优化点，请记得在下方评论，一起学习~
原文地址https://blog.csdn.net/Howinfun/article/details/106019231
