MYSQL 索引的设计——关注点

覆盖索引

从一个例子看一下：
表名test1

show create table test1\G;
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'prime key id',
  `name` varchar(120) NOT NULL default '' COMMENT 'user name',
  `id_dir` tinyint(4) default '0',
  PRIMARY KEY  (`id`),
  KEY `id_index` (`id_dir`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='test1'
1 row in set (0.00 sec)

其中id_dir有索引

explain select id,id_dir from test1 where id_dir=0\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: id_index
          key: id_index
      key_len: 2
          ref: const
         rows: 1
        Extra: Using where; Using index

explain select id,id_dir,name from test1 where id_dir=0\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: id_index
          key: id_index
      key_len: 2
          ref: const
         rows: 1
        Extra: Using where

第一条中其中Extra有Using index说明访问的id和id_dir这两字段是被索引覆盖的，不需要回访数据表再获取其他字段，这样是高效的；第二条需要name字段，这个没有索引覆盖，extra没有Using index，需要回访数据表获取相应字段的信息，这相对于索引覆盖需要增加IO输出，增加成本。

---

key_len 来判断使用的几个索引

以test1表为例

explain select * from test1 where id_dir = 0;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test1 | ref  | id_index      | id_index | 2       | const |    1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

其中使用了一个索引id_dir，key_len=2
key_len的计算如下:

key_len的计算
通过key_len可以知道复合索引都使用了哪些字段.key_len的计算上:
当字段定义可以为空时,需要额外的1个字节来记录它是否为空,当字段定义为not null时,这额外的1个字节是不需要的.
当字段定义为变长数据类型(比如说varchar)时,需要额外的2个字节来记录它的长度; 当字段定义为定长数据类型(比如说int,char,datetime等),这额外的2个字节是不需要的.
对于字符型数据,varchar(n),char(n), n都是定义的最大字符长度, gbk的话:2n ,utf8的话:3n
int 4个字节,bigint 8个字节,这些定长类型占用的字节数,这里只列举这2个吧.
索引使用哪些字段,上述计算公式计算出的字节的和就是ken_len,就可以确定索引使用了哪些字段

---

order by 是否要加索引

order by 是将符合where条件的全部取出来，然后放到内存里，再进行物理排序；但是可不可能不进行物理排序直接获取数据呢？答案是可以的。因为索引通过叶节点上的双向链表实现了逻辑有序性,比如说对于 ==where a=? order by b limit 1; == 可以直接使用index(a,b)来达到效果,不需要物理排序,从索引的根节点,走到叶节点,找到a=?的位置,因为这时b是有序的,只要顺着链表向右走,扫描1个位置,就可以找到想要的1条记录,这样既达到了业务SQL的要求,也避免了物理的排序操作。这种情况下,执行计划的Extra部分就不会出现Using filesort,因为它只扫描了极少量的索引叶节点就返回了结果,所以一般而言,执行很快,资源消耗很少,是我们想要的效果.

1. explain select * from banner where status=0 order by begin_time;
+----+-------------+--------+------+--------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys            | key               | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+--------------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | banner | ref  | status,status_begin_time | status_begin_time | 1       | const |   31 | Using where |
+----+-------------+--------+------+--------------------------+-------------------+---------+-------+------+-------------+
2. explain select * from banner where status=0 order by end_time;
+----+-------------+--------+------+--------------------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys            | key    | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+--------------------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | banner | ref  | status,status_begin_time | status | 1       | const |   31 | Using where; Using filesort |
+----+-------------+--------+------+--------------------------+--------+---------+-------+------+-----------------------------+

第一条，(status,begin_time)是联合索引，Extra中没有Using filesort表示没有用到物理排序；第二条，没有联合索引，用到Using filesort。很显然，第一条是快速的，显然也是我们期待的。