高性能MySQL读书笔记

高性能MySQL

第一章 MySQL架构

MySQL最重要、与众不同的特性时它的存储引擎架构，这种架构的设计将查询处理及其他系统任务和数据存储、提取相分离。
这种处理和存储的设计可以在使用时根据性能、特性以及其他需求来选择数据的存储方式

mysql服务器逻辑架构图

最上层并不是MySQL独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构，如连接处理、授权认证、安全等
第二层、查询解析、分析、优化、缓存以及所有内置函数。所有夸存储引擎的功能都在这一层实现：存储过程，触发器，视图
第三层包含了存储引擎

1.2 并发控制

服务器层
存储引擎层
1.2.1 读写锁
- 共享锁/排他锁或者读锁/写锁
1.2.2 锁粒度
所谓锁策略，就是在锁的开销和数据的安全性之间寻求平衡。
- 表锁
是锁最基本的锁策略，并且开销最小的策略。
服务器会为alter table之类的语句使用表锁，而忽略存储引擎的锁机制
- 行级锁
最大程度的支持并发处理，同时也带来了最大程度的锁开销。
1.3 事务
一组原子性的sql查询，或者说一个独立的工作单元。
- A
- C
- I
- D
1.3.1 隔离级别
- Read Uncommitted
- Read Committed
- Repeatable Read
可重复读隔离级别还是无法解决另外一个幻读的问题：当某一个事务在读取某个范围内的记录时，另一个事务由在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行
- Serializable

1.3.2 死锁

1.3.3 事务日志

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录持久化在硬盘上事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域的顺序I/O，
Wtite ahead Logging

1.3.4 Mysql中的事务

Mysql默认采用autocommit模式

SET TRANSACTION ISOLATION LEVEL  
设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
设置数据库的隔离级别

隐式锁和显式锁

Innodb采用的是两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行commit或者rollback的时候才会释放。并且所有锁在同一时间释放

显式锁

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

## 1.4 多版本并发控制
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能不一样。

REPEATABLE READ隔离级别下MVCC实现

SELECT 
 a.InnoDB查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改的。
 b.行的删除版本要么为定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
INSERT
    Innodb 为新插入的每一行保存当前系统版本号作为行版本号
DELETE
    InnoDB 为删除的每一行保存当前系统版本号作为删除标识
UPDATE
    InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

第三章服务器性能你剖析

第四章 Shcema与数据类型优化

逻辑设计物理设计和查询执行

4.1 选择优化的数据类型

避免null

null对于mysql来说更难优化，因为可为null的列使得索引、索引统计信息和值都更复杂

DateTime 和Timestamp都可以存储相同类型的数据，时间和日期，精确到秒。而Timestamp只使用DateTime一般的存储空间，并且会根据时区变化，具有自动更新的能力。

4.1.1 整数类型

存储空间（bit）
TINYINT 8
SMALLINT 16
MEDIUMINT 24
INT 32
BIGINT 64

4.1.2 实数类型

尽量值在对小数进行精确计算时才使用decimal

4.1.3 字符串类型

varchar 和 char

VARCHAR
用于存储可变长的字符出串，比定长类型更节省空间，。如果使用ROW_FORMAT=FIXED的话，每一行都会使用定长存储。

VARCHAR需要使用1或2个额外字节记录字符串长度。
但是由于时变长，在update时可能使得行变得比原来长，这就导致需要额外的工作。如果一个行占用的空间增长，并且页内没有更多空间存储时，不同存储引擎处理方式不同。
MyISAM会将行拆成不同的片段存储。 InnoDB需要分裂页来使得行可存储。

CHAR
mysql根据定义的字符串长度分配足够的空间，当存储char值时，mysql会删除所有末尾空格。

使用枚举代替字符串类型

mysql在内会将每个值在列表中的位置保存为证书，并在.frm文件中保存数字-字符串的映射关系
枚举字段时按照内部存储的证书而不是定义的字符串进行排序的。

4.2 MYSQL schema设计中的陷阱

太多的列
太多的关联

“实体-属性-值”设计模式是一个常见的糟糕的设计模式，尤其是在MySQL下不能靠谱的工作。 mysql限制了每个关联操作最多只能有61张表

全能的枚举
变相的枚举

4.3 范式和反范式

在范式化的数据库中，每个事实数据都会出现并且只出现一次。相反在反范式化的数据库中，信息时冗余的，可能会存储在多个地方。

4.3.1 范式的优点和缺点

范式的更新操作通常比反范式快
数据比较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
范式化的表通常更小，可以更好的存放在内存里，所以执行操作会更快
很少由多余的数据意味着检索列表数据需要更少的distinct或者group by
范式化的schema缺点是需要关联，

4.3.2 反范式的优点和缺点

