前言:
Innodb引擎
优点:
- 支持事务、支持崩溃修复。
- 会在内存中建立缓冲池,用于缓冲数据和索引。
缺点:
- 读写效率与MyIASM比相对较差,占用的磁盘空间比较大(MVVC的隐藏列、索引占用了一定的空间)。
- 没有存储表的行数,SELECT COUNT(*) FROM TABLE 时需要扫描全表。
应用:
- 需要使用事务的表。
- 高并发,更新操作比较多的表.
- 对自动灾难恢复有要求的表。
死锁
死锁产生的四个必要条件:
- 互斥条件 资源只能由一个线程使用
- 请求保持 保持已锁定的资源不释放
- 不可剥夺 已持有的资源不会被其它线程剥夺
- 环路条件 循环等待
避免死锁:
- 导致死锁的常见场景:两个Connection中加锁的顺序不一致。
- 不同的方法并发存取多个表时,尽量以相同的顺序访问这些表。
- 在一个事务中,尽量一次性锁定所需的所有资源,即一次性锁定多行。
mysql死锁排查:
查看正在执行的线程(只能看到登录用户自己的线程),运行时间最大的的线程最有可能是导致死锁的线程。
- SQL:show processlist
- Id 线程ID(连接ID)
- User 登录的用户
- Host 这个连接是从哪个ip+端口发出的
- db 数据库名
- Command 当前连接正在执行的命令,常见取值:Sleep(休眠)、query(查询)、connect(连接)
- Time 连接持续时间,单位:秒
- State sql语句当前的状态,
- Info sql语句,注:show full processlist可查看完整的sql语句。
- 杀掉线程SQL:kill MySQL线程Id
SQL分析Tips:
- 在select后面添加sleep(n)后,该sql最少会执行n秒;若查询结果为m行,则该sql最少会执行m*n秒。 eg:select sleep(5),t.name from t_user t where t.age=1
查看information_schema数据库中关于Innodb事务和锁的三张表:
- 查看在InnoDB引擎中正在执行的事务,包括:正在执行的事务 和 因申请加锁而等待的事务。
- SQL:select * from information_schema.INNODB_TRX
- trx_id 事务的ID
- trx_state 事务的状态: RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
- trx_started 事务的开始时间
- trx_requested_lock_id 事务等待的锁的ID(如果事务状态不是LOCK WAIT,这个字段是NULL),详细的锁的信息可以连查INNODB_LOCKS表
- trx_wait_started 事务等待开始的时间(如果事务状态不是LOCK WAIT,这个字段是NULL)
- trx_weight 事务的权重,反映了一个事务修改和锁住的行数。当发生死锁回滚的时候,优先选择该值最小的进行回滚。
- trx_mysql_thread_id MySQL中的线程ID,show processlist结果中的Id列。
- trx_query 事务中正在运行的sql语句
- trx_operation_state 事务当操作的类型
- trx_tables_in_use 查询用到的表的数量
- trx_tables_locked 查询加行锁的表的数量
- 查看在InnoDB引擎中存在的锁,包括:事务正在申请的锁 和 事务已经持有的锁。
- SQL:select * from information_schema.INNODB_LOCKS
- lock_id 锁ID
- lock_trx_id 事务ID,可以连INNODB_TRX表查事务详情
- lock_mode 锁的模式: S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC
- lock_type 锁的类型:行级锁 或 表级锁
- lock_table 加锁的表
- lock_index 如果是lock_type='RECORD' 行级锁,为锁住的索引,如果是表锁为null
- lock_space 如果是lock_type='RECORD' 行级锁,为锁住对象的Tablespace ID,如果是表锁为null
- lock_page 如果是lock_type='RECORD' 行级锁,为锁住页号,如果是表锁为null
- lock_rec 如果是lock_type='RECORD' 行级锁,为锁住行号,如果是表锁为null
- lock_data 事务锁住的主键值,若是表锁,则该值为null
- 查看在InnoDB引擎中锁等待的相关信息:
- SQL:select * from information_schema.INNODB_LOCK_WAITS
- requesting_trx_id 申请锁的事务ID
- requesting_lock_id 申请的锁的ID
- blocking_trx_id 阻塞的事务ID
- blocking_lock_id 阻塞的锁的ID
- SQL:show open tables where in_use > 0
- Database 数据库
- Table 表名
- In_use 正在访问该表的线程数
- Name_locked 表名是否被锁(Drop或Rename这张表时,表名会被锁住)
MyIASM引擎:
优点:
- MyIASM表保存成文件形式,跨平台使用比较方便。
- 存储了表的行数,SELECT COUNT(*) FROM TABLE 时可以直接读取已存储的值而不需要进行全表扫描。
缺点:
- 锁的粒度是表级别的,不支持行级锁,故写操作(insert、update)需要锁定整个表。
- 不支持事务
- 不支持外键
应用:
- 表的读操作远远多于写操作并且不需要数据库事务的支持。
Memory引擎:
- 优点:访问速度快,索引类型支持哈希索引(默认)、BTree索引。
- 缺点:数据存放在内存上,一旦服务器关机,数据将不再存在。
- 应用:要求快速访问的临时数据,并且允许数据的丢失。
CSV引擎:
- 特点:以csv文件的形式来存储数据。
查看存储引擎的信息:
# 查看数据库的版本,eg:5.6.33-log
select version();
# 查看MySQL所支持的存储引擎
show engines;
# 查看MySQL默认的存储引擎
show variables like "%storage_engine%";
# 查看数据库的data目录
show variables like "datadir";
# 查看数据库当前连接进程的信息
show processlist;
# 查看数据库事务的隔离级别,MySQL默认为:REPEATABLE-READ
select @@tx_isolation
# 查看自动提交是否开启(1表示开启,0表示关闭,默认开启)
select @@autocommit