MYSQL高阶

MYSQL

存储引擎

innoDB

1.支持事务

对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务;(也不是说越多越好，多了会造成卡顿)。

2.支持表锁和行锁

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

例如：
 
    t_user(uid, uname, age, sex) innodb;
 
    uid PK
    无其他索引
    update t_user set age=10 where uid=1;              命中索引，行锁。
 
    update t_user set age=10 where uid != 1;           未命中索引，表锁。
 
    update t_user set age=10 where name='chackca';     无索引，表锁。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

3.是聚集索引

聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有唯一索引，通过主键索引效率很高。可以不要自增主键，InnoDB会有一个隐藏列Row_Id来充当默认主键哦。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

4.InnoDB 不保存表的具体行数

执行 select count(*) from table 时需要全表扫描。

5.innodb四大特性

InnoDB为什么推荐使用自增ID作为主键？

答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

1.插入缓冲

insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效。

只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

2.二次写

double write带给InnoDB存储引擎的是数据页的可靠性。

3.自适应hash

特点 　　1、无序，没有树高 　　2、降低对二级索引树的频繁访问资源，索引树高<=4，访问索引：访问树、根节点、叶子节点 　　3、自适应 3、缺陷 　　1、hash自适应索引会占用innodb buffer pool； 　　2、自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'，而对于其他查找类型，如范围查找，是不能使用的； 　　3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

4.预读

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

MYISAM

1.不支持事务

2.支持表锁不支持行锁

3.支持空间数据索引和全文索引

如何选择

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

系统奔溃后，MyISAM恢复起来更困难，能否接受；

MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

InnoDB 和 MyISAM 的比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

外键：InnoDB 支持外键。

备份：InnoDB 支持在线热备份。

崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

其它特性：MyISAM 支持压缩表和空间数据索引。

MySQL 索引

索引种类

b+树索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

是大多数 MySQL 存储引擎的默认索引类型。

是大多数 MySQL 存储引擎的默认索引类型。

1.因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

2.因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

3.可以指定多个列作为索引列，多个索引列共同组成键。

4.适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

哈希索引

注意:hash索引是等值索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；

• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据

索引的优点

大大减少了服务器需要扫描的数据行数。

帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的优化流程（必备）

1.排除 缓存 sql nocache

2.预发跑sql explain

3.看一下行数对不对 不对可以用analyze table t 矫正

4.添加索引 索引不一定是最优的 force index 强制走索引 不建议用

5.存在回表的情况

6.覆盖索引避免回表，不要*

7.联合索引 不能无限建 高频场景

问题:

如何给字符串加索引

使用前缀索引：

