创建高性能索引（高性能mysql读书笔记二）

1、索引类型

（1）B-Tree索引（MyISAM、InnoDB使用）

          所有值按照顺序排放

          每一个叶子节点到根节点的距离相同

          树的深度和表的大小相关

          （说明：MyISAM使用前缀压缩技术是索引更小，InnoDB采用原数据存储。MyISAM索引通过存储数据的物理地址来引用数据，InnoDB通过主键引用数据）

       B-Tree索引的限制

          如果不是按照索引的最左列开始查询，那么不能使用索引

          不能跳过索引中的列，如果那样，只能引用到前面的索引

          如果查询中有范围查询（like %），那么右边的列无法使用索引进行优化查询

（2）hash索引（Memory的默认索引，但是Memory也支持B-Tree索引）

          Memory支持非唯一的hash索引，如果多个列的哈希值相同，那么索引会以链表的方式存放多个记录指针到同一个hash条目中。（很类似jdk中HashMap的实现）

        限制：

          因为是散列值，无法在排序时引用索引

          不支持部分索引列匹配查找

          只支持等值的索引引用          

（3）全文索引

          查找文本中的关键词

2、高性能的索引策略

（1）使用独立的列：索引列不能是表达式的一部分，也是不能是函数的参数

（2）前缀索引的使用

          怎样确定前缀的长度？

          1）通过不断调整，使前缀的选择性接近玩整理的选择性

          2）计算完整列的选择性

           select  count  ( distinct  city) / count  ( 1 )  from  citys

      select  count  ( distinct  left  (city, 3 ))  / count ( 1 )  as  sel3,

   count ( distinct  left (city, 5  )) /  count ( 1  )  as  sel5,

   count ( distinct  left (city, 7  )) /  count ( 1  )  as  sel7,

   count ( distinct  left (city, 9  )) /  count ( 1  )  as  sel9

from  citys

比对前缀值为哪个的时候和完整列的比率最接近

如果是7最接近

alter  table  citys  add  key  (city(  7 ))

（3）多列索引

          对于多个条件的and查询

          如果查询同时对两个单列索引扫描，会使用索引的合并策略（一般说明索引建立的不够合理）

（4）合适的索引顺序

          1）在不考虑排序和分组的时候，将选择性最高的列放在前面通常比较好

          e.g.

           select  *  from  mobile_article_pv  where  appid  = 'abc'  and  pv =  3

      select  sum  (appid = 'abc'  ), sum  (pv = 3  )  from  mobile_article_pv（这种方法往往受具体的某一条记录的值影响）

     

      select  count  ( distinct  appid) / count  ( 1 ) appid_select, count ( distinct  pv) / count ( 1 )  from  mobile_article_pv

     

           对整体的数据作比较，可以判断出pv的选择性更高

（5）聚簇索引

          聚簇：表示数据行相邻的键值紧凑的存储在一起，因为无法把数据行存放在两个不同的地方，一个表只能有一个聚簇索引

          InnoDB（聚簇索引实现）：通过主键来聚集数据，如果没有，会选择一个非空索引代替（如果没有这种索引，会隐式的定义一个）

     innodb的主键索引都是聚簇索引,它的辅助索引里面都包含有表的主键,而且主键都在辅助索引的最后一列.而myisam的索引,都是普通索引,存储的是列的值,还有列在原表中的地址值---不存储主键值

     

     非主键索引的查询需要查询两次，先查到主键，然后在通过主键的聚簇索引找到数据

（6）覆盖索引

          在一个索引总就能获取到所需要查询字段的值。因为B-Tree在索引文件中是包含列值的。

          e.g.

show  index  from  mobile_article_pv

          

explain  select  appid  from  mobile_article_pv  where  time = '2012-10-31 16:35:00'  and a Id =  'abcd'

Using index 表示这是一个覆盖索引的查询

注意：在非主键索引中，也会包含主键最为叶子节点中的数据值，所以覆盖索引也能获取到主键值

（7）压缩索引前缀（MyISAM）

          MyISAM会采用压缩索引前缀来进行处理，这样每次可以放入内存更多的索引

          方式：先保存索引块的第一个值，然后将其他值和第一个进行比较得到相同前缀的字符数，和剩余不同的后缀，把这部分保存起来。例如，第一个值为perform，第二个值为performance，那么第二个值压缩之后的存储为7，ance。

          好处：减小空间，能更多的索引放入内存。

          缺点：某些操作可能更慢，因为所有索引都依赖前面的值，无法在索引中用二分查找，只能顺序查找。倒序扫描的效率很低

MyISAM中的create table语句中可以指定pack_keys选择压缩方式

* PACK_KEYS 
PACK_KEYS takes effect only with MyISAM tables. Set this option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT tells the storage engine to pack only long CHAR or VARCHAR columns. 
If you do not use PACK_KEYS, the default is to pack strings, but not numbers. If you use PACK_KEYS=1, numbers are packed as well.

（8）冗余索引

          1、注意不要把主键再建立secondary index中，因为主键原本就会存放在secondary index中

          2、注意一些前缀索引的使用，有时候不需要新建立索引

（9）索引原则

          1、尽可能的将所需要的范围查询的列放到索引的后面，以便优化器能够尽可能多的使用索引列

          2、in的组合会使查询数量成指数倍上升

          3、避免多个范围查询

demo：利用上面所说，可以利用覆盖索引对系统中先用的 mobile_channel_expand进行改造

1、可以将现有的channel和appid都改为关联表中数字，类型改为整型。重新生成索引结构

2、利用覆盖索引进行优化sql

一般在项目中使用的查询，

select  *  from  mobile_channel  where  appid  = 'abc'  and  channel = 'll'  order  by  time  desc

可以改造为（利用覆盖索引获取主键，然后通过主键获得所有列的数据）：

select  *  from  mobile_channel  as  a,( select  id  from  mobile_channel  where  appid = 'abc'  and  channel = 'll'  order  by time desc )  as  b  where  a.id  = b.id