高性能MySQL读书笔记---索引优化

索引优化

索引是储存引擎中用于快锁查找记录的一种数据结构

1. 索引类型

   • B-Tree索引
     B-Tree通常意味着所有的值都是按顺序储存的，并且每一个叶子页到根的距离相同。

   B-Tree对索引是顺序组织存储的，所以很适合查找范围数据。
   B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。
   适用场景：

    1. 全值匹配,和索引中的所有列进行匹配。
    2. 匹配最左前缀,只能使用索引的第一列
    建立channel_id,ref索引,单独查询channel_id时可以用到索引,单独查询ref时用不到索引,检索了全部数据         

   

     3. 匹配列前缀,使用LIKE查询时只能查询以什么开头时才能用到索引,并且只能用到索引第一列  
     例如建立了name_age(name,age) 的聚合索引,查询以张字开头的姓并且年龄为18的数据时只能用到name索引,而查以辉字结尾的姓别时用不到索引。
     建立ref,channel_id的索引,ref使用like查询时扫描了70万行,改为等于查询时扫描了10万行
     

     4. 匹配范围值 当索引中列有范围查找时其后面列也将无法使用到索引
     当channel_id使用范围查询时,使用索引检索了所有大于30的channel_id,但是索引第二列的ref没有使用索引

 - 哈希索引
 哈希索引是基于哈希表实现的。(感觉使用场景不多,而且只支持Memory引擎表,略过)
 - 空间数据索引
 - 全文索引 用于查找列中关键词,不是直接比较索引中的值。

1. 索引的优点
   可以减少服务器需要扫描的数据量,避免排序和临时表,将随机I/O变成顺序I/O；
   什么时候使用索引：只有当索引帮助引擎快速查询记录的好处大于它带来的额外工作时才使用索引。

2. 索引优化

   • 简化WHERE条件 始终将索引列放入=号得一侧
   • 尽量使用多列索引而不是吧WHERE的每一列都建上单独的索引,而且当有多列索引的最左列索引和单列索引共同存在时MySQL查询时会使用多列索引而不是单列索引,例如建立了ab(a,b),a,b三个索引 WHERE a=1 and
     b=2时只能使用到ab索引

   

- 当服务器出现对多个索引的相交操作通常需要一个包含查询列的多列索引而不是多个单列索引
- 建立多列索引时将选择性最高的列放入索引的最左边。
- 当数据重复性太高时,例如一张大表中有一半数据都包含着某个值,另一边包含着另一个值   这样的列就没有建立所以的必要了。

例如图中channel_id字段在400万数据中有四分之一都是,就算建立索引每次查询也会检索100万条记录。

- 当某几个列同时查询的频率很高的时候,可以针对这几个列建立一个全覆盖索引。
- 避免重复顺序索引的创建,会造成索引冗余。
创建一个多列索引,两个单列索引,查询时会使用多列索引而不是两个单列索引

- 尽量把需要范围查询的列放入多列索引的右面,便于优化器尽可能索引大多数的列。