1 null值注意

• count数据丢失null值行
• distinct数据丢失
  当使用count(distinct col1,col2)查询时，如果其中某一列为null，查询结果将丢失数据；
• null不能使用比较运算符，使用isnull(列)函数、is null 、is not null
• sum(列)如果列中包含null，返回null；解决方案

select ifnull(sum(列),0)

• null不会影响索引

2 事务特性

2.1 基本概念

事务：访问和更新数据库程序执行单元，事务包含一个或多个sql语句，这些sql语句要么一起执行成功，要么一起失败；
mysql基本结构图

image.png

2.2 ACID特性

• 原子性(Atomicity)
  Inno DB存储引擎提供两种事务日志undo log和redo log，当sql对数据库修改时，inno db的undo log会记录sql执行相关信息，如果sql语句发生错误，innodb会根据undo log内做相反的操作；
• 一致性（Consistency）
  指事务执行结束后，数据完整性没有被破坏
• 隔离性（Isolation）

1. 写写操作（锁机制）

# 查看锁情况
select * from information_schema.infodb_locks;
# InnoDB整体状态，其中包括锁的情况
show engine innodb status;
# 事务A中执行
start transaction;
update account set balance=1000 where id=1;
# 事务B中执行
start transaction;
update account set balance=2000 where id=1;

2. 写读操作（MVCC）
3. 脏读、不可重复读、幻读

• 持久性（Durability）
  mysql数据存储在磁盘中，如果每次读取数据都经过磁盘IO则对性能影响较大，InnoDB提供了redo log是预写式日志，该缓冲包含磁盘数据页的一个映射，作为访问数据的缓冲；当向数据库写入数据时，也会先向将数据写入redo log，该缓冲写入数据，定期将缓冲中数据定期刷新到磁盘上，进行持久性操作；
  redo log是追加模式，属于顺序IO操作；
  redo log工作策略：

1. 当提交事务时，并不将缓冲区的redo日志写入磁盘的日志文件，而是等待主线程每秒刷新；
2. 在事务提交时将缓冲区的redo日志同步写入到磁盘，保证一定会写入成功；
3. 在事务提交时将缓冲区的redo日志异步写入到磁盘，即不能完全保证commit时肯定会写入redo日志文件，只有这个动作

3 MVCC

3.1 MVCC基本概念

MVCC（多版本并发控制），主要是为了提高数据库的发布性能（InnoDB支持事务，MyIsam不支持事务）
同一行数据平时发生读写请求时，会上锁阻塞；MVCC使用更好的方式处理读写请求，做到读写请求时不用加锁；
这里的读指的是快照读，而不是当前读，当前读是一种加锁操作，是悲观锁；

• 当前读
  它读取数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据，是悲观锁的一种操作；
  当前读的操作有：

1. select lock in share mode(共享锁)
2. select for update(排它锁)
3. update(排它锁)
4. insert(排它锁)
5. delete(排它锁)
6. 串行化事务隔离级别

• 快照读
  改读取方式是基于MVCC，快照读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据；
  快照读包括

1. 不加锁的select操作（注：事务级别不是串行化）

3.2 数据库四种隔离级别

隔离性是通过（锁和MVCC实现的）

隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重读（repeatable-read）	否	否	是
可串行化（serializable）	否	否	否

1、脏读：
又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改但是还未提交，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改并提交，这就导致了T2所读取到的数据是无效的，值得注意的是，脏读一般是针对于update操作的。

2、不可重复读：
是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

3，幻读：
事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
1）read-uncommitted 读未提交：

在该级别，所有的事务都可以看到其他未提交事务的执行结果，本隔离级别很少用于实际应用，因为它的性能不比其他级别好多少。读取未提交的数据，也称之为脏读。

2）read-committed 读提交内容：

这是大多数数据库系统的默认隔离级别（但不是MYSQL默认的），它满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。也支持所谓的不可重复读。

3）repeatable-read 可重读：

是MYSQL默认的，确保统一事务的多个实例在并发读取数据时，会看到同样的数据行。

4）serializable 可串行化：

这是最高的隔离级别，他通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，他是在每个读的数据行上加上共享锁。在这个级别可能导致大量的超时现象和锁竞争。

3.3 read-committed 读提交内容和repeatable-read 可重读的快照读使用MVCC

对于表中的相同记录行包括事务ID（trx_id,自增）、回滚指针（roll pointer）;对于同一条数据的修改的历史记录存储到undo日志中，并通过回滚指针指向上一次修改的记录，形成版本链；

