1.MySql事务,事务的四大特性,事务带来的问题

Mysql中事务的隔离级别分为四大等级:读未提交(READ UNCOMMITTED)读提交 (READ COMMITTED)可重复读 (REPEATABLE READ)串行化 (SERIALIZABLE)
在Mysql中事务的四大特性主要包含:原子性(Atomicity)一致性(Consistent)隔离性(Isalotion)持久性(Durable),简称为ACID

  1. 原子性:是指事务的原子性操作,对数据的修改要么全部执行成功,要么全部失败,实现事务的原子性,是基于日志的Redo/Undo机制。
  2. 一致性:是指执行事务前后的状态要一致,可以理解为数据一致性。
  3. 隔离性:侧重指事务之间相互隔离,不受影响,这个与事务设置的隔离级别有密切的关系。
  4. 持久性:则是指在一个事务提交后,这个事务的状态会被持久化到数据库中,也就是事务提交,对数据的新增、更新将会持久化到数据库中。

原子性、隔离性、持久性都是为了保障一致性而存在的,一致性也是最终的目的。
没有那种隔离级别是完美的,只能根据自己的项目业务场景去评估选择最适合的隔离级别,大部分的公司一般选择Mysql默认的隔离级别:可重复读

隔离级别从:读未提交-读提交-可重复读-串行化,级别越来越高,隔离也就越来越严实,到最后的串行化,当出现读写锁冲突的时候,后面的事务只能等前面的事务完成后才能继续访问。

  1. 读未提交:读取到别的事务还没有提交的数据,从而产生了脏读。
  2. 读提交:读取别的事务已经提交的数据,从而产生不可重复读。
  3. 可重复读:事务开启过程中看到的数据和事务刚开始看到的数据是一样的,从而产生幻读,在Mysql的中通过MVCC多版本控制的一致性视图解决了不可重复读问题以及通过间隙锁解决了幻读问题。
  4. 串行化:对于同一行记录,若是读写锁发生冲突,后面访问的事务只能等前面的事务执行完才能继续访问。

2.MVCC

MVCC叫做多版本控制,实现MVCC时用到了一致性视图,用于支持读提交和可重复读的实现。

对于一行数据若是想实现可重复读取或者能够读取数据的另一个事务未提交前的原始值,那么必须对原始数据进行保存或者对更新操作进行保存,这样才能够查询到原始值。

在Mysql的MVCC中规定每一行数据都有多个不同的版本,一个事务更新操作完后就生成一个新的版本,并不是对全部数据的全量备份,因为全量备份的代价太大了。

3.Mysql的InnoDB和MyISAM区别

  1. InnoDB和MyISAM都是Mysql的存储引擎,现在MyISAM也逐渐被InnoDB给替代,主要因为InnoDB支持事务和行级锁,MyISAM不支持事务和行级锁,MyISAM最小锁单位是表级。因为MyISAM不支持行级锁,所以在并发处理能力上InnoDB会比MyISAM好。
  2. 数据的存储上:MyISAM的索引也是由B+树构成,但是树的叶子结点存的是行数据的地址,查找时需要找到叶子结点的地址,再根据叶子结点地址查找数据。
    InnoDB的主键索引的叶子结点直接就是存储行数据,查找主键索引树就能获得数据,若是根据非主键索引查找,非主键索引的叶子结点存储的就是,当前索引值以及对应的主键的值,若是联合索引存储的就是联合索引值和对应的主键值。
  3. 数据文件构成:MyISAM有三种存储文件分别是扩展名为:.frm(文件存储表定义)、.MYD (MYData数据文件)、.MYI (MYIndex索引文件)。而InnoDB的表只受限于操作系统文件的大小,一般是2GB。
  4. 查询区别:对于读多写少的业务场景,MyISAM会更加适合,而对于update和insert比较多的场景InnoDB会比较适合。
  5. coun()区别:select count() from table,MyISAM引擎会查询已经保存好的行数,这是不加where的条件下,而InnoDB需要全表扫描一遍,InnoDB并没有保存表的具体行数。
  6. 其它的区别:InnoDB支持外键,但是不支持全文索引,而MyISAM不支持外键,支持全文索引,InnoDB的主键的范围比MyISAM的大。

4.查询语句的流程

