1 字段选择

1.1.尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

1.2 尽可能的使用 varchar/nvarchar 代替 char/nchar

因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来

1.3 时间字段使用datetime

不适用timesstamp

比如时间字段：datetime和timestamp，datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间

1.4 禁止使用Bob或text

1.5 varchar 长度小于4000

1.6禁止字符类型放入时间

1.7禁止字符类型放入数字

1.8 禁止表字段 单独定义字符集

1.9 禁止使用哪个ENUM 使用TINYINNT代替

1.10 总结

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。
  简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。
• 尽量避免NULL：通常情况下最好指定列为NOT NULL

2 大小

2.1 单库和索引不查过500G

2.2 单库表不超过150个

2.3 单表字段不超过80个

2.4 单表不超过1500W行

3 拒绝数据库本身功能

3.1 分区功能

3.2 触发器

• 触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；
• 如果能够使用约束实现的，尽量不要使用触发器；
• 不要为不同的触发事件(Insert，Update和Delete)使用相同的触发器；
• 不要在触发器中使用事务型代码。

3.3 外键

3.4 存储过程

3.5 禁止使用视图

视图无法完成sql审核和优化，容易产生性能问题。

4 索引相关

4.1 is null和 is not null不能使用索引

应尽量避免在where子句中对字段进行null值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默认值

4.2 like （%abc%）会导致索引失效

4.3 or 是困难谓词（可以使用in（mysql优化了in可以使用索引））

否则将导致引擎放弃使用索引而进行全表扫描

4.4 左前缀原则

4.5 查询字符串 不加引号会使索引失效

4.6 在索引上计算（计算，函数，手动转换）会导致索引失效

4.7 避免在整个表上使用count(*) ，它可能会将整个表锁住

4.8 在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引

4.9 避免select * ，最小化你要查询的数据，只获取你需要的数据

4.10 in 和 not in 也是困难谓词

4.11 应尽量避免在 where 子句中使用!=或<>操作符

4.12 表的索引不超过5个

4.13 尽量避免使用否定形式

• <>
• !=
• NOT IN

所以以下 了SQL 语句会导致全表扫描

SELECT *  FROM SomeTable WHERE col_1 <> 100;

可以改成以下形式

SELECT *  FROM SomeTable WHERE col_1 > 100 or col_1 < 100;

MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE

4.13 表join 应有索引

4.14 避免类型转换和隐式转换

这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换：

人为在column_name 上通过转换函数进行转换直接导致 MySQL(实际上其他数据库也会有同样的问题)无法使用索引，如果非要转换，应该在传入的参数上进行转换，由数据库自己进行转换

假设 col 是 char 类型，则推荐使用以下第二，三条 SQL 的写法，不推荐第一条 SQL 的写法

× SELECT * FROM SomeTable WHERE col_1 = 10;○ SELECT * FROM SomeTable WHERE col_1 = '10';○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

虽然第一条 SQL 会默认把 10 转成 ‘10’，但这种默认类型转换不仅会增加额外的性能开销，还会导致索引不可用，所以建议使用的时候进行类型转换。

4.14.1 函数引起的 索引失效

4.14.2 隐式类型转换 字符串到int 不能使用索引

4.14.3 隐式字符编码转换

———— 字符集utf8mb4是utf8的超集， 所以当这两个类型的字
符串在做比较的时候， MySQL内部的操作是， 先把utf8字符串转成utf8mb4字符集， 再做比较

4.15 保证索引简单，不要在同一列上加多个索引

4.16 MySQL 会选择错误的索引，这种情况使用 USE INDEX

4.17 很多时候用exists代替in是一个好的选择

4.18 如果合适，用 GROUP BY 代替 DISTINCT

加了索引之后 distinct 比没加索引的 distinct 快了 107倍。
加了索引之后 group by 比没加索引的 group by 快了 43倍。
再来对比 ：distinct 和 group by

不管是加不加索引 group by 都比 distinct 快。因此使用的时候建议选 group by

5 查询优化

5.1 保持查询一致，这样后续类似的查询就能使用查询缓存了

5.2 关联代替子查询

5.3 尽量避免大事务操作，提高系统并发能力

5.4 不要有超过5个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过2个为宜。

5.5 当只要一行数据时使用LIMIT 1

5.6 在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面

减少判断的次数

5.7 尽量使用“>=”，不要使用“>”

5.8 多表关联必须使用表的别名， 别名的使用，别名是大型数据库的应用技巧

就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍

