mysql的存储引擎

前言：

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