1. 事务

1.1 ACID

1. 原子性（automicity）
   所有操作要么全部成功，要么全部回滚失败。
2. 一致性（consistency）
   数据库总是从一个一致性状态转移到另外一个一致性状态。
3. 隔离型（isolation）
   不同事务之间的相互影响。
4. 持久性
   一旦事务提交，则其所做的修改就会永久保存到数据库中。

1.2 隔离级别

更多信息可以参考：mysql加锁处理分析

1. READ_UNCOMMITED
   无意义
2. READ_COMMITED
   mysql可配置处于该级别。通过MVCC实现快照读不加锁，当前读时会加读/写锁。

• REAPEATABLE_READ
  mysql默认处于该级别，并且在读已提交的基础上通过间隙锁和next-key 锁解决了幻读问题。
• SERIALIZEABLE
  mysql在该级别时都是当前读，所有的读或写都会加读或写锁。但所有的请求并不是完全串行化，比如加读锁时是可以并行化的。

1.3 MVCC

作用：快照读不加锁，快照读和写不冲突。
实现原理：
每行记录后面保存两个隐藏的列，一列保存行的创建事务版本号，一个保存行的删除事务版本号。mysql对于每个新的事务，都会自增得到一个事务版本号。

1. SELECT
   创建时间小于等于当前版本号，而且删除版本号未定义或者大于当前事务版本号。
2. INSERT
   插入行的创建事务版本号为当前事务的版本号
3. DELETE
   删除版本号为当前事务版本号
4. UPDATE
   插入一行新的记录，保存当前系统版本号为创建版本号，同时保存当前系统版本号到原来的行作为删除版本号。

1.4 锁

更多信息可以参考：mysql加锁处理分析

1.4.1 死锁

1. 检测到死锁时回滚持有资源最少的事务，或者新的请求直接报错
2. 什么都不管等待锁超时

锁类型

1. 根据粒度：行锁、表锁
2. 根据互斥类型：读锁、写锁
3. 一般一个DML语句常用的锁：行/索引读锁、行/索引写锁、行/索引间隙锁

2.0 数据类型

2.1 数据类型选择原则

参考自《高性能Mysql》

• 更小的通常更好。（减小IO）
• 简单更好。比如int相对于字符串的比较性能就好很多。
• 通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值（根据后面的解释，我觉得应该是索引列不应该为NULL，普通列可以为NULL*）。因为可为NULL的列使得索引、索引统计和值的比较都更复杂，可为NULL的列会使
  用更多的存储空间，在mysql里也需要特殊处理。

2.2 数据类型

1. 数值
   这里就不详细列出有哪些数值类型了。说下INT(11)的含义，11只是规定了MySql的一些交互工具（例如命令行客户端）用来显示字符的个数，对于存储的大小无影响。
2. 字符串

类型	优缺点
CHAR	固定长度，不适用于可变的场景。相对于VARCHAR不需要专门的字节存储长度，另外update时也不会造成分裂
VARCHAR	可变长度。需要1-2个字节存储长度，另外update时会造成分裂

3. 日期
4. DATETIME
   时间范围是1001-9999，精确到秒，8个字节。不依赖时区。
5. TIMESTAMP
   时间范围1970-2038，精确到秒，4个字节。依赖时区。

3. 范式和反范式

参考自：《高性能MySql》

范式化带来的好处：
范式化的表一般会拆分成多个表，并进行关联。

1. 范式化操作通常比反范式快
2. 范式化的表有很少或者没有重复数据，所以只需要修改更少的数据。
3. 范式化的表通常更小，可以更好的放到内存里，所以执行操作更快。
4. 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

坏处:
因为太范式了，列都拆到了多个表，那么没法通过一个索引完成查询。

4. 索引

参考自《高性能MySql》
索引类型：

1. B+树
   M阶平衡树，每个叶子节点有下一个叶子节点的指针。
   叶子节点存储的是真实列值指针。

3. 一个简单sql可以利用多个索引（索引合并）

4. in之后的索引也可以利用，只是可能会触发多次索引查询。

5. 覆盖索引的优点：避免再去读聚簇索引，每次读聚簇索引都是一次随机IO。

6. 索引排序一方面可以减少不必要行的读取，另一方面避免了排序。

