1、先了解MySQL的执行过程

了解了MySQL的执行过程，我们才知道如何进行sql优化。

（1）客户端发送一条查询语句到服务器；

（2）服务器先查询缓存，如果命中缓存，则立即返回存储在缓存中的数据；

（3）未命中缓存后，MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树，MySQL解析器将使用MySQL语法进行验证和解析。例如，验证是否使用了错误的关键字，或者关键字的使用是否正确；

（4）预处理是根据一些MySQL规则检查解析树是否合理，比如检查表和列是否存在，还会解析名字和别名，然后预处理器会验证权限；

根据执行计划查询执行引擎，调用API接口调用存储引擎来查询数据；

（5）将结果返回客户端，并进行缓存；

2、数据库常见规范

（1）所有数据库对象名称必须使用小写字母并用下划线分割；

（1）所有数据库对象名称禁止使用mysql保留关键字；

（3）数据库对象的命名要能做到见名识意，并且最后不要超过32个字符；

（4）临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀；

（5）所有存储相同数据的列名和列类型必须一致；

3、所有表必须使用Innodb存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）。

展开全文

Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

4、每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的：

（1）不要使用更新频繁的列作为主键，不适用多列主键；

（2）不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）；

（3）主键建议使用自增ID值；

5、数据库和表的字符集统一使用UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集需要采用utf8mb4字符集。

6、查询SQL尽量不要使用select *，而是具体字段

select *的弊端：

（1）增加很多不必要的消耗，比如CPU、IO、内存、网络带宽；

（2）增加了使用覆盖索引的可能性；

（3）增加了回表的可能性；

（4）当表结构发生变化时，前端也需要更改；

（5）查询效率低；

7、避免在where子句中使用 or 来连接条件

（1）使用or可能会使索引失效，从而全表扫描；

（2）对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；

（3）也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；

（4）虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的；

8、尽量使用数值替代字符串类型

（1）因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

（2）而对于数字型而言只需要比较一次就够了；

（3）字符会降低查询和连接的性能，并会增加存储开销；

9、使用varchar代替char

（1）varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；

（2）char按声明大小存储，不足补空格；

（3）其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

10、财务、银行相关的金额字段必须使用decimal类型

非精准浮点：float,double

精准浮点：decimal

（1）Decimal类型为精准浮点数，在计算时不会丢失精度；

（2）占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节；

（3）可用于存储比bigint更大的整型数据；

11、避免使用ENUM类型

（1）修改ENUM值需要使用ALTER语句；

（2）ENUM类型的ORDER BY操作效率低，需要额外操作；

（3）禁止使用数值作为ENUM的枚举值；

12、去重distinct过滤字段要少

（1）带distinct的语句占用cpu时间高于不带distinct的语句

（2）当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据

（3）然而这个比较、过滤的过程会占用系统资源，如cpu时间

13、where中使用默认值代替null

（1）并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；

（2）如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；

（3）其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；

（4）如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

14、避免在where子句中使用!=或<>操作符

（1）使用!=和<>很可能会让索引失效

（2）应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描

（3）实现业务优先，实在没办法，就只能使用，并不是不能使用

15、inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

inner join 内连接，只保留两张表中完全匹配的结果集；

left join会返回左表所有的行，即使在右表中没有匹配的记录；

right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；

使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；

这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

16、提高group by语句的效率

（1）反例

先分组，再过滤

select job, avg（salary） from employee

group by job

having job ='develop' or job = 'test';

（2）正例

先过滤，后分组

select job，avg（salary） from employee

where job ='develop' or job = 'test'

group by job;

（3）理由

可以在执行到该语句前，把不需要的记录过滤掉

17、清空表时优先使用truncate

truncate table在功能上与不带 where子句的 delete语句相同：二者均删除表中的全部行。但 truncate table比 delete速度快，且使用的系统和事务日志资源少。

delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 drop table语句。

对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带 where子句的 DELETE 语句。由于 truncate table不记录在日志中，所以它不能激活触发器。