《高性能 MySQL》读书笔记

比眉伴天荒 2022-08-20 04:28 229阅读 0赞

**结构和历史**

1、隔离级别有四种：  
READ UNCOMMITTED（未提交读），同事务中某个语句的修改，即使没有提交，对其他事务也是可见的。这个也叫脏读。  
READ COMMITTED（提交读），另一个事务只能读到该事务已经提交的修改，是大多数据库默认的隔离级别。但是有下列问题，一个事务中两次读取同一个数据，由于这个数据可能被另一个事务提交了两次，所以会出现两次不同的结果，所以这个级别又叫做不可重复读。这里的不一样的数据包括虚读（两次结果不同）和幻读（出现新的或者缺少了某数据）。  
REPEATABLE READ（可重复读），这个级别不允许脏读和不可重复读，比如MYSQL中通过MVCC来实现解决幻读问题。  
SERIALIABLE（可串行化），这儿实现了读锁，级别最高。

2、显示和隐式锁定：事务执行中，随时可以执行锁定，锁只有在COMMIT或ROLLBACK的时候才释放，而且所有的锁是同时释放的。这些锁定都是隐式锁定。也可以通过特定语句显式锁定，比如SELECT … LOCK IN SHARE MODE等。

3、MVCC（多版本并发控制）：通过保存数据在某个时间点的快照来实现。在INNODB中通过每行记录后保存两个隐藏的列，一个保存行的创建时间，一个保存行的过期（删除）时间，这儿的保存不是时间而是系统版本号，随着事务的数量增加而增加版本号。  
SELECT:只找版本号早于当前事务版本的数据，删除版本要大于当前版本号。  
INSERT：插入时保存当前版本号为行版本号。  
DELETE：为删除的每行保存当前版本号为行的删除标示。  
UPDATE：先为插入的行保存版本号，同时保存当前版本号为行删除标示。  
I  
4、NNODB通过MVCC来支持高并发，通过间隙锁来防止幻读。

5、MYISAM支持读取的时候插入（并发插入），支持延迟更新索引键（Delayed Key Write），先写内容最后才更新索引，需要指定DELAY\_KEY\_WRITE。

**SCHEMA与数据类型优化**  
1、避免使用NULL。

2、整数类型中，TINYINT使用8位存储空间，BIGINT为64位，一般做SIMHASH选择64位做特征值应该是基于这个，转成16进制有16位。其中指定的宽度只在命令行中展示时起作用。

3、实数类型中，DECIMAL用于存储精确的小数，比如货币。

4、VARCHAR比定长CHAR更省空间，因为它只需要使用必要的空间，但是其需要使用1或者2个额外字节用来记录字符串的长度。但是在update的时候，容易造成碎片。  
CHAR是定长的，MYSQL根据定义字符串的长度分配空间，而且其会删除所有末尾空格。比如存”STRING “的时候，末尾的空格会被删除。  
VARCHAR(5)和VARCHAR(100)存同一个字符虽然空间开销相同，但是在存的时候会消耗更多内存，还有在使用临时表的时候也会比较糟糕。

5、BLOB和TEXT是为存储很大数据而设计的，分别以二进制和字符方式存储。TEXT是SMALLTEXT的同义词，BLOB也是。

6、ENUM类型存储是非常紧凑，其实际存储为整数。

7、BIT可以在一列中存储一个或多个0/1值，最大长度为64。问题是存进去是二进制，但是展示出来却是十进制的。

8、计数器表的优化，对于单表的a+1操作可能受到锁的影响，可以通过创建100行数据，然后随机选取一行写，取的时候使用SUM(a)进行查询。

9、高效ALTER TABLE，修改表结构涉及到不需要改变数据只要改frm文件的时候，可以使用语句ALTER COLUMN来操作。  
还有替换frm的高效方法，首先create table like来建立新表，修改新表结构，对旧表数据执行锁定”FLUSH TABLES WITH READ LOCK;”  
执行系统命令，mv new.frm a.frm之类，记得备份。  
UNLOCK TABLES;

10、高效载入数据到MyISAM表，可以暂时禁用索引。  
ALTER TABLE tab DISABLE KEYS;  
ALTER TABLE tab ENABLE KEYS;  
但是DISABLE KEYS只对非唯一索引有效。

**创建高性能的索引**

1、B-Tree索引，其意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离都相等。  
B-Tree对索引列是顺序存储的，所以很适合查找范围数据。  
缺点是必须按照索引从最左列开始查找，否则无法使用索引。

2、R-Tree（空间数据索引），MyISAM表支持空间索引，可以用作地理数据存储。

3、独立的列无法使用索引，独立的列是指索引列为表达式的一部分或者函数的参数。

4、前缀索引，索引很长的字符列会让索引变大变慢，所以选择一个合适的长度来索引是很有效率的。  
首先需要找出合适长度的前缀，用语句：  
select count(\*) as cnt,LEFT(city,3) as pref from group by pref order by cnt;  
调整其中LEFT函数的值选择最合适的长度。建索引时如下：  
ALTER TABLE a ADD KEY(city(7))；  
还可以考虑后缀索引，比如查找某个域名的所有电子邮件地址，需要把字符串翻转后存储。

5、多列索引的顺序非常重要，要选择最有效率的列放到最左边。

6、聚族索引并不是一种单独的索引类型，而是一种数据存储的方式。  
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(LEAF PAGE)中，聚簇表示数据行和相邻的键值紧凑地存储在一起。

