欢迎访问我的博客,你的支持,是我最大的动力!

MySQL王者晋级之路(三)索引、事务、锁

Mysql-笔记 小马奔腾 7590℃ 评论
目录:
[显示]

《MySQL王者晋级之路》张甦,电子工业出版社,2018.3

基于MySQL5.6和MySQL5.7

索引

索引是对数据库表中一列或多值值进行排序的一种结构。MySQL数据库两个主要的索引是:B+tree索引和哈希索引

演化

二叉树 -> 平衡二叉树 -> B-tree ->B+tree

二叉树:左子树<根<右子树
平衡二叉树:左右两子树高度差绝对值不超过1
B-tree:又称Btree,一个结点可以拥有多个子结点,所有叶子结点位于同一层,叶子结点不包含任何关键字信息
B+tree:所有关键字信息都出现在叶子结点,并含有关键字记录的指针,所有数据都保存在叶子结点中
B+tree索引是双向链表结构

聚集索引和普通索引
对聚集索引,索引键值的逻辑决定了表数据行的物理存储顺序,叶子结点存放表中行数据,数据即索引、索引即数据
创建表时,要显式为表创建一个主键(聚集索引),若不主动创建主键,InnoDB会选择第一个不包含null值的唯一索引作为主键索引,若没有唯一索引,则使用rowid作为主键对普通索引,叶子结点仅有自己本身的键值和主键的值,通过普通索引叶子结点上的主键来获取要查找的行数据记录
普通索引创建语法
alter table table_name add index index_name (索引字段);
create index index_name on table_name (索引字段);
查看表中的索引
show index from table_name

实验

CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT ,
name varchar(20) NOT NULL ,
address varchar(20) NOT NULL ,
PRIMARY KEY (id)
);
desc t;
select count(*) from t;
explain select * from t where name='cc';
#查看执行计划
type列出现ALL,代表全表扫描
key列,看是否使用了索引,null代表没有使用索引
row列,扫描的行数
extra列,若出现using filesort或者using temporary关键字,说明很影响性能
filtered列,返回结果行占需要读到行的百分比,估算值
#优化思路
1)表的数据类型是否合理,要遵守选取数据类型越简单越小的原则
2)表中碎片是否整理
3)表的统计信息是否收集,只有统计信息准确,执行计划才能帮助优化SQL
4)查看执行计划,检查索引使用情况,没有用到索引,考虑创建
5)创建索引前,查看索引的选择性distinct,判断该字段是否适创建索引
索引选择性指不重复的索引值和数据记录总数的比值,越接近1越好;主键索引和唯一索引选择性是1
6)创建索引后,再查看执行计划,对比两次结果,查看效率是否提高
#合理创建索引的三个经常
1)经常被查询的列(一般放在where条件后面)
2)经常用于表连接的列
3)经常排序分组的列(order by/group by后面的字段)

#为name字段创建索引
查看name字段的索引选择性
select count(distinct name)/count(*) from t;
创建索引
create index idx_name on t(name);
show index from t\G
explain select * from t where name='cc'\G

ICP、MRR和BKA

ICP,index condition pushdown是使用索引从表中检索行数据的一种优化方式,5.6开始支持,如果where后的条件可以使用索引,则将过滤放到存储层执行,而不是原来的由server层处理,可以减少基表访问次数和server层访问存储引擎次数,默认开启

show variables like "%optimizer_switch%"\G
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on
开关命令
set optimizer_switch="index_condition_pushdown=on|off";
当使用ICP优化时,执行计划extra列会显示Using index condition关键字

MRR,multi-range read optimization,5.6之后新增,通过optimizer_switch中的mrr和mrr_cost_based控制,默认均为开启状态
mrr_cost_based选项表示是否通过基于成本的算法来确定开启mrr特性,on为自动判断,off为强制开启mrr

#set global optimizer_switch="mrr=on|off,mrr_cost_based=on|off";
set optimizer_switch="mrr=on|off,mrr_cost_based=on|off";
当mrr=on,mrr_cost_based=off时,表示总是开启MRR优化
当使用MRR优化时,执行计划的extra列会显示Using MRR关键字

