《高性能MySQL》学习笔记

第1章 MySQL架构与历史

1.2 并发控制

　　MySQL在两个层面实现并发控制：服务器层与存储引擎层。

 

读锁和写锁：

　　在处理并发读或写时，可以通过实现一个由两种锁组成的系统来解决问题。

　　这两种锁通常被称为共享锁和排他锁，或者称为读锁和写锁。

　　读锁：是共享的，或者说是相互不阻塞的，多个客户可以在同时读取同一数据。

　　写锁：是排他的，一个写锁会阻塞其他的写锁和读锁，同一时刻只能有一个用户能够写入，并防止其他用户读取正在写入的数据。

 

锁粒度：

　　锁粒度：指加锁的对象的大小。显然，锁的粒度越小，并发控制效率越高。

　　锁的各种操作，包括获得锁、检查锁和释放锁等，都会增加系统开销。

　　因此，如果系统花费大量时间来管理锁，而不是用来获取数据，就会影响系统性能。

　　锁策略：在锁的开销和安全性之间寻求平衡。

　　有两种常见的锁策略，表锁和行级锁。

　　表锁：开销较小，但是并发控制不好。

　　行级锁：可以很好地实现并发控制，但是开销比较大。

　　行级锁是在 mysql 的存储引擎层实现，没有在服务器层实现，innodb 中实现了行级锁。

 

1.3 事务

　　事务：是一组原子性的SQL语句，事务内的语句，要么全部执行成功，要么全部执行失败。

 

事务的四大特性ACID：

　　原子性：整个事务要么全部提交成功，要么全部失败回滚，不可以只执行一部分。

　　一致性：数据库总是从一个一致状态转换到另一个一致状态。（转账失败后，卡里的钱应该不变，不能少了）

　　隔离性：事务提交之前所做的修改对其他事务是不可见的。（通常来说）

　　持久性：事务提交之后，所做的修改会持久化到数据库中。

　　事务处理也会使系统做更多额外工作，用户可以根据业务是否需要进行事务处理，选择合适的存储引擎。

 

四种事务的隔离级别：

READ UNCOMMITTED (未提交读)：

　　事务中的修改，即使没提交，对其他事务也是可见的。

　　事务可以读取未提交的数据，也叫脏读，一般很少使用。

 

READ COMITTED (提交读)：

　　一个事务开始时，只能看见已经提交的修改，并且所做的修改对其他事务不可见。

　　这个级别有时候也叫不可重复读，因为两次执行同样的查询，可能会得到不同的结果。

　　事务A前后执行两次查询，前一次读取某条记录之后，事务B对其进行了修改并提交，这时当A再次读取该数据的时候就会发现与之前读取的结果不一样。

　　是大多数数据库默认的隔离级别（但MySQL不是）。

 

REPEATABLE READ (可重复读)：

　　保证了同一个事务多次读取同样记录的结果是一致的。但无法解决幻读。

　　幻读：事务A读取某个范围内的记录时，事务B又在该范围内插入新的记录，当事务A再次读取时，会产生幻行。

　　另一种幻读情况：事务A对数据库所有行做了修改时，事务B对向数据库中插入了一行新的数据。这时A发现还有没有修改的记录，就像发生幻觉一样。

　　InnoDB存储引擎使用一种被称成next-key locking的策略来避免幻读(phantom)现象。

　　这是MySQL  （InnoDB引擎）的默认事务隔离级别。

 

SERIALIZABLE (可串行化)：

　　强制事务串行执行，在读取每行数据上都加锁，可能产生大量的超时和锁争用问题。

　　只有在非常需要确保数据的一致性，且可以接受没有并发的情况下才考虑使用该级别。

死锁：

　　死锁指的是多个事务在同一资源上相互占用，并请求对方占用的资源，导致恶性循环的现象。

　　数据库系统中实现了各种死锁检测和死锁超时机制。

　　InnoDB目前处理死锁的方法是：将持有最少行级排他锁的事务回滚。

 

事务日志：

　　存储引擎修改表数据的时候只修改其在内存中的拷贝，再讲修改记录持久化到硬盘上的事务日志中。

　　不用每次修改都将数据持久化到硬盘，能提高事务的效率。

　　也被称为预写式日志，修改数据需要写两次硬盘。

　　系统崩溃，重启后能根据日志恢复这部分修改的数据。

 

MySQL中的事务：

