面试中常问:mysql数据库做哪些优化也提高mysql性能
根据服务器配置mysql性能优化参数,从系统层面增强mysql的性能优化数据表结构,字段类型,字段索引,分表,分库,读写分离等等,从数据库层面增强性能
通过sysbench的oltp_read_write测试来模拟业务压力、以此来给指定的硬件环境配置一份比较合理的MySQL配置文件。
环境介绍
硬件配置
请点击输入图片描述
软件环境
请点击输入图片描述
优化层级与指导思想
优化层级
MySQL数据库优化可以在多个不同的层级进行,常见的有:
SQL优化
参数优化
架构优化
本文重点关注:参数优化
指导思想
日志先行 -- 一个事务能否成功提交的关键是日志是否成功落盘,与数据没有太大的关系;也就是说对写的优化可以表述为各方面的资源向写操作倾斜。
瓶颈分析 -- 通过show global status 的各个计数器的值基本上就能分析出当前瓶颈所在,再结合一些简单的系统层面的监控工具如top iostat 就能明确瓶颈。
整体性能是“读”&“写”之间的再平衡。
在开始演示之前,我们先介绍下两个概念。
概念一,数据的可选择性基数,也就是常说的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": [ "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)
- 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": [ "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)
不加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)
如果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数据库”来提高“mysql性能”的方法有:
1、选取最适用的字段属性。
MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。
2、使用连接(JOIN)来代替子查询(Sub-Queries)。
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。
3、使用联合(UNION)来代替手动创建的临时表。
MySQL 从4.0的版本开始支持UNION查询,它可以把需要使用临时表的两条或更多的SELECT查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。
4、事务。
要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句块中每条语句都操作成功,要么都失败。
5、锁定表。
尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。
6、使用外键。
锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
7、使用索引
索引是提高数据库性能的常用方法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候,性能提高更为明显。
8、优化的查询语句
绝大多数情况下,使用索引可以提高查询的速度,但如果SQL语句使用不恰当的话,索引将无法发挥它应有的作用。
Mysql占用CPU过高的时候,该从哪些方面下手进行优化?
占用CPU过高,可以做如下考虑:
1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL,比如适当建立某字段的索引;
2)打开慢查询日志,将那些执行时间过长且占用资源过多的SQL拿来进行explain分析,导致CPU过高,多数是GroupBy、OrderBy排序问题所导致,然后慢慢进行优化改进。比如优化insert语句、优化group by语句、优化order by语句、优化join语句等等;
3)考虑定时优化文件及索引;
4)定期分析表,使用optimize table;
5)优化数据库对象;
6)考虑是否是锁问题;
7)调整一些MySQL Server参数,比如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等;
8)如果数据量过大,可以考虑使用MySQL集群或者搭建高可用环境。
9)可能由于内存latch(泄露)导致数据库CPU高
10)在多用户高并发的情况下,任何系统都会hold不住的,所以,使用缓存是必须的,使用memcached或者redis缓存都可以;
11)看看tmp_table_size大小是否偏小,如果允许,适当的增大一点;
12)如果max_heap_table_size配置的过小,增大一点;
13)mysql的sql语句睡眠连接超时时间设置问题(wait_timeout)
14)使用show processlist查看mysql连接数,看看是否超过了mysql设置的连接数(http://www.cnblogs.com/kevingrace/p/6226324.html)
1.查询时,能不用* 就不用,尽量写全字段名。
2.索引不是越多越好,每个表控制在6个索引以内。范围where条件的情况下,索引不起作用,比如where value<100
3.大部分情况连接效率远大于子查询,但是有例外。当你对连接查询的效率都感到不能接受的时候可以试试用子查询,虽然大部分情况下你会更失望,但总有碰到惊喜的时候不是么...
4.多用explain 和 profile分析查询语句
5.有时候可以1条大的SQL可以分成几个小SQL顺序执行,分了吧,速度会快很多。
6.每隔一段时间用alter table table_name engine=innodb;优化表
7.连接时注意:小表 jion 大表的原则
8.学会用explain 和 profile判断是什么原因使你的SQL慢。
9.查看慢查询日志,找出执行时间长的SQL试着优化去吧~~
可能是代码上重复调用数据库,
什么是MySql数据库
将拷贝过去的文件的属主改为mysql:mysql,权限改为660 chown mysql:mysql \/var\/lib\/mysql\/mydb\/* chmod 660 \/var\/lib\/mysql\/mydb\/* Mssql转换mysql的方法: 1.导表结构 使用MySQL生成create脚本的方法。找到生成要导出的脚本,按MySQL的语法修改一下到MySQL数据库中创建该表的列结构什么的。 2.导表数据 在MS...
测试工程师面试常见问题是什么?
答:这个主要就是看公司具体的项目是什么,了解清楚功能模块,对主要功能模块做业务逻辑的一个描述就行。3、测试的过程中,还用过一些什么工具吗?答:有就有,没有就没有,一定要如实回答!比如在测试接口或者简单施压的时候用到了jmeter。4、如果简历上写了,MySQL、Linux等,一般会问,掌握的程度...
mysql中limit的用法
mysql遇到自增id用完的情况时,用户只需将自增ID的类型改为BigInt格式即可。MySQL由瑞典MySQLAB公司开发,是最流行的关系型数据库管理系统之一。在WEB应用方面,MySQL是最好的RDBMS应用软件之一。MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,...
MySQL错误SQL Error:1366怎么解决
MySQL 错误 SQL Error: 1366 的解决方法 SQL Error: 1366 是一个常见的 MySQL 错误,通常表示在执行 SQL 查询时,数据类型不匹配或者数据超出了范围。为了解决这个错误,我们需要先理解它的成因,然后针对性地采用解决方案。错误成因:1. 数据类型不匹配:当我们在查询中使用了错误的数据类型,或者尝试...
mysql中常见的数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的 MySQL支持多种数据类型,大致可以分为三类:数值 日期\/时间和字符串 二、数值类型 1.整数类型 2.浮点数 如果希望保证值比较准确,推荐使用定点数数据类型。MySql中的浮点类型有float,double和real。他们定义方式为:FLOAT(M,D) 、 REAL(M,D) ...
mysql常用查询命令?
1. SELECT:查询内容,语法:SELECT 列名称 FROM 表名称 2. SELECT DISTINCT 查询非重复内容,语法:SELECT DISTINCT 列名称 FROM 表名称 3. WHERE 查询条件, 子句中可以指定任何条件,条件与条件之间用and 和 or 隔开 语法:SELECT 列名称 FROM 表名称 WHERE条件1 AND 条件2 4. AND 和 OR ...
为什么MySQL总是连不上
1. 检查一下MySQL连接参数配置,连接参数中主机地址写的是IP还是机器 名;2. 检查MySQL服务器是否是动态获取IP,如果是,那客户端连接时,如果按IP联接,就有可能出现IP不正确的情况。3. 检查MySQL服务器,看服务是否经常停机;如果是这个问题保证服务稳定即可。建议连接池参数写IP,然后服务器设置固定IP...
MySQL中几种常见的日志
前言:在 MySQL 系统中,有着诸多不同类型的日志。各种日志都有着自己的用途,通过分析日志,我们可以优化数据库性能,排除故障,甚至能够还原数据。这些不同类型的日志有助于我们更清晰的了解数据库,在日常学习及运维过程中也会和这些日志打交道。本节内容将带你了解 MySQL 数据库中几种常用日志的作用...
mysql 常用命令手册
mysql> desc MyClass;mysql> show columns from MyClass;3、删除表 命令:drop table <表名> 例如:删除表名为 MyClass 的表 mysql> drop table MyClass;4、插入数据 命令:insert into <表名> [( <字段名>[,..<字段名n> ])] values ( 值 )[, ( 值n )]例如,往表 MyClass中插入...
15个MySQL常用基本SQL语句
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。在MySQL中,数据库称为database,数据表称为table,一个数据库软件中有多个数据库(databases),每个数据库中又可以有多个...
浦全复方: Mysql占用CPU过高的时候,该从哪些方面下手进行优化? 占用CPU过高,可以做如下考虑: 1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL...
抚顺县13180297758: 请问MySQL用来做什么的? - ?
浦全复方: MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司.在2008年1月16号被Sun公司收购.而2009年,SUN又被Oracle收购.MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内.这样就增加了速度并提高了灵活性.MySQL的SQL“结构化查询语言”.SQL是用于访问数据库的最常用标准化语言.MySQL软件采用了GPL(GNU通用公共许可证).由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库.
抚顺县13180297758: Mysql 数据库面试问题 - ?
浦全复方: selectt1.non_seller, t3.coupon, ifnull(sum(t2.bal),0) from(select seller,coupon from(select distinct seller from t2) c, (select distinct coupon from t2) d) t3 left join t2 on t3.seller=t2.seller and t3.coupon=t2.coupon join t1 on t1.seller=t3.seller group by...
抚顺县13180297758: mysql dba是指什么 会哪些东西才能成为mysql dba?请大家帮忙指点?
浦全复方: MySQL DBA意思是MySQL数据库管理员,DBA就是数据库管理员的意思. 要成为MySQL DBA,不是会哪些东西的问题,而是一定要对MySQL数据库方方面面都得非常精通才行,除非你面试的公司要求不高.DBA的职责是: 安装和升级数据...
抚顺县13180297758: mysql存储过程是什么意思?什么时候会用到,主要用来做什么? - ?
浦全复方: 存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合.可将其视为批件,虽然它们的作用不仅限于批处理. 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表,更新数据, 删除等等. 你可以理解为用...
抚顺县13180297758: 数据库 面试时候面试官常会问到哪些知识点,关于Sql server的. - ?
浦全复方: 应届一般都是建表,增,删,改,查,排序,分组,升序,降序,表连接(左右),有工作经验那就要求高啦,数据库优化,数据结构,存储过程,游标,sql脚本
抚顺县13180297758: 面试被问到怎么优化mysql - ?
浦全复方: 根据服务器配置mysql性能优化参数,从系统层面增强mysql的性能 优化数据表结构,字段类型,字段索引,分表,分库,读写分离等等,从数据库层面增强性能
抚顺县13180297758: 面试mysql数据库dba时候会问什么问题 - ?
浦全复方: 1.数据优化,2.sql,3.场景分析
抚顺县13180297758: 通常去面试,面试官会问,数据库会的怎么样? - ?
浦全复方: 如果是DBA的话可能会深一些,如果面试程序员之类的应该不会问太深.大都是数据库常用的一些,比如自定义,LEFT ,RIGHT,INNER JOIN区别之类.对于程序员就够了.
抚顺县13180297758: 想从事数据库相关的工作,迷惑中 - ?
浦全复方: 数据库方面的工作分为两类:一类是利用SQL做开发,这个是对数据库的应用.一般来说,都是编程人员,利用接口比如JDBC调用数据库的SQL来做查询.这种工作重点还是在编程上.因为涉及到的数据库方面的知识并不算多.说白了,就是...