MYSQL学习（十）索引

已于 2023-06-28 01:21:12 修改
阅读量130 收藏
点赞数
文章标签： mysql 学习 数据库
于 2023-06-28 01:18:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43208082/article/details/131428014
版权

索引

索引（index）是帮助Mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

优缺点：

优势劣势
提高数据检索的效率，降低数据库的IO成本索引列也是要占空间
通过缩影对数据进行排序，降低数据排序的成本，降低CPU的消耗索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

索引结构

索引结构描述InnoDB,MyISAM,Memory是否支持
B+Tree索引最常见的索引类型，大部分引擎都支持B+树索引都支持
Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询仅Memory支持
R-Tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少仅MyISAM支持
Full-text(全文索引)是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Solr,ESInnoDB5.6版本之后支持，MyISAM支持，Memory不支持

二叉树：一个节点下面包含两个子节点，左侧所有的节点比根节点小，右侧所有的节点比根节点大。

二叉树缺点：

  • 当顺序插入的时候会形成一个单向链表（比方，36、34、33、23、22、18、16依次插入，即每一个子节点都在跟节点的左侧，会形成一个单向的链表，如果需要查找，则相当于把整个链表遍历了一次）。通过红黑树可解决此

  • 由于一个根节点下只有两个子节点，当数据量过大的情况下，层级较深（二叉树的高度较高），检索速度慢 。

B-Tree(多路平衡查找树)

一颗最大度数（max-degree）为5（5阶）的b-tree，每个节点最多存储4个key，5个指针。树的度数指的是一个节点的子节点个数。

B+Tree：

所有的元素都会出现在叶子节点，非叶子节点起到索引的作用。叶子节点形成一个单向链表，每一个节点都会通过一个指针指向下一个节点。MySQLs索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能

哈希索引：

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点：

  1. Hash索引只能用于对等比较(=，in)，不支持范围查询(between,>,<,...)（因为hash存储的时候没有顺序）

  2. 无法利用索引完成排序操作（hash运算出来的结果是无序的）

  3. 查询效率高，在不出现hash碰撞的情况下，通常只需要一次检索就可以了，效率通常要高于B+tree索引

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

  • 相对于二叉树，层级更少，搜索效率更高；

  • 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低，采用B+Tree，不管是哪个数据都需要在叶子节点中查找，搜索效率稳定，而且叶子节点形成一个双向链表 ，便于范围搜索和排序。

  • 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

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

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

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

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引。

  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

select * from user where name = 'Arm';-- name字段上加了索引idx_name
-- 首先通过二级索引idx_name去找到存放的主键，然后通过主键到聚集索引中去找到行数据，这个过程称为回表查询

InnoDB主键索引的B+tree高度为多高呢？

假设：一行数据大小为1k,一页/块中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为Bigint，占用字节数为8。

高度为2：n8 + (n+1)6 = 16*1024 。 n ≈1171 1171 * 16 = 18736

高度为3：1171 * 1171 * 16 = 21939856

索引语法

-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name on table_name(index_col_name,..);
-- 查看索引
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX index_name on table_name;

SQL性能分析

SQL执行频率

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

SHOW GLOBAL STATUS LIKE 'Com_______'; -- 七个下划线代表七个字符

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf linxu系统）中配置如下信息：

# 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';
# 开启MySQL慢查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

profile详情

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

SELECT @@have_profiling;
-- profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：
SET profiling = 1;
-- 查看所有语句的耗时：
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时：
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划

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

# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

EXPLAIN执行计划各字段含义：

  • Id：select查询的序列号，表示查询中执行select子句或者式操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）

  • select_type：表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY(SELECT/WHERE之后包含了子查询)

  • type：表示连接类型，性能由好到差的连接类型为NULL（不查任何表，例如select 1）、system(查询系统表)、const（查询通过唯一索引）、eq_ref、ref、range、index、all。

  • possible_key：可能应用在这张表上的索引，一个或多个

  • Key：实际使用的索引，如果为 NULL，则没有使用索引

  • Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

  • rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的

  • filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

索引使用

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

