MySQL疑难杂症

[Danceiny]

• https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

[Danceiny]

加锁

• 某些数据库锁住的行不仅仅是where条件过滤掉的行。当where条件中部分字段有索引，部分没有，锁住的行有时候仅仅是有索引的条件过滤出来的行。

[Danceiny]

修改表

对线上数据库加字段，有些数据库会事先在写入每个块时预留少部分空间，便于添加字段。但是可能不够用，那么数据库可能将一行数据的某个部分放在另一个块中，以前一次IO得到的数据，现在需要多次IO。虽然可以通过压缩、整理等方式处理，但是仍然很消耗数据库性能。

[Danceiny]

row_format

• InnoDB的行记录格式, Compact, Redundant, Compressed, Dynamic

by DBA

建议建表语句最后default charset utf8 后面 都加上一个 ROW_FORMAT = COMPRESSED ; 启用表压缩以节约磁盘空间

[Danceiny]

回表

查找数据分两个过程，首先通过索引查找相应的标识符，然后通过标识符到原表中提取相应的数据。

分页到后面性能下降，主要是因为回表。解决方案：

1. select id from where ... limit 10 offset N（对应于oracle，是使用rowid这样的物理地址而非主键），然后通过主键拿数据。该方案的前提是索引要挂在主键上。

2. 根据条件在索引上查询出分页下限的最大值，该最大值也是建立在索引上的，因此不会回表，再通过最大值求出一页数据。

[Danceiny]

移动数据文件夹(datadir)

参考 https://blog.51cto.com/haowen/1274721 一顿操作后，重启服务失败，看新的文件夹权限似乎没问题，仍然说没有权限（直接运行mysqld即可看到最直观的报错日志）。

需要关注的点：

• /etc/apparmor.d/usr.sbin.mysqld
• 外挂磁盘 （我的deepin 分区后有一个分区被视为外挂磁盘，日常截图都无法直接保存到这个分区的文件夹，很奇怪）

[Danceiny]

索引列的长度限制

3072bytes
一个varchar(128) + varchar(1024)即超过限制，无法创建联合索引。

[Danceiny]

导出数据库表结构

mysqldump -h 172.31.1.130 -P 3308 -uroot -pCgar -d -A --skip-lock-tables >ddl.sql

[Danceiny]

