mysql基础知识

并发问题

1.丢失修改

一个事务的更新操作被另一个事务的更新操作替换。

2.脏读

不同的事务下，当前的事务可以做到另外事务未提交的事务。

3.不可重复读

不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。

4.幻读

幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

数据库并发场景有三种，分别为：

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

隔离级别

1. 未提交读

事务中的修改，即使没有提交，对其它事务也是可见的。

2. 提交读

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

3. 可重复读

保证在同一个事务中多次读取同一数据的结果是一样的。

4. 可串行化

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

mysql如何实现四大隔离级别?

1. 相关概念

当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

2. mvcc

是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题(要加锁)
• MVCC + 悲观锁
  MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC + 乐观锁
  MVCC解决读写冲突，乐观锁解决写写冲突
  这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题。

3. InnoDB的三种行锁算法

3.1 Record Locks

锁定一个记录上的索引，而不是记录本身。（索引失效会锁全表）

3.2 Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

3.3 Next-Key Locks（innodb默认的锁就是Next-Key locks。）

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间。

select * from gap_tbz where id > 5 and id < 11 for update;
#以下报错 lock等待超时
insert into gap_tbz values(11);

mysql索引分类与优化

1. 主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

使用自增主键每次插入新的记录会顺序插到当前索引节点的后续位置，维护b+树平衡的开销就会小。使用自增主键在存储空间上也会比较好，二级索引（或则说普通索引）的叶子节点会存主键，自增id一般只需要4个字节。

2. 辅助索引

辅助索引的叶子节点存储的数据是主键。也就是说，通过辅助索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于辅助索引。

3. 复合索引

多个列构成的索引（相当于二（多）级目录）（name, age）先根据name找人，如果name重复了，再根据age筛选一次。

a有序的条件下b有序，若没有a，b不会有序，索引会失效。

4. 聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

更新代价大，不需要回表查询。

5. 非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

更新代价小一些，需要回表查询。

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='guang19';

6. 慢sql的原因和排查

避免索引失效的原则：

1. 复合索引，不要跨列或无序使用（最佳左前缀），尽量使用全索引匹配
2. 不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效
3. 复合索引不能使用(!= <>)或is null，否则自身和右侧索引全部失效（大概率）（a,b),中a不相等，b就没有序
4. 复合索引不能使用>，否则右侧索引全部失效（大概率）比如建立了（a,b),a>1中a不相等，b就没有序（自身有效后面失效）
5. %放在两边或者左边，也不走索引
6. in 失效（自身有效，后面失效）

双路排序 先读待排字段，再其它字段 两次扫描磁盘 buffer用排序

单路排序 一次IO buffer有限 但是快，数据大的话可以调buffer的大小

explain:

type:结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows：显示MySQL认为它执行查询时必须检查的行数。

extra: using filesort：排序和查找不是同一个字段

​ using tempory：group by

​ using where：需要回表查询

​ impossible where：会扫描全表

**慢查询日志：**mysql提供的日志记录，记录超过默认值10s的SQL语言

mysql dumpslow 可以查询慢sql

profiles 分析海量数据，不够精确，只能看到总花销时间，不能看见特定硬件的查询时间

profile可以精确看到特定硬件的查询时间

innodb和myisam的区别

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 存储结构：innodb是聚集索引，而myisam是非聚集索引。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

B+树

hash不适合范围查询，而且磁盘IO不小，二叉查找树不平横，AVL树树太高，红黑树树也高，B树可以，但也不适合范围，B+树非叶子节点只存索引，叶子节点才存数据。