索引（数据库重点！！！）

1.介绍

• 索引（index）是帮助MySQL高效获取数据的数据结构。

2.索引结构

• B+Tree索引：最常见的索引类型
• Hash索引：哈希表实现
• R-tree（空间索引）
• Full-text（全文索引）

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

以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key,5个指针）

B+Tree

以一颗最大度数为4的b+tree为例

Hash

哈希索引采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上，然后存储在hash表中

思考题

为什么InnoDB存储引擎选择使用B+tree索引结构

• 相对于二叉树，层级更少，搜索效率高
• 对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，导致一页存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加数的高度，导致性能降低
• 相对Hash索引，B+tree支持范围匹配及排序操作

3.索引分类

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

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引

思考题

4.索引语法

• 创建索引
• 查看索引
• 删除索引

-- 查看索引
show index from tb_user;
-- name字段为姓名字段,该字段的值可能会重复,为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值,是非空,且唯一的
create unique index idx_user_phone on tb_user(phone);
-- 为profession、age、status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);
-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
-- 删除索引
drop index idx_user_email on tb_user;

 SQL性能分析

• SQL执行频率

MySQL客户端连接成功后,通过show[session|global]status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的插入、查询、更新的访问频次：

show global status like 'com_______'; //7个下划线

• 慢查询日志

记录了所有执行时间超过指定参数的所有SQL语句的日志

MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息

• profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;

• explain执行计划

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

语法：直接在select语句之前加上关键字explain/desc

EXPLAIN执行计划各字段含义：

• id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同,执行顺序从上到下；id不同,值越大，越先执行）
• select_type：表示SELECT的类型,常见的取值SIMPLE（简单表）、PRIMARY（主查询，即外层的查询）、UNION、SUBQUERY
• type：表示连接类型,性能由好到差的类型为NULL、system、const、eq_ref、ref、range、index、all
• possible_key：显示可能应用在这张表上的索引,一个或多个
• Key：实际使用的索引,如果为null，则没有索引
• Key_len：表示索引中使用的字节数
• rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值
• filtered：表示返回结果的行数占需读取行数的百分比，值越大越好

使用规则

• 验证索引效率

索引失效原则 

• 最左前缀法则

如果索引了多列，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效

• 范围查询

联合索引中，出现范围查询，范围查询右侧的列索引失效

• 索引列运算

不要在索引列上进行运算操作,索引将失效

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

•  字符串不加引号

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

• 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，则会失效

select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';

• or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到

• 数据分布影响

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

• SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

use index：建议用哪个索引

ignore index：忽略用哪个索引

force index：强制用哪个索引

explain select * from tb_user use/ignore/force index(idx_user_pro) where profession = '软件工程';

•  覆盖索引

• 前缀索引

• 单列索引与联合索引

单列索引：索引只包含单个列

联合索引：索引包含多个列

在业务场景中，如果存在多个查询条件,考虑针对于查询字段建立索引时，建议建立联合索引

联合索引情况：

• 索引设计原则

1.数据量较大,且查询频繁的表建立索引

2.针对于常作为查询条件、排序、分组操作的字段建立索引

3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4.如果是字符串类型的字段,字段的长度较长，可以针对于字段的特点，建立前缀索引

5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6.要控制索引的数量

7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它