Previous article was on 10 Ways To Destroy A SQL Database that sort of teaches you what mistakes many company might make on their database that will eventually lead to a database destroy. In this article, you will get to know 15 ways to optimize your SQL queries. Many ways are common to optimize a query while others are less obvious.
Indexes
Index your column is a common way to optimize your search result. Nonetheless, one must fully understand how does indexing work in each database in order to fully utilize indexes. On the other hand, useless and simply indexing without understanding how it work might just do the opposite.
Symbol Operator
Symbol operator such as >,<,=,!=, etc. are very helpful in our query. We can optimize some of our query with symbol operator provided the column is indexed. For example,
1 |
SELECT * FROM TABLE WHERE COLUMN > 16 |
Now, the above query is not optimized due to the fact that the DBMS will have to look for the value 16 THEN scan forward to value 16 and below. On the other hand, a optimized value will be
1 |
SELECT * FROM TABLE WHERE COLUMN >= 15 |
This way the DBMS might jump straight away to value 15 instead. It’s pretty much the same way how we find a value 15 (we scan through and target ONLY 15) compare to a value smaller than 16 (we have to determine whether the value is smaller than 16; additional operation).
Wildcard
In SQL, wildcard is provided for us with ‘%’ symbol. Using wildcard will definitely slow down your query especially for table that are really huge. We can optimize our query with wildcard by doing a postfix wildcard instead of pre or full wildcard.
1 |
#Full wildcard |
2 |
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%' ;
|
3 |
#Postfix wildcard |
4 |
SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%' ;
|
5 |
#Prefix wildcard |
6 |
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello' ;
|
That column must be indexed for such optimize to be applied.
P.S: Doing a full wildcard in a few million records table is equivalence to killing the database.
NOT Operator
Try to avoid NOT operator in SQL. It is much faster to search for an exact match (positive operator) such as using the LIKE, IN, EXIST or = symbol operator instead of a negative operator such as NOT LIKE, NOT IN, NOT EXIST or != symbol. Using a negative operator will cause the search to find every single row to identify that they are ALL not belong or exist within the table. On the other hand, using a positive operator just stop immediately once the result has been found. Imagine you have 1 million record in a table. That’s bad.
COUNT VS EXIST
Some of us might use COUNT operator to determine whether a particular data exist
1 |
SELECT COLUMN FROM TABLE WHERE COUNT (COLUMN) > 0
|
Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field ‘COLUMN’. The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.
Wildcard VS Substr
Most developer practiced Indexing. Hence, if a particular COLUMN has been indexed, it is best to use wildcard instead of substr.
1 |
#BAD |
2 |
SELECT * FROM TABLE WHERE substr ( COLUMN, 1, 1 ) = 'value' .
|
The above will substr every single row in order to seek for the single character ‘value’. On the other hand,
1 |
#BETTER |
2 |
SELECT * FROM TABLE WHERE COLUMN = 'value%' .
|
Wildcard query will run faster if the above query is searching for all rows that contain ‘value’ as the first character. Example,
1 |
#SEARCH FOR ALL ROWS WITH THE FIRST CHARACTER AS 'E'
|
2 |
SELECT * FROM TABLE WHERE COLUMN = 'E%' .
|
Index Unique Column
Some database such as MySQL search better with column that are unique and indexed. Hence, it is best to remember to index those columns that are unique. And if the column is truly unique, declare them as one. However, if that particular column was never used for searching purposes, it gives no reason to index that particular column although it is given unique.
Max and Min Operators
Max and Min operators look for the maximum or minimum value in a column. We can further optimize this by placing a indexing on that particular columnMisleading We can use Max or Min on columns that already established such Indexes. But if that particular column is frequently use, having an index should help speed up such searching and at the same time speed max and min operators. This makes searching for maximum or minimum value faster. Deliberate having an index just to speed up Max and Min is always not advisable. Its like sacrifice the whole forest for a merely a tree.
Data Types
Use the most efficient (smallest) data types possible. It is unnecessary and sometimes dangerous to provide a huge data type when a smaller one will be more than sufficient to optimize your structure. Example, using the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space. On the other hand, VARCHAR will be better than longtext to store an email or small details.
Primary Index
The primary column that is used for indexing should be made as short as possible. This makes identification of each row easy and efficient by the DBMS.
String indexing
It is unnecessary to index the whole string when a prefix or postfix of the string can be indexed instead. Especially if the prefix or postfix of the string provides a unique identifier for the string, it is advisable to perform such indexing. Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks.
Limit The Result
Another common way of optimizing your query is to minimize the number of row return. If a table have a few billion records and a search query without limitation will just break the database with a simple SQL query such as this.
1 |
SELECT * FROM TABLE |
Hence, don’t be lazy and try to limit the result turn which is both efficient and can help minimize the damage of an SQL injection attack.
1 |
SELECT * FROM TABLE WHERE 1 LIMIT 10 |
Use Default Value
If you are using MySQL, take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.
In Subquery
Some of us will use a subquery within the IN operator such as this.
1 |
SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE) |
Doing this is very expensive because SQL query will evaluate the outer query first before proceed with the inner query. Instead we can use this instead.
1 |
SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable WHERE dummytable.COLUMN = TABLE.COLUMN;
|
Using dummy table is better than using an IN operator to do a subquery. Alternative, an exist operator is also better.
Utilize Union instead of OR
Indexes lose their speed advantage when using them in OR-situations in MySQL at least. Hence, this will not be useful although indexes is being applied
1 |
SELECT * FROM TABLE WHERE COLUMN_A = 'value' OR COLUMN_B = 'value'
|
On the other hand, using Union such as this will utilize Indexes.
1 |
SELECT * FROM TABLE WHERE COLUMN_A = 'value'
|
2 |
UNION |
3 |
SELECT * FROM TABLE WHERE COLUMN_B = 'value'
|
Hence, run faster.
Summary
Definitely, these optimization tips doesn’t guarantee that your queries won’t become your system bottleneck. It will require much more benchmarking and profiling to further optimize your SQL queries. However, the above simple optimization can be utilize by anyone that might just help save some colleague rich bowl while you learn to write good queries. (its either you or your team leader/manager)
相关推荐
SQL数据库性能优化 转载,初学者可参考。
对优化SQL SERVER数据库和如何使用T-SQL语句来操作SQL SERVER数据库进行了系统的规化,由简单到深入的讲解以及附加示例代码给于参考。本资料属于原创,如要转载,请征求作者的许可!!!
因为 Transact-SQL 语句本身保持不变,仅参数值发生变化,所以 SQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。 说通俗一点就是:如果用 EXEC 执行一条动态 SQL 语句,由于每次传入的参数不一样,...
NULL 博文链接:https://myspace1916.iteye.com/blog/1441580
15、提供API整合接口,可整合主流论坛、博客等软件,轻松实现多个程序紧密结合。 可同时整合动网论坛、oblog等第三方软件,轻松实现会员一站通,帮助用户轻松实现“cms + 论坛 + 博客”的多功能门户方案。 16...
全书以问题为导向,精选了前端开发中的34个疑难问题,从分析问题的原因入手,逐步给出解决方案,并分析各种方案的优劣,最后针对每个问题总结出高效编程的最佳实践和各种性能优化的方法。全书共7章,内容从逻辑上...
方法重构:将多个方法的共同代码提炼出来,单独写在一个方法中,然后引入该方法即可 ———————————————— 版权声明:本文为CSDN博主「EP Fitwin」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上...
笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后,其运行速度有了明显地提高!下面我将从这三个方面分别进行总结
DBA相关的转载 初始化参数(sga)的调整 关于statspack的若干建议 logmnr在调优中的运用 如何对sql进行调整及优化
番禺人才网(本人转载,仅供学习) 适合做行业性人才网,也可以做地方性人才门户站,目前主要功能有: 0、个人在线添加求职简历 1、个在线上传个人相片 2、首页显示热点新闻(后台可以设置),HR宝店,人事代理,政策法规...
声明:未经允许,请勿转载 MyShop商城是一个在线购物平台,致力于提供便捷的购物体验。为了满足用户需求,商城需要一个可靠、高效的数据库系统来管理商品、用户和订单信息。数据库系统应具备性能、可靠性和扩展性,...
这些系统⼤部分采⽤了关系数据模型并且⽀持SQL语句查询, 但为了能够并⾏执⾏SQL的查询操作,系统中采⽤了两个关键技术:关系表的⽔平划分和SQL查询的分区执⾏。 ⽔平划分的主要思想就是根据某种策略将关系表中的...
这是最新MONyog MySQL Monitor and Advisor GA (Stable) 5.0.0-6 Full,带序列号,本人亲测可用。...这个软件将积极主动地监控数据库环境,并会就如何用户可以优化性能,加强安全或任何MySQL系统减少停机时间提供意见。
这是最新MONyog MySQL Monitor and Advisor Beta 4.8.0-1 Full,带序列号,本人亲测可用。...这个软件将积极主动地监控数据库环境,并会就如何用户可以优化性能,加强安全或任何MySQL系统减少停机时间提供意见。
这是最新MONyog MySQL Monitor and Advisor GA (Stable) 4.7.2-0 Full,带序列号,本人亲测可用。...这个软件将积极主动地监控数据库环境,并会就如何用户可以优化性能,加强安全或任何MySQL系统减少停机时间提供意见。
语言 <BR> 第十七课 SQL语言妙用 <BR> 第十八课 外部数据的使用 <BR> 第十九课 数据的优化和安全 <BR> 第二十课 容易忽略的工作 <P> 本网上转载的电子书籍纯粹是作为个人编程参考,不作为商业用途,建议你购买...
DotBBS是一个Asp.Net开源论坛,轻量、安全、易扩展。深受广大用户喜爱,包括 CSDN、华军软件园、中国站长站 等各大专业网站纷纷转载。官方支持:http://www.dotbbs.net/bbs , DotBBS包括Access版和Sql Server版,可以...
java 源码分析 简介 Mybatis 是一个持久层框架,它对 JDBC 进行了高级封装,使我们的代码中不会出现任何的 JDBC 代码,另外,它还通过 xml 或注解的方式将 ...本文为原创文章,转载请附上原文出处链接:
在ASP.NET中使用WINDOWS验证方式连接SQL SERVER数据库 改进ADO.Net数据库访问方式 ASP.NET 2.0 绑定高级技巧 简单实用的DataSet更新数据库的类+总结 [ADO.NET]由数据库触发器引发的问题 为ASP.NET封装的SQL数据库...
这是MONyog MySQL Monitor and Advisor GA (Stable) 5.1.2-1 Full,带序列号,本人亲测可用。...这个软件将积极主动地监控数据库环境,并会就如何用户可以优化性能,加强安全或任何MySQL系统减少停机时间提供意见。