select count(distinct left(email,4)）as L4 from SUser;

优点：如果定义好长度，可以做到既节省空间，又不用额外增加太多的查询成本。

缺点：1.使用前缀索引的最大的一个缺点是一定会回表.使用不了覆盖索引（所以前缀索引如果要做复合索引最好放最右）

　　　2.可能会因为区分度扫描更多行

倒序存储:字符串逆序之后再使用前缀索引

hash字段:

select  field from t where id_card_crc=crc32('input_id_card') and id_card=id_input_id_card

（1）从占用额外的空间上看，采用哈希值字段索引会占用一个额外的字段空间，但是如果前缀的长度比较长的话，这个差距就可以忽略不计。 （2）从cpu消耗上看，使用哈希索引需要调用crc32()函数。 （3）从查询效率上看，使用hash值字段索引性能更稳定，因为crc64()结合了md5算法将冲突的概率降到了很低，因此平均扫描次数接近1。使用前缀索引会增加扫描次数。 （4）无论使用哪种凡是，都无法进行覆盖索引，也无法使用范围查找

聚簇索引和非聚簇索引

聚簇索引

主键索引: 如果当前表文件中字段添加了主键约束，mysql主动的将当前字段上数据进行排序，其生成的索引被称为主键索引

唯一性索引: 如果当前表文件中字段添加了唯一性索引，mysql主动的将当前字段上数据进行排序，其生成的索引被称为唯一性索引。 唯一性索引不包含Null

普通索引:如果当前当前表文件中字段上没有添加任何索引，此时在这个字段上 创建的索引就是普通索引。

执行效率

主键索引 》 唯一索引 》普通索引

非聚簇索引

使用二叉树将列中数据进行存储。在二叉树最后一级也就是叶子层存储的是数据对应的【索引值】和【数据所在行位置‘数据行的引用地址’】，这中索引就是【非聚簇索引】

对于采用了INNODB存储引擎表，除了一个聚簇索引之外，其他字段上创建的索引都是非聚簇索引

在采用MyIsam存储引擎的表中，创建的所有索引都是非聚簇索引

聚簇索引和非聚簇索引的区别

1.叶子节点存储内容

• 聚簇索引:叶子节点存储的【索引值】以及【所在数据行内容】

  因为聚簇索引的这种结构,所以在查询时可以直接在定位数据节点上，读取当前数据所在数据行中字段信息不需要使用I/O流到硬盘上表文上进行读取．因此执行效率较快

• 非聚簇索引:叶子节点存储的【索引值】和【数据行的引用地址】

  由于数据节点存储的当前数据坐在行数,没有其相关内容所以定位之后，需要使用I/O流到硬盘上表文件中,定位数据行其他字段内容。因此执行效率相对较慢

2.关于主键的有无

• innodb 主动将主键作为聚簇索引，如果当前表中没有主键，MySql将会选择一个添加唯一性约束的字段作为聚于索引。如果当前表中既没有主键字段，也没有添加了唯一性约束字段Mysql将创建一个 6 个字节的字段作为聚簇索引

• myisam 允许表中可以没有主键和索引

以下是存储引擎的区别

3.关于自动增长

• myisam 引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后 递增。

• innodb 引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列

4.关于 count 函数

• 1) myisam 保存有表的总行数，如果 select count( * ) from table;会直接取出出该值

• 2) innodb 没有保存表的总行数，如果使用 select count(*) from table；就会遍历整个表，消耗相当大，但是在加了 wehre 条件后，myisam 和 innodb 处理的方式都一样

5. delete from table

• 1) 使用这条命令时，innodb 不会从新建立表，而是一条一条的删除数据，在 innodb 上如果要清空保存有大量数据的表，最好不要使用这个命令。(推荐使用 truncate table)

• 2)myisam 会创建一个全新临时表，在临时表进行删除。然后使用临时表覆盖掉原始表

聚簇索引与非聚簇索引适用的场合

mysql5.6之后 索引下推 减少回表次数

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

Mysql5.6之前的版本

• 5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：

• 会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

Mysql5.6及之后版本

• 5.6版本添加了索引下推这个优化，执行的过程如下图：

• InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

Mysql日志

redo log

又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。 redo log文件的大小设置对于InnoDB存储引擎的性能有着非常大的影响。

*设置的太大 设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务，如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。

*设置的太小 当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。

一般是先写入redo log buffer 中再I/O写入redo log文件中（先写日志再写磁盘）, 通过操作系统异步将其刷到日志里面。

innodb_flush_log_at_trx_commit 配置:

参数值	含义
0（延迟写）	事务提交时不会将 redo log buffer中日志写入到 os buffer，而是每秒写入 os buffer并调用 fsync()写入到 redo log file中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
1（实时写，实时刷）	事务每次提交都会将 redo log buffer中的日志写入 os buffer并调用 fsync()刷到 redo log file中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
2（实时写，延迟刷）	每次提交都仅写入到 os buffer，然后是每秒调用 fsync()将 os buffer中的日志写入到 redo log file。

undo log

undo:意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作，返回某个状态

undo log:数据库事务开始之前会将要修改的存放在undolog中，当事务回滚时或者数据库崩溃时，可以利用undo日志，撤销未提交事务对数据产生的影响

undo log 产生和销毁:undo Log 在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread 进行回收处理。undo log属于逻辑日志，记录一个变化过程。执行一个update undo log会记录一个相反的update。

