在做Web开发的时候，新手总喜欢把所有东西全部都放到数据库里面。
渐渐的，我们开始不喜欢往MySQL里放东西
因为它在查询时候真的很慢
于是我们考虑用缓存的方式解决问题，或者考虑用NoSQL来解决问题
但是，最终我们没有办法逃避，有一些事情终归是需要MySQL（关系型数据库）去做的
所以，优化MySQL数据库也是不可避免的
索引
一般来说，如果一个字段出现在查询语句中基于行的选择、过滤或者排序条件中，那么该字段建立索引便是有价值的。
但这句话不是绝对的，我们可以通过MySQL非常游泳的explain关键字来观察以下几句SQL
由图可见，当我们对title字段加了索引之后，并不是每次查询都能正确的使用索引，就连模糊查询也不能够很好的支持
尤其是在字段内容开头都不确定的情况下，搜索变为了全表搜索
组合索引
（这里的计算时间并不是真正的搜索时间，而是explain时间，这个需要知道）
当我们面对 ... where a = 1 and b = 2 这样的语句时，就算a和b都加上了索引 ，但仍然很难受
因为一次查询对于一个表只能用一个索引……
这样，你的查询仍然是一个局部（Range）查询。
这个时候我们要建立组合索引。
我们建立了一张表叫做stable(innodb)，有四个字段，自增主键ID，name，company和school。
然后依靠各种办法往这个表里面填充40000数据。
在没有设置索引的情况下进行搜索
SELECT * FROM `stable` WHERE `name`=20 ORDER BY name,company,school
查询花费了 0.0148 秒。这时我们解释一下这条SQL
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | stable | ALL | NULL | NULL | NULL | NULL | 40052 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
会发现它搜索了所有的row，并且using了where和filesort，filesort是很慢的，我们必须要避免，所以我们加索引。
然后我们对这三个字段分别加索引，再次运行这个语句，查询花费 0.0138 秒，我们解释这条SQL
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | stable | ALL | name | NULL | NULL | NULL | 36958 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
哈哈。果然还是搜索了所有的Rows，而且也用了file sort
这个时候我们使用组合索引，将name，company和school按照这个顺序变成一个索引
根据最左前缀原则，相当于我们建立了name索引，name company索引和name company school索引
这时我们再查询，竟然只花了 0.0019 秒
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | stable | index | ncs | ncs | 231 | NULL | 40040 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
但是如果我们的sql是这样的
SELECT * FROM `stable` WHERE `name`=20 ORDER BY company,school
它的结果就是和第二个的结果一样了，查询了ALL的rows并且用了filesort，也花了0.0146秒
mysql> explain SELECT * FROM `stable` WHERE `name`=20 ORDER BY company,school;
+----+-------------+--------+-------+---------------+------+---------+------+-------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+-------+------------------------------------------+
| 1 | SIMPLE | stable | index | ncs | ncs | 231 | NULL | 40063 | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+-------+------------------------------------------+
等等，明明是符合最左前缀原则的，为什么失败了呢？
其实小飘我也郁闷了很久，最后发现竟然是20的问题。因为name字段的类型是varchar，它不识别20的。但是当我们修改语句
mysql> explain SELECT * FROM `stable` WHERE `name`='20' ORDER BY company,school;
+----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | stable | ref | ncs | ncs | 77 | const | 38 | Using where; Using index |
+----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
嘿嘿，这次只花了0.0003秒，果然瞬间出山的感觉啊
（经我测试，字段为数字，条件语句为字符串可以调用索引。但字段为字符串，查询条件为数字则不可以。这就是为何SQL标准要求where里面都是字符串）
另外group by是同理的，看下面第一个例子，由于是name和school，不符合最左前缀原则，所以组合索引也帮不了它了
mysql> explain SELECT count(id) FROM `stable` WHERE `name` ='56' GROUP BY school;
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------------------------------------------+
| 1 | SIMPLE | stable | ref | ncs | ncs | 77 | const | 37 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------------------------------------------+
mysql> explain SELECT count(id) FROM `stable` WHERE `name` ='56' GROUP BY company,school;
+----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | stable | ref | ncs | ncs | 77 | const | 37 | Using where; Using index |
+----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
组合索引的问题
我们在搜索SELECT * FROM `stable` WHERE `company` = '754'时候，用了0.008秒
而在搜索SELECT * FROM `stable` WHERE `name` = '754'，用了0.0003秒
这就是在组合索引下所出现的问题,它其实并不是没有用到索引,而是错误的运用了索引
mysql> SELECT * FROM `stable` WHERE `company` = '754' limit 5;
+-------+------+---------+--------+
| id | name | company | school |
+-------+------+---------+--------+
| 38764 | 13 | 754 | 794 |
| 32064 | 160 | 754 | 82 |
| 7315 | 205 | 754 | 812 |
| 37515 | 206 | 754 | 857 |
| 14730 | 211 | 754 | 192 |
+-------+------+---------+--------+
mysql> explain SELECT * FROM `stable` WHERE `company` = '754' limit 5;
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | stable | index | NULL | ncs | 231 | NULL | 40052 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
它运用了索引,并且是按照name的顺序进行排序,当你把所有索引删掉,会发现查询速度甚至还可能不如没有索引的情况
这就是使用组合索引可能带来的麻烦
此时我们把表结构改为MyISAM,呵呵,虽然是搜索全表,但果然变快了(本来MyISAM比INNODB查询快).
不过我们不能因为这件小事就改变表结构吧
于是就有下面这个方法……
SELECT * FROM `stable` WHERE `company` = '757' ORDER BY id LIMIT 5
运行了以下，只花了0.0021秒，嗯，剩了四分之三的时间，原因如下
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | stable | index | NULL | PRIMARY | 4 | NULL | 5 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
是的，在这个查询中使用了主键，所以加快了速度
所以说使用order by 主键是一个很好的加快查询速度的办法
还有就是，如果有键（a，b，c）有时候你用ab而有时候用ac
这时你可以考虑b和c哪个是有限集，不如开关状态，性别
这样可以用补位的形式来做，比如就是用a和c来查询，而b只有0和1两个值
|
1
|
select
*
from
table
where
a =
'A'
and
b
in
(0, 1)
and
c =
'C'
;
|
通过慢查询来发现问题
我们在实际工作中，总不能很好的优化索引，有时候是忘记了，有时候是没想到可能会用到，还有时候是实在设计不出能满足情况的索引
就这样，庞大的系统就起来了，我们需要想一些办法来检查这个系统的数据库有没有很慢的语句
这个时候就需要用得到慢查询
打开my.cnf，找到如下部分
|
1
2
3
4
|
# Here you can see queries with especially long duration
#log_slow_queries = /
var
/log/mysql/mysql-slow.log
#long_query_time = 0.2
#log-queries-not-using-indexes
|
我们将日志路径和long_query_time这两个注释去掉，这样，mysql就会自动把查询速度超过200毫秒的语句写到日志中
至于最后一行，如果将它打开，那么所有没有使用索引的查询都将会被写入，我们暂且不考虑这么复杂的问题
保存设置，重新启动mysql，打开你的某些站点
然后我们在终端输入mysqlreport，使用mysql的report工具可以看到慢日志已经被报告出来了
Slow 200 ms 2 0.0/s 0.39 %DMS: 0.48 Log: ON
超过200ms的sql只占常规查询的0.48%，恩情况良好
如果我将慢定义为20ms，再次查询mysqlreport
Slow 20 ms 65 3.1/s 6.29 %DMS: 10.17 Log: ON
呵呵……每秒钟有3.1次超过20ms的请求，占总请求量的10.17%。嗯，虽然这不是什么问题，但是至少它还能说明一些事情。
恩。我们先提升到50ms，然后使用mysqldumpslow命令来分析慢日志
mysqldumpslow /var/log/mysql/mysql-slow.log
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
Count:
1
Time=
0.71
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), debian-sys-maint[debian-sys-maint]@localhost
select count(*) into @discard from `information_schema`.`COLUMNS`
Count:
1
Time=
0.35
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), debian-sys-maint[debian-sys-maint]@localhost
select count(*) into @discard from `information_schema`.`PARTITIONS`
Count:
1
Time=
0.25
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), root[root]@localhost
INSERT INTO cache_bootstrap (cid, serialized, created, expire, data) VALUES (
'S'
,
'S'
,
'S'
,
'S'
,
'S'
)
Count:
1
Time=
0.18
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), root[root]@localhost
DELETE FROM cache_update
WHERE (cid =
'S'
)
Count:
4
Time=
0.07
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), root[root]@localhost
INSERT INTO cache_menu (cid, serialized, created, expire, data) VALUES (
'S'
,
'S'
,
'S'
,
'S'
,
'S'
)
Count:
1
Time=
0.06
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), root[root]@localhost
UPDATE cache SET serialized=
'S'
, created=
'S'
, expire=
'S'
, data=
'S'
WHERE ( (cid =
'S'
) )
Count:
1
Time=
0.06
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), root[root]@localhost
INSERT INTO cache_field (cid, serialized, created, expire, data) VALUES (
'S'
,
'S'
,
'S'
,
'S'
,
'S'
)
Count:
1
Time=
0.05
s (
0
s) Lock=
0.00
s (
0
s) Rows=
0.0
(
0
), root[root]@localhost
DELETE FROM cache_bootstrap
WHERE (cid =
'S'
)
|
竟然有语句用了0.7秒，而且我大致知道了它来自哪里
这就是慢查询的使用正确姿势
索引缓存
我们通过mysqlreport来查询索引缓存
__ Key _________________________________________________________________
Buffer used 0 of 16.00M %Used: 0.00
Current 1.84M %Usage: 11.52
Write hit 0.00%
Read hit 0.00%
呃……命中率全是0……我是有多么悲催
后之后觉的我发现，原来这个是针对MyISAM的，我最近似乎没有使用过这个引擎，于是我把上面那个stable改成MyISAM，查询了几次name
再次使用mysqlreport
__ Key _________________________________________________________________
Buffer used 398.00k of 16.00M %Used: 2.43
Current 1.84M %Usage: 11.52
Write hit 99.02%
Read hit 100.00%
啊哈哈哈哈哈哈
最后一点是，如何设置key_buffer的值
我只能说，凭感觉预留充分的空间，具体方法很复杂需要更深的讨论
索引的代价
索引的第一个代价就是空间，建的索引越多，越大，那么所占用的空间就越大
不过索性现在这个时代，不用特别关注MySQL的空间问题，因为磁盘都是足够大的
所以拿空间来换性能，是一个不错的选择
索引的第二个代价就是写入速度的变慢
我们在更新的时候，会让索引也跟着变化
所以会花更多的时间
这个时候，我们需要使用mysqlreport来看一看，到底该如何取舍
DMS 724 1.1/s 44.58
SELECT 624 0.9/s 38.42 86.19
INSERT 60 0.1/s 3.69 8.29
DELETE 24 0.0/s 1.48 3.31
UPDATE 16 0.0/s 0.99 2.21
REPLACE 0 0/s 0.00 0.00
如果像这样，查询占86%,那就多加点索引吧，写入慢点就慢点吧。
本篇大致写了一些关于索引的简单基本问题
具体关于引擎对索引的影响还没有了解
不过一般来说，写操作多的表用InnoDB，读操作多的表用MyISAM。这也算是比较多的处理方式了。
1473
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
