SQL Optimization Tips

Most of the software we develop, the basic business process is: data collection → data stored in the database → according to business needs to query the corresponding data → data processing → to the front desk display. Analyzing the whole process, we can find that most of the operation time consumption of the software is spent on the database related IO operation. So optimizing our SQL statements can improve the responsiveness of the software and bring about a better user experience.

Before you begin to introduce SQL optimization techniques, first promote a database management artifact Navicat, official website:Https://www.navicat.com.cn/whatisnavicat

Navicat is a fast, reliable, and comprehensive database management tool designed to simplify database management and reduce administrative costs. Navicat's intuitive graphical user interface provides an easy way to manage, design and manipulate data for MySQL, MariaDB, SQL Server, Oracle, PostgreSQL, and SQLite.

Before encountering Navicat, the common database management tools for developing software are:

(1) Mysql

PhpMyAdmin, official website:https://www.phpmyadmin.net/

MySQL Workbench, official website:http://dev.mysql.com/downloads/workbench/

(2) Orace

PL/SQL Developer, official website:https://www.plsqldev.com/

PL/SQL Developer is an integrated development environment developed by Allround Automations Corporation, which specializes in the development of program units for Oracle database storage.

(3) SQL Server

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, and developing all of the components of SQL Server. SQL Server Management Studio combines a number of graphical tools and a rich scripting editor to enable developers and administrators of all skill levels to access SQL server.

Front kan a lot of nonsense, to the point, formally into the topic: SQL Optimization skills.

1. Query Index optimization:

The SQL used in the ①-⑤ test is based on the Oracle database.

① queries that the year is 2015 of all rows: When a table field is placed in a function to execute a query, the index will not work.

CREATE INDEXTb1_idx ontb1 (date_column);SELECTText_column1, Date_column fromtb1WHEREDate_column>=To_date ('2015-01-01','YYYY-MM-DD') andDate_column<To_date ('2016-01-01','YYYY-MM-DD');

② query for a row of data from the most recent date:

CREATE INDEXTb1_idx ontb1 (A, date_column);SELECT *   from     (         SELECTID, text_column1, date_column fromtb1WHEREA=: AORDER  byDate_columnDESC     )WHERERowNum< = 1;

This SQL statement will be executed according to the indexed Top-n query, and its efficiency is equivalent to the index UNIQUE scan.

③ Two query statements, through a common column query:

CREATE INDEX  on Tb1 (A, b); SELECT ID, a, b    from tb1 WHERE = : a    and = : b; SELECT ID, a, b    from tb1 WHERE =: b;

The established index can only be used for the first query, and the second SQL cannot use the index to improve efficiency.

④ Query A string:

CREATE INDEX  on tb1 (text_column1); SELECT ID, text_column1    from tb1 WHERE  like ' %termstr% ';

if the query character of like corresponds to the beginning of a wildcard, the index will not function. Nor is there an easy way to optimize this SQL.

⑤ Query the number of records under a condition:

CREATE INDEXTb1_idx ontb1 (A, date_column); SELECTDate_column,Count(*)  fromtb1WHEREA=: AGROUP  byDate_column; SELECTDate_column,Count(*)  fromtb1WHEREA=: A andB=: bGROUP  byDate_column;

The above two query statements, the first one may be detected thousands of or tens of thousands of records, and the second statement because a condition may be found only a few or dozens of records, you may think the second statement is more efficient. In fact, the first statement is executed more efficiently than the other way around. Because the index overrides all query fields in the first statement, the B condition in the second SQL is not indexed.

2. Paging Performance optimization:

the SQL used in the following tests is based on the MySQL database.    

①efficient calculation of the number of rows:

If the engine used is MyISAM, you can directly execute count (*) to get the number of rows. Similarly, the number of rows in the heap table is also stored in the table's meta information. However, if the engine is InnoDB, the situation can be complicated because InnoDB does not save the table's exact number of rows. The following statement is executed when the number of rows can be cached and then periodically updated through a daemon or when the user's actions cause the cache to fail:

SELECT COUNT (*)    from Test  Use INDEX (PRIMARY);

One of my test examples:

when offset (page offset) is large, it looks like this:

SELECT Vendorcode, VendorName    from  10000000,

A large paging offset increases the data used, and MySQL loads a large amount of data that will eventually not be used into memory. Even if we assume that most Web site users only have access to the first few pages of data, a small number of large page-offset requests can be harmful to the system as a whole. Facebook is aware of this, but instead of optimizing the database to handle more requests per second, Facebook is focusing on a smaller variance in the response time of the request.

② Get records:

Sort by real-time (most recently released at the top, that is, the oldest of the IDs), to achieve a high-performance paging.

A more efficient way is based on the maximum ID to query. Query the next page of the statement below, you need to pass in the current page to show the last ID.

SELECT ID, Vendorcode, perioddate, Materialcode    from WHERE < 1000000 ORDER  by DESC   -

A statement that queries the previous page is similar, except that you need to pass in the first ID of the current page and reverse it.

SELECT ID, Vendorcode, perioddate, Materialcode     from WHERE > 1500000 ORDER  by DESC   -

the above query method is suitable for simple paging, that is, do not display a specific page navigation, only the "previous" and "next", such as the blog footer display "Previous page", "Next Page" button. But if it's hard to get a real page navigation, here's a look at another way.

If the records in the table are rarely deleted or modified, you can also store the page number for the record in the table and create an appropriate index on the column. In this way, when a new record is added, the following query needs to be executed to regenerate the corresponding page number.

SET p:=0;   UPDATE SET Page=ceil (p:=+1/ORDER by DESC;

of course, you can also add a table dedicated to paging, you can use a daemon to maintain.

UPDATE pagination T JOIN (   SELECT ID, ceil (p:=+1/  $perpage) page     from test   ORDER by ID) Con=  t.idSET= c.page;

now it's easy to get an element of any page:

SELECT *   from Test A JOIN  on a.ID=b.idWHERE page=$offset;

There are a lot of tricks in SQL optimization, and I'm just swim here, 108,000 miles worse than a senior DBA.

Here are some of the articles I recommend for SQL optimization:

(1) MySQL knowledge sharing site:http://ourmysql.com/archives/category/optimize

(2) SQL to develop a good habit is a fortune :http://www.cnblogs.com/MR_ke/archive/2011/05/29/2062085.html

(3) MySQL Query Statement execution process:Http://shanks.leanote.com/post/MySQL%E6%9F%A5%E8%AF%A2%E8%BF%87%E7%A8%8B

(4) MySQL Paging performance optimization guide :http://www.codeceo.com/article/mysql-page-performance.html

(5) 21 Best MySQL Performance optimizations:http://www.phpxs.com/post/5092/

(6) 100+ MySQL debugging and optimization tips:http://mp.weixin.qq.com/s?__biz=MzAwMDM2NzUxMg==&mid=2247484514&idx=1&sn= 2cb4246bbf991186eb08aeacd71b2893&scene=21#wechat_redirect

SQL Optimization Tips