文档章节

MySQL—SQL优化

w
 waterme
发布于 2016/07/09 23:28
字数 1359
阅读 1
收藏 0
点赞 0
评论 0

学到了几个不错的sql优化方法:
1、技术优化
建立索引并确保合理有效使用上索引,分析执行计划,在基于代价执行场景下,通过执行计划explain不是高手很难分析出问题(慢慢学习吧~)。
2、业务优化
1)明确查询主表再left join查询辅助列,from MainTabel m,m中包括整个sql返回结果的全部行;
A表的ID取前10然后再取B表的name
select T.,B.name from (select from A order by a.id limit 10 ) T left join B on t.id = b.id
2)去掉不必要的列和表;
去掉列好说,去掉表别说不可能,见过不少,特别是用到数据量大的表进行关联取数据,没业务环境例子不那么好举。
select tn.id,tn.cn, t1.c1,t2.c2,… from t1,t2,…,tn where … and tn.id = ‘100’ and tn.cn = ‘cn’
考虑不使用tn表,查询列用到tn的两个列如果非得加在sql里可以写成select ‘100’ id, ‘cn’ cn, … from …
去掉不必要的表优化效果非常明显,前提是得较深入了解业务。
3)利用子查询限定数据范围;
select * from t1,t2,…,tn条件全部在where中
实际上用到t2表仅有一条记录,过多的条件关联未能确保对于记录少的表优先处理,可以考虑
select * from t1,(select * from t2 where t2条件),…,tn
4)子查询未加条件导致范围过大;
select t1.c1,t1.c2,t2.cnt
from t1,(select c3,max(c4) cnt from t1 group by c3) t2
where t1.c3 = t2.c3
and t1.id1 = ? and t1.id2 = ?
在t2子查询中group by的是整个t1表数据,实际只需要对id1和id2为特定值的数据进行分组,在子查询中加上条件。
5)创建临时表或使用with语法;
with w_tab as
(select from t1,t2,t3)
select from w_tab m, w_tab a
6)优化count()、min()、max()
min() :如果使用min()函数的数据列创建了索引,那么查询在O(1)的时间内完成,B-tree可以直接找最左端的数据。同理,max()直接找最右端的数据返回
count():没有where条件的count对于myisam存储引擎来说是很快的,因为在myisam引擎中维护了一个变量来存储行数
7)优化order by,使用索引扫描来做排序
order by 的列要满足最左索引,也可以跟where条件同时使用来满足最左索引
条件: **
1)最左索引,不能跳跃
2)顺序跟索引顺序一致
3)排序顺序要一致,不能一个升序一个降序**
示例:
CREATE TABLE pro_subgroup (
pro_subgroup_id int(10) NOT NULL AUTO_INCREMENT
pro_subgroup_name varchar(50) NOT NULL DEFAULT ”
pro_group_id int(10) NOT NULL DEFAULT ‘0’
PRIMARY KEY (pro_subgroup_id),
KEY name_id (pro_subgroup_id,pro_subgroup_name,pro_group_id)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
EXPLAIN SELECT * FROM pro_subgroup ps
ORDER BY ps.pro_subgroup_id DESC, ps.pro_group_id DESC
Using index; Using filesort
跳跃未使用索引
8)使用覆盖索引。覆盖索引就是查询的列是全部的索引或部分索引列。使用覆盖索引的好处就是不需要回表再取其他列数据,直接使用索引查询就能返回结果,查询效率会提高很多。
对于innodb存储引擎而言,主键索引是聚集索引,聚集索引在叶子结点上保存了行的其他信息。二级索引在叶子结点上保存了行了主键值。如果在innodb上不使用覆盖索引,那么在需要先遍历二级索引,找到相应的主键值,然后再从主键查找其他列的信息。而使用覆盖索引就可以直接返回索引查询到的信息。
对于myisam来说,myisam的索引文件时存在内存中的,数据文件时放在磁盘中的。如果使用覆盖索引相当于是只访问内存数据,不用去磁盘查找。所以效率也是相当好的。
查询全部的索引列,可以使用覆盖索引
Key(name)
EXPLAIN SELECT NAME FROM user_admin
这里写图片描述
注意使用覆盖索引时,查询的列必须在一个索引集里面。比如单独给name和password创建了两个索引。此时select name,password from table是无法使用覆盖索引的
查询列不在一个索引集里,无法使用覆盖索引
Key name(name)
Key password (‘password’)
EXPLAIN SELECT NAME,PASSWORD FROM user_admin
这里写图片描述
查询列为索引的一部分,可以使用覆盖索引
Key name_password (name,password)
EXPLAIN SELECT NAME FROM user_admin
这里写图片描述
查询全部的列
EXPLAIN SELECT * FROM user_admin WHERE NAME=’admin’ AND PASSWORD =”
这里写图片描述
Using index condition :必要的时候查看是否需要全表扫描,因为select * 还是要读到表数据的,但是使用了索引。
优化
EXPLAIN SELECT * FROM user_admin a JOIN (SELECT admin_id FROM user_admin WHERE NAME=’admin’ AND PASSWORD =”) b
ON a.admin_id = b.admin_id
这里写图片描述
没有优化
继续。。
修改key:
alter table modify key name_password_email id_name_password(‘admin_id’,’name’,’password’)
EXPLAIN SELECT * FROM user_admin a JOIN (SELECT admin_id FROM user_admin WHERE NAME=’admin’ AND PASSWORD =”) b
ON a.admin_id = b.admin_id
这里写图片描述
终于可以了,子查询使用覆盖索引,然后对查询出的结果再与主表进行连接查询其他列数据
如果表使用的引擎是innoDB,那么可以有效的利用主键进行覆盖索引查询。因为二级索引的叶子结点保存了主键值
EXPLAIN SELECT admin_id,NAME FROM user_admin WHERE NAME=”
这里写图片描述
9)优化分页limit
在(mysql——sql优化继上篇)中有详细的介绍。
不知道问什么一篇文章会限制字数。。。。。。(无语)

