锐客网

  1. 首页 > 睿知 > it技术 > >

mysql优化配置怎么用 mysql配置优化参数

优化IT技术

linux 下怎么优化mysql占用内存？修改mysql配置文件 ， 优化缓存大小和连接数连接方式，优化sql语句 ,记得mysql好像是有工具可以查看最占用资源的sql语句，找到他，优化他 。
安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境 ， 当然需要有不同的配制文件了 。
一般的情况下 ， my-medium.cnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了 ， 使用mysqladmin variables extended-status _u root _p 可以看到目前的参数，有3个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache 。
key_buffer_size只对MyISAM表起作用 ，
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度 。一般我们设为16M,实际上稍微大一点的站点　这个数字是远远不够的 ， 通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理 。比例 key_reads / key_read_requests应该尽可能的低，至少是1:100 ， 1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得） 。或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:
这个服务器已经运行了20天
key_buffer_size _ 128M
key_read_requests _ 650759289
key_reads - 79112
比例接近1:8000 健康状况非常好
mysql 优化包括哪些内容？mysql的优化大的有两方面：
1、配置优化
配置的优化其实包含两个方面的：操作系统内核的优化和mysql配置文件的优化
1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；
2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可 ， 网上的相关配置文件很多，大同小异 ， 常用的设置大多修改这些差不多就够用了 。
2、sql语句的优化
1）尽量稍作计算
Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的 。
2）尽量少 join
MySQL 的优势在于简单，但这在某些方面其实也是其劣势 。MySQL 优化器效率高 ， 但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多 。对于复杂的多表 Join ， 一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离 。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈
3）尽量少排序
排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间 。
对于MySQL来说，减少排序有多种办法，比如：
通过利用索引来排序的方式进行优化
减少参与排序的记录条数
非必要不对数据进行排序
4）尽量避免 select *
在数据量少并且访问量不大的情况下，select * 没有什么影响，但是量级达到一定级别的时候，在执行效率和IO资源的使用上，还是有很大关系的 ， 用什么字段取什么字段，减少不必要的资源浪费 。
5）尽量用 join 代替子查询
虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势 。MySQL 的子查询执行计划一直存在较大的问题 ， 虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在 ， 一直没有太大改善 。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题 。
怎么进行mysql数据库优化？有八个方面可以对mysql进行优化：
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存?。且话闼道矗?数据库中的表越小，在它上面执行的查询也就会越快 。因此 ， 在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小 。
2. 使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询 。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中 。
3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中 。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效 。
4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的 。更多的时候是需要用到一系列的语句来完成某种工作 。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来 。设想一下 ， 要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后 ， 数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据 。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中 。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束 。其实 ， 有些情况下我们可以通过锁定表的方法来获得更好的性能 。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性 。这个时候我们就可以使用外键 。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行 ， 尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显 。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果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;
如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等 ， 那么对这两条语句来说，可能不准确的就是B了 。
这里顺带说下 ， MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限 ， 需要的可以查阅手册 。
那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子 。
示例表结构：
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：
SQL C：
select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;
SQL D：
select * from t1 where rank1 =100and rank2 =100and rank3 =100;
表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引 。
那我们来看SQL C的查询计划 。
显然，没有用到任何索引，扫描的行数为32034，cost为3243.65 。
mysql explainformat=json select * from t1where 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": ["idx_rank1","idx_rank2","idx_rank3"],"rows_examined_per_scan": 32034,"rows_produced_per_join": 115,"filtered": "0.36","cost_info": {"read_cost": "3232.07","eval_cost": "11.58","prefix_cost": "3243.65","data_read_per_join": "49K"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"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给相同的查询，再次看看查询计划 。
这个时候用到了index_merge,union了三个列 。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍 。
mysql explainformat=json select /*index_merge(t1) */ * from t1where 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": ["idx_rank1","idx_rank2","idx_rank3"],"key": "union(idx_rank1,idx_rank2,idx_rank3)","key_length": "5,5,5","rows_examined_per_scan": 1103,"rows_produced_per_join": 1103,"filtered": "100.00","cost_info": {"read_cost": "330.79","eval_cost": "110.30","prefix_cost": "441.09","data_read_per_join": "473K"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"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)
我们再看下SQL D的计划：
不加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": ["idx_rank1","idx_rank2","idx_rank3"],"key": "idx_rank1","used_key_parts": ["rank1"],"key_length": "5","ref": ["const"],"rows_examined_per_scan": 555,"rows_produced_per_join": 0,"filtered": "0.07","cost_info": {"read_cost": "478.84","eval_cost": "0.04","prefix_cost": "534.34","data_read_per_join": "176"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"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": ["idx_rank1","idx_rank2","idx_rank3"],"key": "intersect(idx_rank1,idx_rank2,idx_rank3)","key_length": "5,5,5","rows_examined_per_scan": 1,"rows_produced_per_join": 1,"filtered": "100.00","cost_info": {"read_cost": "5.13","eval_cost": "0.10","prefix_cost": "5.23","data_read_per_join": "440"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"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)
对比下以上两个，加了HINT的比不加HINT的cost小了100倍 。
总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了 。相信MySQL未来的版本会带来更多的HINT 。
优化mysql数据库的几个步骤1、选取最适用mysql优化配置怎么用的字段属性,尽可能减少定义字段长度,尽量把字段设置NOT NULL,例如'省份,性别',最好设置为ENUM
2、使用连接（JOIN）来代替子查询:
a.删除没有任何订单客户:DELETE FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)
b.提取所有没有订单客户:SELECT FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)
c.提高bmysql优化配置怎么用的速度优化:SELECT FROM customerinfo LEFT JOIN orderid customerinfo.customerid=orderinfo.customerid
WHERE orderinfo.customerid IS NULL
3、使用联合(UNION)来代替手动创建的临时表
a.创建临时表:SELECT name FROM `nametest` UNION SELECT username FROM `nametest2`
4、事务处理:
a.保证数据完整性,例如添加和修改同时,两者成立则都执行,一者失败都失败
mysql_query("BEGIN");
mysql_query("INSERT INTO customerinfo (name) VALUES ('$name1')";
mysql_query("SELECT * FROM `orderinfo` where customerid=".$id");
mysql_query("COMMIT");
5、锁定表,优化事务处理:
a.mysql优化配置怎么用我们用一个 SELECT 语句取出初始数据mysql优化配置怎么用，通过一些计算mysql优化配置怎么用，用 UPDATE 语句将新值更新到表中 。
包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，
不会有其它的访问来对 inventory 进行插入、更新或者删除的操作
mysql_query("LOCK TABLE customerinfo READ, orderinfo WRITE");
mysql_query("SELECT customerid FROM `customerinfo` where id=".$id);
mysql_query("UPDATE `orderinfo` SET ordertitle='$title' where customerid=".$id);
mysql_query("UNLOCK TABLES");
6、使用外键,优化锁定表
a.把customerinfo里的customerid映射到orderinfo里的customerid,
任何一条没有合法的customerid的记录不会写到orderinfo里
CREATE TABLE customerinfo
(
customerid INT NOT NULL,
PRIMARY KEY(customerid)
)TYPE = INNODB;
CREATE TABLE orderinfo
(
orderid INT NOT NULL,
customerid INT NOT NULL,
PRIMARY KEY(customerid,orderid),
FOREIGN KEY (customerid) REFERENCES customerinfo
(customerid) ON DELETE CASCADE
)TYPE = INNODB;
注意:'ON DELETE CASCADE',该参数保证当customerinfo表中的一条记录删除的话同时也会删除order
表中的该用户的所有记录,注意使用外键要定义事务安全类型为INNODB;
7、建立索引:
a.格式:
(普通索引)-
创建:CREATE INDEX 索引名 ON tablename (索引字段)
修改:ALTER TABLE tablename ADD INDEX [索引名] (索引字段)
创表指定索引:CREATE TABLE tablename([...],INDEX[索引名](索引字段))
(唯一索引)-
创建:CREATE UNIQUE 索引名 ON tablename (索引字段)
修改:ALTER TABLE tablename ADD UNIQUE [索引名] (索引字段)
创表指定索引:CREATE TABLE tablename([...],UNIQUE[索引名](索引字段))
(主键)-
它是唯一索引,一般在创建表是建立,格式为:
CREATA TABLE tablename ([...],PRIMARY KEY[索引字段])
8、优化查询语句
a.最好在相同字段进行比较操作,在建立好的索引字段上尽量减少函数操作
例子1:
SELECT * FROM order WHERE YEAR(orderDate)2008;(慢)
SELECT * FROM order WHERE orderDate"2008-01-01";(快)
例子2:
SELECT * FROM order WHERE addtime/724;(慢)
SELECT * FROM order WHERE addtime24*7;(快)
例子3:
SELECT * FROM order WHERE title like "%good%";
SELECT * FROM order WHERE title="good" and name"good";
超详细MySQL数据库优化 数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.
1. 优化一览图
2. 优化
笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.
2.1 软优化
2.1.1 查询语句优化
1.首先mysql优化配置怎么用我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
2.例:
显示:
其中会显示索引和查询数据读取数据条数等信息.
2.1.2 优化子查询
在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.
2.1.3 使用索引
索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者MySQL数据库索引一文,介绍比较详细,此处记录使用索引的三大注意事项:
2.1.4 分解表
对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,
2.1.5 中间表
对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.
2.1.6 增加冗余字段
类似于创建中间表,增加冗余也是为了减少连接查询.
2.1.7 分析表,,检查表,优化表
分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.
1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;
2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]
option 只对MyISAM有效,共五个参数值:
3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;
LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.
2.2 硬优化
2.2.1 硬件三件套
1.配置多核心和频率高的cpu,多核心可以执行多个线程.
2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.
2.2.2 优化数据库参数
优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.
2.2.3 分库分表
因为数据库压力过大mysql优化配置怎么用，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响 。另外一个 ， 压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表读写分离，也就是把一个库拆分为多个库 ， 部署在多个数据库服务上，这时作为主库承载写入请求 。然后每个主库都挂载至少一个从库，由从库来承载读请求 。
【mysql优化配置怎么用 mysql配置优化参数】 2.2.4 缓存集群
如果用户量越来越大，此时你可以不停的加机器 ， 比如说系统层面不停加机器，就可以承载更高的并发请求 。然后数据库层面如果写入并发越来越高 ， 就扩容加数据库服务器 ， 通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库 。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高 。如果你就是简单的不停的加机器 ， 其实是不对的 。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生 。所以单机承载的并发量都在每秒几万，甚至每秒数十万 ， 对高并发的承载能力比数据库系统要高出一到两个数量级 。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群 。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求 。这样的话 ， 通过缓存集群，就可以用更少的机器资源承载更高的并发 。
一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统 。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.
mysql优化配置怎么用的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql配置优化参数、mysql优化配置怎么用的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：如何建立一个科学的上网服务器？ 怎么搭建科学上网服务器

    下一篇：如何修复广告服务器问题？ ad服务器怎么修复