数据库调优（mysql）

查询过程

java Btree java btree 文件索引_加锁

 

B-TREE索引

      BTREE索引是Mysql中最常用的索引类型，它所支持的匹配原则如下：

      全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引（覆盖索引）；

      另外BTREE索引是按照顺序存储数据的，所以也支持匹配ORDER BY 和 GROUP BY 操作

高性能索引策略

  1. 独立的列   select * from test where id+1=8 –> select * from test where id=8+1;
  2. 前缀索引   alter table test add key(id(length))，不支持order by、group by以及覆盖索引
  3. 多列索引以及合适的索引列顺序（利用最左前缀匹配原则）
  4. 覆盖索引   select name from tb_user 其中name为索引列
  5. 使用索引来做排序和分组（order by、group by）
  6. 避免冗余索引和重复索引、删除未使用索引

Innodb之行级锁

  1. Record Lock：单个行记录上的锁
  2. Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  3. Next-key Lock:Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

      注意：

  1. 当Query 无法利用索引的时候，Innodb 会放弃使用行级别锁定而改用表级别的锁定
  2. 去除间隙锁可以通过设置参数innodb_locaks_unsafe_for_binlog为true，或者降低事务隔离级别为Read Committed
  3. 锁等待时间设置innodb_lock_wait_timeout（单位是秒）

事务隔离级别

java Btree java btree 文件索引_加锁_02

Repeatable read 级别下，Innodb引擎通过多版本并发控制（MVVC）解决了幻读的问题。

事务和锁的优化

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

死锁

      减少死锁概率建议：

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

      出现死锁怎么办？（show processlist）

      如何定位死锁？（show engine innodb status）

更多特性

  • 数据切分

        垂直切分（按业务功能模块切分）、水平切分（按业务表中字段切分，分区/分表/分库）

  • 日志设置优化

        错误日志（Error Log），二进制日志（Binlog），查询日志（Query Log），慢查询日志（Slow Query Log）等。