当Mysql执行一条查询的SQl的时候大概发生了以下的步骤:

  1. 客户端发送查询语句给服务器。
  2. 服务器首先进行用户名和密码的验证以及权限的校验。
  3. 然后会检查缓存中是否存在该查询,若存在,返回缓存中存在的结果。若是不存在就进行下一步。注意:Mysql 8就把缓存这块给砍掉了。
  4. 接着进行语法和词法的分析,对SQl的解析、语法检测和预处理,再由优化器生成对应的执行计划。
  5. Mysql的执行器根据优化器生成的执行计划执行,调用存储引擎的接口进行查询。服务器将查询的结果返回客户端。
    Mysql中语句的执行都是都是分层执行,每一层执行的任务都不同,直到最后拿到结果返回,主要分为Service层和引擎层。
    在Service层中包含:连接器、分析器、优化器、执行器。引擎层以插件的形式可以兼容各种不同的存储引擎,主要包含的有InnoDB和MyISAM两种存储引擎。

image.png

5.redo log和binlog

redo log日志也叫做WAL技术(Write- Ahead Logging),他是一种先写日志,并更新内存,最后再更新磁盘的技术,为了就是减少sql执行期间的数据库io操作,并且更新磁盘往往是在Mysql比较闲的时候,这样就大大减轻了Mysql的压力。

redo log是固定大小,是物理日志,属于InnoDB引擎的,并且写redo log是环状写日志的形式。若是四组的redo log文件,一组为1G的大小,那么四组就是4G的大小,其中write pos是记录当前的位置,有数据写入当前位置,那么write pos就会边写入边往后移。

check point记录擦除的位置,因为redo log是固定大小,所以当redo log满的时候,也就是write pos追上check point的时候,需要清除redo log的部分数据,清除的数据会被持久化到磁盘中,然后将check point向前移动。

redo log日志实现了即使在数据库出现异常宕机的时候,重启后之前的记录也不会丢失,这就是crash-safe能力。

binlog称为归档日志,是逻辑上的日志,它属于Mysql的Server层面的日志,记录着sql的原始逻辑,主要有两种模式:一个是statement格式记录的是原始的sql,而row格式则是记录行内容。

redo log和binlog记录的形式、内容不同,这两者日志都能通过自己记录的内容恢复数据。

之所以这两个日志同时存在,是因为刚开始Mysql自带的引擎MyISAM就没有crash-safe功能的,并且在此之前Mysql还没有InnoDB引擎,Mysql自带的binlog日志只是用来归档日志的,所以InnoDB引擎也就通过自己redo log日志来实现crash-safe功能。

6.线上要给热点数据表添加字段

首先给表加一个字段,会导致扫描全表数据,并且会加MDL写锁,所以在线上操作一定要谨慎再谨慎,有可能还没操作完就导致数据库给搞崩了。

对于这种情况有限考虑线上的稳定的运行,加字段是其次,可以通过在alter table后设定等待的时间,若是获取不到锁后面在进行尝试,并且可以选择访问量比较上的时间段进行获取。

若是能获取到锁那是最好了,当然即使获取到锁也不要阻塞后面的业务语句,一切都是以业务优先为原则。

7.Msyql的索引的底层实现

Mysql的索引是一种加快查询速度的数据结构,索引就好比书的目录一样能够快速的定位你要查找的位置。Mysql的索引底层是使用B+树的数据结构进行实现。
索引的一个数据页的大小是16kb,从磁盘加载到内存中是以数据页的大小为单位进行加载,然后供查询操作进行查询,若是查询的数据不在内存中,才会从磁盘中再次加载到内存中。

索引的实现有很多,比如hash。hash是以key-value的形式进行存储,适合于等值查询的场景,查询的时间复杂度为O(1),因为hash储存并不是有序的,所以对于范围查询就可能要遍历所有数据进行查询,而且不同值的计算还会出现hash冲突,所以hash并不适合于做Mysql的索引。

有序数组在等值查询和范围查询性能都是非常好的,那为什么又不用有序数组作为索引呢?因为对于数组而言作为索引更新的成本太高,新增数据要把后面的数据都往后移一位,所以也不采用有序数组作为索引的底层实现。

最后二叉树,主要是因为二叉树只有二叉,一个节点存储的数据量非常有限,需要频繁的随机IO读写磁盘,若是数据量大的情况下二叉的树高太高,严重影响性能,所以也不采用二叉树进行实现。

而B+树是多叉树,一个数据页的大小是16kb,在1-3的树高就能存储10亿级以上的数据,也就是只要访问磁盘1-3次就足够了,并且B+树的叶子结点上一个叶子结点有指针指向下一个叶子结点,便于范围查询。

8.查看索引是否生效,索引失效原因

