MySQL优化

一. 为什么MySQL需要优化??

• 首先其数据存储在磁盘上,读写效率低
• 其次随着数据增多,处理压力也随之增大
• 再者数据的处理速度往往就是应用的吞吐量的瓶颈

二. 如何优化MySQL??

• 项目开发前,对数据库的设计,优先考量最优存储和计算
  • 　精度要求,使用decimal     (精确到设置的大小范围)
  • 　字段尽可能使用not null    (null数值,计算逻辑复杂)
  •     不要有过多的字段            (可以有预留字段以备扩展)
  •     符合三范式
    • 　字段原子性,默认             (一个字段只存储一项信息)
    •    消除主键部分依赖           (不同的表不要合成一张)
    •    消除对主键的传递依赖    (主键不直接和外键字段关联,因另起字段进行关联)
• 表的自身优化,即索引
  • 　常用普通索引,提高访问速度
  •     索引建议最多不超过6个                       (只对频繁使用的字段设置索引)
  •     避免在条件中对字段进行null值判断     (否则导致引擎放弃索引,直接全表扫描)  
• 主从复制,读写分离
  •  　主服务器处理数据,生成binlog日志,使用日志,实现从服务器的数据同步
  •     使用MyCat读写分离时,写操作只对主服务器,读操作只对从服务器
• SQL语句优化
  • 　 对于查询,建议在条件的字段上建立索引
  •     避免在条件中对字段 或 != 或 < > 或 or 进行null判断或则表达式操作   (否则导致引擎放弃索引,直接全表扫描)
  •     in 和 not in 慎用                                                                                     (否则导致引擎放弃索引,直接全表扫描)
  •     尽可能使用数值型字段,减少字符型字段                                                (提高查询和连接的性能)
  •     索引建议最多不超过6个                                                                        (索引多,查询而言,优;插入和修改而言,差)
• 大表拆分
  •     对庞大数据进行分表分库　　　　
    • 　水平拆分 : 1000万的大表,分成四张结构相同,平均250万一张的小表
    •    垂直拆分 :  订单表存储在订单库中,用户表存储在用户库中

三. @Transactional 哪些情况下无效??

• 　　跨资源访问             (多connction,不是同一个)
•        spring的事务传播策略 , 在内部方法调用不起作用     (事务注解加到要调用方法上面)
•        数据库引擎不是InnoDB
•        注解在protected或private上
•        业务层异常没向上抛出,使用了try...catch吃掉了异常,就没办法知道异常去进行回滚操作
•        遇到非检查异常,如throwable
•        不要写在接口上        (spring采用AOP针对具体实现类做的代理实现)

四. 本地事务

•        必须是同一个连接对象
•        ACID 事务的四大特征
  • 　　A 原子性      如何保证??
  • 　　依靠undo_log表:记录执行的sql语句.如果执行commit 就删除当前记录;如果执行rollback就反执行当前sql.所以必须保证是同一个connction
  •        C 一致性      如何保证??
    • 　　事务的原子性和隔离性保证了数据的一致性
  •        I 隔离性       如何保证??
    • 　　设置默认隔离级别REPEATABLE READ(可重复读) + Next-Key Locking，保证了数据库的隔离性
  • 　　D 持久性     如何保证??
    • 　　InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试通过redo log进行恢复操作。