MySQL进阶学习1

于 2024-09-02 11:39:21 发布
阅读量664 收藏 20
点赞数 20
文章标签： mysql 学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/eugene03/article/details/141815898
版权

MySQL体系架构

image.png

  • 连接层
    • 最上层是一些客户端和链接服务，主要完成一些类似于连接处理，授权认证，相关的安全方案，服务器也会为安全接入的每个客户端校验它具有的操作权限
  • 服务层
    • 第二层架构主要完成大多数核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行，所有缓存储引擎的功能也在这一层实现，如过程，函数等
  • 引擎层（不同的存储引擎，索引结构不同，MySQL默认innodb）
    • 存储引擎真正的负责了MySQL中数据的存储和提取，服务通过API和存储引擎进行通信，不同的存储引擎具有不同的功能，这样我们可以根据自己的需求，来选择合适的存储引擎
  • 存储层
    • 主要将数据存储在文件系统上，并完成与存储引擎的交互

存储引擎

存储数据，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
1724915228056.png
在创建表时，指定存储引擎

CREATE TABLE 表名 {
    ...
} ENGINE = INNODB

查看当前数据库支持的存储引擎

SHOW ENGINES
INNDB引擎

INNDB 是一种兼顾高可靠性和高性能的通用存储引擎，
特点：

  • DML操作遵循ACID模型，支持事务
  • 行级锁，提高并发访问性能
  • 支持外接FOREIGN KEY 约束 保证数据的完整性和正确性

文件：
xxx.idb: xxx代表的是表名，innodb引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm, sdi ) 数据 和 索引， 参数 innodb_file_per_table （默认开启）(MySQL8.0后 每个表都会有一个表空间文件)

MyISAM和Memory引擎
MYISAM

是MySQL做早的默认存储引擎
特点：

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

文件

  • xxx.sdi: 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储引擎
Memory

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

  • 内存存放
  • hash索引（默认）

文件：

  • xxx.sdi: 存储表结构信息
    | 特点 | INNODB | MYISAM | MEMORY |
    | — | — | — | — |
    | 存储限制 | 64TB | 有 | 有 |
    | 事务安全 | 支持 | - | - |
    | 锁机制 | 行锁 | 表锁 | 表锁 |
    | B+tree索引 | 支持 | 支持 | 支持 |
    | Hash索引 | - | - | 支持 |
    | 全文索引 | 支持（5.6版本后） | 支持 | - |
    | 空间使用 | 高 | 低 | N/A |
    | 内存使用 | 高 | 低 | 中等 |
    | 批量插入速度 | 低 | 高 | 高 |
    | 支持外接 | 支持 | - | - |
存储引擎的选择
  • INNODB
    • MySQL默认存储引擎，支持事务，外接，如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新，删除和操作，那么可以选择INNODB
  • MYISAM
    • 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完成性，并发性要求不是很高，那可以选用MYISAM
  • MEMORY
    • 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保证数据的安全性

索引

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

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

我们平常所说的索引，如果没有特别指明，都是B+树结构组织的索引

索引结构-B+Tree

二叉树
1724915267122.png
二叉树缺点：顺序插入时，会形成一个链表, 查询性能大大降低，大数量量情况下，层级较深，检索速度慢
红黑树：大数量量情况下，层级较深，检索速度慢

B-Tree(多路平衡查找树)
以一颗最大度数为5（5阶）的b-tree为例（每个节点最多存储4个key,5个指针）
1724916481872.png
每个叶子下存放的是数据
B-Tree动画演示地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+Tree
以一颗最大度数为4（4阶，3个key，4个指针）为例子
1724916936101.png
相对于B-Tree:

  • 所有的数据都会出现在叶子节点
  • 叶子节点形成一个单向的链表
MySQL的B+Tree

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

HASH索引

哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表中
如果两个（或多个）键值，映射到一个相同的槽位撒花上，他们就产生了Hash冲突，可以通过链表解决
1724917406052.png
Hash索引的特点：

  • Hash索引只能用于对等比较（=， in）, 不支持范围查询（between， >< > , …）
  • 无法利用索引完成排序
  • 查询效率高，通常只需要一遍检索就可以，效率要高于B+Tree (如果有冲突是链表结构，那就不是一遍检索)

存储引擎支持：

  • 在MySQL中，支持HASH索引的是MEMORY引擎，而INNDB中具有自适应Hash功能，Hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