普通情况下,普通索引获取数据方式为,通过索引叶子结点找到对应的主键,再通过主键找到相对应的行数据记录。若普通索引有重复值,那么该字段做where条件时,每次取到的主键不是顺序的,会发生随机IO
MRR原理:将找到的主键值存储到read_rnd_buffer中,并对buffer进行排序,最后利用排序过的主键值,访问表中的数据,将原来的随机IO变成顺序IO,降低IO开销
生产环境中,read_rnd_buffer_size 可设置4~8MB之间

BKA,batched key access,提高表join性能的算法,作用是在读取join表的记录时使用顺序IO
原理:对于多表join语句,当使用索引访问第二个join表时,使用一个join buffer收集第一个操作对象生成的相关列值。BKA构建好key后,批量传给引擎层做索引查找,key是通过MRR接口提交给引擎的,这样使得查询更加高效

BKA默认是关闭的
要想开启该功能,要保证是在强制使用MRR的基础上才可以
set global optimizer_switch="mrr=on,mrr_cost_based=off";
set global optimizer_switch="batched_key_access=on";
当BKA被使用时,执行计划的extra列会显示Using join buffer(Batched Key Access)关键字

MRR与BKA之间的关系

存储引擎上端是MRR,范围扫描range access中将扫描到的数据存入read_rnd_buffer_size,对其按照主键rowid进行排序,然后使用排序后的数据顺序回表,转换随机读取为顺序读取
BKA中,被连接表使用ref、eq_ref索引方式扫描时,第一个表中扫描的键值放到join_buffer_size中,然后调用MRR接口进行排序并顺序访问且通过join条件得到数据,这样连接条件成为顺序比对

主键索引和唯一索引

主键索引就是聚集索引,每张表有且仅有一个主键,可以由表中一个或多个字段组成
主键的条件:值唯一、没有null值、保证该值自增
使用自增列做主键,可以保证写入数据的顺序也是自增的,增高存取效率

创建主键
alter table table_name add primary key(colunm_name);

唯一索引是约束条件的一种,不允许有重复的值,但可以有null值。一个表可以有多个唯一索引

创建唯一索引
alter table table_name add unique(colunm_name);
覆盖索引
MySQL只需要通过索引就可以返回查询数据,而不必在查到索引后再回表查询数据,这样减少大量IO。在执行计划extra列中会出现Using index关键字
explain select id from t where name="cc"\G
普通索引相尖于(name,id)索引
#如果查询的是address字段,那么就不是覆盖索引了,因为拿到主键后,还需要回表
前缀索引
对blob、text或长的varchar类型的列,将其前几个字符(具体长度在建立索引时指定)建立索引,这样的索引叫做前缀索引
前缀索引较小,查询更快
前缀索引不能在order by 或 group by中使用,也不能用作覆盖索引创建前缀索引
alter table table_name add key(column_name(prefix_length));
#prefix_length是长度,根据实际需要设置
联合索引
联合索引,复合索引,对两个或以上的列创建索引,利用索引中的附加列,缩小检索的段池范围,更快地搜索到数据
创建语法和普通索引一样
create index idx_c1_c2 on table_name(c1,c2);
联合索引必须满足最左前缀原则,一般把选择性高的列放前面
一条查询语句可以只使用索引中的一部分,但必须从最左侧开始#可以使用到索引
select * from t where c1=某值;
select * from t where c2=某值 and c1=某值;
select * from t where c1=某值 and c2 in (某值,某值);
select * from t order by c1,c2;
select * from t where c1=某值 order by c2;#不能使用索引
select * from t where c2=某值;
select * from t where c2=某值 order by c1;
select * from t where c1=某值 or c2=某值;尽量在生产环境中,让程序多做一些判断,不要让数据库做各种运算
尽量避免在SQL语句中出现or,多列可以考虑使用union
哈希索引

哈希索引使用哈希算法,把键值换算成新的哈希值,只能进行等值查询,不能进行排序、模糊查找、范围查询等
检索时一次哈希算法立刻定位,速度非常快

索引的总结

索引优点
1)提高数据检索效率
2)提高聚合函数效率
3)提高排序效率
4)使用覆盖索引可以避免回表

