mysql(5.7.x)

ACID

Atomicity

undo log

Consistency

redo log

Isolation

MVCC (版本链和一致性试图)

Durability

隔离级别

• RU：读未提交
• RC：读已提交
• RR：可重复读
• SERIALIZABLE：串行

脏读：事务A读取了事务B更新的数据，B回滚，A读取的为脏数据库
不可重复读 (update)：事务A重复读取一个数据，过程中事务B修改了数据，A则读取的数据前后不一致
幻读 (delete/insert)：事务A将数据改成另一种表达形式，事务B在A的过程插入一条数据依然是原先表达形式，造成幻读情况

Index

聚簇索引：不是单独的索引类型，是一种数据存储方式；即就是将数据和索引放在一起,找到数据就找到了数据，具体细节看实现，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

非聚簇索引 （回表）：将数据存储与索引分开存储，索引结构的叶子节点指向了数据的对应行。当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

primary index
common index
unique
fulltext

索引类型

hash：等值查询较快，范围查询不友好
b+tree：数据存放在叶子节点，指针环形指向

索引策略

• 覆盖索引：针对查询数据为主键索引，无需回表
• 最做前缀原则（组合索引）：多列组合索引，按照索引设置左右顺序执行，如果不按顺序、使用like(%x%) 、> 、< 、<> 等等索引失效
• 索引下推（optimizer_switch）：其实质就是把where条件查询数据交给索引过滤然后在让mysql服务器处理

Engine

InnoDB
MylSAM

Explain

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_account | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

字段	说明	扩展
id	标识符，表示执行顺序	id 相同 从上往下执行，不同从大到小，部分不同从大到小从上倒下
select_type	查询类型	simple 简单的查询没有union； primary 有子查询外部使用primary；union；union result ;
partitions	使用的哪个分区，需要结合表分区才可以看到
type	表示按某种类型来查询，例如按照索引类型查找，按照范围查找。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all	1）const：表中最多有一个匹配行，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快（2）eq_ref：唯一性索引扫描，对于每个来自于前面的表的记录，从该表中读取唯一一行（3）ref：非唯一性索引扫描，对于每个来自于前面的表的记录，所有匹配的行从这张表取出（4）ref_or_null：类似于ref，但是可以搜索包含null值的行，例如：select * from student where address=‘xxx’ or address is null，需要在address建立索引。（5）index_merge：查询语句用到了一张表的多个索引时，mysql会将多个索引合并到一起6）range：按指定范围（如in、<、>、between and等，但是前提是此字段要建立索引）来检索，很常见。如：select * from student where id < 5，id上要有索引（7）index：全”表“扫描，但是是在索引树中扫描，通常比ALL快，因为索引文件通常比数据文件小，index扫描是通过二叉树的方式扫描，而all是扫描物理表。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。例如：select name from student，但name字段上需要建立索引，也就是查询的字段属于索引中的字段。8）all：全表扫描，扫描完整的物理表，此时就需要优化了
possible_key	指出 MySQL 能在该表中可能使用的索引，显示的是索引的名称，多个索引用逗号隔开，如果没有，则为null。
key	MySQL决定实际用到的索引，显示的是索引的名称，多个索引用逗号隔开，如果没有，则为null
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
filtered	通过过滤条件之后对比总数的百分比	给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和执行计划中的前一个表（前一个表就是指id列的值比当前表的id小的表）进行连接的行的数目。
extra	额外的信息，例如：using file sort ，using where， using join buffer，using index等	（1）using where：表示查询使用了where 语句来处理结果（2）using index：表示使用了覆盖索引。这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。（3）using join buffer：这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能（4）using filesort：这是 order by 语句的结果。这可能是一个CPU密集型的过程。using filesort表示出现了文件内排序，表示很不好的现象，必须要优化，特别是大表，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。（5）using temporary：mysql需要创建一张临时表来保存中间结果。 也就是说，需要先把数据放到临时表中，然后从临时表中获取需要的数据。出现这种临时表，也是必须需要优化的地方，特别是数据量大的情况。两个常见的原因是在来自不同表的列上使用了distinct，或者使用了不同的 order by 和 group by 列。