Wow56 个人知识库

# MySQL 进阶

# 参考文档

// TODO

# 存储引擎

常见搜索引擎有 InnoDB(5.5 之后默认)、MyIsam、Memory...

  • 建表时，指定存储引擎

    CREATE TABLE 表名 (
    字段1 字段1类型 [COMMENT 注释]，
    字段2 字段2类型 [COMMENT 注释],
    ...
) ENGINE = INNODB [COMMENT 表注释]
    1
    2
    3
    4
    5

  • 查看当前数据库支持的所有搜索引擎 SHOW ENGINES;

# InnoDB

MySQL 5.5 之后的默认存储引擎。

特点：

  • 支持事务，DML 操作遵循 ACID 模型。
  • 行级锁，提高并发访问性能。
  • 支持外键 FOREIGN KEY。

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

idb2sdi xxx.ibd

# 逻辑存储结构

  • 表空间 TableSpace(.idb 文件)
  • 段 Segment
  • 区 Extent
  • 页 Page
  • 行 Row

InnoDB 存储结构

# InnoDB主键索引树高度

可以先去看索引原理中的树结构图。

InnoDB 指针占用 6 字节，一页大小固定为 16k，如果主键使用 bigint 类型（8 字节），一行数据假设为 1k，一页最多就可以存储 16 条数据，则：

  • 若高度为 2：

    n*8 + (n+1)*6 = 16*1024 (一页大小)，n 为 key 数，可得 n = 1171，那么一页最多存储 1171 Key（page），1171*16（每一页数据量）=18736，可知，高度为 2，最多存放 18736 条数据。

  • 若高度为 3：

    1171 * 1171 * 16 = 21939856，大概可以存放 2000w 左右数据。

# MyISAM

MySQL 老版的默认存储引擎。

特点：

  • 表锁。
  • 访问速度快。

文件：.MYD（data）、.MYI(index) 和 .sdi（表结构）。

# Memory

Memory 引擎的表数据是存储在内存中的，所以只用做临时表或缓存。

特点：

  • hash 索引（default）。
  • 访问速度极快。

文件：xxx.sdi，存储表结构信息。

# 常见引擎对比

常见引擎对比

# 索引

MySQL 的索引共有以下几类：

  • 主键索引 PRIMARY
  • 唯一索引 UNIQUE
  • 普通索引
  • 全文索引 FULLTEXT
    • 全文索引搜索的是文本中的关键词，不是比较索引中的值。

相关语法：

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

# 最左前缀原则

最左前缀原则，或最左匹配原则，最左前缀匹配原则 指的是同一个东西。

数据库索引分为单键索引和复合索引，单键索引的命中无需多说。最左前缀原则是指在复合索引中的命中规则。

假如有一个索引 a_1_b_1_c_1，当查询 a、a+b、a+b+c 都可以命中该索引，但是 b、b+c、c 就不能命中索引。

另外，a+c 也会命中索引，它会从所有 a 相同的数据中查询等于 c 的数据。

# 范围查询

在复合索引中，如果出现范围查询（>,<），范围查询右侧列索引失效。这时候建议使用 >= or <=。

# 索引失效场景

  • 优化器认为全表扫描更快。
  • 字符串类型字段使用时，没有加 ''，索引会失效。
  • 头部模糊匹配（尾部模糊不会失效），索引失效。
  • or 连接的两侧字段都要有对应索引，否则索引失效。
  • 在索引列进行运算时，索引会失效。

# SQL 提示

  • use index explain select * from table_name use index(index_name) where x = y // 建议
  • ignore index explain select * from table_name ignore index(index_name) where x = y
  • force index explain select * from table_name force index(index_name) where x = y

# 覆盖索引

回表，联合索引涉及到的字段并没有完全包含所要查询的字段，这时候就需要先在辅助索引（联合索引）中查找主数据的 id，然后拿 id 去聚簇索引中查找辅助索引中不存在的字段值。

要想更好的了解回表，需要先了解 InnoDB 的聚簇索引和辅助索引的概念。

尽量使用覆盖索引，即查询返回的列在该索引中都可以找到，减少使用 SELECT *，本质是减少回表操作。

# 前缀索引

当某个字段类型是字符串（varchar、text 等）时，如果这个字段中存放的字符串长度很大，被索引时，索引也就变的很大，查询时，就会浪费大量的磁盘 IO，进而影响查询效率。这时，可以取字符串的一部分前缀作为索引，这样就可以很大程度的解约索引空间，从而提高索引效率。

语法：CREATE INDEX index_name ON table_name(field_name(n)) // n 代表截取值的前 n 位

在评估 n 的取值时，可以根据索引的选择性来判断。

选择性：一列中数据去重后/这一列总数 = 选择性。例如：

  • select count(distinct email) / count(*) from user;
  • select count(distinct substring(email,1,5)) / count(*) from user; // 取 email 的前 5 位后的选择性值

