MySQL索引

索引概述

索引 （index）是帮助 MySQL 高效获取数据的 （有序）数据结构。
数据库除了存储数据外，还维护着实现了高级查找算法的数据结构，这些数据结构以某种方式指向数据，这种数据结构就是索引

索引的优缺点

• 优点：提高数据查询的效率，降低数据库的IO成本；提高数据库排序的效率，降低CPU的消耗
• 缺点：索引列需要占用磁盘空间；降低更新表的速度，对表数据增删改时，效率降低

索引的结构

平时说的索引一般是指B+树索引

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎 都支持B+树索引
Hash索引	底层数据结构用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询，只有Memory引擎支持
RTree索引	空间索引是MyISAM引擎的一个特殊索引，主要用于地理空间数据类型，使用很少
全文索引	是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术

B-Tree

• 如果最大度数为5，结点内元素最多4个，指针最多5个

• 当结点元素个数达到最大度数时，中间元素向上分裂
  

B+Tree

• 是 B-Tree 的变种

• 它满足：1. 所有元素都会出现在叶子结点，叶子结点形成了一个单向链表
  2. 叶子结点存放数据，非叶子结点起到索引的作用

  

MySQL中的B+树索引

• MySQL对经典的 B+Tree 进行了优化。在原树的基础上，增加了一个指向相邻叶子结点的链表指针，提高了区间访问的性能

  

MySQL中的Hash索引

• 哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后储存在 hash表中
• 查询效率高，通常只需要一次检索就可以，效率高于B+树索引
• Hash索引 只能用于对等比较（=、in），不支持范围查询（between，>，<，…）、无法完成排序操作

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

• 二叉树在顺序插入时会退化成链表
• 红黑树是一个二叉树，数据量很大时，树的层级很高，检索速度比较慢
• 对于B树来说，叶子结点和非叶子结点都要存放数据，也要存放key和指针，而树的一个结点对应一个磁盘块，一页的大小是固定16K的，他可以存放的键值和指针变少了，那么树的高度就增加，检索速度就会变慢。

索引的分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中某数据列的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	查找文本中的关键字	可以有多个	fulltext

聚集索引和非聚集索引

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

分类	含义	特点
聚集索引	索引结构和数据一起存放，索引结构叶子结点保存了整条行数据	InnoDB 中的主键索引是聚集索引，必须有，且只有一个
非聚集索引	索引结构和数据分开存放，索引结构叶子结点关联对应主键	二级索引是非聚集索引，可以有多个

聚集索引选取规则：

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

主键索引和二级索引

分类	含义	特点
主键索引	主键列使用的就是主键索引	主键索引的叶子节点存储的是主键和整行的数据
二级索引	是辅助索引，用于定位主键的位置	二级索引的叶子节点存储的是主键

回表查询

先从二级索引查到对应的主键，在从聚集索引查到整条行数据

联合索引与单列索引

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

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

• 在业务场景中，如果存在多个查询条件，考虑建立索引时，推荐使用联合索引，不使用单列索引

索引的语法

数据量很大，且经常被查询的数据表可以设置索引

索引只添加在经常被作为检索条件的字段上面

不要在大字段上创建索引，字符串过长不适合添加索引

#创建索引
create table t_message (
 id int unsigned primary key,
 content varchar(200) not null,
 type ENUM("公告","通报","个人通知") not null,
 create_time timestamp not null,
 index idx_type (type)
);

#删除索引
drop index idx_type on t_message;

#添加索引
create [unique|fulltext] index idx_type on t_message(type);
alter table t_message add index idx_type(type);

#查看索引信息
show index from t_message;

SQL性能分析

做SQL优化首先要定位出对那些SQL进行优化

• 查看执行频次

  查看当前数据库的 insert、update、select、delete 访问频次

show global status like 'Com_______';

• 慢查询日志

#查看是否开启慢查询
show variables like 'slow_query_log';

慢查询日志记录了所有执行时间超过指定参数（long_query_time 默认10s）的所有SQL语句

MySQL慢查询日志的开启，在MySQL配置文件 my.cnf 中配置

#开启MySQL慢查询
slow_query_log=1;

#设置慢查询的时间为10s
long_query_time=10;

• profile详情

  在做SQL优化时帮助我们了解时间都消耗到哪了

#确保开关打开
select @@have_profiling;
show @@profiling;
set profiling=1;
#查看每一条SQL的耗时
show profiles;
#按query_id查看单条SQL的耗时
show profile for query 68;

• explain执行计划

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

#直接在select语句之前加上关键字explain/desc
explain select * from t_emp where empno=7499;

• id：select 查询的序列号，id越高执行的优先级越高，id相同，执行顺序从上到下
• select_type：表示select查询的类型
• table：表名
• partitions：
• type：表示连接类型性能由好到差为 null、system、const、eq_ref、ref、range、index、all
• possible_keys：可能用到的索引
• key：实际用到的索引
• key_len：索引长度
• ref：
• rows：
• filtered：
• Extra：额外的信息

索引的规则

• 最左前缀法

  如果索引了多列（联合索引），要遵守最左前缀法则。必须包含最左边索引（查询时顺序可以不同），并且不能跳过索引中的列，如果跳过，右边的索引失效。

create index idx_ejm on t_emp(ename,job,mgr);

#正确的
explain select * from t_emp 
where ename='SMITH' and job='CLERK' and mgr=7902;

#错误的
#不包含最左边索引
explain select * from t_emp 
where  job='CLERK' and mgr=7902;
#跳过了中间索引，后面索引失效
explain select * from t_emp 
where ename='SMITH' and mgr=7902;

• 范围查询

  联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效，业务允许的情况下，尽量使用 >=；like，BETWEEN 不会出现索引失效

#错误的，前面两个索引有效
explain select * from t_emp 
where ename='SMITH' and job>'CLERK' and mgr=7902;
#正确的
explain select * from t_emp 
where ename='SMITH' and job>='CLERK' and mgr=7902;

• 索引失效的情况

  • 在索引列上进行运算（或函数运算）
  • 字符串不加引号
  • like 模糊匹配中，在后面加 %（‘软件%’）索引不会失效，只要在前面加 %（‘%工程’ ‘%工%’）索引就会失效
  • or 连接的条件必须都存在索引
  • 数据分布的影响，如果MySQL评估使用索引比全表扫描更慢，则不使用索引

• SQL提示

create index idx_temp_ename on t_emp(ename);

#建议MySQL使用某个索引
explain select * from t_emp
use index(idx_temp_ename)
where ename='SMITH';
#忽略某个索引
explain select * from t_emp
ignore index(idx_ejm)
where ename='SMITH';
#强制使用某个索引
explain select * from t_emp 
force index(idx_ejm)
where ename='SMITH';

• 覆盖索引（数据查询方式）

  索引中包含所有 select 选中的 字段的值，不需要进行回表查询

• 前缀索引

    对长字符串一部分前缀建立索引
  

#以字符串前n位为索引
create index idx_XXX on table_name(column(n));
#可以计算长度为n的索引，不重复数据占比
select count(distinct email)/count(*) from t_user;

索引设计原则

1. 针对数据量较大（超过一百万），且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4. 如果是字符串类型的索引，字符串长度越长，可以针对字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引。联合索引很多时候可以覆盖索引，避免回表查询
6. 要控制索引的数量，索引越多，维护索引结构的代价越大，会影响
7. 如果索引列不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以选择合适的索引用于查询