Mysql索引

结构

二叉查找树

        左子树小于根节点。

        缺点：当顺序插入时查询效率低，当数据量大的时候层级较深，查询效率低

平衡二叉树

        在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

        缺点：当数据量大的时候层级较深，查询效率低

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

        度数为n的B-Tree数有n - 1个key以及n个指针，每个节点不仅包含数据的key值，还有data值。

        缺点：当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

B+Tree

        B+Tree是在B-Tree基础上的一种优化 。B+Tree的非叶子节点只存储键值信息，所有叶子节点之间都有一个链指针，数据记录都存放在叶子节点中。

        优点：层级少，效率高。

hash

        哈希索引是采用一定的哈希算法，将键值换成新的哈希值，映射到对应的槽位上，然后储存在哈希表中，查询效率高。

        缺点：只能等值查询，无法进行排序操作。

分类

主键索引：

        针对表中主键创建的索引，默认自动创建，只能有一个。

        关键字：PRIMARY

唯一索引：

        避免同一个表中某数据列中值重复，可以有多个。

        关键字：UNIQUE

常规索引：

        快速定位特定数据，可以有多个。

        关键字：无

全文索引：

        全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个。

        关键字：FULLTEXT

聚集索引（innoDB）：

        将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，有且只有一个。

二级索引（innoDB）：

        将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个。

因为二级索引只存了索引和主键所以当使用二进索引查找所有数据时会发生回表查询。

语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

关联一个字段的叫单列索引，关联两个字段的叫联合索引

查看索引

SHOW INDEX FROM table_name ; 

 删除索引

DROP INDEX index_name ON table_name ;

 性能分析

执行频率

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

explain(查看索引信息)

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

Explain 执行计划中各个字段的含义:

使用规则 

最左前缀法则（组合索引）

        注：与输入顺序无关，按创建索引时的顺序判断

索引失效的原因 

        未遵守最左前缀法则（部分失效）

        使用了不带等号的范围查询（<, >）

        使用or时有字段未建立索引（单列索引，有组合索引不算）

        在查询时进行了运算或者字符串不加引号（进行了类型转换操作）

        进行了模糊查询（只要前面带了%才会失效）

        mysql评估不使用索引反而更快时

SQl提示

  建议使用idx索引

select * from user use index(idx) where id = 1;

  不使用idx索引

select * from user ignore index(idx) where id = 1;

   强制使用force索引

select * from user force index(idx) where id = 1;

覆盖索引

 前缀索引

语法

create index idx_xxxx on table_name(column(n)) ;

前缀长度

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;