MySQL 基础笔记（2）

1. 索引

（1）什么是索引?     

（2） 索引有几种类型？         

（3）加索引的原则？     

（4）实际业务应用？

        索引就像一本书的目录，城市的地图。它的存在是为了更加方便的查找书中内容的。书前面的目录独立一个章节，索引也就独立一个文件，也是占用物理存储空间的。

        索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

2. MySQL 底层索引的数据结构 : B+ 树

索引是存在硬盘上的, 存储的数据结构是什么样的呢?

演示分析 :   Data Structure Visualization  《数据结构可视化》 演示分析 

• 哈希索引 : 

       哈希值是一个无序的值, 在数据库中有很多大于、小于的查询, 因为哈希值是无序的,所以不能进行范围查找 ; 而且在进行排序的时候也不能用哈希值进行排序; 而且在引入链表的时候, 哈希值相同的情况, 就需要对链表内容逐一比对,相当于进行了全表扫描. 

• 平衡二叉树 : 

        缺点 : 1. 树越高 他的查找速度越慢;  2. 在查找大于某个数的时候, 需要回旋查找, 速度比较慢.

        平衡因子：左子树的高度减去右子树的高度。由平衡二叉树的定义可知，平衡因子的取值只可能为0,1,-1. 故左右子树的高度绝对值不会大于1. 

• B树 :

        查询效果优于平衡二叉树(10 个数的查找次数是2次, 平衡二叉树中十个数的查找次数是3次), 但是也存在回旋查找的问题. 

•  B+ 树

        彻底解决了平衡二叉树、B树中回旋查找的问题. 

        B+ 树比B 树多了个叶子结点(实际上就是链表), 把所有的数进行了一个从小到大的排序, 树中没有叶子节点, 链表中存在叶子节点.   叶子节点只存储key,不存value. 非叶子节点既存key, 又存value. (key就是数字, value就是地址).

​

 联合索引在B+ 树上的排序?

联合索引类似于(a,b)的形式, 排序的时候先拍a , a相等的时候再去排序b.  

3.索引有哪些优缺点？

索引的优点:

• 可加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点:

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

可以尝试在一个字段未建立索引时，根据该字段查询的效率，然后对该字段建立索引（alter table 表名 add index(字段名)），同样的SQL执行的效率，你会发现查询效率会有明显的提升（数据量越大越明显）。

MySQL 的索引优化

事务特性ACID : 原子性, 一致性(可见性), 隔离性, 持久性

事务是访问和更新数据库的程序执行单元, 事务中可能包含一个或多个SQL 语句, 这些SQL语句要么都成功, 要么都不成功.

1. MySQL 的基础架构:  

是一个三层架构的.

第一层是处理客户端连接, 以及认证授权的, 一般使用navicat 来做连接.

第二层是服务器层, 默认自动提交的方式. 如果执行过程中SQL语句出现问题就会执行rollback 回滚操作, 这也是原子性的一个体现.

​

2. ACID 四种特性 : 

1. 原子性（Atomicity）：强调事务的不可分割, 是最小的操作单元, 同生共死关系

要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。

2.1 原子性 的实现原理: undo log :

这是一个日志, mysql的日志有很多, 比如说二进制日志, 错误日志.,查询日志、慢查询日志等等. Innodb 存储引擎提供来两种事务的日志, 一个是redo log, 另一个是undo log. 这个undo log 就是实现原子性的一个原理,  会把执行前的一个状态记录下来,

2.2 原子性 实际项目中的应用:

在实际项目中, 如果程序上线失败, 或者刚上线就遇到一些不能及时解决的bug, 这时就需要回滚上线的版本. 把当前错误版本回滚到上一个正确的版本, 当事务对数据库进行修改的时候, Innodb 就会生成对应的undo log , 他会记录SQL执行的相关信息, Innodb 就会去做相反的工作,

实例:  (如果开始执行了一个insert 操作, 回滚的时候就会执行一个相反的操作 delete; 同理如果之前执行了一个delete操作, 回滚的时候就会执行一个相反的insert 操作; 对于update, 回滚的时候就会执行一个相反的update,把数据再改回去.)

2. 一致性（Consistency）：事务执行前后的数据的完整性保持一致 , 多个事务对同一个数据的读取结果是相同的. 

一致性是事务追求的最终目标; 事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定

eg：拿转账来说，假设用户A和用户B两者的钱加起来一共是20000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是20000，这就是事务的一致性。

3. 隔离性（Isolation）：一个事务执行过程中，不受到其他事务的干扰 

自己的理解过程  : 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

2.3 隔离性的“写-写操作” 原理 : 锁

Innodb 有行锁、表锁、间隙锁.如果两个事务同时对一行数据进行写操作, 这时只有一个数据可以对锁进行操作. 锁保证了同一时刻只有一个人在操作数据, 在事务做修改之前, 事务要获得相应的锁, 获得了这个锁,就可以修改数据了.