问题：为什么INNODB存储引擎选择使用B+Tree索引结构
  • 相对于二叉树，层级更少，搜索效率高
  • 对于B-Tree, 无论是叶子结点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要保存同样大量数据，只能增加数的高度，导致性能降低
索引的分类
分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
普通索引快递定位特定数据可以有多个
全文索引全文索引找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEST
在INNODB中，根据索引的存储形式，可以分为以下两种
分类含义特点
聚集索引(Clustered Index)将数据存储与索引放在一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引（Secondary Index）(非聚集索引)将数据与索引分开存储，索引结构的叶子节点关联的是对应的组件可以存在多个

聚集索引选取规则：

  • 如果存在主键，注解索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIOUQ）索引作为聚集索引
  • 如果没有主键，也没有唯一索引，那INNODB会自动生成一个rowId作为隐藏的聚集索引

1724918799923.png
1724918786856.png

问题：以下SQL语句，那个执行效率高？ 为什么？

SELECT * FROM user WHERE id = 10;

SELECT * FROM user WHERE name = "A";

id为主键索引， name 为普通索引

答：
第一条比较快，因为id为聚集索引，可以直接根据id定位到数据
第二条name是普通索引，需要先定位到id值，在回表查询，定位到行值

问题：INNODB主键索引的B+Tree高度为多高？
答：
假设，一行数据大小为1K，一页中可以存储16行这样的数据，InnoDB的指针占用6个字节，主键及时为bigint，占用字节数为8
树高度为2：n*8+(n+1)*6 = 16 * 1024 算出n约1170 有1171个指针
1171 * 16 = 18736
高度为3：
1171 * 16 * 1171 = 21939856 （如果有2000万条数据，那么树的机构才3层）

索引的语法

创建索引

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_' (_匹配一个字符)

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

# 开启MySQL慢查询日志开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行超过两秒，就会视为慢查询日志，记录慢查询日志
long_query_time=2

配置完成之后，通过一下指定重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
:::info
/var/lib/mysql/localhost-slow.log
:::
1724998528273.png
SQL性能分析
profile详情
:::info
show profile能够在SQL优化时帮助我们了解时间都消耗到哪里去了。通过have_profiling参数，能都看到当前MySQL是否支持profile操作。
SELECT @@have_profiling;
:::
image.png
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling

SET profiling=1;

image.png
image.png
执行一系列的业务SQL的操作，然后用过如下指令查看指令的执行耗时；

# 查看每一条SQL的耗时基本情况
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query_id;

# 查看指定query_id的SQL语句的CPU使用情况
show profile cpu for query query_id;

1724999231599.pngimage.png
image.png
1724999243231.png

explain执行计划

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

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

image.png

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

image.png
image.png

  • id不同

image.png

  • select_type

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

  • type

    • 连接类型，性能由好到差的连接类型为NULL，system， const，eq_ref，ref，range，index，all。
      • null 不访问任何表，会出现null 如 EXPLAIN SELECT ‘A’;
      • system 访问一张系统表，才会出现
      • const 根据主键或者唯一索引，会出现const
      • ref 使用非唯一索引
      • index 用到了索引，但是会对索引进行扫描，遍历整个索引数
      • all 全表扫描

  • possible_key

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

  • key

    • 实际用到的索引。如果为null 则没有用到索引

  • ken_len

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

  • rows

    • MySQL认为必须执行查询的行数，在INNODB的表中，是一个预估值，可能并不是准确的

  • filtered

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

  • Extra

    • 额外信息，前面没有展示的，在这里展示
      :::info
      Extra 列的含义

  • Using where

    • 含义：表示在索引扫描过程中应用了过滤条件。
    • 说明：MySQL在读取索引条目时就进行了WHERE子句中的条件过滤，而不是先读取所有索引条目再过滤。

  • Using index

    • 含义：表示使用了覆盖索引（即索引包含了所有需要的列）。
    • 说明：查询所需的全部列都在索引中，因此不需要回表查询（即不需要访问实际的数据行）。

  • Using temporary

    • 含义：表示使用了临时表。
    • 说明：MySQL需要使用临时表来存储中间结果。这通常发生在需要对结果集进行排序或去重的情况下，但没有合适的索引可用。

  • Using filesort

    • 含义：表示需要进行排序操作。
    • 说明：MySQL需要进行额外的排序操作。这通常发生在ORDER BY或DISTINCT之后，如果没有合适的索引支持排序需求。

  • Using join buffer (Block Nested Loop)

    • 含义：表示MySQL使用了Block Nested Loop算法来处理JOIN操作。
    • 说明：这通常发生在没有合适的索引来支持JOIN操作时。

  • Impossible WHERE noticed after reading const tables

    • 含义：表示MySQL发现WHERE子句中的条件是不可能满足的。
    • 说明：查询将不会返回任何结果。

  • Select tables optimized away

    • 含义：表示MySQL优化掉了查询中的某些表。
    • 说明：这通常发生在查询中包含常量表时，MySQL可以直接从常量表中获取结果。

  • No matching row in const table

    • 含义：表示在查询中涉及到的某个常量表中没有匹配的行。
    • 说明：查询将不会返回任何结果。

  • Using index condition

    • 含义：表示MySQL使用了索引条件推送（Index Condition Pushdown, ICP）。
    • 说明：这是一种优化技术，允许MySQL在索引扫描期间就应用WHERE子句中的条件，从而减少需要读取的数据量。

  • NULL

    • 含义：表示没有额外的操作。存在回表查询
    • 说明：查询已经通过索引或其他方式得到了优化，没有需要报告的额外操作。
      :::
