mysql优化索引优化策略

电玩女神 2022-07-12 06:24 349阅读 0赞

*  索引优化策略
    
     *   *   *  1索引类型
                
                 *  1 B-tree索引
                 *  2 hash索引
             *  2 btree索引的常见误区
                
                 *  1 在where条件常用的列上都加上索引
                 *  2 在多列上建立索引后查询哪个列索引都将发挥作用索引左前缀原则

# 索引优化策略 #

#### 1:索引类型 ####

##### 1.1 B-tree索引 #####

> 注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree,而Myisam,innodb中,默认用B-tree索引

但抽象一下—B-tree系统,可理解为”排好序的快速查找结构”.

##### 1.2 hash索引 #####

在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)

疑问: 既然hash的查找如此高效,为什么不都用hash索引?  
答:  
1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,  
比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.  
2: 不法对范围查询进行优化.  
3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引  
查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)  
因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机  
4: 排序也无法优化.  
5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据

#### 2: btree索引的常见误区 ####

##### 2.1 在where条件常用的列上都加上索引 #####

例: where cat\_id=3 and price>100 ; //查询第3个栏目,100元以上的商品  
误: cat\_id上,和, price上都加上索引.  
错: 只能用上cat\_id或Price索引,因为是独立的索引,同时只能用上1个.

##### 2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用（索引左前缀原则） #####

误: 多列索引上,索引发挥作用,需要满足左前缀要求.  
以 index(a,b,c) 为例,

<table> 
 <thead> 
  <tr> 
   <th align="center">语句</th> 
   <th align="center">索引是否发挥作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">Where a=3</td> 
   <td align="center">是,只使用了a列</td> 
  </tr> 
  <tr> 
   <td align="center">Where a=3 and b=5</td> 
   <td align="center">是,使用了a,b列</td> 
  </tr> 
  <tr> 
   <td align="center">Where a=3 and b=5 and c=4</td> 
   <td align="center">是,使用了abc</td> 
  </tr> 
  <tr> 
   <td align="center">Where b=3 / where c=4</td> 
   <td align="center">否</td> 
  </tr> 
  <tr> 
   <td align="center">Where a=3 and c=4</td> 
   <td align="center">a列能发挥索引,c不能</td> 
  </tr> 
  <tr> 
   <td align="center">Where a=3 and b&gt;10 and c=7</td> 
   <td align="center">A能利用,b能利用, C不能利用</td> 
  </tr> 
  <tr> 
   <td align="center">同上,where a=3 and b like ‘xxxx%’ and c=7</td> 
   <td align="center">A能用,B能用,C不能用</td> 
  </tr> 
 </tbody> 
</table>

为便于理解, 假设ABC各10米长的木板, 河面宽30米.  
全值索引是则木板长10米,  
Like,左前缀及范围查询, 则木板长6米,

自己拼接一下,能否过河对岸,就知道索引能否利用上.  
如上例中, where a=3 and b>10, and c=7,  
A板长10米,A列索引发挥作用  
A板正常接B板, B板索引发挥作用  
B板短了,接不到C板, C列的索引不发挥作用.

假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分  
A where c1=x and c2=x and c4>x and c3=x  
B where c1=x and c2=x and c4=x order by c3  
C where c1=x and c4= x group by c3,c2  
D where c1=x and c5=x order by c2,c3  
E where c1=x and c2=x and c5=? order by c2,c3

create table t4 ( c1 tinyint(1) not null default 0, c2 tinyint(1) not null default 0, c3 tinyint(1) not null default 0, c4 tinyint(1) not null default 0, c5 tinyint(1) not null default 0, index c1234(c1,c2,c3,c4) );
    insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

对于A:  
c1=x and c2=x and c4>x and c3=x <==等价==> c1=x and c2=x and c3=x and c4>x  
因此 c1,c2,c3,c4都能用上. 如下:

mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: t4
     type: range
    possible_keys: c1234
     key: c1234
     key_len: 4 #可以看出c1,c2,c3,c4索引都用上
     ref: NULL
     rows: 1
     Extra: Using where

对于B: select \* from t4 where c1=1 and c2=2 and c4=3 order by c3  
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.  
而c4没发挥作用.

mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 2
              ref: const,const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)
    
    mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 2
              ref: const,const
             rows: 1
            Extra: Using where; Using filesort
    1 row in set (0.00 sec)

对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引

mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
              ref: const
             rows: 1
            Extra: Using where; Using temporary; Using filesort
    1 row in set (0.00 sec)
    
    mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 1
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.  
因此,没用到filesort

mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 1
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

E: 这一句等价与 elect \* from t4 where c1=1 and c2=3 and c5=2 order by c3;  
因为c2的值既是固定的,参与排序时并不考虑

mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 2
              ref: const,const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

一道面试题:  
有商品表, 有主键,goods\_id, 栏目列 cat\_id, 价格price  
说:在价格列上已经加了索引,但按价格查询还是很慢,  
问可能是什么原因,怎么解决?

答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查.

改正: 去掉单独的Price列的索引, 加 (cat\_id,price)复合索引  
再查询.