存储:undo log采用段的方式进行记录和管理。在innodb数据文件包含一种rollback segment回滚段，内部包含1024个undo log segment。

作用:

1️⃣实现事务的原子性

undo log是为了实现事务的原子性而出现的产物。如果出现了错误或者用户执行了ROLLBACK语句，MYSQL可以利用Undo log中的备份将数据库恢复到事务开始之前的状态。

2️⃣实现多版本并发控制（MVCC）

事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log中的数据可以作为数据旧版本快照供其他并发事务进行快照读

bin log

即Binary log(二进制日志),简称binlog。会记录所有的修改操作，不会记录Select和Show这类操作。binlog是以事件形式记录，还包含语句所执行消耗时间。开启Binlog日志有以下俩个重要的场景使用

1️⃣主从复制

主机开启binlog，主机会把binlog传递给从库，保证数据一致性

2️⃣数据恢复

通过mysqlbinlog这样的工具来恢复数据

row:记录每一行日志被修改的情况，然后再salve对相同的数据进行修改(安全可靠，能完全实现同步和数据恢复，记录的每一行的数据,会产生大量的日志)

statement:每一条被修改的数据的sql都会记录到master的binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过相同的SQL再次执行，简称SQL语句的复制。(记录少日志量，减少磁盘I/O，提升存储和恢复速度；不可靠，在某些情况下会导致主从数据不一致，比如last_insert_id,now())

mixed:以上俩种模式的混合使用:根据执行的sql语句执行区分一般会使用statement模式保存binlog。对于statement模式无法复制的操作ROW模式保存binlog。MYSQL会根据执行的SQL语句选择写入。

binlog写入机制:

以事件为机制进行触发的，事件触发机制。

将事务执行过程中产生log envent写入缓冲区，每个事务线程都有一个缓冲区，Log envent，每个事务都有一个缓冲区。

事务在提交阶段会将惨产生的logenvent写入外部的binlog文件中。

不同事务以串行方式将log envent写入biglog文件中，所以一个 事务包含的内容不会与其他事务混淆，会先执行bin log再执行redo log和undo log。

binlog和redolog的区别

redo log是属于innodb引擎功能，binlog是属于MYSQL Server自带的功能，并且以二进制文件记录。

redo log属于物理日志，记录该数据页更新状态内容，binlog是逻辑日志，记录更新过程

redo log日志是循环写的是固定的，空间的大小是固定，binlog是追加写入，写完一个写下一个，不会循环使用。

redo log作为服务器异常宕机后事务数据自动恢复使用，binlog可以作为主从复制和数据恢复。binlog没有自动crash-safe的能力。

mysql的flush时机

第一种情况： redo log写满，要flush脏页，在flush期间，所有的更新业务暂停，对性能来说是大打击。

第二种情况： 内存不够，需要刷新脏页到磁盘中。内存分为三种，已使用脏页，已使用干净页，未使用页。当需要申请一个内存页面时，内存满了，需要先淘汰页面，使用相应的淘汰算法选择页面，例如最长时间未使用算法(LRU)，淘汰的页面也分两种情况：①干净页：直接释放使用；②脏页：flush到磁盘中变成干净页而后释放使用。

第三种情况：

以下两种情况会明显影响性能：

1. redo log写满，更新操作停住进行flush操作。

2. 一个更新操作涉及多个脏页flush。内存分为三种，已使用脏页，已使用干净页，未使用页。淘汰的页面也分两种情况：①干净页：直接释放使用；②脏页：flush到磁盘中变成干净页而后释放使用。

想要控制这两个我们需要控制脏页的比例和redo log的写盘速度。

MySQL通过设置脏页比来，来计算出redo log应该的写盘速度

innodb_io_capacity设置低了 会让innoDB错误估算系统能力 导致脏页累积

MySQL在flush内存中脏页时，有一个策略叫做脏页连坐，如果当前flush的脏页的邻居也是脏页，也会连同一起flush，直到碰见一个干净页的邻居。我们也可以通过参数 innodb_flush_neighbors 设置，1表示开启，0表示不开启。

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 。

索引维护