索引覆盖、索引排序、group by、distinct

5.3 高性能的索引策略

1. 独立的列（需要把索引列单独放到比较符号的一侧）
   select actor_id from xxx where actor_id+1=5
2. 索引选择性
   索引列的选择性应该很高，而且值的分布均匀。
3. 前缀索引
   当索引列很大时，需要使用前缀索引，否则索引树也会很高。比如对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。
4. 多列索引（每个列都建索引）
   比如：select xxx,xxx from xxx where a=xxx and(或者or) b=xxx，其中a和b都是索引。
   mysql会使用索引合并策略，即同时使用使用a和b这两个索引列进行扫描，并将结果进行union(or)或者取交集(and)。
   索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：
   • 当出现合并索引策略时，可能需要一个联合索引来减少不必要的行读取。
   • 索引合并策略通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
   • 优化器不会把这些计算到”查询成本“中，优化器只关心随机页面读取。这会使得查询的成本被”嘀咕“，导致该执行计划还不如直接走全表扫描。
5. 选择合适的索引顺序
   需要以索引选择性、业务字段频率、索引排序、group by、distinct这些维度来综合考虑。下面是一个一般法则，具体的策略还是要根据场景具体考虑。
   • 在不考虑排序和分组时，将选择性最高的列放在前面是很好的。
   • 根据运行频率来调整索引顺序。
6. 聚簇索引
   因为相邻的行是按照主键聚集在一起的，索引叫聚簇索引。
   二级索引的叶子节点存储的是主键值，而不是聚簇索引的指针，是为了防止防止聚簇索引的行存储块因为移动之后还要同步修改二级索引。
   聚簇索引最好是单调递增的，比如使用自增主键。否则在插入新行不能利用顺序写特性，会造成：
   • 先读取目标页
   • 目标页分裂，插入新行。这会造成移动大量数据，一次插入最少修改三个页面而不是一个。
   • 由于页频繁分裂，页会变得稀疏，有碎片。
7. 索引覆盖（explain里的extra列出现using index说明是索引覆盖）
   • 避免了再从聚簇索引中读取数据。
8. 索引扫描（过滤）和排序问题
   当一个条件是范围查询时，不代表该联合索引后面的字段就都无效了。可以继续利用后面的字段做精确排序，也可以进行索引扫描过滤数据。
9. 索引排序
   索引排序是怎么起到优化作用的：比如 order by a desc limit 20，如果不使用索引排序，则需要读出很多行数据，使用索引排序之后，直接能确定要读取的最终行数，减少了IO。
   索引排序的要求：
   • 索引列的顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样
   • 如果查询需要关联多张表，则只有order by子句引用的字段全部是第一个表时，才能使用索引做排序。
   • 最左前缀原则
     1. 和查询条件的最左前缀原则有些不同，比如where rental_date>'xxx' order by rental_date,inventory_id是可以索引排序的。
     2. where条件或者join子句中对这些列指定了常量时order by 的里可以不包含这些前缀。比如where rental_date='xxx' order by inverntory_id和where rental_date='xxx' order by rental_date ,inverntory_id是一样的效果。
10. 冗余索引
    大多数时候应该扩展已有索引，而不是新建索引，但需要根据具体的业务情况分析确定。比如原来已有索引a，现在需要一个索引a,b,有两种办法：1. 扩展a成a,b 2. 新建索引a,b。这两种方案各有优劣，要根据具体场景情况具体应用，第一种方案造成a,b索引比a的索引树高会变长，where a=xxx时会变慢；第二中情况会造成多了一个索引，更新时效率会低点。
    怎么发现冗余索引：可以使用Percona Tookit中的pt-duplicate-key-checker工具。
11. 未使用的索引
    可以使用Percona Tookit中的pt-index-usage工具，或者通过INFORMATION_SCHEMA.INDEX_STATICS查看每个索引的使用频率来找到无用的索引。
12. 索引和锁
    当前读时如果扫描了过多的行，会出现多余的加锁，影响性能和并发。具体的影响可以参考：
    mysql加锁处理分析
13. 一个索引来支持多种过滤条件

• 技巧1 范围查询尽量放在最后，比如age
• 技巧2 当where没有指定前缀的某个索引时，可以使用in的方式,但是in里面的值不应该太多，否则优化器需要做的租户将以指数形式增加。比如， sex in (0,1)

