一文搞懂mysql索引以及优化

认识mysql索引

索引是什么?

1种有序的存储结构，按照单个或者多个列的值进行排序

索引目的是什么？

提升查找效率

索引的代价是什么？

占用空间，维护的代价:dml操作变慢。

索引分类

主键索引、唯一索引、普通索引、组合索引、以及全文索引；

类型	介绍
主键索引	非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息；
唯一索引	不可以出现相同的值，可以有NULL值；
普通索引	允许出现相同的索引内容；
组合索引	组合索引
全文索引	将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术

 创建

主键索引（Primary Key Index）:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
唯一索引（Unique Index）：

ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);
普通索引（Normal Index）：

ALTER TABLE table_name ADD INDEX index_name (column_name);
全文索引（Full-Text Index）：

ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);
需要注意的是，全文索引只能用于 InnoDB 或 MyISAM 存储引擎的表。

空间索引（Spatial Index）：
ALTER TABLE table_name ADD SPATIAL INDEX index_name (column_name);

6大约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key， foreign key， default, not null；

约束	介绍和特点
主键约束  primary key	唯一性：主键的值必须在表中是唯一的。 非空性：主键列的值不能为NULL。 唯一标识：主键用于唯一标识表中的每一行记录。 表中只能有一个主键。
自增键约束  auto_increment	自动增长：自增键是一种特殊的主键，它在插入新记录时自动递增。 唯一性：自增键的值在表中必须是唯一的。
唯一键约束  unique	唯一性：唯一键确保列的值在表中是唯一的。 允许NULL：唯一键可以允许NULL值，但NULL值在列中只能出现一次。
非空约束  not null	非空性：非空约束确保列的值不能为NULL。
默认值约束  default	默认值：默认值约束用于指定列的默认值。 当插入新记录时，如果未提供该列的值，则会使用默认值。
外键约束  foreign key	关联关系：外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作 用；而innodb完整支持外键；

 约束与索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个 数据结构既包含逻辑的概念也包含物理的存储方式；

索引原理

索引存储 

MySQL 中的索引存储在磁盘上，具体来说，索引数据存储在磁盘上的索引文件中。每个索引文件对应一个表的索引。索引文件由多个索引页组成，每个索引页大小通常为16KB。

innodb下由段、区、页组成；

段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由64个 连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区 中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区；

索引的存储结构 

B+树全称：多路平衡搜索树，减少磁盘访问次数；

用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为 16K；对页的访问是一次磁盘io，缓存中会缓存常访问的页；

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范 围查询； 每个索引对应着一个 B+ 树；

 B+树层高问题

B+树的一个节点对应一个数据页；B+树的层越高，那么要读取到内存的数据页越多，io次数越 多；

innodb一个节点16kB；

假设： key为10byte且指针大小6byte，假设一行记录的大小为1kB； 那么一个非叶子节点可存下16kB/16byte=1024个（key+point）；每个叶子节点可存储1024行数 据； 结论： 2层B+树叶子节点1024个，可容纳最大记录数为： 1024 * 16 = 16384； 3层B+树叶子节点1024 * 1024，可容纳最大记录数为：1024 * 1024 * 16 = 16777216； 4层B+数叶子节点1024 * 1024 * 1024，可容纳最大记录数为：1024 * 1024 * 1024 * 16 = 17179869184

简单理解 4层高度（4次io）可容纳17亿数据，3层可容纳1677 万数据。

每层增长相当于内存和磁盘速度差距的10倍不止

关于自增id 

超过类型最大值会报错； 建议使用类型 bigint 范围：这个值是 2^63 - 1，使用了 8 个字节存储。 假设采用 bigint 1秒插入1亿条数据，大概需要5849年才会用完索引；

myisam和innodb

文件组织结构

MyISAM 文件组织结构：

MyISAM 存储引擎使用三个文件来组织表数据：.frm、.MYD 和 .MYI。
.frm 文件是表定义文件，包含表的结构信息，如列名、类型等。
.MYD 文件是数据文件，存储实际的表数据。
.MYI 文件是索引文件，存储表的索引信息。

InnoDB 文件组织结构：

.frm 文件是InnoDB 表定义文件，包含表的结构信息，如列名、类型等。

