mysql数据库优化的一些记录

最新推荐文章于 2022-02-25 14:33:18 发布

小瘦猪

最新推荐文章于 2022-02-25 14:33:18 发布

阅读量252

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/lyp80/article/details/78327008

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、建表相关

1.永远为每张表设置一个ID

库表设计时应该为每张表都设置一个ID作为其主键，最好是int型(推荐使用unsigned)，并设置上自动增加的auto_increment，使用varchar类型来当主键会使得性能下降。

有留意过大型电商网站的表字段，用户编号，订单编号等都是int型，且订单编号有一定的规则，具体的没研究过，调试一号店接口的时候发现订单编号的后3位和用户编号的后3位是一样的。

二、配置文件相关

1.tmp_table_size 默认2097152 =2M

# MySQL的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。
# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
# 如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果

2.query_cache_size

（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。最佳选项是将其从一开始就停用，设置query_cache_size = 0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果你已经为你的应用启用了query cache并且还没有发现任何问题，query cache可能对你有用。这是如果你想停用它，那就得小心了。

一般高并发，写入量大的系统，建议禁掉

3.myisam_sort_buffer_size

myisam表发生变化时重新排序所需的缓冲

4.innodb_thread_concurrency

设置为cpu核数的两倍，比如4个cpu，每个cpu为8核，则设置为4*8*2=64

表扫描情况

mysql> show global status like 'handler_read%';
+-----------------------+-------------+
| Variable_name       | Value    |
+-----------------------+-------------+
| Handler_read_first | 5803750     |
| Handler_read_key    | 6049319850   |
| Handler_read_next     | 94440908210 |
| Handler_read_prev     | 34822001724 |
| Handler_read_rnd    | 405482605 |
| Handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+

调出服务器完成的查询请求次数：

mysql> show global status like 'com_select';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_select | 222693559 |
+---------------+-----------+

计算表扫描率：

表扫描率＝ Handler_read_rnd_next / Com_select

如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB 。

table_open_cache

table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。
如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了(上述状态值可通过SHOW GLOBAL STATUS LIKE ‘Open%tables’获得)。

发现open_tables等于table_open_cache，都是512，说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_open_cache的值，4G内存的机器，建议设置为2048
比较适合的值：
Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95
如果对此参数的把握不是很准，有个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。