人生不过如此

 

 

 

 

昨晚收到客服MM电话，一用户反馈数据库响应非常慢，手机收到load异常报警，登上主机后发现大量sql执行非常慢，有的执行时间超过了10s
优化点一：
SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>=’2.00′ ORDER BY tran_id DESC LIMIT 10;
表结构为：
CREATE TABLE `game_shares_buy_list` (
`tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`………..’
PRIMARY KEY (`tran_id`),
KEY `ind_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8;
执行计划：
root@127.0.0.1 : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>=’2.00′ ORDER BY tran_id DESC LIMIT 10;
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)
分析该sql的执行计划，由于tran_id是表的主键，所以查询根据主键降序顺序扫描，这样就可以不用排序，
然后在过滤条件price>2.00的记录，看上去这个执行计划貌似非常好，如果查询扫描到了满足条件的10条记录，就会停止扫描；
但是这里有个问题，如果表中有大量的记录是不符合2.00的，意味查询就需要扫描非常多的记录，才能找到符合条件的10条：
root@127.0.0.1 : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10;
+——-+——-+
| price | cnt |
+——-+——-+
| 1.75 | 39101 |
| 1.68 | 38477 |
| 1.71 | 34869 |
| 1.66 | 34849 |
| 1.72 | 34718 |
| 1.70 | 33996 |
| 1.76 | 32527 |
| 1.69 | 27189 |
| 1.61 | 25694 |
| 1.25 | 25450 |
可以看到表中有大量的记录不是2.00的，所以这个时候不能在根据主键顺序扫描，在过滤记录；
那么是否需要在price建立一个索引：
root@127.0.0.1 : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>’2′;
+———-+
| count(*) |
+———-+
| 4087 |
+———-+
root@127.0.0.1 : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ;
+———-+
| count(*) |
+———-+
| 1572100 |
从上面price的数据分布可以看出，price的分布相对还是比较集中的，如果在price建立索引，mysql也有可能认为由于需要回表的记录过多，
同时需要额外的排序，而不选择在price上的索引：
root@127.0.0.1 : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price);
Query OK, 0 rows affected (5.79 sec)

可以看到优化器虽然注意到了我们新加的索引，但是最终还是选择了primary来扫描；
所以这个时候我们加上去的索引没有产生效果，数据库负载依然很高，如果强制走price上的索引，效果会这样：
root@127.0.0.1 : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>=’2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。。
10 rows in set (7.06 sec)

root@127.0.0.1 : sitevipdb 09:36:00> SELECT * FROM `sitevipdb`.`game_shares_buy_list` force index(ind_game_shares_buy_list_price) WHERE price>=’2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。
10 rows in set (1.01 sec)
可以看到如果强制走索引，时间已经明显下降了，但是还是有些慢，能不能在快一点？其实我们需要扫描的记录只有10条，但查询在取得这10条记录的时候需要扫描大量无效的记录

怎么降低这个数据：其实只要改写一下sql就可以，我们先从索引中得到满足条件的10个id，在回表进行关联：
root@127.0.0.1 : sitevipdb 09:44:45> select * from game_shares_buy_list t1,
-> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>=’2.0′ ORDER BY tran_id DESC LIMIT 10) t2
-> where t1.tran_id=t2.tran_id;
10 rows in set (0.00 sec)
可以看到执行时间已经不在秒级别了，和客户电话沟通后，很愿意这样改写sql。

—这里看到是order by tran_id是要额外排序的，索引也可以这样来建立消除排序(tran_id,price)这样可以消除排序，同时可以利用order by desc/asc +limit M,N的优化。

优化点二：
CREATE TABLE `game_session` (
`session_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT ’0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` text,
…………………….
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询为select `session_data`, `session_expires` from `game_session` where session_id=’xxx’出现大量等待情况
同时该表的insert，也有等待的现象；
可以看到这个表结构设计是有些问题的，咨询了客户后，可以改为下面结构：
CREATE TABLE `game_session` (
id int auto_increment,
`session_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT ’0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` varchar(200),
PRIMARY KEY (id),
key ind_session_id(session_id,session_data, session_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-新增自增主键id作为表的主键，这样对插入的性能提升是很好的，同时也降低了表主键的大小；
-将session_data由text改为了varchar(200),咨询了客户后，这个字段可以不用大字段存储，同时有text改为了varchar，就可以冗余到索引中；
由于查询可以使用覆盖索引来完成，所以将查询的3个字段冗余到索引中，查询通过索引完成，不用回表；

 

 

丁总给了我一张2012数据库技术大会的门票，让我和他们一块去北京，记得上次去北京的时候，2天都在酒店里呆着，说是中国人，北京都没有逛逛，于是乎想着这次去北京的3天，一定要去外面看看，更重要的事情是要拜访道德经艺术馆和韩金英老师；

我们团队去的人有苏普，应元和我，苏普是这次演讲的嘉宾，我和应元就得自己定酒店了，公司给每个人出差的酒店标准还是比较高的，但我们还是定了比较便宜的如家。到了北京后，就直接打车到了新疆驻京办吃羊肉，听起来倒是挺拉风，到了那里后海吃了一大桌羊肉。第二天早上一大早，我们就感到了会场，看到了很多久负盛名的DBA前辈，想起在大学的时候，就是看他们的博客，帖子，然后转到DBA这个领域的；

丁总和苏普这次分享的主题是在年初的技术大学的mysql@taobao系列课程中的内容，每次听他们的分享都有不同的体会，丁总的–《在淘宝五年》是淘宝数据库发展的见证着，也最具有发言权，丁总做事情一直站在很高的高度去做，因此看的东西就比较深入；

 

 

苏普—-《mysql核心库运维》最大的感受是这伙计是用心做事情，就是不一样；

晚上和应元去了一趟天安门，到王府井买北京烤鸭，还带来一些北京的特产回去忽悠同事，哈哈，真是两个土人；

这次北京之行，还有一个很重要的事情就是去拜访韩金英老师，第二天一大早，我就查好了路线，从早上9点出发，坐公交，坐地铁，在乘到通州的公交车，中午12点30才到镇上，在小镇上吃了一碗面后，就开始找道德经艺术馆，那天太阳异常的大，走在街上，心里总是默默的念着难得到北京一回，一定要找到，最后在镇上绕到了大半圈，在15：30才最终找到馆址。开门的是韩老师，当时还是很激动的，以前只在博客上看到过她，进屋后已经有两位今天同样来馆参观的友人，他们正在那里喝着茶，茶是来自台湾友人带来的，韩老师招呼我坐下，我就静静的坐在那里听他们喝茶论道，屋里屋外显得非常非常的安静；一些道理虽然在书上看到过，但是听人讲起来真的不一般—德者本也，财者末也；有德此有土，有土此有财。和台湾友人喝茶论道的是灵珠子（后来才从韩老师博客上得知），也是他给我指来的路，从他的言语中常常能够吐出一些妙语来，在聊起他和韩老师的缘分的时候，讲到他是一个隐士修行之人，有很高的修为，在艺术馆只是帮韩老师打理日常的事情。

临走的时候，一同到来的两位友人觉得我们三人很有缘分，于是乎一定要拍照做纪念，哈哈，感觉今天真的很奇妙，还以为今天会白跑一趟，见到了韩老师，灵珠子，两位友人，不枉此行啊。

 

4月马上就要完了，很久都没有更新博客了，是自己太懒了吧，回忆一下这过去的4周，好像一直没有停下来过，先从第一周开始说起吧。

第一周是清明节，省去了外地旅游的机会，跑到了朋友的书院去图个清闲，找到了一个在杭州幽远宁静的地方，这个书院靠山临水，四周种满了茶叶，由于去的时候正值采摘清明茶，正好露一手：

记得小时候可是靠这个来补贴每周的生活费的，自家山林里种植了一亩左右的茶，每周末一早上起来就提着口袋去采，采完回来后就立刻拿到茶厂去买，多的时候可以卖到四五十元，然后一路哼着小曲回家吃中午饭，那个真的是高兴啊；有时候为了卖一个好一些的价钱，要走很长的路，到很远的茶厂去卖，真的是天真无比；如果自家的茶采完了的话，还可以和大人们一起到山上的大茶园去采茶，天一亮就要起来准备好上山，那个时候想睡懒觉都不行，现在想想那个时候生活在农村的孩子，必须要靠自己的双手来获得生存，从小就建立自力更生的习惯，而现在的孩子又是怎么一个现状…..书院面就有一大片的茶园，看到地里有很多采茶的人，我一下就跑到茶园里和她们一起采起茶来，和他们的聊天中，居然发现还有四川的老乡，真是天涯无处不相逢。

朋友以前在书院教书，这次从北京回来在书院住了几天，听他说过完清明节，他将要骑自行车南下到广东，帐篷和车都已经买好了，

真的很佩服他的决心和力量，当一个人下定心要做一件事情的时候，这个力量是非常之大的。我和另外一个朋友折腾了很久才把它给搭建好，还担心他一个人在外地能不能顺利装上，如果遇到下雨天，他就很倒霉了。

这所书院的学生主要还是在3-5岁的小孩子，周一到周五都在书院里吃住，只有周末，父母在把他们接回家中，听朋友说，很多小孩子一开始都很不习惯，但是后来慢慢都习惯了这里的生活环境，个个都能够独立自己的生活，有一次一个小女孩想她的妈妈，在那里一直哭，带她们上课的老师对她说，如果还要哭的话，到另外一边自己拿着纸巾在哭，这么一说她就默默的到另外一边自己慢慢的哭，不过一会儿，居然不哭了，又回到了老师的身边。

朋友给我推荐了一本书《与神对话》，我一看目录吸引住了我，目录的内容就像是为我心中一直以来的疑问解答而精心编排的…..

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验，在子查询上都认为数据库已经做过优化，能够很好的选择驱动表执行，然后在把该经验移植到mysql数据库上，但是不幸的是，mysql在子查询的处理上有可能会让你大失所望，在我们的生产系统上就由于碰到了这个问题：

select  i_id, sum(i_sell) as i_sell

from table_data

where i_id in (select i_id from table_data where Gmt_create >= ’2011-10-07 00:00:00′)

group by i_id;

（备注：sql的业务逻辑可以打个比方：先查询出10-07号新卖出的100本书，然后在查询这新卖出的100本书在全年的销量情况）。

这条sql之所以出现的性能问题在于mysql优化器在处理子查询的弱点，mysql优化器在处理子查询的时候，会将将子查询改写。通常情况下，我们希望由内到外，先完成子查询的结果，然后在用子查询来驱动外查询的表，完成查询；但是mysql处理为将会先扫描外面表中的所有数据，每条数据将会传到子查询中与子查询关联，如果外表很大的话，那么性能上将会出现问题；

针对上面的查询，由于table_data这张表的数据有70W的数据，同时子查询中的数据较多，有大量是重复的，这样就需要关联近70W次，大量的关联导致这条sql执行了几个小时也没有执行完成，所以我们需要改写sql：

SELECT t2.i_id, SUM(t2.i_sell) AS sold

FROM (SELECT distinct i_id FROM table_data

WHERE gmt_create >= ’2011-10-07 00:00:00′) t1,  table_data t2

WHERE t1.i_id = t2.i_id GROUP BY t2.i_id;

我们将子查询改为了关联，同时在子查询中加上distinct，减少t1关联t2的次数；

改造后，sql的执行时间降到100ms以内。

 

 

 

 

 

 

 

今天丁原问我mysql执行计划中的key_len是怎么计算得到的，当时还没有注意，在高性能的那本书讲到过这个值的计算，但是自己看执行计划的时候一直都没有太在意这个值，更不用说深讨这个值的计算了：

ken_len表示索引使用的字节数，根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段都被查询用到。

在查看官方文档的时候，也没有发现详细的key_len的计算介绍，后来做了一些测试，在咨询了丁奇关于变长数据类型的值计算的时候，突然想到innodb 行的格式，在这里的计算中有点类似，总结一下需要考虑到以下一些情况：

(1).索引字段的附加信息：可以分为变长和定长数据类型讨论，当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节；对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；

(备注：当字段定义为非空的时候，是否为空的标记将不占用字节)

(2).同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;

 

先看定长数据类型的一个例子(编码为gbk)：

root@test 07:32:39>create table test_char(id int not null ,name_1 char(20),name_2 char(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

root@test 07:33:55>insert into test_char values(1,’xuancan’,'taobaodba’);

root@test 07:34:55>explain select * from test_char where name_1=’xuancan’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 41

ref: const

rows: 1

Extra: Using where; Using index

key_len=41=20*2+1（备注：由于name_1为空，isnull的标记被打上，需要计算1个字节）

root@test 07:35:31>explain select * from test_char where name_1=’xuancan’ and name_2=’taobaodba’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 82

ref: const,const

rows: 1

Extra: Using where; Using index

key_len=82=20*2+20*2+1+1（备注：由于name_1,name_2两列被使用到，但两列都为为空，需要计算2个字节）

变长数据类型（gbk编码）：

root@test 08:30:51>create table test_varchar(id int not null ,name_1 varchar(20),name_2 varchar(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

root@test 08:37:51>insert into test_varchar values(1,’xuancan’,'taobaodba’);

root@test 08:38:14>explain select * from test_varchar where name_1=’xuancan’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_varchar

type: ref

possible_keys: ind_name

key: ind_name

key_len: 43

ref: const

rows: 1

Extra: Using where; Using index

key_len=43=20*2+1+2（备注：由于为name_1字段定义为空，所以需要计算1,；同时由于是变长字段varchar，所以需要加上2）

root@test 08:38:46>alter table test_varchar modify column name_1 varchar(20) not null;

Query OK, 1 row affected (0.52 sec)

Records: 1 Duplicates: 0 Warnings: 0

root@test 08:42:11>explain select * from test_varchar where name_1=’xuancan’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_varchar

type: ref

possible_keys: ind_name

key: ind_name

key_len: 42

ref: const

rows: 1

Extra: Using where; Using index

key_len=42=20*2+2(备注由于name_1字段修改为not null之后，isnull的标记锁占用的字节释放掉，但是变长字段长度所占用的2个字节没有释放)；

上面是测试gbk编码的测试，同时也可以测试一下其他编码的key_len计算。

 

innodb在io的优化上有个比较重要的特性为预读，innodb以64个page为一个extent，那么innodb的预读是以page为单位还是以extent？

这样就进入了下面的话题：linear read-ahead和randomread-ahead；

为了区分这两种预读的方式，我们可以把linear预读放到以extent为单位，而random 预读放到以extent中的page为单位；

linear 预读着眼于将下一个extent提前读取到buffer pool中，

而random预读着眼于将当前extent中的剩余的page提前读取到buffer pool 中：

linear的预读方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中： innodb_read_ahead_threshold:如果一个extent中的被顺序读取的page超过或者等于该参数变量的，innodb将会异步的将下一个extent读取到buffer pool中，比如该参数的值为30，那么当该extent中有30个pages 被 sequentially的读取，则会触发innodb linear预读，将下一个extent读到内存中；在没有该变量之前，当访问到extent的最后一个page的时候，innodb会决定是否将下一个extent放入到buffer pool中；

该参数可以动态的修改：

root@(none) 09:20:02>set global innodb_read_ahead_threshold=40;

Query OK, 0 rows affected (0.00 sec)

random的预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，innodb会将该extent中的剩余page一并读到buffer pool中，由于random的预读方式给innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。

在监控innodb的预读时候，我们可以通过show innodb status中的 Pages read ahead和evicted without access 两个值来观察预读的情况：

或者通过两个状态值：

Innodb_buffer_pool_read_ahead 和 Innodb_buffer_pool_read_ahead_evicted.

Innodb_buffer_pool_read_ahead：表示通过预读请求到buffer pool的pages；

Innodb_buffer_pool_read_ahead_evicted：表示由于请求到buffer pool中没有被访问，而驱逐出buffer pool的pages；

root@(none) 10:19:42>show global status like ‘%read_ahead%’;

+—————————————+———+

| Variable_name | Value |

+—————————————+———+

| Innodb_buffer_pool_read_ahead | 775378 |

| Innodb_buffer_pool_read_ahead_evicted | 1888537 |

而通过show innodb status得到的 Pages read ahead 和evicted without access 则表示每秒读入和读出的pages；

Pages read ahead 1.00/s, evicted without access 9.99/s.

ref：

Reintroducing Random Readahead in InnoDB

http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-read_ahead.html