由于在参与的实际项目中发现当mysql表的数据量达到百万级时,普通SQL查询效率呈直线下降,而且如果where中的查询条件较多时,其查询速度简直无法容忍。因此如何提高sql语句查询效率,显得十分重要。以下是累积的比较广泛使用的22种SQL查询语句优化方法:
1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
5、下面的查询也将导致全表扫描:(俗话说后通配 ,走索引;前通配,走全表。所以不能前置百分号)
select id from t where name like ‘%c%’
若要提高效率,可以考虑全文检索。
6、not in 也要慎用,否则会导致全表扫描,php的代码,数组、参数等对变量的类型没有强制统一要求,因此in条件中既有字符串又有整数,也会导致索引失效
7、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
8、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
9、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
10、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使 用,并且应尽可能的让字段顺序与索引顺序相一致。
11、很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
12、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
13、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
14、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
15、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
16、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
17、避免频繁创建和删除临时表,以减少系统表资源的消耗。
18、尽量避免大事务操作,提高系统并发能力。
19、使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
20、索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
21、检查有无子查询,子查询会每次都扫描子查询的整表,尽量少使用子查询
22、检查关联每个表的每个条件都有无索引
相关推荐
以下是十个 SQL 语句优化技巧来提升 MYSQL 查询效率: 1. 优化 MySQL 查询缓存 MySQL 查询缓存可以启用高速查询缓存,让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时,如果...
本文主要探讨了如何通过优化查询和利用索引来显著提升MySQL查询速度。 首先,性能分析的关键在于定位耗时查询。MySQL提供了一个内置的慢查询日志功能,通过编辑`my.cnf`配置文件,将`slow_query_log`设置为"ON",并...
如果之后有相同的查询再次执行,MySQL可以直接从缓存中获取结果而无需重新执行查询,从而大大提高了查询速度。然而,需要注意的是,查询缓存的使用可能会受到某些因素的影响,例如缓存空间大小限制以及缓存失效策略...
7. **数据分区**: 对于非常大的表,可以考虑使用分区策略,如范围分区、列表分区等,以提高数据插入和查询速度。 8. **避免回滚段**: 如果数据导入允许,可以关闭自动提交,减少回滚段的使用,以减少写入开销。 9....
本资料"mysql查询速度优化从3万秒到0.001秒共14页.pdf"揭示了如何将一个原本耗时长达3万秒的查询优化到只需0.001秒,这是一个显著的性能提升,体现了深度的优化技术和策略。 首先,优化查询速度的关键在于理解SQL...
索引的引入是为了提升查询速度,但其效果并非在所有情况下都是一致的。索引的使用能否提升查询性能,取决于多个因素,比如查询模式、数据量大小、索引的选择和优化器的决策等。索引可以减少磁盘IO次数,因为索引结构...
- **优点**:减少了对磁盘的访问次数,提高了查询速度。 - **缺点**:增加了服务器内存的负担,对于频繁修改的数据表可能会导致大量的缓存更新操作,反而降低性能。 ### 二、MySQL 内部机制 #### 2.1 存储引擎 ...
通过执行计划和索引分析,可以帮助优化数据库性能,提升系统响应速度。 6. **远程访问与同步**:SQLYog支持多数据库连接,允许用户远程管理多个MySQL服务器。此外,它的数据库同步功能可以帮助用户比较和同步不同...
在分析测试结果时,我们需要关注几个关键点:是否随着查询范围扩大性能下降,不同时间类型和格式是否有显著性能差异,以及优化器是否能够有效地利用索引来提升查询速度。如果存在性能瓶颈,可能需要考虑调整索引策略...
总之,索引优化是提升MySQL查询性能的核心手段,需要结合业务需求和查询模式,进行细致的分析和调整。通过以上策略的实施,可以显著减少查询时间,提升数据库系统的整体效率。在实际工作中,不断学习和实践,掌握...
通过对本书的学习,你可以深入了解MySQL在实际工作中的应用,提升数据库管理、查询优化以及数据存储的能力。 首先,我们要理解MySQL的核心概念。MySQL是一种关系型数据库管理系统(RDBMS),它支持SQL语言,广泛...
在设计高效合理的MySQL查询语句时,关注查询性能至关重要,因为查询操作在数据库操作中占据了主要部分,而SELECT语句的执行成本最高。随着数据量的增加,全表扫描会导致查询时间显著增长,可能需要数十分钟甚至数...
- **提高查询速度**:通过对数据进行逻辑分组,查询可以直接定位到相关的分区,从而减少搜索范围。 - **简化数据管理**:每个分区都是独立的,可以单独进行备份或恢复操作,提高了管理的灵活性。 - **提高可维护性**...
MySQL 数据库优化是提高百万条数据查询速度的关键技术,尤其对于大数据量的表,优化查询策略至关重要。以下是一些核心的 MySQL 优化建议: 1. **建立索引**:索引能够极大地加速查询过程,特别是在 WHERE 和 ORDER ...
MySQL 5.6及以上版本支持InnoDB存储引擎的全文索引,它能有效提高模糊查询速度。不过,全文索引对短文本字段效果更好。 3. **使用SOUNDEX或MATCH...AGAINST**:SOUNDEX是一种英文单词的音近字匹配函数,MATCH......
通过阅读[美河学习在线eimhe.com]MySQL查询理解.pdf,你可以更详细地了解这些概念,并找到更多实用的案例来提升你的MySQL SQL查询技巧。记住,实践是最好的老师,不断尝试和优化你的查询,将帮助你在数据库管理领域...
相比之下,索引通过预先排序和组织数据,使得查询能够迅速定位到目标数据行,显著提升查询速度。 **2. 索引如何工作** 以一个无索引的`ad`表为例,若要查找特定公司的所有记录,数据库必须遍历每一行数据进行匹配...
### MySQL查询优化浅析 #### 重要概念:查询优化 查询优化是数据库管理系统(DBMS)中的关键组件之一,其核心目标在于寻找最有效的查询执行计划,以最小化资源消耗(如CPU时间、I/O操作)并加快数据检索速度。在...