7、当存在OR条件的时候，会看到此时使用了index\_merge类型索引，这个说明表上的索引很糟糕，这个是由于在OR左右两个条件都建立了索引，应该修改索引，或者使用IGNORE INDEX来会略某些索引。  
8、在选择多列索引的时候，通常把选择性更大的放到前面(该条件下统计数量更小的)。  
9、在INNODB中最好使用自增作为主键，而使用UUID等随机的聚簇索引会对I/O密集型应用造成很坏性能，它使得聚簇索引的插入变得完全随机。

10、当要查询的字段的值在索引中，就称该索引为覆盖索引。在explain的时候extra显示using index。为了能用到覆盖索引，可以使用延迟关联（deferred join）。书上有很巧妙的例子：）。注意的是，INNODB中二级索引的叶子节点都包含了主键的值，所以查询的值包含主键id时，主键id可以不在所建的联合索引中。关于延迟关联还有个经典例子，大偏移翻页的时候。  
11、当索引类型为index时，说明MYSQL使用了索引扫描来做排序。  
12、在5.1或更新版本中，INNODB在服务器端过滤掉行后就释放锁，而早期版本中则需要在事务提交后才释放锁。  
13、EXPLAIN中出现Using where表示在存储引擎返回行后再使用where过滤条件。  
14、一个诀窍，一个符合查询条件的多列索引中，有时候条件里没有包含存在的索引列，这时候使用IN来满足最左前缀。比如多列索引中有sex列，但是用户查询时没有选择sex，则使用IN(‘M’,’F’)来满足使用索引的条件。  
某一些条件比如age，一般是范围查询，而根据最左前缀碰到范围查询后会终止，所以这类一般放在多列索引的最后面。  
而使用开始的IN语句满足最左前缀也不能滥用，3个IN条件，每 个有N个枚举值，则会产生N\*N\*N中组合，降低效率。

15、按顺序访问范围数据很快，因为顺序I/O不需要多次磁盘寻道，不需要额外排序操作。

16、聚簇索引(Clustered Index)，一个索引项直接对应实际数据记录存储页。  
索引项和实际数据行的排序完全一样。  
一个表只能有一个聚簇索引。但是该列能包含多个列，就像电话簿使用姓氏和名字同时进行排序。

17、INNODB支持聚簇索引，其中聚簇索引就是表，必须要像MYISAM那样的行存储。聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。  
InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。  
在INNODB主键中插入UUID，由于主键会保持有序，会严重影响性能。

**查询性能优化**  
1、检查响应时间，扫描的行和返回的行，扫描的行数和访问类型（Explain的时候）是三个简单衡量查询的指标。  
2、在进行大查询的时候使用分而治之，比如delete大数据的时候使用limit，使用do while分解操作，避免大语句锁住过多数据，占满事务日志，耗尽系统资源，阻塞很多重要查询。  
3、关联查询拆成简单查询然后在应用层聚合数据，可以让缓存效率更高，单个查询可以减少锁竞争，本身查询效率也更高，在数据库中做关联查询还可能导致需要重复地访问一部分数据。  
4、mysql客户端和服务器之间的通信协议是半双工，任何一个时刻只能单向发送数据而不能两边同时进行，像是抛绣球。所以mysql通常需要等所有数据都已经发送给客户端后才能释放这条查询锁占用的资源，这时max\_allowed\_packet很重要。  
5、一个完整查询包含如下过程包括客户端/服务器端通信->查询缓存->语法解析器和预处理->查询优化器->数据和索引的统计信息->查询执行引擎->返回结果给客户端。下面会一次说说每个步骤。  
![这里写图片描述][20160131192148472]

6、查询状态，一个连接或者线程，在任何时刻都有一个状态。  
sleep，线程正在等待客户端发来新请求。  
query，线程正在执行查询或者将结果发送给客户端。  
locked，该线程正在等待表锁。而存储引擎级别的锁比如innodb的行锁并不会体现在线程状态。  
copying to tmp table\[on disk\]，线程正在执行查询并且将结果集都复制到一张临时表，一般是group by或者文件排序等操作。on disk表示正在将一个内存临时表放到磁盘上。  
sorting result，线程正在对结果集进行排序。  
sending data，线程可能在多个状态间传送数据，或者正在生成结果集或者正在向客户端返回数据。  
了解这些状态可以很快了解谁正在掷球。  
7、在查询缓存后，先进行语法解析器和预处理，mysql通过关键字将SQL语句进行解析并生成一颗对应的解析树，进行语法规则验证。当语法树被认为合法了，则由优化器将其转化为执行计划，一条语句可能有很多执行方式并返回相同结果，优化器的作用就是找到这其中最好的执行计划。优化器是基于成本来预测。  
8、在很多数据库中IN等同OR，但是在mysql中，会把IN中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(log n)的操作。当IN中有大量数据的时候效率会更快。  
9、关联查询，MySQL认为任何一次查询都是一次关联，不仅仅是UNION，子查询等都可能是。对于UNION，MYSQL现将一系列查询的单个查询结果放到一个临时表中，再重新读出临时表的数据来完成UNION查询。  
MYSQL对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。  
当在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中，然后将这个临时表当做一个普通表对待（派生表）。  
10、执行计划，MYSQL生成查询的一颗指令数。可以使用EXPLAIN EXTENDED后再使用WARNINGS。  
任何多表查询都可以用一棵树来表示，比如四表查询：  
而事实上MYSQL总是从一个表开始一直嵌套循环，是一颗左侧深度优先的树。  
![这里写图片描述][20160131192404582]  
![这里写图片描述][20160131192419017]

[20160131192148472]: /images/20220731/08f4b042503948999784eb65c625d664.png
[20160131192404582]: /images/20220731/4f32e799e8904b1b9b94392307f58943.png
[20160131192419017]: /images/20220731/1413844b0c3844ae8db21a57d5a58fe6.png