人生不过如此

使用过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

上篇中我们提到用伪loose index scan来优化max/min，这一篇我们将用伪loose index scan来优化distinct：

有这样的一个需求：select count(distinct nick) from user_access_xx_xx;

这条sql用于统计用户访问的uv，由于单表的数据量在10G以上，即使在user_access_xx_xx上加上nick的索引，

通过查看执行计划，也为全索引扫描，sql在执行的时候，会对整个服务器带来抖动；

root@db 09:00:12>select count(distinct nick) from user_access;

+———————-+

| count(distinct nick) |

+———————-+

|               806934 |

+———————-+

1 row in set (52.78 sec)

执行一次sql需要花费52.78s，已经非常的慢了

现在需要换一种思路来解决该问题：

我们知道索引的值是按照索引字段升序的，比如我们对（nick，other_column）两个字段做了索引，那么在索引中的则是按照nick，other_column的升序排列：

我们现在的sql：select count(distinct nick) from user_access;则是直接从nick1开始一条条扫描下来，直到扫描到最后一个nick_n,

那么中间过程会扫描很多重复的nick，如果我们能够跳过中间重复的nick，则性能会优化非常多（在oracle中，这种扫描技术为loose index scan，但在5.1的版本中，mysql中还不能直接支持这种优化技术）：

所以需要通过改写sql来达到伪loose index scan：

root@db 09:41:30>select count(*) from ( select distinct(nick) from user_access)t ;

| count(*) |

+———-+

|   806934 |

1 row in set (5.81 sec)

Sql中先选出不同的nick，最后在外面套一层，就可以得到nick的distinct值总和；

最重要的是在子查询中：select distinct(nick) 实现了上图中的伪loose index scan，优化器在这个时候的执行计划为Using index for group-by ，

需要注意的是mysql把distinct优化为group by，它首先利用索引来分组，然后扫描索引，对需要的nick只扫描一次；

两个sql的执行计划分别为：

优化写法：

root@db 09:41:10>explain select distinct(nick) from user_access-> ;

+—-+————-+——————————+——-+—————+————-| id | select_type | table                        | type  | possible_keys | key                             | key_len | ref  | rows    | Extra                    |

+—-+————-+——————————+——-+—————+————-

|  1 | SIMPLE      | user_access | range | NULL          | ind_user_access_nick | 67      | NULL | 2124695 | Using index for group-by |

+—-+————-+——————————+——-+—————+————-

原始写法：

root@db 09:42:55>explain select count(distinct nick) from user_access;

+—-+————-+——————————+——-+—————+————-

| id | select_type | table                        | type  | possible_keys | key                        | key_len | ref  | rows     | Extra       |

+—-+————-+——————————+——-+—————+————-

|  1 | SIMPLE      | user_access | index | NULL          | ind_user_access | 177     | NULL | 19546123 | Using index |

有时候我们会遇到以下的应用场景：

SELECT MAX(log_time)

FROM log_table

WHERE log_machine IN ($machines)

CREATE TABLE log_table (

id INT NOT NULL PRIMARY KEY,

log_machine VARCHAR(20) NOT NULL,

log_time DATETIME NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

我们建立的索引为：(log_machine,log_time),当我们传入单个machine的时候，速度很快，但是当我们传入多个machines的时候，查询速度会一下子就降下来；

首先我们看看下面的查询：

root@test 10:21:15>explain select max(log_time) from log_table;

+—-+————-+——-+——+—————+——+———+——+——+—–

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——+—————+——+———+——+——+——————————+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+—-+————-+——-+——+—————+——+———+——+——+—–

当然我们的索引时建立在log_time上的单列索引，这个时候优化器发现不用扫描所有的叶子节点，而直接到最右叶子节点的尾部就可以得到最大的log_time；

同理当我们在in list中传入单个值的时候索引为 (log_machine, log_time)：

root@test 10:16:18>explain SELECT MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′)

-> ;

+—-+————-+——-+——+—————+——+———+——+——+—–

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——+—————+——+———+——+——+—–

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+—-+————-+——-+——+—————+——+———+——+——+—–

