【mysql】索引总结

不倒翁玩偶

已于 2023-09-11 21:39:25 修改

阅读量440

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 java

于 2023-04-10 03:36:52 首次发布

本文链接：https://blog.csdn.net/sh1307212321/article/details/130051322

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

检查索引关键字：

explain

explain select * from emp where asl > 1000\G

官网文档：

https://dev.mysql.com/doc/refman/5.7/en/
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
- PRI 主键约束UNI 唯一约束MUL 可以重复
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
- Using index，Using index condition，Using where
- 其中Using index 表示使用了覆盖索引

索引是什么？

索引是帮助mysql高效的获取数据
索引存储在文件系统中
索引的文件存储形式和存储引擎有关
索引文件的结构：
- hash
- 二叉树
- B树
- B+树
索引的目的：
- 提高访问效率
- 加快sql语句的查询

索引的优势和劣势

优势
- 类似于书籍的目录索引，提高数据检索的效率，降低数据库的io成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势
- 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的
- 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT，UPDATE,DELETE。因为更新表时，MYSQL不仅要保存数据，还需保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引的分类:

功能划分：

主键索引：
- 创建了主键就自动就是主键索引
唯一索引：
- 设置了唯一约束，就自动是唯一索引
普通索引：
- 辅助索引
- 二级索引
全文索引：
- 文档管理系统里面放得都是文章，把文章放到mysql数据库里面了，想检索一下所有文章里面包含java这个关键字的文章怎么做？
  - 主要用于全文检索
  - MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
  - MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
  - 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

列数划分：

组合索引：
- 一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
单列索引：
- 一个索引只包含一个列，一个表可以有多个单例索引。

物理分类：

聚蔟索引
非聚蔟索引

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YNWqrEaN-1681068448478)(C:\Users\86185\AppData\Roaming\Typora\typora-user-images\image-20230405221237636.png)]$

索引的创建：

索引名称 index_name 是可以省略的，省略后，索引的名称和索引列名相同。

-- 创建普通索引 
CREATE INDEX index_name ON table_name(col_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

-- 创建联合全文索引列
create fulltext index keyname on xxtable(colume1,colume2);
alter table xxtable add fulltext index keyname (colume1,colume2);
-- 建表的时候
FULLTEXT KEY keyname(colume1,colume2)

删除索引

-- 直接删除索引
DROP INDEX index_name ON table_name;

-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

其他命令

-- 查看表结构
desc table_name;

-- 查看生成表的SQL
show create table table_name;

-- 查看索引信息（包括索引结构等）
show index from  table_name;

-- 查看SQL执行时间（精确到小数点后8位）
set profiling = 1;
SQL...
show profiles;

如何设计一套性能优良的索引系统？

mysql的数据在磁盘
索引也在磁盘
- （加载的过程）当你启动mysql服务之后，会优先把一部分数据加载到内存
- 所以看起来好像从内存中取得一样，实际上是要持久化磁盘的
- 为什么持久化磁盘，因为服务器或电脑突然断电了，内存数据就没了难道要从新创建索引吗？效率非常之地的，所以需要持久化到磁盘
  - 加载：就涉及到了IO的问题，读写问题（固态硬盘比机械硬盘效率高）
    - IO可以是硬件问题，把机械换成固态（成本高）
    - 减少IO次数（一次与十次），减少IO量（一兆或十兆）时间肯定是不一样的

磁盘预读：

局部性原理：
- 发现程序和数据的访问都有聚集成群的倾向，在一个时间段内，仅使用其中一小部分（称空间局部性），或者最近访问过的程序代码和数据，很快又被访问的可能性很大（称为时间局部性）
如果你想只读一个a字母，那么是把a的这一块数据都取了出来，取数据的大小是根据操作系统相关的，一般是4k，8k，就是页的整数倍（page）
主存和磁盘以页为单位交换数据

现在我有个256兆内存的机器，我有1T的数据文件，如何能利用256兆的空间把这1T的数据排序？

您可以使用外部排序的方法，将原始文件划分为多个小文件，每个小文件大小不超过256兆。然后，使用快速排序等算法对每个小文件进行排序，最后再使用归并排序将这些已排序的小文件合并成一个大文件，即可完成1T的数据文件的排序。

分快读取，分而治之
磁盘预读：
- 内存和磁盘进行交互的时候，有一个基本的逻辑单位叫做页
- 一般情况下页的大小跟操作系统是相关的是4k或8k
- 我们在进行数据读取的时候，读取的是页的整数倍
- innoDB读取16kb
- ```
show variables like'innodb_page_size';
除以1024
```
数据格式——> K-V
数据结构——> 哈希表，二叉树，BST，AVL，红黑树，B树，B+树

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gEMmGX1o-1681068448479)(C:\Users\86185\AppData\Roaming\Typora\typora-user-images\image-20230409043247860.png)]$

