注：由于互联网行业普遍使用MySQL，以下内容无特殊注明均面向MySQL
MyIsam和Innodb差异对比
| MyIsam | Innodb | |
|---|---|---|
| 存储文件 | .frm（表定义文件）、.myd（数据文件）、.myi（索引文件） | .frm（表定义文件）、.ibd（数据文件） |
| 锁 | 表锁 | 表锁、行锁 |
| 事务 | 不支持 | ACID |
| count | 专门存储的地方 | 扫表 |
性能调优
1、explain 分析执行计划
Id - 第几层执行计划
Select_type
- Simple
- Subquery
- …
Type - 依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，
unique_subquery，index_subquery，range，index_merge，index，ALL- system const的特例，仅返回一条数据的时候。
- const 查找主键索引，返回的数据至多一条（0或者1条），属于精确查找 。
- eq_ref 查找唯一性索引，返回的数据至多一条。属于精确查找。
- ref 查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条 。
- range 查找某个索引的部分索引，一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行，属于范围查找 。
- index 查找所有的索引树，比ALL要快的多，因为索引文件要比数据文件小的多。
- ALL 不使用任何索引，进行全表扫描，性能最差。
Key - 使用的索引
key_len - 索引键长度
Rows - 记录条数
Extra - 额外细节信息
2、只取需要的列
数据列占空间，排序占空间
3、索引
3.1 索引的类型
按聚集分类
索引可以分为聚集索引、非聚集索引。
- 一个表只有一个聚集索引（通常是主键），聚集索引直接在叶子节点上保存数据；
- 非聚集索引的叶子节点不保存数据，只保存主键，需要通过主键再在聚集索引上查找数据，所以需要二次查询。
按实现算法分类
非聚集索引索引类型分为唯一索引、普通索引、全文索引、空间索引，实现方式分为HASH和B+TREE。其中Hash索引无法实现范围查询，只在部分场景下适用。
3.2 索引的数据结构
B+Tree
为什么不使用B-树或红黑树？
对比B-树：
- B+树只有叶子节点有data域，相对B-树更节省空间
- B+数叶子节点之间有链指针相连，遍历叶子节点即可以实现区间访问
对比红黑树：
红黑树（二叉查找树）、AVL树（平衡二叉树）都是二叉树，基本只会在内存中使用，数据库的索引涉及大量的IO操作，树的深度对IO影响很大，B树可以有多个子节点，有效降低树高度。
3.3 索引的优缺点：
优点：
查询效率高
缺点：
- 更新索引的IO耗时：插入、重排
- 占用存储空间
不要盲目创建索引
3.4 组合索引
尽量使用组合索引，组合索引满足最左原则，如有组合索引（A,B,C)，可理解为数据库创建了对应的三个索引（A)、（A,B)、(A,B,C)，所以索引A不用独立创建索引，否则浪费存储空间和影响插入、更新的效率。
从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用。比如：
- where a=3 and b=45 and c=5 …. 这种三个索引顺序使用中间没有断点，全部发挥作用；
- where a=3 and c=5… 这种情况下b就是断点，a发挥了效果，c没有效果
- where b=3 and c=4… 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
- where b=45 and a=3 and c=5 …. 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关
3.5 合理索引减少排序性能耗损
多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。
简单理解即组合索引中第二个索引数据可实现自动排序（BTree数据结构会按顺序排列，所以查询时可免去排序运算）
3.6 避免索引失效
索引失效情况：
- like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
- 当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描
- 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作、字段上使用函数。
- 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
4 小表驱动大表
当两个关联字段中只有一个字段有索引的时候：
Select * from A join B on A.id = B.userId where A.name like ‘陈%’
B.userId建立了索引，以A表驱动B表时，先查出A表符合的数据，然后再以表A数据匹配表B，而遍历表B时因为触发索引查询，所以匹配的效率为LogN
假设A表100条数据，B表10000条数据，查询的过程可以理解为
for 100条数据 in A {
Log(10000)
}
查询效率为 100log(10000)，如果以B表驱动A表，则效率为10000log(100)，显然前者性能更优。
假如使用块的嵌套循环连接的话
内存中放小表的I/O代价小于内存中放大表的 I/O代价，数据库系统实现P108，简而言之，经过化简是 B(S)+B(S)B( R)/M-1,由于加号的后面是定值，所以前面的值的话整体就是小的
in和exists的对比:
下面关于大表和小表的讨论均为A是小表
小表放到in之后的效率优于放到in之前
例：select name from B where id in(select id in A);
分析：上面的查询语句可以拆成 首先 select id in A; 其次 select name from B where B.id=A.id;这样的话
小表放到exists之前效率优于放到exists之后
例：select name from A where exitsts (Select * from B where B.id=A.id);
分析：先遍历小表A，取得A中每个值和B中的值进行比对，如果比对成功了返回true
事务
事务的并发问题
1、脏读
事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读
事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读。
（注： 期间事务B 删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条，这种情况归为 不可重复读）
MySQL事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读（读已提交）（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read）（默认） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
MVCC机制
锁机制开销较大，Mysql的可重复读模式是采用MVCC（多版本控制）机制实现，MVCC可以在大多数情况下代替行级锁，能降低其系统开销。
MVCC是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。当我们创建表完成后，mysql会自动为每个表添加 数据版本号（最后更新数据的事务id）db_trx_id 删除版本号 db_roll_pt （数据删除的事务id） 事务id由mysql数据库自动生成，且递增。
