数据库之索引与慢查询优化

索引与慢查询优化

索引就类似于书的目录,可以加快数据查询速度
索引虽然能够加速查询 但是也不是越多越好
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

• primary key
• unique key
• index key

上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响:

• 在表中有大量数据的前提下，创建索引速度会很慢
• 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

b+树

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项

思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

聚集索引(primary key)

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。

myisam在建表的时候对应到硬盘有几个文件(三个)？

innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。

特点:叶子结点放的一条条完整的记录

辅助索引(unique,index)

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点： 叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})

select name from user where name='jason';

上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select age from user where name='jason';

上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

测试索引

准备

#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<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

慢查询日志: 设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！

# root 根节点
# branch 分支节点
# leaf 叶子节点

# 父子节点

# b+树
    # 平衡树(btree-balance tree) 能够让查找某一个值经历的查找速度尽量平衡
    # 分支节点不存储数据 -- 让树的高度尽量矮,让查找一个数据的效率尽量的稳定
    # 在所有叶子结点之间加入了双向的地址链接 -- 查找范围非常快

# 两种索引的差别
# 聚集索引 聚簇索引          全表数据存储在叶子节点中  innodb存储引擎的的主键就是这么存的
    # Innodb 必有且仅有一个 :主键
# 非聚集(簇)索引 辅助索引    叶子结点不存放具体的整行数据，而是存储的这一行的主键的值
    # innodb            就是你建的（create)索引，如果要拿自己的值就不要回表，不然都要回到聚集索引查询
    # myisam

# innodb存储引擎中的 主键默认就会创建一个聚集索引

# 数据库使用的时候有什么注意事项
    # 从搭建数据库的角度上来描述问题
    # 建表的角度上
        # 1.合理安排表关系
        # 2.尽量把固定长度的字段放在前面
        # 3.尽量使用char代替varchar
        # 4.分表: 水平分,垂直分
    # 使用sql语句的时候
        # 1.尽量用where来约束数据范围到一个比较小的程度,比如说分页的时候
        # 2.尽量使用连表查询而不是子查询
        # 3.删除数据或者修改数据的时候尽量要用主键作为条件
        # 4.合理的创建和使用索引
            # 1.查询的条件字段不是索引字段
                # 对哪一个字段创建了索引,就用这个字段做条件查询
            # 2.在创建索引的时候应该对区分度比较大的列进行创建
                # 1/10以下的重复率比较适合创建索引
            # 3.范围
                # 范围越大越慢
                # 范围越小越快
                # like 'a%'  快
                # like '%a'  慢
            # 4.条件列参与计算/使用函数  不能命中索引
            # 5.and和or
                # id name
                # select * from s1 where id = 1800000 and name = 'eva';
                # select count(*) from s1 where id = 1800000 or name = 'eva';
                # 多个条件的组合,如果使用and连接
                    # 其中一列含有索引,都可以加快查找速度
                # 如果使用or连接
                    # 必须所有的列都含有索引,才能加快查找速度
            # 6.联合索引 : 最左前缀原则(必须带着最左边的列做条件,从出现范围开始整条索引失效)  指括号里的内容  对id。 id，name 。name,email。 id name email.有效
                # (id,name,email)  与其在语句中的顺序无关，至于括号内的顺序有关
                # select * from s1 where id = 1800000 and name = 'eva' and email = 'eva1800000@oldboy';
                #这里句中的id顺序没有影响
                # select * from s1 where id = 1800000 and name = 'eva';
                # select * from s1 where id = 1800000 and email = 'eva1800000@oldboy';
                # select * from s1 where id = 1800000;
                # select * from s1 where name = 'eva' and email = 'eva1800000@oldboy';
                # (email,id,name)  这里不能将id放在括号的前面，因为一上来是个范围整个缩影就失效了
                # select * from s1 where id >10000 and email = 'eva1800000@oldboy';
            # 7.条件中写出来的数据类型必须和定义的数据类型一致
                # select * from biao where name = 666   # 不一致
            # 8.select的字段应该包含order by的字段
                # select name,age from 表 order by age;  # 比较好
                # select name from 表 order by age;  # 比较差
# https://www.cnblogs.com/Eva-J/articles/10126413.html


# select * from 表 where 条件 group by 分组 having 聚合;
# 300万条数据
# 分页
# page = 1
# num_per = 10
# tmp = (page-1)*num_per = 1-1=0*10 = 0
# select * from 表 where id between tmp and tmp+num_per
# page +=1 = 2
# tmp = (page-1)*num_per = 10
# select * from 表 where id between 10 and 20
#
# select * from 表 limit 10,10
# select * from 表 limit 20,10
#
# select * from 表 limit 2999990,10

# 联合索引
# (id,email)
# id = 100 and email like 'eva%';

# 索引合并 :分开创建在查询过程中临时合并成一条 Using union(ind_id,ind_email)
    # 创建索引的时候
    # create index ind_id on s1(id)
    # create index ind_email on s1(email)
    # select * from s1 where id=100 or email = 'eva100@oldboy'
    # 临时把两个索引ind_id和ind_email合并成一个索引


