mysql优化sql语句的⼏种⽅法
转载：
优化sql语句的⼏种⽅法
1、通过show status命令了解SQL的执⾏效率
show [ssion|global]status like 'com_%';
2、定位执⾏效率较低的SQL语句
1）慢查询
开启⽅法
linux：
配置⽂件（/etc/myf）的[mysqld]中增加
log-slow-queries=/var/log/mysql/slowquery.log(指定⽇志⽂件存放位置，可以为空，系统会给⼀个缺省的⽂件host_name-slow.log)
long_query_time=2(记录超过的时间，默认为10s)
log-queries-not-using-indexes (log下来没有使⽤索引的query，可以根据情况决定是否开启)
log-long-format (如果设置了，所有没有使⽤索引的查询也将被记录)
windows:
在my.ini的[mysqld]添加如下语句：
log-slow-queries =E:\web\mysql\log\mysqlslowquery.log
权益法核算
long_query_time = 2(其他参数如上)
查看慢查询⽅法
使⽤mysql⾃带命令mysqldumpslow查看
mysqldumpslow -s c -t 20 -g "left join" host-slow.log
-s，表⽰按照何种⽅式排序，c,t,l,r分别表⽰按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表⽰相应的倒序；
-t，是top n的意思，即为返回前⾯多少条的数据；
-g，后边可以写⼀个正则匹配模式，⼤⼩写不敏感的；
mysqldumpslow -s c -t 20 host-slow.log
difficult的反义词mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条⾥⾯含有左连接的sql语句。
查看慢查询还有⼀个⽅法即使⽤mysqlsla⼯具，⼤部分都使⽤这个⽅法，⽅法可参考
慢查询开启与⽅法可见
2）show processlist
3、使⽤explain分析SQL.在 explain的帮助下，您就知道什么时候该给表添加索引，以使⽤索引来查找记录从⽽让lect 运⾏更快。explain [extended] lect … from … where …
如果使⽤了extended，那么在执⾏完explain语句后，可以使⽤show warnings语句查询相应的优化信息。
执⾏EXPLAIN SELECT * FROM res_ur ORDER BYmodifiedtime LIMIT 0,1000 得到如下结果：
显⽰结果分析：
table | type | possible_keys | key |key_len | ref | rows | Extra
EXPLAIN列的解释：
table
显⽰这⼀⾏的数据是关于哪张表的
type
这是重要的列，显⽰连接使⽤了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys
显⽰可能应⽤在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择⼀个合适的语句
key
实际使⽤的索引。如果为NULL，则没有使⽤索引。很少的情况下，MYSQL会选择优化不⾜的索引。这种情况下，可以在SELECT语句中使⽤USE考研词汇
INDEX（indexname）来强制使⽤⼀个索引或者⽤IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len
使⽤的索引的长度。在不损失精确性的情况下，长度越短越好
ref
显⽰索引的哪⼀列被使⽤了，如果可能的话，是⼀个常数
rows
MYSQL认为必须检查的⽤来返回请求数据的⾏数
Extra
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这⾥可以看到的坏的例⼦是Using temporary和Using filesort，意思MYSQL根本不能使⽤索引，结果是检索会很慢
extra列返回的描述的意义
remindme
Distinct
⼀旦MYSQL找到了与⾏相联合匹配的⾏，就不再搜索了
Not exists
MYSQL优化了LEFT JOIN，⼀旦它找到了匹配LEFT JOIN标准的⾏，
就不再搜索了
Range checked for each
Record（index map:#）
没有找到理想的索引，因此对于从前⾯表中来的每⼀个⾏组合，MYSQL检查使⽤哪个索引，并⽤它来从表中返回⾏。这是使⽤索引的最慢的连接之⼀Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进⾏额外的步骤来发现如何对返回的⾏排序。它根据连接类型以及存储排序键值和匹配条件的全部⾏的⾏指针来排序全部⾏
deposited
Using index
列数据是从仅仅使⽤了索引中的信息⽽没有读取实际的⾏动的表返回的，这发⽣在对表的全部的请求列都是同⼀个索引的部分的时候
Using temporary
看到这个的时候，查询需要优化了。这⾥，MYSQL需要创建⼀个临时表来存储结果，这通常发⽣在对不同的列集进⾏ORDER BY上，⽽不是GROUP BY上Where ud
使⽤了WHERE从句来限制哪些⾏将与下⼀张表匹配或者是返回给⽤户。如果不想返回表中的全部⾏，并且连接类型ALL或index，这就会发⽣，或者是查询有问题
不同连接类型的解释（按照效率⾼低的顺序排序）
system
表只有⼀⾏：system表。这是const连接类型的特殊情况
const
表中的⼀个记录的最⼤值能够匹配这个查询（索引可以是主键或惟⼀索引）。因为只有⼀⾏，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref
在连接中，MYSQL在查询时，从前⾯的表中，对每⼀个记录的联合都从表中读取⼀个记录，它在查询使⽤了索引为主键或惟⼀键的全部时使⽤
ref
这个连接类型只有在查询使⽤了不是惟⼀或主键的键或者是这些类型的部分（⽐如，利⽤最左边前缀）时发⽣。对于之前的表的每⼀个⾏联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range
这个连接类型使⽤索引返回⼀个范围中的⾏，⽐如使⽤>或<；查找东西时发⽣的情况
index
这个连接类型对前⾯的表中的每⼀个记录联合进⾏完全扫描（⽐ALL更好，因为索引⼀般⼩于表数据）
ALL
这个连接类型对于前⾯的每⼀个记录联合进⾏完全扫描，这⼀般⽐较糟糕，应该尽量避免
可参考：
4、性能优化器 profile
可以先使⽤
牛津英汉词典mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in t (0.00 c)来查看是否已经启⽤profile，如果profilng值为0，可以通过
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 c)
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
考研政治参考书
+-------------+
1 row in t (0.00 c)
来启⽤。启⽤profiling之后，我们执⾏⼀条查询语句，⽐如：
SELECT * FROM res_ur ORDER BY modifiedtimeLIMIT 0,1000
mysql> show profiles;
+----------+------------+-------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------+
| 1| 0.00012200 | SELECT @@profiling |
| 2| 1.54582000 | SELECT res_id FROM res_ur ORDER BY modifiedtime LIMIT 0,3 | +----------+------------+-------------------------------------------------------------+
2 rows in t (0.00 c) 注意：Query_ID表⽰刚执⾏的查询语句
mysql> show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000013 |
| checking query cache for query | 0.000035 |
| Opening tables | 0.000009 |
| System lock | 0.000002 |
| Table lock | 0.000015 |
| init | 0.000011 |
| optimizing | 0.000003 |
| statistics | 0.000006 |
| preparing | 0.000006 |
| executing | 0.000001 |
无线鼠标键盘| Sorting result | 1.545565 |
| Sending data | 0.000038 |
| end | 0.000003 |
| query end | 0.000003 |
| freeing items | 0.000069 |
| storing result in query cache | 0.000004 |
| logging slow query | 0.000001 |
| logging slow query | 0.000033 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
19 rows in t (0.00 c)
结论：可以看出此条查询语句的执⾏过程及执⾏时间，总的时间约为1.545s。这时候我们再执⾏⼀次。
mysql> SELECT res_id FROM res_ur ORDERBY modifiedtime LIMIT 0,3;
+---------+
| res_id |
+---------+
| 1000305 |licentious
| 1000322 |
中英文在线翻译器| 1000323 |
+---------+
