MYSQL优化杂谈一,基于锁的优化
2016-11-02 本文已影响153人
小直
MyISAM
MyISAM的读锁，写锁
1.myisam 表引擎，读写锁互斥，并且是串行的；
2.myisam 表引擎，在同时发生读锁和写锁的时候，写锁优先级会提高；所以myisam不适合读写量很大且同时发生的业务场景；
3.myisam 表引擎采用表锁，表锁是mysql最大颗粒度的锁定机制；
4.表所获取和释放锁速度快，但是发生锁定资源征用的概率大；
5.虽然myisam 表引擎，读写锁互斥，并且是串行的;
但是myisam 表引擎还有Consurrent Insert的特性（并发插入），并不是myisam引擎就只能做串行操作；
MyISAM优化建议
- 1.从MyISAM存储引擎锁定问题入手：缩短锁定时间；
a).尽可能减少复杂Query,将复杂Query拆成几个小Query分布进行；
b).尽可能建立高效索引，提升数据检索速度；
c).尽可能让MyISAM存储引擎表只存放必要信息，控制字段类型；
d).利用合适的机会优化MyISAM表数据文件；
- 2.分离能并行的操作；
因为myisam 表引擎还有consurrent_insert的特性（并发插入）;
所以，将consurrent_insert=1或者2的时候，myisam 表引擎支持读写并发；
例子：在myisam 表引擎上加上读锁，在做insert写操作的时候是可以执行成功的，但在做update操作的时候便会失效；
- 3.合理利用读写优先级；
mysql的表级锁是对于读和写是有不同优先级设定的，默认下是写优先于读；
但是也可以根据系统环境，系统业务场景调整优先级；
low_priority_updates=1，将写的优先级设置比读的优先级地；
Innodb
Innodb的行锁
1.行锁是通过索引实现的，如果不通过索引访问数据，Innodb使用的是表锁
2.行锁特点是锁定粒度很小，由于力度小，发生锁定资源征用的概率小；
3.虽然粒度小，但是获取锁和释放锁要做的事情也多，消耗也大；行级锁最容易发生死锁；
Innodb优化建议
- 合理利用行级锁，扬长避短
a).尽可能让所有的数据都通过索引来完成，从而避免Innodb以为无法通过索引键加锁而升级完成；
b).合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确,尽可能能缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行;
c).尽可能减少*基于范围的数据检索过滤条件*，避免以为间隙锁带来的负面影响而锁定率不该锁定的记录；
d).尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e).在业务环境允许情况下，尽量使用低级别的事物隔离，以减少Mysql因为实现事务隔离级别所带来的附加成本；
- 减少死锁的概率
a).类似业务中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b).在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概念；
c).对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
mysql DDL(alter table)
类似的操作，mysql是通过重建整个表然后再rename而实现的功能，所以整个过程原表仍然可以提供服务；
通过获取WRITE_ALLOW_READ锁，这种锁定一般发生在对表做DDL时；
mysql 数据存储相关
不适合在数据库中存储的数据：
1.二进制多媒体文件：空间资源消耗严重，并且这些数据存储很消耗数据库主机的cpu资源；
2.流水队列数据：流水队列数据会有不断的写操作，支持事物的存储引擎对应这些写操作的日志的生产量非常大；
使用第三方队列软件可以提升性能；
3.超大文本数据：
* 数据库varchar最大长度为64kb；
* 但是长度超过255bytes之后，占用空间就是实际长度的两倍；
* 另一方面可以使用TEXT(64KB)或者LONGTEXT(4GB),但是这两种类型的处理性能比varchar要低很多；