本文转载自:http://blog.csdn.net/feihongxueni/article/details/51189509

共有 人打赏支持
w
粉丝 0
博文 27
码字总数 0
作品 0
海淀
GROUP BY另类优化技巧

分享嘉宾:知数堂〖SQL开发优化班〗讲师郑松华,韩国Infobridge的SQL优化专家&7年SQL开发和调优经验&资深数据库工程师。 本次主题《GROUP BY另类优化技巧》,主要内容是从 GROUP BY、ORDER B...

iMySQL | 老叶茶馆
2017/04/15
0
0
Mysql 多表联合查询效率分析及优化

多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如: [sql]view plaincopyprint? SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM...

蓝狐乐队
2014/04/30
0
0
【数据库优化专题】MySQL视图优化(一)

本期数据库优化专题分享,为大家带来的是DBA+社群MySQL领域原创专家——李海翔所著的MySQL视图优化系列文章。以下是第一部分的内容,未完部分敬请关注后续更新。 专家简介 李海翔 网名:那海...

李海翔
2015/11/06
0
0
优化案例 | CASE WHEN进行SQL改写优化

导读 今天给大家分享一个通过SQL改写而独辟蹊径的SQL优化案例 待优化场景 发现SLOW QUERY LOG中有下面这样一条记录: 实话说,看到这个SQL我也忍不住想骂人啊,究竟是哪个脑残的XX狗设计的?...

iMySQL | 老叶茶馆
2017/04/14
0
0
mysql查询日志计数统计

分析统计MySQL general日志 找出查询次数最多的SQL 当我们需要优化MySQL查询时,第一想到的是开启慢日志,慢日志可以看到执行消耗超过一定时间的SQL语句和未使用索引的SQL。但如果我们想分析...

全村人希望
2017/06/21
0
0
10条SQL优化语句,让你的MySQL数据库跑得更快!

慢SQL消耗了70%~90%的数据库CPU资源; 1 不使用子查询 例:SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在MySQL5.5版本里,内部执行计划器是这样执行的...

kangjunfei
2017/12/14
0
0
Oracle、MySQL、云计算、大数据,最热话题好文都在这了!