　　MySQL提供了两种事务型存储引擎，InnoDB和NDB Cluster。

　　在MySQL中默认是自动提交事务的，每个查询操作被当作一个事务。

　　可以使用set autocommit来设置是否自动提交。

　　可以通过set session transaction isolation level来设置隔离级别。

　　在同一事务中使用多种存储引擎是不可靠的。

 

隐式锁定：

　　InnoDB采用的是两阶段锁协议。

　　在事务执行过程中随时可能执行锁定，只有在提交和回滚的时候才会释放。

 

显示锁定：

　　InnoDB支持通过特定语句显示锁定

1.4 多版本并发控制（MVCC）

　　MVCC相当于行级锁的一个变种，但在很多情况下避免了加锁操作，开销更低。

　　大多数都实现了非阻塞读操作，写操作也只用锁定必要的行。

　　MVCC只在读提交和可重复读这两个隔离级别下工作。

 

 

1.5 MySQL的存储引擎

InnoDB：

　　MySQL5.5之后的默认存储引擎。

　　采用MVCC来支持高并发，并且实现了四个标准的隔离级别(默认可重复读)。

　　通过间隙锁策略防止幻读：间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

　　表是基于聚簇索引建立的。

　　聚簇索引对主键查询有很高的性能。二级索引中必须包含主键列，所以主键列最好别太大。

 

　　内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够自动在内存中创建哈希索引以加速读操作的自适应哈希索引、能够加速插入操作的插入缓冲区等。

　　通过一些机制和工具支持真正的热备份。其它存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

 

 

MyISAM：

　　MyISAM 提供了大量的特性，包括全文索引、压缩表、空间数据索引等。

　　应该注意的是，MySQL 5.6.4 也添加了对 InnoDB 存储引擎的全文索引支持。

　　MyISAM不支持事务和行级锁，而且无法在崩溃之后安全恢复；

　　它对整张表加锁，而不是某行。

　　读取时对读到的所有表加共享锁，写入时对表加排他锁。

　　并发插入：在表有读取查询的同时，也可以插入新数据。

　　支持地理空间搜索。

　　如果不在乎可扩展能力和并发能力，以及崩溃后数据丢失，却对InnoDB空间占用过多比较敏感，可以考虑用MyISAM。

　　如果不需要支持事务，主要是select和insert操作，（比如日志型应用），可以考虑用MyISAM。

 

 

比较：

1. 事务：InnoDB 是事务型的。
2. 备份：InnoDB 支持在线热备份。
3. 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
4. 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
5. 其它特性：MyISAM 支持压缩表和空间数据索引。

 

 

Memory：

　　所有数据都保存在内存中，比MyISAM快一个数量级。

　　支持hash索引，查找非常快。

　　表级锁，并发写入性能较低。

 

第4章 Schema与数据类型优化

4.1 选择优化的数据类型

　　更小的通常更好：更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理需要的CPU周期也更少。

　　简单就好：简单的数据类型的操作通常需要更少的CPU周期。例如：整型比字符串操作的代价更低，因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子：

                       一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该使用整型存储IP地址。

　　避免使用null：通常情况下最好指定列为not null（特别是要在该列上建索引时），除非真的需要存储null。因为null列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在MySQL中也需要特殊处理。

 

整数类型：

　　TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8，16,24,32,64位存储空间。

　　MySQL可以为正数类型指定宽度，例如INT(11)，但大多数应用这是没有意义的。对于存储和计算来说，INT(1)和INT(20)是相同的。

 

实数类型：

　　实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。

　　FLOAT和DOUBLE类型支持使用标准的浮点运算进行近视计算。DECIMAL类型用于存储精确的小数。

　　浮点类型在存储同样类型的范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储.DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

　　因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将对应的值扩大N倍。

 

字符串类型：

VARCHAR:

　　它比定长类型更节省空间，因为它仅使用必要的空间。

　　有额外字节记录长度。

　　但是由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。

　　MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

　　MySQL在存储和检索时会保留末尾空格。

 

CHAR:

　　定长，当存储CHAR值时，MySQL会删除所有的末尾空格。

　　适合存储MD5值。

　　定长的CHAR类型不容易产生碎片，对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，VACHAR还有一个记录长度的额外字节。

 

日期和时间类型：

　　MySQL能存储的最小时间粒度为秒。

 

DATETIME :

　　这个类型能保存大范围的值，从1001年到9999年，精度为秒。

　　它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中。默认显示格式为"2008-02-16 22:37:08"