like xxx%可以转化成比较操作。
为什么全表扫描会很快：

1. 扫描索引本身是很快的，因为值需要从一条索引记录移动到紧接着下一条记录（顺序读）。但如果索引不能覆盖全部所需的列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢。
   数据库的核心优化点在于减少IO，尤其是随机IO。

6. 查询性能优化

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

• 请求了不需要的行
• 请求了不需要的列。会导致无法使用索引覆盖等特性。但是也不能为了mysql的优化在任何时候都只返回特定的几列，因为在很多时候为了在应用层增加cache是需要mysql返回所有的列并cache，需要整体平衡。
• 由于查询方式或者索引问题等，导致mysql扫描的行数相对于返回的行数过多。
  一般mysql能使用如下三种方式应用where条件，从好到坏依次为：
  • 全部索引等值查询
  • 从索引中过滤不需要的列
  • 服务器层过滤（using where）

6.2 一个复杂查询还是多个简单查询

一个复杂查询拆分成多个原子查询的好处：

1. 如果一个大语句一次性完成的话，可能需要一次锁住很多数据（而且事务时间长导致锁的占用时间也长）、占满整个事务日志、耗尽系统资源、阻塞很多小的但很多的查询。
   所以需要拆分成多个查询，而且尽可能的将这些查询分散到不同的时间来执行，以此来降低对服务器的影响。
2. 一方面，拆分成原子查询后可以在应用中cache各原子查询的数据；另一方面，下次来查时如果能直接从cache查询到数据就可以减少mysql的查询。
3. 关联查询拆分成多个原子查询后，实际上是在应用层中做关联，避免了mysql语句直接的关联和耦合，这样可以更容易对数据库进行拆分，更容易做到高性能和扩展。
4. 小查询更容易使用索引的特性和被优化。

6.3 查询执行过程

1. 客户端发送一条查询给服务器
2. 服务器先查询cache，如果命中了cache（mysql使用的是hash算法）则立刻返回结果。否则，进入下一阶段。
3. 服务器端进行sql解析、预处理，再由优化器生成对应的执行计划。
4. mysql根据优化器生成的执行计划，调用存储引擎的api来执行查询。
5. 将结果返回客户端。

然后书上是上面各个步骤的详细说明，比较重要的是《查询优化处理》，需要再看一遍。

6.7 优化特定类型的查询

1. count(*/列)
   select count(*) from xxx即使没有where，因为MVCC原因，也需要全表扫描。
   • 可以使用MYISAM引擎
   • 使用explain的近似值
   • 成本低的索引覆盖扫描，避免聚簇上的全表扫描。
2. min和max
   select max/min(a) from xxx没有where条件而且a上有索引时，是常量时间
   还一个例子参见《高性能mysql》p231中的6.5.8部分
3. 关联查询
   • 当表A和表B用列C关联的时候，如果优化器的关联顺序是B、A，那么久不需要再B表的对应列上建索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
   • 确保任何的group by和order by中的表达式只涉及到一个表中的列，这样mysql才可能使用索引来优化这个过程。
4. group by和distinct
   ...
5. limit
   核心问题是当offset很大时，每次查询都需要扫描大量的行并丢弃。

• 只支持下一页，不支持跳转到第几页。查询下一页时，传入当前页的最大/最小id以where id>xxx limit 10方式来查询。这样的话，offset永远很小。
• limit搜索时先使用成本低的索引查询出要返回的列，然后再查询出这些列的详细信息。

5. UNION
   mysql总是通过创建并填充临时表的方式来执行union查询。
   • 需要将where、limit、order by等子句“下推到”union的各个子句查询中，以便优化器可以充分利用这些条件进行优化。
   • 除非需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL，mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性检查。

6.8 MySql优化策略：

发现

对mysql的各种性能数据进行统计和监控。其中的一些参数是执行时间长、扫描行数/返回行数过小的sql

怎么解决

1. 大Sql拆分成小的 参见6.2
2. 应用层的优化。加cache，在应用层做关联，修改业务逻辑、是否返回了过多的行和列等...
3. 表的结构（范式和数据类型问题）
4. 索引优化