详解MySQL索引机制及原理 - 主机乐

列名title	解释desc	取值value
table	索引对应表的名称	DB中的表
Non_unique	索引包含value是否为唯一（是否为唯一索引）	0代表是唯一，1代表不是
Key_name	索引的名称	不命名为创建时列名称，联合查询为Seq_in_index为1的列名称，重复是使用_+number区分
Seq_in_index	索引中列的序列号，从1开始,表明在联合查询中的顺序，我们可以根据这个推断出联合索引中索引的前后顺序（使用最左优化原则）	从1递增至联合索引的列数
Column_name	索引的列名	索引的列名
Collation(n.排序方式，校队)	指排序方式	A表示升序，B表示降序，NULL表示未排序。
Cardinality	基数的意思，表示索引中唯一值的数目的估计值，我们知道某个字段的重复值越少越适合建立索引，所以我们一般根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，就需要评估这个字段是否适合做索引	最小值为1，表示索引的列字段值都重复，最大为表中字段数
Sub_part	当索引是前缀索引的时候，sub_part表示前缀的字符数	非前缀为0，前缀索引为字符数
Packed	指示关键字如何被压缩。	如果没有被压缩，则为NULL
NUll	如果列含有null，则含有yes	null/yes
Index_type	表示索引类型，全文索引是Fulltext，Memory引擎对应Hash，其他大多数是Btree，Rtree没有见过	FULLTEXT，HASH，BTREE，RTREE
Comment	注释	…
Index_comment	注释	…

删除索引

删除前:

删除后：

单列索引

普通的索引，没有什么介绍

唯一索引

当索引的列是unique的时候，会生成唯一索引，唯一索引关于null有下列两种情况

SQLSERVER 下的唯一索引的列，允许null值，但最多允许有一个空值

MYSQL下的唯一索引的列，允许null值，并且允许多个空值

会建立两个索引，一个非聚簇索引，一个是唯一索引

可以插入两个空值（明人不说暗话，我喜欢MySQL）

前缀索引

一方面，它不会索引所有字段所有字符，会减小索引树的大小.
另外一方面，索引只是为了区别出值，对于某些列，可能前几位区别很大，我们就可以使用前缀索引。
一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

前缀索引实例的博文:

复合索引的最左前缀匹配原则：

对于复合索引，查询在一定条件才会使用该索引

我们插入进去的时候，数据的id都是乱序的，为什么这里最后select查询出来的结果都是进行了排序？

这是因为InnoDB索引底层实现的是B+tree，B+tree具有下列的特点：

所以上面的排序是为了使用B+tree的结构，B+tree为了范围搜索，将主键按照从小到大排序后，拆分成节点。后续还有新的节点进入的时候，和B-tree相同的操作，会进行分裂。

一般来说，聚簇索引的B+tree都是三层

为什么这里辅助索引叶子结点不直接存储数据呢？

MYISAM只有非聚簇索引，索引最终指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的优势在哪里？

由于行数据和聚簇索引的叶子结点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把也加载到了buffer中(缓存器),再次访问时，会在内存中完成访问，不必访问磁盘，这样主键和行数据是一起被载入内存的，找到叶子结点就可以立刻将行数据返回了，获得数据更快。
辅助索引的叶子结点，存储主键值，而不是数据的存放地址，好处是当行数据发生变化时，索引树的节点也需要分裂变化，或者是我们需要查找的数据，在上一次读写的缓存中没有，需要发送一次新的IO操作时，可以避免对辅助索引的维护工作，只要维护聚簇索引树就好了，另外一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间的大小。

Q:主键索引作为聚簇索引需要注意什么

当使用主键为聚簇索引时，主键最好不要使用UUID，因为UUID的值过于离散（可以查看UUID的产生过程），不适合排序，并且可能在两个已经排序好的结点中会出现新插入的节点，导致索引树调整复杂度变大。
建议使用int类型的自增，int类型自增主键数据量为4亿，满足一般开发要求，并且由于自增，主键本身就有序，因此开销很小，辅助索引中保存的主键值也会跟着变化，占用存储空间，也会影响到IO操作读取到的数据量。

查询语句中使用Like关键字
在查询语句中使用LIke关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会使用。如果“%”不是在第一位，索引就会使用
查询语句中使用多列索引
多列索引是在表的多个字段上创建的索引，满足最左前缀匹配原则，索引才会被使用
查询语句中使用OR关键字
查询语句只有Or关键字时候，如果OR前后的两个条件都是索引，这这次查询将会使用索引，否则Or前后有一个条件的列不是索引，那么查询中将不使用索引

5.关于Explain语句

作者不会，建议查找，这里列出是作为提醒

永远年轻，永远热泪盈眶