MySQL

MySQL有哪几种数据存储引擎?

可以使用SQL查看支持的数据存储引擎：

show ENGINES;

其中最为常用的是InnoDB和MyISAM两种，

MyISAM和InnoDB的区别：

  • 存储文件，MyISAM每个表有两个文件，MYD和MyISAM文件，MYD是数据文件，MYI是索引文件，而InnoDB每个表只有一个文件，idb
  • InnoDB支持事务，支持行锁，支持外键。
  • InnoDB支持XA事务。
  • InnoDB支持事务的savePoints

什么是脏读、不可重复读、幻读？

脏读、不可重复读、幻读的概念：

  • 脏读：在事务进行过程中，读到了其他事务未提交的数据。
  • 不可重复读：在一个事务过程中，多次查询的结果不一致。（update）
  • 幻读：在同一个事务中，用同样的操作查询数据，得到的记录数不相同。(insert)

处理的方式有很多种：加锁、事务隔离、MVCC，这里只介绍使用加锁来解决这些问题：

类型处理方式
脏读在修改时加排他锁，直到事务提交提交才释放，读取时加共享锁，读完释放锁
不可重复读读数据时加共享锁，写数据时加排他锁
幻读加范围锁

事务的基本特性和隔离级别

事务：表示多个数据操作组成一个完整的事务单元，这个事务内的所有数据操作要么同时成功，要么同时失败。

事务的特性：ACID

  • 原子性：事务是不可分割的，要么完全成功，要么完全失败。
  • 一致性：事务无论是完成还是失败。都必须保持事务内操作的一致性。当失败是，都要对前面的操作进行会滚，不管中途是否成功。
  • 隔离性：当多个事务操作一个数据的时候，为防止数据损坏，需要将每个事务进行隔离，互相不干扰
  • 持久性：事务开始就不会终止，他的结果不受其他外在因素的影响

在MySQL中可以设置事务的隔离级别：

SQL语句含义
SHOW VARIABLES like '%transaction'显示事务的隔离级别
set transction level **设置隔离级别
set session transaction level **当前会话的事务隔离级别
set global transaction level **当前全局的事务隔离级别

MySQL当中有五种隔离级别：

隔离级别具体含义
NONE不使用事务
READ UNCOMMITED允许脏读
READ COMMITED防止脏读，是最常用的隔离级别
REPEATABLE READ防止脏读和不可重复读，MySQL默认
SERIALIZABLE事务串行，可以防止脏读、幻读、不可重复度

五种隔离级别，级别越高，事务的安全性是更高的，但是，事务的并发性能也会越低。

MySQL的锁有哪些？什么是间隙锁？

从锁的粒度来区分：

1）行锁：加锁粒度小，但是加锁的资源开销比较大。InnoDB支持

共享锁：读锁，表示多个事务可以对同一个数据共享同一把锁，持有锁的事务都可以访问数据，但是只读不能修改。 select ** LOCK IN SHARE MODE

排他锁：写锁，只有一个事务能够获得排他锁，其他事务都不能获取该行的锁。InnoDB会对update，delete、insert语句自动添加排他锁。select ** for update。

自增锁：通常是针对MySQL当中的自增字段。如果有事务会滚这种情况，数据会回滚，但是自增序列不会回滚。

2）表锁：加锁粒度大，加锁资源开销比较小，MyIAM和InnoDB都支持。

表共享读锁，表排他写锁

意向锁：是InnoDB自动添加的一种锁，不需要用户干预。

3）全局锁：Flush tables with read lock，加锁之后整个数据库实例都处于只读状态，所有的数据变更操作都会被挂起，一般用于全库备份的时候

常见的锁算法：

1、记录锁：锁一条具体的数据。

2、间隙锁：RR隔离级别下，会加间隙锁。锁一定的范围，而不是锁具体的记录，是为了防止产生幻读。

3、Next-key：间隙锁 + 右记录锁。

MySQL索引结构是什么样的？

MySQL采用B+树作为存储索引的数据结构。

Mysql的索引结构为什么使用B+树？

总体来说有以下好处：

  • 可以减少磁盘IO的次数
  • 能够很好的同时支持等值查询和范围查询
    • 等值查询：哈希表、跳表不适合范围查询
    • 范围查询：二叉树/红黑树可以很好的满足范围查询，但当树过高时，会带来磁盘IO过高的问题；B树的范围查询，会一直到根节点再到叶子节点查询，B+树解决了范围查询的带来的问题
  • B树的查询效率不稳定，在O(1-logN)之间，而B+树可以稳定在O(logN)

