MySQL性能优化

架构设计层面

• 搭建mysql集群或者使用读写分离, 分库分表

程序配置方面

• MySQL 5.7版本默认的最大连接数是151个，这个值可以再my.cnf中去修改
• binlog日志默认不开启，可以开启
• max_allowed_packet: 服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小, 这个参数控制通信的 packet 大小, 默认是4M, 可以根据适当情况增加或者减少
• innodb_buffer_pool_size:
  • 用于缓存数据大小的参数, 当然越大越好, 但是占用的是宝贵的内存, 所以设置多少要根据服务器情况来看, 默认是128M, 如果是独立服务器,可以设置为服务器的80%左右, 共享就视情况而定
  • 与之有关联的还有innodb_change_buffer_max_size (写缓冲大小)参数 和innodb_change_buffering参数
    • innodb_change_buffer_max_size: 默认占整个缓冲池25%
    • innodb_change_buffering: 配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等
    • 写缓冲开启后, 新添加的数据数据在写缓冲区内, 在达到触发刷新磁盘时才会进行数据落盘. 如果添加后立马查询, 则会触发一次磁盘IO, 所以对于添加数据后立马就要查询不一定适用, 针对具体情况具体设置.

sql语句方面

• 设计表时字段占据最小内存原则, 字段类型占的字节
• 设计表时字段尽量不给 NOT NULL
• 可以是join代替子查询,或者使用exist函数
• 增加索引
  • 唯一索引
  • 主键
  • 普通索引
• 避免在索引字段进行数学运算和函数,会导致索引失效
• 可以使用in代替or, 使用or也可能导致索引失效
• like函数如果双百分号无法使用到索引, 右边加上就可以使用到索引
• where条件后面比较字段时字段类型保存与值一样, 否则索引可能不会生效
• 组合索引最左前缀: 如果组合索引为：(name,age), name and age -- 使用索引, name -- 使用索引, age -- 不使用索引
• 分组统计可以禁止排序, SELECT id,name FROM tab GROUP BY id ORDER BY NULL;
• 插入数据时数据多时使用批量插入数据, 要注意批量插入数据的字节不要超过服务器端和客户端在一次传送数据包的大小.