2_MySQL_索引

一、什么是索引（Index）

        是帮助MySQL高效获取数据的数据结构， 排好序的快速查找数据结构  。

二、优势
        1、提高数据检索的效率，降低数据库的IO成本
        2、通过排序降低数据排序的成本，降低了CPU的消耗

三、劣势
        1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占用空间的
        2、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行Insert,Update和delete,
             因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次跟新添加了索引列的字段，都会调整
              因为更新所带来的键值变化后的索引信息
        3、索引只是提高效率的一个因素，如果你的MySQL有大数据的表，就需要花时间研究建立最优秀的索引，
             或者优化查询

四、分类

       单值索引 建议最多建5个索引
               一个索引只包含单个列，一个表可以有多个单列索引
       唯一索引
             值必须唯一，允许空值
      复合索引
              一个索引包含多个列

 五、基本语f法
        创建 create [UNIQUE] INDEX indexName ON mytable(columnname(length))
             ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
    
        删除 DROP INDEX [indexName] ON mytable
        
        查看 SHOW INDEX FROM table_name\G

六、MySQL 常见瓶颈
    1、CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
    2、IO磁盘IO瓶颈发生在装入数据远大于内存容量的时候
    3、服务器硬件的性能瓶颈：top,free，iostat和vmstat来看系统的性能状态

    4、那些情况需要创建索引
        1.主键自动建立唯一索引
        2.频繁作为查询的字段应该创建索引
        3.查询中与其他表关联的字段，外键关系建立索引
        4.频繁更新的字段不适合创建索引--因为每次更新不单单更新了记录还会更新索引
        5.where条件里用不到的字段不创建索引
        6.单键/组合索引的选择问题？who?(在高并发倾向创建组合索引)
        7.查询中排序的字段，排序字段若通过索引访问将大大提高排序速度
        8.查询中统计或者分组字段

   5、那些情况不需要创建索引
        1.表记录太少
        2.经常增删改的表 -- 提高了查询速度，同时会降低更新表的速度，如对表进行INSERT,
          UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
        3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引
          注意，如果某个字段数据列包含许多重复内容，为它建立索引就没有太大的实际效果

七、索引失效（应该避免）

      CREATE TABLE staffs(
                 id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名'，
                age INT NOT NULL DEFAULT 0 COMMENT '年龄',
                pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
                add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
    )CHARSET utf8 COMMENT '员工记录表'；
    
    INSERT INTO staffs(NAME,age,pos,add_time)VALUES('z3',22,'manager',NEW());
    INSERT INTO staffs(NAME,age,pos,add_time)VALUES('July',23,'manager',NEW());
    INSERT INTO staffs(NAME,age,pos,add_time)VALUES('2000',23,'manager',NEW());
    
    select * from staffs;
    添加复合索引
    ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);

1.全值匹配 队列的值精确匹配 a = 'fisrtname'

2.最佳左前缀法则：如果索引多列，要遵循最左前缀法则，指查询从索引的最左前列开始并且不跳过索引中的列

      下列三个都复合

        explain select * from staffs where name = 'July';
        explain select * from staffs where name = 'July' And age =23;
        explain select * from staffs where name = 'July' And age = 23 And pos = 'dev';

3.不在索引列上左任何操作（计算、函数（自动or手动）类型转换），会导致索引失效儿转向全表扫描
    
4.存储引擎不能使用索引中范围条件右侧的列 (范围之后的索引全失效)
        explain select * from staffs where name ='July' And age > 23 And pos='dev';
        
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 
    
6.mysql在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
    
 7.is null，is not null 也无法使用索引
    
 8.like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描的操作 
        问题：解决like % 字符串时索引不被使用的方法？？
               select XXX from yyy where a  like 'abc%' 可以使用索引
        或是
        **    用覆盖索引 解决两边都有%号的问题
    
 9.字符串不加单引号索引失效
    
 10.少用or，用它来连接时会索引失效
      explain select * from staffs where name ='July' or name = 'z3';

假设inde(a,b,c)
     where 语句                                                                                    索引是否被使用
1) where a = 3                                                                                      Y,使用到a
2) where a = 3 and b = 5                                                                      Y,使用到a,b
3) where a = 3 and b = 5 and c = 4                                                      Y,使用到a,b,c 
4) where b = 3 或者 where b = 3 and c = 4 或者 where c = 4              N,没有用到任何索引
5) where a = 3 and c = 5                                                                      Y,使用到a,但是c不可以，b中间断了
6) where a = 3 and b > 4 and c = 5                                                      Y,使用到a和b，c不能用在范围之后，b断了
7) where a = 3 and b like 'kk%' and c= 4                                              Y,使用到a,b,c
   7.1) where a = 3 and b like '%kk' and c= 4                                        Y,只用到a
   7.2) where a = 3 and b like '%kk%' and c= 4                                     Y,只用到a
   7.3) where a = 3 and b like 'k%kk%' and c= 4                                   Y,使用到a,b,c

index(a,b,c,d) order by 

8) where a = '1' and b = '2' and d = '4' order by c                                  使用到a,b       c用于排序
   等价于
  where a = '1' and b = '2'  order by c 

9) where a = '1' and b = '2'  order by d                                                   使用到a,b  使用了内排序性能下降
10) where a = '1' and e = '2'  order by b,c                                              使用到a    b,c用于排序
11) where a = '1' and e = '2'  order by c,b                                              使用到a    b,c用于排序
12) where a = '1' and b = '2'  order by b,c                                              使用到a    b,c用于排序
     等价于
     where a = '1' and b ='2' e = '5'  order by b,c                                     使用到a     b,c用于排序

13) where a = '1' and b ='2' e= '5'  order by c,b                                       b是一个常亮值，不用排序，实际上只有c 不会产生filesort

14) where a = '1' and d = '4' group by b ,c;                                             用到 a

15) where a = '1' and d = '4' group by c ,b;                                             用到 a,Using temporary,Using filesort

定值，范围还是排序，一般order by 是给个范围
    group by 基本上都需要进行排序，会有临时表产生
    
 一般建议
        1、对于单值索引，尽量选择针对当前query过滤性更好的索引
        2、在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
        3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
        4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的