MySQL–索引的使用

数据准备

创建30万条数据

# 执行前3步即可，因数据量庞大，创建时间可能超级长！！！可以另打开一个数据表用来查询数据个数
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<300000)do
        insert into s1 values(i,'wuye','female',concat('wuye',i,'@blog'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 调用存储过程
call auto_insert1();
#4. 查看存储过程
show create procedure auto_insert1\G

使用索引前，不妨先自己查询一下看看执行时间，当索引创建成功后，再利用索引查询看一下时间对比啦！

索引介绍

# 索引的优势
    # 根据带有索引的字段查询速度非常快
# 索引的劣势
    # 创建索引同样占用磁盘空间，索引越多，占用内存越多
    # 索引越多，写入数据越慢
    # 删除索引并不会删除创建索引的数据结构，内存不会变小

索引原理

# 索引的原理
    # block 磁盘预读原理
        # for line in f
        # 4096个字节
    # 读硬盘的io操作的时间非常的长，比CPU执行指令的时间长很多
    # 尽量的减少IO次数才是读写数据的主要要解决的问题

    # 数据库的存储方式
        # 新的数据结构 —— 树
        # 平衡树 balance tree - b树
        # 在b树的基础上进行了改良 - b+树
            # 1.分支节点和根节点都不再存储实际的数据了
                # 让分支和根节点能存储更多的索引的信息
                # 就降低了树的高度
                # 所有的实际数据都存储在叶子节点中
            # 2.在叶子节点之间加入了双向的链式结构
                # 方便在查询中的范围条件
        # mysql当中所有的b+树索引的高度都基本控制在3层
            # 1.io操作的次数非常稳定
            # 2.有利于通过范围查询
        # 什么会影响索引的效率 —— 树的高度
            # 1.对哪一列创建索引，选择尽量短的列做索引
            # 2.对区分度高的列建索引，重复率超过了10%那么不适合创建索引

聚焦索引和辅助索引

# 聚集索引和辅助索引
    # 在innodb中，聚集索引和辅助索引并存的
        # 聚集索引 -- 主键 更快
            # 数据直接存储在树结构的叶子节点
        # 辅助索引 -- 除了主键之外所有的索引都是辅助索引，稍慢
            # 数据不直接存储在树中
    # 在myisam中，只有辅助索引，没有聚集索引

索引的种类

# 索引的种类
    # primary key,主键,默认创建聚集索引  约束的作用：非空 + 唯一
        # 联合主键
    # unique 自带索引 辅助索引   约束的作用：唯一
        # 联合唯一
    # index  辅助索引            没有约束作用
        # 联合索引

索引的创建与删除

# 创建单个索引   
    # 注意：如果表中数据越多，创建索引越慢，索引越多，占用磁盘空间越多，且写入数据越慢
create index 索引名 on 表名(需要创建索引的字段);
create index ind_id on t1(id);  # 在 t1 表为 id 字段 创建索引，索引名为 ind_id;

# 删除索引  注意：删除索引并不会删除创建索引的数据结构
drop index 索引名 on 表名;
drop index ind_id on t1;          # 在 t1 表中删除索引名为 ind_id 的索引；

# 创建联合索引　　# 注意：使用联合索引时要命中索引必须要有字段1，否则索引不生效
create index 索引名 on 表名(字段1,字段2...);

重建索引

在验证之前，我们需要先知道如何可以查看MySQL中找到索引的创建或修改时间，可以通过如下的方式间接的进行查看：

1、表的创建时间，可以间接推断索引的创建时间。因为索引的创建时间肯定在表的创建时间之后。

2、对应表的idb文件的修改或创建时间，当然这种方法不是非常准确。对于InnoDB存储引擎的表而言，对应的索引数据存储在ibd文件中，所以文件的创建时间或修改时间是间接判断索引创建时间。如果存储引擎为MyISAM的话，还有专门的索引文件MYI。

方法一：(线上最经常用的方法，注意要与数据库引擎保持一致，非InnoDB引擎未测试，效果未知)
```
ALTER TABLE t1 ENGINE = InnoDB;
```
方法二：

OPTIMIZE TABLE也可以对索引进行重建，官方文档的介绍如下：

OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

意思就是说OPTIMIZE TABLE操作使用Online DDL模式修改Innodb普通表和分区表，这样减少了并发DML操作的停机时间；该方式会在prepare阶段和commit阶段持有表级锁：在prepare阶段修改表的元数据并且创建一个中间表，在commit阶段提交元数据的修改。由于prepare阶段和commit阶段在整个事务中的时间比例非常小，可以认为该OPTIMIZE TABLE的过程中不影响表的其他并发操作。
```
optimize table t1, t2;
```
方法三：

REPAIR TABLE方法用于修复被破坏的表，而且它仅仅能用于MyISAM, ARCHIVE,CSV类型的表。如下是官方文档描述：The REPAIR TABLE method is only applicable to MyISAM, ARCHIVE, and CSV tables.
如果表是innodb表的话，就会出现提示该存储引擎不支持repair操作
```
REPAIR TABLE t1;
```

验证是否重建：如果重建完成，create_time时间会发生改变

SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t1';

索引的命中

索引是否生效

# 以email为条件查询
    # 不添加索引的时候肯定慢
    # 查询的字段不是索引字段，也慢
# id作为条件的时候
    # 如果不加索引，速度慢
    # 加了索引，速度快

# 索引不生效的原因
    # 要查询的数据的范围大
        # > < >= <= !=
        # between and
            # select * from 表 order by age limit 0,5;  # 使用limit 速度慢
            # select * from 表 where id between 1000000 and 1000005;  # 使用between速度快
        # like
            # 结果的范围大 索引不生效
            # 如果 abc% 索引生效，%abc索引就不生效
    # 如果一列内容的区分度不高，索引也不生效
        # name列
    # 索引列不能在条件中参与计算
        # select * from s1 where id*10 = 1000000;  索引不生效
        # select * from s1 where id = 1000000*10;  索引生效
    # 对两列内容进行条件查询
        # and and条件两端的内容，优先选择一个有索引的，并且树形结构更好的，来进行查询
            # 两个条件都成立才能完成where条件，先完成范围小的缩小后面条件的压力
            # select * from s1 where id =1000000 and email = 'wuye1000000@blog';
        # or or条件的，不会进行优化，只是根据条件从左到右依次筛选
            # 条件中带有or的要想命中索引，这些条件中所有的列都是索引列
            # select * from s1 where id =1000000 or email = 'wuye1000000@blog';
    # 联合索引  # create index ind_mix on s1(id,name,email);
        # select * from s1 where id =1000000 and email = 'wuye1000000@blog';
        # 在联合索引中如果使用了or条件索引就不能生效
            # select * from s1 where id =1000000 or email = 'wuye1000000@blog';
        # 最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列
            # select * from s1 where id =1000000;    能命中索引
            # select * from s1 where email = 'wuye1000000@blog';  不能命中索引
            # (a,b,c,d)
                # a,b
                # a,c
                # a
                # a,d
                # a,b,d
                # a,c,d
                # a,b,c,d
        # 在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了
            # select * from s1 where id >1000000 and email = 'wuye1000001@blog';
            # select * from s1 where id =1000000 and email like 'wuye%';

各种索引的使用场景

# 什么时候用联合索引
    # 只 对a 对abc 条件进行索引
    # 而不会对b，对c进行单列的索引

# 单列索引
    # 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符
# 使用or来连接多个条件
    # 在满上上述条件的基础上
    # 对or相关的所有列分别创建索引

# 覆盖索引
    # 如果我们使用索引作为条件查询，查询完毕之后，不需要回表查，覆盖索引
    # explain select id from s1 where id = 1000000;
    # explain select count(id) from s1 where id > 1000000;
# 合并索引
    # 对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这个时候这两个索引就成为了合并索引

执行计划

# 执行计划 : 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划
    # 情况1：
        # 30000000条数据
            # sql 20s
            # explain sql   --> 并不会真正的执行sql，而是会给你列出一个执行计划
    # 情况2：
        # 20条数据 --> 30000000
            # explain sql

注意

# 索引的命中：
    # 范围，条件的字段是否参与计算(不能用函数)
    # 列的区分度(长度)，条件and/or
    # 联合索引的最左前缀问题
# 建表、使用sql语句的时候注意的
    # char 代替 varchar
    # 连表 代替 子查询
    # 创建表的时候 固定长度的字段放在前面