# 锁的概况

select * from information_schema_innodb_locks;

# Innodb 整体状态, 包含锁的情况

show engine innodb status;

# 在事务A中执行

# 开启事务 start transaction

# 执行 update account SET balabce = 1000 where id = 1;

2.4 隔离性的 “写-读操作”原理 : MVCC

(基于MVCC 去解决的 脏读、幻读、不可重复读)

2.5 脏读、幻读、不可重复读?

 第一类丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖。

• 脏读(Dirty) :  脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

eg：小明的银行卡余额里有100元。现在他打算用手机点一个外卖饮料，需要付款10元。但是这个时候，他的女朋友看中了一件衣服95元，她正在使用小明的银行卡付款。于是小明在付款的时候，程序后台读取到他的余额只有5块钱了，根本不够10元，所以系统拒绝了他的交易，告诉余额不足。但是小明的女朋友最后因为密码错误，无法进行交易。小明非常郁闷，明明银行卡里还有100元，怎么会余额不足呢？（他女朋友更郁闷。。。）

• 不可重复读(Non-repeatable) : 
• 幻读(Phantom) : 一个事务的两次查询后的结果不一致 

即:一个事务执行两次查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。幻读是事务非独立执行时发生的一种现象。

2.6 两种读之间的 区别 与 侧重 : 

Tips：

• 不可重复读和脏读的区别：

脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前(另)一事务提交了的数据。　

• 幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），

所不同的是 : 不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

• 不可重复读的重点是修改 :

同一事务,两次读取到的数据不一样。

• 幻读的重点在于新增或者删除

同样的条件 , 第 1 次和第 2 次读出来的记录数不一样

• 脏读: 强调的是第二个事务读到的不够新。

第二类丢失更新：是不可重复读的特殊情况。如果两个事物都读取同一行，然后两个都进行写操作，并提交，第一个事物所做的改变就会丢失。[第二个后做的操作就把先做的操作给覆盖了].

2.7 隔离级别:

隔离级别分为：按照效率由高到低,安全性由低到高的顺序排序 : 

1. 读未提交:  即 : 一个事务可以读取另一个未提交事务的数据。 写读 , 读写 , 读读 并行

eg：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

 

Analyse：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

2. 读已提交:  就是一个事务要等另一个事务提交后才能读取数据。读写 , 读读 并行

eg：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

Analyse：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

3. 可以重复读:  重复读，就是在开始读取数据（事务开启）时，不再允许修改操作. 读 和 读之间可以并行

eg：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

Analyse：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

4. 序列化 :  Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。所有的事务都是串行的

eg：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

隔离级别	脏读	不可重复读	幻读
读未提交	✅	✅	✅
读已提交		✅	✅
重复读			✅
序列化

注意: Mysql 默认采用的 重复读 隔离级别 Oracle 默认采用的 读已提交 隔离级别

---

2.8  MVCC 

MVCC 即多版本并发控制, 在读的时候不用去竞争锁, 主要是为了提高数据库的并发性能. 让数据库在读写的时候不用去枷锁, 这个主要是使用Innodb引擎来看, 因为MyIsam 是不支持事务的. 

MVCC 主要处理读操作, 采用快照读的方式. 而非当前读 (当前读采用的是一种加锁操作, 是悲观锁)

2.9 快照读 和 当前读 

当前读 : 会对当前数据进行加锁, 防止其他食物修改数据, 是一种悲观锁. 常见的当前读有:

        共享锁、拍他锁[select for update、update、insert、delete]、穿行话事务隔离级别.

快照读 :  在数据库层面确保了不会有锁竞争. 

        快照读 主要从以下几个方面答: undo log、版本链、readview.

undo log : 每次操作的操作日志

版本链 : 最新记录上的回滚指针roll_pointer 标有上次的记录, 起到一个索引的作用, 以此把一连串的的记录连接起来.

readview : 是个快照, 版本链中有多个版本, readview 的作用就是选出版本. 

​  

  ReadView 如何判断哪个版本链中的版本可用?

​

 MVCC 是主要针对读一提交和可重复读的.

mvcc还不了解的同学，可以先把概念搞清楚，read view中四变量。活跃事务（活跃事务就是没提交的事务）集合，当前事务id，集合中的最小值，集合中最大值的下个值。都是select查出的最新数据事务id跟四个变量进行比较判断的。判断方法可以逆推，就是凡是小于最小值，不包含活跃事务里面的，刚好等于当前事务的id，都可以查询最新值。其他任何情况都要一直递归查询undo日志，直到符合这三个条件为止。

---

4. 持久性（Durability）：事务一旦结束，数据就持久化到数据库中,对数据库的改变是永久性的

就像是操作记录, 形成之后就一直持久存在的了

2.10持久性的原理是: redo log 日志 

