MySQL之查询优化方式（笔记）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之查询优化方式（笔记），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8000字，纯文字阅读大概需要12分钟。

内容图文

1.COUNT()

　　对COUNT的优化可以通过下面的SQL实现

mysql> select count(gnp<10000 or null) as  ‘<<<<‘,count(gnp>=10000 or null) as ‘>>>>‘ from country;
+------+------+
| <<<< | >>>> |
+------+------+
|  152 |   87 |
+------+------+
1 row in set (0.00 sec)

count(*)与count(cloumn)返回的值可能不一样，因为如果存在空的情况，count(*)也会计算在内

2.SUM()

对SUM（）的优化需要通过建立索引实现

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

mysql> create index popu_index on city(population);
Query OK, 4079 rows affected (0.05 sec)
Records: 4079  Duplicates: 0  Warnings: 0

mysql> explain select SUM(population) as  sum from city;
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | index | NULL          | popu_index | 4       | NULL | 4079 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select SUM(population) as  sum from city;
+------------+
| sum        |
+------------+
| 1429559884 |
+------------+
1 row in set (0.00 sec)

　3.子查询的优化

　　对于子查询的优化可以通过on实现

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   | MUL | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> desc country;;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+--
| Field          | Type                                                                                  | Null | Key | Default | E
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+--
| Code           | char(3)                                                                               | NO   | PRI |         |
| Name           | char(52)                                                                              | NO   |     |         |
| Continent      | enum(‘Asia‘,‘Europe‘,‘North America‘,‘Africa‘,‘Oceania‘,‘Antarctica‘,‘South America‘) | NO   |     | Asia    |
| Region         | char(26)                                                                              | NO   |     |         |
| SurfaceArea    | float(10,2)                                                                           | NO   |     | 0.00    |
| IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |
| Population     | int(11)                                                                               | NO   |     | 0       |
| LifeExpectancy | float(3,1)                                                                            | YES  |     | NULL    |
| GNP            | float(10,2)                                                                           | YES  |     | NULL    |
| GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |
| LocalName      | char(45)                                                                              | NO   |     |         |
| GovernmentForm | char(45)                                                                              | NO   |     |         |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |
| Capital        | int(11)                                                                               | YES  |     | NULL    |
| Code2          | char(2)                                                                               | NO   |     |         |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+--
15 rows in set (0.01 sec)

ERROR:
No query specified

mysql> explain select t.* from city t where t.countrycode in( select code from country);
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | t       | ALL    | NULL          | NULL    | NULL    | NULL                | 4079 | NULL        |
|  1 | SIMPLE      | country | eq_ref | PRIMARY       | PRIMARY | 3       | world.t.CountryCode |    1 | Using index |
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+-------------+
2 rows in set (0.04 sec)

mysql> explain select t.* from city t join country t1 on t.countrycode = t1.code;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | t     | ALL    | NULL          | NULL    | NULL    | NULL                | 4079 | NULL        |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 3       | world.t.CountryCode |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+
2 rows in set (0.00 sec)

　　这样的优化可能返回重复的行，去重需要使用关键字distinct

4.Group by优化

正常执行情况下如下

mysql> explain select t.* from city t join country t1 on t.countrycode = t1.code group by t.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
|  1 | SIMPLE      | t     | ALL    | NULL          | NULL    | NULL    | NULL                | 4079 | Using temporary; Using filesort |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 3       | world.t.CountryCode |    1 | Using index                     |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
2 rows in set (0.02 sec)

　　可以看到了使用临时表还有文件排序的方式来实现，那么我们应该怎么减少IO已经对资源的消耗呢，通过下面方式可以看出

mysql> explain select t.* from city t join (select code from country) t1 on t.countrycode = t1.code;
+----+-------------+------------+-------+---------------+-------------+---------+---------------------+------+-------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref                 | rows | Extra       |
+----+-------------+------------+-------+---------------+-------------+---------+---------------------+------+-------------+
|  1 | PRIMARY     | t          | ALL   | NULL          | NULL        | NULL    | NULL                | 4079 | NULL        |
|  1 | PRIMARY     | <derived2> | ref   | <auto_key0>   | <auto_key0> | 3       | world.t.CountryCode |   10 | Using index |
|  2 | DERIVED     | country    | index | NULL          | PRIMARY     | 3       | NULL                |  239 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+---------------------+------+-------------+
3 rows in set (0.00 sec)