　　它与时区无关。

 

TIMESTAMP：

　　保存从1970年1月1日午夜依赖的秒数，它和UNIX时间戳相同。只能表示从1970年到2038年。TIMESTAMP因为空间占用小，所以效率更高。

　　MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

　　它和时区有关。

　　默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

　　应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

 

 

范式的优点和缺点：

优点：

　　范式化的更新操作通常比反范式化要快

　　修改更少的数据

　　范式化的表通常表更小，可以更好地放在内存中，所以执行操作会更快

缺点：

　　通常需要关联查询，不仅代价昂贵，也可能使一些索引策略无效

 

反范式的优点和缺点：

优点：

　　很好的避免关联，更有效的使用索引策略

缺点：

　　范式的优点，就是反范式的缺点

 

第5章 创建高性能的索引

5.1 索引基础

　　索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也很重要，因为MySQL只能高效地使用索引的最左前缀列。

　　在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，没有统一的标准：不同存储引擎工作方式不同。

 

B-Tree索引：

　　也就是通常所指的索引。

　　InnoDB使用B+Tree这种数据结构。

　　B+Tree：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

　　适合查找范围数据。比如:找出所有以I到K开头的名字。

　　

可以使用B-Tree索引的查询类型，以多列索引key(last_name, first_name, dob)为例：

• 全值匹配：指定查询的人的fitst_name, last_name和dob;
• 匹配最左前缀：查找指定了last_name的记录；
• 匹配列前缀：匹配某一列的值的开头部分，比如last_name以J开头；
• 精确匹配某一列并范围匹配另一列：查找last_name为Allen，并且first_name以k开头的；
• 只访问索引的查询：B-Tree通常可以支持只访问索引的查询，即查询只需要访问索引，而无需访问数据行。

 

B-Tree的一些限制：

1. 如果不是按照从最左列开始查找，则无法使用索引。例如无法查找只指定了first_name或者dob的记录；
2. 不能跳过索引中的列：不能在查找的时候只指定了last_name和dob，那么dob不会使用索引。
3. 如果查询的时候有某个列的查询范围，则其右边的所有列都无法使用索引优化查找。比如对last_name使用了like，那么first_name和dob将不会使用索引。

 

哈希索引：

　　基于哈希表实现，只有精确匹配索引所有列的查询才有效。

　　因为它对每行中的所有索引列计算出一个哈希码，作为哈希表的键（原理是基于拉链法的解决碰撞的策略）。

　　在MySQL中只有Memory引擎显式地支持哈希索引，Memory引擎同时也支持B-Tree索引。

　　InnoDB中的自适应哈希索引：某些索引值使用非常频繁时，会在内存中基于B-Tree索引只上再创建一个hash索引。

 

哈希索引的一些限制：

1. 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能用索引中的值来避免读取行；
2. 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
3. 哈希索引不支持部分列匹配查找，因为它用所有索引列来计算得到哈希值。
4. 索引列只支持等值查询，理由同上；
5. 哈希索引数据查找非常快，除非有很多哈希冲突；
6. 如果哈希冲突比较高，一些索引维护操作的代价也会很高。比如性别字段，冲突会很高。

空间数据索引(R-Tree)：

　　MyISAM表支持空间索引，可以用作地理数据存储。

　　空间索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

　　必须使用 GIS 相关的函数来维护数据。

 

 

全文索引：

　　它查找的是文本中的关键词，而不是直接比较索引中的值。

　　使用 MATCH AGAINST，而不是普通的 WHERE。

 

5.2 索引的优点

　　索引大大减少了服务器需要扫描的数据量。

　　索引可以帮助服务器避免排序和临时表，（B+Tree 索引是有序的，可以用来做 ORDER BY 和 GROUP BY 操作）。

　　索引可以将随机IO变为顺序IO，（B+Tree 索引是有序的，也就将相邻的列值都存储在一起）。

 

　　对于小型的表，使用全表扫描更高效。

　　对中到大型的表，使用索引非常有效。

　　对于特大型的表，建立和使用索引的代价会随之增长。这种情况下可以使用分区来查出一组数据，而不是一条一条地匹配。

5.3 高性能的索引策略

独立的列：

　　如果查找中的列不是独立的，则MySQL不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。比如

　　select * from actor where actor_id + 1 = 5;
　　select * from actor where to_days(current_date) - to_days(col_day) <= 10;

