Mysql 优化专题

因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作

这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作

这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:

select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时

如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询

如需要生成一个空表结构：select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

13.区分in和exists、not in和not exists

select * from 表A where id in (select id from 表B)

上面SQL语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原SQL语句：

select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：

select colname … from A表 Left join B表 on where a.id = b.id and b.id is null

14.并不是所有索引对查询都有效

SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15. 索引并不是越多越好

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16. 应尽可能的避免更新 clustered 索引数据列

因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段

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

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

因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t

用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。

如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

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

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效

例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时

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

24.在使用到了临时表时

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

25.尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前

应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27. 与临时表一样，游标并不是不可使用。

对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。

无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

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

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

31.当只要一行数据时使用 LIMIT 1，这是为了使EXPLAIN中type列达到const类型

32. EXPLAIN 你的 SELECT 查询

33.为搜索字段建索引

34.当你需要在一篇大的文章中搜索一个词时

如： “WHERE post_content LIKE ‘%apple%’”，索引可能是没有意义的。你可能需要使用MySQL全文索引或是自己做一个索引(比如说：搜索关键词或是Tag什么的)

创建全文索引的SQL语法是：

ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的SQL语句是：

select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

35. 在Join表的时候使用相当类型的例，并将其索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。(两个表的字符集有可能不一样)

36. 千万不要 ORDER BY RAND()

想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里

的问题是：MySQL会不得不去执行RAND()函数(很耗CPU时间)，而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序)

select id from `dynamic` order by rand() limit 1000;

上面的SQL语句，可优化为：

select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid limit 1000;

37. 尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。

首先，问问你自己“Empty”和“NULL”有多大的区别(如果是INT，那就是0和NULL)?如果你觉得它们之间没有什么区别，那么你就不要使用NULL。(你知道吗?在 Oracle 里，NULL 和 Empty 的字符串是一样的!)

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

38. 越小的列会越快

对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

如果一个表只会有几列罢了(比如说字典表，配置表)，那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。

当然，你也需要留够足够的扩展空间，不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子(2009年11月06日)，一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。

39. 选择正确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

下面是MySQL的手册

target=”_blank”MyISAM Storage Engine

InnoDB Storage Engine

40.mysql强制索引和禁止某个索引

1、mysql强制使用索引:force index(索引名或者主键PRI)

例如:

select * from table force index(PRI) limit 2;(强制使用主键)

select * from table force index(ziduan1_index) limit 2;(强制使用索引”ziduan1_index”)

select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引”PRI和ziduan1_index”)

2、mysql禁止某个索引：ignore index(索引名或者主键PRI)

例如:

select * from table ignore index(PRI) limit 2;(禁止使用主键)

select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引”ziduan1_index”)

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)

强制索引案例：

详见：初步优化：为order_level,input_date 创建复合索引

41、limit分页优化

利用表的覆盖索引加速分页查询，利用索引查询的语句中如果只包含索引列（覆盖索引），会查询很快
在例子中，id字段为主键，包含了默认的主键索引
利用覆盖索引，只包含id列，查询最后一页的数据

select id from product limit 866613, 20 0.2秒

相对于查询所有页的37.44秒，提升了100多倍的速度
若要查询所有列，有两种方法:
1）id>=形式

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

查询时间为0.2秒

2）利用join （延迟关联）

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

或

SELECT
	A .*
FROM
	relation A,
	(
		SELECT
			ID
		FROM
			relation
		WHERE
			biz_type = '0'
		AND end_time >= '2014-05-29'
		ORDER BY
			ID ASC
		LIMIT 149420,
		20
	) b
WHERE
	A . ID = b. ID;

3)利用书签（https://mp.weixin.qq.com/s/qtWGOu46_ShYtuTMJLHB4A）

首先要获取复合条件的记录的最大 id和最小id(默认id是主键)

select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;

其次根据id 大于最小值或者小于最大值进行遍历。

select xx,xx from t where kid=2333 and type=1 and id >=min_id order by id asc limit 100;

select xx,xx from t where kid=2333 and type=1 and id <=max_id order by id desc limit 100;

42、In List

select * from mytable where id in(:id1,id2,...,idn);

一般IN里面的值个数超过20个以后性能基本没什么太大变化，也特别说明不要超过100，超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本，这个需要专业DBA评估。注：在比较高的版本5.6，mysql查询会自动把in查询优化成joint查询，就不会出现子查询比较慢的问题。有时候也可以采用distinct关键词来限制子查询的数量，但是需要注意的是distinct很多时候会转化为group by，这个时候就会出现一个临时表，就会出现copy数据到临时表的时延。

43、合理设置fetchsize

fetchsize不能设置太大，如果一次取出的数据大于JVM的内存会导致内存溢出，所以建议不要超过1000，太大了也没什么性能提高，反而可能会增加内存溢出的危险

44、合理使用存储过程

当业务数据量很大的时候（也就是需要将数据库里大数量的记录传输到应用程序里）使用存储过程，这样可以减少网络交互的成本。

45、合理使用排序

以下列出了可能会发生排序操作的SQL语法：

Order by

Group by

Distinct

Exists子查询

Not Exists子查询

In子查询

Not In子查询

Union（并集），Union All也是一种并集操作，但是不会发生排序，如果你确认两个数据集不需要执行去除重复数据操作，那请使用Union All 代替Union。

