msyql调优方式

一、 表设计规范

1. 库名、表名、字段名必须使用小写字母，“_”分割。
2. 库名、表名、字段名必须不超过12个字符。
3. 库名、表名、字段名见名知意,建议使用名词而不是动词。
4. 建议使用InnoDB存储引擎。
5. 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
6. 建议使用UNSIGNED存储非负数值。
7. 建议使用INT UNSIGNED存储IPV4。
8. 整形定义中不添加长度，比如使用INT，而不是INT(4)。
9. 使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。
10. 不建议使用ENUM类型，使用TINYINT来代替。
11. 尽可能不使用TEXT、BLOB类型。
12. VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。
13. VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。
14. 表字符集选择UTF8。
15. 使用VARBINARY存储变长字符串。
16. 存储年使用YEAR类型。
17. 存储日期使用DATE类型。
18. 存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。
19. 建议字段定义为NOT NULL。
20. 将过大字段拆分到其他表中。
21. 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。
22. 表结构变更需要通知DBA审核。

二、 索引规范

1. 非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名。
2. 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名。
3. 索引名称必须使用小写。
4. 索引中的字段数建议不超过5个。
5. 单张表的索引数量控制在5个以内。
6. 唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
7. 没有唯一键或者唯一键不符合5中的条件时，使用自增（或者通过发号器获取）id作为主键。
8. 唯一键不和主键重复。
9. 索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。
10. ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
11. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，UsingTemporary。
12. UPDATE、DELETE语句需要根据WHERE条件添加索引。
13. 不建议使用%前缀模糊查询，例如LIKE “%weibo”。
14. 对长度过长的VARCHAR字段建立索引时，添加crc32或者MD5 Hash字段，对Hash字段建立索引。
15. 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
16. 合理利用覆盖索引。
17. SQL变更需要确认索引是否需要变更并通知DBA。

18. 虽然索引的可以提高查询的效率，但是在进行insert，update，和delete的时候会降低效率，因为在保存数据的同时也会去保存索引。

    不要在一个表里建过多的索引，问题跟上面一样，在操作数据的时候效率降低，而且数据量少的表要看情况建索引，如果建索引跟没建索引的效果差不多少的情况下就不要建索引了，如果是数据量大的表，就需要建索引去优化查询效率。

  此处引用https://www.cnblogs.com/zjfjava/p/6920407.html  致谢！

1.select语句务必要指明字段名称

select * 增加很多不必要的消耗（cpu，io，内存，网络带宽等）；增加了使用覆盖索引的可能行；当表的结构发生修改时，前端也需要更新。所以要求直接在select后面接上字段名。尽量避免select * 这种操作，若不知可

select * from 表 limit 10；（只是查看对应表数可展示部分操作使用 limit函数）

2.当只要一条数据的时候，用limit 1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

这是为了使explain中type列达到const类型。

3.使用合理的分页方式以提高分页的效率

select id,name from product limit 866613, 20

4.围绕避免sql进行全表扫描

1.避免在where子句中对字段进行null的判断，对于null的判断会导致引擎放弃索引而进行全表扫描

select id,name from product limit 866613, 20

2.不建议使用%前缀的模糊查询

select * from student where name like ‘%三‘;

select * from student where reverse(name) like reverse(‘%三‘);

3.避免在where子句中对字段进行表达式操作

select user_id,user_project from user_base where age*2=36;

可优化为：
select user_id,user_project from user_base where age=36/2;

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

select id from student where id!=‘5‘;

5.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

 select id from t where num=10 or num=20

 可以这样查询： select id from t where num=10 union all select id from t where num=20

6.in 和 not in 也要慎用，否则会导致全表扫描。

 select id from t where num in(1,2,3)

 对于连续的数值，能用 between 就不要用 in 了：

 select id from t where num between 1 and 3

7.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where substring(name,1,3)=’abc’–name

select id from t where datediff(day,createdate,’2005-11-30’)=0–‘2005-11-30’生成的id 应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30’ and createdate<’2005-12-1’

8.尽量用union all而不是union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗和延迟。
前提：union all是在两个结果集没有重复条件下，否则会有重复数据的。

9.避免隐式类型转换

比如a=1还是a=‘1‘
要提前确认数据格式，避免转换格式

10.如果排序字段没有用到索引，就尽量少排序

11.避免频繁创建和删除临时表，以减少系统表资源的消耗。

12.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

13.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

14.通过explain查看sql执行计划

eg:

1. type列，连接类型。一个好的sql语句至少要达到range级别，杜绝all级别
2. key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
3. key_len列，索引长度
4. rows列，扫描行数。该值是个预估值
5. extra列，详细说明。常见的不友好的值有：Using filesort,Using temporary。

此博客介绍执行计划特别详细可以学习： https://www.cnblogs.com/klvchen/p/10137117.html