一、什么是索引
1.在关系型数据库中，索引是一种单独的、物理层面的对数据表中一列或多列的值进行排序的一种存储结构，也称之为key
2.可以理解：搜索引导，索引是一个特殊的数据结构，其存储的是数据的关键信息与详细信息的位置关系，例如：图书的目录，可以根据目录中的页码快速找到所需的内容
3.有三种索引：unique key、primary key、index key
二、为什么需要索引
加速查询，当数据量非常大的时候，查询某一个数据集是非常慢，索引是提升查询效率最有效的手段
三、索引的影响：
1.注意：在数据库中插入数据会引发索引的重建
2.不是说有了索引就能加速，得看你的查询语句有没有正确使用索引
3.索引也需要占用额外的数据空间
4.添加索引后将导致增减删除修改变慢（写入）
四、什么样的数据应该添加索引：
1.查询操作较多，写入较少并且数据量很大时
2.查询与写入操作的占比为 10：1 ，或者查询更多时
五、索引的实现原理
1.相关知识
1.数据库的索引，实现思路与字典是一致的，需要一个独立的存储结构，专门存储索引数据
2.本质上索引是通过不断缩小查询范围来提高查询效率
2.磁盘io
1.平均查找一个数据需要花费至少9.15ms，这段时间CPU就会切换到其他的程序
2.操作系统预读取：当一次io时，不光把当前磁盘地址的数据读取，而是把相邻的数据也都读取到内存缓冲区中，这个理论对于索引的数据结构设计非常有帮助
3.我们要加速查询，必须减少io操作的次数
3.索引数据结构
1.b+树（第一层为根节点只能有一个，中间层为分支节点，最下面的是叶子节点，当叶子节点过多时，分支节点势必也会相应的增加，上面的根节点也就需要多个，由于根节点有且只有一个的特性，因此只能增加层数，那么查询时遇到的io操作也就相应的增加了，影响效率）：
2.在b+树种，叶子节点才是存储真实数据的，叶子数量越多，树的层级越高，进而导致io操作增加（一层一个io操作）
3.要避免这个问题，在叶子节点中尽可能的存储更多的数据，应该将数据量小的字段作为索引
4.最左匹配原则：
1.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候(多字段联合索引)，b+树会按照从左到右的顺序来建立搜索树，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性
2.索引在查找时，是按照从左往右依次比较，如果查询语句没有出现最左边的索引，将无法加速查询
5.聚集索引：
1.聚集索引种包含了所有字段的值，如果已指定了主键，主键就是聚集索引，如果没有则找一个非空且唯一的字段作为聚集索引，如果连这个也找不到，则自动生成一个字段作为聚集索引
2.聚集索引中存储了所有的数据
6.辅助索引
1.除了聚集索引以外的都叫做辅助索引，包括foreign key与unique
2.辅助索引中只包含当前的索引字段和主键的值
7.覆盖查询
1.指的是在当前索引结构中就能找到所有需要的数据，如果使用的是聚集索引来查询那么一定覆盖查询，速度是最快的
2.如果覆盖索引指定的数据仅在辅助索引中能找到，则不需要再查找聚集索引数据就能找到：
# 比如
# 假设stu表的name字段是一个辅助索引
select name from stu where name = 'jack':
8.回表查询
1.指的是在当前索引结构中（辅助索引）找不到所需的数据，需要通过主键id去聚集索引中查询，速度慢于聚集索引
2.步骤：从辅助索引中获取主键的值，再拿着主键值到聚集索引中找到sex值
# 比如
# name字段是一个辅助索引，而sex字段不是索引
select sex from stu where name = 'jack';
9.查询速度对比
聚集索引 > 覆盖索引 > 非覆盖索引
六、正确使用索引
1.测试
# 建表
create table usr(id int,name char(10),gender char(3),email char(30));
#准备数据
delimiter //
create procedure addData(in num int)
begin
declare i int default 0;
while i < num do
insert into usr values(i,"jack","m",concat("xxxx",i,"@qq.com"));
set i = i + 1;
end while;
end//
delimiter ;
#执行查询语句 观察查询时间
select count(*) from usr where id = 1;
#1 row in set (3.85 sec)
#时间在秒级别 比较慢
1.
#添加主键
alter table usr add primary key(id);
#再次查询
select count(*) from usr where id = 1;
#1 row in set (0.00 sec)
#基本在毫秒级就能完成 提升非常大
2.
#当条件为范围查询时
select count(*) from usr where id > 1;
#速度依然很慢 对于这种查询没有办法可以优化因为需要的数据就是那么多
#缩小查询范围 速度立马就快了
select count(*) from usr where id > 1 and id < 10;
#当查询语句中匹配字段没有索引时 效率测试
select count(*) from usr where name = "jack";
#1 row in set (2.85 sec)
# 速度慢
3.
# 为name字段添加索引
create index name_index on usr(name);
# 再次查询
select count(*) from usr where name = "jack";
#1 row in set (3.89 sec)
# 速度反而降低了 为什么?
#由于name字段的区分度非常低 完全无法区分 ,因为值都相同 这样一来B+树会没有任何的子节点,像一根竹竿每一都匹配相当于,有几条记录就有几次io ,所有要注意 区分度低的字段不应该建立索引,不能加速查询反而降低写入效率,
#同理 性别字段也不应该建立索引,email字段更加适合建立索引
# 修改查询语句为
select count(*) from usr where name = "aaaaaaaaa";
#1 row in set (0.00 sec) 速度非常快因为在 树根位置就已经判断出树中没有这个数据 全部跳过了
# 模糊匹配时
select count(*) from usr where name like "xxx"; #快
select count(*) from usr where name like "xxx%"; #快
select count(*) from usr where name like "%xxx"; #慢
#由于索引是比较大小 会从左边开始匹配 很明显所有字符都能匹配% 所以全都匹配了一遍
4.索引字段不能参加运算
select count(*) from usr where id * 12 = 120;
#速度非常慢原因在于 mysql需要取出所有列的id 进行运算之后才能判断是否成立
#解决方案
select count(*) from usr where id = 120/12;
#速度提升了 因为在读取数据时 条件就一定固定了 相当于
select count(*) from usr where id = 10;
#速度自然快了
5.有多个匹配条件时 索引的执行顺序 and 和 or
#先看and
#先删除所有的索引
alter table usr drop primary key;
drop index name_index on usr;
#测试
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "xxxx2@qq.com";
#1 row in set (1.34 sec) 时间在秒级
#为name字段添加索引
create index name_index on usr(name);
#测试
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "xxxx2@qq.com";
#1 row in set (17.82 sec) 反而时间更长了
#为gender字段添加索引
create index gender_index on usr(gender);
#测试
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "xxxx2@qq.com";
#1 row in set (16.83 sec) gender字段任然不具备区分度
#为id加上索引
alter table usr add primary key(id);
#测试
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "xxxx1@qq.com";
#1 row in set (0.00 sec) id字段区分度高 速度提升
#虽然三个字段都有索引 mysql并不是从左往右傻傻的去查 而是找出一个区分度高的字段优先匹配
#改为范围匹配
select count(*) from usr where name = "jack" and gender = "m" and id > 1 and email = "xxxx1@qq.com";
#速度变慢了
#删除id索引 为email建立索引
alter table usr drop primary key;
create index email_index on usr(email);
#测试
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "xxxx2@qq.com";
#1 row in set (0.00 sec) 速度非常快
#对于or条件 都是从左往右匹配
select count(*) from usr where name = "jackxxxx" or email = "xxxx0@qq.com";
#注意 必须or两边都有索引才会使用索引
and 语句中只要有一个存在索引就能提高速度
6.多字段联合索引
为什么需要联合索引
案例:
select count(*) from usr where name = "jack" and gender = "m" and id > 3 and email = "xxxx2@qq.com";
假设所有字段都是区分度非常高的字段,那么除了id为谁添加索引都能够提升速度,但是如果sql语句中没有出现索引字段,那就无法加速查询,最简单的办法是为每个字段都加上索引,但是索引也是一种数据,会占用内存空间,并且降低写入效率
此处就可以使用联合索引,
联合索引最重要的是顺序 按照最左匹配原则 应该将区分度高的放在左边 区分度低的放到右边
#删除其他索引
drop index name_index on usr;
drop index email_index on usr;
#联合索引
create index mul_index on usr(email,name,gender,id);
create index mul_index on usr(id,email,gender,name); # 推荐
# 查询测试
select count(*) from usr where name = "xx" and id = 1 and email = "xx";
只要语句中出现了最左侧的索引(email) 无论在前在后都能提升效率
drop index mul_index on usr;
2.结论：
1.使用占用空间最小的字段作为索引
2.不要在一行中存储太多的数据，例如小说、视频，如果字段太多可以分表
3.尽量使用覆盖查询
4.如果字段区分度低（字段重复度高），建立索引是没有意义的，反过来说应该将区分度高的字段（比如自增的id）作为索引
5.模糊匹配中，百分号尽量不要写在前面
6.不要在等号的左边做运算，例如：select count(*) from usr where id*3 = 6 -- 也会遍历所有记录 ;
7.and语句中会自动找一个具备索引的字段优先执行，所以我们应该在and语句中至少包含一个具备索引的字段
8.or语句要避免使用，如果要用，则要保证所有字段都有索引才能加速
9.联合索引中，顺序应该将区分度最高的放到左边，最低的放到右边，查询语句中必须保证最左边的索引出现在语句中
3.需要注意的是：
1.如果要查询的数据量非常大时，索引将无法加速
2.不是添加了索引就能提速，需要考虑索引添加的是否合理，SQL语句是否使用到了索引
七、语法
1.创建索引的语法：
create index 索引的名字 on 表名称(字段名);
2.删除索引：
drop index 索引名称 on 表名;
3.联合索引：
create index 索引名称 on 表名(字段名1，字段名2,......)