MySQL-零碎知识点

建索引几个原则

  1. 最左前缀匹配原则, 非常重要的原则, mysql会一直向右匹配直到遇到范围查询(>, <, between, like)就停止匹配.

    比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引, d是用不到索引的; 如果建立(a,b,d,c)的索引则都可以用到, a,b,d的顺序可以任意调整.

  2. =和in可以乱序.

    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序, mysql的查询优化器会帮你优化成索引可以识别的形式.

  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*), 表示字段不重复的比例, 比例越大我们扫描的记录数越少, 唯一键的区分度是1, 而一些状态, 性别字段可能在大数据面前区分度就是0.

    一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录.

  4. 索引列不能参与计算, 保持列”干净”.

    比如from_unixtime(create_time) = ‘2014-05-29’就不能使用到索引, 原因很简单, b+树中存的都是数据表中的字段值, 但进行检索时, 需要把所有元素都应用函数才能比较, 显然成本太大. 所以语句应该写成create_time = unix_timestamp(‘2014-05-29’).

  5. 尽量的扩展索引, 不要新建索引.

    比如表中已经有a的索引, 现在要加(a,b)的索引, 那么只需要修改原来的索引即可.

使用索引几个原则

  1. 部分or逻辑可以使用union改造.
  2. 慢sql使用desc 或者 explain来分析执行计划.
  3. varchar等值比较写单引号.

InnoDB锁

InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁

select 加锁语法

共享锁:SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁:SELECT * FROM table_name WHERE ... FOR UPDATE

行锁与表锁

  1. 在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
  2. 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
  3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
  4. 即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;

对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

例:
假如emp表中只有101条记录,其empid的值分别是 1,2,…,100,101,下面的SQL:

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

InnoDB使用间隙锁的目的:

  1. 防止幻读,以满足相关隔离级别的要求。对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;
  2. 为了满足其恢复和复制的需要。

很显然,在使用范围条件检索并锁定记录时,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

除了间隙锁给InnoDB带来性能的负面影响之外,通过索引实现锁定的方式还存在其他几个较大的性能隐患:

  1. 当Query无法利用索引的时候,InnoDB会放弃使用行级别锁定而改用表级别的锁定,造成并发性能的降低;
  2. 当Query使用的索引并不包含所有过滤条件的时候,数据检索使用到的索引键锁住的数据可能有部分并不属于该Query的结果集的行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键;
  3. 当Query在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同的时候(索引只是过滤条件的一部分),一样会被锁定。

因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件即避免使用间隙锁.

还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。

死锁

MyISAM由于只有表锁,故是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。

但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,当两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。

在InnoDB的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后,InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的(插入、更新或者删除的数据量较小)事务来回滚,而让另外一个较大的事务成功完成。

但是有一点需要注意的就是,当产生死锁的场景中涉及到不止InnoDB存储引擎的时候,InnoDB是没办法检测到该死锁的,这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

几种避免死锁的常用方法

  1. 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。
  2. 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。
  3. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。
  4. REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题。
    (5)当隔离级别为READ COMMITTED时,如果两个线程都先执行SELECT…FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁。这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁。

什么时候使用表锁

对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表级锁:

  1. 事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
  2. 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
    当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。

在InnoDB下,使用表锁要注意以下两点:

  1. 使用LOCK TABLES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层──MySQL Server负责的,仅当autocommit=0、InnoDB_table_locks=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁,否则,InnoDB将无法自动检测并处理这种死锁。
  2. 在用 LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁。正确的方式见如下语句:

    1
    2
    3
    4
    5
    SET AUTOCOMMIT=0;
    LOCK TABLES t1 WRITE, t2 READ, ...;
    [do something with tables t1 and t2 here];
    COMMIT;
    UNLOCK TABLES;

锁相关的优化

尽量避免锁的做法:

  1. 尽可能让所有的数据检索都通过索引来完成,从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定;
  2. 合理设计索引,让InnoDB在索引键上面加锁的时候尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他Query的执行;
  3. 尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录;
  4. 尽量控制事务的大小,减少锁定的资源量和锁定时间长度;
  5. 在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL因为实现事务隔离级别所带来的附加成本。

锁发生后的优化:

  1. 类似业务模块中,尽可能按照相同的访问顺序来访问,防止产生死锁;
  2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
  3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。