索引的使用

全表扫描 vs 索引扫描：如果mysql认为索引的选择性不高（即有很多重复值），那么即使存在索引，MySQL也可能决定进行全表扫描，因为它认为这样做效率更高。

最左前缀法则

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

  • 索引类型

image.png
执行语句：

# 三个索引都使用到了
1. EXPLAIN SELECT * FROM system_menu WHERE parent_id = 0 AND path = '/watermark' AND `name` = 'WATERMAEK';
# parent_id 和 path 索引都使用到了
2. EXPLAIN SELECT * FROM system_menu WHERE parent_id = 0 AND path = '/watermark';
# parent_id 索引使用到 name 索引失效，因为跳过了path列。
3. EXPLAIN SELECT * FROM system_menu WHERE parent_id = 0 AND `name` = 'WATERMAEK';
# 使用到了索引
4. EXPLAIN SELECT * FROM system_menu WHERE parent_id = 0;
# 索引失效，全表扫描，因为最左边的索引不在
5. EXPLAIN SELECT * FROM system_menu WHERE path = '/watermark' AND `name` = 'WATERMAEK';
# 三个索引都使用到了，MySQL会判断三个索引列是否都存在，如果都存在于位置无关。
6. EXPLAIN SELECT * FROM system_menu WHERE path = '/watermark' AND `name` = 'WATERMAEK' AND parent_id = 0;

2和6 索引的使用与顺序无关，数据库的查询优化器会自动帮助我们优化我们的sql，看哪个执行的效率最高

索引使用情况： 从上到下
image.png
image.png
image.png
image.png
image.png
image.png

范围查询

联合索引中，出现了范围查询（>,<）范围查询右侧的列索引失效
例子：

# parent_id后面的索引全部失效
EXPLAIN SELECT * FROM system_menu WHERE parent_id > 0 AND path = '/watermark'  AND `name` = 'WATERMAEK';

# 联合索引正常使用，意见： 如果在业务允许的话，尽量使用>= , <= .
EXPLAIN SELECT * FROM system_menu WHERE parent_id >= 0 AND path = '/watermark' AND `name` = 'WATERMAEK';

image.png
image.png

索引列运算

不要在索引列上进行运算操作，索引将会失效
例子：
image.png
匹配后面两个字符为EK，使用运算，变成了全表扫描
image.png

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效（涉及到隐私转换）
image.png
image.png

模糊匹配

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效
image.png
image.png
image.png

or连接的条件

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

数据分布影响

如果MySQL评估使用索引会比全表扫描慢，则不会使用索引。
**对索引使用is null or is not null **

image.png

对索引使用is not 和 is not null 不一定使用is not 就会使用索引，使用is not null 就索引失效，取决去当前数据库表中字段的数据分布情况，

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一下人为的提示来达到优化操作的目的
use index: 告诉数据库使用这个索引 (建议MYSQL使用，具体是否使用，MYSQL决定)

EXPLAIN SELECT * FROM table_name use index(index_name) WHERE col = 'xxxx';

ignore index: 告诉数据库忽略这个索引