　　这样就可以避免了文件排序还有使用该临时表的情况。

5.limit操作方式的优化

　　使用之前：

mysql> explain select * from city order by name limit  100,10;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

　　可以看到使用了文件排序，这样会造成性能上的低下。

　　优化方式：可以通过主键或者是含有索引的列进行order by 操作；

mysql> create index name_index  on city(name);

mysql> explain select name from city order by name limit  100,10;
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | index | NULL          | name_index | 35      | NULL |  110 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

　　可以发现只扫描了35行而且使用了索引但是效率增加了很多。

原文：http://www.cnblogs.com/zhangminghui/p/4170486.html

内容总结

以上是互联网集市为您收集整理的MySQL之查询优化方式（笔记）全部内容，希望文章能够帮你解决MySQL之查询优化方式（笔记）所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1322312.html

来源：【匿名】

【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL之查询优化方式（笔记）】教程文章相关的互联网学习教程文章

1.COUNT()　　对COUNT的优化可以通过下面的SQL实现mysql> select count(gnp<10000 or null) as ‘<<<<‘,count(gnp>=10000 or null) as ‘>>>>‘ from country; +------+------+ | <<<< | >>>> | +------+------+ | 152 | 87 | +------+------+ 1 row in set (0.00 sec) count(*)与count(cloumn)返回的值可能不一样，因为如果存在空的情况，count(*)也会计算在内2.SUM()对SUM（）的优化需要通过建立索引实现mysql> desc city; ...

Mysql查询优化随笔记录

select SQL_CALC_FOUND_ROWS * from (select * from oses_vehdata201606 union all select * from oses_vehdata201605) t where 1=1 and t.Check_Time between ‘2016-06-23 00:00:00‘ and ‘2016-06-23 23:59:59‘ order by Check_Time desc 结果：45.66 s 、36.5sselect SQL_CALC_FOUND_ROWS * from (select * from oses_vehdata201606) t where 1=1 and t.Check_Time between ‘2016-06-23 00:00:00‘ and ‘2016-06-23...

MySQL查询优化注意下面的四个细节

原文：http://bbs.landingbj.com/t-0-244231-1.html 在任何一个数据库中，查询优化都是不可避免的一个话题。对于数据库工程师来说，优化工作是最有挑战性的工作。MySQL开源数据库也不例外。其实笔者认为，数据库优化并没有大家所想象的那么苦难。通常情况下，大家可以从以下四个细节出发来做好MySQL数据库的查询优化工作。一、利用EXPLAIN关键字来评估查询语句中的缺陷如下图所示，现在笔者在数据库中执行了一条简单的Sel...

mysql千万级大数据SQL查询优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=03.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。4.应尽...

《打造扛得住的MySQL数据库架构》第7章 SQL查询优化

SQL查询优化7-1 获取有性能问题SQL的三种方法如何设计最优的数据库表结构如何建立最好的索引如何拓展数据库的查询查询优化，索引优化，库表结构优化如何获取有性能问题的SQL1、通过测试反馈获取存在性能问题的SQL2、通过慢查日志获取存在性能问题的SQL3、实时获取存在性能问题的SQL 7-2 慢查询日志介绍存储日志所需要的大量的磁盘空间slow_query_log 启动停止记录慢查询日志，默认为关闭如果希望在已经运行的MySQL上on此功能可通...

MySQL查询优化处理

查询的生命周期的下一步是将一个sql转化成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析sql，预处理，优化sql执行计划。这个过程中任何错误（例如语法错误）都可能终止查询。这里不打算详细介绍MySQL内部实现，而只是选择性的介绍其中几个独立的部分，在实际中，这几部分可能以前执行也可能单独执行。我们的目的是帮助大家理解MySQL是如何执行查询的，以便写出更优秀的查询。·　　语法解析器和...

