1,查询条件里有不等号
SELECT * FROM `t_user` WHERE `id` != 2 中,id 的索引将不会被使用。
2,查询条件里使用了函数
SELECT * FROM `t_user` WHERE DAY(`regTime`) = 6 中,regTime 的索引将不会被使用。
3,在JOIN操作中,主键和外键的数据类型不同
SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `t_user` AS `U` ON `U`.`id` = `C`.`userId` 中,如果 t_user 表的 id 字段和 t_user_city 表的 userId 字段数据类型不同,那么这两个字段的索引将不会被使用。
4,查询条件里使用比较操作符 LIKE 和 REGEXP,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
①SELECT * FROM `t_user` WHERE `name` LIKE ‘123%’ 和
②SELECT * FROM `t_user` WHERE `name` LIKE ‘%123’ 中,
① 查询能使用 name 的索引,而 ② 查询却不行。
使用索引查询出的记录数量超过全表记录的30%,MySQL 会遍历全表。
SELECT * FROM `t_user` WHERE `isNovice` = 1 中,t_user 表所有的记录都会被遍历,因为 isNovice 只有 0 和 1 这两个值,记录数各占 50%,设置 isNovice 为索引没有任何意义。
除了注意以上的几条规则以外,还有一些构造的技巧,也可以帮助你提升查询的效率。
1,尝试使用 IN 代替 OR
①SELECT * FROM `t_user` WHERE `id` = 2 OR `id` = 4 OR `id` = 6 OR `id` = 8 OR `id` = 10 和
②SELECT * FROM `t_user` WHERE `id` IN (2, 4, 6, 8, 10) 中,② 的效率明显比 ① 要高的多。
2,避免在查询条件中使用函数
①SELECT * FROM `t_user` WHERE DATE(`regTime`) = ‘2010-02-01’ 和
②SELECT * FROM `t_user` WHERE `regTime` > ‘2010-02-01’ AND `regTime` < ‘2010-02-02’ 中,② 的效率明显比 ① 要高的多。 3SELECT `userId`, SQRT(POW(`unitX`, 2) + POW(`unitY`, 2)) AS `distance` FROM `t_map_unit` WHERE `userId` = 2 中,计算距离的操作完全可以由程序来做,或者在需要的时候再用程序计算。为了图一时方便,让数据库来运算,是得不偿失的。
3,尽量避免使用联合查询
①SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `t_user` AS `U` ON `U`.`id` = `C`.`userId` 和 ②SELECT * FROM `t_user_city` 、③SELECT `name` FROM `t_user` WHERE `id` IN (……) ,我更建议使用后者,实际上通过增加简单的程序处理就可以实现,但是效率上区别确是很大的,特别是当 2 个表的记录都很多的时候。两个查询有时候比一个更快,这完全取决于查询语句的复杂度。
一、问题的提出
在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量的SQL语句,提高系统的可用性。
在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。
二、SQL语句编写注意问题
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。
1. IS NULL 与 IS NOT NULL
不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2. 联接列
对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个叫比尔. 克林顿(Bill Cliton)的职工。
下面是一个采用联接查询的SQL语句,
select * from employss
where
first_name||''||last_name ='Beill Cliton'
上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。
Select * from employee
where
first_name ='Beill' and last_name ='Cliton'
遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:
select * from employee
where
first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)
3. 带通配符(%)的like语句
同样以上面的例子来看这种情况。目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:
select * from employee where last_name like '%cliton%'
这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。在下面的查询中索引得到了使用:
select * from employee where last_name like 'c%'
4. Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。
相关推荐
第3课 查询优化技术理论与MySQL实践(一)------子查询的优化(一) 第4课 查询优化技术理论与MySQL实践(二)------子查询的优化(二) 从理论看,子查询包括的内容和范围,建立清晰的概念 从实践看,MySQL的子查询...
MySQL查询优化技术_索引
mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...
sql查询优化 提高MySQL数据库查询效率的几个技巧
描述 MySQL 查询优化器的工作原理。 MySQL 查询优化器主要为执行的查询决断最有效的路线
MySQL查询优化浅析
1、理解MYSQL的Query Optimizer 2、Query语句优化基本思路和原则 3、充分利用Explain 和 Profiling 4、合理设计并利用索引 5、order by、group by 和 DISTINCT优化 6、小结
本文档详细介绍了Mysql的查询优化性能的研究,对一些常用方法进行了优化改进~
InnoDB 查询优化实现分析 -- MySQL
针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!针对 MySQL 的查询优化,非常给力!
基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf...
本书主要介绍了MYSQL的查询缓存原理,还有MYSQL的内部机制
本文深入探讨了MySQL查询优化的多种策略和技巧,旨在帮助数据库管理员和开发人员提升MySQL数据库的性能。首先,介绍了使用EXPLAIN命令分析查询执行计划的重要性,以便识别潜在的性能瓶颈。接着,详细阐述了索引优化...
MySQL海量数据查询优化策略,方法。 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引...
mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql...
因此,了解如何优化MySQL查询性能至关重要。 本文通过详细的示例和说明,深入探讨了MySQL查询优化的重要性及其实现方法。我们介绍了查询优化技术的各个方面,包括索引、查询语句、表结构等基本优化方法以及高级优化...
MySQL查询优化系列讲座之查询优化器.pdf
详细介绍慢查询日志及示例演示,MySQL查询优化器介绍及特定SQL的查询优化等 1.获取有性能问题的SQL的三种方法 2.慢查询日志概述 3.慢查询日志实例 4.实时获取性能问题SQL 5.SQL的解析预处理及生成的执行计划 6.如何...
Mysql查询优化,查询优化器,子查询,分页查询1)在执行计划1中,哪张表是驱动表? 表的连接顺序是怎样的?每一步表的扫描类型是什么? 2)在执行计划2中,表的执行顺序是怎样的?每一步表的扫描类型是什么? 3)在...