目录

一, 索引与B树介绍

1. B树 ,B+树 ,B*树

2. 聚集索引

3. 辅助索引

3.1 普通辅助索引
3.2 覆盖辅助索引

4. 唯一索引

二. 索引管理命令

1. 索引的增删查(重点*******)

2. 查看执行计划explain(desc)

三. 不走索引的情况（开发规范, 重点*******）

四. 建立索引的原则（运维规范）

=======================================================================.
建索引与不建索引, 在数据量50w条时, 性能差距近1w倍, 亲测
https://www.jianshu.com/p/7939a78e9088
=======================================================================

一, 索引与B树介绍

1. B树 ,B+树 ,B*树:

如图(蓝色部分先不看):

第一排为根节点, 蓝色部分存的是下面一个子节点存储的最小值, 黄色为对应子节点的指针

第二排为子节点(可能有多个子节点), 蓝色部分存的是下面数据页存储的最小值, 黄色为对应数据页的指针

第三排为数据页存的是正正的数据,

每一个框规定存的大小为16K, 索引存的数据越多, 子节点就越多, 树就越高. 对应的性能就会变差

举例: 查找数字 63

1\. 在根节点找, 63在28-65之间, 进入p2节点
2\. 63比56大,进入p3数据页
3\. 找到63

这就是B树(把图中蓝色部分不看就是)

如果要查找大于63的数字怎么办? 在每个数据页中存入旁边的指针, 这样就可以在数据页上直接跳转了, 这就是B+树

在子节点存旁边的指针叫B树*

2. 聚集索引:

基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一键做为聚集索引

在图中, 将数字改为表的主键的值, 根节点与子节点只存主键的值, 数据页上存主键的值与对应的一条记录

根据主键查询的时候效率会大大提高

如果业务中很少根据主键 , 而是根据其他字段查的最多呢, 例如name?

这就需要用到辅助索引了

3. 辅助索引:

人为创建的(普通,覆盖)

3.1 普通辅助索引:

​ 将表的某个字段设为索引, 图中的数字就替换为这个字段的值, 数据页中存的是这个字段的值与对应的主键, 根据这个字段的某个值, 找到对应的主键, 再去聚集索引拿到数据(这一步叫回表)

​ 例如根据name字段找到一条数据, 就可以将name字段设为辅助索引

​ 如果数据页中除了主键还存了其他值呢, 那就不需要再去回表了, 这就是覆盖辅助索引了

3.2 覆盖辅助索引:

​ 如果需要根据name字段拿到phone与sex的值, 可以将sex, phone一并存到数据页中, 这样就不用回表了

4. 唯一索引:

人为创建(普通索引,聚集索引)

二. 索引管理命令

1. 索引键(key),表中的某个列

1. 辅助索引(BTREE)
    怎么生成的:
        根据创建索引时,指定的列的值,进行排序后,存储的叶子节点中
    好处:
        1.优化了查询,减少cpu mem IO消耗
        2.减少的文件排序

    创建普通辅助索引(MUL)
        # 给表blog_userinfo的email字段添加索引
        alter table blog_userinfo add key idx_email(email);
        # 给表blog_userinfo的phone字段添加索引
        create index idx_phone on blog_userinfo(phone);
    查看索引
        desc blog_userinfo;
        show index from blog_userinfo;
    删除索引
        alter table blog_userinfo drop index idx_email;
        drop index idx_phone on   blog_userinfo;

2. 前缀索引: 
    有时候需要用很长的字符串作为索引, 这会让索引变得大且慢, 通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。
    # 查看password字段的前20个字符是否重复
    select count(*),substring(password,1,20) as sbp  from blog_userinfo group by sbp;
    # 将password前10个字符设为索引
    alter table blog_userinfo add index idx(password(10));

3. 唯一键索引(UNI,如果有重复值是创建不了的)
    alter table blog_userinfo add unique key uni_email(email);

4. 覆盖索引(联合索引)
    作用:不需要回表查询,不需要聚集索引,所有查询的数据都从辅助索引中获取
    alter table t1 add index idx_gam(gender,age,money);
    当where   gender age money 这三个条件时, 效率就会变得很高
    注意, 设索引时的字段顺序与where条件的字段顺序, 最好一样, 
        第一个条件不一样就不走索引
        第二个条件不一样效率就会降低
    好处:
        减少回表查询的几率

2. 查看执行计划explain(desc)

在需要执行的select语句前面加上 explain 或者 desc

返回的结果字段分析

type字段: 索引类型
    值(性能从上往下为越来越高):
        ALL:全表扫描
            例如: select  *  from  t1;
        Index:全索引扫描
            例如: select 索引字段 from city ;
        range:索引范围扫描
            例如: 在where中用了 >  <  >=  <=  in or  between and like 'CH%'
        ref:辅助索引的等值查询
            select * from city where 辅助索引字段='XXX'
        eq_ref: 多表链接查询(join on )

        const ,system :主键或唯一键等值查询

Extra字段:额外信息
    using filesort: 文件排序
    将order by , group by , distinct 后的列和where条件列建立联合索引
        注意, 必须将where条件里的字段放在前面

possible_keys: 可能会走的索引

key: 真正走的索引

三. 不走索引的情况（开发规范）

1. 重点关注

1. 没有查询条件，或者查询条件没有建立索引

   select * from tab;   全表扫描。
   select  * from tab where 1=1;
   改为:
   select  * from tab where id=1;
   
   

2. 查询结果集是原表中的大部分数据

   查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

   假如: tab表有数据100w条, id有索引,要查后50w条, 
    select * from tab  where id>500000;
   改为:
       select * from city where id > 500000 and id < 600000
       union all 
       select * from city where id >= 600000 and id < 700000
       union all 
       select * from city where id >= 700000 and id < 700000
        ...;
   
   

3. 索引本身失效，统计数据不真实

   索引有自我维护的能力。
   对于表内容变化比较频繁的情况下，有可能会出现索引失效。

4. 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等), 子查询

   错误的例子：select * from test where id-1=9; 
   正确的例子：select * from test where id=10;
   
   

5. 隐式转换

导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

t1表的telnum字段为char类型
错误的例子: select * from t1 where telnum=110;
改正: select * from t1 where telnum='110';

原因, 用数字110去查,其实使用了函数将数字转为字符串

6. <> ，not in , !=不走索引, like "%_" 百分号在最前面不走索引

7. 单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引.

四. 建立索引的原则（运维规范）

1. 选择唯一性索引
2. 经常需要排序、分组和联合操作的字段建立联合索引
3. 为常作为where查询条件的字段建立索引
4. 尽量使用前缀来索引
5. 限制索引的数目, 尽量的少
6. 索引维护要避开业务繁忙期