当前位置： 首页 > news >正文

MySQL之索引详解

news 来源：原创 2024/5/9 15:52:35

华子目录

  • 索引概述
    • 优缺点
  • 索引的原理
  • 索引的设计原则
  • 索引结构
    • B-tree（多路平衡查找树）
    • B+tree
    • Hash
  • 为什么InnoDB存储引擎选择B+tree？
  • 索引分类
    • 聚集索引选取规则
  • 单列索引和多列索引
  • 前缀索引
  • 创建索引
    • 1.创建表时创建索引
    • 2.在已经存在的表上创建索引
    • 3.使用alter table语句创建索引
  • 使用计划查询SQL使用索引情况（==explain==）
  • 查看索引
  • 删除索引
  • 案例
  • SQL性能分析
    • 查看SQL执行频率
    • 慢查询日志
    • profile详情
    • explain执行计划
  • 索引的使用
    • 验证索引效率
    • 最左前缀法则
    • 范围查询
    • 索引列运算
    • 字符串不加引号
    • 模糊查询
    • or连接的条件
    • 数据分布的影响
    • SQL提示
    • 覆盖索引
    • 前缀索引
    • 单列索引和联合索引

索引概述

  • 索引（index） 是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
  • 在这里插入图片描述
  • 无索引时，需要一条一条查找每一条数据，有索引时就不需要逐一查询数据
  • 在数据库中用来加速对表的查询；通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O；与表独立存放，但不能独立存在，必须属于某个表；由数据库自动维护，表被删除时，该表上的索引自动被删除；
  • 索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

优缺点

  • 在这里插入图片描述

索引的原理

就是把无序的数据变成有序的查询

  1. 把创建的索引的列的内容进行排序
  2. 对排序结果生成倒排表
  3. 在倒排表内容上拼上数据地址链
  4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。

  1. 选择惟一性索引
  2. 为经常需要排序、分组和联合操作的字段建立索引
  3. 为常作为查询条件的字段建立索引
  4. 限制索引的数目
  5. 尽量使用数据量少的索引
  6. 尽量使用前缀来索引
  7. 删除不再使用或者很少使用的索引

在这里插入图片描述

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

索引结构描述
B+tree索引最常见的索引类型，大部分引擎都支持B+tree索引
Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，通常使用较少
R-tree（空间索引）空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

在这里插入图片描述
在这里插入图片描述

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

在这里插入图片描述

B+tree

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

Hash

在这里插入图片描述
在这里插入图片描述

为什么InnoDB存储引擎选择B+tree？

在这里插入图片描述

索引分类

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

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

分类含义特点
聚集索引将数据存储与索引放到了一块，索引结构的叶子节点保存了完整的行数据必须存在，且只有一个
非聚集索引（二级索引）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则

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

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

  • 第一个执行效率高：因为第一个查的是主键索引，只查一张表。第二个先查name字段的索引，再根据name字段的索引值进行回表查询，查两张表。

单列索引和多列索引

MySQL中的单列索引和多列索引都是用于提高数据库查询效率的工具，它们有一些不同之处。

  1. 单列索引：

    • 单列索引是针对表中的单个列创建的索引。
    • 它可以加速针对该列的查找、排序和过滤操作。
    • 适用于单列条件查询，例如：SELECT * FROM table WHERE column = value;
    • 单列索引可以包括在多列查询中，但只有第一列索引将被用于加速查找。
    • 创建单列索引的语法示例：CREATE INDEX index_name ON table_name (column_name);

  2. 多列索引：

    • 多列索引是针对表中多个列组合而成的索引。
    • 它可以加速涉及这些列组合的查询，例如联合查询或者多列条件查询。
    • 当查询涉及到多个列时，多列索引通常比单列索引更有效。
    • 多列索引的列顺序非常重要，因为只有查询中使用的列的左侧前缀才会被索引所利用。
    • 创建多列索引的语法示例：CREATE INDEX index_name ON table_name (column1, column2, ...);

前缀索引

在 MySQL 中，你可以创建前缀索引来提高查询效率。前缀索引是指只对列值的一部分进行索引，而不是整个列值。这在某些情况下可以减少索引的大小，并提高查询性能，尤其是对于较大的列类型（如 TEXT 或 VARCHAR）。

要在 MySQL 中创建前缀索引，你可以使用以下语法：

CREATE INDEX index_name ON table_name (column_name(prefix_length));

在这里，prefix_length 是你希望索引的列值的前缀长度。以下是一个示例：

CREATE TABLE my_table (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100)
);CREATE INDEX idx_name_prefix ON my_table (name(10));

在这个示例中，我们为 name 列创建了一个前缀长度为 10 的索引。这意味着索引将仅包含 name 列值的前 10 个字符。你可以根据你的需求调整 prefix_length 的值。

请注意，使用前缀索引时需要注意选择适当的前缀长度。如果前缀长度太短，可能会导致索引失效，而如果太长，可能会增加索引的大小并降低性能提升效果。因此，需要根据你的数据和查询模式来选择合适的前缀长度。

创建索引

  • 创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。
  • 创建索引有三种方式，这三种方式分别是创建表时创建索引、在已经存在的表上创建索引和使用alter table语句来创建索引。

