MySQL索引与数据结构详解-优快云博客

六.索引

1.数据结构

1.B-树

B-树,这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的搜索树它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。

B树有以下特点：

所有键值分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
在关键字全集内做一次查找,性能逼近二分查找

2.B+树

B+树和B-树之间的区别是，B+树所有的关键字都存储在叶子节点中，所有的叶子节点之间增加了一个双向指针。这样做可以更加方便的范围查找。

3.选型缘由

问题一：为什么在b-树或b+树中选择？

mysql数据模型更适合用这类数据结构，一条数据中通常包含【id】+【其他列数据】，我们可以很轻松的根据id组织一颗B+树。
我们知道innodb使用【页】（这是inndb管理数据的最小单位）保存数据，一页（16k），b+树中的每个节点都是一页数据。

问题二：为什么选择B+树？

相同的空间，不存放【整行数据】就能存【更多的id】，b+树能使每个节点能检索的【范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问】。
Mysql是一种关系型数据库，【区间访问】是很常见的一种情况，B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况。

4.发现索引

我们发现当使用id去查询数据时，效率很高，因为使用id可以利用B+树的特性，加速查询。相同的数据，使用id列查询和其他列查询，时间消耗差距很多。

那么我们是否可以创建一个和B+树相同的结构，但是使用别的列查询，答案是可以的但是如果我们创建这样的结构我们的数据库体积就会膨胀很多倍，十分影响性能。因此我们可以对这样的结构进行优化，优化之后我们只在叶子节点里id，当我们查到id之后再进行回表(回到原来的结构中根据id进行查询)，查询整条记录，这就是我们日常工作中经常创建的【索引】。

2.索引的分类和创建

1.聚簇索引和非聚簇索引

主键和数据共存的索引叫聚簇索引，其他的叫做非聚簇索引，或辅助索引，或二级索引，例如我们上面使用姓名列+主键建立的索引。InnoDB使用的是聚簇索引，MyIsam使用的是非聚簇索引。

小问题：主键为什么建议使用自增id?

主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

对于【二级索引】而言，根据其不同的特性，我们又可以分为普通索引、唯一索引、复合索引等。

2.普通索引（常规索引 normal）

就是普通的索引，没有任何要求，理论上任何列都可以当作普通索引，创建方式如下：

create index idx_user_name on user(user_name);

删除索引：

drop index idx_user_name on user;

其中idx_user_name为索引名，user为数据库名，user_name为当作索引的列。(创建索引是一个很费时间的操作)

当我们创建一个索引时，如果这个列的数据很长，我们可以截取这个列的前几个字符当作索引，语法如下：

create index idx_email on user(email(5)); --使用email列的前五位作为索引

使用修改表的方式创建索引：

alter table user add index idx_user_name (user_name);

建表的同时创建索引：

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    index [indexName] (fieldName(length))
)

3.唯一索引（UNIQUE ）

对列有要求，要求列的值不能重复。

建表时创建唯一索引:

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    unique index unique_index_tname (tname)
)

独立的sql语句创建索引，邮箱，用户名就应该创建唯一索引，姓名就应该是普通索引：

create unique index idx_email on user(email);

通过alter语句添加索引：

ALTER table tbl_name ADD UNIQUE [ux_indexName] (username(length))

唯一索引和主键的区别：

唯一索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为非空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

唯一约束和唯一索引的区别：

唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null。
创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据唯一。
创建一个唯一索引，这个索引就是独立的索引，可以单独删除。
如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。

4.多个二级索引的组合使用

MySQL在执行查询语句时一般只会使用一个索引，除非是使用or连接了两个索引列会产生索引合并。（如果or连接了一个无索引列时索引会直接失效）。

5.复合索引（联合索引）重要

当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

比如：我们经常按照A列、B列、C列进行查询时，通常的做法是建立一个由三个列共同组成的【复合索引】而不是对每一个列建立【普通索引】。

创建联合索引的方式如下：（复合索引会优先按照第一列排序，第一列相同的情况下会按照第二列排序）

alert table test add idx_a1_a2_a3 table (a1,a2,a3) --使用alert创建
create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7)); --直接创建

最左前缀原则(重点)：

（1）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）=和in可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）。

(因此在编写sql语句的时候尽量把范围查询的条件往后放)

小问题：什么联合索引的性能会比索引的组合使用效率高？

这是因为最左前缀原则引起的，假如我们查询两个

6.全文索引（FULLTEXT）

做全文检索（不如百度的搜索功能）使用的索引，但是这种场景，我们有更好的替代品，如：ElacticSearch，所以实际使用不多，只当了解。

使用 like + % 实现的模糊匹配有点类似全文索引。但是对于大量的文本数据检索，全文索引比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在【精度问题】。同时普通索引在使用like时如果%放在首位，索引会失效。

全文索引的版本支持

MySQL 5.6 以前的版本，只有 MyIsam 存储引擎支持全文索引。
MySQL 5.6 及以后的版本，MyIsam 和 InnoDB 存储引擎均支持全文索引。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

使用全文索引的注意

使用全文索引前，搞清楚版本支持情况。
全文索引比 like + % 快 N 倍，但是可能存在精度问题。
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

创建全文索引的方式：

(1).创建表时创建全文索引:

create table user (    
    ..   
    FULLTEXT KEY fulltext_text(text)  
)

(2).在已存在的表上创建全文索引:

create fulltext index fulltext_text  on user(text);

(3).通过 SQL 语句 ALTER TABLE 创建全文索引:

alter table user add fulltext index fulltext_text(text);

(4).直接使用 DROP INDEX 删除全文索引:

drop index fulltext index on user;

(5).全文检索的语法:

select * from user where match(text) against('text');

7.hash索引

hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引，memory的数据是放在内存中的，一旦服务关闭，表中的数据就会丢失。

合理的使用memory引擎可以极大的提升性能，针对memory引擎的特点重启丢失），我们最好在其中存储一些公共的、常用的、不经常发生改变的数据，比如一些字典数据、配置数据等。同时，这些数据最好持久化在一些其他的地方，比如配置文件、其他的表，在程序启动的时候，主动的进行加载，我们可以使用如下sql，将一张表的数据加载到内存中：

insert into hash_user select * from user where id < 2000000;

当我们执行过程中可能会出现错误，提醒我们hash_user已经满了，我们只需要调节下边两个参数，修改配置文件重启MySQL即可：

tmp_table_size = 4096M
max_heap_table_size = 4096M

关于hash索引需要了解的几点：

hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来构建热点数据缓存，存取效率很好。
即使是相近的key，hash的取值也完全没有规律，索引hash索引不支持范围查询。
hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询（首先查询行的位置，然后找到具体的数据）。
hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。
只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

8.空间索引（SPATIAL）

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用的一种索引，其他场景用的很少，所以不需要深入学习。

3.explain的用法

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。用法如下图：

执行explain会产生以下11列内容，如下：

列号	列	说明
1	id	select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2	select_type	查询类型
3	table	正在访问哪个表
4	partitions	匹配的分区
5	type	访问的类型
6	possible_keys	显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7	key	实际使用到的索引，如果为NULL，则没有使用索引
8	key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9	ref	显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10	rows	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数 filtered //查询的表行占表的百分比
11	filtered	查询的表行占表的百分比
12	Extra	包含不适合在其它列中显示但十分重要的额外信息

1.id字段

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

（1） id相同

id如果相同，可以认为是一组，执行顺序从上至下，如下查询语句：

（2） id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行例子。

（3）id部分相同部分不同

id如果相同，可以认为是一组，从上往下顺序执行在所有组中，id值越大，优先级越高，越先执行例子：

2.select_type字段

（1）SIMPLE

简单查询，不包含子查询或Union查询的sql语句。

（2）PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。

（3） SUBQUERY

在select或where中包含子查询。

（4）UNION

若第二个select出现在union之后，则被标记为UNION。

（6）UNION RESULT

从UNION表获取结果的合并操作。

3.type字段

最好到最差备注：掌握以下10种常见的即可NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

NULL MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
system 表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。
const 表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行。
ref_or_null 类似ref，但是可以搜索值为NULL的行。
index_merge 表示使用了索引合并的优化方法
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现between、<>、in等的查询。
index（全索引扫描）Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
ALL Full Table Scan，将遍历全表以找到匹配行.

4.table字段

表示数据来自哪张表

5.possible_keys字段

显示可能应用在这张表中的索引，一个或多个查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用

6.key字段

实际使用到的索引，如果为NULL，则没有使用索引查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

7.key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度在不损失精确度的情况下，长度越短越好key_len显示的值为索引字段最大的可能长度，并非实际使用长度即key_len是根据定义算而得，不是通过表内检索出的

8.ref字段

哪些列或常量被用于查找索引列上的值

9.rows字段

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

10.partitions字段

匹配的分区

11.filtered字段

它指返回结果的行占需要读到的行(rows列的值)的百分比

4.使用索引的问题

1.哪些情况下适合建索引

频繁作为where条件语句查询的字段
关联字段需要建立索引
分组，排序字段可以建立索引
统计字段可以建立索引，例如count()，max()等

2.哪些情况下不适合建索引

频繁更新的字段不适合建立索引
where条件中用不到的字段不适合建立索引
表数据可以确定比较少的不需要建索引
数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值
参与列计算的列不适合建索引，索引会失效

3.能用复合索引的要使用复合索引

4.null值也是可以走索引的，他被处理成最小值放在b+树的最左侧

5.使用短索引

对字符串的列创建索引，如果可能，应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

6.排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列创建复合索引。

7.MySQL索引失效的几种情况

如果条件中有or，即使其中有条件带索引也不会使用走索引，除非全部条件都有索引
复合索引不满足最左原则就不能使用全部索引
like查询以%开头
存在列计算
如果mysql估计使用全表扫描要比使用索引快，则不使用索引，比如结果的量很大
存在类型转化

关于类型转化导致索引失效的问题也不是所有的类型转化都会导致索引失效，比如以下例子中，=用int类型的age与字符串'18'比较，以及把日期类型与字符串'2008-05-31 17:20:54'比较，索引就没有失效，但是当用字符串gander与数字作比较时类型就失效了。

-- 索引不失效
explain select * from student where age = '18'  
explain select * from ydl_user where login_date = '2008-05-31 17:20:54'
-- 索引失效 本来是字符串，你使用数字和他比较
explain select * from student where gander = 1