数据库-索引

索引概述

索引(Index)是帮助MySQL高效获取数据的数据结构（有序）。（B+ Tree）

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
 优点：提高数据检索效率，降低数据排序成本
 缺点：维护索引表，占用空间，更新表同时更新索引，效率降低 。

也就是空间换时间的思想。由于日常业务中，数据的增删改所占的频率是远小于查询的，因此优化查询节省的时间要远大于增删改时对索引表的维护所花费的时间。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的存储结构，主要包含以下几种:

• B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引（重点） InnoDB,MyISAM,Memory都支持
• Hash索引：底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询.只有Memory支持
• R-Tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 只有MyISAM支持
• Full-Tree（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES。 InnoDB 5.6版本后支持，MyISAM支持

如果没有特别指明，索引指的就是B+树索引。

由于二叉树只有两个分支，在大数据量的情况下了，层级较深，检索速度慢，因此，B-Tree应运而生。

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

特点：

• 一个节点有多个数据，多个指针

B+Tree

特点：

• 1.所有的元素都会出现在叶子节点
• 2.叶子节点数据构成一个链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原有B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+tree,提高区间访问的性能。也就是双向循环链表

索引分类

• 主键索引 ：只能有一个
• 唯一索引：可以有多个
• 常规索引：可以有多个
• 全文索引：可以有多个

在InnoDB存储引擎中，根据索引的存储形式，又可以分为一下两种:

• 聚集索引  ：必须有，而且只能有一个
• 二级索引  ：可以有多个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果上述都没有，则InnoDB会自动生成一个rowid作为隐藏聚集索引。

重点：如果查询的索引为二级索引，那么会发生回表查询

索引语法 

创建索引

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

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

explain执行计划

explain或者DESE命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

语法：在SELECT前面加上EXPLAIN

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

表中各字段的含义：

索引设计原则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳过某一列，那么索引将部分失效（后面的字段索引失效）。

范围查询

联合索引中，出现范围查询(>,<) ,范围查询右侧的列索引失效。

select * from tb_user where profession = '软件工程' and age>30 and status='0';

此时，status索引失效。

结论：范围查询1.使用（>=,<=)2.若要使用> <，放在最右边。

索引列运算

不要在索引列上进行运算操作，否则，索引失效。

select * from tb_user where substring(phone,10,2)='15';

在phone列上进行substring操作，索引失效。

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

模糊查询

如果仅仅是尾部模糊匹配 like '软件%'，索引不会失效；如果是头部模糊匹配 like '%软件'，索引失效。

or连接的条件

用or分割开的条件A，B，若想要索引生效，必须A，B的列均包含索引。

数据分布影响

如果MySQL评估使用索引比全表扫描更慢，则不使用索引。