可以很好的避免关联
如果不需要关联，对大部分查询最差的情况，即使没有使用索引，是全表扫描。当数据比内存大时，这可能比关联的要快得多，避免了随机i/o

4.5 加快alter table操作的速度

mysql执行大部分修改表结构操作的方法时用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表

修改.frm文件

列的默认值实际上存放在.frm文件中，所以可以直接修改这个文件而不需要改动表本身。

alter colum 操作改变列的默认值，而不涉及改表的数据。

4.6 总结

尽量避免过度设计
使用小而简单的合适数据类型
尽量使用象通的数据类型存储相似或相关的值
注意可变长字符串，其在临时表和排序是可能导致悲观的按最大长度分配内存
使用整形定义标识列
小心使用enum和set

第五章创建高性能的索引

5.1 索引基础

5.1.1 索引的类型

在mysql中，索引实在存储引擎而不是在服务器层实现的

B-TREE

B-TREE通常意味着所有的值都是按顺序存储，并且每一个叶子页到根的距离象通

B-TREE索引

B-TREE对索引列的顺序组织存储的，很适合查找范围数据

可以使用b-tree索引的查询类型：全键值、键值范围或键前缀查找

全值匹配
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询（索引覆盖）

关于B-tree索引的限制

如果不是按照索引的最左列开始查找，则无法使用索引
不能跳过索引中的列
如果查询某个列的范围查询，其右边所有的列都无发使用索引优化

哈希索引

mysql中只有memory引擎显式支持哈希索引

哈希索引的限制

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
哈希索引不能用于排序
无法用域范围查找
只支持等值比较

Innodb引擎有一个特殊功能叫做自适应哈希：当Innodb注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引值上再创建一个哈希索引。这样就让B-tree索引也具有哈希索引的一些优点

空间数据索引

全文索引

5.2 索引的优点

大大减少了服务器需要扫描的数据量
所以可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

relational  database index design and the optimizers
---by Tapio lahdenmak and Mike leach

索引并不总是最好的工具
- 对于非常小的表，大部分情况下简单的全表扫描更有效
- 对于中到大型的表，索引就非常有效
- 对于特大型的表，建立和使用索引的代价将随之增长，可以使用分区技术

5.3 高性能的索引策略

5.3.1 独立的列

如果不是独立的列， mysql就不会使用索引，独立的列时指索引列不能是表达式的一部分

5.3.2 前缀索引的索引选择性

索引选择性（cardinality）是指不重复的索引值和数据表总记录的比值

select count(*) as cnt LEFT(city,7) as pref from sakil.city_demo group by pref order by cnt desc limit 10;

通过统计发现前缀长度到达7的时候，再增加前缀长度选择性提升的幅度已经很小了。
创建前缀索引
alter table sakila.city_demo add key(city(7));

5.3.3 多列索引

当出现服务器对多个索引做香蕉操作时，意味着需要一个包含所有相关列的多列索引
当服务器需要对多个索引做联合操作时，通常需要耗费大量cpu和内存资源，再算法的缓存、排序和合并上
优化器不会把这些计算到查询成本上，优化器只关心随机页面读取，这会使得查询成本被低估。

5.3.4 选择合适的索引列顺序

索引从最左列进行匹配：
选择性最高的列放到索引最前列

但是性能不只是依赖于所有索引列的选择性，也和查询条件和具体值有关，也就是和值的分布有关。

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而时一种数据存储方法。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上再同一个结构中保存了B-TREE索引和数据行

当由聚簇索引时，它的数据行实际上存放在索引的叶子页中。聚簇表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
Innodb过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
InnoDB只聚集再同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

聚簇索引的一些优点

可以把相关数据保存在一起，降低i/o次数
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点

极大提高了i/o密集型应用的性能，但是如果数据全部都放在内存中，则访问顺序就没那么重要了，聚簇索引也就没有优势了
插入速度严重依赖于插入顺序，按照逐渐的顺序插入时加载数据到InnoDB表中速度最快的方式，但是如果不是按照主键顺序加载数据，那么再加载完成后最好使用optimize table命令重新组织以下表
更新聚簇索引列代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
基于聚簇做因的表插入新航，或者主键被更新导致需要移动时，可能面临页分裂的问题
聚簇索引可能导致全表扫描变慢，尤其是比较稀疏，或者由于页分裂导致数据存储不连续的时候
耳机索引可能比想想的更大，因为在二级索引的叶子节点包含了引用行的主键列
二级索引访问需要两次索引查找

InnoDB和myisam的数据分布对比

MyISAM的数据分布非常简单，按照数据插入的顺序存储在磁盘上。

myIsam中主键和其他索引在结构上并没有什么不同

myisam索引结构图

InnoDB