数据结构模型网站：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

哈希表：

不支持范围查询
- 因为是无序的，必须挨个匹配查询
哈希碰撞，哈希冲突（扰动函数）
hash索引仅能满足（=）（<>）和in查询
- 对于等值查询来说hash效率高，索引列的重复值如果很多，效率就会降低，这是因为遇到hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时，所以，hash索引通常不会用到重复值多的列上，比如列为性别，年龄的情况
hash索引的数据存储是没有顺序的，在order by的情况下，使用hash索引还需要对数据重新排序
对于联合索引的情况，hash值是将联合索引建合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询

$(C:\Users\86185\AppData\Roaming\Typora\typora-user-images\image-20230409042500353.png)]$

为什么Memory支持hash索引呢？
- 因为它快，基于内存的表结构，所以速度是非常快的，挨个匹配也来得及

扰动函数：

B树：

3层B树就是16×16×16

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eIcLAagM-1681068448480)(C:\Users\86185\AppData\Roaming\Typora\typora-user-images\image-20230409044020268.png)]$

B+树：

下面有指针
冗余
叶子节点包含了全量数据，非叶子节点不是全量的数据
数据都放在叶子节点里头，key值放在非叶子节点里

B树和B+树的区别？

B树和B+树都是常见的数据结构，它们实际上是一种搜索树。它们都能够支持快速检索、插入和删除操作。B树和B+树之间有几个区别，在节点拆分和索引结构上略有不同，B+树还具有更高的范围查询效率。B树对于范围节点搜索速度较慢，因为需要在节点内遍历。而在B+树中，所有数据都存在叶子节点，在这里可以很容易地遍历数据并快速检索节点。因此，B+树通常用于实现关系型数据库。

索引几层？

一般情况下3-4层的B+树足以支持千万级别的数据量存储

能存多少是跟什么紧密相关的？

跟键值，表中记录的主键紧密相关
加入p1+28占100个字节
16×1024÷100×160×16
索引列的值要尽可量能少的占用空间（数据类型）

主键int和vachar在满足业务的需求下用那个来存储啊？

int占用4个
vachar占用不定,一个字长3字节：varchar（255）=255*3=765
非空设置：如果字段没设置非空就多占1字节
谁小用谁，key值尽可量的少暂用空间

主键要自增。

UUID尽量不要用：

因为它是无序的，插入的时候就会乱序插入
会造成什么？
- 页的分裂和合并
- 会导致下面数据满了开始分裂两块，上一层就会分裂指针来指向，会影响之前结构的变更（自增的话往后面加就可以了，不会影响分裂）

思考：

一个表中可以有几个索引？

多个

一个索引对应一棵树，还是多个索引对应一棵树？

一个索引一棵树

在B+树中叶子节点存储数据，那么实际的数据存储几份？

1份

其他的索引的叶子节点存储是什么？

在innoDB存储引擎中，数据在进行插入的时候必须要跟某一个索引列绑定在一起，如果有主键，那么选择主键，如果没有主键，那么选择唯一键，如果没有唯一键，那么mysql会生成一个6字节的rowid来绑定存储，类型为长整形
其他的索引的叶子节点中存储的是跟数据绑定存储的索引列的值

什么是存储引擎？

存储引擎就是不同的数据文件在物理磁盘上的不同的组织形式
```
代码查看：
show engines;
```

innoDB是否支持hash索引？

支持的是自适应hash
什么是自适应hash？
- 我们人为是控制不了当前的索引的，这是mysql系统自动做判断的，会把我们的B+树转成对应的hash索引，这件事是用户没办法去干预的

索引失效：

like左侧加%
类型不匹配
- 如果列是字符串类型，传入条件是必须用引号引起来，不然报错或索引失效。
使用函数
使用表达式
in集合值太多（100行记录，in出来80个就不需要）
or要分情况来讨论
- or 的条件列除了同时是主键的时候，索引才会生效。其他情况下的，无论条件列是什么，索引都失效。

Mysql聚蔟和非聚蔟索引的区别是什么？

