位置：首页-资讯-数据库

优化MySQL哪些地方需要注意

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

本文主要给大家简单讲讲优化MySQL哪些地方需要注意，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望优化MySQL哪些地方需要注意这篇文章可以给大家带来一些实际帮助。

1、控制适当的数据文件大小：

数据量越大，数据文件的大小也会越大，索引文件也就越大，从而影响性能，会使性能急剧下降，所以，要限制数据库单表的数据量和数据文件大小；

2、碎片空洞注意：

往往我们在删除表数据的时候，明明已经删除可是在底层只是被标记为删除，实际上并没有释放空间，所以要定期查看表的碎片，

show table status ---发现表的碎片情况（查看实际数据data_length和空洞数据data_free的比例如果，data_free非常大，则说明该表需要优化了）

优化的方法是：optimize table 表名；或者 alter table 表名 engine=innodb来重建表空间；

3、行存储格式注意：

在MySQL5.7.9之后，innodb表的行格式由之前默认的compact变成了dynamic，二者的区别可以查看官方文档，但是，compact格式会节约20%的空间，同时在存储UTF8或UTF8MB4数据的时候，compact格式在存储时会尽量的节约空间，不对其中的空格进行存储；

4、正确使用索引：

索引虽然能提升查询性能，但是会降低MySQL写数据的速度，同时，也会增大数据文件的大小，加的索引越多，写入数据越慢，数据文件也就越大。所以在设计索引的时候，会要求主键的字段类型一定要用数字类型，并且要尽可能的小，能用INT的，绝对不要用bigint。在设计二级索引的时候，只添加需要的索引，避免添加重复索引和冗余索引，针对长字符串字段，尽量添加前缀索引；

---如何正确使用索引：

①：MySQL在使用索引时，采用的是最左匹配原则，如果是多列索引：idx_a_b_c(a,b,c)，则可以发挥索引功能组合有：a, (a,b) (a,b,c)

②：MySQL在计算列里无法使用索引

③：MySQL在否定条件中不能使用索引

④：MySQL在join中连接字段类型如果不一致，则不能使用索引

*********************************************************************************

在MySQL中一些比较常用的系统参数：

1、general_log：

建议在数据库正常服务时，将该参数关闭，因为他会记录提交到MySQL的一切东西，即浪费磁盘又影响效率。同时它也是分析问题的利器，可以在数据库有异常的时候，把它打开，然后截取一段时间的日志，以帮助定位问题；

2、query_cache_size：

是用来缓存sql语句文本和对应查询结果的缓存空间。如果表没有变化，再次查询会直接返回结果，这是很高效的；如果表变化非常频繁，则要不断更新cache中的内容，并且这个时候所粒度非常大，反而会成为瓶颈。所以很多情况下会关闭这个选项，将参数设置0

3、tmp_table_size：

在group by 或 distinct 的时候，如果sql语句用不到索引，就会使用系统内部临时表记录中间状态。如果tmp_table_size不够大，则MySQL会自动使用物理磁盘，这会对查询性能造成很大影响，增加此参数可以降低这种情况发生的概率；（注意：这是占用物理内存的，要考虑实际的内存空闲情况）

4、innodb_buffer_pool_size：

innodb最只要的缓存，用来缓存innodb索引页面，undo页面及其他一些辅助数据，建议配置物理内存的50%--75%

5、innodb_buffer_pool_instances：

这个参数，把原来一整块buffer pool分割为多块内存空间，每个空间独立管理自己的空闲链表、刷新链表、LRU及其他数据结构。这大大增加了并发性，能更有效利用缓存；

6、innodb_log_file_size和innodb_log_files_in_group

这两个参数结合，决定了redo空间的大小，redo空间越大，可以存储的增量更新日志越大，有效降低buffer pool脏页面被淘汰的速度，同时减少了checkpoint的次数，降低磁盘IO置换率，从而提升数据库的写入效率。不过也有可能导致数据库异常退出时，恢复时间被拉长；

7、innodb_old_blocks_pct和innodb_old_blocks_time

这两个参数控制buffer pool中缓存数据的过期和移动行为，二者结合设置，可以优化一些全表扫描带来的大规模更新buffer等问题；

8、innodb_numa_interleave：

该参数避免出现内存被交换到SWAP分区，影响性能

9、innodb_autoinc_lock_mode：

在innodb有自增列的情况下，在插入数据的时候，会自动产生自增值，这个参数是控制自增值生成的方式。目前有3个选项：0 1 2 ；使用2即in特人leave的，这样在insert数据的时候不会用到标记的auto-inc锁，避免了auto-inc的死锁问题，在insert...select场景下会极大提升性能，在做普通insert的时候，也会提升并发执行的效率；

---注意：这个时候产生的自增值不是连续的，同时binlog格式需要设置为ROW，才能保证数据的安全性和一致性；

10、innodb_flush_method

innodb刷新数据和日志到磁盘文件的方式，默认为NULL，但其实如果是Unix系统上，默认是fsync；在使用SSD或PCIE类型的存储时，可以设置为O_DIRECT, 该参数会提升性能；

11、innodb_doublewrite

如果底层存储时支持原子写的，则可以关闭两次写，以提升效率；

12、innodb_io_capacity

如果使用SSD设备的时候，可以适当提高这个参数值，以加速flush的频率；

13、innodb_thread_concurrency

在并发量大的实列上，增加这个值，可以降低innodb在并发线程之间切换的花销，以增加系统的并发吞吐量；

14、innodb_flush_log_at_trx_commit

该参数的值0 1 2；0性能最好，但不安全； 1 最安全，但性能最差，如果使用的磁盘足够好，还是建议使用1 ，否则根据实际情况选择性的设置2

15、sync_binlog