mysql优化参数(如何设置合理的mysql的参数)
本文目录
如何设置合理的mysql的参数
为了设置合理的MySQL参数,需要根据实际情况和使用场景来进行调整。通常而言,你应该关注以下几个方面:
连接数量: 在MySQL中有一个max_connections参数,用于限制MySQL服务器能够同时支持的客户端连接数。
数据库缓存: MySQL有一个query_cache参数,用于设置查询结果的缓存大小,以便加快对相同SQL语句的多次执行。
数据库日志: MySQL有一个log_error参数,用于设置日志文件的位置和大小,以便更好地监控MySQL的运行情况。
NineData是一款非常有特色的数据库SQL开发产品,对MySQL常用功能支持非常完整,包括智能的SQL补全、SQL执行历史、结果集、数据对比、结构对比、数据迁移与复制等。它采用SaaS架构模式,用户不仅可以免费使用,而且无需下载安装,上手比较简单。NineData产品更新迭代比较敏捷,对于开发者的新需求响应比较迅速。另外,该产品在多云适配上是其重要的强项,支持多种连接和访问云数据库的方式,对阿里云、腾讯云、华为云、AWS等都有比较好的支持。另外,也适配国内比较流行的PolarDB、GaussDB、TDSQL等数据库。
对于新用户NineData还会赠送两个示例数据库,供用户使用。另外,NineData还提供了企业级SQL开发能力,支持多用户管理、数据库访问权限控制、变更流程、SQL规范、SQL与操作审计等内容,可以较好的解决企业内多人协作访问数据库的问题。
mysql性能优化,欢迎高手
在开始演示之前,我们先介绍下两个概念。
概念一,数据的可选择性基数,也就是常说的cardinality值。
查询优化器在生成各种执行计划之前,得先从统计信息中取得相关数据,这样才能估算每步操作所涉及到的记录数,而这个相关数据就是cardinality。简单来说,就是每个值在每个字段中的唯一值分布状态。
比如表t1有100行记录,其中一列为f1。f1中唯一值的个数可以是100个,也可以是1个,当然也可以是1到100之间的任何一个数字。这里唯一值越的多少,就是这个列的可选择基数。
那看到这里我们就明白了,为什么要在基数高的字段上建立索引,而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面,至于更深入的探讨就不在我这篇探讨的范围了。
概念二,关于HINT的使用。
这里我来说下HINT是什么,在什么时候用。
HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作,使她生成最优的执行计划。一般来说,优化器的执行计划都是最优化的,不过在某些特定场景下,执行计划可能不是最优化。
比如:表t1经过大量的频繁更新操作,(UPDATE,DELETE,INSERT),cardinality已经很不准确了,这时候刚好执行了一条SQL,那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢?
来看下具体演示
譬如,以下两条SQL,
A:
- select * from t1 where f1 = 20;
B:
- select * from t1 where f1 = 30;
- mysql》 desc t1;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)
- mysql》 select count(*) from t1;+----------+| count(*) |+----------+| 32768 |+----------+1 row in set (0.01 sec)
SQL C:
- select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;
SQL D:
- select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;
- mysql》 explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3243.65" }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": , "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)
- mysql》 explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "441.09" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": , "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)
不加HINT,
- mysql》 explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "534.34" }, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": , "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" } }}1 row in set, 1 warning (0.00 sec)
加了HINT,
- mysql》 explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.23" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": , "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" } }}1 row in set, 1 warning (0.00 sec)
如果f1的值刚好频繁更新的值为30,并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等,那么对这两条语句来说,可能不准确的就是B了。
这里顺带说下,MySQL提供了自动更新和手动更新表cardinality值的方法,因篇幅有限,需要的可以查阅手册。
那回到正题上,MySQL 8.0 带来了几个HINT,我今天就举个index_merge的例子。
示例表结构:
表记录数:
这里我们两条经典的SQL:
表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。
那我们来看SQL C的查询计划。
显然,没有用到任何索引,扫描的行数为32034,cost为3243.65。
我们加上hint给相同的查询,再次看看查询计划。
这个时候用到了index_merge,union了三个列。扫描的行数为1103,cost为441.09,明显比之前的快了好几倍。
我们再看下SQL D的计划:
对比下以上两个,加了HINT的比不加HINT的cost小了100倍。
总结下,就是说表的cardinality值影响这张的查询计划,如果这个值没有正常更新的话,就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。
MySQL性能优化的参数有哪些
set global 参数名=参数值;set @@global.参数名 :=参数值;会话参数set 参数名=参数值;set @@session.参数名 :=参数值;内存配置相关参数确定可以使用的内存的上限确定mysql每个连接使用的内存
mysql 参数调优(10)之 tmp_table_size 优化临时表
***隐藏网址***
tmp_table_size默认16M。tmp_table_size如果过小,存不下了就会存到磁盘上。对于group by会有性能影响。
下面的sql EXPLAIN 如下,出现了Using temporary。表示查询会利用临时表。
在默认tmp_table_size大小16M下执行:
查看临时表统计信息,Created_tmp_disk_tables 为0,Created_tmp_tables 为1表示上诉sql执行后生产了一张内存里的临时表。
将tmp_table_size 调从16M调整为16K
再次执行,查询时间从4变成了18秒
重新统计
再次查看status,这次有在磁盘上创建1个临时表。
设置为32M
Percona Server中的临时表信息会记录到慢查询日志 由于MySQL慢查询日志里没有使用临时表的信息,这就给我们诊断性能问题带来了一些不便,第三方的版本如Percona Server,在慢查询里可以有更详细的信息,将会记录临时表使用的情况,从而有助于我们诊断和调优。
mysql8中对临时表有较大的优化 临时表引擎使用innodb(default 磁盘)和temptable(default 内存)
更多文章:
pdf转cad免费转换器手机版(在手机上如何将PDF图纸转换成CAD图纸呢)
2024年7月2日 09:52
漫画英雄 终极联盟2(漫画英雄终极联盟2 应用程序无法正常启动0xc000000142怎么回事)
2024年2月26日 03:00
生化危机真的会发生吗在自然界僵尸病毒真的存在吗?世界真的有生化危机的僵尸吗
2023年6月30日 11:40
4399无需下载马上玩(不用下载4399在线游戏可以马上玩吗)
2024年5月1日 12:22
求冒险岛2.0机械师1-200级挂机升级路线/ 加点、?冒险岛机械师技能点和升级路线
2024年7月19日 17:35
玉林同城游戏大厅手机版(同城游戏大厅官方下载六副够级纸牌手机版)
2024年4月10日 19:10
最新网游手游排行榜2022前十名(2022年最火的游戏前十名)
2024年10月6日 02:26
图片格式转换器在线(有哪些在线免费heic图片转换,HEIC格式如何转换成JPG格式)
2024年7月12日 19:28