mysql语句优化

一.执行计划

可参考这篇博客:https://blog.csdn.net/wuseyukui/article/details/71512793

二.索引类型

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
普通索引：Normal建立在单列或多列上，用于加快查询速度，一张表可建立多个普通索引。在查询时经常引用，且数据区分度较高（单个条件扫描记录小于10%）的字段上应建立普通索引。
组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
注:索引并不是越多越好！索引是需要消耗存储和CPU资源的,如果数据更新频繁，索引越多，更新就越慢，过多的索引也会干扰sql优化器，导致语句分析时间变长，建立在低区分度上的索引反而会降低查询速度。

2.1 使用索引技巧

1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引（与其说是“数据库查询只能用到一个索引”，倒不是说是 和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。）。
4.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算.函数右移
select name from table where date_format(date,‘%Y%m%d’)=‘20190321’ 改写为 select name from table where date=str_to_date(‘20190321’,’%Y%m%d’)
6.union代替or
select name from table where id=1 or id=2改写为 select name from table where id=1 union select name from table where id=2
7使用内连接代替【not】in
select name from tableA where id in(select id from tableB) 改写为 select a.name from tableA a inner join tableB b on a.id=b.id

2.2 sql解析顺序（序号越小执行越先）

(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(4) <join_type> JOIN <right_table>
(2) ON <join_condition>
(3) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
注：执行顺序并非一成不变的，优化器在实际运行中可能会调整执行顺序！

优化经验总结

1、索引应该建立在经常作为查询条件的字段上，联合索引应该将最常使用的字段放在索引最左边。
2、Mysql按照从左至右的顺序解析where条件，应该将能过滤更多数据的条件写在左边
3、Left join 查询中应该将数据集更少的记录放在前面
4、避免书写导致的索引失效的查询条件
5、select 语句中避免使用*，应该明确列出需要使用的字段
6、全表删除时用truncate代替delete
7、如有可能尽量用 exists 代替查询条件中in 语句（可以用内连接用内接）,group by 代替distinct
8、多表连接查询中,明确标记字段来源
9、用union all 代替 union （明确数据无重复的情况）
10、减少使用order by 如非必要不要在数据库进行排序
11、连接查询中，在关联条件不是主键或者索引的情况下，join语句要先筛选（在被join的表里where过滤）再join其他表。
12、复合索引的情况下，如果查询条件不包含索引列的最左边部分，即不满足最左前缀原则，则不会使用索引。如： select * from table1 where col1=A AND col2=B AND col3=D 如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引