查看索引是否起作用可以使用explain关键字,查询后的语句中的key字段,若是使用了索引,该字段会展示索引的名字。
原因:

  1. where条件查询中使用了or关键字,有可能使用了索引进行查询也会导致索引失效,若是想使用or关键字,又不想索引失效,只能在or的所有列上都建立索引。
  2. 条件查询中使用like关键字,并且不符合最左前缀原则,会导致索引失效。
  3. 条件查询的字段是字符串,而错误的使用where column = 123 数字类型也会导致索引失效。
  4. 对于联合索引查询不符合最左前缀原则,也会导致索引失效。
  5. 在where条件查询的后面对字段进行null值判断,会导致索引失效,解决的办法就是可以把null改为0或者-1这些特殊的值代替
  6. 在where子句中使用!= ,< >这样的符号,也会导致索引失效。
  7. where条件子句中=的左边使用表达式操作或者函数操作,也会导致索引失效。

9.索引种类

索引从数据结构进行划分的分为:B+树索引、hash索引、R-Tree索引、FULLTEXT索引。

索引从物理存储的角度划分为:聚族索引和非聚族索引。

从逻辑的角度分为:主键索引、普通索引、唯一索引、联合索引以及空间索引。

10.SQL优化

SQL的优化主要是对字段添加索引,主要包含有这四种索引(主键索引/唯一索引/全文索引/普通索引),以及结合具体的业务场景分析具体是使用什么索引最合理。
explain 可以帮助我们在不真正执行某个sql语句时,就查询mysql怎样执行,这样利用我们去分析sql指令。

  1. id:查询的序列号。
  2. select_type:查询类型。
  3. table:查询表名。
  4. type:扫描方式,all表示全表扫描。
  5. possible_keys:可是使用到的索引。
  6. key:实际使用到的索引。
  7. rows:该sql扫面了多少行。
  8. Extra:sql语句额外的信息,比如排序方式。

优化方法

(1)对于条件查询,首先考虑在条件where和order by后的字段建立索引。
(2)避免索引失效,避免where条件后进行null值的判断。
(3)避免where后使用!=或<>操作符。
(4)避免在where后面进行使用函数。(5)避免where条件后使用or关键字来连接。

索引的种类

另一方面就是考虑到底是建立哪种索引比较合适,这里以普通索引和唯一索引进行举例说明。

假如我们的业务场景是读多写少的场景,那么SQL查询请求过来,假如数据已经在内存中,获取到数据后就直接返回,假如数据不在内存的数据页中,就会加载磁盘到内存中再返回,对于这种场景可能对于普通索引和唯一索引的选择性能上并没有明显的区别。

但是,一般建议选择普通索引,在写多读少的场景下,这两者索引的选择对性能的影响就比较大了,对于普通索引的的写,不管数据是否存在于内存中,都会先写入内存中的一小块叫做chang buffer内存中,然后在通过后台刷盘,一般会选择Mysql比较闲的时候进行刷盘。

而唯一索引就不同了,因为他要确保索引的唯一性,索引写数据的时候,假如数据不在内存中,要先从磁盘中加载数据到内存中,然后比较是否唯一,所以唯一索引就不能使用chang buffer的优化机制,会频繁的进行随机的磁盘IO。

11.聚簇索引和非聚簇索引

聚族索引和非聚族索引的主要区别是:聚族索引的叶子结点就是数据节点,而非聚族索引的叶子结点存储仍然是索引节点,只不过有指向对应数据块的指针。

12.回表

InnoDB引擎的主键索引存储的是行数据,二级索引的叶子结点存储的是索引数据以及对应的主键,所以回表就是根据索引进行条件查询,回到主键索引树进行搜索的过程。因为查询还要回表一次,再次查询主键索引树,所以实际中应该尽量避免回表的产生。
解决:建立联合索引进行索引覆盖。

13.最左前缀原则

最左前缀原则可以是联合索引的的最左N个字段,也可以是字符串索引的最左的M个字符。

14.主键使用自增ID还是UUID

自增ID和UUID作为主键的考虑主要有两方面,一个是性能另一个就是存储的空间大小,一般没有特定的业务要求都不推荐使用UUID作为主键。

因为使用UUID作为主键插入并不能保证插入是有序的,有可能会涉及数据的挪动,也有可能触发数据页的分裂,因为一个数据页的大小就是16KB,这样插入数据的成本就会比较高。

而自增ID作为主键的话插入数据都是追加操作,不会有数据的移动以及数据页的分裂,性能会比较好。

另一方面就是存储空间,自增主键一般整形只要4个字节,长整形才占8字节的大小空间,而使用UUID作为主键存储空间需要16字节的大小,会占用更多的磁盘,在二级索引中也会存出一份主键索引,这样多占用消耗的空间就是两倍,性能低,所以不推荐使用。

15.Mysql控制并发的访问资源

Mysql内部通过锁机制实现对资源的并发访问控制,保证数据的一致性,锁机制的类型和引擎的种类有关,MyISAM中默认支持的表级锁有两种:共享读锁和独占写锁。表级锁在MyISAM和InnoDB的存储引擎中都支持,但是InnoDB默认支持的是行锁。