MySQL 的数据是存在磁盘中的, 但是如果每次去读数据都需要经过磁盘IO, 那么他的效率就会很低, 所以Innodb 提供了一个缓存buffer, 这个buffer 包含了磁盘中的部分数据页的一个映射, 作为访问数据库的一个缓冲, 当要从数据库读取这个数据,就会先从这个buffer中取, 如果buffer中没有就会从磁盘中读取, 读取完之后再放到这个buffer缓存中.

那么当向数据库写入数据的时候, 也会首先向buffer中写数据, 定期将buffer中的数据写入到磁盘上, 这个时候就会出现一个问题, 虽然读写效率提升了, 但是也增加了数据丢失的风险. 写入间歇时MySQL宕机,buffer中的数据丢失, 数据要是丢失了, 持久性也就无法保证了. 所以引入redo log 来解决这个问题.

---

2.11 持久性存入  改进后的流程 :

当数据库的数据要进行新增和修改的时候, 除了修改buffer 中的数据,  还要把这次的操作记录到redo log 中, 这样即便MySQL 宕机了, 他还有redo log 可以去恢复数据, redo log 是预写式日志, 预写式日志即将所有的修改先写入到日志里面, 然后再更新到buffer中, 以保证数据不会丢失, 这样也就保证了数据的持久性. 

2.6 redo log 和 buffer的存储速度区别?

Q : 既然redo log 也需要把事务提交的日志写入到磁盘, 那么他为什么要比直接将buffer中的日志写入磁盘要快呢 ?

A :  buffer 中的数据持久化是随机写的IO, 每次修改的数据位置都是随机的, 但是redo log 是追加模式的(在文件的尾部去追加,属于一种顺序IO的操作, 这种方式就很快, Kafka 也是采用这种顺序操作机制去操作的, ).另外buffer 持久化数据是以数据页page 为单位的, MySQL 默认的配置页大小是16k, 一个数据页上任何一点修改都需要把数据重新写入. redo log 只需要写入真正需要的部分就可以了, 所以redo log 要比buffer快很多.

2.12 redo log是怎么同步到磁盘里去?

redo log 没有同步到缓存区前是在redo log缓冲区中, 这时就算宕机也没关系, 事务没执行完,也就没提交,可以采用回滚机制对其进行回滚.

通过这个语句来控制持久化机制 : innodb_flash_log_at_trx_commit

0 : 表示提交事务时, 并不将缓冲区的redo 日志写到磁盘的日志文件, 而是等待主线程每秒刷新.

1 : (通常使用1 这种情况.)在事务提交时将缓冲区的redo 日志同步写入到磁盘, 保证一定会写入成功. 

2 : 在事务提交时将缓冲区的redo 日志异步写入到磁盘, 既不能完全保证commit是肯定会写入redo 日志文件,只是有这个操作.

引擎

1. MySQL 的存储引擎MyISAM 和 InnoDB 的区别:

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是是一套文件系统存储实现的。常用的存储引擎有以下：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
   

	MyISAM	Innodb
使用	MySQL 5.5版本之前(早些年使用的)	MySQL 5.7 版本之后(现在使用的)
事务	不支持	支持
外键	不支持	支持
锁(锁是避免资源征用的一个机制, MySQL锁对用户几乎是透明的)	表锁(锁住整个表)	行锁、表锁都有, 所动力度小并发能力高
全文索引	支持	不支持
哈希索引	不支持	支持
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
增/ 删/ 改		更好用
查询	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd

2. MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

3. InnoDB引擎的4大特性

插入缓冲（insert buffer)

二次写(double write)

自适应哈希索引(ahi)

预读(read ahead)

4. 存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。
 

索引

1. 

3. 数据库单表查询基础:

select 字段.. from 表名 where 条件;//执行顺序: 先from 再where,最后select

where : where 索引的条件中 使用and 和 or 连接时,注意 and 的优先级要高于or.当运算符的优先级不确定的时候, 加小括号.加小括号的优先执行.

注意: 数据库当中 NULL 不是一个值, 他代表了什么也没有, 为空. 在进行判断是,需使用 is null 或 is not null 来判断.

数据库建立索引常用的规则如下：

1、表的主键、外键必须有索引； 
2、数据量超过300的表应该有索引； 
3、经常与其他表进行连接的表，在连接字段上应该建立索引； 
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 
5、索引应该建在选择性高的字段上； 
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； 

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引； 
9、删除无用的索引，避免对执行计划造成负面影响；

慢查询

source : MySQL如何定位并优化慢查询sql - X凯 - 博客园

MySQL慢查询就是在日志中记录运行比较慢的SQL语句，这个功能需要开启才能用。

索引是建立得越多越好吗

        1.数据量小的表不需要建立索引，建立会增加额外的索引开销。

　　2.数据变更需要维护索引，因此更多的索引意味着更多的维护成本。

　　3.更多的索引意味着也需要更多的空间。

SQL优化