1.MySQL索引(二)

对于a和b,既有联合查询,又有基于a,b各自的查询,查询条件中只有b这个条件,是无法使用(a,b)这个联合索引的,这时不得不维护另外一个索引b也就是需要同时维护(a,b),b两个索引。这时要考虑的原则就是空间了,考虑a和b哪个占用的空间小,哪个单独建一个索引。

MySQL在5.6之后引入了索引下推优化,即若存在联合索引(a,b),对于select a,b,c from T where a=1 and b=1 会对满足a,b结果的值进行回表查询,而之前的版本是对满足a结果的值进行回表,在回表查询的结果中再筛选满足结果b的内容

例如: select * from tuser where name like '张 %' and age=10 and ismale=1;

图1 无索引下推执行流程

图2 索引下推执行流程

图1中,在 (name,age) 索引里面,这个过程 InnoDB 并不会去看 age 的值,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4 次。

图2跟图1的区别是,InnoDB 在 (name,age) 索引内部就判断了age是否等于 10,对于不等于10的记录,直接判断并跳过。在这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。

MySQL索引中的重建索引

原因:索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。

对于重建索引的如下语句是否合理?

重建索引 k 的做法是合理的,可以达到省空间的目的。但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,可以用这个语句代替: alter table T engine=InnoDB。

根据加锁的范围,MySQL里面的锁大致可以分为全局锁、表级锁、行锁。

全局锁:

顾名思义,全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。(MVCC:Multi-versionconcurrency control).single-transaction方法只适用于所有的表使用事务引擎的库。

set global readonly=true也可以让全库进入只读状态,但还是会建议使用 FTWRL 方式,主要有两个原因:

1.在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大,不建议你使用。

2.在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。

2.1表级锁:

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。表锁的语法是 lock tables … read/write。可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。

如何安全地给小表加字段?

首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。

如果要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而不得不加个字段,该怎么做呢?

这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MDL锁:用于解决或者保证DDL操作与DML操作之间的一致性。

MDL锁是系统默认会加的,事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

2.2行锁(行锁一):

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。

两阶段锁(行锁二):

在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

死锁和死锁检测(行锁三):

当出现死锁以后有两种策略:

直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect (默认50s)设置为 on,表示开启这个逻辑。

死锁检测要消耗大量的CPU资源,每个新来的的被堵住的线程都要判断会不会由于自己的加入导致死锁,这是一个O(n)的操作。一种常用的思路是控制并发度,并发的控制不能放在客户端,因为客户端很多,假如有 600 个客户端,这样即使每个客户端控制到只有 5 个并发线程,汇总到数据库服务端以后,峰值并发数也可能要达到 3000,因此,这个并发控制要做在数据库服务端。如果有中间件,可以考虑在中间件实现,基本思路就是,对于相同行的更新,在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

2.3事务的启动时间:

begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句,事务才真正启动。马上启动一个事务,可以使用 start transaction with consistentsnapshot 这个命令。

第一种启动方式,一致性视图是在第执行第一个快照读语句时创建的;

第二种启动方式,一致性视图是在执行 start transaction with consistentsnapshot 时创建的。