前缀索引和索引的选择性：

　　索引很长的字符串会让索引变得大且慢。通常可以只索引开始部分的字符，这样可以节约索引空间，从而提高索引的效率。

　　缺点是会降低索引的选择性。索引的选择性是指不重复的索引值和记录总数的比，显然越大越好。

　　所以，我们需要选择足够长的前缀来保证选择性，同时又不能太长以降低索引空间。

多列索引：

　　常见的错误是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

 

选择合适的索引顺序：

　　如果要对多个列建立一个索引，除了上面的问题之外，还应该考虑所建的索引中列的顺序。

　　不考虑排序和分组时，可以将选择性高的列放在前面。

 

聚簇索引：

　　聚簇索引不是一种单独的索引类型，而是一种数据存储方式。

　　InnoDB的聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行。

　　因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

　　叶子页包含了行的全部数据，但是节点列只包含索引列。

　　InnoDB通过主键聚集数据，如果没有主键就选择一个唯一的非空索引，如果没有这样的索引，就隐式定义一个主键作为聚簇索引。

 

聚簇的优点：

1. 将相关数据保存在一起，减少磁盘I/O。
2. 数据访问更快。因为数据和索引保存在一起。
3. 使用覆盖扫描的查询可以直接使用页结点中的主键值。

缺点：

　　聚簇索引最大限度提高了 I/O 密集型应用的性能，但是如果数据全部放在内存，就没必要用聚簇索引。

　　插入速度严重依赖于插入顺序，按主键的顺序插入是最快的。

　　更新操作代价很高，因为每个被更新的行都会移动到新的位置。

　　当插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，页分裂会导致表占用更多的磁盘空间。

　　如果行比较稀疏，或者由于页分裂导致数据存储不连续时，聚簇索引可能导致全表扫描速度变慢。

　　二级索引可能比想象更大，因其包含了引用行的主键列；

　　二级索引需要两次查找，而不是一次。

 

　　使用InnoDB应当尽可能地按主键顺序插入数据，并尽可能地使用单调的聚簇键的值来来插入新行。

　　最好避免随机的聚簇索引，特别是对于IO密集型的应用。因为随机插入的时候，需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这回增加很多额外的工作，并导致分布不够优化。

 

覆盖索引：

　　如果一个索引包含了所有需要查询的字段的值，就称之为覆盖索引。

　　MySQL只能使用B-Tree索引做覆盖索引。

　　对于 InnoDB 引擎，若二级索引能够覆盖查询，则无需访问聚簇索引。

 

　

按索引扫描来排序：

　　生成有序结果的两种方式：排序，按索引顺序扫描。

 

下面是一些例子，假设索引是(col1, col2, col3)，那么：

...where col1 = 1 order by col2, col3;(√)
...where col1 = 1 order by col2;(√)
...where col1 > 1 order by col1, col2;(√)

...where col1 > 1 order by col2, col3;(X)
...where col1 = 1 order by col2 desc, col3 asc;(X)
...where col1 = 1 order by col2, col4;(X)
...where col1 = 1 order by col3;(X)
...where col1 = 1 and col2 in(1,3) order by col3;(X)

冗余和重复索引:

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。常见的错误有：

1. 使用主键和唯一约束时与已有的索引冲突，因为主键和唯一约束是通过索引来实现的，如果再定义索引就会冗余；
2. 若创建了索引(A,B)再创建索引(A)则冗余，而索引(B,A)和(B)不是，因为(B)不是最左前缀。

 

索引与锁:

　　InnoDB只有在访问行的时候才会对其加锁，而索引能够减少访问行的次数，所以索引能减少锁的数量。

 

第6章 查询性能优化

6.1 为什么查询速度会慢

　　查询是一个任务，它有很多子任务构成，每个子任务都需要消耗一定的时间。

　　优化查询的目的就是减少和消除这些操作所花费的时间。

 

查询的生命周期大致顺序：

　　从客户端，到服务器，然后在服务器上执行解析，生成执行计划，执行，然后将结果返回给客户端。

 

6.2 慢查询基础：优化数据访问

　　确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能访问了太多的列。

　　确认Mysql服务器层是否在分析大量超过需要的数据行。

 

是否向数据库请求了不需要的数据：

　　查询不需要的记录：

　　　　比如查了100行，但是在后端代码中只取了前10行。

　　　　只查询需要的记录，在相关查询后加上limit。

　　多表关联时返回全部列：