1.创建表时创建索引

创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：

mysql> create table 表名(-> 列名 数据类型 约束,-> 列名 数据类型 约束,--------------------> 列名 数据类型 约束,-> [unique/fulltext] index 索引名(列名 [asc/desc])-> );

mysql> create table mytable(-> id int auto_increment primary key,-> name varchar(20),-> age int(3),-> phone int unique,-> unique index index_mytable_phone(phone),#创建唯一索引-> index index_mytable_name(name(3)),#创建前缀索引-> index index_mytable_age(age)#创建常规索引-> );
Query OK, 0 rows affected, 2 warnings (0.07 sec)mysql> show index from mytable;
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mytable |          0 | PRIMARY             |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| mytable |          0 | phone               |            1 | phone       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| mytable |          0 | index_mytable_phone |            1 | phone       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| mytable |          1 | index_mytable_name  |            1 | name        | A         |           0 |        3 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| mytable |          1 | index_mytable_age   |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

mysql> create table mytable(-> id int auto_increment primary key,-> name varchar(20),-> age int(3),-> phone int unique,-> index index_mytable_name(name(3)),-> index index_mytable_age(age)-> );
Query OK, 0 rows affected, 1 warning (0.05 sec)mysql> show index from mytable;
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mytable |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| mytable |          0 | phone              |            1 | phone       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| mytable |          1 | index_mytable_name |            1 | name        | A         |           0 |        3 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| mytable |          1 | index_mytable_age  |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

注：MySQL8会为主键和唯一和外键自动创建索引

2.在已经存在的表上创建索引

前提是：该表上无索引，需要手动添加索引

mysql> create [unique/fulltext] index 索引名 on 表名(列名);  #创建单列索引
mysql> create [unique/fulltext] index 索引名 on 表名(列名1,列名2...);  #创建多列索引 

mysql> create table mytable(-> id int auto_increment primary key,-> name varchar(20)-> );mysql> create unique index index_mytable_name on mytable(name(3));mysql> show index from mytable;
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mytable |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| mytable |          0 | index_mytable_name |            1 | name        | A         |           0 |        3 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

3.使用alter table语句创建索引

mysql> alter table 表名 add [unique/fulltext] index 索引名(列名);

mysql> create table mytable(-> id int auto_increment primary key,-> name varchar(20)-> );#通过添加索引的方式添加约束
mysql> alter table mytable add unique index index_mytable_name(name);   mysql> show index from mytable;
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mytable |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| mytable |          0 | index_mytable_name |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

使用计划查询SQL使用索引情况（explain）

mysql> desc stu;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| age     | int         | NO   |     | NULL    |                |
| classid | int         | NO   | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+mysql> show index from stu;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| stu   |          0 | PRIMARY             |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| stu   |          1 | stu_classid_foreign |            1 | classid     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+mysql> select * from stu;
+-----+--------+-----+---------+
| id  | name   | age | classid |
+-----+--------+-----+---------+
| 101 | 小天   |  18 |    1001 |
| 102 | 小明   |  20 |    1003 |
| 103 | 小红   |  13 |    1002 |
+-----+--------+-----+---------+mysql> explain select * from stu where id=101;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | stu   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+mysql> explain select * from stu where id=103;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | stu   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

explain分析结果的含义:id: 每一行的编号select_type: 查询类型。这里是 SIMPLE，表示这是一个简单的查询。table：这是表的名字。partitions: 所使用的分区（如果有）type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）possible_keys：可能可以利用的索引的名字Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。key_len：索引中被使用部分的长度，以字节计。ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1filtered: 表示按表的过滤条件过滤后的结果所占百分比。Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

查看索引

mysql> show index from 表名;

删除索引

mysql> drop index 索引名 on 表名;

案例

按照下列需求，完成索引的创建

  1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引（创建一个常规索引）
mysql> create index index_stu_name on stu(name);
  1. phone手机号字段的值是非空且唯一（primary），为该字段创建唯一索引（创建一个唯一索引）
mysql> create unique index index_stu_phone on stu(phone);
  1. 为profession、age、status创建联合索引（多个常规索引）
mysql> create index index_stu_profession_age_status on stu(profession,age,status);
  1. 为email建立合适的索引来提升查询效率（创建一个常规索引）
mysql> create index index_stu_email on stu(email);

SQL性能分析

查看SQL执行频率

MySQL客户端连接成功后，通过show [session/global] status命令可以提供服务器状态信息，可以查看当前数据库insert，update，delete，select的访问频次

mysql> show [session/global] status like 'Com_______';

在这里插入图片描述

慢查询日志

  • 慢查询日志记录了所有执行时间超过指定时间（long_query_time，默认10秒） 的所有SQL语句的日志。
  • MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
#开启MySQL慢日志查询开关
slow_query_log=1#设置慢日志时间为2秒，当SQL语句执行时间超过2秒，就会是为慢查询，并将该慢查询记录在慢查询日志中
long_query_time=2
  • 配置完成后，重新启动MySQL服务，进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

在这里插入图片描述

profile详情

  • show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling变量，能够看到当前MySQL是否支持profile操作
mysql> select @@have_profiling;
  • 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:
mysql> set profiling=1;

执行一系列SQL操作后，然后通过如下指令查看SQL执行的耗时情况：

#查看每一条SQL的耗时基本情况
mysql> show profiles;#查看指定query_id的SQL语句各个阶段的耗时情况（在SQL指定过程中的各个阶段）
mysql> show profile for query_id;#查看指定query_id的SQL语句cpu的使用情况
mysql> show profile cpu for query query_id;

explain执行计划

  • explain或desc命令获取MySQL执行select语句的信息，包括select语句执行过程中如何连接和连接的顺序。
#直接在select语句之前加上关键字explain或desc
mysql> explain/desc select 字段列表 from 表名 where 条件;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

索引的使用

验证索引效率

在这里插入图片描述

最左前缀法则

在这里插入图片描述

范围查询

在这里插入图片描述

索引列运算

在这里插入图片描述

字符串不加引号

在这里插入图片描述

模糊查询

在这里插入图片描述

or连接的条件

在这里插入图片描述

数据分布的影响

在这里插入图片描述

SQL提示

在这里插入图片描述

覆盖索引

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

前缀索引

在这里插入图片描述
在这里插入图片描述

单列索引和联合索引

在这里插入图片描述
在这里插入图片描述

相关文章：

  • 【RK3288 Android6， T8PRO 快捷按键 gpio 配置上拉输入】
  • ROS从入门到精通4-2：Docker安装ROS、可视化仿真与终端复用
  • 利用MATLAB模拟点电荷的电场分布
  • 通过 python 和 wget 批量下载文件（在Linux/Ubuntu/Debian中测试）
  • C语言深入学习 --- 1.数据的存储
  • 【Apache Camel】基础知识
  • 关于制作一个Python小游戏(三)
  • Batch Nomalization 迁移学习
  • 【框架学习 | 第一篇】一篇文章读懂MyBatis
  • 2024.1.28 校招 实习 内推 面经
  • 【嵌入式——QT】Model/View
  • 【PHP】PHP通过串口与硬件通讯，向硬件设备发送数据并接收硬件返回的数据
  • 基于Springboot免费搭载轻量级阿里云OSS数据存储库（将本地文本、照片、视频、音频等上传云服务保存）
  • 揭秘共享旅游卡项目变现的6种方式，至少有4种适合创业者！
  • STM32/GD32——I2C通信协议
  • 2018天猫双11|这就是阿里云！不止有新技术，更有温暖的社会力量
  • ECMAScript入门（七）--Module语法
  • nginx（二）：进阶配置介绍--rewrite用法，压缩，https虚拟主机等
  • Redis 中的布隆过滤器
  • vue-cli3搭建项目
  • Vue全家桶实现一个Web App
  • webpack入门学习手记（二）
  • 闭包--闭包作用之保存(一)
  • 初探 Vue 生命周期和钩子函数
  • 大型网站性能监测、分析与优化常见问题QA
  • 翻译--Thinking in React
  • 算法-插入排序
  • 微信小程序--------语音识别（前端自己也能玩）
  • 小程序、APP Store 需要的 SSL 证书是个什么东西？
  • 新版博客前端前瞻
  • 一起来学SpringBoot | 第三篇：SpringBoot日志配置
  • 做一名精致的JavaScripter 01：JavaScript简介
  • 看到一个关于网页设计的文章分享过来！大家看看！
  • PostgreSQL 快速给指定表每个字段创建索引 - 1
  • 基于django的视频点播网站开发-step3-注册登录功能 ...
  • 如何通过报表单元格右键控制报表跳转到不同链接地址 ...
  • ​DB-Engines 11月数据库排名：PostgreSQL坐稳同期涨幅榜冠军宝座
  • ​比特币大跌的 2 个原因
  • #调用传感器数据_Flink使用函数之监控传感器温度上升提醒
  • (1)Map集合 (2)异常机制 (3)File类 (4)I/O流
  • （11）MATLAB PCA+SVM 人脸识别
  • （2.2w字）前端单元测试之Jest详解篇
  • (libusb) usb口自动刷新
  • （Redis使用系列） Springboot 使用Redis+Session实现Session共享 ，简单的单点登录 五
  • (Spark3.2.0)Spark SQL 初探： 使用大数据分析2000万KF数据
  • （八）五种元启发算法（DBO、LO、SWO、COA、LSO、KOA、GRO）求解无人机路径规划MATLAB
  • （二）七种元启发算法（DBO、LO、SWO、COA、LSO、KOA、GRO）求解无人机路径规划MATLAB
  • (附源码)计算机毕业设计SSM保险客户管理系统
  • （六）Hibernate的二级缓存
  • （六）什么是Vite——热更新时vite、webpack做了什么
  • （七）Java对象在Hibernate持久化层的状态
  • (三)docker：Dockerfile构建容器运行jar包
  • （一）80c52学习之旅-起始篇
  • (转)Oracle 9i 数据库设计指引全集(1)
  • （转）关于如何学好游戏3D引擎编程的一些经验