mysql锁机制和索引_MySql锁机制及索引 · 十年饮冰，难凉热血

一：ACID

原子性：对数据库的修改，要么都执行，要么都不执行

一致性：事务开始和结束时，都必须保持一致状态，事务结束时，索引等也必须完全正确

隔离性：事务处理过程中的中间状态对外部都是不可见的，反之亦然

持久性：事务完成后，对数据的修改是永久的

二：问题

更新丢失：两个人同时修改一份文件，前一个人的修改会被覆盖

脏读：事务A读到事务B已经修改但尚未提交的数据，B回滚之后，出现问题

不可重复读：事务A在读取数据的某个时间，再次读取数据，发现被修改、或者删除的数据。

幻读：事务A读到了事务B已经提交的新增的数据。

三：对应关系

隔离级别 脏读 不可重复读 幻读

未提交读 是 是 是

已提交读 否 是 是

可重复读 否 否 是

可序列化 否 否 否

四：需要建立索引的情况主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引

查询与其他表关联的字段，外键关系，建立索引

查询中排序的字段(排序字段若通过索引讲大大提高排序速度)

查询中经过统计，分组的字段

五：不能建立索引的字段频繁更新的字段

where条件中用不到的字段

表记录太少

经常需要删除的表

六：Explain sql语句后的字段

1.id

表的加载顺序

2.select type

查询类型

3.type(一般至少达到range，最好达到ref)

访问类型，是重要指标，从好到坏依次是

system > const > eqref > ref > range > index > All

system：

表中只有一条记录 ( 系统表 )

const

通过索引一次就找到了(主键索引)

eqref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配(主键 / 唯一索引)

ref

非唯一性索引扫描，返回匹配某个单独值的索引行 ( 本质上也是一个索引访问，返回所有匹配某个单独的行，然而， 可能找到多个复合条件的行，属于查找和扫描的混合体 )

range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在where语句中出现了between < > in 等的查询。

index

index和all的区别是index类型只遍历索引树，通常比all快，因为index是从索引中读取的。

all

全表扫描

4.possible_keys

可能应用的索引，一个或多个，不一定被查询实际使用

5.key

实际使用的索引

6.key_len

表中索引中使用的字节数

7.ref

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

8.extra

use filesort

说明MySql 对数据进行了外部文件排序，这是非常不好的，

using tempory

使用了临时表存放中间结果，常见于排序，order by 和group by

using index

表示相应select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，是我们要求的

using where

where过滤

using joinbuffer

使用了连接缓存

七：索引失效的原因

全职匹配我最爱，

最佳左前缀，如果索引了多列， 要遵守最佳左前缀原则，即一定要从最左列开始，并且不能跳过索引的中间列

不能在索引上做任何操作

计算，函数，(手动 / 自动)类型转换，会导致索引失效，转向全表扫描

范围之后全失效

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引

只访问索引的查询，(索引列和查询列一致)

使用 != 或者 <> 会使索引失效

!= <>

is null 或者 is not null 也会导致全表扫描

is null 或者 is not null

like以通配符开头( ‘%abc’ ) 索引失效，变为全表扫描

like

字符中不加单引号索引失效

单引号

少用or，用or连接会导致索引失效

or

八：mysql锁

分类

按照对数据操作的类型(读/写)区分： 读锁 / 写锁

按对数据操作的力度分为：行锁 / 表锁

表锁(偏读)

偏向MyIsam 存储引擎

开销小，加锁快，无死锁

发生锁冲突概率较高

并发度最低

MyIsam 在执行select前，会自动给涉及的所有表加读锁，在执行增删改之前，自动给涉及的所有表加写锁。

读锁会阻塞写，但是不会阻塞读，而写锁读写都阻塞

行锁(偏写)

偏向InnoDB 存储引擎，开销大，加锁慢，

锁的粒度最小

发生锁冲突概率最低，并发度也最高

支持事务