Mysql学习笔记（九)索引查询优化【代码】【图】

原文:Mysql学习笔记（九)索引查询优化PS：上网再次看了一下数据库关于索引的一些细节...感觉自己学的东西有点少...又再次的啃了啃索引....学习内容：索引查询优化...上一章说道的索引还不是特别的详细，再补充一些具体的细节...1.B-Tree索引...B-tree结构被称为平衡多路查找树...其数据结构为：这就是其数据结构图。。。我们没必要完全的理解其中的原理。。并且我也不会做过多的原理介绍。。。我们只需要知道数据库是以这种方式...

MySQL分页查询优化【代码】

当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。下面简单说一下我知道的一些方法。准备工作为了对下面列举的一些优化进行测试，下面针对已有的一张表进行说明。表名：order_history描述：某个业务的订单历史表主要字段：unsigned int id，tinyint(4) int type字段情况：该表一共37个字段，不包含...

mysql 子查询优化

今天用到要查询七天内都没有装机的门店信息，首先想到了用not in，先把装机的userid查出来，然后再id not in,但是这样就必须使用子查询，数据量少还可以，数据量大了的话，肯定效率特别低，因为MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。首先使用：selec...

MySQL索引原理及慢查询优化【代码】

建索引的几大原则1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。 3.尽量...

mysql嵌套查询和联表查询优化方法

嵌套查询糟糕的优化在上面我提到过，不考虑特殊的情况，联表查询要比嵌套查询更有效。尽管两条查询表达的是同样的意思，尽管你的计划是告诉服务器要做什么，然后让它决定怎么做，但有时候你非得告诉它改怎么做。否则优化器可能会做傻事。我最近就碰到这样的情况。这几个表是三层分级关系：category, subcategory和item。有几千条记录在category表，几百条记录在subcategory表，以及几百万条在item表。你可以忽略category表了，我只...

mysql查询优化

针对mysql查询时反应慢问题，可以在select上做些修改1 尽量少使用null在where语句中，这样会导致引擎放弃使用索引而进行全表扫描比如 select id from table where num is null; 可以改成 select id from table where num=0;2 在where语句中少使用or连接，这样会导致引擎放弃使用索引而进行全表扫描，比如 select id from table where num=10 or num=20; 可以使用 select id from table where num=10 union select id from table w...

MySQL——查询优化|47s到0.1s|我做了什么【图】

前言这个代码是之前的同事写的，现在我接管了，但是今天早上我打开这个模块的时候发现数据加载异常的缓慢，等了将近一分钟左右数据才显示到页面。这特么的绝对不正常啊，数据量压根没那么多呀，这特喵的什么情况！果断打开代码分析下SQL！后台框架: TP5.1步骤使用fetchSql()打印出SQL语句，然后使用Navicat的查询先跑下。不跑不知道,一跑吓一跳,这什么鬼,需要47s仔细查看SQL后,发现where条件存在问题，直接优化修改后只需要0.1s分...

MySQL巧用自定义函数进行查询优化【代码】

用户自定义变量是一个很容易被遗忘的MySQL特性,但是用的好,发挥其潜力,在很多场景都可以写出非常高效的查询语句。一. 实现一个按照actorid排序的列 1 mysql>set@rownum :=0;2 Query OK, 0 rows affected (0.00 sec)3 4 mysql>select actor_id ,@rownum :=@rownum+1as rownum5->from sakila.actor limit 3;6+----------+--------+ 7| actor_id | rownum | 8+----------+--------+ 9|58|1|10|92|2|11|182|3|12+----------+--------+1...

MySQL查询优化【代码】

select的 high_priority还是比较有用，在实践中，平均5~6秒提高到3秒=========================================================================前面的部分主要是聚焦于如何让单独的查询执行的速度更快。MySQL还允许你改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。这一部分讲解MySQL的默认的调度策略和可以用...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL之查询优化方式（笔记）