# 覆盖索引:在查询过程中不需要回表   Using index
    # 对id字段创建了索引
    # select id from s1 where id =100     覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
    # select count(id) from s1 where id =100     覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
    # select max(id) from s1 where id =100     覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
    # select name from s1 where id =100   相对慢

# 什么是mysql的执行计划?用过explain么?
    # 在执行sql语句之前,mysql进行的一个优化sql语句执行效率的分析(计划),可以看到有哪些索引,实际用到了那个索引,执行的type等级
    # id name email
    # select * from s1 where id = 1000000 and name=eva and email = 'eva1000000@oldboy';
        # 有没有索引
        # 有几个
        # 用哪一个索引比较效率高
    # explain select * from s1 where id = 1000000 and name=eva and email = 'eva1000000@oldboy';

#

 

 

# b+树
    # b是balance 平衡的意思
        # 为了保证每一个数据查找经历的IO次数都相同
    # 只在叶子节点存储数据
        # 为了降低树的高度
    # 叶子节点之前加入了双向连接
        # 为了查找范围的时候比较快




# 聚集索引(聚簇索引)
    # 全表数据都存储在叶子节点上 -- Innodb存储引擎中的主键    如果没有全部命中索引的话就要查询聚集索引
# 非聚集索引(非聚簇索引)/辅助索引
    # 叶子节点不存放具体的整行数据,而是存储的这一行的主键的值

# 索引的创建和删除
    # create index ind_name on 表名(字段名);
    # create index ind_name on 表名(字段名,字段2);
    # drop index 索引名 on 表名

# 正确的使用mysql数据库
    # 从库的角度
        # 搭建集群
        # 读写分离
        # 分库
    # 从表的角度
        # 合理安排表与表之间的关系 :该拆的拆,该合的合
        # 把固定长度的字段放在前面
        # 尽量使用char而不是varchar
    # 从操作数据的角度
        # 尽量在where字段就约束数值到一个比较小的范围 : 分页
            # where a between value1 and value2   a必须创建索引才简单
        # 尽量使用连表查询代替子查询
        # 删除数据和修改数据的时候条件尽量使用主键
        # 合理的创建和使用索引
            # 创建索引
                # 1.选择区分度比较大的列        重复率低
                # 2.尽量选择短的字段创建索引        数据在硬盘上是按照block块进行存储
                # 3.不要创建不必要的索引,及时删除不用的索引
            # 使用索引
                # 1.查询的字段不是索引字段
                # 2.在条件中使用范围,结果的范围越大速度越慢,范围小就快
                # 3.like 'a%'命中索引,like '%a'不命中索引
                # 4.条件列不能参与计算\不能使用函数
                # 5.and/or
                    # and条件相连 有一列有索引都会命中
                    # or条件相连 所有列都有索引才能命中
                # 6.联合索引
                    # create index mix_ind on 表 (id,name,email)  与其在语句中的顺序无关，只与括号内的顺序有关
                    # 遵循最左前缀原则,且从出现范围开始索引失效
                    # select * from 表 where id = 123; 命中索引
                    # select * from 表 where id > 123; 不命中索引
                    # select * from 表 where id = 123 and name = 'alex'; 命中索引
                    # select * from 表 where id > 123 and name = 'alex'; 不命中索引
                    # select * from 表 where id = 123 and email = 'alex@oldboy'; 命中索引
                    # select * from 表 where email = 'alex@oldboy'; 不命中索引,因为条件中没有id
                    # select * from 表 where name='alex' and email = 'alex@oldboy'; 不命中索引,因为条件中没有id
                # 7.条件中的数据类型和实际字段的类型必须一致
                # 8.select字段中应该包含order by 中的字段
                    # select age from 表 order by age;   快
                    # select name from 表 order by age;  慢

# 覆盖索引 : 查询过程中不需要回表
#  select id from 表  where id > 10000000;
#  select max(id) from 表  where id > 10000000;
#  select count(id) from 表  where id > 10000000;

# 索引合并 : 分别创建的两个索引在某一次查询中临时合并成一条索引  a=1 or b=2
# 执行计划 : explain select 语句 ;能够查看sql语句有没有按照预期执行,可以查看索引的使用情况,type等级
# 慢查询优化 :
    # 首先从sql的角度优化
        # 把每一句话单独执行,找到效率低的表,优化这句sql
        # 了解业务场景,适当创建索引,帮助查询
        # 尽量用连表代替子查询
        # 确认命中索引的情况
    # 考虑修改表结构
        # 拆表
        # 把固定的字段往前调整
    # 使用执行计划,观察sql的type通过以上调整是否提高
# mysql的慢日志
    # 在mysql的配置中开启并设置一下
    # 在超过设定时间之后,这条sql总是会被记录下来,
    # 这个时候我们可以对这些被记录的sql进行定期优化