首页 > 数据库 >mysql语句优化心得

mysql语句优化心得

时间:2024-03-21 15:34:27浏览次数:32  
标签:语句 cc 心得 查询 project mysql where id select

一、常用SQL优化处理

1.1 like查询优化方案

like模糊查询形如'%AAA%'和'%AAA'将不会使用索引,但是业务上不可避免可能又需要使用到这种形式查询方式:

优化方案一:

使用覆盖索引,即查询出的列只是用索引就可以获取,而无须查询表记录,这样也走了索引;

优化方案二:

使用locate函数或者position函数代替like查询:
如table.field like '%AAA%'可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0

 

1.2 日期函数优化

原SQL1:

select d.id, d.project_id, b.project_name, sum(d.order_sum)as order_sum,
sum(d.order_rate)as order_rate, sum(d.unfinished)as unfinished, d.data_time
from data_workorder_num d
left join base_project b on b.project_id = d.project_id
where d.project_id in
#{proId}
and date_format(d.data_time, "%Y-%m-%d") between str_to_date( #{startDate}, "%Y-%m-%d" )
and str_to_date( #{endDate}, "%Y-%m-%d" )
group by d.project_id

 

优化后的SQL1:

select d.id, d.project_id, b.project_name, sum(d.order_sum)as order_sum,
sum(d.order_rate)as order_rate, sum(d.unfinished)as unfinished, d.data_time
from data_workorder_num d
left join base_project b on b.project_id = d.project_id
where d.project_id in
#{proId}
and d.data_time between concat(str_to_date( #{startDate}, "%Y-%m-%d" ),':00:00:00')
and concat(str_to_date( #{endDate}, "%Y-%m-%d" ),':00:00:00')
group by d.project_id

 

1.3 查询两表使用join on和使用子查询in的比较

不要轻易使用in子查询,由于in子查询总是以外层查询的table作为驱动表,所以如果想用in子查询的话,一定要将外层查询的结果集降下来,降低io次数,降低nested loop循环次数,即:永远用小结果集驱动大的结果集。

 

1.4 in和exists使用场景

1.4.1 in和exists

如果查询的两个表大小相当,那么用in和exists差别不大。 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in: 例如:表A(小表),表B(大表)

1:

select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引;

select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。

 

2:

select * from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;

select * from B where exists(select cc from A where cc=B.cc) 效率低,用到了A表上cc列的索引。

 

1.4.2 not in和not exists使用比较

如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论哪个表大,用not exists都比not in要快。

 

1.4.3 in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao'); 

select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。

 

 

1.5 left join的优化

用小表驱动大表,同时对关联的条件列建立相关的索引。

 

 

二、mysql常用关键字处理

2.1 password

password-->passwd

 

2.2 type

type-->前缀_type  //建议前缀可以是表名的部分

 

2.3 其他关键字与保留字

关键字、保留字--->前缀_关键字或保留字

 

三、分页

LIMIT高效的分页

3.1 传统分页

select * from table limit 10000,10

3.2 LIMIT原理:

(1) Limit 10000,10 ​ (2)偏移量越大则越慢

3.3 推荐分页

分页方式一:
select * from table where id>=23423 limit 11;
#10+1(每页10条)
select * from table where id>=23434 limit 11;

分页方式二:
select * from table where id>=(select id from table limit 10000,1) limit 10;

分页方式三:
select * from table INNER JOIN (select id from table limit 10000,10) using(id);

分页方式四:
a. 程序获取id: select id from table limit 10000,10;
b. select * from table where id in (123,456...);

3.4 案例2

1、普通写法

select  *  from t where sellerid=100 limit 100000,20 
普通limit M,N的翻页写法,往往在越往后翻页的过程中速度越慢,原因
mysql会读取表中的前M+N条数据,M越大,性能就越差。

2、优化化的写法

优化写法: 
select t1.* from t t1, (select id from t where sellerid=100 limit 100000,20) t2
where t1.id=t2.id;
优化后的翻页写法,先查询翻页中需要的N条数据的主键id,在根据主键id
回表查询所需要的N条数据,此过程中查询N条数据的主键ID在索引中完成

 

四、其他

标签:语句,cc,心得,查询,project,mysql,where,id,select
From: https://www.cnblogs.com/lkj371/p/18087500

相关文章

  • 5、控制语句的使用
    fromflaskimportFlask,render_templateapp=Flask(__name__)#定义类用于参数传递classUser:"""对于参数age是后续加上去的,因为前期已经对于类进行过实例化了,所以在增加参数时,最好给上一个默认值.不然之前的写法都要重新修改."""def__i......
  • Node+Vue毕设社区居家养老管理系统(程序+mysql+Express)
    本系统(程序+源码)带文档lw万字以上 文末可获取本课题的源码和程序系统程序文件列表系统的选题背景和意义选题背景:在人口老龄化日益加剧的今天,社区居家养老已成为解决老年人养老问题的重要方式。随着社会经济的发展和科技的进步,老年人对于养老服务的需求不再局限于基本的......
  • 【Mysql数据库基础01】去重、连接字符、模糊查询、通配符、检索表的结构信息
    去重、连接字符、模糊查询、通配符、检索表的结构信息1去重distinct2连接字符函数concat(str1,str2,...)3模糊查询3.1like包含3.2通配符3.3betweenand3.4in3.5isnull4安全等于<=>5检索表的结构信息desc6课后练习1去重distinct使用DISTINCT关......
  • MySQL InnoDB Instant schema 变更注意点
    在MySQL8.0.12中为DDL引入了新的算法,在修改表的定义的时候不会阻塞。第一个instant操作是在表的最后增加一个列,这是腾讯游戏团队贡献的。在8.0.29中,官方增加在任意位置增加或删除列的instant操作。本文要讲的是盲目使用该特性会带来的危害。 默认算法从MySQL8.0......
  • 通过Docker安装MySQL数据库
    1.安装Docker首先,确保你的系统上已经安装了Docker。如果还没有安装,可以访问Docker官网查看安装指南。对于大多数Linux发行版,可以使用以下命令安装Docker:sudoapt-getupdatesudoapt-getinstalldocker.io安装完成后,启动Docker服务并设置开机自启:sudosystemctlstart......
  • PHP 封装整理 insert语句
    废话不多说直接上代码publicfunctioninsert($table,$data,$keyName,$canRepeatAdd=true){$keyStr="";$paramStr="";$params=[];foreach($dataas$key=>$value){if(is_array......
  • mysql-存储引擎
    1.mysql的体系结构下面这张图值得注意的是不同的存储引擎的Index不一样,从5.5版本以后存储引擎默认的就是InnoDB了。2.存储引擎 --查询建表语句---默认存储引擎:InnoDBshowcreatetablecourse;--查询当前数据库支持的存储引擎showengines;--创建表my_myisam,......
  • MYSQL事务面试题记录
    1.什么是数据库事务数据库事务是数据库管理系统执行过程种的一个逻辑单位,由一个有限的数据库操作序列构成,这些操作要嘛全部执行,要嘛都不执行,是一个不可分割的工作单位。2.Mysql事务的四大特性是什么?原子性事务作为一个整体被执行,对数据库的操作要吗全部被执行,要吗都不执行,......
  • MySQL学习八:窗口函数(一)
    目录一、窗口函数1.窗口函数定义2.窗口函数语法3.演示表格一4.窗口的确定4.1例1:查询各班级总分4.2例2:查询各班级累计总分4.3分区子句(partitionby)4.4排序子句(orderby)4.5窗口子句(rows)4.6总体执行流程5.函数分类5.1排序类函数5.2聚合类函数5.3跨行类函数......
  • 结构化语句header nav aside main article section footer
    点击查看代码<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><metaname="viewport"content="width=device-width,initial-scale=1.0"><title>语义化结构结构化元素</ti......