MySQL高级理解

一、查询SQL语句的执行

在 MySQL 中一条查询 SQL 是如何执行的呢？比如下面这条SQL语句

select name from t_user where id=1

1. 获取链接：获取链接使用到MYSQL中的连接器
2. 查询缓存：key为SQL语句，value为查询结果，如果查询到就直接返回。在MySQL8.0版本已经将查询缓存删除，也就是说MySQL
   8.0 版本后不存在查询缓存功能。
3. 分析器：分析器分为词法分析和语法分析。这个阶段会做一些SQL解析，语法校验。所以一般语法错误在此阶段判出。
4. 优化器：在表里有多个索引的时候，决定使用哪个索引或者是在一个语句中存在多表关联（join）的时候，决定各个表的连接顺序。
5. 执行器：通过分析器让SQL知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行的时候还要判断是否拥有此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的执行引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断id是否等于1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，知道取到这个表的最好一行，最后返回。

 

二、MySQL数据库引擎

MySQL数据库引擎有哪些，如何查看MySQL提供的所有存储引擎？

show engines

MySQL常用引擎包括：MYISAM、Innodb、Memory、MERGE,所有的执行引擎如下

• Innodb：行级锁，提供了具有提交、回滚和奔溃恢复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，但是占用空间是MYISAM的2.5倍，处理效率相对会差一些。
• MYISAM：全表锁，拥有较高的执行速度，占用空间相对较小，但是不支持事务，不支持外键，并发性能差，对事务完整性没有要求。
• Memory：全表锁，存储在内存中，速度快但会占用和数据量成正比的内存空间且数据在MySQL重启时丢失，默认使用HASH索引，检索效率很高，但是不适用于精确查找，主要用于那些内容变化不频繁的代码表。
• MERGE：是一组MYISAM表的组合。

 

三、InnoDB和MyISAM区别

事务方面
InnoDB支持事务，MyISAM不支持事务。
对于InnoDB每一条SQL语言都会默认封装诚事务，自动提交，这样会影响速度，所以最好把多条SQL语句放在begin和commit之间，组成一个事务。

外键方面
InnoDB支持外键，而MyISAM不支持。
对一个包含外键的InnoDB表转为MyISAM会失败。

索引方面
InnoDB是聚集索引，而MyISAM是非聚集索引。
InnoDB是聚集索引，数据文件和索引是绑定在一起的，必须要有主键，通过主索引效率很高。但是辅佐索引需要两次查询，先查询主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键过大的话，其他索引其他索引也会很大。
MyISAM是非聚集索引，索引和数据文件是分离的，索引保存的是数据文件的指针。主索引和辅助索引是独立的。

全文索引方面
InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高。

表的具体行数方面
InnoDB不保存表的具体行数，执行select count（*）from table 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要独处该变量即可，速度很快。

 

四、InnoDB和MyISAM查询count（1）

为什么查询语句

select count(1) from table 

在InnoDB中速度比MyISAM慢？
对于select count(1) from table 语句，在没有where 条件的情况下，InnoDB比MyISAM可能慢很多，尤其是在大表的情况下。
因为InnoDB不保存表的具体行数，执行select count（*）from table 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时，只需要读出该变量即可，速度很快。

 

五、数据库事务

具体数据库事务方面的知识见我的另一篇博客：数据库事务的特性

 

六、数据库中几个删除关键字的区别

SQL中的drop、delete和truncate都表示删除，但是三者有如下的区别

1. delete和truncate只删除表的数据不删除表的结构。
2. 他们的速度一般来说：drop > truncate > delete

• delete语句是DML，这个操作会放到rollback segement中，事务提交之后才生效；而如果有相应的trigger，执行的时候将被触发。
• truncate、drop是DDL，操作立即生效，原数据不放到rollback semgment中，不能回滚。操作不会触发trigger。

 

七、SQL优化方案

在使用框架操作数据库时，我们经常会写SQL语句，但是有些时候我们写的SQL语句的效率不高，那么我们就要去学一下SQL优化。具体见我另一篇博客：SQL优化方案

 

八、MySQL索引

说到MySQL数据库，我们一定会说到MySQL的获取数据的高效性，那为什么MySQL在查询的时候它为什么会这么快呢？原因是存在索引。
索引方面的具体知识我写在另一篇我的博客上面了，具体见：MySQL索引

 

九、相关类型理解