-- 示例 联合索引idx_a_b_c
select * from table_name where a='xxx' and b='xxx' and c='xxx'; -- abc都走了索引
select * from table_name where a='xxx' and b='xxx'; -- ab都走了索引
select * from table_name where a='xxx' and c='xxx'; -- a走了索引 c未走索引
select * from table_name where b='xxx' and c='xxx'; -- bc均未走索引

范围查询：联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效（在业务允许的情况下可将>改为>=，索引就不会失效）

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

-- 查询手机尾号为15的数据 phone有索引，但是对phone进行了截取操作，此时索引失效
select * from table_name where substring(phone,10,2) ='15';

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

模糊查询：如果仅仅时尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引会失效

or连接的条件：用or分割开的条件，如果or前的条件中有索引，后面的列没有索引，那么涉及到索引都不会被用到。

数据分布影响：如果MySQL评估使用的索引比全表更慢，则不使用索引。（就是通过该字段索引查询出来的数据如果比全表扫描查出来的慢，则不使用索引。）

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

-- 建议使用idx_a索引
select * from table_name use index(idx_a) where a ='xxx';
-- 不使用idx_a索引
select * from table_name ignore index(idx_a) where a ='xxx';
-- 强制使用idx_a索引
select * from table_name use force index(idx_a) where a ='xxx';

覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。（using index condition：查找使用了索引，但是需要回表查询数据。using where,using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据）

-- id为主键，idx_name_phone联合索引
select id,name,phone,adderss from table_info where name ='xxx' and phone ='123456';
#个人理解：由于idx_name_phone是二级索引，二级索引的叶子节点挂的是主键id,查询的信息中有个adderss,所以还需要通过主键id(聚簇索引的叶子节点挂的是一行数据)去查询address，这个过程称为回表。如果只select id,name,phone。因为索引树上都有这些数据，所以不需要回表查询。

前缀索引： 当字段类型为字符串（varchar,text等）时，有时候需要索引很长的字符串，这会让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

创建前缀索引语法：create index idx_xxx on table_name(column(n))；-- n指前缀的长度

前缀长度：可以根据索引的选择性来决定，而选择性时指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性时1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) from table_user; -- 查出不重复email的数量
-- 不重复email的数量与 整个email数量的占比
select count(distinct email)/count(*) from table_user; 
-- 截取n个字符之后的email数量与整个email数量的占比，如果这个比例更接近1说明性能更，则n就是前缀
select count(distinct substring(email,1,n))/count(*) from table_user;

单列索引与联合索引

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

联合索引：即一个索引包含了多个列

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

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建议索引。

  2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

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

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

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

  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效地用于查询。

学习来源：17. 进阶-索引-语法_哔哩哔哩_bilibili