在过去的九个月里, DBAplus社群始终聚焦各种数据库、云计算、大数据及中间件等热门话题,坚持每天为大家献上最新鲜、最内涵的技术好文!下面小编就把这些优质文章分门别类,给大家做个梳理,...

DBAplus社群
2016/06/29
0
0
如何高效快速地优化MySQL、SQL语句(附源码)

作者介绍 韩锋,宜信技术研发中心数据库架构师。精通多种关系型数据库,曾任职于当当网、TOM在线等公司,曾任多家公司首席DBA、数据库架构师等职,多年一线数据库架构、设计、开发经验。著有...

Yomut
2016/10/10
135
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
[慢查优化]建索引时注意字段选择性 & 范围查询注意组合索引的字段顺序

写在前面的话: 之前曾说过“不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程”,但对于字段选择性差意味着什么,组合索引字段顺序意味着什么,要求每个人必须...

旁观者-郑昀
2013/09/22
0
1

没有更多内容

加载失败,请刷新页面

加载更多

下一页

elasticdump 备份数据导出导入

版本: elasticsearch 5.5.2 elasticdump 2.2 系统 CentOS7.3 因项目需求 从生产导出一份索引到测试 https://github.com/taskrabbit/elasticsearch-dump?utm_source=dbweekly&utm_medium=em......

雁南飞丶
3分钟前
0
0
saltstack配置目录管理

1.服务端配置 -接着编辑之前的 top.sls 文件 #vim /srv/salt/top.sls //修改为如下 base: 'slaver.test.com': - filedir -新建 filedir.sls 文件 # vim /srv/salt/filedir.sls file-dir: fi......

硅谷课堂
4分钟前
0
0
python日期时间

日期和时间 Python内建的datetime模块提供了datetime、date和time类型。datetime类型结合了date和time,是最常使用的: In [102]: from datetime import datetime, date, timeIn [103]:...

火力全開
11分钟前
0
0
你不了解的v-show

vue指令v-show的使用 1、判断谋个元素是否显示或隐藏 <el-button v-show="list.power == 1" @click="toUpload" class="toUpload" type="primary">去上传<i class="el-icon-upload el-icon--r......

不负好时光
12分钟前
0
0
网站从一万到亿级流量的全景描述

故事的开头 如果你一毕业参见工作就进入了大型互联网公司,那么恭喜你了,你将体验到大公司深厚的技术底蕴以及企业文化。 如果你进入到了一个初创的中小型企业,那么也恭喜你了,你将可能体会...

大大枣
12分钟前
0
0
Tomcat中JAVA JVM内存介绍、内存溢出及合理配置

一、Java JVM内存介绍 JVM管理两种类型的内存,堆和非堆。按照官方的说法:“Java 虚拟机具有一个堆,堆是运行时数据区域,所有类实例和数组的内存均从此处分配。堆是在 Java 虚拟机启动时创...

學無止境
13分钟前
0
0
centOS7.4+nginx 1.12.2负载均衡

centOS7.4+nginx 1.12.2负载均衡 2018年04月10日 09:24:51 阅读数:58 1:参数信息 三台 centOS7.4 A,B,C A作为主服务器,B C作为分流的服务器 都搭建 nginx 1.12.2 一:安装 nginx 1:下载...

linjin200
18分钟前
0
0
分布式之抉择分布式锁

前言: 目前网上大部分的基于zookpeer,和redis的分布式锁的文章都不够全面。要么就是特意避开集群的情况,要么就是考虑不全,读者看着还是一脸迷茫。坦白说,这种老题材,很难写出新创意,博...

Java大蜗牛
24分钟前
0
0
rm: cannot remove `xxx’: Operation not permitted

rm: cannot remove `xxx': Operation not permitted可以先用lsattr xxx查看文件的隐藏属性。如果看到-----a-------的情况,可以用chattr -a xxx去除a属性,然后再进行删除就可以了....

殘留回憶
24分钟前
0
0
oracle 如何查看当前用户的表空间名称

如何查询当前用户的表空间名称?因为oracle建立索引,需要知道当前用户的表空间,查找了一下资料 --查询语法-- select default_tablespace from dba_users where username='登录用户' 如,...

youfen
28分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部