1.页满了 页分裂 页利用率下降

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

2.数据删除 页合并

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

3.自增 只追加可以不考虑 也分页

4.索引长度

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。

changebuffer处理流程

唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。

如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

事务的隔离级别

多版本并发控制（MVCC）

为解决高并发进行访问，如何提高读写效率。

明白这个这个需要俩个基础知识

当前读：读的是当前的最新版本数据。

快照读：读的以前的历史版本。

实现原理

原子性--->undolog

隔离性--->mvcc

持久性--->redolog(二阶段提交)--->WAL机制--->先写日志再写数据(磁盘)--->随机读写和顺序读写（为了保证一致性先写日志再写磁盘)。

一致性--->依托于其他三个特性

俩阶段提交

RR始终用的是第一次快照吗?

答案:不是，再更改时会产生幻读(因为update，delete，insert用的是当前读)。视图发生了改变。可以用间隙锁解决，语句后面加for update。

最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

MVCC + 悲观锁` MVCC解决读写冲突，悲观锁解决写写冲突

MVCC + 乐观锁` MVCC解决读写冲突，乐观锁解决写写冲突

MYSQL中的锁

mysql中有哪些锁?

从粒度上分:有表级锁，行级锁，页级锁

从操作上分:读锁,写锁

从实现方式上分:乐观锁和悲观锁

悲观锁

表锁:

1️⃣表读锁:在查询字段后面＋in a share lock,在对表加读锁之后无法进行加写锁

2️⃣表写锁

3️⃣元组锁:在进行CURD时会加元组读锁，在进行DML时会加上元组写锁

行锁:

1️⃣行读锁:多个读锁可以并行使用。但是写锁会被阻塞

2️⃣行写锁:在一个事务进行行写锁时，其他事务的行锁和写锁都会阻塞

3️⃣间隙锁:在表中的间隙中加如锁，主要防止insert操作。（RR隔离级别）

4️⃣间隙锁和写锁的组合.(RR隔离级别)

在用非索引字段进行查询或者修改时行锁会变成表锁

当用唯一索引加读锁或者写锁时就直接加这一行的读锁和写锁就行

如果对非唯一索引加读锁或者写锁时，会锁住多行记录和之间的间隙。

乐观锁

mysql中的时间戳和序列号.

死锁问题

1.俩个版本都正在争夺对方未释放的锁

2.使用for update将共享锁变为排他锁

死锁的检测: 事务等待图法:

超时法:

采用事务回滚的方法，撤销掉代价最小的事务。

热点问题

读热点:

我们开始先讨论比较容易解决的读热点问题的解决方案。一般我们做系统之出使用数据库，直接对用户的请求做sql的select操作，那对于此类热点问题我们首先想到的是需要优化数据库的读操作，我们对应的查询是否走了索引，走的是否是唯一索引甚至于主健效果最佳，优化了sql性能后们可以借助于mysql innodb的buffer做一些文章，在数据库层面就提供足够的缓冲区，加速对应的性能，实验证明，只要走的是主健或唯一索引，innodb缓冲区足够大的情况下，mysql抗上亿的数据也是没有任何问题的，真正出问题的不是点而是热，由于访问频次太高，mysql的cpu扛不住了这个时候我们考虑到的是将对应的读热点放到例如redis的缓存中用于卸载压力，由于redis4版本以后就可以支持cluster的集群模式，其借助分片集的效果理论上可以扩展到1000个左右的节点，如此一来我们可以依靠缓存去解决读热点问题，一旦商家变更了读热点的数据，我们可以在业务应用使用提交后异步清除缓存的方式将redis的数据清除，这样在下一次的请求中可以依靠数据库的回源更新redis数据。

写热点:

就是排队，大家都不要竞争，按照先到先得的方式将所有对热点的写入访问操作队列化，使用单线程的方式去队列中取得下个写入操作，然后写完后再取下一个，这样可以避免掉写锁竞争的无谓cpu和内存消耗，也可以使用单线程的方式解决，没有cpu调度切换的开销

count(*)=count(1)>count(primary key)>count(column)