聚簇索引就是表，所以不想myISAM那样需要独立的行存储
聚簇索引的每一个叶子节点都包含了主键值、事务Id，用于事务和MVCC的回滚指针以及所有剩余列
innodb的二级索引的叶子节点中存储的不是行指针，而时主键值，并以此作为指向行的指针。这样的策略减少了出现行移动时或者数据页分裂时二级索引的维护工作

Innodb表的主键分布

聚簇索引和非聚簇表对比

在InnoDB表中主键顺序插入行

AUTO_INCREMENT

最好避免随机的聚簇索引

随机写入的缺点

写入的目标也已经刷到磁盘上并从缓存中移除，或者时还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘中读取目标页到内存中，这将导致大量的随机I/O
因为写入时乱序的，InnoDB不得不频繁的做页分裂
由于频繁的页分裂，页会变得稀疏并且不被规则的填充，所以最终会有碎片

随机值载入聚簇索引之后，也需要做一次OPTIMIZE_TABLE 来重建表并优化页的填充

5.3.6 覆盖索引

如果一个索引包含所有查询字段的值，我们就称之为覆盖索引

覆盖索引带来的好处

索引条目通常远小于数据行大小
索引是按照列值顺序存储的
一些存储引擎，如myISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题，尤其那些系统调用占用了数据访问中最大开销大的场景
INNODB中如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

当发起一个被索引覆盖的查询时，在EXPLAIN的extra列可以看到using index 的信息。

索引无法发起覆盖查询的原因

没有任何索引能够覆盖这个查询
mysql不能再索引中执行like操作

5.3.7 使用索引扫描来排序

MYSQL 有郎中方式可以生成有序结果

通过排序操作
按索引顺序扫描

如果explain 出来的type列值为index 则说明mysql使用了索引扫描来排序

扫描索引本身时很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不没扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其时I/O密集性的工作负载
只有当索引列的顺序和order by子句顺序完全一致时，并且所有列排序方向一样时， mysql才能使用索引对结果做排序。
如通查询需要关联多张表，则只有当order by 子句引用的字段全部是第一个表示，才能使用索引做排序。

order by 子句和查找型查询的限制是一样的，需要满足索引最左前缀的要求，否则，mysql都需要执行排序操作，而不发使用索引排序

有一种情况order by 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，

5.3.11 索引和锁

InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的次数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。

5.6 总结

选择索引和编写利用这些索引查询时

单行访问时很慢的，，如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作，最好读取块中能尽可能包含所需要的行
按顺序访问方位数据是很快的，第一顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需要顺序读取数据，就不需要额外的排序工作
索引覆盖擦汗寻时很快的

第六章查询性能优化

查询优化、库表优化、索引优化

查询的生命周期大致可以按照顺序来看：从客户端、服务器、然后在服务器上进行解析、生成执行计划、执行、返回结果给客户端。
其中执行可以认为是整个生命周期中最重要的阶段，这其中包括爱了大量为检索数据到存储引擎的调用以及调用后的数据处理、包括排序、分组。

在完成这些任务的时候查询需要在不同地方花费时间，包括网络、CPU计算、生成统计信息和执行计划、锁等待等操作。尤其是像底层存储引擎检索数据的调用操作。这些调用需要在内存操作、cpu操作和内存不足时导致的I/O操作上消耗时间，根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

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

1、确认应用程序是否在检索大量超过需要的数据
2、确认mySQL服务器是否在分析大量查过需要的数据行

6.2.1 请求了不需要的数据

查询不需要的记录
在查询后加上limit
多表关联时返回全部列
总是取出全部列
查询相同数据（加缓存）

6.2.2 mysql是否在扫描额外记录

响应时间
服务时间和排队时间
扫描行数
返回行数
理想情况下扫描的行数和返回的行数应该是相同的但是在做关联查询时，服务器必须扫描多行才能生成结果集中的几行，扫描的行数对放回的行数比例一般在1：1到10：1之间
扫描的行数和访问类型

访问类型：
全表扫描
索引所秒
范围扫描
唯一索引扫描
常数引用

一般mysql能够使用如下三种方式应用where条件

在索引中使用where条件来过虑不匹配的记录，这是在存储引擎层完成的
使用索引覆盖来返回记录，直接从索引中过滤不需要的记录并返回命中结果，这是在mysql服务层完成的无需再回表查询
从数据表中返回数据，然后过滤不满足条件的记录（Using where)这在mysql服务器层完成

6.3 重构查询的方式

6.3.1 一个复杂的查询还是多个简单查询

6.3.2 切分查询

 delete from messages where created < DATE.SUB(now(),INTERVASL 3 MONTH);

 rows_affected = 0
 do {
     row_affected = do_query(
         "delete from messages where created < DATE_SUB(NOW(),INTERVAL 3 MONTH ) limit 1000")
} while rows_affected > 0