MyISAM锁机制

(1)MyISAM表级写锁:当一个线程获取到表级写锁后,只能由该线程对表进行读写操作,别的线程必须等待该线程释放锁以后才能操作。

(2)MyISAM表级共享读锁:当一个线程获取到表级读锁后,该线程只能读取数据不能修改数据,其它线程也只能加读锁,不能加写锁。

InnoDB锁机制

InnoDB和MyISAM不同的是,InnoDB支持行锁和事务,InnoDB中除了有表锁和行级锁的概念,还有Gap Lock(间隙锁)、Next-key Lock锁,间隙锁主要用于范围查询的时候,锁住查询的范围,并且间隙锁也是解决幻读的方案。

InnoDB中的行级锁是对索引加的锁,在不通过索引查询数据的时候,InnoDB就会使用表锁。

但是通过索引查询的时候是否使用索引,还要看Mysql的执行计划,Mysql的优化器会判断是一条sql执行的最佳策略。

若是Mysql觉得执行索引查询还不如全表扫描速度快,那么Mysql就会使用全表扫描来查询,这是即使sql语句中使用了索引,最后还是执行为全表扫描,加的是表锁。

16.Mysql的死锁

死锁在InnoDB中才会出现死锁,MyISAM是不会出现死锁,因为MyISAM支持的是表锁,一次性获取了所有的锁,其它的线程只能排队等候。

而InnoDB默认支持行锁,获取锁是分步的,并不是一次性获取所有的锁,因此在锁竞争的时候就会出现死锁的情况。

虽然InnoDB会出现死锁,但是并不影响InnoDB成为最受欢迎的存储引擎,MyISAM可以理解为串行化操作,读写有序,因此支持的并发性能低下。

17.Mysql的主从复制

读写分离

实现MySQL读写分离的前提是我们已经将MySQL主从复制配置完毕,读写分离实现方式:(1)配置多数据源。(2)使用mysql的proxy中间件代理工具。

主从复制的原理

MySQL的主从复制和读写分离两者有着紧密的联系,首先要部署主从复制,只有主从复制完成了才能在此基础上进行数据的读写分离。
image.png

读写分离的原理

读写分离就是只在主服务器上写,只在从服务器上读。基本原理是让主数据库处理事务性查询,而从服务器处理select查询。数据库复制被用来把事务性查询导致的变更同步到从数据库中。

18.分库分表

原因:
(1) 如果一个表的每条记录的内容很大,那么就需要更多的IO操作,如果字段值比较大,而使用频率相对比较低,可以将大字段移到另一张表中,当查询不查大字段的时候,这样就减少了I/O操作
(2)如果表的数据量非常非常大,那么查询就变的比较慢;也就是表的数据量影响查询的性能。
(3)表中的数据本来就有独立性,例如分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用,而另外一些数据不常用。
(4) 分表技术有(水平分割和垂直分割)

垂直分割

垂直分割是指数据表列的拆分,把一张列比较多的表拆分为多张表。垂直分割一般用于拆分大字段和访问频率低的字段,分离冷热数据。

垂直分割比较常见:例如博客系统中的文章表,比如文章tbl_articles (id, titile, summary, content, user_id, create_time),因为文章中的内容content会比较长,放在tbl_articles中会严重影响表的查询速度,所以将内容放到tbl_articles_detail(article_id, content),像文章列表只需要查询tbl_articles中的字段即可。

垂直拆分的优点:可以使得行数据变小,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。

垂直拆分的缺点:主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。此外,垂直分区会让事务变得更加复杂。

水平分割

水平拆分是指数据表行数据的拆分,表的行数超过500万行或者单表容量超过10GB时,查询就会变慢,这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当,比较均匀。

水平拆分会给应用增加复杂度,它通常在查询时需要多个表名,查询所有数据需要union操作。在许多数据库应用中,这种复杂性会超过它带来的优点。

因为只要索引关键字不大,则在索引用于查询时,表中增加2-3倍数据量,查询时也就增加读一个索引层的磁盘次数,所以水平拆分要考虑数据量的增长速度,根据实际情况决定是否需要对表进行水平拆分。

水平分割最重要的是找到分割的标准,不同的表应根据业务找出不同的标准

  1. 用户表可以根据用户的手机号段进行分割如user183、user150、user153、user189等,每个号段就是一张表。
  2. 用户表也可以根据用户的id进行分割,加入分3张表user0,user1,user2,如果用户的id%3=0就查询user0表,如果用户的id%3=1就查询user1表。
  3. 对于订单表可以按照订单的时间进行分表。

分库分表技术

主要的分库分表技术有mycat和sharding-jdbc。

Q.E.D.


Nothing really matters to me!