MySQL提高篇，深度剖析数据库索引，提高检索效率，优化数据库

已于 2024-06-07 10:19:59 修改
阅读量677 收藏 21
点赞数 27
分类专栏： MySQL数据库 文章标签： mysql oracle sql 数据库
于 2024-06-07 00:47:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_73933965/article/details/139508069
版权

文章目录

MySQL两小时精通，深入剖析多表查询

MySQL体系结构

连接层，服务层，引擎层，存储层

存储引擎

  • 存储数据、建立索引、更新/查询数据等技术的实现方式

  • 存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

建表的默认存储引擎为：InnoDB

在创建表时，指定存储引擎
CREATE TABLE 表名(
			字段1  字段1类型 [COMMENT 字段1注释]
			......
			字段n  字段n类型 [COMMENT 字段n注释]
	) ENGINE = INNODB [COMMENT 表注释];
查看当前数据库支持的存储引擎
SHOW ENGINES ；
InnoDB介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，是默认的MySQL存储引擎

InnoDB特点

  • DML操作遵循ACID模型，支持事务

  • 行级锁，提高并发访问性能

  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性

InnoDB文件

​ xxx.ibd ：xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引

​ 参数：innodb_file_per_table

逻辑存储结构
结构大小
TableSpace(表空间)
Segment(段)
Extent(区)大小为：1M （可以包含64个页）
Page(页)大小为：16K
Row(行)
MyISAM介绍

MyISAM是MySQL早期的默认存储引擎

MyISAM特点

  • 不支持事务，不支持外键

  • 支持表锁，不支持行锁

  • 访问速度快

MyISAM文件
文件作用
xxx.sdi存储表结构信息
xxx.MYD存储数据
xxx.MYI存储索引
Memory介绍

Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

Memory特点

  • 内存存放

  • hash索引（默认）

Memory文件
文件作用
xxx.sdi存储表结构信息

重点了解InnoDB和MyISAM的区别

InnoDBMyISAM
支持事务安全不支持事务安全
锁机制是行锁锁机制是表锁
支持外键不支持外键
三者的特性
存储引擎特性
InnoDB是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择
MyISAM如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的
MEMORY将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

InnoDB：存储业务系统中对于事务、数据完整性要求较高的核心数据

索引

优势
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势
  • 索引列也是要占空间的
  • 索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低
索引结构
索引类型特点
B+Tree索引最常见的索引类型，大部分引擎都支持B+树索引
Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES
B-Tree(多路平衡查找树)

  • 所有元素都会出现在叶子节点

  • 叶子节点形成了单向链表。

在MySQL索引数据结构中对B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能

Hash(哈希索引)

  • 采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

  • 如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决

Hash索引特点
  1. Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，<，…)
  2. 无法利用索引完成排序操作
  3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引
为什么InnoDB存储引擎选择使用B+Tree索引结构
  1. 相对于二叉树，层级更少，搜索效率高
  2. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  3. 相对Hash索引，B+tree支持范围匹配及排序操作

索引分类

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

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

聚集索引(Clustered Index)

​ 含义：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

​ 特点：必须有，而且只有一个

二级索引(Secondary Index)

​ 含义：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

​ 特点：可以存在多个

聚集索引选取规则
  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一( UNIQUE ) 索引作为聚集索引
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
回表查询

先走二级索引找到对应的主键，再去聚集索引查找数据

不重要

假设：

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

高度为2：

​ n * 8 + (n+1) * 6 = 16 * 1024 算出n约为1170

​ 1171 * 16 = 18736

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

索引语法

创建索引
CREATE[ UNIQUE | FULLTEXT ] INDEX index_name ON table_name( index_col_name , ...);

index_name索引名称一般为：index_表名字段名

查看索引
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

​ MySQL客户端连接成功后，通过show [ session|global ] status命令可以提供服务器状态信息

​ session是当前会话，global是全局

通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次；

SHOW GLOBAL STATUS LIKE 'Com_______'；

​ 七个下划线

慢查询日志

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

  1. vi /etc/my.cnf

  2. 按 i ，在最后加上下面的两句话，加完按esc退出，输入:wq保存并退出

    • 开启MySQL慢查询日志的开关slow_query_log=1

    • 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志long_query_time=2

  3. cd /var/lib/mysql 接着输入ll

  4. 找slow.log

  5. cat ********slow.log

    查看慢日志文件

  6. tail -f *******slow.log

    查看慢日志文件实时插入的信息

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

查看是否支持profile操作
SELECT @@have_profiling;

通过have_profiling参数，能够看到当前MySQL是否支持profile操作

