mysql查询优化（持续更新）

避免 select *

（如果不查询表中所有的列，避免使用SELECT *，它会进行全表扫描，不能有效利用索引）

最大数据量

MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。

《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。

最大并发数

并发数是指同一时刻数据库能处理多少个请求，由max_connections和max_user_connections决定。

max_connections是指MySQL实例的最大连接数，上限值是16384，max_user_connections是指每个数据库用户的最大连接数。

MySQL会为每个连接提供缓冲区，意味着消耗更多的内存。如果连接数设置太高硬件吃不消，太低又不能充分利用硬件。一般要求两者比值超过10%，计算方法如下：max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%

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

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

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

[mysqld]
max_connections = 100
max_used_connections = 20

查询耗时0.5秒

建议将单次查询耗时控制在0.5秒以内，0.5秒是个经验值，源于用户体验的3秒原则。如果用户的操作3秒内没有响应，将会厌烦甚至退出。响应时间=客户端UI渲染耗时+网络请求耗时+应用程序处理耗时+查询数据库耗时，0.5秒就是留给数据库1/6的处理时间。

原则：数据库少干活，应用程序多干活

1、充分利用但不滥用索引，须知索引也消耗磁盘和CPU。

2、不推荐使用数据库函数格式化数据，交给应用程序处理。

3、不推荐使用外键约束，用应用程序保证数据准确性。

4、写多读少的场景，不推荐使用唯一索引，用应用程序保证唯一性。

5、适当冗余字段，尝试创建中间表，用应用程序计算中间结果，用空间换时间。

6、不允许执行极度耗时的事务，配合应用程序拆分成更小的事务。

7、预估重要数据表（比如订单表）的负载和数据增长态势，提前优化。

数据类型的选择原则：更简单或者占用空间更小

1、如果长度能够满足，整型尽量使用tinyint、smallint、medium_int而非int。

2、如果字符串长度确定，采用char类型。

3、如果varchar能够满足，不采用text类型。

4、精度要求较高的使用decimal类型，也可以使用BIGINT，比如精确两位小数就乘以100后保存。

5、尽量采用timestamp而非datetime。

避免空值

MySQL中字段为NULL时依然占用空间，会使索引、索引统计更加复杂。从NULL值更新到非NULL无法做到原地更新，容易发生索引分裂影响性能。尽可能将NULL值用有意义的值代替，也能避免SQL语句里面包含is not null的判断。

text类型优化

由于text字段储存大量数据，表容量会很早涨上去，影响其他字段的查询性能。建议抽取出来放在子表里，用业务主键关联。

索引优化

mysql索引使用主要是b-tree和hash两种，最常用的是b-tree

b-tree类似于二叉树，表主键索引最好是自增整型，索引尽量使用数字型字段

hash类型索引效率最高，但只适用于等值查找，如果列确定只会等值查找，不会用于范围查找，才可以使用hash索引

分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引。

单表索引数不超过5个、单个索引字段数不超过5个。

字符串可使用前缀索引，前缀长度控制在5-8个字符。

字段唯一性太低，增加索引没有意义，如：是否删除、性别。

1、需要建立索引的列

where条件，group by ，order by ，表连接 on 的列，聚合函数（max、min、sum等）对聚合字段添加索引

2、索引字段越小越好

3、对于联合索引，数据量少并且离散度大的列(重复数据较少)的放在前面，而且sql语句查找顺序最好与联合索引的顺序一致

4、不适合建立索引的列

频繁更新的字段、where条件中用不到的字段、字段的差异性不大或重复性高

索引创建和使用原则

1、单表查询：哪个列作查询条件，就在该列创建索引

2、多表查询：left join时，索引添加到右表关联字段；right join时，索引添加到左表关联字段

3、不要对索引列进行任何操作（计算、函数、类型转换）

4、索引列中不要使用 !=, <>非等于

5、索引列不要为空，且不要使用 is null 或 is not null 判断

6、索引字段是字符串类型，查询条件的值要加 ' ' 单引号，避免底层类型自动转换

操作符<>优化

通常<>操作符无法使用索引，举例如下，查询金额不为100元的订单：

select id from orders where amount != 100;

如果金额为100的订单极少，这种数据分布严重不均的情况下，有可能使用索引。鉴于这种不确定性，采用union聚合搜索结果，改写方法如下：

(select id from orders where amount > 100) union all(select id from orders where amount < 100 and amount > 0)

OR 优化

在Innodb引擎下or无法使用组合索引，比如：

select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;

OR无法命中mobile_no + user_id的组合索引，可采用union，如下所示：

