在开始演示之前,我们先介绍下两个概念。
概念一,数据的可选择性基数,也就是常说的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 80 带来了几个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 (000 sec)表记录数:
mysql> select count() from t1;+----------+| count() |+----------+| 32768 |+----------+1 row in set (001 sec)这里我们两条经典的SQL:
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;表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。
那我们来看SQL C的查询计划。
显然,没有用到任何索引,扫描的行数为32034,cost为324365。
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": "324365" }, "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": "036", "cost_info": { "read_cost": "323207", "eval_cost": "1158", "prefix_cost": "324365", "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 (000 sec)我们加上hint给相同的查询,再次看看查询计划。
这个时候用到了index_merge,union了三个列。扫描的行数为1103,cost为44109,明显比之前的快了好几倍。
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": "44109" }, "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": "10000", "cost_info": { "read_cost": "33079", "eval_cost": "11030", "prefix_cost": "44109", "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 (000 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": "53434" }, "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": "007", "cost_info": { "read_cost": "47884", "eval_cost": "004", "prefix_cost": "53434", "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 (000 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": "523" }, "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": "10000", "cost_info": { "read_cost": "513", "eval_cost": "010", "prefix_cost": "523", "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 (000 sec)对比下以上两个,加了HINT的比不加HINT的cost小了100倍。
总结下,就是说表的cardinality值影响这张的查询计划,如果这个值没有正常更新的话,就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。
在进行软件开发过程中,数据库的使用是非常重要的,但是数据库有很多种,不同数据库的使用方法是不同的。进行软件开发过程中,至少需要掌握一种数据库的使用方法。SQL数据库语法简单、 *** 作方便和高效,是很多人最优的选择,但是SQL语句会受到不同数据库功能的影响,在计算时间和语言的效率上面需要进行优化,根据实际情况进行调整。下面电脑培训为大家介绍SQL数据库的优化方法。
一、适当的索引
索引基本上是一种数据结构,有助于加速整个数据检索过程。唯一索引是创建不重叠的数据列的索引。正确的索引可以更快地访问数据库,但是索引太多或没有索引会导致错误的结果。IT培训认为如果没有索引,处理速度会变得非常慢。
二、仅索引相关数据
指定需要检索数据的精度。使用命令和LIMIT代替SELECT。调整数据库时,必须使用所需的数据集而不是整个数据集,尤其是当数据源非常大时,指定所需的数据集,能够节省大部分时间。
三、根据需求使用或避免临时表
如果代码可以用简单的方式编写,那么永远不要使临时表变得复杂。当然,如果数据具有需要多个查询的特定程序,北大青鸟建议在这种情况下,使用临时表。临时表通常由子查询交替。
四、避免编码循环
避免编码循环是非常重要的,因为它会减慢整个序列的速度。通过使用具有单行的唯一UPDATE或INSERT命令来避免编码循环,并且北京北大青鸟发现WHERE命令能够确保存储的数据不被更新,这样能够方便在找到匹配和预先存在的数据时被找到。
数据库优化可以从以下几个方面进行:
1结构层: web服务器采用负载均衡服务器,mysql服务器采用主从复制,读写分离
2储存层: 采用合适的存储引擎,采用三范式
3设计层: 采用分区分表,索引,表的字段采用合适的字段属性,适当的采用逆范式,开启mysql缓存
4sql语句层:结果一样的情况下,采用效率高,速度快节省资源的sql语句执行
优化SQL语句
写存储过程
给表建索引
减少一些不必要的查询
比如:只要查询demo表中的
name
字段一般都会这样写
"select
from
demo"
这样写他是查询的整张表如果有很多的字段这样差出来的效率是很低的
可以写成这样
"select
name
from
deom"
1、建表要科学,业务允许的话,尽可能的控制字段不要太多,合理的限制字段的类型和长度;
2、在常用的查询字段上建立索引;
3、不要太被第几范式之类的东西迷惑,最后是按着规范来了,每个查询都要join个5、6个表谁看着也不舒服,要合理的冗余(这个要根据自己业务来的);
4、多用业务层的缓存,比如字典库的东西不算多的话,都缓存起来吧,这样用的时候就不用select了。
5、能用TinyInt不用int,能用int不用char等等,效率来说,还是数字大于字符的;
6、多看看数据库的样例表,能学到很多东西的。
先想到了这么多,希望能帮助到你。
现在的服务器都很NB了,只要不是滥用缓存的话,就不用太在意内存大小了。
sql优化主要有两点,一是格式优化,使sql看起来整洁,易读,这样对于其他人阅读你的sql有很大帮助。二是性能优化,格式优化多数来说,使用工具就可以做到,但性能优化需要人为来做,根据你的实际业务确定优化方案,可大幅减少复杂sql的执行时间,这也是sql优化的主要目的和内容。
以上就是关于mysql数据库怎么优化,有几方面的优化全部的内容,包括:mysql数据库怎么优化,有几方面的优化、SQL数据库优化的方法有哪些、数据库该如何优化等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)