一、索引理论
ps:数据都是存在于硬盘上的，查询数据不可避免的需要进行IO操作
# 索引:就是一种数据结构，类似于书的目录。
意味着以后在查询数据的应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低IO操作
# 索引在MySQL中也叫“键”，是存储引擎用于快速查找记录的一种数据结构
primary key # 主键索引 用途：加速查询 约束：唯一 且不为空
unique key # 唯一索引 用途：加速查询 约束：不为空
index key # 普通索引 用途：加速查询 约束：无
# 注: foreign key 外键 不是用来加速查询用的，是用来限制表之间的关系的，不在索引的研究范围之内
# 添加索引的本质： 索引建立在表的列上(字段)的
将某个字段添加成索引，就相当于依据该字段，建立了一颗b+树 从而加快查询速度
如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找)
# 索引的存放位置
Innodb 两个文件 直接将主键存放在了s1.idb表中
MyIsam 三个文件 单独将索引存在一个索引文件table1.MYI
# 索引的原理：
通过不断的缩小想要的数据范围筛选出最终的结果，
同时将随机事件(一页一页的翻)，变成顺序事件(先找目录、找数据)，
也就是说有了索引机制，我们可以总是用一种固定的方式查找数据
# 缺点：
一张表中可以有多个索引(多个目录)，索引虽然能够帮助你加快查询速度但是也有缺点
1 当表中有大量数据存在的前提下 创建索引速度会很慢
2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
# 理解：本来只需要写数据，现在需要额外操作(建索引)，数据越多，索引就越复杂(就越慢)
# 注意：索引不要随意的创建！！！
1. B+树
# B+树：
是树状数据结构中的一种，其他树状的数据结构还有：二叉查找树、平衡二叉树、B树
B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的
# Mysql中的Innodb存储引擎 默认的索引结构为B+树
# B+树的特点：
1.内部枝节点不保存数据的，只作索引作用，叶子节点才保存数据。
# 只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 仅仅是用来指路的
2.树的层级越高，查询数据所需要经历的步骤就越多 (树有几层查询数据就需要几步)
# 为什么建议将id字段(自增主键)作为索引 int占4个字节，bigint占8个字节
因为id字段占得空间少，一个磁盘块能够存储的数据多
间接降低 B+Tree 的高度，提高搜索效率
# 个人理解：
B+树的各个节点，是存储在不同的磁盘块上
而一个磁盘块存储是有大小限制的
数据项越小，每个节点(磁盘块) 存储的 元素数量就越多
同样的数据元素，存放在的节点(磁盘块)就越少
就降低了树的高度（层级），从而减少了磁盘块的IO读取次数
2. 聚集索引(Clustered Index)
# 聚集索引指的就是主键索引 PRIMARY KEY
按照每张表的主键构造一棵B+树，同时 叶子节点中存放的即为整张表的行记录数据。
# InnoDB存储引擎：
-若定义了主键，InnoDB会自动使用 主键 来创建聚集索引。
-若没有定义主键，InnoDB会选择一个 唯一的非空索引 代替主键作为聚集索引。
-若没有唯一的非空索引，InnoDB会 隐式定义一个主键 来作为聚集索引。
3. 辅助索引(Secondary Index)
# 前景：
查询数据时不可能一直使用到主键，也有可能会用到name,password等其他字段
那么此时不能利用聚集索引，就可以根据情况 给其他字段设置辅助索引(也是一个b+树）
# 辅助索引：也叫非聚集索引、二次索引
除主键索引之外，都是辅助索引，包含：唯一索引(unique key）、普通索引(index key)
# 与聚集索引的对比：
聚集索引的叶子结点存储的是 一行完整的数据
辅助索引的叶子结点存储的是 数据的主键值
# 根据 辅助索引 查到完整数据的流程：
eg: select * from user where username='javaboy'
1.先去辅助索引的B+树，拿到数据的主键值
2.再去主键索引的B+树，查询行的全部数据
4. 覆盖索引(Covering index)
# 覆盖索引：
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。
# 即在辅助索引的叶子节点，就已经拿到了需要的数据，而不需要再次查询聚集索引中的记录。
# InnoDB存储引擎支持覆盖索引
# 覆盖索引的好处
可以减少大量的IO操作
# 给name设置辅助索引后 查询name字段数据，能直接从name索引B+树 获取
select name from user where name='jason';
# 非覆盖索引
select age from user where name='jason';
5. 前缀索引
# 前缀索引: 按照该列数据的前n个字母创建索引
# 好处：
如果索引字段的值很长，最好使用值的前缀来索引
例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间
如果只检索字段的前面的若干个字符，这样可以提高检索速度
6. 联合索引
# 联合索引：指对表上的多个列进行索引 专门用于组合搜索
# 种类：
PRIMARY KEY(id,name) : 联合主键索引
UNIQUE(id,name) : 联合唯一索引
INDEX(id,name) : 联合普通索引
# 好处：
是在第一个键相同的情况下，已经对第二个键进行了排序处理
7. 测试索引是否有效的代码
感兴趣就自己试一试 不感兴趣直接忽略
**准备**
# 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字段一剑封喉
#### 联合索引
mysql
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'; # 速度变快
总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了
二、索引操作
https://www.yuque.com/nidhogg14/wssb4n/gevr3z#nU4u0
# 查看表结构
desc 表名;
# 查看生成表的SQL
show create table 表名 \G; # \G表示美化输出
# 查看索引
show index from 表名;
# 查看执行时间 了解SQL执行的线程的状态及消耗的时间。
set profiling = 1; # 默认是关闭的 开启 show profile分析
SQL...
show profiles;
2.1 创建索引
# 几种索引的创建时间：
主键索引 # 创建表时
唯一索引 # 创建表时
普通索引 # 创建表时 和 表已创建 ***
##### 创建表时
# 语法：
1.单独字段设置索引：主键、唯一、普通索引 可直接写在字段的 约束条件里
2.也可以类似联合索引一样 单独设置索引
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
# 单独设置索引时：
[PRIMARY | UNIQUE | FULLTEXT | SPATIAL] # 主键、唯一、全文和空间 后两者几乎不用
INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
# eg:
create table t1(
id int primary key, # 约束条件里 设置主键索引
name char,
age int,
sex enum('male','female'),
unique key uni_id(id), # 给id字段建 名叫uni_id 的唯一索引
index ix_age(age), # 普通索引 index没有key
index ix_name(name(8)) # 前缀索引 name字段的前8个字符
);
##### 表已创建
# 语法1：
CREATE [UNIQUE] INDEX 索引名
ON
表名(字段名[(长度)] [ASC | DESC]);
# eg：
create index ix_age on t1(age);
# 语法2：
ALTER TABLE 表名 ADD [UNIQUE] INDEX
索引名(字段名[(长度)] [ASC | DESC]);
# eg:
alter table t1 add UNIQUE index ix_sex(sex);
2.2 删除索引
# 删除索引
# 语法：
DROP INDEX 索引名 on 表名
或
alter table 表名 drop index 索引名;
# eg：
DROP INDEX index_emp_name on emp1;
alter table emp1 drop index index_emp_name;
三、索引原则
3.1 设计原则
# 背景
为了使索引的使用效率更高，在创建索引时必须考虑
在哪些字段上创建索引 创建什么类型的索引
# 索引设计原则
# 1.唯一性字段 建立索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
# 主键索引和唯一键索引，在查询中使用是效率最高的 !!!
# eg：
学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息
如果使用姓名的话，可能存在同名现象，从而降低查询速度
# 2.常排序、分组和联合操作的字段 建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段
排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作
# 3.常查询条件的字段 建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度
但 是不是真的适合去做索引，根据区分度去判断 # 简单讲：该列的值 越不重复 越适合
# 区分度： count(distinct col)/count(*) 表示字段不重复的比例 比例越大 越适合
统计该列去重之后的行数 和 总行数对比 # 唯一键的区分度是1
# eg: 性别 就不合适作为索引列
select count(DISTINCT population ) from city;
select count(*) from city;
# 4.短索引原则：尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引，节省大量索引空间
# eg: TEXT和BLOG类型的字段，进行全文检索会很浪费时间。
备注 列字段VARCHAR(200) 如果该列设置为索引列，查询效率不很高
因为索引字段长度过大，索引节点树高增加，I/O次数也会增加
将 备注 字段值得前10个字符设置为索引，节省索引空间
# 5.限制索引的数目
设置索引时要考虑设置合适的列，不要造成“过多的索引列”
因为每个索引都需要占用磁盘空间，并降低写操作的性能
并且修改表时，对索引的重构和更新很麻烦。索引列越多，会使更新表变得很浪费时间
# 对于长时间不再使用或者很少使用的索引要进行删除操作
3.2 优化原则
# 索引的优化原则：
就是避免查询 出现不走索引查询(索引未命中)的情况，从而导致全表扫描数据
# 索引未命中(失效)的情况： 重点关注
# 1.没有查询条件 或者 查询条件没有建立索引
# 注：在业务数据库中 特别是数据量比较大的表，是没有全表扫描这种需求
1.对用户查看是非常痛苦的
2.对服务器来讲毁灭性的
# eg:
1.select * from tab; # 没有查询条件 直接全表扫描
改？
select * from tab order by price limit 10 # 需要在price列上建立索引
2.select * from tab where name='zhangsan' # 查询条件name列 没有索引
改？
1.换成有索引的列作为查询条件
2.或 将name列建立索引
# 2.查询结果集是原表中的大部分数据，应该是30％以上
查询的结果集，超过了总数行数30%，优化器觉得就没有必要走索引了
# eg：
1.tab表(id, name) id:1-100w , id列有索引
select * from tab where id>500000; # 此时结果集>30%， 不会走索引了
改 ？
1.如果业务允许，可以使用limit控制
2.结合业务判断，有没有更好的方式
如果没有更好的改写方案，尽量不要在mysql存放这个数据了 放到redis里面。
# 3.查询条件使用函数在索引列上 或者 对索引列进行运算 包括(+ - * / !等)
# eg:
1.select * from test where id-1=9;
改？
select * from test where id=10;
2.select * from test where from_unixtime(create_time) = ’2014-05-29’;
改？
select * from test where create_time = unix_timestamp(’2014-05-29’);
# 4.隐式转换 开发中经常会犯的错误 !!!
数据类型出现隐式转换的时候不会命中索引
特别是当列类型是字符串，一定要将字符常量值用引号引起来
# eg: tu_mdn为varchar2(20) 传递了number类型
1.select * from test where tu_mdn=13333;
改？
select * from test where tu_mdn='1333';
# 5.负向条件查询 !=、<>、not in、not exists、not like等
即：优化为in查询
# 注：
1.单独的>,<,in 可能走，跟结果集和版本有关，尽量结合业务添加limit
2.union、in、or都能够命中索引 # or或in 尽量改成union
# eg:
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改？
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'
# 6.like "%_" 左模糊匹配(百分号在最前面)
# eg：
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' # 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' # 不走索引
改？
%linux%类的搜索需求 可以使用elasticsearch
# 7.联合索引时，违背最左前缀匹配原则
即：联合索引 保证查询条件中存在最左索引
# 强调：
1.组合索引(a,b,c)的情况下，实际上相当于建立了(a), (a,b), (a,b,c)共三个索引
2.最左匹配原则并不是指查询条件的顺序，而是指查询条件中是否包含索引最左列字段
# eg：
CREATE TABLE t1 (
id INT,
NAME VARCHAR(20),
age INT ,
sex ENUM('m','f'),
money INT
);
# 复合索引：money,age,sex
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
1.走索引的情况： 跟查询条件的顺序无关
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m'; # 部分走索引
2.不走索引的情况： 未包含最左列字段
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
# 8.索引本身失效，统计数据不真实
索引有自我维护的能力
对于表内容变化比较频繁的情况下，有可能会出现索引失效