优化SQL的前提是：SQL写的正确

索引

创建

  • 多查少改的列
  • 数据差异化大的列
  • 索引会降低insert及update效率(重建索引)，最好不要超过6个

使用

  • 如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时，并且应尽可能的让字段顺序与索引顺序相一致

  • 不在索引列上使用函数，将结果直接计算出来后再进行比较

  • 禁止更新clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
    • 指定为PRIMARY KEY或UNIQUE约束创建聚集或非聚集索引。PRIMARY KEY 约束默认为 CLUSTERED；UNIQUE约束默认为 NONCLUSTERED。如果表中已存在聚集约束或索引，那么在 ALTER TABLE 中就不能指定 CLUSTERED。如果表中已存在聚集约束或索引，PRIMARY KEY 约束默认为 NONCLUSTERED。
  • 尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  • 正确书写where条件
    • 尽量不使用
      • 操作符!=、<>、!<、!>
      • 判断：is null或is not null、NOT、NOT EXISTS、NOT IN、NOT LIKE
      • 连接条件：where a or b，用union或union all替代
      • 通配符：%a%
    • 对字段进行表达式操作
      • where num/2=100 -> where num=200
    • 对字段进行函数操作
      • substring(name,1,3)='abc' -> where name like 'abc%'
    • =左边进行函数、算术运算或其他表达式运算
    • 将能减少结果集数量的条件放最左侧
  • 慎重使用in和not in
    • 连续数值，用between替代in
    • exists或not exists替代
    • left join替代
  • 判断是否存在符合条件的数据
    • 利用limit 1、top 1取得一行
  • 只查询真正需要的数据
    • 用实际使用的数据列名替代*
    • 如果只需要很少的行数，用limit来限制返回数量
      • 查询是否存在符合条件的数据
      • 分页查询
1
2
3
4
5
6
7
8
9
--wrong
SELECT * FROM users WHERE age > 20;
--right
SELECT id, last_name, sex, age FROM users WHERE age > 20;

--wrong
SELECT name, price FROM products;
--right
SELECT name, price FROM products LIMIT 10;
  • 减少不必要的子查询
1
2
3
4
---wrong
SELECT user_id, last_name FROM users WHERE EXISTS (SELECT * FROM donationuser WHERE donationuser.user_id = users.user_id);
---right
SELECT DISTINCT users.user_id FROM users INNER JOIN donationuser ON users.user_id = donationuser.user_id;

需要结合使用场景，如果查询大部分行，使用inner join效率更高。

  • like优化
    • 左通配符是否必须
    • 使用全文搜索替代(MySQL 5.7+，支持中文)
1
2
3
4
---wrong
SELECT * FROM users WHERE name LIKE '%bar%';
---right
SELECT * FROM users WHERE name LIKE 'bar%';
  • 尽量用 union all替换 union
    • 涉及排序，增加运算量
    • 确认不可能出现重复结果或不在乎重复时
  • 避免类型转换，数据库将不使用索引
    • 非要类型转换，在传入的参数上进行转换
  • 尽量使用数字型字段
    • 若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  • 尽可能的使用 varchar/nvarchar 代替 char/nchar
    • 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  • limit优化
1
2
3
4
5
6
7
8
9
---很快
SELECT id FROM A LIMIT 1000,10
---很慢
SELECT id FROM A LIMIT 90000,10
---优化
---用索引
select id from A order by id limit 90000,10;
---用between替换
select id from A order by id  between 10000000 and 10000010;
  • 批量插入优化
1
2
3
4
5
6
---wrong
INSERT into person(name,age) values('A',14)
INSERT into person(name,age) values('B',14)
INSERT into person(name,age) values('C',14)
---right
INSERT into person(name,age) values('A',14),('B',14),('C',14),
  • 查询语句不知道效率如何，建议使用EXPLAIN。
1
2
3
4
5
6
--查询语句如何运行
EXPLAIN sql
--当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。在MySQL5.0及更新的版本里都可以使用，在MySQL5.1里它有额外增加了一个过滤列(filtered)。
EXPLAIN EXTENDED sql
--JSON格式显示结果
EXPLAIN FORMAT=JSON (5.6新特性)

例如：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE new12306 null ALL null null null null 7141154 100.0 null

解释：

id SELECT识别符。这是SELECT的查询序列号
select_type SELECT类型,可以为以下任何一种:
SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)
table 输出的行所引用的表
type 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
system:表仅有一行(=系统表)。这是const联接类型的一个特例。
const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys 指出MySQL能使用哪个索引在该表中找到行
key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref 显示使用哪个列或常数与key一起从表中选择行。
rows 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered 显示了通过条件过滤出的行数的百分比估计值。
Extra 该列包含MySQL解决查询的详细信息
Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

注意：在其它场景，语句会修改数据(INSERT、UPDATE、DELETE、EXECUTE)，可以用事务。

1
2
3
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
  • 性能优化器 profile
1
2
3
4
5
6
7
8
9
10
--查询是否开启
SELECT @@profiling;
--设置开启
SET profiling = 1;
--执行一次查询
select a,b,c from tableName;
--查看结果
show profiles;
--查看当前查询的信息, 2为执行语句所对应的Query ID
show profile for query 2;

其它阅读

三分钟测试SQL水平

参考文档：

5 tips to optimize your SQL Queries

mysql explain用法和结果的含义