6.3.3 分解关联查询

优势

让缓存的效率更高。对于mysql的查询缓存来说秒如果关联中某个表发生了变化，那么就无法使用查询缓存了。而差分之后，如果某个表很少改变，基于该表的查询就可以重复利用
查询分解后，执行单个查询可以减少锁的竞争
在应用层关联，可以更容易对数据库有进行拆分，更容易做到高性能和扩展性
可以减少荣誉记录的查询

6.4 查询执行的基础

查询执行路径

客户端发送一条查询给服务器
服务器先检查查询缓存
服务端进行sql解析、预处理、再由优化器生成对应的执行计划
mysql根据优化器生成的执行计划，调用春初引擎的api来执行查询
将结果返回给客户端

6.4.1 MySQL客户端/服务器通信协议

半双工通信协议，意味着在任何时刻，要么是服务器想客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
也意味着，无法进行流量控制。一旦一段开始发送消息，另一端要接受完整消息才能响应它。

查询状态

SHOW FULL PROCESSLIST
查看当mysql的状态

sleep
线程正在等待客户端发送新请求
query
线程正在执行查询或者正在将结果发送给客户端
locked
再mysql服务器层，该线程正在等待表锁，在存储引擎级别实现的锁，例如InnoDB的行锁是不会体现在线程状态中。
analyzing and statistics
线程正在收集存储引擎的统计信息，并生成查询执行计划
copying to tmp table
线程正在执行查询，并且将其结果都复制到一个临时表中，这个状态一般是在做GROUP BY操作、要么是文件排序操作或者时UNION操作
sorting result
线程正在对结果进行排序
sending data

6.4.2 查询缓存

6.4.3 查询优化处理

这个阶段包括：解析SQL，预处理，优化SQL执行计划

语法解析器和预处理

查询优化器

mysql能够处理的优化类型

重新定义关联表的顺序
将外连接转化成内连接
使用等价变换规则
优化count、min、max
预估并转化为常数表达式
覆盖索引扫描
子查询优化
提前终止查询（limit）
等值传播
列表in（）
in()完全等同于多个or条件子句。mysql将IN()列表的数据进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。这是一个O(logn)复杂度的操作

等价转化成or语句复杂度为O(N)

数据和索引的统计信息

mySQL如何执行关联查询

嵌套循环关联

UNION为例， mySQL首先将一系列的单个查询结果放到一个临时表中，然后重新读出临时表的数据完成union操作

当前mysql关联执行的策略很简单，mysql对任何关联执行嵌套循环关联操作，即mysql先在一个表中循环读出单条数据，然后再嵌套循环到下一个表中寻找匹配的行。依次下去。

全外连接就无法通过嵌套循环和回溯的方式完成，这是当发线关联表中没有找到任何匹配行的时候，则可能是因为关联时恰好从一个没有任何匹配的表开始

执行计划

mysql执行计划总是一个左侧深度优先的树

排序优化

排序是一个成本很高的左槽，从性能角度考虑，应该尽可能避免排序或者尽可能避免对大量数据进行排序。
当不能通过索引进行排序时，mysql需要自己进行排序，如果数据量小则再内存中进行，如果数据量大则需要使用磁盘，不过mysql将这个过程统一称为文件排序(file sort)

6.4.4 查询执行引擎

6.4.5 返回结果给客户端

mysql将结果返回给客户端是一个增量、逐步返回的过程

6.5 mysql查询优化器的局限性

6.5.1 关联子查询

IN的查询
例子

select * from sakila.file where film_id in ( select file_id from sakila.film_actor where actor_id = 1);

实际上mysql会将象关的外城表压到子查询，它认为这样可以更高效率地查找到数据行

select * from sakila.file where exists(select * from sakila.film_actor where actor_id = 1 and film_actor.film_id = film.film_id);

可以改写这个查询为

select film.* from sakila.file INNER JOIN sakila.film_actor USING(film_id) where actor_id = 1;

使用IN（）加子查询性能可能回很差，所以通常建议使用exists()等效改写查询获得更好的效率

6.5.5 并行查询

mysql无法利用多核特性来并行执行查询。尽管其他很多关系型数据库能够提供这个特性

6.5.9 再同一个而表上查询和更新

mysql不逊于对同一张表同时进行查询和更新

6.7 优化特定类型地查询

6.7.1 优化count

统计列值的数量（非null）
统计行数

通常count（）需要扫描大量的数据
“快速、精确和实现简单”三者永远只能满足其二

6.7.2 优化关联查询

确保on 或者using子句中的列上索引。再创建索引的额时候急需要考虑到关联的顺序。
确保任何group by 和 order by 中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引优化这个过程