🌟思维风暴
Author Image
༒过渡忙碌使你落后

© 2024 tkstorm.com

粤ICP备14088510号

《Mysql45讲》（二）实践相关(索引、日志、InnoDB存储引擎等)

2019/11/18 共 4900 字 14 分钟阅读
BOOKS • DATABASE mysql 《mysql45讲》
AI 摘要: 这篇文章主要介绍了MySQL索引、存储过程和函数的区别，以及一些优化技巧，包括使用临时表、使用join连接、分区表等。其中涉及到普通索引、唯一索引、InnoDB引擎架构、查询和更新过程、ChangeBuffer的Merge与RedoLog的WAL等内容。此外，还提到了使用order by rand()时的流程、临时表重命名、insert锁的问题和授权操作的flush privileges问题。最后还介绍了InnoDB和Memory引擎的选择、最快复制表的方法以及自增主键的连续性问题和应对措施。

实践内容：索引

1. 索引篇

1.1. 普通索引、唯一索引

1.1.1. InnoDB引擎架构

1.1.2. 查询过程

InnoDB中的索引是采用的B+树结构，包含主键索引(聚簇索引)、非主键索引(普通索引、唯一索引、关联索引)，普通索引又具备最左前缀规则。

InnoDB中按数据页读写，每个数据页默认16KB，一个数据页包含多个记录，因此普通索引和唯一索引差异基本很低：

唯一索引，找到数据记录后，就停止继续往后搜索，而普通索引会继续找，直至第一个找到和条件不匹配的。

1.1.3. 更新过程

  • change buffer: 数据页在内存，找到记录更改内存；数据页没有在内存，先更新到change buffer，下次查询/定期进行内存Merge，再刷入磁盘！
  • change buffer占用buffer pool的内存空间，可以通过innodb_change_buffer_max_size设置
  • change buffer适用写多读少的场景，因为随机IO写操作被批量merge了，让才进行磁盘IO，若写入后，立马查询的场景，这样随机IO没有降低，反倒多了change buffer开销

唯一索引不能使用change buffer，因为唯一索引必须要和数据页中的数据校对，因此数据页一定需要读入内存（因此也没有了change buffer的述求了），因此如果是唯一索引，会导致更大的磁盘IO开销

综合看来，普通索引足够适用，特别是机械硬盘时代，对大表更新效果还是较为明显的（调大change buffer容量）

1.1.4. 对比带changebuffer的查询和更新

  • 更新过程：
    1. 更新页在内存中: 直接更新
    2. 更新页不在内存中: 写changebuffer
    3. 将上述两处操作写redolog(ib_log_fileX)
    4. 后台异步写系统表空间(ibdata1)和数据表空间(t.ibd)
  • 读取过程：
    1. 读取页在内存中：直接读取
    2. 读取页不在内存中：读入页数据到内存中，然后应用changebuffer中的操作日志，生成一个新版本返回

1.1.5. ChangeBuffer的Merge与RedoLog的WAL

change buffer中有Merge操作，redo log中有WAL机制，都是降低随机磁盘IO，但Merge是降低了读IO(不用立马随机IO读取数据页)，WAL是降低了日志写IO（将多个redolog转成顺序写）

Merge的流程：

  1. 从磁盘读入数据页到内存（老版本）
  2. 从ChangeBuffer找到数据页的Change记录，依次Merge得到新版本数据（产生内存脏页，后续被Mysql刷回磁盘）
  3. 写入redolog，包含了数据的变更和ChangeBuffer的变更

1.1.6. 唯一索引、普通索引的应用

  1. 业务需要数据库做强依赖，保证数据的唯一性
  2. 归档库表，考虑使用普通索引，优化磁盘IO

1.2. Mysql服务选取索引原理

1.2.1. 存储过程和函数的区别

两者可以实现类似的能力，但函数更偏向解决一个特定的问题，存储过程一般稍复杂，同时限制条件较少。

  • 存储函数：不能用临时表、可以嵌入SQL执行、参数仅能为in(传值类型)、仅能返回一个结果值(RETURN返回)
  • 存储过程：需要用CALL调用、支持结果集返回、参数支持in(传值方式)、out(结果返回)、inout(传址方式)
 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

// 1. 存储过程申明

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

