sql优化
sql优化
参考博客：
sql语句用大写
解析sql语句时，把小写的字母转换成大写的再执行
对查询进行优化，应尽量避免全表扫描，首先考虑在where及order by上建立索引。
应尽量避免在where子句中进行以下操作，以下操作将导致引擎放弃索引而进行全表扫描。
-
对字段进行null判断，如：
SELECT id FROM table WHERE num is null解决方法：
方法1：在创建表时，使用默认值（DEFAULT）约束；例如：将字符串设置为空字符串，将数字设置为0。然后查询该特定值
方法2：在添加记录时，将NULL转换为特定值，然后查询该特定值
-
使用!=或<>操作符；
-
使用or连接条件；
SELECT id FROM table1 WHERE num=10 OR num=20解决方法：
SELECT id FROM table1 WHERE num=10 UNIT ALL SELECT id FROM table1 WHERE num=20 -
使用in或not in；
SELECT id FROM table1 WHERE num in (1, 2, 3)解决方法：
对于连续数值，能用between就不用in：
SELECT id FROM table1 WHERE num BETWEEN 1 AND 3对于离散数值，可用union all：
SELECT id FROM table1 WHERE num=1 UNIT ALL SELECT id FROM table1 WHERE num=2 UNIT ALL SELECT id FROM table1 WHERE num=3 -
使用like；
SELECT id FROM table1 WHERE name like '%abc%'解决方法：
使用全文搜索，MySQL有该索引
-
等号左侧使用算术运算；
SELECT id FROM table1 WHERE num/2=100解决方案：
SELECT id FROM table1 WHERE num=100*2 -
对字段进行函数运算等；
SELECT id FROM table1 WHERE SUBSTRING(name,1,3)='abc' SELECT id FROM table1 WHERE DATADIFF(day,createdate,'2005-11-30')=0解决方案：
SELECT id FROM table1 WHERE name like 'abc%' SELECT id FROM table1 createdate>='2005-11-30' and createdate<'2005-12-1'
不要用一些没有意义的查询，如生成一个空表
SELECT col1, col2 into #t FROM table1 WHERE 1=0
解决方法：
CREATE table #t(...)
使用exists替代in，用not exists替代not in。not in 是低效的，因为它对子查询中的表执行了一个全表遍历，他执行了一个内部的排序和合并。select num from a where exists（select 1 from b where num=a.num）
SELECT num FROM table1 WHERE num in (SELECT num FROM table2)
解决方法：
SELECT num FROM table1 a WHERE exists(SELECT 1 FROM table2 num=a.num )
对只含数值信息的字段尽量使用数值型代替字符型，否则会降低查询和连接性能
引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型而言只需要比较一次
尽可能使用varchar代替char，节约存储空间，提高效率
变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些
尽量用具体字段代替*进行查询
在使用索引字段作为条件时，如果索引是复合索引，必须使用该索引的第一个字段作为条件才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序一致
当索引中有大量重复数据时，索引是无效的
当进行update或insert操作时，索引的存在会降低该操作的效率
尽量避免频繁创建或删除临时表，减少系统资源消耗
临时表并不是不可使用，适当地使用可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表
在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免产生大量log，提高效率
如果使用到了临时表，在存储过程的最后务必将所有的临时表显示的删除，先truncate table ，然后drop table，避免系统表长时间锁定
尽量避免使用游标，因为游标效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
对于小型数据集使用fast_forward游标要优于其他逐行处理方法，尤其是在必须引用几个表才能获取所需要的数据时
表名顺序。选择最有效率的表名顺序，from后面先跟大表，再跟小表，因为from子句中写在最后的表被优先处理，from后跟多个表的情况下，应该选择记录条数最少的表作为优先处理的表
where子句连接顺序。那些可以过滤最大数量记录的条件必须写在where子句的末尾
在所有的存储过程和触发器的开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。
高效删除重复记录
最高效的删除重复记录方法delete from emp a where a.rowid>(select min(b.rowid) from emp b where a.emp_no=b.emp_no);
使用truncate替代delete
当用delete删除表中记录时，回滚段rollback用来被存放可以被恢复的信息，如果你不执行commit，oracle会将数据恢复到删除之前的状态；当运行truncate时，回滚段不再存放任何可被恢复的信息，当运行truncate时，数据不再被恢复，此时很少的资源被调用，执行时间也会很短
尽量多使用commit
随着commit的多次使用，系统资源被释放，性能会提高；
用where子句替换having子句
having只会在检索出所有记录之后才对结果集进行过滤。on、where、having这三个都是删选条件的子句，on最先执行，where次之，hiving最后；on先把不符合条件的记录过滤才进行统计，它可以减少中间运算要处理的数据，on的使用仅限于多表连接；where也是过滤数据后才进行sum；hiving是在计算之后才启作用
使用表的别名
当sql语句中连接多个表时，请使用表的别名并用别名前缀识别每个column，这样可以减少sql解析时间，避免歧义
用>=替代>
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效
高效：SELECT * FROM EMP WHERE DEPTNO >=4
低效：SELECT * FROM EMP WHERE DEPTNO >3
注：前者DBMS将直接跳到第一个DEPTNO等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPTNO大于3的记录。
用union all替换union
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。
当使用join（inner join或left join）操作时，就应该是小表在前，大表在后。把重复关联键少的表放在join前面做关联可以提高join的效率
注：如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表
尽量使用表变量来代替临时表
如果表变量包含大量数据，请注意索引非常有限（只有主键索引）
尽量避免大事务操作，提高系统并发能力
字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效
指定查询的索引
-
use index(索引): 推荐使用指定的索引
SELECT * FROM table1 USE INDEX(索引A) -
ignore index(索引) : 忽略掉这个索引
SELECT * FROM table1 IGNORE INDEX(索引A) -
force index(索引): 强制使用该索引
SELECT * FROM table1 FORCE INDEX(索引A)
insert 优化
-
需要插入多条数据的时候 使用批量插入(多次插入需要频繁的建立连接，浪费资源)
-
多次插入数据时,采用手动提交事务
sql语句在执行的时候如下所示, 会自动开启事务和提交事务. 我们可以手动开启事务,然后执行多条sql后,在手动提交事务.减少资源浪费
start transaction; 执行sql; commit;
count 优化
速度:count(*)>count(1)>count(字段)
count(字段):遍历整张表 会把每一行的字段值取出来,然后返回
count(1): 便利整张表,但不取值,对于返回的数据,放入1进去.然后累加
count(*):inndb引擎,特意做了优化,不会取出值,直接服务层进行累加
创表时使用同一编码
explain关键字分析
explain是非常重要的关键字,要善于运用它. 通过explain我们可以获得以下信息：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
使用方法：explain + sql语句。可根据结果进行解析sql的问题所在
EXPLAIN SELECT * FROM table1
浙公网安备 33010602011771号