完整链接：MySQL 为什么采用 B+树作为索引open in new window。

聚簇索引和非聚簇索引有什么区别？

聚簇索引：数据和索引是在一起。

非聚簇索引：数据和索引不在一起。

MyISAM使用的非聚簇索引，树的子节点上的data不是数据本身，而是数据存放的地址，InnoDB采用的是聚簇索引，树的叶子节点上的data就是数据本身。

聚簇索引的数据是物理存放顺序和索引顺序是一致的，所以一个表中只能有一个聚簇索引，而非聚簇索引可以有多个。

InnoDB中，如果表定义了PK，那PK就是聚簇索引，如果没有PK，就会找一个非空的unique列作为聚簇索引。否则，InnoDB会创建一个隐藏的row-id作为聚簇索引。

MySQL主键索引和普通索引有什么区别？

MySQL的索引覆盖和回表是什么？

如果只需要在一颗索引树上就可以获取SQL所需要的所有列，就不需要再回表查询，这样查询速度就可以更快。

实现索引覆盖最简单的方式就是将要查询的字段，全部建立到联合索引当中。

MySQL集群是如何搭建的？读写分离是怎么做的？

MySQL主从结构原理：

img

MySQL通过将主节点的Binlog同步给从节点完成主从之间的数据同步。

MySQL的主从集群只会讲Binlog从主节点同步到从节点，而不会反过来同步问题。

因为要保证主从之间的数据一致，写数据的操作只能在主节点完成。而读数据的操作，可以在主节点或者从节点上完成。

这种方式有丢失数据的风险，可以采用半同步的方式：

img

MySQL如何进行分库分表？多大数据量需要进行分库分表？分库分表的方式和分片策略由哪些？分库分表后，SQL语句执行流程是怎样的？

什么是分库分表：就是当表中的数据量过大时，整个查询效率就会降低的非常明显，这是为了提升查询效率，就要将一个表中的数据分散到多个数据库的多个表当中。

数据分片的方式有垂直分片和水平分片。垂直分片就是从业务角度将不同的表拆分到不同的表中，能够解决数据库数据文件过大的问题，但是不能从根本上解决查询问题。水平分片就是从数据角度将一个表中的数据拆分到不同的库或者表中，这样可以从根本上解决数据量过大造成的查询效率低的问题。

常见的分片策略有：

  • 取余/取模：优点：均匀存放数据，缺点，扩容非常麻烦
  • 按照范围分片：比较好扩容，数据分布不够均匀
  • 按照时间分片：比较容易将热点数据区分出来
  • 按照枚举值分片：例如按地区分片
  • 按照目标字段前缀指定进行分区：自定义业务规则分片

建议：一个表的数据量超过500W或者数据文件超过2G，就要考虑分库分表了，分库分表最常用的组件：MyCat、ShardingSphere

ShardingSphere分库分表的执行流程：

img

与之相关的会衍生出一系列的问题，例如一个user表，按照userid进行了分片，然后我需要按照sex字段去查，这样怎么查？强制指定只查一个数据库，要怎么做？查询结果按照userid来排序，要怎么排？

分库分表也并不是完美的，在解决了一些问题的同时，也带来了一定的缺点：

  • 事务一致性问题
  • 跨节点关联查询问题
  • 跨节点分页、排序函数
  • 主键避重

Mysql的三种删除方式的区别？

删除方式区别
delete删除数据，保留表结构，可以有条件的删除，也可以回滚数据，删除数据时进行两个动作：删除与备份
truncate删除所有数据，无条件选择删除，不可回滚，保留表结构
drop删除数据和表结构 删除速度最快

慢SQL的优化思路？

SQL优化的思路：

  • 定位优化对象的性能瓶颈，确定时IO还是CPU瓶颈
  • 从Explain入手，保证索引生效

limit语句会扫描全表吗？

大数量分页查询该怎么优化？

平时项目里面表结构是如何进行设计的？

MySQL的索引类型？

MySQL目前主要有以下几种索引类型：

  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引
  • 全文索引

MySQL如何快速插入千万级数据？

参见：大批量数据高效插入数据库表open in new window

要点总结：

  • 使用批处理语句（可能需要修改MySQL的Max_allowed_packet配置项）
  • 开启事务，在事务内进行插入操作
  • 数据有序插入，避免B+树出现分裂合并

补充：

  • 避免使用ORM框架