Minus（差集）

Intersect（交集）

Create Index

Merge Join，这是一种两个表连接的内部算法，执行时会把两个表先排序好再连接，应用于两个大表连接的操作。如果你的两个表连接的条件都是等值运算，那可以采用Hash Join来提高性能，因为Hash Join使用Hash 运算来代替排序的操作。具体原理及设置参考SQL执行计划优化专题。

46、减少比较操作

我们SQL的业务逻辑经常会包含一些比较操作，如a=b，a<b之类的操作，对于这些比较操作数据库都体现得很好，但是如果有以下操作，我们需要保持警惕：

Like模糊查询，如下所示：

a like ‘%abc%’

Like模糊查询对于数据库来说不是很擅长，特别是你需要模糊检查的记录有上万条以上时，性能比较糟糕，这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。

不能使用索引定位的大量In List，如下所示：

a in (:1,:2,:3,…,:n) ----n>20

如果这里的a字段不能通过索引比较，那数据库会将字段与in里面的每个值都进行比较运算，如果记录数有上万以上，会明显感觉到SQL的CPU开销加大，这个情况有两种解决方式：

a、将in列表里面的数据放入一张中间小表，采用两个表Hash Join关联的方式处理；

b、采用str2varList方法将字段串列表转换一个临时表处理，关于str2varList方法可以在网上直接查询，这里不详细介绍。

以上两种解决方案都需要与中间表Hash Join的方式才能提高性能，如果采用了Nested Loop的连接方式性能会更差。

如果发现我们的系统IO没问题但是CPU负载很高，就有可能是上面的原因，这种情况不太常见，如果遇到了最好能和DBA沟通并确认准确的原因。

47、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

48、尽量用union all代替union

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

49、关于JOIN优化（https://mp.weixin.qq.com/s/oKg4aNl5bjbgniXseEPL4w）

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

1）MySQL中没有full join，可以用以下方式来解决：

select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

2）尽量使用inner join，避免left join：

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

3）合理利用索引：

被驱动表的索引字段作为on的限制字段。

4）利用小表去驱动大表

5）巧用STRAIGHT_JOIN：

inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。

LIMIT 语句

在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件

// 原语句

select * from operation where type='type' and name='name' order by create_time limit 100000 10;

// 优化
select * from operation where type='type' and name='name' and create_time > '2017-03-16 00:00:00' order by create_time limit 10

隐式转换

性能调优

分库分表

注：单表行数超过500万行或者单表容量超过2GB，才推荐分库分表

https://www.cnblogs.com/winkey4986/p/6824747.html

当一个数据文件过大的时候，操作系统对大文件的操作就会比较麻烦与耗时，而且有的操作系统就不支持大文件，所以这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，B+树就会从层次和节点上比较多，当查询一个节点的时候可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。

所以综上我们就必须进行分表与分库的操作。

分库分表利器——sharding-sphere

https://www.jianshu.com/p/952108f777a3

永不迁移数据、避免热点的分库分表

https://mp.weixin.qq.com/s/-ve2IXPd1S4jKZfUPMWxnA

缓存的配置

show variables like '%cache%'

在这里主要分析query_cache_size，它是主要用来缓存查询数据。当你想使用该cache，必须把query_cache_size大小设置为非0。当设置大小为非0的时候，server会就会缓存每次查询返回的结果，到下次相同查询server就直接从缓存获取数据，而不是再执行查询。能缓存的数据量就和你的size大小设置有关，所以当你设置的足够大，数据可以完全缓存到内存，速度就会非常之快。

但是，query cache也有它的弊端。当你对数据表做任何的更新操作(update/insert/delete)等操作，server为了保证缓存与数据库的一致性，会强制刷新缓存数据，导致缓存数据全部失效。所以，当一个表格的更新数据表操作非常多的话，query cache是不会起到查询提升的性能，还会影响其他操作的性能。

索引优化

详见：https://blog.csdn.net/weixin_37946518/article/details/88868851

最大数据量

建议单表行数不要超过500万行或者单表容量不要超过2GB，超过建议分库分表

最大并发数

计算公式：max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3% // 一般要求两者比值超过10%

查看最大连接数与响应最大连接数：

show variables like '%max_connections%';
show variables like '%max_user_connections%';

在配置文件my.cnf中修改最大连接数

[mysqld]
max_connections = 100
max_used_connections = 20

查询耗时不超0.5秒

实施原则

实施原则是数据库少干活，应用程序多干活。

性能分析

slow_query_log分析

show variables like '%slow_query%'

当你设置slow_query_log为on的时候，server端会对每次的查询进行记录，当超过你设置的慢查询时间 (long_query_time)的时候就把该条查询记录到日志。而你对性能进行优化的时候，就可以分析慢查询日志，对慢查询的查询语句进行有目的的优化。

善用EXPLAIN查看SQL执行计划

select_type列，查询类型是简单查询，简单的select语句没有union和子查询。
type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。结果值从好到坏依次是：system > const（唯一索引） > eq_ref > ref（普通索引） > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式(可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引)。
key_len列，索引长度。在不损失精确性的情况下，长度越短越好
rows列，扫描行数。该值是个预估值。
extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort（文件排序），Using temporary。