(select id，product_name from orders where mobile_no = '13421800407') union(select id，product_name from orders where user_id = 100);

此时id和product_name字段都有索引，查询才最高效。

IN 优化

IN适合主表大子表小，EXIST适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。

尝试改为join查询，举例如下：

select id from orders where user_id in (select id from user where level = 'VIP');

采用JOIN如下所示：

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

不做列运算

通询条件列运算会导致索引失效，如下所示：

select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';

date_format函数会导致这个查询无法使用索引，改写后：

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

Like 优化

like 用于模糊查询，举个例子（field已建立索引）：

SELECT column FROM table WHERE field like '%keyword%';

这个查询未命中索引，换成下面的写法：

SELECT column FROM table WHERE field like 'keyword%';

去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢？全文索引fulltext可以尝试一下，但Elasticsearch（基于Lucene的搜索服务器）才是终极武器。

Join 优化

1、驱动表和被驱动表尽可能增加查询条件，满足ON的条件而少用Where，用小结果集驱动大结果集。

2、被驱动表的join字段上加上索引，无法建立索引的时候，设置足够的Join Buffer Size。

3、禁止join连接三个以上的表，尝试增加冗余字段。

分页Limit 优化

limit用于分页查询时越往后翻性能越差，解决的原则：缩小扫描范围，如下所示：

select * from orders order by id desc limit 100000,10 
耗时0.4秒
select * from orders order by id desc limit 1000000,10
耗时5.2秒

先筛选出ID缩小查询范围，写法如下：

select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10
耗时0.5秒

如果查询条件仅有主键ID，写法如下：

select id from orders where id between 1000000 and 1000010 order by id desc
耗时0.3秒

order by 优化

可以在排序字段上添加索引来优化排序语句。

如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句。得注意排序字段的顺序与联合索引中列的顺序要一致

先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句

使用范围查询再排序，将无法使用索引

select id,a,b from t1 where a>9000 order by b;

ASC 和 DESC 混合使用将无法使用索引

select id,a,b from t1 order by a asc,b desc;

group by 优化

默认情况，会对 group by 字段排序，因此优化方式与 order by 基本一致，如果目的只是分组而不用排序，可以指定 order by null 禁止排序。

联合索引

可以完整用到联合索引的情况（假设建立索引：idx_a_b_c）

1、联合索引各字段都做为条件时，各字段的位置不会影响联合索引的使用。

select * from t11 where c=1 and b=1 and a=1;

2、当联合索引前面的字段使用了范围查询，后面的字段做为条件时仍然可以使用完整的联合索引。

select * from t11 where a=2 and b in (1,2) and c=2;

3、联合索引前面的字段做为条件时，对后面的字段做排序可以使用完整的联合索引。

select * from t11 where a=1 and b=2 order by c;

4、对联合索引第一个字段做条件筛选时，对后面两个字段做排序可以使用完整的联合索引。

select * from t11 where a=1 order by b,c;

5、对联合索引的字段同时做排序时（但是排序的三个字段顺序要跟联合索引中三个字段的顺序一致），可以完整用到联合索引

select a,b,c from t11 order by a,b,c;

只能使用部分联合索引的情况

1、当条件只包含联合索引的前面部分字段时，可以用到部分联合索引。

select * from t11 where a=1 and b=1;

2、对于联合索引 idx_a_b_c（a,b,c），如果条件中只包含 a 和 c，则只能用到联合索引中 a 的索引。c 这里是用不了索引的。

联合索引 idx_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 三种索引，称为联合索引的最左原则。

select * from t11 where a=1 and c=1;

3、当联合索引前面的字段使用了范围查询，对后面的字段排序使用不了索引排序，也就是只能用到联合索引前面两个字段 a 和 b 的索引。

select * from t11 where a=2  and b in (3,4) order by c;

不能使用联合索引的情况

1、如果只使用联合索引后面的字段做为条件查询，则使用不了联合索引（联合索引最左匹配）

select * from t11 where b=1;

2、联合索引中，如果第一个字段在条件中没有出现，那么联合索引的后面所有字段作为条件都无法使用这个联合索引。

select * from t11 where b=1 and c=1;

explain各列的含义

table: 表名，数据出自那张表

type: 查找类型，性能从好到差 const、eq_reg、ref、range、index、all

prossible_keys: 查找可能用的索引

key: 查找实际使用的索引

key_len: 索引的长度，长度越小越好

ref: 显示索引的哪一列被使用了

rows：mysql 查找的行数

extra: Using filesort 、 Using temporary 出现这两个值的时候，说明需要优化索引或者 sql语句太复杂了，最好拆成多个简单的sql