在做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。这也算是比较多的处理方式了。