mysql在线ddl(加字段、加索引等修改表结构之类的操作）过程

A.对表加锁(表此时只读)
B.复制原表物理结构
C.修改表的物理结构
D.把原表数据导入中间表中，数据同步完后，锁定中间表，并删除原表
E.rename中间表为原表
F.刷新数据字典，并释放锁

ref: https://www.cnblogs.com/wangtao_20/p/3504395.html

[Danceiny]

GCS vs Compact

腾讯互娱 在线加字段的解决方案：http://www.zhdba.com/mysqlops/2013/09/14/mysql-innodb-online-ddl/

套路总结：

• 利用Compact聚集索引的记录头里面的两个bit的保留位，存储Field Count （兼容Compact，省空间）
• 新建一张系统表，存储新增字段的默认值

[Danceiny]

聚集索引 vs 非聚集索引

https://www.cnblogs.com/aspnethot/articles/1504082.html

[Danceiny]

在线改索引引发的惨案

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-05-30 17:31:06 0x7f4a16deb700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 180013 srv_active, 0 srv_shutdown, 4791 srv_idle
srv_master_thread log flush and writes: 184804
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 463073111
--Thread 139955702556416 has waited at buf0buf.cc line 5214 for 0.00 seconds the semaphore:
Mutex at 0x7f4a447c6618, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1709, lock var 1

wait has ended
--Thread 139955703097088 has waited at buf0buf.cc line 5214 for 0.00 seconds the semaphore:
Mutex at 0x7f4a447c6618, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1709, lock var 1

--Thread 139956841232128 has waited at buf0buf.cc line 4383 for 0.00 seconds the semaphore:
Mutex at 0x7f4a447c6618, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1709, lock var 1

wait has ended
--Thread 139956186650368 has waited at buf0buf.cc line 4383 for 0.00 seconds the semaphore:
Mutex at 0x7f4a447c6618, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1709, lock var 1

OS WAIT ARRAY INFO: signal count 444696169
RW-shared spins 0, rounds 905619623, OS waits 254415838
RW-excl spins 0, rounds 263253601, OS waits 2522239
RW-sx spins 1084437, rounds 21871631, OS waits 267135
Spin rounds per wait: 905619623.00 RW-shared, 263253601.00 RW-excl, 20.17 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 174334358
Purge done for trx's n:o < 174333622 undo n:o < 0 state: running but idle
History list length 297
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421431800329896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800322000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800237400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800320872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800319744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800317488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800298312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800289288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800252064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800244168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800283648, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800282520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800278008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800276880, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800270112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800255448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800254320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800253192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800236272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800249808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800247552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800243040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800241912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800328768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800378400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800265600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800354712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800345688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800274624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800272368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800257704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421431800256576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 174333546, ACTIVE 636 sec reading clustered index
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 333572, OS thread handle 139955753424640, query id 122414009 172.17.3.223 chaoge altering table
/* ApplicationName=DataGrip 2019.1.3 */ create index shop_crawled_time_src_id_src_index
	on shop (crawled_time, src_id, src)
Trx read view will not see trx with id >= 174333548, sees < 174333547
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: native aio handle (read thread)
I/O thread 3 state: processing completed aio requests (read thread)
I/O thread 4 state: complete io for buf page (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [74, 238, 149, 1] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2145240514 OS file reads, 46122186 OS file writes, 9782762 OS fsyncs
37761.96 reads/s, 8192 avg bytes/read, 5.83 writes/s, 5.11 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5468, seg size 5470, 163765 merges
merged operations:
 insert 135061, delete mark 95276, delete 1284
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
0.00 hash searches/s, 4.22 non-hash searches/s
---
LOG
---
Log sequence number 211120239404
Log flushed up to   211120239404
Pages flushed up to 211120230471
Last checkpoint at  211120230471
Max checkpoint age    869019772
Checkpoint age target 841862905
Modified age          8933
Checkpoint age        8933
0 pending log flushes, 0 pending chkp writes
23713089 log i/o's done, 1.44 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 283639808
Dictionary memory allocated 407026
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 4596032 	(4431296 + 164736)
    Page hash           277384 (buffer pool 0 only)
    Dictionary cache    1514850 	(1107824 + 407026)
    File system         845024 	(812272 + 32752)
    Lock system         800120 	(664936 + 135184)
    Recovery system     0 	(0 + 0)
Buffer pool size   16382
Buffer pool size, bytes 268402688
Free buffers       614
Database pages     25894
Old database pages 9545
Modified db pages  4
Pending reads      459
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 248051793, not young 10401757892
77.61 youngs/s, 202190.49 non-youngs/s
Pages read 2145425281, created 1407542, written 18479080
37743.40 reads/s, 0.00 creates/s, 2.61 writes/s
Buffer pool hit rate 878 / 1000, young-making rate 0 / 1000 not 657 / 1000
Pages read ahead 30207.54/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 25894, unzip_LRU len: 2797
I/O sum[414648]:cur[1185], unzip sum[1835107]:cur[5882]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
87 read views open inside InnoDB
1 RW transactions active inside InnoDB
---OLDEST VIEW---
Read view low limit trx n:o 174333548
Trx read view will not see trx with id >= 174333548, sees < 174333547
Read view individually stored trx ids:
Read view trx id 174333547
-----------------
Process ID=11858, Main thread ID=139956286641920, state: sleeping
Number of rows inserted 4543713, updated 19383669, deleted 0, read 97285465437
0.11 inserts/s, 0.39 updates/s, 0.00 deletes/s, 766540.64 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.02 sec)

[Danceiny]

SQL语言艺术

链接：https://pan.baidu.com/s/1yNwRGpW8v0t6coBG_8hqcg 密码：ztll

[Danceiny]

InnoDB 线程

默认情况下，InnoDB存储引擎的后台线程有：

• IO thread 4
  • insert buffer thread
  • log thread
  • read thread
  • write thread
• master thread
• lock monitor thread
• error monitor thread 1

[Danceiny]

InnoDB 内存

• 缓冲池 buffer pool
  show engine innodb status\G 查看

Buffer pool size   16382
Buffer pool size, bytes 268402688
Free buffers       715
Database pages     25427
Old database pages 9405
Modified db pages  730
Pending reads      0
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 465406565, not young 26346271152
529.75 youngs/s, 172019.05 non-youngs/s
Pages read 4516411110, created 3700272, written 48918495
22358.76 reads/s, 17.33 creates/s, 252.82 writes/s
Buffer pool hit rate 956 / 1000, young-making rate 1 / 1000 not 345 / 1000
Pages read ahead 58.72/s, evicted without access 0.00/s, Random read ahead 0.00/s

上面的Buffer pool size 16382是指有16382个buffer frame，那么一个buffer frame是多大呢？通过bytes 268402688可以计算得： 268402688/16382 / 1024 = 16KB。
（此次完整的输出见：https://gist.github.com/Danceiny/8805204b05d50720fc48047c8b6d019b）

• 重做日志缓冲池 redo log buffer
• 额外内存池 additional memory pool

[Danceiny]

InnoDB关键特性

1. 插入缓冲

假设根据nickname列创建索引，则产生一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键id的执行顺序，但是对于非聚集索引，叶子节点的插入不再是顺序的了。（这是由B+树的特性决定的非聚集索引插入的离散性）
InnoDB的应对方案是：

对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓存池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，“欺骗”数据库这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引叶子节点的合并操作。这时通常能将多个插入合并到一个操作中（因为在一个索引页中）。

插入缓冲的使用需要满足两个条件：

• 索引是辅助索引
• 索引非唯一

非唯一的这个要求是因为，在把它插入到插入缓冲时，我们不会去查找索引页的情况。如果需要查找，那又会出现离散读的情况，插入缓冲就没有意义了。

依然是分析InnoDB的status，从上面的完整输出中可以找到下面这段文本：

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5468, seg size 5470, 779149 merges
merged operations:
 insert 2412040, delete mark 166371, delete 2079
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 69239, node heap has 11 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
27419.31 hash searches/s, 69624.85 non-hash searches/s

seg size显示了当前插入缓冲的大小是 5470个buffer frame，也就是 5470 × 16kB，约85.5MB。free list len 5678代表了空闲列表的长度，size是已经合并记录页的数量。
我们真正关心的数据在：insert 2412040, delete mark 166371, delete 2079。

2. 两次写

插入缓冲为InnoDB提升了性能，两次写为InnoDB提升了数据的可靠性。

缓冲池的脏页刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer（大小约2MB），再通过doublewrite buffer分两次每次写入1MB到共享表空间的物理磁盘上，然后马上调用fsync同步磁盘，避免缓冲写带来的问题。由于doublewrite页是连续的，因此该过程是顺序写。在完成doublewrite页的写入之后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入是离散的。如果在将页写入磁盘的时候操作系统崩溃，恢复过程中InnoDB可以从共享表空间的doublewrite中找到该页的一个副本，将其拷贝到表空间文件，再应用redo log。

Input: show global status like 'innodb_dblwr%';
Output:

Innodb_dblwr_pages_written	63549076
Innodb_dblwr_writes	12877913

这个比例大概是5:1。远小于64:1，说明系统写入压力并不高。

有些文件系统本身就提供部分写失效的防范机制，如ZFS，这种情况就不需要启用doublewrite。

3. 自适应哈希索引

设计思想：数据库自优化（self-tunning）

关键特性：InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。自适应哈希索引通过缓冲池的B+树构造而来，因此建立速度很快，且不需要将整个表都建哈希索引，InnoDB会自动根据访问的频率和模式来为某些页建立哈希索引。

Ibuf: size 1, free list len 5468, seg size 5470, 779149 merges
merged operations:
 insert 2412040, delete mark 166371, delete 2079
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 69239, node heap has 11 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
27419.31 hash searches/s, 69624.85 non-hash searches/s

哈希索引只能用来搜索等值的查询，其他查找类型如范围查找，是无法使用的，因此出现non-hash searches/s的情况。