MySQL索引及其实现原理-蒲公英云

MySQL索引及其实现原理

索引概述
索引是一种可以加快检索的数据库结构，它包含从表或视图的一列或多列生成的键，以及映射到指定数据存储位置的指针。索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

MySQL索引的原理：B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。
索引种类
主键索引：创建在主键上索引
辅助索引（普通索引）：创建在非主键上的索引
单例索引：创建在一个属性上的索引
多列索引：创建在多个属性上的索引
唯一索引：创建在不重复字段的索引 id（主键） email主键索引是一个特殊的唯一性索引
空间索引：空间属性索引。
全文索引：建立字符属性上的索引。
只支持CAHR，VARCHAR和TEXT类型的字段上。

引擎
聚簇型索引（InnoDB）数据文件和索引文件 .ibdB+叶子节点，存放的是数据本身。
非聚簇型索引（MyIsam）.myi .mydB+叶子节点，存放的是数据内存地址 ——-》根据地址再去找数据。
InnoDB:
主键索引：创建在主键上索引叶子节点存放就是数据本身了，叶子节点上存放的是所有的数据
辅助索引（普通索引）：创建在非主键上的索引叶子节点存放的是数据本身，只存放了创建索引的属性和主键如果想知道所有的属性，需要通过得到的这个主键再去主键索引树上查询一次得到最终的结果。
MyIsam引擎：
主键索引：创建在主键上索引B+叶子节点，存放的是数据内存地址和主键（创建索引的属性） ——-》根据地址再去找数据。
辅助索引（普通索引）：创建在非主键上的索引B+叶子节点，存放的是数据内存地址和创建索引的属性——-》根据地址再去找数据。

索引创建

单列索引：一个属性。创建是否有值：
user id name  age
id  select * from where id > 5;  
name = 'dasdas';
多列索引：index(a,b,c);
select * from  user where a = 'dsad';
select * from  user where b = 'dsad';
select * from  user where c = 'dsad';
select * from  user where a = 'dsad' and b = 'dasd' and c = 'dsad';
index(a,b,c);（1）abc 三个属性都用到的话 无论顺序 一定能用到索引  
（2）ab 无论顺序能用到索引 索引命中的字段减小 随即key_len的值也会减少
（3）ac 无论顺序能用到索引 索引命中的字段减小  key_len 只是name字段 只命中name字段（4）bc 无论顺序没有用到索引的
（5）只用单个属性 只有a 能够用到索引最左前缀原则 原理类似于  字符串大小比较的时候

注意事项：索引 —》数据结构 ——》存放在文件
索引删除
1.通过Management Studio删除索引
2.通过SQL语句删除：DROP INDEX<table or view name>.<index name>或者DROP INDEX <index name> ON <table or view name>index name表示要删除的索引名
table or view name表示当前索引基于的表名或者试图名。
查看索引

show index from table name；

索引优化
1、最左前缀匹配原则，联合索引，mysql会从做向右匹配直到遇到范围查询。
2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序。
3、索引列不能参与计算，保持列“干净”。
4、使用索引时，索引字段最好小而且唯一，避免select * 的情况。
5、尽量的扩展索引，不要新建索引。（select age from user where name = ‘dasda’; 主键是idindex（name）辅助索引 innoDB：可以用联合索引解决查询两棵索引树的问题）
6、如果确定有多少条数据，使用 limit 限制一下。
7、利用查询缓存，很多时候MySQL会对查询结果进行cache，但是对应“动态”的数据会不cache。
8、join 语法，尽量将小的表放在前面，在需要on的字段上，数据类型保持一致，并设置对应的索引，否则MySQL无法使用索引来join查询。