在MySQL优化中，最常做的事情就是SQL语句优化，因为这个才是影响性能的最主要因素。
explain语句
对SQL语句进行优化少不了使用explain分析SQL语句。下面先来说说怎么使用explain语句。
explain语法
explain语法比较简单，只需要在你要分析的SQL语句前面加上explain即可，如：
explainselectid,namefromtb_user；
SQL
explain语句输出的每一行为对一个语句的分析，来看看每行有哪些输出：
mysql> explain select * from test \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra:
1 row in set (0.00 sec)
这里为了方便查看结果使用了\G代替分号。从上面可以看出explain的每行分析结果有10列，下面逐一分析每列的作用。
id
代表select语句的编号, 如果是连接查询，表之间是平等关系，select编号相同。如果某select中有子查询，则编号基于主查询递增。例如：
mysql> explain select * from (select c1 from test) as tmp \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: test
type: index
possible_keys: NULL
key: c1234
key_len: 4
ref: NULL
rows: 6
Extra: Using index
2 rows in set (0.00 sec)
select_type
select_type表示select语句的类型，取值有如下几种：
simple：简单语句，不含子查询
primary：含有子查询的语句
subquery：非from型子查询语句
derived：from型子查询语句
union：union的语句
union result：union的结果
table
table即该语句所查询的表，可能的取值有：
实际的表名
表的别名：如explain select * from t2 as tmp，则table为tmp
derived：from型子查询时
NULL：直接计算得结果，不用走表
type
type指查询的方式，非常重要，是分析数据查询过程的重要依据。可能的值如下：
ALL：意味着对全表逐行扫描，运气不好扫描到最后一行，性能最低。
index：比all性能稍好一点，通俗的说: ALL扫描所有的数据行（扫描整本书），index扫描所有的索引节点（扫描书的全部目录），其实可以理解为index_all。
range：查询时，能根据索引做范围的扫描（扫描书的部分目录），可理解为index_range。
ref：通过索引列，可以直接引用到某些数据行（定位到某些行的数据范围）。
eq_ref：通过索引列，直接引用某1行数据（定位到某行的数据位置），常见于连接查询中。
const,system,null：这3个分别指查询为常量级别, 甚至不需要查找时间。一般按照主键来查询时，易出现const,system，或者直接查询某个表达式，不经过表时, 出现NULL。
以上取值代码的性能越来越高，所以我们的优化目标应该是将SQL语句优化到常量级别，最好则是不使用SQL查询。
possible_keys
possible_keys指开始查询前估计可能使用的索引。
key
key指实际查询时所使用的索引。
key_len
key_len指实际查询时所使用的索引的长度。因为对于多列索引可能只会用到其中的部分列，用这个看出来用了哪些列。
ref
在表的连接匹配时，哪些列或常量被用于查找索引列上的值。
rows
估计查询结果的行数，MySQL根据表统计信息及索引选用情况，估算找到所需的记录所需要读取的行数。
Extra
查询的额外信息，比较重要。取值为下面的一项或多项：
useing index：使用了索引覆盖，效率非常高。
using where：光靠索引定位不了，还使用了where辅助判断。
using temporary：使用了临时表，当group by与order by不同列时会出现。
using filesort：使用文件排序(文件可能在磁盘,也可能在内存)，当数据量大时性能较低，要避免这种情况。
注：如果取出的列，含有text,blob或者更大的如mediumtext等，filesort将会发生在磁盘上。
in型子查询的陷阱
MySQL针对in型子查询做了优化，将in改成了exists子查询的执行效果。
执行过程不是我们直观想象的：先执行in子查询取出所有的数据，然后执行主查询判断每个数据是否在in取出的数据中。
而实际上的执行过程是：先执行主查询取出数据，然后遍历每个数据，将每个数据使用exists查询，这会每次拿着数据去in子查询表中查询该数据是否存在。
当in子查询表数据越多时, 查询速度越慢，我们可以使用连接查询代替in型子查询。如果in子查询表数据很少，使用in问题不大，甚至性能比连接查询要好。
limit及翻页优化
我们可能会经常使用limit做翻页：limit offset, N。其实上limit有一个问题：当offset非常大时, 效率极低。
原因是MySQL并不是跳过offset行，然后只取出后面的N行，而是会取出offset+N行，之后再丢掉前offset行。如果offset过大，那么取出的数据会非常大，很消耗资源。
如何优化？
从业务上去解决，办法: 不允许翻过100页，以百度为例,一般翻页到70页左右。这种方法是限制offset不至于过大。
不用limit，用条件查询：
-- 原limit语句selectid,title,timefromtb_articlelimitoffset,N-- 使用下面代替selectid,title,timefromtb_articlewhererow_index>offsetlimitN
SQL
这种方法需要增加一个额外的字段记录行数(可以直接使用主键)，并且数据不进行物理删除(可以逻辑删除，什么是逻辑删除请自行百度)。
非要物理删除，还要用offset精确查询，还不限制用户分页，怎么办？优化思路是不查,少查,查索引,少取列。如果必须要查，则只查索引，不查数据，得到id，再用id去查具体条目信息，这样会使取出的数据不至于过大。SQL语句参考：
selectid,title,timefromtb_articleinnerjoin(selectidfromtb_articlelimitoffset,N)astmpusingid
SQL
其它子查询优化
from型子查询
from子查询查到的临时表, 是没有索引的。所以from的返回内容要尽量少，如果需要排序，在子查询内就先排好序。
group by优化
注意：分组用于统计，而不用于筛选重复数据。不重复的行，分组统计数据用, 而不要让查询产生N多重复数据，用group去重，效率会很低。
比如：1->N 连接时，栏目---左连接--->商品表，将会产生重复行。
比如: 分组用于统计平均分、最高分较适合，但用于筛选重复数据，则不适合。
以及可以用索引来避免临时表和文件排序（using filesort）。
group by 的列要有索引，可以避免临时表及文件排序。
order by 的列要和group by 的一致，否则也会引起临时表。(原因是因为group by 和order by 都需要排序，所以如果2者的列不一致，那必须经过至少1次排序)。
order by优化
通过索引排序是性能最好的，通常如果SQL语句不合理，就无法使用索引排序，以下几种情况是无法使用索引排序的。
查询使用了两种不同的排序方向，但是索引列都是正序排序的;
查询的where和order by中的列无法组合成索引的最左前缀;
查询在索引列的第一列上是范围条件;
查询条件上有多个等于条件。对排序来说，这也是一种范围查询。
union优化
union总是会产生临时表，对union的优化比较棘手。
注意union的子句条件要尽量具体，即--查询更少的行。
子句的结果在内存里并成结果集，需要去重复，去重复就得先排序。而加all之后,不需要去重, union尽量加all，可以在代码中去重（压根就不应该取出重复的行）。
count() 优化
误区: myisam的count()非常快
答: 是比较快，但仅限于查询表的”所有行”比较快, 因为Myisam对行数进行了存储。一旦带有where条件, 速度就不再快了，尤其是where条件的列上没有索引更慢了。
假如，id<100 的商家都是我们内部测试的,我们想查查真实的商家有多少?
selectcount(*)fromlx_comwhereid>=100;-- (1000多万行用了6.X秒)-- 小技巧:selectcount(*)fromlx_com;-- 极快selectcount(*)fromlx_comwhereid<100;-- 快（数据少）select(selectcount(*)fromlx_com)-(selectcount(*)fromlx_comwhereid<100);-- 快
网友评论