EXPLAIN SELECT * FROM table_name ignore index(index_name) WHERE col = 'xxxx';

force index: 告诉数据库强制使用这个索引

EXPLAIN SELECT * FROM table_name force index(index_name) WHERE col = 'xxxx';
覆盖索引

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

1725009146424.png

问题：
一张表：有4个字段（id, username, passwrod, status）由于数据量大，需要对以下SQL语句进行优化，该如果优化方案最佳？

  1. SELECT id, username, password FROM tb_user WHERE username = ‘xxxx’;
    1. 针对于username 和 password 创建联合索引， 不需要回表查询。
前缀索引

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

CREATE INDEX idx_xxx ON table_name(column(n))

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

SELECT COUNT(DISTINCT email) / COUNT(*) FROM table_user;
SELECT COUNT(DISTINCT SUBSTRING(email,1,5)) / COUNT(*) FROM table_user;

image.png

单列索引和联合索引
  • 单列索引: 即一个索引包含了单个列
  • 联合索引：即一个索引包含了多个列

image.png
再业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而给单列索引
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。
image.png

索引设计原则
  • 针对于数据量较大（上百万）且查询比较频繁的表建立索引
  • 针对于常作为查询条件（WHERE）排序（ORDER BY）分组（ORDER BY）操作的字段建立索引
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  • 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  • 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率
  • 如果索引列不能存储NULL值，请再创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效地用于查询

SQL语句优化

插入数据

insert优化

  • 批量插入
INSERT INTO table_name VALUES(zz,xx,yy),(zz,xx,yy); 最多500到1000条最合适
  • 手动事务提交 （mysql事务自动提交，说明插入一条就提交一次事务）
START TRANSACTION;
INSERT INTO table_name VALUES(XX,TT),(NN,MM);
INSERT INTO table_name VALUES(XX,TT),(NN,MM);
INSERT INTO table_name VALUES(XX,TT),(NN,MM);
INSERT INTO table_name VALUES(XX,TT),(NN,MM);
COMMIT;
  • 主键顺序插入
  • 大批量插入数据

如果需要一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入，如下：
image.png
主键顺序插入性能要高于乱序插入

主键优化
  • 数据组织顺序
    • 在INNODB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表
  • 页分裂
    • 页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据多大，会行溢出）根据主键排列

image.png
主键乱序插入，50 原来应该在第一页或者第二页头部，但是现在已经都满了，那么会进行一个操作将第一页百分50的地方，移动到第三页，把新插入的放在移动后的第三页后面，
然后将第一页的指向更换到第三页，新增第三页和第二页的双向指向。
image.png
image.png
image.png

  • 页合并
    • 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标识（flaged）为删除并且他的空间变得允许被其他记录使用
    • 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的的页（前后页）看看是否可以将两个页合并以优化空间

image.png
image.png
image.png
:::info
MERGE_THRESHOLD: 合并页的阈值，可以自己设置，再创建表或者索引时指定
:::

  • 主键设计原则
    • 满足业务需求的情况下，尽量降低主键的长度
    • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
    • 尽量不要使用UUID做主键或者是其他自然主键，如身份证
    • 业务操作时，避免对主键的修改
ORDER BY 优化
  • using filesort: 通过表的索引或者全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序的结构都叫FileSort排序
  • using index: 通过有序索引顺序扫描直接返回有序数据，这种情况为using index 不需要额外的排序，操作效率高。

因为user_id 和 user_mobile 没有索引
image.png
创建完索引后

CREATE INDEX user_id_mobile_index ON test_user(user_id, user_mobile);

image.png

Backward index scan; 因为我们是倒叙，所以这是反向扫描索引。

使用第二个字段排序，违背最左前缀法则
image.png
第一个ASC 第二个DESC
image.png
解决办法，针对这个创建索引

CREATE INDEX user_id_mobile_index ON test_user(user_id ASC, user_mobile DESC);

以上前提，都在覆盖索引的情况下使用
如果使用SELECT * , 则还是useing filesort;
image.png
总结：

  • 根据排序字段建立索引，多字段排序时，也遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC， DESC）
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓冲区的大小（sort_buffer_size） 默认256K（如果256K满了，会涉及到使用磁盘文件排序，效率又降低）
GROUP BY优化

using temporary： 创建临时表
image.png
创建一个联合索引，查询结果为using index
image.png
根据user_id 进行分组（用到了索引，也用到了临时表）
image.png
满足最左前缀法则，直接走索引
image.png
在WHERE里面使用最左边的索引，也会生效
image.png