　　　　多表关联时会从磁盘中读取全部列然后在内存中进行计算，非常低效。

　　总是取出全部列：

　　　　不要用select *，这会取出全部列，会让优化器无法完成索引覆盖扫描这类优化。

　　重复查询相同的数据：

　　　　当查询重复的数据时，可以考虑将数据缓存起来，需要是时候从缓存取。

 

Mysql是否在扫描额外的记录：

对于Mysql，最简单的衡量查询开销的三个指标为：

　　响应时间

　　扫描的行数

　　返回的行数

 

　　这三个指标会记录到Mysql的慢日志中

　　所以检查慢日志记录是找出扫描行数过多的查询的好办法。

 

一般Mysql能够使用三种方式应用WHERE条件，从好到坏依次为：

　　在索引中使用WHERE条件来过滤不匹配的记录，这是在储存引擎中完成的。

　　使用索引覆盖扫描(explain语句的extra列出现了Using index)来返回记录。直接从索引中过滤不需要的记录并返回命中的结果，这是在Mysql服务器层完成的，但无须再回表查询记录。

　　从数据表中返回数据，然后过滤不满足的条件(explain语句的extra列出现了Using where)。这在Mysql服务器层完成的，Mysql需要从数据表读取记录然后过滤。

 

如果发现查询需要扫描大量的数据但只返回少量的行，通常可以尝试下面的技巧去优化它：

　　使用索引覆盖扫描，把所有用到的列都放到索引中，这样储存引擎无须回表获取对应行就可以返回结果了。

　　改变库表结构，使用单独的汇总表，比如count()时。

　　重写这个复杂的查询，让Mysql优化器能够以更优化的方式执行这个查询。

 

6.3 重构查询的方式

1.一个复杂查询还是多个简单查询

　　有时候，将一个大的复杂查询分解为多个小查询往往效果会更好。

　　Mysql从设计上让链接和断开链接都是轻量级的操作，在返回一个小的查询结果时很高效。

 

2.切分查询

　　将大查询分为多个小查询，每个查询功能完全一样，只完成一小部分。

　　比如：定期的清楚大量数据时，如果一个大的语句一次性完成的话，则可能一次锁住很多数据，占满整个事物日志，耗尽系统资源，阻塞很多小的但重要的查询。

　　这个时候可以分多次删除，每次删除一部分数据，删除一次暂停一会再接着做下一次删除，可以将服务器上一次性的压力分散到一个更长的时间段，性能会更好。

 

3.分解关联查询

　　将一条关联多个表的sql语句通过应用程序拆分成多个操作单个表的语句。

分解关联查询方式的优势：

　　让缓存效率更高，应用程序可以缓存了单表的结果对象，然后下次直接使用这个缓存的结果，这样就跳过了一次查询。

　　将查询分解后，执行单个查询可以减少锁的竞争

　　在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

　　查询本身效率也会有提升

　　可以减少冗余记录的查询：在应用层做关联，意味着某条记录只需要查询一次，而在数据库做关联，则可以会重复返回一部分数据。

　　这样分解相当于在应用中实现哈希关联，而不是使用数据库的嵌套循环关联

 

 

6.4 查询执行的基础

当向mysql发送一个请求的时候，mysql做了什么：

　　客户端发送一条查询给服务器

　　服务器先查询查询缓存，若缓存命中，则立刻返回,否则进入下一阶段

　　服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划

　　Mysql根据优化器生成的执行计划，调用储存引擎的API执行查询

　　将结果返回给客户端

 

　　Mysql客户端和服务器之间的通信协议是”半双工”的

 

查询优化器：

　　一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就好找到其中最好的执行计划。

　　Mysql使用基于成本的优化器，它尝试预测一下查询使用某种计划时的成本，并选择其中最小的一个。

　　选择的执行计划并不一定是最优的，因为是估算，可能有偏差。

 

Mysql能够处理的优化类型：

• 重新定义关联表的顺序
• 将外连接转化为内连接
• 使用等价变换规则，如（5=5 and a>5）改为a>5
• 优化COUNT() MIN() MAX():如要找某列的最小值，只需查B-Tree中最左端的记录
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表IN()的比较：mysql将in()列表中的数据先进行排序，然后通过二分查找的方式然定列表中的值是否满足条件

 

排序优化：

　　排序是一个成本很高的操作，从性能考虑，应尽可能避免排序或尽可能避免对大量数据排序。