Once0311
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL学习-索引部分
05-20
用Xmin制作的思维导图
MySQL入门学习-深入索引.空间索引
weixin_69763181的博客
07-15 1021
在 MySQL 中，空间索引是一种用于对空间数据类型（如 'GEOMETRY' ）进行高效查询的索引类型。
MySQL学习笔记5——函数和索引
qq_44778824的博客
04-22 2464
- 删除索引DROP INDEX 索引名 ON 表名；--删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;索引的成本存储空间的开销，是指索引|需要单独占用存储空间。数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索弓|能够指向正确的记录。因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。
MySQL学习笔记-索引
金鳞踏雨
04-03 1762
什么是索引？ 索引的分类 功能分类 结构分类 索引的操作 创建索引 创建索引的语法 （1）为student表中姓名列创建一个普通索引 （2）为student表中年龄列创建一个唯一索引 查看索引 查看索引的语法 （1）查看student表中的索引 alter语句添加索引 添加索引的语法 （1）为student表中name列添加全文索引 删除索引 删除索引的语法 （1）删除student表中的idx_score索引 索引的实现原则 磁盘存储 BTree B+Tr.
MySQL学习[2] ——MySQL索引
shenfenxihuan的博客
08-11 4909
通过将多个字段组合成一个索引，这个索引就叫做联合索引。联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。这是**最左匹配原则，按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效**，这样就无法利用到索引快速查询的特性了。
MySQL入门学习-深入索引.匹配顺序
weixin_69763181的博客
07-13 993
在 MySQL 中，索引的匹配顺序是指在查询执行时，数据库系统根据查询条件中涉及的列和索引的结构，决定如何使用索引来提高查询效率的方式。
MySQL学习(四)之索引篇
weixin_65743593的博客
01-19 2189
MySQL索引，B树，B+树，为什么需要注意联合索引中的顺序？为什么要建立联合索引？或者说联合索引的好处？什么是最左前缀法则？什么情况索引会失效？
MySQL索引知识点学习
weixin_49369478的博客
11-23 2140
什么是索引？ MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。通俗的说，索引就相当于一本书的目录，能加快数据库的查询速度。 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。InnoDB存储引擎默认将索引文件和数据文件放在 .ibd文件 。 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二
mysql索引学习必备知识
默慊的博客
09-02 1931
1、创建索引的几种方式 1.1 直接创建 CREATE INDEX indexName ON table_name (column_name) 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。 1.2 修改表结构(添加索引) ALTER table tableName ADD INDEX indexName(columnName) 1.3 创建表的时候直接指定 CREATE TABLE mytable(
MySQL学习总结--索引 & 排序
u012324798的博客
05-28 1792
索引模型、索引类型、索引覆盖、索引下推、唯一索引 VS 普通索引、change buffer 重建索引/表、索引选择异常、字符串字段如何加索引、order by 排序字段要加索引、索引字段不要应用函数，其传参可以应用函数。
新手学习MySQL索引
09-09
MySQL索引是数据库管理系统中用于加速数据检索的关键技术。它的工作原理类似图书的目录，能够帮助数据库系统快速定位和访问所需的数据。对于大型网站来说，尤其是每天产生大量数据的环境，索引的作用至关重要，因为...
MySQL中的唯一索引的简单学习教程
12-15
在MySQL数据库中，唯一索引（UNIQUE）是一种特殊的索引类型，它的主要作用在于确保索引列中的每个值都是唯一的，从而防止数据重复。这与主键索引（PRIMARY KEY）有所不同，主键索引不仅要求唯一性，还强制列的非空性...
MySQL学习笔记(3)——索引练习
12-14
根据之前的MYSQL学习笔记(2)的学习，通过一些小练习来加深对索引的理解。 建下表并插入数据最后建立相关索引： CREATE TABLE IF NOT EXISTS `test`( id int PRIMARY KEY auto_increment, c1 char(10), c2 char(10), ...
MySQL中几种常见的行格式
weixin_45923633的博客
09-13 450
动态格式会根据行中数据的实际大小动态地调整行的存储方式。如果行中的某个字段是变长的，那么这个字段的实际数据将被存储在行的末尾，而不会在行的开头占用固定的空间。这种格式适用于所有列都是固定长度的情况，可以提供最快的访问速度，但可能会浪费存储空间。：紧凑格式试图减少内部碎片，使得行的平均大小变小，从而在一个数据页中可以存储更多的行。：页面格式是一种较新的行存储格式，它旨在提高大表的访问性能和存储效率。是用来指定表中行的存储格式的属性。不同的行格式会影响数据的存储方式，进而影响存储空间的使用效率和访问性能。
【MySQL】表的操作【有关表结构的操作】【创建、查看、删除、修改表结构】
aaa114514aaaa的博客
09-12 1207
对于mysql数据库的表的结构的查看，创建、删除、修改、【对字段的添加、修改】
2024最新版MySQL详细学习教程
最新发布
2401_86940371的博客
09-17 1074
返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。如果count是负数，返回第(count的绝对值(从右边数))个字符右边的字符串。
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统
程序员大金的博客
09-11 1316
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统，附源码。
MYSQL数据库基础篇——MYSQL的安装与使用
qq_74177011的博客
09-11 509
mysql -u表示用于登录的用户名，后面的root就是用户名称，之前讲到，安装mYSQL时默认用户名是root，-p表示指定密码，即登录密码。首先打开命令提升符，并输入net start mysql80可以启动，输入net stop mysql80可以停止。这里选择第一个，当然，有可能你的版本下的MYSQL并没有这个选项，那么我们可以选择。注意：如果报以下错误，则可以尝试通过管理员的方式打开命令提示符进行操作。输入mysql -u root -p，然后输入密码就好了。然后显示下面的界面说明连接成功了；
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值