【MySQL】索引

一、索引基础

1、索引优化应该是对查询性能优化最有效的手段
2、包含多个列的索引，列的顺序十分重要，mysql只能高效实用最左前缀列
3、mysql中索引是在存储引擎层

二、索引的优点：

快速定位到表的位置
可以支持ORDER BY和GROUPBY
索引可以是随机I/O变成顺序I/O

三、mysql索引分类

普通索引、唯一索引、联合索引、全文索引、空间索引
普通索引：加速查找
唯一索引：
primary key(主键索引)：加速查找+约束不为空且唯一
unique(唯一索引)：加速查找+约束（唯一）
联合索引：
primary key(id,name):联合主键索引
unique(id,name):联合唯一索引
index(id,name):联合普通索引
全文索引：
fulltest:用于搜索文章

索引的使用

帮助文档
help create
help create index
--------------
1.创建索引
    -在创建表时就创建（需要注意的几点）
    create table s1(
    id int ,#可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

最左前缀匹配原则

create index ix_name_email on s1(name,email,)
- 最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

mysql区分度公式

SELECT count(distinct id)/count(*) FROM `tp_single_project_score`

四、索引数据结构分类

B-Tree索引

优点：
适合范围查询：因为所有值都是按顺序存储
特点：
每个叶子到根的距离相同
树的深度和表的大小直接相关
为什么B-Tree所有能加快访问速度？
存储引擎不在需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始查找
索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引列的顺序
除了Archive存储引擎外，其它所有存储引擎都支持B-Tree索引，在不同的存储引擎中会对B-tree索引进行改造
如：在Innodb存储引擎中，B-Tree索引实际使用的存储结构是B+Tree.

哈希索引

是什么?基于哈希表实现，只有精确匹配索引所有列的查询才有效
优点：检索效率非常高，索引的检索可以一次定位，所以查询效率远高于B-Tree索引
限制：
Hash索引仅能满足“=”、“IN”和“<=>”查询，不能使用范围查询（哈希索引只支持等值比较查询）
无法利用哈希索引进行排序运行：hash索引中存放的是经过Hash计算之后的Hash值，Hash值的大小关系并不一 定和Hash运算前的键值完全一样
对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用
Hash索引在任何时候都不能避免表扫描：Hash索引是将索引键通过Hash运算之后，将运算结果和Hash值所对应的行指针存放在一个Hash表中，由于不同索引存在相同Hash值（哈希冲突），所以满足某个Hash键值的数据的记录条数，无法从Hash索引中直接完成查询，还是需要通过访问表中的实际数据进行相应的比较，并得到结果。
Hash索引遇到大量Hash值相等的情况后性能不一定会比B-Tree索引高

不同存储引擎支持的索引类型
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，
InnoDB不支持 Hash 索引，但是支持自适应哈希索引：当某些索引值被频繁使用时，它会在内存中基于B-Tree索引上创建一个哈希索引，即，InnoDB存储引擎会根据索引的使用情况自动 优化，创建hash索引。
详情参考官网链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；