查看profiling是否打开
SELECT @@profiling;
开启profiling
SET profiling = 1；

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

查看每一条SQL的耗时基本情况
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 表名 WHERE 条件;
EXPLAIN执行计划各字段含义
id

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

select_type

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

type

表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all

possible_key

显示可能应用在这张表上的索引，一个或多个

Key

显示实际使用的索引，如果为NULL，则没有使用索引

Key_len

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

rows

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

filtered

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

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列

如果跳跃某一列，索引将部分失效（后面的字段索引失效），如果跳过了第一列，整个索引失效

范围查询

联合索引中，出现范围查询 > , < 范围查询右侧的列索引失效

如果使用 >= , <= 就不会出现索引失效

索引失效的情况
失效问题解释
索引列运算不要在索引列上进行运算操作，索引将失效
字符串不加单引号字符串类型字段使用时，不加引号，索引将失效
模糊查询如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。前面加%不走索引，后面加%可以走索引
or连接的条件用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
数据分布影响如果MySQL评估使用索引比全表更慢，则不使用索引

​

如何使用索引

SQL提示

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

use index

建议指定使用哪个索引（MySQL会自己判断优劣，进行选择）

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
ignore index

忽略不使用哪个索引

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程'；
force index

强制指定使用哪个索引（MySQL不会自己判断）

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

索引分类

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *

前缀索引

