数据库锁及MySQL优化

数据库锁

1. 行锁和表锁

1. 主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁
   • 行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
   • 表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
2. 行锁 和 表锁 的区别：
   • 表锁： 开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
   • 行锁： 开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2. 乐观锁和悲观锁

1. 悲观锁
   顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在 拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。

   传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是 在做操作之前先上锁。

2. 乐观锁
   顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会 上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等 机制。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于 write_condition 机制的其实都是提供的乐观锁。

3. MySQL优化

1. 定位执行效率慢的 sql 语句

   • 命令:show status like ‘Com____’,通过这条命令, 我们可以知道当前数据库是以查询为主 还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.
   • explain + sql语句查询sql执行过程, 通过执行计划，我们能得到哪些信息：
     • 哪些步骤花费的成本比较高
     • 哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观
     • 这条sql语句是否走索引
   • show profile分析SQL,可以查看所有sql语句的执行效率(所用时间). 前提是这个命令需要 被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并 展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化 这条 sql.
   • 慢查询日志(常用的工具)：慢查询日志记录了所有执行时间超过参数 long_query_time 的 sql 语句的日志,long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下, 有个 slow_query.log 文件

2. 优化索引

   1. 索引设计原则
      索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

      • 对查询频次较高, 且数据量比较大的表, 建立索引.
      • 索引字段的选择, 最佳候选列应当从where 子句的条件中提取, 如果where 子句中的组合 比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
      • 使用唯一索引, 区分度越高, 使用索引的效率越高.
      • 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维 护效率.
      • 使用短索引, 提高索引访问时的 I/O效率, 因此也相应提升了Mysql 查询效率.
      • 如果where 后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法 则, N个列组合而成的复合索引, 相当于创建了N个索引.
        复合索引命名规则 index_表名_列名 1_列名 2_列名 3
        比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)

   2. 避免索引失效

      • 如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开 始, 并且不能跳过索引中的列.
      • 尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进 42行全表扫描
      • 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全 表扫描。
      • 不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函 数.计算表达式等, 都会是索引失效.
      • 查询 like，如果是 ‘%aaa’ 也会造成索引失效.
      • 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有 索引，将导致引擎放弃使用索引而进行全表扫描

3. SQL语句调优

   • 根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极 大的提高查询效率.
   • 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
   • where 条件字段上需要建立索引, 但Where 条件上不要使用运算函数，以免索引失效.
   • 排序字段上, 因为排序效率低, 添加索引能提高查询效率.
   • 优化 insert 语句: 批量列插入数据要比单个列插入数据效率高.
   • 优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有 索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升 序或同时用降序.
   • 优化group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序, 但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止 排序, 使用 order by null 禁用.

     select age, count(*) from emp group by age order by null
     

   • 尽量避免子查询, 可以将子查询优化为 join 多表连接查询.