# 性能分析

# SQL 执行频率

通过执行频率可以判断某个数据库重点是以增删改查中哪类操作为主，来判断是否需要进行优化。

语法：SHOW GLOBAL/SESSION STATUS LIKE 'Com_______' // 7 个下划线

sql execution frequency

# 慢查询日志

  • 查看慢查询日志配置 show variables likes 'slow_query_log';

MySQL 慢查询日志默认关闭，需要在配置文件（/etc/my.cnf）中配置：

# 开启
slow_query_log=1;
# 配置慢查询日志阈值，这里是超过 2s 会被记录，默认为 10s
long_query_time=2;
1
2
3
4

日志默认存放位置 /var/lib/mysql/localhost-slow.log

# profile

使用前通过 have_profiling 参数查看当前数据库是否支持 profile，SELECT @@have_profiling。

  • 通过 SELECT @@profiling 查看是否开启。
  • MySQL 的 profile 默认是关闭的，可以通过 SET profiling = 1; 来开启 profiling。

查看详情：

  • 通过 show profiles 可以在优化 SQL 时，看到时间具体的耗费详情。
  • 查看某一条 query 的具体时间消耗详情 show profile for query query_id;。
  • 查看某一条 query 的 SQL 语句的 CPU 使用情况 show profile cpu for query query_id;。

# explain 执行计划

直接在查询语句前面加上 EXPLAIN 或 DESC 就可以查看该 query 对应的执行计划分析了，例如：

explain SELECT * from user;

explain 示例

# explain 各字段含义

点开查看 
# 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
# 
# 表示返回结果的行数占需读取行数的百分比，值越大越好。

# Extra 
# 
#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# SQL 优化

# 插入数据

  • 多条 insert 可以改成 insert 批量插入。

  • 手动提交事务，可以所有数据插入完成再提交事务（默认每条 SQL 自动提交事务）。

  • 主键按照顺序插入。

  • 大批量的插入数据可以使用 load 加载 CSV 文件的数据。

    # 连接客户端时，使用 --local-infile 参数
mysql --local-infile -u root -p
# 开启从本地加载文件导入数据的开关，设置全局参数 local_infile = 1;
set global local_infile = 1;
# load 命令加载文件数据
# fields terminated by ',' 指定字段的分隔符
# lines terminaterd by '\n' 指定行分隔符
load data local infile '/home/sql1.log' into table table_name fields terminated by ',' lines terminaterd by '\n';
    1
    2
    3
    4
    5
    6
    7
    8

# 主键优化

  • 主键需要顺序插入
    • 如果乱序可能会发生页分裂
  • 尽量降低主键长度，因为主键数据也是会占用空间的。

页分裂、页合并现象。判断阈值默认为 50%

# orderBy 优化

  • using filesort，通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成操作，所有不是通过索引直接返回排序结果的都是 filesort 排序。
  • using index，通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高。

大数量排序可以适当增大排序缓冲区大小：sort_buffer_size（默认 256kb）

# groupBy 优化

索引对于 group by 的影响。尽量使用索引覆盖要操作的字段。

# limit 优化

select * from user limit 50000,10 大分页问题。

主键索引+子查询方式优化 select u.* from user u, (select id from user limit 50000,10) t where u.id = t.id;，将子查询看做临时表，然后联查。

# count 优化

select count(*) from user

  • count(主键)，InnoDB 遍历整张表，把每行的 id 取出来，然后累加。
  • count(字段)，先判断该字段是否有 not null 约束，来判断直接累加还是去判断每行是否为 null 再累加。这里只会统计不为 NULL 的数据个数。
  • count(1)，遍历整张表，不取值，每遍历一行数据，返回一个 1，最后将累加。注意这里是加返回数字的个数，并不是累加返回的数字1，这里 0 和 1 的结果一样的。
  • count(*)，InnoDB 不取值，直接按行累加。

效率由高到低：count(*) ≈ count(1) > count(主键) > count(字段)

# update 优化

update 更新数据时，要根据 索引字段 作为条件，这样的话，InnoDB 会加 行锁。但是如果不加索引，行锁会升级为表锁，这时其他的 update 操作会阻塞。

  • update user set name = "xiaoming" where id = 1 // 这里只会锁住 id = 1 这行数据
  • update user set name = "xiaoming" where age = 18 // 如果 age 没有索引，是表锁，age 有索引，只会是行锁

# 索引原理

索引就是帮助快速搜索数据的一种数据结构，每种搜索引擎的实现都不太一样，主要常见的索引有以下几种：

  • B+Tree 索引，最常用的索引实现方式，大部分引擎都支持。
  • Hash 索引，通过哈希表实现，只有精确匹配索引列的时候有效，不支持范围查询。
  • Full-text 全文索引，是一种通过建立倒排索引，快速匹配文档的方式。例如 Lucene、Solr、Elasticsearch(基于 Lucene)
  • ...