5.9 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边

5.10 SQL中的in不超过500个

5.11 尽量用union all代替union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

5.12 LIMIT M,N 在特定场景下会降低查询效率，有节制使用。

SELECT * FROM film LIMIT 100000, 10

offset 特别大!

这是我司出现很多慢 SQL 的主要原因之一，尤其是在跑任务需要分页执行时，经常跑着跑着 offset 就跑到几十万了，导致任务越跑越慢。

LIMIT 能很好地解决分页问题，但如果 offset 过大的话，会造成严重的性能问题，原因主要是因为 MySQL 每次会把一整行都扫描出来，扫描 offset 遍，找到 offset 之后会抛弃 offset 之前的数据，再从 offset 开始读取 10 条数据，显然，这样的读取方式问题。

可以通过延迟查询的方式来优化

假设有以下 SQL,有组合索引（sex, rating）

SELECT <cols> FROM profiles where sex='M' order by rating limit 100000, 10;

则上述写法可以改成如下写法

SELECT <cols>   FROM profiles inner join(SELECT id form FROM profiles where x.sex='M' order by rating limit 100000, 10)as x using(id);

这里利用了覆盖索引的特性，先从覆盖索引中获取 100010 个 id，再丢充掉前 100000 条 id，保留最后 10 个 id 即可，丢掉 100000 条 id 不是什么大的开销，所以这样可以显著提升性能

5.13 避免使用 ORDER BY RAND()。

)

上面的查询，会导致每条记录都执行rand()，成本很高
建议，通过mt_rand()，先确定的随机主键，再从数据表中获取数据。

5.14 应尽量避免在where子句中使用or来连接条件，使用UNION代替

否则将导致引擎放弃使用索引而进行全表扫描，
select id from t where num=10 union all select id from t where num=20

5.15 使用 UNION 来代替 WHERE 子句中的子查询

5.16 使用索引字段和 ORDER BY 来代替 MAX ， max() 效率更高，无论这个字段有没有索引

6 其他

6.1 重启 MySQL 时，记得预热数据库。

确保将数据加载到内存，提高查询效率。

6.2 当服务器的负载增加时，使用SHOW PROCESSLIST来查看慢的/有问题的查询

6.3 使用 DROP TABLE ，然后再 CREATE TABLE ，而不是 DELETE FROM ，以删除表中所有数据

6.4 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率

6.5 select num from a where num in(select num from b)。用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)。

https://blog.csdn.net/u011225585/article/details/42555549
select count(*)和select count(1)的区别

对 UPDATE 来说，使用 SHARE MODE 来防止排他锁

6.6 update 、delete 不使用limit

56 update 、delete 不使用order by 。group by

6.7 使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 来代替 UPDATE，避免 UPDATE 前需要先 SELECT

6.8 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样以避免系统表的较长时间锁定

7 数据库参数调优

sysbench：一个模块化，跨平台以及多线程的性能测试工具。
iibench-mysql：基于 Java 的 MySQL、Percona、MariaDB 索引进行插入性能测试工具。
tpcc-mysql：Percona开发的TPC-C测试工具。

具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：

7.1 back_log

backlog值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到maxconnections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即backlog，如果等待连接的数量超过backlog，将不被授予连接资源。可以从默认的50升至500。

7.2 wait_timeout

数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时。

7.3 maxuserconnection

最大连接数，默认为0无上限，最好设一个合理上限thread_concurrency：并发线程数，设为CPU核数的两倍。

7.4 skipnameresolve

禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问。

7.5keybuffersize

索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like’keyread%’，保证keyreads / keyreadrequests在0.1%以下最好。

7.6 innodbbufferpool_size

缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like ‘Innodbbufferpoolread%’，保证(Innodbbufferpoolreadrequests – Innodbbufferpoolreads)/Innodbbufferpoolreadrequests 越高越好。

7.7 innodbadditionalmempoolsize

InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小。

7.8 innodblogbuffer_size

InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB。

7.9 querycachesize

缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcachehits/(Qcachehits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。

可以通过命令show status like ‘Qcache_%’查看目前系统Query catch使用大小。

7.10 readbuffersize

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能。

7.11 sortbuffersize

MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sortbuffersize变量的大小。

7.12 readrndbuffer_size

MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

7.13 record_buffer

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值。

7.14 threadcachesize

保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的。

7.15 table_cache

类似于threadcachesize，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM。

参考

1 https://blog.csdn.net/heqinghua217/article/details/78600967