从执行计划中我们中，我们可以看到端倪，优化器和上面一样能够直接找到满足条件的最大log_time，不过这次优化器需要首先在索引中定位到Machine 1，接着在

这一系列前缀值都为machine 1的记录中，在直接在定位到最后到一条记录；

在mysql 5.5以前，mysql对loose index scan并不支持，这就意味着mysql的索引扫描通常需要一个确定的起点和终点，即使查询只需要其中一些不连续的行，

mysql也会扫描起点和终点范围内的所有行；

那么由于不支持loose index scan，现在我们传入的是一个list，mysql不得不把所有满足在list中的machine的记录查询出来，然后在这些记录中得到最大的log_time；

root@test 10:47:10>explain SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′);

+—-+————-+———–+——-+———————+———————+—-

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———–+——-+———————+———————+—-

| 1 | SIMPLE | log_table | range | ix_log_machine_time | ix_log_machine_time | 62 | NULL | 569160 | Using where; Using index |

+—-+————-+———–+——-+———————+———————+—-

root@test 10:47:15>SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′);

+————-+———————+

| log_machine | MAX(log_time) |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

1 row in set (0.65 sec)

我们看到有569160 行参加了运算；

这个时候如果我们换一种想法，把每个machine的最大log_time计算出来，然后在计算一次所有machine的中最大的log_time，这样不仅可以利用优化器能够直接得到每个machine的最大log_time的优化特点，而且还可大大减少参与计算的行，这样就可以明显提升性能：

root@test 10:47:17>SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′)

-> group by log_machine;

+————-+———————+

| log_machine | MAX(log_time) |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

| Machine 2 | 2010-05-07 23:58:42 |

| Machine 3 | 2010-05-07 23:58:41 |

| Machine 4 | 2010-05-07 23:56:29 |

+————-+———————+

4 rows in set (0.00 sec)

root@test 10:51:44>SELECT log_machine, MAX(log_time) max_log_time

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′)

-> group by log_machine

-> order by max_log_time desc

-> limit 1;

+————-+———————+

| log_machine | max_log_time |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

+————-+———————+

1 row in set (0.00 sec)

root@test 10:52:21>explain SELECT log_machine, MAX(log_time) max_log_time

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′)

-> group by log_machine ;

+—-+————-+———–+——-+———————+———————+—-

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———–+——-+———————+———————+—-

| 1 | SIMPLE | log_table | range | ix_log_machine_time | ix_log_machine_time | 62 | NULL | 18 | Using where; Using index for group-by |

+—-+————-+———–+——-+———————+———————+—-

1 row in set (0.00 sec)

执行时间从0.65s降到了0.00s，这个优化速度是非常明显的；

从执行计划中我们可以清楚看到优化器使用到了using index for group by，这样就可以让优化器使用“伪松散索引扫描”，最终达到优化的目的；

ref:http://explainextended.com/2010/05/08/max-and-min-on-a-composite-index/

压缩算法:innodb plugin采用了 zlib library函式库的LZ77 的压缩算法来对数据进行压缩,这种算法已经很成熟，在cpu利用率，压缩比率在50%以上，更为重要的是无数据丢失；

innodb的数据存储方式：innodb在数据储存上采用聚簇的方式(B-树)来组织数据，叶子节点上存放了表中定义的所有列数据；第二索引同样也是B-树结构，在索引列的后面会加上聚簇键列，用于索引列到聚簇索引查找数据；

压缩B-树：由于对B-树的频繁的更新，进而导致B-树的页节点的分裂，不断的对数据进行解压，压缩，innodb面对这种情况，对其进行了优化：在B-树压缩页维护一些系统信息，来表明已经被更新，如对于删除来说，innodb采用打is_deleted的标记，不需要任何的压缩解压操作；当索引页的值发生变化的时候，为了避免不必要的解压和压缩，innodb在压缩页上维护了一个modification log用于记录页的所有改变，insert，update的值会记录到modification log中，而不用重构整个页，当该页modification log的空间不够时，innodb才会解压该页，然后应用log，再对该页重新压缩；当重压缩失败时，叶节点就会分裂，直到insert，update成功；