索引类型 InnoDB MyISAM Memory
B+Tree Y Y Y
Hash - - Y
Full-text 5.6 以后支持 Y -

MySQL 的索引使用 B+Tree 原理，在 B+Tree 基础上改进了一些。

MySQL 索引原理

# 聚簇索引

InnoDB 的索引分为两类，聚簇索引和辅助索引（二级索引）。

从 B+Tree 的原理中可以看出 InnoDB 中索引是和数据在一起的，但实际上并不是所有的索引都和数据存放在一起（硬盘中）。InnoDB 的表数据中只有主键索引，其他索引单独存在（不包含数据）。当一张表被创建时，InnoDB 会选择设置的主键那一列作为主键索引来组织数据，如果没有设置主键索引，那么它会选择一列它认为值唯一的一列作为主键索引来组织数据，如果没有这样的一列数据，那它会自己生成一列唯一数据隐藏起来追加在表的最后一列作为主键来组织数据。

除了主键索引外的所有索引都是辅助索引，也叫二级索引。辅助索引的 B+Tree 的叶子结点中存放的是数据的主键值，聚簇索引中的叶子结点存放的是原数据（row）。

聚簇索引是 InnoDB 中的概念，在 MyIsam 中，数据（在硬盘中）和索引是分别存放的，MyIsam 的索引叶子结点存放的都是主键值。

# 存储对象

# 视图

# 创建视图

语法：CREATE [OR REPLACE] VIEW 视图名称 AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

例如 create or replace view user_view as select id, name from user where age > 10;

检查选项

在对视图进行增删改查的时候，实际操作的是 基表。

假如一个视图是这么创建的 create view user_view as select id, name from user where age > 10;，在对视图的数据进行操作的时候，如果你执行了 insert into user_view values(5, "Daming", 12)，插入一个 12 岁的数据，显然不符合视图的限制。但是这里却能插入成功，只是在视图中不显示。如果想要限制插入不符合视图的数据，可以这样创建视图 create view user_view as select id, name from user where age > 10 with check option;，这时候再插入 12 岁的数据就会直接报错。

这里的参数 with check option 等同于 with cascaded check option，cascaded 是默认值。另外还有一个值 local，即 with local check option。

因为视图是可以基于另一个视图去创建的，当视图 2 的条件和视图 1 的条件不一样的时候，使用 with cascaded check option 可以在插入数据的时候，检查视图 2 的同时也会去检查是否满足视图 1。如果视图 3 又基于视图 2 创建，但是没有添加 with check option 参数，那么插入的时候，不会检查当前视图 3 的条件，但是会向上检查。

# 查询视图

查看创建视图语句：SHOW CREATE VIEW 视图名称，查询数据的话，和查询表数据是一样的。

# 修改视图

两种方式：

  • CREATE OR REPLACE VIEW 视图名称 AS SELECT 语句 // 利用了 OR REPLACE 参数
  • ALTER VIEW 视图名称 AS SELECT 语句

# 删除视图

DROP VIEW [IF EXISTS] 视图名称

# 存储过程

// TODO

# 存储函数

// TODO

# 触发器

// TODO

# 锁

# 全局锁

锁定数据库中的所有表

使用场景：全库数据备份中使用全局锁，对所有表锁定，为了获取一致性视图，保证数据的完整性。

flush tables with read lock; 加锁 -> mysqldump -uroot -p12345 user > user.sql 导出数据 -> unlock tables; 释放锁

可以使用 --single-transaction 参数实现不加锁的一致性数据备份：mysqldump --single-transaction -uroot -p12345 user > user.sql

# 表级锁

锁住整张表

表级锁主要分为 3 类：表锁、元数据锁（meta data lock）和意向锁。

# 表锁

表锁分为：

  • 表共享读锁（read lock），加锁后，当前客户端和其他客户端都只能读，不能写。
  • 表独占写锁（write lock），加锁后，当前客户端可读可写，其他客户端不可读写。

语法：

  • 加锁，lock tables 表名... read/write
  • 释放锁，unlock tables / 客户端断开链接

# 元数据锁

元数据锁，meta data lock，即 MDL。MYSQL 5.5 引入。

MDL 加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。

当对一张表进行增删改查的时候，加 MDL 读锁（共享）；当对表结构进行变更操作的时候，加 MDL 写锁（排他）。

# 行级锁

锁住单行数据

# InnoDB 详解

# 讨论区

由于评论过多会影响页面最下方的导航，故将评论区做默认折叠处理。

点击查看评论区内容，渴望您的宝贵建议~
Last Updated: 8/22/2023, 2:04:47 PM

← MySQL 安装配置 →