mysql应该知道的那些事

程序员读书会
226 阅读8分钟

sql执行过程

先上图，来源文章mysql执行过程以及顺序

主要分2层:

server层：

  1. 连接器：用户连接认证
  2. 分析器：
  3. 优化器
  4. 执行器

引擎层： 具体执行操作由引擎执行。

事务隔离机制

事务隔离级别脏读不可重复读幻读
读未提交（read-uncommitted）是是是
不可重复读（read-committed）否是是
可重复读（repeatable-read）默认否否是
串行化（serializable）否否否

串行化：它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。 在这个级别，可能导致大量的超时现象和锁竞争。

扩展：

  1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

  2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

  3. 幻读：指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的数据行。 MVCC课避免幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

四种事务隔离级别 ACID

　　1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。靠undolog保证，rollback时执行。

　　2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。（a、i、d）实现了c。

　　3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。（用锁实现）

　　4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。（redolog保证）

理解MVCC、多版本并发控制

innodb的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个是行的创建时间，一个保存行的过期时间。存储的是系统版本号，不是真实的时间。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。 就是乐观锁的实现。

阐述对数据的理解

一切皆数据，无数据不编程。

存储引擎的区别

  • InnoDB mysql默认引擎，支持事务，行锁、表锁（没有索引就会表锁）、外键。数据文件frm 表结构、idb数据，是聚集索引，索引和数据在一起。
  • MyISAM 不支持事务和行锁。支持表锁。数据文件：frm表结构、myd表数据、myi表索引，是非聚集索引，索引和数据文件是分开的。
  • MEMORY 内存数据库， 不能持久化，索引是hash。

索引原理

Mysql索引原理 简单概况就是InnoDB用的是B+数做索引，非叶子节点不存data，减少io次数，提高效率。

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

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

分库分表

可参考mycat的思路：

事务：尽量避免事务。

跨库Join的几种解决思路

  1. 全局表
  2. 字段冗余
  3. 数据同步，读写分离
  4. 系统层组装 说起来容易，做起来难。
  5. 读写分离，查询的放在只读库

limit： 如果是limit 0，n。给db1、db2都发送一条。取结果级的最小值。

如果是limit m，n。需要先执行limit 0，n。再取结果级的m，n。 非常耗时。

hash索引和btree索引

  • hash
  1. Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。
  2. Hash 索引无法排序,因为hash是取模。
  3. Hash 索引不支持多列联合索引的最左匹配规则
  4. Hash索引在任何时候都不能避免表扫描
  5. Hash索引每次查询要加载所有的索引数据到内存当中，而B+树只需要根据匹配规则选择对应的叶子数据加载即可。

sql查询大部分都是范围查找，而hash擅长的是等值判断。

B+树与B树的不同在于：

  1. 所有关键字存储在叶子节点，非叶子节点不存储真正的data
  2. 为所有叶子节点（左右相邻的节点之间）增加了一个链指针

explain

  • type type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般情况下，得保证查询至少达到range级别，最好能达到ref

  • key 实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

  • ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。

  • rows 根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好，绝大部分rows小的语句执行一定很快。所以优化语句基本上都是在优化rows。

mysql锁机制

InnoDB行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁。

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

乐观锁：不是数据库提供的锁机制，通过版本号或者时间戳来实现乐观锁。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};

悲观锁：在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。 要使用悲观锁，必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。set autocommit=0;

mysql日志

undolog：1.为了实现数据的原子性，操作数据时，先将数据备份到undolog。然后执行操作，如果需要rolback，就把unlog数据恢复。比如执行一条delete语句，就在undolog中执行一条insert语句。可以理解为回滚日志。日志总是和执行的操作相反。

redolog：

  1. 数据产生修改的时候，innodb先将数据写到redolog中，并更新内存，此时更新完成，innodb会在合适的时候将数据同步到磁盘中。
  2. 超过固定的大小后，会覆盖最先写入的日志。
  3. redolog，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。，crash-safe。

binlog：记录了对MySQL数据库执行更改的所有操作，不包括SELECT和SHOW这类操作。之前的操作都记录binlog。写的时候直接顺序追加到binlog，效率高。先写内存，再写磁盘。主从复制依赖binlog。

binlog属于mysql server层，是二进制文件。redolog、undolog属于InnoDB，是sql语句。

16、Mysql的主从同步原理

原理：

  1. 数据操作写入master的binlog，master启动dump线程，为每个slave发送biblog的二进制。
  2. slave启动io线程和sql线程，I/O将master中的binlog写入relay-log。sql线程将relay-log写入数据库。

mysql高可用

主从复制、MMM、Keepalived、MHA等。 参考mysql各个集群方案的优劣

avatar
文章
阅读
粉丝