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）
- 写写操作（锁机制）
# 查看锁情况
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;
- 写读操作（MVCC）
- 脏读、不可重复读、幻读
- 持久性（Durability）
mysql数据存储在磁盘中，如果每次读取数据都经过磁盘IO则对性能影响较大，InnoDB提供了redo log是预写式日志，该缓冲包含磁盘数据页的一个映射，作为访问数据的缓冲；当向数据库写入数据时，也会先向将数据写入redo log，该缓冲写入数据，定期将缓冲中数据定期刷新到磁盘上，进行持久性操作；
redo log是追加模式，属于顺序IO操作；
redo log工作策略：
- 当提交事务时，并不将缓冲区的redo日志写入磁盘的日志文件，而是等待主线程每秒刷新；
- 在事务提交时将缓冲区的redo日志同步写入到磁盘，保证一定会写入成功；
- 在事务提交时将缓冲区的redo日志异步写入到磁盘，即不能完全保证commit时肯定会写入redo日志文件，只有这个动作
3 MVCC
3.1 MVCC基本概念
MVCC（多版本并发控制），主要是为了提高数据库的发布性能（InnoDB支持事务，MyIsam不支持事务）
同一行数据平时发生读写请求时，会上锁阻塞；MVCC使用更好的方式处理读写请求，做到读写请求时不用加锁；
这里的读指的是快照读，而不是当前读，当前读是一种加锁操作，是悲观锁；
- 当前读
它读取数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据，是悲观锁的一种操作；
当前读的操作有：
- select lock in share mode(共享锁)
- select for update(排它锁)
- update(排它锁)
- insert(排它锁)
- delete(排它锁)
- 串行化事务隔离级别
- 快照读
改读取方式是基于MVCC，快照读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据；
快照读包括
- 不加锁的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 索引查询数据流程
- MyIsam和InnoDB区别
| 对比项 | MyIsam | InnoDB |
|---|---|---|
| 事务 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 索引 | 非聚簇，支持FullText类型全文索引 | 聚簇，不支持FULLTEXT类型全文索引 |
| 锁粒度 | 表锁 | 行锁 |
| 存储结构 | .frm表定义文件、.MYD数据文件、.MYI索引文件 | frm表定义文件、Idb文件数据和索引文件 |
- 聚簇和非聚簇索引
聚簇索引（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 索引优化
- 最左前缀法则
如果建立的是复合索引，索引的顺序要建立时的顺序，既从左到右，如 a->b->c
无效索引举例：
a->c, a有效，c无效
b->c: b、c都无效
c：c无效
- 不要对索引做如下处理，如下操作会使索引失效
- 运算符号，如+,-,*,/,!=,<>,is null, is not null,or
- 函数，如sum(),round()
- 手动、自动类型转换，如id="1",本来是数字，写成了字符串
- 索引不要放在范围查询右边
如复合索引：a->b->c,当 where a='' and b>10 and 3='',这时只用到a和b，c用不到索引，因为在范围之后索引都失效（和B+树结构有关） - 减少select *的使用
尽量使用覆盖索引，如select查询字段和where中使用的字段一致； - like模糊搜索
失效情况
like '%xx'
或
like '%xx%'
解决方案
使用覆盖索引
select name from table where name like '%张%'
或
like '张%'
- 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%';
网友评论