// 2. 存储函数申明
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

两者的跟多信息：help create procedure

1.2.2. 创建一个存储过程，往一个表中插入10万行记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

// 声明一个存储过程
DELIMITER ;;
CREATE PROCEDURE make_data()
BEGIN
    declare i int; 
    set i=1; 
    while(i<=100000) do 
        insert into `big_t` values(i,i,i); 
        set i=i+1; 
    end while; 
END ;;

// 调用存储过程
DELIMITER ;
CALL make_data();

// 查询存储过程
SHOW PROCEDURE STATUS\G
SHOW CREATE PROCEDURE test_db.make_data\G

// 删除存储过程
DROP PROCEDURE test_db.make_data

1.2.3. 开启慢日志调试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

// 设置为0表示当前线程，记录所有SQL日志
set long_query_time=0; 
select * from t where a between 10000 and 20000\G /*Q1*/
select * from t force index(a) where a between 10000 and 20000\G/*Q2*/

// 索引查看
mysql> show index from big_t;

// 分析优化器执行过程
explain select * from t where a between 10000 and 20000\G

1.2.4. 优化器的逻辑

选择索引是优化器的工作，优化器除了扫描行、是否使用临时表、是否排序等综合考虑。

扫描行是通过统计(索引的区分度)估算记录数，当Cardinality基数越大，索引的区分度就越好，统计还会考虑回表的资源开销。

  • 如果通过explain发现实际行数差别太大，可以通过analyze table t修改
  • 可以通过force index(x)修正
  • 可以通过修改SQL语句，引导优化器选择到正确的索引
  • 创建一个更合适的索引，或删掉误用的索引

1.3. 给字符串加索引

1.3.1. 针对Email邮箱案例 - 前缀有差异

  1. 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

  2. 确定我应该使用多长的前缀，依次执行不同的count比对差异：

    1
2
3
4
5
6
7
8

    // 查询差异
