索引的使用

数据准备

# 执行前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...);

 

索引的命中

# 以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
    # 连表 代替 子查询
    # 创建表的时候 固定长度的字段放在前面

 

原文链接：https://www.cnblogs.com/Eva-J/articles/10126413.html#_label3