MySQL优化

前言

SQL优化中就三点:

• 最大化利用索引
• 尽可能避免全表扫描
• 减少无效数据的查询

一、了解各种SQL 的执行频率

1.1 SHOW STATUS

show[session|global] status 可以根据需要加上参数“session”或者“global”来显示session 级（当前连接）的统计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

Com_xxx 表示每个xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

• Com_select：执行select 操作的次数，一次查询只累加1。
• Com_insert：执行INSERT 操作的次数，对于批量插入的INSERT 操作，只累加一次。
• Com_update：执行UPDATE 操作的次数。
• Com_delete：执行DELETE 操作的次数。

1.2 SHOW PROCESSLIST

通过该命令来查看当前mysql在进行的线程，

1.3 EXPLAIN

我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

如果id一样，则表示从上而下加载，如果id值不同，则越大表示优先级越高。

mysql查看性能工具explain中type有很多种，主要的有：     type的值对优化很重要

链接类型	说明
system	表只有一行，MyISAM引擎。这是const类型的特例
const	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext	全文搜索
ref_or_null	与ref类似，但包括NULL
index_merge	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery	在in子查询中，就是value in (select…)把形如select unique_key_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
index_subquery	同上，但把形如”select non_unique_key_column“的子查询替换
range	常数值的范围
index	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描(full table scan)

从下到上，越来越好

 

二、优化order by 语句

2.1 mysql中的两种排序方式

• 通过有序索引顺序扫描直接返回有序数据，不需要额外的排序，操作效率较高。因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index。
• Filesort排序，对返回的数据进行排序：所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort。

ORDER BY优化的核心原则：尽量减少额外的排序，通过索引直接返回有序数据。

示例：先查看userinfo表中索引情况

order by 优化

2.2 查询的字段，应该尽可能只包含此次查询使用的索引字段和主键，其余的非索引字段和索引字段作为查询字段则不会使用索引。

2.3 排序字段在多个索引中,无法使用索引排序,查询一次只能使用一个索引：

所以，只查询索引字段和主键时，可以利用索引来排序。

MySQL默认的InnoDB引擎在物理上采用聚集索引这种方式，按主键进行搜索，所以InnoDB引擎要求表必须有主键，即使没有显式指定主键，InnoDB引擎也会生成唯一的隐式主键，也就是说索引中必定有主键。

2.4 排序字段顺序与索引列顺序不一致,无法利用索引排序

2.5 ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。

 

三、优化GROUP BY语句

3.1 首先看是否有临时表的创建，临时表会使索引失效

• 如果GROUP BY 的列没有索引,产生临时表
• 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表
• 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表
• 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表
• 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表
• 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表

可以看到这里不仅创建了临时表，还进行了文件排序

学习了ORDER BY优化，就知道文件排序效率低，那么可以在这里使用ORDER BY NULL来禁止排序

对于不创建临时表的优化可以针对上文例举会产生临时表的情况进行优化。

四、优化嵌套查询

子查询有些情况下可以被更有效的连接（JOIN）替代。因为连接（JOIN）不需要再内存中创建临时表来完成

示例：

通过以下sql来查询有角色的用户信息，此时就是通过子查询来完成的

看一下执行情况

当使用连接来完成上述查询时

五、优化OR语句

对于or语句，如果要利用索引，则or之间的每个条件都必须有索引。并且不走复合索引，必须是单列索引

首先来看一下userinfo表中的索引情况

示例：  此时发现是全表扫描

当or的条件是索引时，range指的是有范围的索引扫描，相对于index的全索引扫描

优化：使用UNION 来替换OR     type变成了const，而const的效率是远高于range的

六、索引提示

6.1 USE INDEX()

在你查询语句中表名的后面，添加 USE INDEX 来提供你希望 MySQ 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

还是先看一下表中索引情况：一个包括name的复合索引，一个name的单例索引

此时查询mysql要判断使用哪个索引

当我们在sql中添加use index()， 去给mysql提供参考，注意仅仅是提供参考

6.2 IGNORE INDEX()

和USE INDEX()正好相反，这个是不想用哪个索引，用法是一样的。

6.3 FORCE INDEX()

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

七、模糊查询

查看表中索引情况，在REALNAME字段上已经创建索引。

7.1 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

八、尽量不要使用!= 和 <>

如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

九、隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

此时应该加上双引号

select col1 from table where col_varchar=123;

十、调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。