.ibd：这是 InnoDB 表的数据文件的默认后缀。

innodb 体系结构 

Change buffer 缓存非唯一索引的数据变更（DML操作），Change buffer 中的数据将会异步 merge 到磁盘当中。比如insert很多数据，会先缓存之后落盘。因为插入数据是顺序io操作，不是随机io.速度极快。

 MySQL缓存策略，Buffer pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 Buffer pool 只缓存比较热的数据；buffer pool从中间开始往头写入，防止最新数据被覆盖。(其实这是误解，当我们知道buffer pool是循环缓冲区，其实从哪开始写入都一样。)按照顺序进行的，从 Buffer Pool 中的一个位置开始，逐渐向后移动。

 

各存储引擎区别 

 聚集索引

innodb下按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分；

主键索引 

MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键 索引的原理图：

辅助索引

InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。如图：

 

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯 一的，而辅助索引的key可以重复，如果给其它字段创建辅助索引，结构图如下

 

 自适应哈希索引

 自适应哈希索引（Adaptive Hash Index）是一种在数据库管理系统中使用的索引结构，旨在提高查询性能和访问效率。它是 InnoDB 存储引擎中的一项功能。

自适应哈希索引使用哈希表数据结构来存储索引信息，而不是传统的 B+ 树结构。它适用于那些经常被查询的小型热点数据集，其中某些数据页或索引节点经常被访问，因此哈希索引能够更快地定位到这些数据。（从磁盘中读取到内存,在内存中构建hash热点数据就不用再读磁盘）

#查看当前数据库实例的配置参数：
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
#打开自适应哈希索引
SET GLOBAL innodb_adaptive_hash_index = ON;
#关闭自适应哈希索引
SET GLOBAL innodb_adaptive_hash_index = OFF;

 索引使用注意点

最左匹配原则

 对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引 树；较少磁盘 io；

索引原则 

查询频次较高且数据量大的表建立索引；

索引选择使用频次较高，过滤效果好的列或者组合；

使用短索引；节点包含的信息多，较少磁盘io操作；比如：smallint，tinyint； 对于很长的动态字符串，考虑使用前缀索引；

索引失效 

select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；

索引字段参与运算，则索引失效；

LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like '%Mark';

在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；

组合索引中，没使用第一列索引，索引失效；

in + or 索引失效；单独的in 是不会失效的；not in 肯定失效的；

优化器成本分析

mysql 优化器主要针对 IO 和 CPU 会计算语句的成本；可能不会按照分析的原理来执行语句；

EXPLAIN 

用来查看SQL语句的具体执行过程。 原理：模拟优化器执行 SQL 查询语句，从而知道 mysql 是如何处理 sql 语句的。

 explain结果字段分析

select_type字段:

                simple：表示不需要union操作或者不包含子查询的简单select语句。有连接查询时，外层的查询 为simple且只有一个。

                 primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即 为primary且只有一个。

                union：union连接的两个select查询，除了第一个表外，第二个以后的表的select_type都是 union。

                 union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id 字段为null。

table字段:

                显示查询的表名；

                如果不涉及对数据库操作，这里显示null；

                如果显示为尖括号就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自于这个查询 产生的；

                如果是尖括号括起来也是一个临时表，表示这个结果来自于union查询的id为M，N 的结果集；

type字段:

                const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type就是 const。

                ref：常见于辅助索引的等值查找，或者多列主键、唯一索引中，使用第一个列之外的列作为等值 查找会出现；返回数据不唯一的等值查找也会出现。

                range：索引范围扫描，常见于使用、is null、between、in、like等运算符的查询中。

                index：索引全表扫描，把索引从头到尾扫一遍；常见于使用索引列就可以处理不需要读取数据文 件的查询，可以使用索引排序或者分组的查询。

                all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。

ref字段:

                如果使用常数等值查询，这里显示const；

                如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；

Extra字段:

                using filesort：排序时无法用到索引，常见于order by和group by语句中。

                using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

开启慢日志

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查 询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？

#打开 MySQL 的配置文件 启用慢查询日志并指定日志文件的路径
slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log
#设置慢查询阈值 10s
long_query_time = 10