MySQL索引_mysql 枚举类型需要索引吗

一.什么是索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

索引是为了加速对表中数据行检索而创建的一种分散的数据结构。

二.索引设计原则

重复率小的列建立索引
唯一列建唯一索引
频繁查询的列建立索引
枚举列不宜建索引

##查看索引
show index from {table_name}

##新增索引
alter table {table_name} add unique index {index_name}({column_name}); # 唯一索引
alter table {table_name} add index {index_name}({column_name}); # 普通索引
create index {index_name} on {table_name}({column_name}); # 普通索引

##删除索引
drop index {index_name} on {table_name};
alter table {table_name} drop index {index_name}

三.索引类别

主键索引：以表的主键创建的索引

聚簇索引：若表存在主键，则以主键生成的索引，若不存在主键，则会选择表中第一个不为空的唯一索引创建聚簇索引，若不存在唯一不为空的列，则InnoDB会在内部生成一个名为GEN_CLUST_INDEX隐式聚簇索引。该索引是基于一个名为DB_ROW_ID的隐藏字段，通常称之隐式主键。

非聚簇索引：将索引和数据分开存储，索引存放的是指向该数据的指针。

覆盖索引(covering index)，也称为索引覆盖，既在InnoDB中，只需要从辅助键索引中就可以查询到最终想要的数据结果，而不需要再从聚簇索引中二次查询，即不需要回表查询

四.索引实现原理

4.1索引结构

BTree索引和B+Tree索引：

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低，大量数据情况下，层级较深，检索速度慢；
红黑树左旋右旋可以解决线性问题，但是无法解决层级较深问题；

B-Tree（多路平衡查找树）

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

平衡二叉树：
二叉查找树的定义： 左子树的键值＜根的键值，右子树的键值＞根的键值。
平衡二叉树的定义：先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。

B+ 树：所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找， 最后得到要查找的数据。

哈希索引：只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

为什么使用B+Tree

相对于二叉树，层级更少，搜索效率高
对于B-Tree，无论是叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值减少，指针跟照减少，树的高度会增加，导致性能降低；
Hash索引，不支持范围查找；

4.2索引分类

主键索引：针对表中的主键创建的索引；
唯一索引：避免同一个表中某数据列重复；
常规索引：普通索引，快速定位特定数据；
全文索引：针对较大的数据，5.7之后innodb支持，只支持英文，MySQL全文检索时，默认检索长度为4，即关键词的长度必须大于5才能被捕获，查找的是文本中的关键词，而不是比较索引中的值；

根据存储形式分类

聚集索引的优点：它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据，默认主键是聚集索引；

辅助索引：InnoDB 存储引擎会遍历辅助索引并通过叶子节点的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录；

回表：回表查询，先根据辅助索引定位主键值，再根据主键索引定位行记录，它的性能较扫一遍索引树更低；

覆盖索引 ：nnoDB 存储引擎支持覆盖索引(covering index, 或称索引覆盖），即从辅助索引中就可以得到查询的记录， 而不需要查询聚集索引中的记录（所查的列都建立在组合索引上）。

索引下推： MySQL 数据库会在取出索引的同时，判断是否可以进行WHERE 条件的过滤，也就是将WHERE 的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL 层对记录的索取(fetch), 从而提高数据库的整体性能。是在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

五.索引性能分析

5.1开启慢查询日志

5.2 explain执行计划
id： 语句的执行顺序，id值相同语句从上到下，不同的话id越大先执行；

select_type： 查询类型

type： const （主键，唯一），ref（普通索引）
key：实际使用到的索引

possible_keys : 可能用到的索引

rows：扫描行数

filtered：数据过滤率

六.索引失效

6.1 or的使用
or的两边条件值都需要有索引，否则索引会失效

6.2 is null 和 is not null
扫描的数据过多会全表扫描，放弃走索引；

where 中使用 != 或 <> 或 or 或表达式或函数（左侧）；

like 语句 % 开头；

字符串未加 ’ ’

索引字段区分度过低，如性别；

未匹配最左前缀；