@[TOC]
mysql基本的增删改查(select,update,delete,insert)
mysql的左连接,右连接,内连接,全连接的区别(left join,right join,ininer join)
mysql的聚合函数(max,min,avg,sum)
mysql的分页及group by
mysql的子查询
一条sql的执行顺序是什么
sql优化
定位慢查询
联合查询效率较高,举例子来说明联合查询:内联inner join 、左联left outer join 、右联right outer join 、全联full outer join 的好处及用法。
联合查询效率较高,以下例子来说明联合查询(内联、左联、右联、全联)的好处:
T1表结构 | userid | username | password |
| 1 | jack | jackpwd |
| 2 | owen | owenpwd |
| |||
T2表结构 | userid | jifen | dengji |
| 1 | 20 | 3 |
| 3 | 50 | 6 |
第一:内联(inner join)。
如果想把用户信息、积分、等级都列出来,那么一般会这样写:select * from T1 ,T3 where T1.userid = T3.userid(其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid )。
把两个表中都存在userid的行拼成一行(即内联),但后者的效率会比前者高很多,建议用后者(内联)的写法。
SQL语句:select * from T1 inner join T2 on T1.userid=T2.userid
运行结果 | T1.userid | username | password | T2.userid | jifen | dengji |
| 1 | jack | jackpwd | 1 | 20 | 3 |
第二:左联(left outer join)。
显示左表T1中的所有行,并把右表T2中符合条件加到左表T1中;右表T2中不符合条件,就不用加入结果表中,并且NULL表示。
SQL语句:select * from T1 left outer join T2 on T1.userid=T2.userid
运行结果 | T1.userid | username | password | T2.userid | jifen | dengji |
| 1 | jack | jackpwd | 1 | 20 | 3 |
| 2 | owen | owenpwd | NULL | NULL | NULL |
第三:右联(right outer join)。
显示右表T2中的所有行,并把左表T1中符合条件加到右表T2中;左表T1中不符合条件,就不用加入结果表中,并且NULL表示。
SQL语句:select * from T1 right outer join T2 on T1.userid=T2.userid
运行结果 | T1.userid | username | password | T2.userid | jifen | dengji |
| 1 | jack | jackpwd | 1 | 20 | 3 |
| NULL | NULL | NULL | 3 | 50 | 6 |
第四:全联(full outer join)。
显示左表T1、右表T2两边中的所有行,即把左联结果表+右联结果表组合在一起,然后过滤掉重复的。
SQL语句:select * from T1 full outer join T2 on T1.userid=T2.userid
运行结果 | T1.userid | username | password | T2.userid | jifen | dengji |
| 1 | jack | jackpwd | 1 | 20 | 3 |
| 2 | owen | owenpwd | NULL | NULL | NULL |
| NULL | NULL | NULL | 3 | 50 | 6 |
总结,关于联合查询,效率的确比较高,4种联合方式如果可以灵活使用,基本上复杂的语句结构也会简单起来。这4种方式是:1)Inner join 2)left outer join 3)right outer join 4)full outer join
①常用的聚合函数
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
②求总行数
–返回非NULL数据的总行数
select count(height) from students;
–返回总行数,包含null值记录
select count(*) from students;
③求最大值
–查询女生的编号最大值
select max(id) from students where gender = 2;
④求最小值
–查询未删除的学生最小编号
select min(id) from students where is_delete = 0;
⑤求和
–查询男生的总身高
select sum(height) from students where gender = 1;
–平均身高
select sum(height) / count(*) from students where gender = 1;
⑤求平均值
– 求男生的平均身高, 聚合函数不统计null值,平均身高有误
select avg(height) from students where gender = 1;
– 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;
说明:ifnull函数:表示判断指定字段的值是否为null,如果为空使用自己提供的值
-----聚合函数的特点
聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算,必须使用ifnull函数对null值做替换
--------小结-------------
count(col):表示求指定列的总行数
max(col):表示求指定列的最大值
min(col):表示求指定列的最小值
sum(col):表示求指定列的和
avg(col):表示求指定列的平均值
————————————————
limit分页:
select * from t (pageNumber-1)*pageSize,总之,前端只需要告诉我们需要第几页,每页显示多少条数据就行。
例如:select * from t limit 0,10,即表示:查询第一页的数据,每页显示10条
group by(具有去重功能):
给张这样的表:
用这样的sql查:
SELECT name FROM test GROUP BY name
结果展示是这样:
子查询:
IN子查询
比较运算符子查询
EXIST子查询
from型子查询(把内层的查询结果当成临时表,供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。)
where型子查询(把内层查询结果当作外层查询的比较条件)
sql优化:
1.使用子查询优化
select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and
id>=(select id from orders_history where type=8 limit 100000,1)
limit 100;
select * from orders_history where type=8 limit 100000,100;
4条语句的查询时间如下:
第1条语句:3674ms
第2条语句:1315ms
第3条语句:1327ms
第4条语句:3710ms
针对上面的查询需要注意:
比较第1条语句和第2条语句:使用 select id 代替 select * 速度增加了3倍
比较第2条语句和第3条语句:速度相差几十毫秒
比较第3条语句和第4条语句:得益于 select id 速度增加,第3条语句查询速度增加了3倍
这种方式相较于原始一般的查询方法,将会增快数倍。
① SQL语句及索引的优化
SQL语句的优化:
1、尽量避免使用子查询
2、避免函数索引
3、用IN来替换OR
另外,MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from table_name where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。
4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引,如果查询的是索引列则可以
5、读取适当的记录LIMIT M,N,而不要读多余的记录
select id,name
from table_name limit 866613, 20
使用上述sql语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。sql可以采用如下的写法:
select id,name from table_name
where id> 866612 limit 20
6、避免数据类型不一致
7、分组统计可以禁止排序sort,总和查询可以禁止排重用union all
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。
另外,如果排序字段没有用到索引,就尽量少排序;
8、避免随机取记录
9、禁止不必要的ORDER BY排序
10、批量INSERT插入
11、不要使用NOT等负向查询条件
你可以想象一下,对于一棵B+树,根节点是40,如果你的条件是等于20,就去左面查,你的条件等于50,就去右面查,但是你的条件是不等于66,索引应该咋办?还不是遍历一遍才知道。
12、尽量不用select *
SELECT *增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前者也需要经常更新。所以要求直接在select后面接上字段名。
13、区分in和exists
select * from 表A
where id in (select id from 表B)
上面sql语句相当于
select * from 表A
where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
14、优化Group By语句
如果对group by语句的结果没有排序要求,要在语句后面加 order by null(group 默认会去排序);
尽量让group by过程用上表的索引,确认方法是explain结果里没有Using temporary 和 Using filesort;
如果group by需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大tmp_table_size参数,来避免用到磁盘临时表;
如果数据量实在太大,使用SQL_BIG_RESULT这个提示,来告诉优化器直接使用排序算法(直接用磁盘临时表)得到group by的结果。
慢查询定位
一、简介
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
二、参数说明
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
long_query_time 查询超过多少秒才记录
三、设置步骤
1.查看慢查询相关参数
mysql> show variables like 'slow_query%'; +---------------------------+----------------------------------+ | Variable_name | Value | +---------------------------+----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /mysql/data/localhost-slow.log | +---------------------------+----------------------------------+ mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+
2.设置方法
方法一:全局变量设置
将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON';
设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';
查询超过1秒就记录
mysql> set global long_query_time=1;
方法二:配置文件设置
修改配置文件my.cnf,在[mysqld]下的下方加入
[mysqld] slow_query_log = ON slow_query_log_file = /usr/local/mysql/data/slow.log long_query_time = 1
3.重启MySQL服务
service mysqld restart
4.查看设置后的参数
mysql> show variables like 'slow_query%'; +---------------------+--------------------------------+ | Variable_name | Value | +---------------------+--------------------------------+ | slow_query_log | ON | | slow_query_log_file | /usr/local/mysql/data/slow.log | +---------------------+--------------------------------+ mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+
四、测试
1.执行一条慢查询SQL语句
mysql> select sleep(2);
2.查看是否生成慢查询日志
ls /usr/local/mysql/data/slow.log
如果日志存在,MySQL开启慢查询设置成功!