1. 在MySQL中，varchar和char的区别是什么？
   char 是一种固定长度的类型，varchar是一种可变长度的类型，对效率高要求用char，对空间使用要求高用varchar。

2. varchar(30)中的30代表什么含义？
   varchar（30）中的30表示最多存放30个字符。
   varchar(30)和varchar（130）存储 hello所占的空间一样，但是后者在排序时会消耗更多的内容，因为order by col采用fixed_length计算col长度。

3. int（11）中的11 代表什么含义？
   int (11) 中的11，不影响字段的存储范围，只影响展示效果。
    

十、MVCC

什么是MVCC

  多版本并发控制（MVCC = Multi-Version Concurrency Controll）,是一种用来解决读 - 写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每一个修改保存一个版本。版本与事务的时间戳相关联，读操作只读了该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重负读。
 

MVCC可以解决数据库的问题

  在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提供了数据库并发读写的性能。同时还可以解决脏读、幻读和不可重复读等事务隔离问题，单不能解决更新丢失问题。

 

MVCC的实现原理

  MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的3个隐式字段、undo日志、Read View 来实现的。

 

十一、说说有关MySQL中的锁（Lock）

为了保证数据的一致性。MySQL数据库存在多种数据引擎，MySQL各存储引擎使用了三种类型(级别)的锁机制：表级锁定，行级锁定和页级锁定。

表级锁：表级锁一次会将整个表锁定，所可以很好的避免死锁问题。
特点：锁定力度大，锁冲突概率高，并发度低。
优点：不会出现死锁，获取锁和释放锁的速度很快。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎，适用于以查询为主，少量更新的应用。

行级锁：行级锁一次锁定一行记录.
特点：行锁锁定的对象的颗粒度很小，发生锁冲突的概率低，并发度高
缺点：行锁容易出现死锁，加锁和解锁较慢。
InnoDB支持行锁（必须有索引才能实现，否则会自动锁全表，那么就不是行了），适用于对事务完整性要求较高的系统。InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。
InnoDB行级锁类型

• 共享锁：共享锁也称为读锁，不会发送堵塞，多个线程可以同一时刻读取同一个共享资源，相互之间没有影响。

• 排它锁：排它锁也称为写锁，会发送堵塞，这样可以只允许一个线程进行写入，防止其他线程读取正在写入的资源。

意向锁：意向锁是InnoDB自动加的，不需要用户干预。
特点：意向锁之间是互相兼容的，意向共享锁和普通共享锁之间是兼容的，意向锁不会与行级锁共享，不会与排它锁互斥。

页面锁：介于表级锁与行级锁之间，会发生死锁。

注意：
MySQL InnoDb引擎中update,delete,insert语句自动加排他锁。

 

锁机制的常见问题

MySQL关于锁的常见问题

1. InnoDB存储引擎什么时候会锁住整张表(什么时候使用行级锁)，什么时候或只锁住一行呢(使用行锁)？

只有通过索引条件查询数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 记住：一定要记住为匹配条件字段加索引。

2. 什么时候使用行级锁？什么时候使用表级锁？

在增删改查时匹配的条件字段不带有索引时，innodb使用的是表级锁，

3. 行级锁锁的是什么？行级锁怎么实现加锁？

(1) 行级锁是针对索引加的锁；

(2) InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。

4. mysql读锁和写锁？

(1) 因为只有触发了读写锁，我们才会谈是进行行级锁定还是进行表级锁定；

(2) 用select 命令时触发读锁，当使用update,delete,insert时触发写锁，并且使用rollback或commit后解除本次锁定。

5. 常见的锁算法：

next KeyLocks锁，同时锁住记录(数据)，并且锁住记录前面的Gap

Gap锁，不锁记录，仅仅记录前面的Gap

Recordlock锁(锁数据，不锁Gap)

所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

6. 什么时候会释放锁？

提交事务或回滚事务就会释放锁。

 

乐观锁和悲观锁

悲观锁

悲观锁：悲观锁，顾名思义很悲观，总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候 都会上锁，读取数据时给加锁，其它事务 无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。这样别人想拿这个数据就会阻塞直到它拿到锁。

悲观锁的实现

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做 操作之前先上锁。Java中synchronized和ReentrantLock等也使用了悲观锁的思想。

 

乐观锁

乐观锁：相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作的独占性，但其对数据库的大量开销，往往是无法接受的。乐观锁顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是 在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

乐观锁的实现