索引创建四不要
1)选择性低的字段不要创建索引,如性别、状态字段
2)很少查询的列不要创建索引
3)大数据类型字段不要创建索引
4)尽量避免使用NULL,应指定列为NOT NULL
#含有空值的值很难进行查询优化,会使得索引、索引的统计及比较运算更加复杂。可以使用空字符串代替空值

使用不到索引的情况
1)通过索引扫描的行记录数超过全表30%,优化器不会走索引,变成全表扫描
2)联合索引中,第一个查询条件不是最左索引列
3)联合索引中,第一个索引列使用范围查询,只能使用到部分索引,有ICP出现
#范围指:< ,= ,<=, between and等
4)联合索引中,第一个查询条件不是最左前缀列
5)模糊查询中,最左以通配符%开始
6)两个单列索引,一个用于检索,一个用于排序,这种情况下只能使用一个索引
#查询语句中最多只能使用一个索引,考虑建立联合索引
7)查询字段上有索引,但使用了函数运算

事务

事务是一组DML(insert、delete、update)语句的集合
InnoDB存储引擎支持事务;MyISAM不支持
MySQL事务默认是自提交模式,要开启事务,必须以begin命令开始,以commit/rollback结束

事务的特性

原子性atomicity
所有操作要么做,要么都不做

一致性consistency
数据在事务操作前和事务处理后必须满足业务规则约束

隔离性isolation
允许多个并发事务同时对数据进行读写和修改的能力

持久性durability
事务处理结束后,对数据的修改是永久的,即使系统发生故障也不丢失

事务语句

事务开启由begin或者start transaction (read write|read only)开始,或者把自提交特性关掉set autocommit=0
事务结束以commit或者rollback结束

隐式提交:DDL语句的操作(DDL语句,如建表命令,默认知带一个commit);再次输入begin或start transaction命令
隐式回滚:退出会话;连接超时;关机

oracle中,事务不是自动提交的,而mysql中默认是自动提交的。
不建议关闭自提交模式,关闭后,不用一个事务一次提交,可以多个事务一起提交,可以提高每秒处理事务的能力,但是若这个过程中有一个事务一直没有提交,那么会导致行锁等待的现象,其他事务必须等这个事务提交后才能继续提交

truncate和delete的区别

truncate是DDL语句,delete是DML语句,它们共同点都是清空表内数据
truncate在事务中不能被回滚,且truncate会清空表的自增属性

事务的隔离级别

InnoDB存储引擎实现SQL标准的4种隔离级别,用来限定事务内外的哪些改变是可见的,哪些是不可见的
低级别的隔离一般支持更高的并发并拥有更低的系统开销

查看当前隔离级别
show variables like "%tx_isolation%";
#REPEATABLE-READ
修改全局/会话事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
set session|global transaction isolation level read uncommitted;
#四个级别:read uncommitted、read committed、repeatable read(默认)、serializable读未提交read uncommitted,简称RU
在其中一个事务中,可以读取其他事务未提交的数据变化,脏读。生产环境中不建议使用读已提交read committed,简称RC
在其中一个事务中,可以读取其他事务已提交的数据变化,不可重复读,允许幻读现象的发生,Oracle数据库默认事务隔离级别可重复读repetable read,简称RR
在其中一个事务中,直到事务结束前,都可以反复读取到事务刚开始看到的数据,并一直不会发生变化,避免了脏读、不可重复读和幻读的发生。默认串行serializable
在每个读的数据行上都需要加表级共享锁,在每次写数据时都要加表级排他锁
会造成并发能力下降,大量的超时和锁竞争,不建议使用到生产环境

脏读、不可重复读、幻读、可重复读

脏读

一个事务读取了其他事务还没有提交的数据

set global transaction isolation level read uncommitted;
show variables like "%tx_isolation%";

不可重复读与幻读

读已提交的数据
set global transaction isolation level read read committed;
show variables like "%tx_isolation%";

不可重复读是读取到了其他事务针对旧数据的修改记录(update/delete)

幻读读取到了其他事务新增的数据,仿佛出现了幻影(insert)

可重复读

MySQL默认级别
set global transaction isolation level repeatable read;
show variables like "%tx_isolation%";

读取到的还是事务一开始的数据,并不会读到新增的内容,要读取到新增的数据,可以在查询语句后添加for update
select * from t for update;

