/ db

mysql-key

参考链接:https://github.com/todayqq/PHPerInterviewGuide/blob/master/mysql.md
『浅入深出』MySQL 中事务的实现:https://draveness.me/mysql-transaction#

mysql 关键在于

  • 事务
  • 联合索引
  • 高并发访问的锁
  • 分库分表

事务 ACID

事务其实就是并发控制的基本单位;相信我们都知道,事务是一个序列操作,其中的操作要么都执行,要么都不执行,它是一个不可分割的工作单位;数据库事务的 ACID 四大特性是事务的基础,了解了 ACID 是如何实现的,我们也就清除了事务的实现,接下来我们将依次介绍数据库是如何实现这四个特性的。

  • 原子性():回滚日志(undo log)。
  • 持久性():与原子性一样,事务的持久性也是通过日志来实现的,MySQL 使用重做日志(redo log)实现事务的持久性。
  • 隔离性():
  • 一致性():

锁是一种最为常见的并发控制机制,在一个事务中,我们并不会将整个数据库都加锁,而是只会锁住那些需要访问的数据项, MySQL 和常见数据库中的锁都分为两种,共享锁(Shared)和互斥锁(Exclusive),前者也叫读锁,后者叫写锁。

读锁保证了读操作可以并发执行,相互不会影响,而写锁保证了在更新数据库数据时不会有其他的事务访问或者更改同一条记录造成不可预知的问题。

联合索引

索引的先后顺序。

死锁

死锁:死锁一般是事务相互等待对方资源,最后形成环路,而无法继续运行。

例如,如果线程A锁住了记录1并等待记录2,而线程B锁住了记录2并等待记录1,这样两个线程就发生了死锁现象。

如何删除1亿数据表中的2千万行?

直接 DELETE FROM table WHERE need_delete = 1 会把表锁死,让其他客户端无法执行 SELECT 查询。

删除大量数据时锁死

在项目中做sql 的优化,有一张表,里面有几千万条记录,查询速度很慢,就想到了分表。

分表后要删除原表中多余的数据,就粗暴的用了 delete from table_name where ldate<'2014-01-01'

执行了2个小时也没有结束,就cancel了这个request, 然后就发现再次查询表就被卡死,一直没有response。又重启了机器,还是不行。

使用下面的语句查看被锁的表:
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'

再用 sp_who 查看是什么操作锁的表,最后悲剧的发现是 “DB STARTUP” 锁死。

从这篇文档中找到: http://stackoverflow.com/questions/3385314/master-database-db-startup-problem

原来我取消 delete, 数据库要做rollback 操作,所以锁表在rollback. 而且只能一直等待它结束。

最后再回到原来的目的,删除大量数据,不能直接用delete, 可以考虑 把数据 selete * into 复制到新的表,再truncate 原表,然后再改回来表名。(注意:select * into 不会复制表的索引)。truncate 不记录日志,不产生rollback

PS: 做任何操作都不能直接在生产环境做,一定要在测试环境上测试之后才能执行。

PS:等了一个半小时,终于解锁了。还不死心,再次用delete from 删除150W记录,执行了28分钟。

总结,删除 百万以上的记录就要小心了,会耗时很久。

分库分表

  • 垂直分表,减少单表数据列,将使用频率低的移动到新建的扩展表。
  • 垂直分库,减少单库数据表,根据业务区分,将数据表移动到相应的数据库。
  • 水平分表,典型的就是对自增 id 取模,分流到不同的表(同一数据库),减轻单表数据行总数,提升查询性能。
    • 根据 id 范围切分。
    • 根据 取模 切分。
  • 水平分库,将表存放到不同是数据库。

写 SQL

1.地铁站点信息表:判断从站点A 到站点B知否直达?
2.员工工资表:一条SQL找到薪水比下属低的主管。