1、乐观锁，大多是基于版本号机制（Version）实现。在基于数据库表的版本解决方案中,为数据增加一个版本标识，一般是通过 为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。当线程 A 要更新数据时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的version 值与当前数据库中的 version 值相等时才更新，否则采取重试或者丢弃策略。

2、在Java中java.util.concurrent.atomic包下的原子变量类就有使用了乐观锁来实现，比如CAS。
具体深入理解CAS见我另一篇博客：深入理解CAS

 

死锁

什么是死锁

死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，将会一直阻塞下去。

死锁的产生条件

产生死锁有四个必要的条件

• 互斥条件：每个资源一次只能被一个线程使用。
• 请求与保持条件：一个线程因请求资源而阻塞，对已经获取的资源保持不放。
• 不剥夺条件：进程已经获得资源，在未使用完之前，不能强行剥夺。
• 循环等待条件：若干线程形成一条头尾相接循环等待资源的关系。

如何避免死锁

避免死锁就从死锁产生的四个必要条件去考虑。

 

什么是锁升级

锁升级一般是从行锁升级为表锁的过程。

• MySQL的行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为InnoDB的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了。
• 当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的
  内容大于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记
  录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成 索引失效，行锁自然就会升级为表锁。
   

读写分离，主从复制

  我们要知道，任何数据库数据到了一定的级别的时候，他都会存在一些物理极限或者是我们网站的并发量越来越大的时候，一个单数据库的QPS（QPS：数据库的连接数）和TPS（TPS：数据库事务的连接数）的承载量会出现问题和爆满。一旦爆满就会出现我们数据库的无法提供连接，一旦无法连接那就拒绝，拒绝那就连接超时，连接超时的话，那么整个网站就瘫痪了，这就是问题所在，所以说我们就会进行减压，采用集群的方式，让读库和写库分开，一旦分开，就涉及到数据的同步问题，所以我们就需要主从复制。
 

读写分离

我们为什么要去完成主从复制？
  平常在我们的认知里会把主从复制，读写分离混成一个概念，其实他们是两个概念，他们两者的关系是相辅相成，读写分离完成的是读和写的分开，而主从复制是完成数据库的一致性的问题。
  首先我们先来说一下读写分离：
  从实际应用上来讲针对于数据库的操作通常都是“读多写少”，也就是说数据库读取数据的压力比较大，首先为了解决我们数据库服务器的压力，我们可以使用MySQL集群。我们在开发中有select语句，insert,update，delete语句，我们可以在我们的MySQL集群中，让其中一台服务器当成写库，我们的写库一般只有一个，但是我们的从库可以有n个，这样他们之间就可以构成一个集群，一主多从，或者一主一从或者一主n从的形式，那么我们读写分离来解决什么问题呢？读写分离就是把我们的对数据库的读和写分开，我们读取的时候会走我们的从库，也就是我们的slave库，我们写的时候，会走我们的主库，也就是我们的master库，这就完成了我们读写分离的操作，读写分离的操作可以缓解我们数据库读和写的压力，我们把这种读和写分开的操作称之为读写分离。

这个时候就涉及到一个问题了，如果在某一时刻，我们把数据写入到我们的主库，而我们查询的时候，我们又往从库去查，那么就会涉及到一个数据不一致的问题。

而解决这种数据不一致的问题，我们就必须使用我们的主从复制。

 

 
  我们需要知道，我们的读写分离是我们业务层面的东西，而主从复制是我们数据层面的东西。我们在业务层面用了读写分离，我们在数据层面就必须使用主从复制。因为我们需要保证我们数据的完整性。
 

主从复制

  由于读写分离，我们读库和写库分开，我们数据写到主库中，那么我们需要把我们的数据同步到我们的从库中，所以说，主从复制是解决MySQL数据库中读写分离数据不一致的问题，我们在主库中写，而写完之后我们会把数据同步到从库中。
 

主从复制原理

• 在Master主库中，主库上面的任何修改都会通过自己的I/O Thread（I/O 线程）保存在一个二进制日志文件里面Binary
  log里面。
• 从服务器上面也启动一个I/O Thread（I/O线程）,通过配置好的用户名和密码，连接到主服务器上面请求读取二进制文件，然后把读取的二进制日志文件写到本地的一个Realy log（中继日志）里面。
• 从服务器上面同时开启一个SQL Thread（SQL 线程）定时检查Realy
  log（中继日志）,如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
• 每个从库都收到主库二进制日志的全部内容副本