mysql优化总结

最近这两周⼀直在做数据存储查询优化，恰逢⽉初听了⼀次数据库技术⼤会，今天便想着对 mysql的优化做⼀下总结。这篇总结主要着眼于mysql的配置，表结构设计以及sql语句的优化。

⼀、mysql配置优化

• max_connections：最⼤客户端连接数，如果服务器的并发连接请求量⽐较⼤，建议调 ⾼此值，以增加并⾏连接数量，当然这建⽴在机器能⽀撑的情况下，因为如果连接数越多，鉴于MySql会为每个连接提供连接缓冲区，这就会开销越多的内存，所以还是需要根据系统各服务实际情况设置。
• key_buffer_size：索引缓存⼤⼩（将从磁盘上读取的索引缓存到内存，可以设置⼤⼀ 些，有利于快速检索）。
• innodb_buffer_pool_size： Innodb 存储引擎缓存池⼤⼩。对于 Innodb 来说最重要的 ⼀个配置，如果所有的表⽤的都是 Innodb ，那么甚⾄建议将该值设置到物理内存的 80%， Innodb 的很多性能提升如索引都是依靠这个。
• max_allowed_packet：mysql接收的最⼤数据包⼤⼩，这个主要在批量插⼊数据场景下可以设置⼤些。
• innodb_log_buffer_size：表⽰InnoDB写⼊到磁盘上的⽇志⽂件时使⽤的缓冲区的字节数，默认值为8M。⼀个⼤的⽇志缓冲区允许⼤量的事务在提交之前不写⽇志到磁盘。因此，如果你有很多事务的更新，插⼊或删除操作，通过这个参数会⼤量的节省了磁盘 IO。
• innodb_log_file_size：该参数决定着mysql事务⽇志⽂件（ib_logfile0）的⼤⼩，对于 具有⼤量数据插⼊操作的应⽤来说，这个参数需要设置⼤⼀些，这样可以提⾼redo log 的IO性能，但是设置的过⼤会导致mysql故障恢复时间变⻓。

⼆、表结构优化

• 尽可能⽤⼩的数据类型并指定较⼩的⻓度：能⽤整型就不要⽤字符串，⽤字符串尽量⽤ varchar并指定较⼩⻓度；
• 字段尽量⽤not null：⾮ null 字段的处理要⽐ null 字段的处理⾼效些！且在sql语句中不 需要判断是否为 null；
• 字段⼀定要加注释：之前项⽬中就是因为字段没有注释，踩了太多坑；
• 设计表的时候，如果有⼀张表将来肯定会存储⼤量数据（这个⼀般是可以预判到的）建 议建表时预留字段，避免将来数据量⼤新增字段过于费时；
• 遵守第⼀〜第三范式； 对于数据量⼤的表，可提前进⾏分表；
• 建⽴索引：建⽴索引需要注意⼀定要根据实际查询⽅式来确定索引。这⾥记录⼀下我们项⽬上踩的坑：我们之前建⽴索引时对⼀张表中查询⽤到的三个列都建⽴单列索引，当查询同时涉及这三个列时，其实mysql只会⽤到其中⼀个它认为效率最优的那⼀列，这时候在存储、查询效率⽅⾯建三个单列索引明显不如建⽴⼀个组合索引划算。

三、sql语句优化

1）查询语句

• ⾸先查询缓存⼀定要开启哈query_cache_type设为1。
• 不要select *，需要什么字段的数据就查什么字段的数据；
• 如果表数据量⼤，不要⼀次性将所有数据查询出来。这⾥需要注意，如果⼀张表数据量 千万级别，会发现查询前⼏万条数据效率还⾏，但是越到后边使⽤limit查询效率直线下滑，这个时候可以考虑使⽤其主键id作为查询游标，这样效率会⼤幅上升；
• 慎⽤in查询，in查询不使⽤索引有时候会导致全表扫描，如果查询参数是连续数值，那就⽤between；
• 连表查询时确保⼩表驱动⼤表，如果你不确定谁驱动谁，那就不要指定left join或者right join，直接⽤join然后让mysql⾃⼰判断；

2）数据插⼊

• ⼤量数据插⼊时，⼀定要⽤insert values这种形式。这⾥需要注意在spring项⽬中批量 插⼊⼤量数据⼀定要⽤jdbc执⾏原⽣sql，不要⽤mybatis，mybatis由于要做字段映射， ⼤量使⽤java反射机制，会导致性能损耗；
• ⼤量数据插⼊时，最好不要并发执⾏，这会导致query排队，最好设计⼀个专门⽤于数据存储的服务，所有数据插⼊都交由它执⾏；

以上就是⼀些常⻅的mysql优化了，当然并不全，后续还会进⾏补充。