select count(distinct left(email,4)）as L4, 
    count(distinct left(email,5)）as L5, 
    count(distinct left(email,6)）as L6, 
    count(distinct left(email,7)）as L7,
from user;
// 更改email索引长度
alter table SUser add index index2(email(6));

  3. 注意：使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

1.3.2. 针对身份证案例 - 前缀雷同

  1. 使用倒序存储：每次存储之前反序存，读取的时候反序读取，索引照样添加，占用额外的CPU

  2. 新增一个hash字段冗余存储，占用额外存储空间，相比倒序更稳定(因为字符串有长有短)

    1
2
3
4

    // crc32
alter table t add id_card_crc int unsigned, add index(id_card_crc);
// 引用到hash列的索引
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

1.4. 日志和索引相关问题

  1. 如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为 6 字节的rowid来作为主键。

2. 主从数据一致性篇

2.1. Mysql怎么保证数据不丢

2.2. Mysql怎么保证主备一致

2.3. 读写分离有哪些坑

2.4. 主库出现问题，从库怎么办

2.5. 为什么从库延迟了几个小时

3. 故障分析篇

3.1. Mysql服务抖动 - Flsuh脏页

  1. 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为干净页
  2. Flush脏页到磁盘的过程，可能就是导致SQL抖动的原因

3.1.1. 导致Flush磁盘IO的场景

  1. redolog写满导致，[writepos, checkpoint]，当wp追上cp时候，阻塞所有的写操作，同时cp会前进到cp’，此时会导致[cp, cp']中的脏页flush到磁盘
  2. 系统BufferPool可用内存容量不足，导致需要进行内存资源回收(脏页刷入磁盘)，从而加大了磁盘IO压力 – 常态，注意控制脏页比率(过多脏页耗时、更新操作阻塞导致业务敏感)
  3. Mysql服务异步Flush – 空闲操作
  4. Mysql服务关停 – 忽略

3.1.2. 控制脏页比率

  1. 正确地告诉InnoDB所在主机的IO能力(IOPS可以利用fio测试)：innodb_io_capacity参数
  2. 多关注脏页比例Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total，不要让他接近75%
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

// io测试
fio -filename=fio-tmp.dat -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

// 当前值查看
mysql> show variables like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.02 sec)

3.2. 幻读问题

3.3. 饮鸩止渴

3.4. 表空间回收：表数据删除和表文件大小问题

简单的通过DELETE语句删除，不能达到回收表空间效果，DROP TABLE可以达到回收表空间效果

3.4.1. 表数据存储以及空洞问题

  • innodb_file_per_table
    • OFF：表数据存放在系统共享表空间，和数据字典放一起
    • ON：每个INNODB表数据单独存放以.ibd为后缀的文件中，MySQL 5.6开始，默认为ON
  • InnoDB数据以B+树存储数据组织，数据按页存储，注意区分数据页的复用和记录的复用
    • 记录复用：页表间的空洞部分
    • 数据页复用：数据页中所有记录都清空掉了
    • 如果相邻数据页中记录数很少，数据页利用率很低，系统会把两个相邻数据页进行页合并
    • DELETE只是把记录的位置标记为可复用，但磁盘文件的大小不会变，导致形成空洞
    • 随机INSERT非顺序插入，也可能导致页分裂，在新页插入导致前一个数据页中存在空洞
  • 把空洞填补，就可达到表搜缩的效果，即重建表，即创建一个临时表，然后往临时表插入数据(数据顺序插入)，5.6版本以后支持Online DDL(将更新记录记录到日志中，后续在新记录重放)
    • alter table A engine=InnoDB 即默认为alter table t engine=innodb,ALGORITHM=inplace;
    • gh-os开源工具
  • tmp_table和tmp_file
    • 临时表是在MySQL Server层做的，即alter table t engine=innodb,ALGORITHM=copy;，强制拷贝表，在Server层做
    • 临时文件是在InnoDB引擎做的，即alter table t engine=innodb,ALGORITHM=inplace;，属于inplace原地做的，需要效果磁盘空间，故1T的表、1.2T不能做inplace DDL
tmp_table tmp_file

Tips: 重建方法都会扫描原表数据和构建临时文件，对于很大的表来说，这个操作是很消耗IO和CPU资源的，因此，如果是线上服务，要很小心地控制操作时间。如果想要比较安全的操作的话，可以采用开源的gh-ost 来做

另外，在重建表的时候，InnoDB 不会把整张表占满，每个页留了1/16给后续的更新用，也就是说，其实重建表之后不是“最”紧凑的。

3.4.2. optimize table、analyze table 和 alter table区别

  • alter table t engine = InnoDB：重建表(recreate table)
  • analyze table t：对表的索引做重新统计，没有修改数据，期间加了MDL锁
  • optimize table：等价于recreate+analyze

3.5. 为什么值改一行，锁了这么多

3.6. 为什么仅查询一行记录也很慢

3.7. 为什么相同逻辑的SQL语句性能差异很大

3.8. 如何判断Mysql是否出现问题了

3.9. 数据库删除了怎么办

3.10. 为什么有kill不掉的SQL

3.11. 查数据会不会撑爆数据库内存？

4. SQL篇

4.1. count(*)很慢

4.1.1. count的实现

  • MyISAM，把表总行数记录在磁盘上，不加过滤条件返回很快，加了过滤条件也很慢了
  • InnoDB，需要一行行的从引擎读取，然后累加计算，因为MVCC存在，不能直接像MyISAM那样，存一个表记录整数；但InnoDB事务支持、并发能力、数据安全等都由于MyISAM

InnoDB是索引组织表，因此记录是哪个索引都可以统计，因此在保证正确逻辑下，MySQL优化器会找最小的那颗B+索引树来遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

注意show table status中的行是采样估算的，误差很大(50%)，因此该行无法使用

有一个页面经常要显示交易系统的操作记录总数，到底应该怎么办呢？答案是，我们只能自己计数。 但如果使用其他缓存，比如Redis，则存在两个分布式存储数据不一致的情况，究其原因，主要是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图；

4.1.2. 不同count(*)、count(id)、count(1)、count(字段)差别

InnoDB中，count()是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

  • count(字段): 返回满足条件结果集，且字段非NULl的
  • count(*)、count(id)、count(1)，返回满足条件结果集的总行数
    • count(主键id)：引擎遍历整个表，把每行id取出，返回给server层；server层判断不能为空，按行累加
    • count(1)：引擎遍历整个表，不取值，返回给server层；server层对每行放一个数字“1”进去，按行累加；（快于count(id)，不用行数据解析)
    • count(字段）：
      • 字段定义为非NULL，引擎遍历表取字段，断定不能为null，按行累加
      • 自动定义为NULL，引擎查询后，还需要判断字段是否为null，非null才累加
  • Mysql性能优化差异：
    • server层要什么，给什么
    • InnoDB值给必要值
    • 优化器仅做了count(*)的取行数优化，
  • 结论：count(*) > count(1) > count(id) > count(字段) - 非NULl定义 > count(字段) - NULl定义，因此尽量使用count(*)即可！

4.2. order by是如何工作的

4.2.1. order by 语句执行过程

explain select city,name,age from t where city='杭州' order by name limit 1000 ;

Extra这个字段中的Using filesort表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。通常类似情况，我们会在city加索引，索引结构见后续图示；

  1. 初始化sort_buffer，确定放入name、city、age这三个字段
  2. 从引擎中city索引中查询到city=杭州的主键ID，回行查询name、city、age，存储到sort_buffer中
  3. 重复步骤2，直至city!=杭州
  4. sort_buffer中的内容，按name名称进行排序，sort_buffer中的内容排序，视排序的数据集规模和内存容量：
    • 内存放得下，但查询记录多用快排
    • 内存放得下，查询记录少用堆排
    • 内存放不下用外部排序
  5. 基于排序结果取出前1000行内容返回给客户端

4.2.2. 全字段排序，当sort_buffer不足，需要做外排序

1
2
3
4
5
6
7
8

mysql> show variables like "%sort_buffer%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;

外排序(采用归并排序)，可以通过优化器跟踪检测（如上）；在排序过程中，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。

4.2.3. rowid排序，当排序内容过长

如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

排序模式为rowid排序就是先利用主键ID和待排序字段name先排完后，再利用ID回表查询的过程（注意，最后取值为逻辑操作，即从之前的无序记录数据中直接找到对应ID的行）；

对于InnoDB表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择, 但如果是内存表，则优化器忽略磁盘访问，优先采用rowid排序;

rowid排序，可以通过SET max_length_for_sort_data = 16;设置最大排序数据长度，当查询字段总长度超过该长度限制，将采用rowid排序

Mysql设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

4.2.4. 联合索引和覆盖索引，优化排序过程

  • 因为联合索引，已经是有序的了，因此可以省略排序的时间。
    • 比如联合索引(city, name)，在索引记录中是按city、name已排序好了，那么where city=x order by name就可以省略排序开销了
    • explain结果中Extra值没有了Using filesort了，仅为Using index condition
  • 因为覆盖索引，待查询内容已经在索引中了，因此可以省略了回表的操作，进一步得到了优化
    • explain结果中的Extra值为Using index

4.3. 如何正确线上随机内容

4.3.1. 通过存储过程批量插入单词

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure iwords()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;
call iwords();

4.3.2. order by rand() 流程

order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法，可见相对流程复杂了很多：

  • 关于行记录唯一标识POS:
    • 对于有主键的rowid表来说，这个rowid=主键ID；
    • 对于没有主键的InnoDB表来说，这个rowid=系统生成POS(6个字节长)；
    • MEMORY引擎不是索引组织表，可以认为它就是一个数组，这个rowid就是数组的下标；
  • 内存表阈值：tmp_table_size
    • 当超过tmp_table_size=16M大小后，内存表会转磁盘临时表
  • 正确从数据库中获取随机记录做法：
    • 取得表行数，记为C
    • 根据相同随机算法，得到从[0, C]中取Y1,Y2,Y3
    • 执行指定次数的limit Yx, 1

在实际应用的过程中，比较规范的用法就是：尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

4.4. 如何使用join连接，以及怎么优化

4.5. 为什么临时表可以重命名

4.6. insert怎么有这么多锁

4.7. 授权问题，grant后，需要flush privileges么？

5. 技巧篇

5.1. 有了InnoDB是否还需要Memory

5.2. 怎么最快复制一张表？

5.3. 要不要使用分区表？

5.4. 自增主键连续性问题，以及自增id用完怎么办？

《Mysql45讲》（一）基础篇关键点整理 《金字塔原理》- Tobe Continue

Related Articles