innodb在innodb buffer pool中怎样处理压缩页:

在一个采用了压缩特性的表中，每一个压缩页(1k,2k,4k,8k,16k)都对应了一个16k的非压缩页，当需要读取压缩页中的数据的时候，如果压缩页不在buffer中，则从磁盘上读到buffer中，然后在对该压缩页进行解压到16k的页；

为了减少i/o以及对页的解压，在buffer中同时存在压缩和未压缩的页；为了给其他需要空间的腾出空间，innodb会将未压缩的页写到磁盘上，保留压缩页在buffer中，也有可能需要将一些没有被访问的压缩页写到磁盘上，因此buffer pool中可能有压缩和未压缩的页，或者只有压缩页；buffer pool采用LRU算法来保证热点页尽量的存放在buffer中，同时为了该LRU算法会评估当前系统正在处于一种什么样的状态，是i/o瓶颈还是cpu瓶颈：当系统正处于i/o瓶颈的时候，innodb倾向于将未压缩页写到磁盘上，以此来腾出更多的空间用于其他的页存放到buffer；当系统正处于cpu瓶颈的时候，inondb倾向于将未压缩和压缩页都写到磁盘上，以此来让更多内存存放热点页，同时减少未压缩页的比例，让更多的压缩页在内存中；

innodb log file对于压缩的处理：

在将压缩页写到数据文件前，innodb会将该页写到redo log中，因此这样就导致了log file会变得很大，同时在进行压缩的时候，需要做一次检查点(log file的大小已经检查点的频率取决于压缩页重组已经重新压缩的次数)。

压测:采用sysbench压测了一下compress的性能，2.5kw数据，未压缩大小为5.7G，采用key_block_size=8压缩后为2.9G：

1GB(BP)  2GB(BP)   4GB(BP)   8GB(BP)   16GB(BP)

compress(2.9G) 487.17     424.85        657.35        694.5           664.32

nocompress(5.7G) 284.12  727.73         933.43     1024.17       1108.8

./sysbench –test=oltp –mysql-table-engine=innodb –num-threads=5 –oltp-table-size=25000000 –mysql-user=root –mysql-socket=/u01/mysql/run/mysql.sock run&

CREATE TABLE `sbtest` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT ’0′,

`c` char(120) NOT NULL DEFAULT ”,

`pad` char(60) NOT NULL DEFAULT ”,

PRIMARY KEY (`id`),  KEY `k` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT = COMPRESSED key_block_size=8k;

今天有点激动，因为在5.6的官方文档中看到Index Condition Pushdown Optimization，简称ICP，这意味着什么，意味着mysql的优化器能够像oracle的优化器那样对索引列进行过滤，而不是像以前版本中只能使用前缀索引来过滤满足查询条件的行；

为了说明优化器是怎么利用ICP，可以使用下面查询来说明其优化：

SELECT * FROM people  WHERE zipcode=’95054′  AND lastname LIKE ‘%etrunia%’  AND address LIKE ‘%Main Street%’;

在没有ICP前，由于优化器只能只能使用前缀索引来过滤满足条件的查询，那么mysql只能够利用索引的第一个字段zipcode，来扫描people表满足ipcode=’95054′条件的记录，而后面的lastname和firstname由于使用了模糊查询，而不能在索引中继续过滤满足条件的记录，这个行为在引擎层完成，这样就导致了到服务器层对people的扫描增加了许多；

那么现在有了ICP，mysql在读取people表前，继续检查满足lastname和firstname条件的记录，

那么ICP的优化在引擎层就能够过滤掉大量的数据，这样无疑能够减少了对base table和mysql server的访问次数，我们可以看到mysql优化器在也越来越智能，这对mysql fans也该是一个好消；由于这个功能在5.6出现，很想迫不及待的测试一把，但官网上还没有提供下载5.6，上面的例子也是在文档上获得，没有具体的实践意义。