文章目录
索引数据结构
mysql用的索引数据结构是B+树。
B+树和B树的区别
B树
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
B+树
- 非叶子节点不存储具体数据，只存储索引（冗余）
- 叶子节点包含所有的索引字段
- 叶子节点用指针连接
B+树比B树更适合做索引的原因
- B+树非叶子节点不存储行数据，只存储索引，相同的页（页的概念是硬件的概念）可以存储更多的索引
- 叶子节点包含了所有的索引字段并且指针连接，查询时只需要在叶子节点通过指针查找即可。
- mysql对B+树有优化，叶子节点是双指针的。
B+树作为索引时，查询和顺序插入会很快。但是如果插入的值是无序的，会不停的破坏平衡导致节点分裂或者合并，所以对表的索引个数不宜太多，而且对于删除和更新操作频繁的字段，不建议建立索引。
Hash索引
mysql索引在新建表的时候支持hash索引，hash索引在查询上效率会好一点，但是区间查询必须群表加上hash冲突，所以没啥用。
存储引擎
MylSAM
新建表选择MylSAM索引引擎（非聚簇索引）时，数据在磁盘里索引和行数据是分在两个文件里的，索引文件里存储数据所在磁盘内存地址。
InnoDb索引
InnoDb索引是聚簇索引，表数据文件本身就是按B+树组织的一个索引数据结构，叶子节点包含了完整的数据记录。
mysql推荐InnoDb必须建主键（如果不建，mysql会强制选出一列作为主键），并且推荐使用整型的自增主键，整型的自增主键更容易比较，而且占用空间更小。再参考B+树的数据结构特点，使用自增的时候，就不会破坏本身原有的索引结构。
非主键索引的叶子节点不存储具体数据，存的是主键索引值，再通过主键去查询具体数据（俗称回表），回表会消耗io，回表次数也是衡量mysql执行效率的一个重要维度。
联合索引是一种特殊的非主键索引，会根据建立时的顺序来构建索引，比如索引建立时，是按照（name,age）的顺序建立索引，查询时会先去匹配name，匹配到name后，再去匹配age。所以联合索引需要把区分度高的列放到前面。
Explain
explain + sql 执行后，会输出sql的执行计划，用来查看sql是否使用了索引等信息
每查询一个表就会输出一行，如果使用了join则会输出两行。
会在explain的基础上额外提供一些查询优化的信息。紧随其后通过showwarnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered列，是一个半分比的值，rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表)
explain中字段
- id id列的编号是select的序列号，有几个select就有几个id，先执行的select语句id越小。id为null的最后执行。
- select_type 表示对应的行是简单的还是复杂的查询
- simple:简单查询，查询不包含子查询和union
- primary：复杂查询中最外层的select
- subquery：包含再select中的子查询（不在from子句中）
- derive：包含在from子句中的子查询，MySql会将结果存放在一个临时表中，也称为派生表
- table列 表示正在访问哪个表，当from有子查询时，table列时 < derivenN >格式，表示当前依赖id = N的查询。当有union时，UNION RESULT的table的列的值为< union1,2 > 1和2表示参与union的select行id。
- type列 表示关联类型或者访问类型，即MySql决定如何查找表中的行，查找数据行记录的大概范围。 从最优到最差分别为：system>const>eq_ref>ref>range>idnex>ALL
-
null 访问索引就可以，不需要再访问表的时候
-
const、system mysql对查询的某部分进行优化并将其转化为一个常量（可以看show warings的结果），用于primary key或unique key的所有列与常树比较时，所以表最多有一个匹配行，读取一次，速度比较快，system是const的特例，表示只有一条数据匹配
-
eq_ref 主键和普通索引的所有部分被连接使用，最多只会返回一条符合条件记录，简单的select不会出现这种类型。
-
ref 没有用到主键索引，使用的是普通索引或者唯一性宿索引的部分前缀，可能返回多行数据
-
range 范围扫描时通常会是这个类型
-
index 扫描全索引拿到结果
-
all 全表查询
-
- possible_keys 显示查询当前语句走了哪些索引
- key 显示mysql实际采用了哪个索引来优化立了查询。
- key_len 显示mysql在索引里使用的字节数，可以计算出具体使用了索引中的哪些列（联合索引的时候有些用）
- ref 显示在key列记录的索引中，表查找值所用的列或者常量
- rows 要读取并检测的行数。
- extra 这一列展示的额外信息，常见的有
- using index：使用覆盖索引，一般针对的是覆盖索引，只需要使用索引，不需要额外回表。
- using where： 使用where语句处理结果，且查询的列未被索引覆盖
- using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
- using temporaty：mysql需要创建一张临时表来处理查询，出现这种情况一般要进行索引优化
- using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序，这种情况也要进行索引优化
- select tables optimized away：使用聚合函数（max，min）来访问存在索引的某个字段时。
索引使用建议
-
全值匹配
-
最左前缀 使用联合索引时，查询语句中where条件按照联合索引的顺序。
-
不要在索引上做任何操作，会导致索引失效转而全表扫描
-
联合索引中，第二个为范围查找时，会导致第三个索引失效。
-
尽量使用覆盖索引（只访问索引的查询），减少select * 语句
-
使用!=或者<>，not in， not exists时无法使用索引，小于大于操作，mysql也会根据检索比例，表大小评估是否使用索引。
-
is null ，is not null一般情况下也无法使用索引
-
like以通配符开头“%xxx”会导致索引失效。
-
字符串查询不加单引号索引会失效
-
or或者in mysql会根据检索比例确定是否使用索引
索引下推
在Mysql5.6以后，对于二级联合辅助索引，联合索引的第一个字段用右like时，会在索引遍历过程中，对索引包含的所有字段先做判断，过滤掉不符合记录之后再回表，减少回表次数。
事务隔离级别与锁机制
事务及其ACID属性
- 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要不全部不执行
- 一致性（Consistent）在事务开始和完成时，事务都必须保持一致状态，这意味着所有相关的数据规则都必须用于事务的修改，以保持数据的完整性。
- 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外不并发操作影响的独立环境执行，事务处理过程中的中间状态对外部是不可见的
- 持久性（Durabel）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
并发事务出现的问题
- 更新丢失（Lost Update）或脏写：最后的更新覆盖了由其他事务所做的更新
- 脏读（Dirty Reads）：事务A读取到了事务B已经修改但是未提交的数据，不符合一致性
- 不可重读（Non-Repeatable Reads）：事务A内部的相同语句在不同时刻读出的结果不同。不符合隔离性
- 幻读（Phantom Reads）：事务A读取到了事务B提交的新增数据，不符合隔离性
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（Read uncommitted） | 可能 | 可能 | 可能 |
| 读已提交（Read committed） | 不可能 | 可能 | 可能 |
| 可重复读（Repeatable read） | 不可能 | 不可能 | 可能 |
| 可串行化（Serializable） | 不可能 | 不可能 | 不可能 |
事务隔离越严格，并发副作用越低，但是性能越差，Mysql默认的隔离级别是可重复读。
可以用：show variables like ‘tx_isolation’；查询数据库隔离级别
数据库锁的分类
- 乐观锁和悲观锁，通过对比版本号
- 读锁（共享锁）和写锁（排它锁）
- 通过粒度来分：表锁和行锁，表锁的粒度大，加锁快，不会初选锁，发生的锁冲突概率高，行锁相反。MylAAM在执行select前会自动给涉及的表增加读锁，在执行增删改操作会给涉及的表增加写锁，InnoDB在执行查询语句时不会加锁，在增删改会增加行锁，
Mysql默认隔离级别是可重复读，幻读问题也有解决方式就是：间隙锁、临键锁
假设有一个用户表的主键id分别是1、2、3、10、20
某事务A执行语句：update user set name = ‘xxx’ where id > 8 and id < 18;那么其他事务在id在（3，20]区间内都无法修改数据。
注意：锁是加到索引上的，如果对非索引字段更新，行锁可能会变成表锁。
行锁分析
可以通过语句：show status like ‘innodb_row_lock%’；
会得到如下结果：
Innodb_row_lock_current_waits 0 // 当前正在等待锁的数量
Innodb_row_lock_time 0 // 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg 0 // 每次等待所花平均时间
Innodb_row_lock_time_max 0 // 从系统启动到现在等待最长的一次所化时间
Innodb_row_lock_waits 0 // 系统启动后到现在总共等待的次数
MVCC
MySQL的隔离机制（读已提交和可重复读）是通过MVCC控制的（Multi-Version Concurrency Control）机制来保证的，串行化是通过加锁互斥实现，读未提交是读取最新数据。
undo日志版本链与read view机制
在数据表中，MySQL会提供两个默认字段trx_id，roll_pointer，trx_id是唯一的事务id，roll_pointer用来记录版本链。
在可重复读隔离级别，每次sql查询会基于undo回滚日志生成当前事务的一致性视图（Read View），该视图在事务结束前都不会改变（如果是读已提交则是每次查询都会重新生成一遍）。read view是一个数组+一个数字（热爱的view：[100,200，300]），100为前未提交和已提交事务的最小id，和已创建事务的最大id组成。
然后通过undo版本链和read-view比较获取不同隔离机制对应的数据。
Buffpool机制
redo log
redo log日志是innodb特有的，redo log 用户空间的缓冲区数据是无法直接写入磁盘的，中间必须经过操作系统内核空间缓冲区 ，操作系统调用fync将其刷到redo log file中。
分三种 通过innodbflushlogattrx_commit参数配置
0延迟写 每秒写
1 实施写实时刷
2 实施写 延迟刷
bin log
用于记录数据库执行的写入操作（不包括查询信息），以二进制的形式保存在磁盘中，是mysql的逻辑日志，由server层记录，mysam和innodb都会记录，通过追加sql的方式写入，通过maxbinlogsize参数设置每个binlog文件的大小，满了以后生成新的。
索引优化原则
表关联算法
- 嵌套循环连接Nest-Loop join 算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集
**备注：**inner join时可以用straight_join强制指定驱动表
NLJ适用于在用索引做关联，left join时，左边的表为驱动表，right join时，右边为驱动表，inner join时，小表为驱动表。 - 基于块儿的嵌套循环连接Block Nest-Loop join算法
把数据批量放到join_buffer,进行全表比对
in\exist
原则：小表驱动大表
如果B的数据集小于A的，则用in
- List item
select * from A where id in (select id from B);
等价于
for (select id from B) {
select * from A where A.id = id;
}
如果B的数据集大于A的则用exist
select * from A a where exists (select 1 from B b where B.id = A.id);
等价于
for (select id from A) {
select * from B b where b.id = id;
}
count 查询优化
字段无索引：count() ≈count(1) > count(id)>count（字段）
字段有索引：count() ≈count(1) > count(字段)>count（id）
- MySAM存储引擎查询count会更快
- 使用show table status
- 将总数维护到内存中
- 增加计算总数的表
order by和group by
- 联合索引时遵循最左前缀原则
- group by可以使用order by null禁止排序
其他
- 长字符串可以使用前缀索引
- where 和order by冲突时优秀使用where
- 代码先行，索引后上。
2719
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