当字符类型为字符串(varchar , text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询

效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法
create index 索引名 on 表名(字段名(n));

前n个字符

前缀长度

可以根据索引的选择性——不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;
单列索引

一个索引只包含单个列

联合索引（推荐使用）

一个索引包含了多个列

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引
  2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
泰勒霉霉霉
关注
  • 27
    点赞
  • 踩
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL的索引是如何提高查询效率的？
weixin_43776741的博客
04-14 3776
MySQL的索引是如何提高查询效率的？一.索引是什么？二.MySQL中存储索引用的是什么结构？三.为什么要用B+树？ 一.索引是什么？ 索引是数据库中用来提高查询效率的技术，类似于目录。如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树桩结构保存，查询数据时会大大降低磁盘块的访问数量，从而提高查询效率。如果表中的数据很少，使用索...
数据库-深度剖析mysql索引原理（上）（三）
平凡之路无尽路的博客
11-21 882
初步理解索引本质
数据库-深度剖析mysql索引原理（下）（四）
平凡之路无尽路的博客
11-30 382
主要介绍索引使用原则以及原理
Mysql底层剖析——Mysql性能优化篇
Durian_piza的博客
07-14 514
1、变小的通常更好 尽量使用正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少2、简单就好 比如使用整型而不是varchar类型存储IP地址3、尽量避免使用Null 因为查询中包含NULL的列，通常来说更难优化，可为Null的列会使用更多的存储空间，在MySQL里也需要特殊处理。4、选择合适的表示列5、整数通常是标识列最好的选择6、避免使用ENUM和SET作为标识列7、避免使用字符串类
MySQL索引原理深度剖析与优化
weixin_42678790的博客
08-31 469
文章目录一、概念篇索引是什么 ？索引的优势和劣势二、数据结构篇B+结构（改造B树）等值查询：范围查询三、索引实现MyIsam索引根据主键等值查询数据：根据主键范围查询数据：辅助索引InnoDB索引主键索引（聚簇索引）等值查询数据：辅助索引组合索引如何优化？平常建索引需要注意什么？ 一、概念篇 索引是什么 ？ 1. 官方介绍索引是帮助MySQL高效获取数据的数据结构。能加快数据库的查询速度。 2. 常见索引有聚集索引、覆盖索引、组合索引、前缀索引、唯一索引。 索引的优势和劣势 优势： 1. 可以提高数据检索
【深入学习二】MySQL 索引深度剖析
搬运Gong
02-04 585
B Tree、B+Tree、索引、数据结构
Mysql索引的深度剖析
evanYang的博客
07-29 469
索引是什么 维基百科对数据库索引的定义： 数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。 首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。 但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为他它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。 索引类型 在InnoDB里面，索引类型
图解｜带你彻底搞懂MySQL的索引_索引的样子，深度剖析原理
2401_84296692的博客
04-19 910
从页32开始遍历吗？但是一个数据页中的记录可能非常多，为了逃避低效的遍历，InnoDB引擎的设计者想出了一种绝妙的搜索方法，把数据页中的所有记录（包括伪记录）分成若干个小组（并对每个小组内的组员数量做了规定），每个小组选出组内最大的一条记录作为“小组长”，接着把所有小组长的地址拿出来，编成目录。因此，虽然在一个数据页内能够做到主键的快速查询，但是InnoDB存储引擎不知道你要查找的记录所在的页号，那也只能从第一页开始沿着双向链表一直进行查找，遍历每一页，至于在每一个数据页中是怎么查找的，你已经很清楚了。
MySQL 索引原理深入剖析
weixin_43281498的博客
03-26 709
一、什么是索引 1.1 索引是什么 维基百科上对数据库索引的定义： 数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、 更新数据库表中数据。 据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们要从表的全部行数据里面检索一条数据，只能依次遍历这张表的全部数据, 直到找到这条数据。 1.2 索引的类型 在 InnoDB 里面，索引类型有三种，普通索引、唯一索引(主键索引是特殊的唯一 索引)、全文索引。 普通（Normal）：也叫非唯一索引，是最
MySQL数据库查询优化：深度剖析查询语句，提升效率，让你的查询语句快如闪电
[MySQL数据库查询优化：深度剖析查询语句，提升效率，让你的查询语句快如闪电](https://img-blog.csdnimg.cn/img_convert/94a6d264d6da5a4a63e6379f582f53d0.png) # 1. MySQL数据库查询优化概述** MySQL数据库查询...
数据库冒险手册：从索引优化到事务管理的深度探索之旅 ️
java专栏
06-30 687
在一些高级场景下，你甚至可以创建自己的索引类型，比如GiST、SP-GiST、GIN和BRIN等，以应对特殊的数据结构和查询需求。不过，这通常需要更深入的数据库知识和经验。-- 假设我们有一个地理位置相关的应用注释：这里展示了如何为地理空间数据类型创建GIST索引，这在处理点、线、面等空间数据查询时非常有效。索引，简而言之，就是数据库中用于加速数据检索的一类数据结构。它的工作原理类似书籍的目录，通过创建指向数据行的指针列表，使得查找特定值的速度大大加快。没有索引，数据库就需要逐行遍历整个表，效率极低。
Mysql索引底层原理及其优化方案
nickel369的博客
05-18 686
使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈在 select 语句之前增加 explain 关键字 ，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。
MySQL数据库中的JSON字段：深度剖析其功能与应用，掌握核心技术
[MySQL数据库中的JSON字段：深度剖析其功能与应用，掌握核心技术](https://ucc.alicdn.com/pic/developer-ecology/5hjnfyzcfuvma_990309e63370421eb31137ebdc8ef9cc.png?x-oss-process=image/resize,s_500,m_lfit) ...
玩转MySQL：深入底层理解索引原理
Java_LingFeng的博客
11-05 382
本篇也是MySQL索引机制，应该是最终章了，在经过前面两篇后，已经对索引有了很高的掌握度了，但MySQL的索引机制，自始至终对于我们都是一个黑盒般的存在，我们并不清楚建立索引后MySQL会发生什么，也并不清楚使用索引查询时会如何检索......。
策略规划：在MySQL中实现数据恢复的全面指南
2401_85339615的博客
09-04 1096
数据恢复是数据库管理中至关重要的一环，它确保在发生数据丢失或损坏的情况下，能够迅速且准确地恢复数据。在MySQL中，实现有效的数据恢复策略规划需要综合考虑备份策略、备份类型、存储管理、故障转移机制以及恢复流程。本文将深入探讨如何在MySQL中进行数据恢复的策略规划，包括策略制定、技术实现和最佳实践。
MySQL 大量 IN 的查询优化
最新发布
xchenhao 的博客
09-09 314
MySQL 大量 IN 的查询优化
三.海量数据实时分析-FlinkCDC实现Mysql数据同步到Doris
墨家巨子@俏如来
09-07 1164
通过上面的 route 配置，会将 app_db.orders 表的结构和数据同步到 ods_db.ods_orders 中。这样，就可以将诸如 app_db.order01、app_db.order02、app_db.order03 的表汇总到 ods_db.ods_orders 中。参考：https://nightlies.apache.org/flink/flink-cdc-docs-release-3.0/zh/docs/get-started/quickstart/mysql-to-doris/
【重学 MySQL】十四、显示表结构
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-06 659
以上就是在MySQL中显示表结构的几种常用方法。DESCRIBE或DESC命令和命令提 供了快速查看表列信息的简便方式，而查询数据库则提供了更详细、更灵活的信息获取方式。命令则适用于需要查看或复制表完整结构的情况。
优化数据库性能：MySQL索引深度解析
本文主要介绍了MySQL数据库中的索引特性，包括不同类型的索引以及它们对数据库性能的影响。通过创建合适的索引，可以显著提升查询效率，但也会在写操作（如插入、更新、删除）时增加IO成本。文章提到了主键索引、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值