假装会优化之数据库优化

数据库优化

优化步骤:

• 1:explain关键字,查询sql执行效率
• 2:system > const > eq_ref > ref >range>index>all
  • system和const是只有一条数据符合数据的情况
  • eq_ref是使用唯一性索引
  • ref是非唯一性索引检索
  • range是范围索引检索
  • index是全局索引检索
• 3:show profile显示sql执行性能(5.7版本之后才支持)
• 4:开启慢查询记录
  • Show variables like ‘slow_query%’;
  • Show variables like ‘long_query_time’;
  • set global slow_query_log=‘ON’; // 开启慢 SQL 日志
  • set global slow_query_log_file=’/var/lib/mysql/test-slow.log’;// 记录日志地址
  • set global long_query_time=1;// 最大执行时间

数据库对事务的支持

• 数据库事务的不同会造成:数据丢失.脏读,幻读,不可重复读(释义)
• 四个隔离级别:读未提交,读已提交,可重复读,序列化(释义)
• 锁分为表锁和行锁,行锁的支持是基于索引来实现的,如果是没有索引的全局检索是会加上表锁的,
• 行锁的实现算法为record lock(专门对索引项加锁),gap lock(对索引之间的数据加锁),next-key lock(结合前两种的锁)
• 在可重复读隔离和读已提交级别中,事务是基于mvcc来实现高性能事务,就是每个事务开始时,取的是当场数据库的undo(快照),每个快照可以看成是一个版本,这样避免对数据重复加锁,提高读的性能

事务的优化

• `1:根据业务场景使用低级别的事务隔离,比如一些不注重数据真实性的场景中,就可以使用低级别的事务,比如修改最后登录时间时,就可以使用低级别的事务,在查询最后登录时间时,对这个登录时间没有严格要求
• 2:避免行锁升级表锁,如果不通过索引条件检索数据,行锁会升级到表锁,
• 3:控制事务的大小,减少锁定的资源量 和锁定时间长度
  尽量吧写操作写在事务的后半部分,减少行锁锁定的时间

不同引擎支持的存储结构

索引优化

• 1:覆盖索引优化查询
  减少一次回表操作,如果查询的数据都是查询条件,则把查询条件整合成一个联合索引,就可以直接把回表这个操作省略掉

• 2:自增字段做主键(innoDB)
  b+树里面是按照主键的顺序来存放的,如果不是自增主键,则是容易造成内存碎片,而且经常会造成数据复制(树当前页扩张时,就需要做这个操作)

• 3:前缀索引优化
  把某个长字段的前缀(有一定规律)建立索引,减少索引的存储空间,减少遍历索引的页数,但是需要根据业务场景来做

• 4:防止索引失效
  如果hash索引实现的列,在范围查询中,优化器不会使用到索引
  以%开头的like查询无法使用

  • 最左匹配原则,查询条件的顺序有可能无法使用复合索引
    在or的前后并不是所有的列都有索引,这样涉及到的就没有使用
    对索引进行函数操作或者表达式计算也会索引失效

• force index(索引名称)强制使用索引

死锁

• Innodb提供了wait-for graph算法来主动进行死锁检测，我们可以通过
  innodb_deadlock_detect = on 打开死锁检测。
• 如何避免死锁
  • 在编程中更新数据库的操作顺序保持一致
  • 在运行幻读和不可重复读的情况下,使用RC事务隔离级别,避免gap lock带来的死锁问题
  • 更新表时使用主键更新
  • 避免长事务,把长事务拆成多个短事务
  • 设置等待超时时间,通过innodb_lock_wait_timeout来设置

分库分表

• 分区->nosql存储->mysql读写分离->单库单表->单库多表->多库多表
• 分表分库的问题:
  • 1:分布式事务问题
    • 1.1:两段式提交(2pc),补偿事务提交(tcc),jta,fescar
  • 2:跨节点join查询问题
    • 建立冗余字段来避免
  • 3:跨节点分页查询问题
    • 使用es,solr来代替分页查询
  • 4 :全局主键id问题:分库分表之后无法使用自增主键,
    • 1:使用分布式锁redis来做id
    • 2:使用uuid实现
    • 3:twitter分布式id生产算法
  • 5:扩容问题
    • 动态扩容表,设计表是按照2倍来扩容,减少数据的迁移量

  总结

  - 在字段复杂,多变不方便统一的情况下,建议使用键值对代替关系型数据库
  - 高并发的情况,使用缓存代替数据库的作用
  - 数据量叠加较快的情况,建议水平分表,避免单表查询的瓶颈
  - 避免一些复杂的join操作,通过字段冗余,减少join操作,创建中间表,也可以减少join操作