聚蔟索引：跟数据绑定的索引叫聚蔟索引，放得是整行的记录
非聚蔟索引：不跟数据绑定的叫做非聚蔟索引，放的是id值

mysql的索引类型跟存储引擎是相关的，innoDB存储引擎数据文件跟索引文件全部放在idb文件中，而myisam的数据文件放在myd文件中，索引放在myi文件中，其实区分聚蔟和非聚蔟索引非常简单，只要判断数据跟索引是否存储在一起就可以了

innoDB存储引擎在进行数据插入的时候，数据必须根索引放在一起，如果有主键就使用主键，没有主键就使用唯一键，没有唯一键就使用6字节的rowid，因为跟数据绑定在一起的就是聚蔟索引，而为了避免数据冗余存储，其他的索引的叶子节点中存储的都是聚蔟索引的key值，因此innodb中既有聚蔟索引也有非聚蔟索引，而myisam中只有非聚蔟索引。

.frm是什么？
- 存储的是表结构相关的数据
.idb是什么？
- 存储的是数据文件和索引文件，看到idb就告诉你了，是innoDB存储引擎
myisam（买桑木）存储引擎中，数据文件和索引文件是分开的！
- .frm：存储的是表结构相关的数据
- .MYD：DATA数据
- .MYI：index索引

回表：

- id，name，gender，age
- id主键，name普通索引
- select  *  from  table  where  name =‘zhangsan’；
- 查找过程：先根据name的值取name的B+树上找到对应的id值，然后再根据id值取id的B+树上找到整行的结果，这个过程叫回表
- 回表的效率低，要尽可能减少回表

需不需要回表取决于什么？

取决于你查询的列有没有在你叶子节点里包含全部的值，如果包含了就不需要回表，不包含就需要回表

索引覆盖：

- id，name，gender，age
- id主键，name普通索引
- select  id，name  from  table  where  name =‘zhangsan’；
- 查找过程：直接根据name的值取name的B+树上检索数据，发现索引的叶子节点中包含了我们要查询的所有列的信息，此时不需要回表，这个过程叫做索引覆盖
- 索引覆盖效率高，要尽量使用

最左匹配原则：

组合原则：多个字段所创建的组合索引，必须已最左边的来查询为主
- 先匹配第一个，在匹配第二个，在匹配第三个，在第四个

- id，name，gender，age
- id主键，name，age 组合索引
- select * from  table  where  name =‘zhangsan’ and age=10；符合
- select * from  table  where  age=10；不符合
- select * from  table  where  name =‘zhangsan’；符合
- select * from  table  where  age=10 and name =‘zhangsan’；符合，优化器，在调换位置后结果与没调换位置的结果一样时候，可以符合
在优化器里面，会对这种sql进行调整，转成最左匹配原则顺序，来满足我们的业务需要

索引下推：

- id，name，gender，age
- id主键，name，age 组合索引
- select * from  table  where  name =‘zhangsan’ and age=10；符合

执行过程：在没有索引下推之前，是在存储引擎中对name的值进行过滤，将赛选之后的数据拿到mysql的server中，然后在堆age进行数据过滤

有了索引下推之后，根据name和age的值直接在存储引擎中做数据筛选，返回的就是符合要求的数据
索引下推指的是，在server层进行的数据过滤下推到了存储引擎层

mysql的结构：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2TPkTIzD-1681068448481)(C:\Users\86185\AppData\Roaming\Typora\typora-user-images\image-20230409060651764.png)]$

当数据从客户端传到了server层，在传到存储引擎层
- 在存储引擎中处理玩完，返还到server中进行排序，在返回客户端，这是正常的
- 在存储引擎中处理玩完排序，返还到server中是处理完的数据，在返回客户端，这是索引下推，把server中的步骤推到存储引擎去做了

索引设计原则：

索引的设计可以遵循一些已有的原则，创建索引的时候尽量考虑符合这些原则，使用提升索引的使用效率，更高效的使用索引。

对查询频繁次较高，且数量比较大的表建立索引
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用，过滤效果最好的列的组合
使用唯一索引，区分度越高，使用索引的效率越高
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高，对于插入，更新，删除等DML操作比较频繁的表来说，索引过多，就引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗，另外索引过多的话，MYSQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的io效率，也可以提升总体的访问效率，假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升mysql访问索引的io效率
利用最左前缀，N个列组合而成的组合索引，南无相当于是创建了N个索引，如何查询时where子句中使用了组成该索引的前几个字段，南无这条查询sql可以利用组合索引来提升查询效率