SQL查询语句执行流程
当客户端和servel端建立连接之后,先查询缓存,如果缓存命中就返回结果(一旦一张表中执行更新数据,这张表的缓存就会清空,所以缓存命中几率不高,特别是更新频繁的表),如果缓存没有命中,就将查询语句交给分析器进行语法分析,然后由优化器进行优化(有索引选择合适索引,join连接优化连接顺序),然后交生产执行计划,交由执行器执行
redoLog、undoLog和binLog
- redoLog(InnoDB引擎日志):为了提高效率,mysql不会每来一条更新操作都写一次磁盘,而是现将记录写到redoLog(顺序IO)中,然后更新内存,等到系统空闲时再将redoLog中的记录写到对应磁盘位置。redoLog数据结构是一个环形队列,一端写数据,一端刷磁盘(将redoLog日志文件中的更新,写到对应数据磁盘上)。
innodb_flush_log_at_trx_commit
设置成1,表示每次事务的redo Log都直接持久化到磁盘。 - undoLog:回滚日志,提供回滚操作,保证事务失败之后数据回滚到上一个版本。保存了事务发生之前的数据的一个版本,可以用于回滚,同时可以提供多版本并发控制下的读(MVCC),也即非锁定读。
- mysql的update操作:当redoLog写满、系统空闲、关机的时候,就会将redoLog中的数据刷盘,刷盘的时候会将redoLog记录的操作更新到磁盘中;当内存不够用,就会将最近不使用的数据页淘汰掉,如果淘汰的是脏页就要更新磁盘数据。
- binLog(Server层日志):binLog有两种模式,statement格式记录的是sql语句,而row格式记录的是内容(记两条,更新前一条更新后一条);Bin log 用于记录了完整的逻辑记录,所有的逻辑记录在 bin log 里都能找到,所以在备份恢复时,是以 bin log 为基础,通过其记录的完整逻辑操作,备份出一个和原库完整的数据。
sync_binlog
设置为1,表示每次事务的binLog都持久化到磁盘。
事务执行的流程
undo日志用于记录事务开始前的状态,用于事务失败时的回滚操作;redo日志记录事务执行后的状态,用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1,其对数据X进行修改,设X的原值是5,修改后的值为15,那么Undo日志为<T1, X, 5>,Redo日志为<T1, X, 15>。
(1)写undo日志到log buffer;
(2)执行事务,并写redo日志到log buffer;
(3)如果innodb_flush_log_at_trx_commit=1,则将redo日志写到log file,并刷新落盘。
(4)提交事务。
更新操作
- 更新操作的流程:mysql执行update更新操作的时候,如果该记录的数据页在内存中,就更新内存中数据页的记录,并把更新操作写到redoLog日志文件中;如果记录的数据页不在内存中,就将更新操作写到changeBuffer中(下次读取该数据页的时候进行merege),并将changBuffer写的操作写到redoLog日志文件中。
- 写redoLog的优势:将更新时的随机写转化为了顺序写,写redolog是顺序写的,先写redolog等合适的时候再写磁盘,间接的将随机写变成了顺序写,性能确实会提高不少。
- redoLog刷盘&内存更新脏页:当redoLog写满、系统空闲、关机的时候,就会将redoLog中的数据刷盘,刷盘的时候会将redoLog记录的操作更新到磁盘中;当内存不够用,就会将**最近最少使用(LRU)**的数据页淘汰掉,如果淘汰的是脏页就要更新磁盘数据。
- 两阶段提交,将 redo log 的写入拆成了两个步骤:prepare 和 commit,这就是"两阶段提交"。
LRU&大表查询
大表的全表查询是将查询数据放到net buffer
中,buffer满了就通过网络发送出去,所以不会因为查询大表造成OOM。
LRU
所有的查询读取的数据页被放在buffer pool
中,通过LUR算法管理(链表形式实现)。
①当查询记录在buffer pool
有,就将该数据页放到链首
②新插入的放在头,当有新查询需要加载数据页且buffer pool
满了的时候就需要淘汰旧页,将链尾数据页淘汰,将新查询的数据页放到链头。
大表查询的问题
对于上述的LRU的实现在查询大表的时候会对缓存造成严重破坏,查询大表会将缓存中之前加载的热点数据页全部淘汰掉,造成其他查询缓存命中很低。
InnoDB的LRU改进
按5:3将buffer pool
化分为yong区和old区,链表前5/8是yong区,后3/8是old区。数据插入时且buffer pool
不够时,淘汰链尾的数据页,将新加载数据页放到LRU_add
分界处,如果新插入数据页存在超过1秒,就重新放到链头,否则位置保持不变。这样就解决了大表查询对缓存的破坏。
删除操作
删除表中的数据通过drop table
可以完全删除这个表(数据加结构),但是使用delete
删除的时候是标记删除,将数据页上删除的记录标记为可复用。所以即使delete了记录也会有很多的空额空间,可以使用alter table A engine=InnoDB
重建表,释放空洞的空间,缩容。其过程就是创建一个新表,将数据拷贝一遍,使空间紧凑。
join实现流程和优化
- 被驱动表可以使用索引:当表a(N行)和表b(M行) join的时候,选择一个小表作为驱动表,大表作为被驱动表,驱动表会全盘扫描,被驱动表可以走索引,所以大表根据索引查找就是走树索引(可以使用索引),比较快,扫描行数N*log2M。
- 当被驱动表不能使用索引:由于被驱动表不能使用索引,索引比较次数是M*N,当数据很大时尽量不要使用join。①可以通过应用层拆分成多个语句然后再拼接查询结果。②使用临时表:将被驱动表(大表)过滤后的数据放入临时表(当前连接有效),并加索引,然后再去进行join。
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);
③hash join:mysql不支持,可以通过业务端实现。将t1表(驱动表)查出来存到一个hash结构中,获取表 t2 中满足条件的 2000 行数据。将这2000行拿去hash查找对应的t1表数据,拼成结果集返回。
InnoDB存储引擎(支持事务、行锁)
InnoDb数据页结构
操作系统每次读取数据按页读取,一页大小4kb;mysql一页的大小是16kb,当一行数据大于16kb就需要跨页存储
InnoDB的B+树索引
- 主键索引: InnoDB中的id通过B+数建立索引主键索引,b+树的每个节点是一个数据页(16K),非叶子节点存储的是主键+下一页的指针,叶子节点存储的是数据,一个页中可以存储多条数据,一个页中的数据通过以链表形式连接(以主键顺序)。当一页中的数据较多的时候,还会为链表再建立一个索引,加快一页中的查询速度。所有叶子节点都通过指针相连,所有当进行范围查询的时候,可以直接遍历叶子结点,速度很快。
- 一般两层的B+树可以存储20000条数据,三层B+树可以存储2000W条数据。假设id为int类型,非叶子节点中的一个(id,指针)对的大小就是(8+6)b,一页可以存储的指针对16 * 1024/(8+6)=1170个,每个指针对指向下层的一个叶子节点,假设一条数据大小是1kb,一页可以存储的数据大概是16条,所以一个两层的B+树能够存储的数据大概是1170*16=18724条。
- 联合索引:如果对多个字段建立一个联合索引,mysql会根据这几个字段顺序计算出对应的数值,根据这个数值建立一个B+树的索引,最底层叶子节点存储的是每个数值对应的id值,找到id之后再去主键索引中查找数据(回表)。回表需要连续访问两次索引,所以对于需要关联的字段可以创建联合索引,这样索引上有需要查询的字段就不会回表了。只有当全值匹配或者前缀匹配的时候才会走索引,缺失前缀不会走索引(最左前缀原则)
B+树索引与Hash索引的区别
简单地说,哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
从上面的图来看,B+树索引和哈希索引的明显区别是:
- 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;
- 从示意图中也能看到,如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索;
- 同理,哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询);
- 哈希索引也不支持多列联合索引的最左匹配规则;
- B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。
char和varchar的区别
-
VarChar:变长字符串,占用空间相对较少,会额外消耗1或2个字节存储字符串长度。
缺点:update的时候操作比较麻烦,当update后需要占用额外空间,并且当前页没有额外空间的话,InnoDB引擎就会分页。
适合场景:
字符串列的最大长度比平均长度大很多
列的更新很少,所以碎片不是问题
使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储 -
Char:定长字符串,MySQL总是根据定义的字符串长度分配足够的空间
适合场景:
适合存储手机号、身份证号、Md5等定长类型的字段
普通索引与唯一索引的选择
- 查询效率:两者在查询的速度上相差无几。普通索引在查询的时候变了树找到对应key之后还要继续向后遍历找寻所以相同key的记录。但数据存储和读取是基于数据页的,所以内存中操作效率比较快,两者查询效率相差无几。
- update效率:对应普通索引来说,update操作可以先将更新操作写到
buffer change
中,之后读取对应数据的时候再统一更新;但是对于唯一索引来说,更新操作由于要判断索引是否冲突,所以需要将对应数据页全部加载到内存,就没有必要使用buffer change
。所以对于数据量比较大,且多写少读的表使用buffer change
对效率提升比较大。buffer change可以通过参数innodb_change_buffer_max_size
来动态设置,大小表示占buffer pool
的百分比。 change buffer
:change buffer
是将更新操作记录在内存中,当下次查询该数据页的时候,就将查出来的数据和change buffer
的操作进行一次merege
,完成更新操作,这样减少了磁盘的IO。change buffer
虽然是保存在内存中的,但是断电或者宕机不会丢失,因为change buffer
在事务提交的时候会写入redoLog
中。
MySQL选错索引索引
在执行SQL语句的时候,优化器会根据语句执行时扫描行数、是否排序、是否使用临时表等因素选择最好的索引。但有时候优化器会选错索引,这时候怎么解决呢?
- 如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以使用
analyze table t
命令来重新统计索引信息。如果只是索引统计不准确,通过 analyze 命令可以解决很多问题,但是前面我们说了,优化器可不止是看扫描行数。 - 通过
force index(a)
强制走指定的索引
索引失效
-
使用了函数计算:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
mysql> select count(*) from tradelog where month(t_modified)=7
我们可以把 SQL 语句改成基于字段本身的范围查询,这样就可以用上 t_modified 索引的快速定位能力了。
mysql> select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
-
对于不改变有序性的函数,优化器也会放弃索引。比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句,这个加 1 操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。
-
多个字段的联合索引:没有遵循最左前缀原则索引会失效
-
不等(!=,<>),null判断(is null,is not null)会导致索引失效
-
隐式转换:触发隐式转换会放弃索引。
select * from tradelog where tradeid=110717;
这个sql的tradeid是varchar(32)类型且有索引,但上述SQL发生隐式转换等价于select * from tradelog where CAST(tradid AS signed int) = 110717;
,所以放弃了索引。
事务及锁原理
事务四大特性:原子性、隔离性、一致性、持久性
隔离级别:读未提交、读已提交、可重复读
- 读未提交(一般不会使用):可以读取事务未提交的数据,会产生脏读。
- 读已提交:一个事务只能读到另一个已经提交的事务修改的数据,并且其他事务对该数据进行一次修改并提交后,该事务都能查询到最新的值。不会出现脏读,会出现不可重复读、幻读。
- 可重复读(mysql默认隔离级别):一个事务第一次读取过某一条数据后,即使其他事务修改了该数据的值并提交,该事务之后再读该数据时,读到的任是第一次读到的值,而不是每次都读到不同的数据(既每个事务之间是隔离的)。不会出现脏读和不可重复读,但会出现幻读。
- 串行化:加锁,让每一次写操作都是串行的,不会出现脏读、幻读(对多行数据而言)、不可重复读(对一行数据前后两次读取不一样),会影响性能。
事务隔离的原理
每个事务都有一个事务号(transactionID),对一条数据的修改会形成一条版本链存储在undolog日志中,回滚指针指向上一条被事务修改的数据。读已提交和可重复读通过版本链和readview实现(MVCC—多版本并发控制)。
- 读已提交原理:mysql内部有一个readview保存活跃的事务id,读取某一条数据的时候只能够读取自己版本号修改的数据,或者是不在readview中的不活跃事务修改的数据(当有事务提交就会把readview中自己的事务id去掉)。因为每次select都会更新一下readview,所有可能两次select中有事务提交了,那么下一次select就会看到提交了的事务的修改(不可重复读)。
- 可重复读原理:读取某一条数据还是只能读取自己版本号修改的数据,或者是已提交事务修改的数据(数据trx_id不在readview中的数据),但是可重复读的隔离级别下,不会每次select都更新readview,所有不会出现不可重复读现象。
- 对于更新数据:更新数据(update语句)都是先读后写的,而这个读,只能读当前的值,称为“当前读”,忽略readview的版本问题,直接读取当前值进行更新。除了 update 语句外,select 语句如果加锁(lock in share mode),也是当前读
锁
- 读锁:共享锁(不阻塞其他事务获取读锁,但不能获取写锁)
写锁:排它锁(其他事务不能获取读锁和写锁)
select:不加锁(select操作不需要获取锁,不会被读锁、写锁阻塞) - 读操作:对于普通的select语句,InnDB不会加任何锁
- select…lock in share mode(当前读):将查找到的数据加上一个读锁,允许其他事务继续获取这些记录的读锁,不能回去这些记录的写锁(会阻塞)
- select…for update:将查找到的数据加上一个写锁,不允许其他事务获取这些记录的写锁和读锁。
- 写操作:DELETE、INSERT、UPDATE操作都会加写锁
事务提交时,释放锁,所以为了提高效率,你可以将最可能产生锁冲突的锁往最后放,这样锁住的时间就比较小,对并发影响较小。
在读已提交的隔离级别下,只对查询出来的行加锁(行锁)
可重复读的隔离级别下,通过加间隙锁防止了在写(update)的情况下产生幻读,利用MVCC防止了读(select)的时候产生幻读。当不走索引的时候,会对整个表加锁,其他事务无法修改,防止幻读。
锁的类型
- 共享锁:表级共享锁、行级共享锁,共享锁之间不互斥
- 排他锁:表级排他锁、表级共享锁,排它锁之间、排它锁和共享锁之间互斥
- 意向锁:InnoDB自动加的锁,目的是为了解决加表锁的效率问题。当对一行加排它锁或者共享锁的时候,InnoDB会自动为这个表加上意向排他锁或者意向共享锁。
当要对一个表加表锁的时候,先判断是否有表锁,再判断是否有意向锁。如果有意向写锁,就表明有行锁,表锁需要阻塞。有了意向锁就不用遍历全表查询是否有行级写锁了,增加了表锁的加锁效率。
加锁时机
- insert时全表锁,update是行级锁(非绝对-成功使用索引时锁行,否则锁表),select不加写锁,但可以手动加锁
- InnoDB行锁是通过给索引上的索引项加锁来实现的,innodb一般情况下走索引或者主键更新都是锁行,其余都是锁表
- InnoDB加锁 锁的是表或者索引,当走索引命中的时候就是锁的索引,当不走索引时就会锁全表
死锁
我们知道数据库的行锁在使用到的时候才会对某一行加锁,当事务提交的时候才释放锁。所以相互等待可能造成死锁。
解决死锁的策略
- 直接进入等待,直到等待超时,超时时间设置
innodb_lock_wait_timeout
- 死锁检查:发现死锁后,主动回滚死锁链中某一个事务,让其他事务继续执行。将参数
innodb_deadlock_detect
设置为no
加锁规则
-
全局锁:通过语句
Flush tables with read lock
对整个数据库加锁,整个数据库处于只读状态,一般在整库备份的时候使用。客户端断开连接自动释放锁。 -
表级锁:①表锁,
lock tables … read/write
显示加锁,unlock tables
释放锁,客户端断开连接也会释放锁。②元数据锁(MDL):当在访问一个表的时候会自动上锁。当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。读锁与读锁不互斥,写锁与读锁 读锁与写锁互斥。在事务提交的时候释放锁。 -
-
行锁:行锁是在引擎层由各个引擎自己实现的,但不是所有的引擎都支持行锁。在增删改某一行记录的时候会对该行甚至访问的行上行锁。行锁是在需要上锁的时候才上,在事务提交的时候释放。如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
-
间隙锁:为了解决幻读,InnoDB引入了间隙锁,锁的范围就是两值之间的空隙(只在可重复读下有间隙锁)。跟间隙锁冲突的是向间隙中插入数据,间隙锁和间隙锁之间不冲突。
间隙锁和行锁合称next-key lock,是前闭后开的区间,通过间隙锁和MVCC解决了幻读问题。
间隙锁的引入会导致同样的语句锁住的范围更大,会影响并发度。对于业务不要求可重复读的情况下,可以开启读已提交+binglog的binlog_format=row模式,避免间隙锁提高效率
间隙锁导致的死锁:
对于下表,如果用select * from t for update要把整个表所有记录锁起来,就形成了7个next-key lock,分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
1、sessionA执行select … for update语句,由于id=9这一行并不存在,因此会加上间隙锁(5,10)
2、sessionB执行select … for update语句,同样会加上间隙锁(5,10),间隙锁之间不会冲突
3、sessionB试图插入一行(9,9,9),被sessionA的间隙锁挡住了,只好进入等待
4、sessionA试图插入一行(9,9,9),被sessionB的间隙锁挡住了
两个session进入互相等待状态,形成了死锁。 -
next-key lock加锁规则:
①原则1:加锁的基本单位是next-key lock,next-key lock是前开后闭区间。
②原则2:查找过程中访问到的对象才会加锁。
③优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
④索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
3范式
1NF: 即表的列的具有原子性,不可再分解,即列的信息,不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server),就自动的满足1NF
2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现,既所有属性依赖于主键,不允许存在部分属性依赖主键
3NF: 即表中不要有冗余数据, 就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF