内容简介:欢迎关注公众号:【如果有需要后台回复
欢迎关注公众号:【 爱编程 】
如果有需要后台回复 2019 赠送 1T的学习资料 哦!!
背景
在这个快速发展的时代,时间变得
越来越重要,也流逝得非常得快,有些人长大了,有些人却变老了。稍不留神,2019已经过完了三分之一。回首这四个月收获什么,懂得了什么?欢迎留言分享给我哟。
**言归正传:
MySQL的查询怎么才能更快,更合理?除了加索引还有什么可以学习的呢?**
原理
要想更好地学习某样东西,从其原理和运作方式入手更容易掌握。道理你们都懂,我就不废话了。
MySQL发送查询请求,到底做了什么工作?
下图是 MySQL 查询执行流程图:
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存,如果命中了缓存,则立刻返回查询在缓存中的结果。否则会进入下一个阶段。
3.服务端进行 SQL 解析、预处理、再由优化器生成对应的执行计划。
4.MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
5.将结果返回给客户端。
是什么导致MySQL查询变慢了?
对于MySQL,最简单的衡量查询开销的三个指标如下:
- 响应时间
- 扫描的行数
- 返回的行数
没有哪个指标能够完美地衡量查询的开销,但它们大致反映了MySQL在内部执行查询时需要访问多少数据,并可以大概推算出查询运行的时间。
查询慢的原因基本都是:我们的不合理操作导致 查询的多余数据太多了 。
常见原因有以下:
1.查询不需要的记录。
2.多表关联时返回全部列
3.总是取出全部列
常用优化技巧
1.用索引
最简单且见效最快的方式就是给你的条件加索引(主键索引,普通索引,唯一索引等)。注:索引是要另开辟一块 空间存储 的,所以不能不要钱滴都加索引。
2.关联子查询
MySQL的子查询实现是非常糟糕的。比如下面的
SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)
MySQL对IN()列表中的选项有专门的优化策略,一般会认为MySQL会先执行子查询返回所有包含author_id 为1的book_id。
或许你想MySQL的运行时这样子的:
SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1 SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)
但是, MySQL会将相关的外层表压到子查询中的 ,就是下面的样子:
SELECT * FROM book WHERE EXISTS (SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)
原因:因为子查询需要book_id ,所以MySQL认为无法先执行这个子查询,而是先对book 进行全表扫描,然后再根据book_id进行子查询。具体可以EXPLAIN该SQL进行分析。
建议:
1.使用左外连接(LEFT OUTER JOIN)代替子查询。
SELECT * from book LEFT OUTER JOIN author USING(book_id) WHERE author.author_id = 1
影响因素:还有数据表放的位置等,具体应用场景就只能你自己explain该语句对比哪种性能比较好点。
2.确保ON或者USING子句的列上有索引
在创建索引的时候就要考虑到关联的顺序。
3.UNION使用
如果希望UNION的各个子句能根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话。
第一个例子:会将author 表和user 表 两个表都存放到一个临时表 中,再从临时表中取出前20条。
(SELECT first_name FROM author ORDER BY last_name) UNION ALL (SELECT first_name FROM user ORDER BY last_name) LIMIT 20
对比上面的这样子,就有很大的改善了。
(SELECT first_name FROM author ORDER BY last_name LIMIT 20) UNION ALL (SELECT first_name FROM user ORDER BY last_name LIMIT 20) LIMIT 20
4.最大值和最小值
比如:
求最小值
第一种方案:
SELECT MIN(id) FROM article WHERE author = 'zero'
第二种方案:
SELECT id FROM article USE INDEX(PRIMARY) WHERE author = 'zero' LIMIT 1
和第一种方案的对比,效果其实是一样的,但是它们的性能略有不同,具体还请自己具体场景分析,择优选择。
5.COUNT()查询
比如如果想统计文章id大于25的数量,可以如下:
EXPLAIN SELECT COUNT(*) FROM article WHERE id >25
另外一种思路: 可以先查询文章总数,减去小于等于25的数量 。仅仅提供思路,具体效果还是你具体情况,自己比较,择优选择。
EXPLAIN SELECT (SELECT COUNT(*) FROM article) - COUNT(*) FROM article WHERE id <=25
题外话:
如果需要区分不同颜色的商品数量时,可以如下做法:
seelct count(color = 'blue' OR NULL) as blue,COUNT(color = 'red' OR NULL) AS RED FROM items
6.GROUP BY和DISTINCT
它们的优化最有效的方法就是用索引来。
但是GROUP BY有时候用得不对,索引是会失效的。
比如:把两个单独的索引合并成一个组合索引,即把where条件字段的索引和group by的分组字段索引组合成一个。
解决方法:参考这篇 函数索引
7.limit分页
下面这条查询,非常常见。
select film_id,description from film order by title limit 50,5;
但是如果这个表很大的时候,那么这个50变成100654这样子的话,这里MySQL就要扫描100654+5条数据,然后丢弃100654条,仅仅去最后5条。
一种思路:
select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim USING(film_id);
该思路是通过 延迟关联 将大大提升查询效率,它让MySQL扫描尽可能少的页面。获取需要访问的记录后,再更加关联列会原表查询所需要的所有列。以上并不一定符合你,具体还需explain对比择优使用。
小结:
总体来说都是围绕着 尽量少全表扫描,尽量使用索引 进行优化。
最后往往是要自己在实际场景 多用explain分析 是否有更好的sql解决方案。
索引会失效的场景
1.隐式转换导致索引失效.
这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子:select * from test where tu_mdn=13333333333; 正确的例子:select * from test where tu_mdn='13333333333';
2. 对索引列进行运算导致索引失效
所指的对索引列进行运算 *包括(+,-, ,/,! 等)
错误的例子:select * from test where id-1=9; 正确的例子:select * from test where id=10;
3. 使用内部函数导致索引失效.
对于这样情况应当创建基于函数的索引.
// 错误的例子: select * from test where round(id)=10; //说明,此时id的索引已经不起作用了 //正确的例子:首先建立函数索引 create index test_id_fbi_idx on test(round(id)); //然后 select * from test where round(id)=10;
4. 不要将空的变量值直接与比较运算符(符号)比较。
如果变量可能为空,应使用 IS NULL 或 IS NOT NULL 进行比较,或者使用 ISNULL 函数。
5. 不要在 SQL 代码中使用双引号。
因为字符常量使用单引号。如果没有必要限定对象名称,可以使用(非 ANSI SQL 标准)括号将名称括起来。
6. 以下使用会使索引失效,应避免使用
a. 使用 <> 、not in 、not exist、!=
b. like "%_" 百分号在前(可采用在建立索引时用reverse(columnName)这种方法处理)
c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列,如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引。
d. 字符型字段为数字时在where条件里不添加引号.
e. 当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。
暂时统计到这么多,如果有更多的以后再补充。
MySQL的EXPLAIN的使用
EXPLAIN是用来分析SQL执行情况分析的
EXPLAIN 命令的输出内容大致如下:
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
各列的含义如下:
- id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
- select_type: SELECT 查询的类型.
- table: 查询的是哪个表
- partitions: 匹配的分区
- type: join 类型
- possible_keys: 此次查询中可能选用的索引
- key: 此次查询中确切使用到的索引.
- ref: 哪个字段或常数与 key 一起被使用
- rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
- filtered: 表示此查询条件所过滤的数据的百分比
- extra: 额外的信息
更详细的可以参考这篇【 性能优化神器 Explain 使用分析 】或者【 高性能MySQL 】
总结
查询优化目的就是为了快速得到结果,所以每当写完SQL应该思考以下几点:
- 是否需要全表查询以及返回的数据是否合理。
- 是否需要索引,索引是否合理。
- 是否有更好的解决办法。
最后
如果对 Java 、大数据感兴趣请长按二维码关注一波,我会努力带给你们价值。觉得对你哪怕有一丁点帮助的请帮忙点个赞或者转发哦。
关注公众号 【爱编码】 ,回复 2019 有相关资料哦。
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- 4 万字全面掌握数据库、数据仓库、数据集市、数据湖、数据中台
- Python3爬虫数据入数据库---把爬取到的数据存到数据库,带数据库去重功能
- Oracle数据库查询重复数据及删除重复数据方法
- sqlserver数据库获取数据库信息
- 从大数据到数据库
- node连接oracle数据库,更新数据后,数据库中不生效问题
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
在线进制转换器
各进制数互转换器
RGB HSV 转换
RGB HSV 互转工具