为保证数据一致性,让各种共享资源在被并发访问时变得有序而设置的规则

不同的存储引擎支持不同的锁机制。InnoDB支持行锁,有时也会升级为表锁;MyISAM只支持表锁
表锁的特点是开销小、加锁快;不会出现死锁;锁粒度大,发生锁冲突概率高,并发度相对低
行锁特点是开销大,加锁慢;会出现死锁;锁粒度小,发生锁冲突概率低,并发度相对行锁较高

InnoDB的锁类型

InnoDB的行锁主要有读锁(共享锁)、写锁(排他锁)、意向锁和MDL锁

读锁
简称S锁,一个事务获取了一个数据行的读锁,其他事务也可以获得该行的读锁,但不能获得写锁
一个事务在读取一个数据行时,其他事务也可以读,但不能对该数据进行增删改操作
1、一致性非锁定读,自动提交模式下的select查询语句,不需加任何锁,直接返回结果
2、通过select ... lock in share mode,在被读取的行记录或记录范围上加读锁,其他事务写会被阻塞

写锁
简称X锁,写锁优化级最高,一个事务获得后,其他事务不能再获得其他锁
一些DML语句的操作都会对行记录加写锁
特殊的select for update会对读取的行加写锁

DML锁
meta data lock,简称DML锁,用于保证表中元数据的信息。一个会话开启查询事务后,会自动获得一个MDL锁,其他会话就不能执行任何DDL语句

意向锁
InnoDB中,意向锁是表级锁,包括:意向共享锁和意向排他锁
作用类似DML锁,防止在事务进行过程中,执行DDL语句的操作而导致数据不一致
意向共享锁IS,给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁IX,给一个数据行加排他锁前必须先取得该表的IX锁

InnoDB行锁各类
InnoDB在默认事务隔离级别RR(可重复读repetable read),且参数innodb_locks_unsafe_for_binlog=0模式下,行锁有三种:
1)记录锁,单个行记录的锁 record lock
#主键和唯一索引都是行记录的锁模式,在PC隔离级别下,只有record lock记录锁模式
2)间隙锁 GAP Lock
3)记录锁和间隙锁的组合叫作next-key lock
#普通索引默认就是next-key lock模式
记录锁,单个行记录的锁

更新同一行数据时,会出现锁等待的现象,InnoDB的行锁是加在索引项上面的

show index from tt;

间隙锁Gap lock

在RR事务隔离级别,为避免幻读现象引入Gap lock,它只锁定行记录数据的范围,不包含记录本身,即不允许在此范围内插入任何数据

加锁方法:select * from tt where score<80 lock share mode;插入score小于80的记录将出现锁等待

间隙锁只针对RR隔离级别才管用,它就是用来避免幻读现象发生的

RC隔离级别下是允许出现幻读现象的

Next-key Locks

Next-key Lock是记录锁Record Lock与间隙锁Gap lock的组合,当InnoDB扫描索引记录时,先对选中的索引记录加入记录锁,再对索引记录两加的间隙加上间隙锁

select * from tt where score<85 for update;再另一个会话中插入score=85的记录,无法插入成功,说明不光锁定了<85的范围,也锁了85这个记录本身

锁等待和死锁

锁等待,一个事务过程中产生的锁,其他事务需要等待上一个事务释放它的锁,才能占用该资源。会一直等待,直到锁等待超时

innodb_lock_wait_timeout = 10 单位秒

死锁,两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象。报错为Deadlock found when tring to get lock;try restarting transaction

避免死锁的四个方法:
1)若不同程序会并发存取多个表,或涉及多行记录时,尽量约定以相同的顺序访问表,可以大大降低死锁的机会
2)业务中尽量采用小事务,避免使用大事务,要及时提交或回滚事务,可减少死锁产生概率
3)在同一事务中,尽可能做到一次锁定所需资源,减少死锁产生概率
4)对于非常容易产生死锁的业务部分,可尝试升级锁粒度,通过表锁定来减少死锁产生概率
锁问题的监控
show full processlist
show engine innodb staus
三张表
information_schema.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS

转载请注明:轻风博客 » MySQL王者晋级之路(三)索引、事务、锁

喜欢 (2)or分享 (0)