limit 优化

问题：一个常见又非常头疼的问题就是limit 200000, 10，此时需要MySQL排序前200010记录，仅仅返回200000-200010的记录，其他记录丢弃，查询排序的代价非常大
1725246991182.png
优化思路：
一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化

EXPLAIN SELECT * FROM table_name AS a, (SELECT id ORDER BY id limit 200000, 10) AS b WHERE a.id = b.id;
count优化
  • MyISAM引擎把一个表的总行数据存在了磁盘中，因此执行count(*)的时候会直接返回这个数，效率很高（前提： 没有WHERE语句）
  • InnoDB引擎比较麻烦，它执行count(*)的时候，需要吧数据一行一行的从引擎读取出来，然后累积计数（有没有WHERE一样）

优化思路：自己计数

count几种用法
  • count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累计值加1，否则不加，最后返回累计值
  • 用法
    • count(*)
      • InnoDB引擎并不会把全部字段取出来，而且专门做了优化，不取值，服务层直接按按行进行累加
    • count(主键)
      • InnoDB引擎会遍历整张表，把每一行的主键id都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为null）
    • count(字段) 如果字段是空，则不会加入累计 count(null) 为0；
      • 没有not null 约束，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层需要判断是否为null，不为null，计数累加
      • 有not null 约束，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接进行累加
    • count(1)
      • InnoDB引擎会遍历整张表但不取值，服务层对于返回的每一行，放一个数字（1）进去，直接按行进行累加（放进去的数字与传入的数字一致）

按效率排行，count() = count(1) >count(id) > count(主键) 所以尽量使用count()

UPDATE优化

InnoDB的行锁是针对于对索引加的锁，不是针对记录加的锁，并且该索引不是失效，否则会从行锁升级成表锁。

Eugene03
关注
  • 20
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 高级（进阶学习）
qq_46381044的博客
07-05 1514
视图就是将某个查询语句存储在数据中，并为其命名，视图中并不存储数据，数据还是在基本表中存储。定义视图使用视图删除视图存储过程就是把一段处理逻辑存入到数据库中，使用是就由 JDBC 调用即可。调用存储过程可以减少应用程序和数据库交互次数，在数据库内部执行，执行效率高。存储事先需要定义，有三种参数类型：in 入参（接收调用者传入的数据）out 返回（向调用者返回数据）inout （既可以接收调用者传入的数据，也可以向调用者返回数据）函数是一个特殊的存储过程。存储过程不仅有输入参数，还有输出参数，但是没有返回值，
mysql进阶学习
10-15
MySQL 进阶学习 MySQL 进阶学习旨在帮助开发者更好地掌握 SQL 语言，提高数据库管理和开发能力。本资源摘要将涵盖 MySQL 进阶学习的主要知识点，包括数据库设计、SQL 优化、查询分析、索引优化、视图和存储过程等。...
MySQL进阶操作
c_study__c的博客
11-11 950
MySQL进阶操作（修整）
Mysql进阶详解
qq_46631261的博客
08-01 2722
mysql锁机制，mysql索引，事务，并发，MVCC，ACID
MySQL进阶学习
wwx1255413402的博客
07-05 187
快来学习MySQL进阶！
MySQL——进阶篇
qq_44689664的博客
12-25 1724
MySQL进阶篇、存储引擎、索引、SQL优化、视图、存储过程、触发器、锁、InnoDB引擎、MySQL管理
mysql进阶+pdf_mysql进阶学习
weixin_35663151的博客
01-28 1301
1，sql小例子SELECT c.coupon_name,c.coupon_id,c.coupon_value,c.camp_condition,c.issue_amount,c.remain_amount,s.store_name,c.local_coupon_condition,from_unixtime(c.start_time,'%Y-%m-%d %H:%i:%s') as startt...
mysql进阶知识_mysql进阶学习一之知识点总结
weixin_42517963的博客
02-08 306
环境：centos7+mysql5.71 mysql5.7的安装这里使用yum安装，参考这个老哥的博客 ，反正我安装的时候一言难尽，重装了好几次，哎，平常自己玩的都是用的docker...装好了记得根据上面的博客去初始化密码哦！2mysql的逻辑架构mysql底层就是经过下面这几部分，我们要知道，执行引擎才是真正干活的；可以发现这里运行了缓存，首先在缓存中取数据，缓存中能命中就直接从缓存中去，...
MySQL进阶 —— 超详细操作演示！！！（上）
weixin_43412762的博客
09-09 2716
大家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。存储引擎就是存储数据建立索引更新/查询数据等技术的实现方式。存储引擎是基于表的。
mysql 进阶学习（一）
YAO_IT的博客
08-11 592
MySQL最重要的特性是：存储引擎架构，该架构将查询处理和其他系统任务和数据的存储、提取 相分离 。所以根据业务特点选择合适的数据存储方式（即选择存储引擎的类型） 1 MySQL架构分为三层 1.1 连接层 该层用于处理客户端的连接请求和客户端的权限认证。 每个客户端都会在MySQL中拥有一个线程，客户端的数据操作将在此线程中执行处理。 MySQL负责缓存线程，不需要客户都安操心 ...
mysql 进阶到高级_Mysql 高级进阶
weixin_42459611的博客
02-18 192
Mysql-高级进阶Mysql-索引索引类型作用普通索引加速查找主键索引加速查找+唯一+不能为空唯一索引加速查找+唯一联合索引联合主键+联合唯一+联合普通概览说明:直接创佳索引会创建额外的临时文件，以某种文件存储，每次查询的时候都会先查这个表索引种类:Hash索引，btree索引，覆盖索引hash索引：单值快，范围查找慢btree索引创建和删除索引覆盖索引索引合并:1.给表中的id 和 email...
Mysql 进阶学习.zip
02-22
性能优化是MySQL进阶学习中的重要部分，包括索引策略、查询优化、存储引擎的选择（如InnoDB和MyISAM）以及配置参数调整。理解EXPLAIN命令，以分析查询执行计划，是找出性能瓶颈的关键。 备份和恢复策略也是不容忽视...
MySQL进阶学习-笔记整理
11-08
1、性能优化 1.1、Mysql索引底层数据结构与算法 1.2、Explain工具 1.3、索引下推 1.4、Trace工具 1.5、索引优化最佳实践 2、事务隔离 2.1、事务隔离级别及琐机制 2.2、MVCC多版本并发控制机制 3、Mysql日志 3.1、...
MySQL进阶篇学习笔记
05-14
黑马MySQL课程总结的学习笔记
【MySQL进阶学习】视图
12-14
为什么要有视图？ 先看我们先到遇到的情况。 视图是从一个或多个表（或视图）查询出的数据形成的虚拟表。如果我们有一个复杂的查询，就可以把这个查询做成视图。视图保存的是SQL语句，不保存查询结果。...
华为云 GaussDB 数据库和 MySQL 数据库的区别
2401_86488984的博客
08-28 991
深入搜索。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 1130
MySQL——子查询（5）带比较运算符的子查询
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 1038
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
阿里龙晰系统上将yum安装的myql_8.0.36升级到mysql_8.4.0的过程
最新发布
老杜的博客
08-29 909
本章内容介绍了在阿里龙晰系统上修复 Oracle MySQL 安全漏洞CVE-2024-21176、CVE-2024-21177、CVE-2024-21179的过程。将Mysql从8.0.36升级到8.4.0
mysql进阶学习路线
06-01
当你已经掌握了MySQL的基础知识，可以考虑进行进阶学习。以下是一些建议的学习路线： 1. 学习MySQL的性能优化：MySQL性能优化是非常重要的，你可以学习MySQL的索引优化、查询优化、配置优化等。可以通过阅读相关书籍或者博客文章来学习。 2. 学习MySQL的高可用性：高可用性是数据库系统的重要特性，MySQL也提供了很多高可用性的解决方案，如主从复制、多主复制、分区表等。你可以学习这些技术，并且了解如何应对数据库故障。 3. 学习MySQL的安全性：数据库的安全性也是非常重要的，你可以学习如何保护你的数据库免受攻击，如设置安全的密码、配置SSL/TLS、限制访问等。 4. 学习MySQL的分布式：当你需要处理大规模数据时，分布式是一个非常重要的解决方案。你可以学习如何使用MySQL Cluster或者MySQL Sharding来构建分布式系统。 5. 学习MySQL的其他高级特性：MySQL还有很多其他高级特性，如事件、存储过程、触发器、视图等。你可以学习这些特性，以便更好地利用MySQL。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值