读
1 查看缓存中是否存在id，2 如果有 则从内存中访问，否则要访问磁盘，3 并将索引数据存入内存，利用索引来访问数据,4 对于数据也会检查数据是否存在于内存，5 如果没有则访问磁盘获取数据，读入内存。6 返回结果给用户。
写
1 先写undo log2 在内存更新数据3 记录变更到redo log，prepare4 写入binlog5 redo log 第二阶段，commit6 返回给client如果有slave第4步之后 经过slave 服务线程 io_thread 写到从库的relay log ，再由sql thread 应用relay log 到从库中。 数据库事务
select * from information_schema.innodb_trx;ch查询正在处理的事务
事务四大特性ACID
原子性
通过类似于加锁的机制，将执行语句整体提交
一致性
隔离性
数据快照，事务之间进行隔离
持久性
数据不会凭空增加或者减少 事务类型
扁平事务带保存点的扁平事务链事务嵌套事务分布式事务 事务的并发问题
脏读
事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
幻读
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。
不可重复读
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。 使用事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
beginstart transaction(mysql)commit 伪事务
适用于不支持事务的数据库表中，如MyIsam 使用锁的方式实现事务的效果，
LOCK TABLES table_name read/write，……
UNLOCK TABLES
读方式锁表写方式锁表避免死锁 事务日志 数据库锁 锁粒度
行级锁
开启事务时添加，结束事务时释放，两阶段锁协议
死锁
超时时间死锁机制，事务回滚
热点行
页级锁
开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间，并发度一般
表级锁
lock table read/write
MDL(metadata lock)
自动添加 读锁不互斥 写锁互斥多个事务之前操作，查询时修改字段容易让线程池饱满
行共享 (ROW SHARE)
行排他(ROW EXCLUSIVE)
共享锁(SHARE)
共享行排他(SHARE ROW EXCLUSIVE)
排他(EXCLUSIVE)
全局锁
全库逻辑备份 间隙锁
数据前后
合理使用索引可以减小锁的范围，提高效率
加锁方式
自动锁
显示锁
子主题 1 使用方式
乐观锁悲观锁 操作方式
DML锁DDL锁 读写锁
读锁写锁 锁级别
排他锁共享锁 特点 轻量级 高性能 支持主从部署
MySQL Cluster
行锁机制更好的支持多线程多用户并发。更好的支持读写混合语句以及扩展。可选择磁盘存储介质永久保存数据Shared-nothing和分布式架构保证无单点故障。99.999% 可用性数据自己主动分布在各个节点，应用开发人员无需考虑分区或分片解决方式。支持MEMORY中不支持的变长数据类型（包含BLOB 和 TEXT）。子主题 7 未设置主键时自动生成主键
自增主键多为数字型 索引查找时速度较快 且相较于string类型占用长度更短避免自增主键用尽 存储引擎
存储引擎的本质是一种文件访问机制
MyIsam5.1之前默认使用该存储引擎
索引数据结构
静态索引非聚集叶子节点存储数据地址
适用场景
适合读多写少，并发量低的场景
数据文件
.MYD数据文件.MYI索引文件
存储格式
静态表
表中字段为定长字段存储迅速去掉数据末尾空格
动态表
需要定期执行OPTIMIZE TABLE
压缩表
优缺点
优点
读取速度快
占用内存和存储资源较少
顺序存储
缓存总行数单独存储
count（）比较快
支持BLOB和TEXT的前500个字符索引
支持全文索引
数据和索引单独存储
支持延迟更新索引，极大地提升了写入性能
支持压缩表
缺点
不支持事务插入或更新数据时需要锁表不支持外键不支持崩溃后安全恢复
锁
支持表级锁
自增长
自动增长列必须是索引 InnoDB
默认存储引擎
索引数据结构
B+树
每个节点对应一个page page大小一般为16k， 非叶子结点只有键值-指针，叶子结点包含完整数据
叶子结点为双向链表结构
对非主键字段设置的索引其实也是非聚集索引,他们的 data 域实际上存储的是对应主键的值,当使用非主键字段作为条件查询时,它会读取到它们各自所在的 data 域(即:主键的值),然后再通过查询主键所在位置获取到完整的记录。
聚集
主键索引叶子节点存储数据
数据文件
单个文件 以.ibd结尾索引和数据保存在同一个文件中文件大小受操作系统限制
适用场景
经常更新的表事务多条SQL组合提交
优缺点
优点
查询效率更高支持事务、回滚、崩溃修复支持自动增加列属性支持行级锁，粒度更小 支持更多的并发采用MVCC来支持高并发，有可能死锁
缺点
非主键索引需要二次遍历不支持全文索引需要更多内存和存储空间
锁
支持表级锁支持行级锁
自增长
自动增长列必须是索引如果是组合索引，则必须是组合索引的第一列
清空表操作
truncate table Memory
数据结构
hash索引
B树索引
部分查询通配查询
存储类型
基于内存
优缺点
优点
访问快基于内存 使用 hash索引MEMORY表能够有多达每一个表64个索引，每一个索引16列，以及3072字节的最大键长度MEMORY存储引擎支持HASH和BTREE索引
缺点
服务关闭数据丢失假设删除行。内存表不会回收内存，仅仅有整张表全部删除的时候。才进行内存回收。同一时候仅仅有在同一张表中插入新行时才会使用之前删除行的内存空间。 要释放已删除行所占用的内存空间。能够使用ALTER TABLE ENGINE=MEMORY对表进行强制重建。当内容过期要释放整张内存表。能够运行DELETe 或 TRUNCATE TABLE清除全部行，或者使用DROP TABLE删除表server须要足够内存来维持全部在同一时间使用的MEMORY表
特征
MEMORY支持AUTO_INCREMENT列MEMORY不能包括BLOB或TEXT列.MEMORY表支持INSERT DELAYED非暂时的MEMORY表在全部client之间共享。就像其他不论什么非暂时表。MEMORY表内容存储在内存中，它会作为动态查询队列创建内部暂时表的共享介质MEMORY表最大值受系统变量 max_heap_table_size 限制，默觉得16MB，要改变MEMORY表限制大小，须要改变max_heap_table_size 的值。该值在 CREATE TABLE 时生效并伴随表的生命周期，(当你使用 ALTER TABLE 或 TRUNCATE TABLE命令时，表的最大限制将改变。或重新启动MYSQL服务时, 全部已存在的MEMORY表的最大限制将使用max_heap_table_size 的值重置。当MySQLserver启动时。假设你想填充MEMORY表，你能够使用–init-file选项。比如。你能够把INSERT INTO … SELECT 或LOAD DATA INFILE这种语句放入这个文件里以便从持久稳固的的数据源装载表。假设你正使用复制，当主server被关闭且重新启动动之时，主server的MEMORY表变空。但是从server意识不到这些表已经变空。所以假设你从它们选择数据。它就返回过时的内容 Archive Federated TokuDB
数据结构
Fractal-tree
FIFO队列用来缓存更新操作
数据文件
适用场景
访问频率不高的数据写入性能高 数据文件
数据文件在物理磁盘中并不一定为连续的，因此查询数据需要查找整个磁盘
数据文件其他mysql 8 重做日志
记录数据库变更记录的文件，用于系统异常crash(掉电)后的恢复操作 回滚日志
也存在于mysql 的ibdata文件，用户记录事务的回滚操作 归档日志
事务提交之后，记录到归档日志中。 中继日志
从master 获取到slave 的中转日志文件，sql_thread 则会应用relay log 其他日志 文件访问顺序
对于以上文件的IO访问顺序可以分为顺序访问 比如binlog ，redolog ，relay log是顺序读写，datafile，ibdata file是随机读写，这些IO访问的特点决定了在os 配置磁盘信息时候，如何考虑分区 ，比如顺序写可以的log 可以放到SAS 盘 ，随机读写的数据文件可以放到ssd 或者fio 高性能的存储。
索引 索引目的减少IO操作，提高查询效率 索引原理
减少磁盘寻址时间
减少每次加载的无效数据
索引节点大小为页大小
innondb页大小默认为16k
磁盘扇区大小为页大小的整数倍
将页数据加载到内存中读取
索引结构
hash索引
适用场景：匹配查找
b+索引
适用场景：范围查找数据结构
全文索引
适用场景 索引类型
主键索引
唯一索引
普通索引
联合索引
全文索引
全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
最小搜索长度最大搜索长度
倒序索引
聚集索引
非聚集索引
数据存储方式
聚簇索引
非聚簇索引
稠密索引稀疏索引 查询效果
覆盖索引
用 explain 的结果，extra 列会出现:using index 索引原则
索引列一般为条件筛选
重复度低的列
较小的数据列
索引列不能参与计算
数据较长的列使用前缀索引
多个列同时作为查询条件时使用联合索引
当or操作较多时，建议不使用索引
避免多个索引范围查找
避免冗余索引、重复索引
删除长期未使用索引
权衡索引与ACID的效率问题
最左前缀原则
联合索引
SQL优化使用limit对查询结果的记录进行限定
避免select *，将需要查找的字段列出来
使用连接（join）来代替子查询
拆分大的delete或insert语句
可通过开启慢查询日志来找出较慢的SQL
不做列运算：SELECT id WHERe age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较，比如用’123’和’123’比，123和123比
尽量避免在WHERe子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERe num BETWEEN 1 AND 5
列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大
explain执行计划分析
table
显示这一行的数据是关于哪张表的
type
显示连接使用了何种类型
system
表只有一行
const
表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_reg
在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index
这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL
这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
possible_keys
显示可能应用在这张表中的索引如果为空，没有可能的索引
key
实际使用的索引如果为NULL，则没有使用索引可以在SELECt语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len
使用的索引的长度在不损失精确性的情况下，长度越短越好
ref
显示索引的哪一列被使用了
rows
MYSQL认为必须检查的用来返回请求数据的行数
Extra
关于MYSQL如何解析查询的额外信息
Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists
MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each Record
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used
使用了WHERe从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
分析查询日志
生产环境禁用，仅用于测试环境对生成的SQL语句的执行分析
使用SQL分析工具
小米SOAR
缓存优化
分页查询
利用延迟关联或者子查询优化超多分页场景
优化原则
范围索引放最后
使用join 代替子查询
减少在内存中创建临时表
不要使用count(列名)或count(常量)来替代count(*)
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行
count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题
SELECT IFNULL(SUM(column), 0) FROM table;
使用ISNULL()来判断是否为NULL值
代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句
不得使用外键与级联，一切外键概念必须在应用层解决
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性
对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名(或 表名)进行限定
索引失效
索引列计算
加减乘除函数计算
索引列类型转换
比较符号
<>not innot exits!=
使用* 代替字段
字段类型不同
索引维护页分裂页合并索引长度
XMind - Trial Version