• 确定查询记录版本的readView对象

m_ids	表示在生成ReadView时当前系统中活跃的读写事务的“事务ID”列表
min_trx id	表示在生成“ReadView”时当前系统中活跃的读写事务中最小的“事务ID”
max_trx id	表示生成“ReadView”时系统中应该分配给下一个事务的“ID”值
creator_trx id	表示生成该“ReadView”的事务的“事务ID”

• ReadView如何判断版本链哪个版本可用
  trx id==creator_trx id：可以访问这个版本
  trx id<min_trx_id: 可以访问这个版本
  trx_id>max_trx_id: 不可以访问这个版本
  min_trx_id<=trx_id<=max_trx_id: 如果trx_id在m_ids中是不可以访问这个版本，反之可以

4 MySQL索引

4.1 索引失效原理

联合索引，底层使用B+树存储（左前缀法则、大于号右侧失效、like会失效、or失效、<>失效、in失效）
联合索引在底层使用B+树存储时，分支的每一个节点存储多个值（最左侧值有序，其他值在最左侧值相等情况下有序），如果不使用最左前缀法则不能利用B+树查找数据；

4.2 索引查询数据流程

1. MyIsam和InnoDB区别

对比项	MyIsam	InnoDB
事务	不支持	支持
外键	不支持	支持
索引	非聚簇，支持FullText类型全文索引	聚簇，不支持FULLTEXT类型全文索引
锁粒度	表锁	行锁
存储结构	.frm表定义文件、.MYD数据文件、.MYI索引文件	frm表定义文件、Idb文件数据和索引文件

2. 聚簇和非聚簇索引
   聚簇索引（InnoDB）
   将数据存储于索引放到一块，索引结构的叶子节点保存了行数据；
   表数据按照索引的顺序来存储，也就是说索引项的顺序与表中记录的物理顺序一致；
   InnoDB中，在聚簇索引之上创建的索引称为辅助索引，像符合索引、前缀索引、唯一索引等；

• 聚簇索引默认是主键
• 如果表没有定义主键，InnoDB会选择一个唯一的非空索引代替
• 如果没有这样的索引，InnoDB会在内部生成一个名为GEN_CLUST_INDEX的隐式的聚簇索引；
  非聚簇索引（MyISAM）
  将数据与索引分开存储，表数据存储顺序与索引顺序无关；

4.3 MyISAM索引查询数据过程

MyIsam的B+树叶子节点存储记录地址；
InnoDB的聚簇索引，B+树叶子节点存储记录；
InnoDB的辅助索引（如复合索引），B+树叶子节点存储key和主键值；

• 未使用索引查询

select sql_no_cache * from 表 where 列1='值' and ...

4.4 索引优化

1. 最左前缀法则
   如果建立的是复合索引，索引的顺序要建立时的顺序，既从左到右，如 a->b->c
   无效索引举例：

a->c, a有效，c无效
b->c: b、c都无效
c：c无效

2. 不要对索引做如下处理，如下操作会使索引失效

• 运算符号，如+,-,*,/,!=,<>,is null, is not null,or
• 函数，如sum(),round()
• 手动、自动类型转换，如id="1",本来是数字，写成了字符串

3. 索引不要放在范围查询右边
   如复合索引：a->b->c,当 where a='' and b>10 and 3='',这时只用到a和b，c用不到索引，因为在范围之后索引都失效（和B+树结构有关）
4. 减少select *的使用
   尽量使用覆盖索引，如select查询字段和where中使用的字段一致；
5. like模糊搜索
   失效情况

like '%xx'
或
like '%xx%'

解决方案
使用覆盖索引

select name from table where name like '%张%'
或
like '张%'

6. order by优化
   当查询语句使用order by进行排序时，如果没有使用索引进行排序，会出现filesort（文件内排序，这种情况记录没有在内存中排序，而是会开辟一块空间进行排序，性能非常慢），这种情况在数据量大活并发高的时候，会出现性能问题
   优化

可以使用覆盖索引，如
select col1 from tab order by col1;

order by字段不是索引字段
order by字段是索引字段，但是select中没有覆盖索引;
order by中同时存在ASC和DESC
order by多个字段排序排序时，不是按照索引顺序进行order by，既不是按照最左前缀法则

5 锁实现

• or导致索引失效，由行锁升级为表锁

set autocommit=0;
update tab set b='a4' where a=1 or a=2;
# 

• 间隙锁

范围查询形成间隙锁；

• 查询锁

show status like 'innodb_row_lock%';