SQL statement Optimization

SQL statement Optimization

I have recently written some documents on mysql database optimization and guidance on SQL statement optimization. I would like to share with you!

The performance improvement obtained by optimizing database parameters only accounts for about 40% of the database application system performance improvement. The other 60% of the system performance improvement comes from the optimization of applications. Many optimization experts even believe that application optimization can improve system performance by 80%. Therefore, it is certain that optimization of the database system by optimizing the application can achieve greater benefits.

SQL statement optimization and database performance optimization can be divided into two aspects. The operations performed by the application on the database are ultimately performed by SQL statements on the database. Database performance tuning is a comprehensive solution that combines hardware, software, and data volume. This requires testers to perform performance tests and work with developers to perform performance tuning.

SQL statement Optimization

3.1 keyword Optimization

All keywords are capitalized. For example, SELECT, FORM, WHERE, AND, CREATE, TABLE, etc. For example, if you use mysql to export an SQL file, we can see that most of the keywords are in uppercase. For example:

Explanation: this is because, at the underlying layer of SQL processing, all SQL statements are converted in uppercase by default.

3.2 SQL statements cannot exist *

The * symbol cannot exist in all query SQL statements. That is, SELECT * FORM. This example shows how to query a department table. Incorrect syntax: SELECT * FROM tdepartment: SELECT idepartmentid, scompanycode, sdepartmentname, iparent1_mentid, sdeptposttype, sifdeleted, sleafnode, sdesc FROM tdepartment. Cause: * All fields are retrieved,

The efficiency of using the "*" sign is low, which is equivalent to the same for loop and foreach. With the "*" sign, the SQL statement queries the underlying layer by default.

How many public fields are queried in the repository and then retrieved one by one. If you do not use *, you do not need to look up the dictionary first.

3.3 COUNT (*)

The project cannot use the COUNT (*) SQL statement. Replace COUNT (*) with COUNT (1 ). This is not obvious when the data volume is small, but the effect is very obvious when the table has a large amount of data.

3.4 multi-purpose matching query and less like Query

Cause: the like query will discard the index directly.

3.5 primary key index usage

All primary keys of all tables are indexes. Primary Key query should be used whenever possible. For example, SELECT * FROM tusers order by dregistertime DESC is less efficient than SELECT * FROM tusers order by iuserid DESC. This is because all primary keys are indexed by default. The registration time is not an index field.

3.6 1st 2nd index Arrangement

Assume that indexes are created for the scompanycode and dregistertime fields in our user table. Scompanycode is the first index. Dregistertime is the second index. For query, the efficiency of SELECT * FROM tusers order by scompanycode and dregistertime DESC is higher than that of SELECT * FROM tusers order by dregistertime and scompanycode DESC. This is because the first index will be retrieved first.

3.7 do not set the default value for fields when creating a table

For example, 'sifaudited' varchar (2) default '0' comment' 0: not reviewed; 1: Reviewed '. When inserting data, the default value will be added to the bottom layer of the database to determine whether there is a value, and the default value will be assigned.

3.8 do not leave null values in the field

This is because the null value occupies a large data size. Null and Null generally take 4 to 8 bytes. For example, 'scompanycode' varchar (16) default NULL comment' company number (unique identification) '. In this case, we generally change the NULL value to 0, which you should understand.

3.9 multi-purpose subquery

The performance of a subquery is higher than that of a connection query. The performance of the subquery is higher than that of the left join, right join, and full join queries.

3.10 connection query performance is higher than loop Query

For Department queries, we generally query the root directory, and then query the sub-door cyclically until the query ends. Low performance. We should use connection query. Or write a function to query stored procedures.

4. Design Optimization

4.1 Log Module, new queue, when the number of logs reaches 100, 200, 500, we use batch insert n, reduce disk io times. This can prolong the life of the disk and significantly improve data insertion.

5. database engine usage

5.1 ENGINE = innodb

The Innodb database engine is an external key and the transaction has been optimized. We use the innodb engine to create all tables. This is incorrect. The purpose of each table should correspond to a different database engine.

5.2 ENGINE = MyISAM

The MyISAM type does not support advanced processing such as transaction processing. MyISAM tables emphasize performance. The execution speed is faster than that of InnoDB tables, but transactions are not supported. Binary data files of the MyISAM type can be migrated in different operating systems. That is, it can be copied directly from Windows to linux. This is the default type, which is based on the traditional ISAM type. ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes), which is the standard Method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice. This type of Donghai projects is mostly used. One of the most commonly used engines.

5.3 ENGINE = BDB

BDB: it can replace the transaction engine of InnoDB and supports COMMIT, ROLLBACK, and other transaction features.

5.4 ENGINE = Memory

Memory: stores all data in RAM and provides extremely fast access in environments where you need to quickly search for references and other similar data.

5.5 ENGINE = Merge

Merge: Allows MySQL DBAs or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. It is suitable for VLDB environments such as data warehousing.

5.6 ENGINE = Archive

Archive: provides a perfect solution for storing and retrieving a large number of rarely referenced historical, archived, or security audit information.

5.7 ENGINE = Federated

Federated: connects multiple separated MySQL servers to create a logical database from multiple physical servers. It is very suitable for distributed environments or data mart environments.

5.8 ENGINE = Cluster/NDB

Cluster/NDB: MySQL Cluster-based database engine, especially suitable for applications with high-performance search requirements. Such search requirements also require the highest normal working time and availability.

5.9 Other: Other storage engines include CSV (referencing files separated by commas as database tables), Blackhole (used to temporarily prohibit database application input ), and the Example engine (which can help you quickly create a custom plug-in storage engine ).

6. Table Field Design

6.1 type restrictions. Whether to delete a field, such as 'sifdeleted' varchar (2) default '0' comment' 0: normal; 1: delete', which is identified by the int (1) type, do not use varchar (2) to occupy more space.

6.2 for the field length limit, such as the mobile phone number 11 digits, we do not need to design more digits. The company number can be set to only eight digits. The user name is limited to 32 characters.

6.3 use less foreign key restrictions

We can use code restrictions. Such as cascade deletion, cascade addition, and modification. It is best not to design a foreign key, which is not good for new data.

6.4 Use less constraints, such as unique constraints.

Automatic growth of less than 6.5

The primary key of yuantong does not grow automatically, but uuid is used, which is automatically generated by java. Considering that our data tables have less data and are rarely used.

6.6 There is no need to create indexes for tables with less content. The index is a waste of space.

6.7 table partition usage

For log tables, we can use table partitions. After the table is partitioned, the query efficiency is greatly improved. By default, there are time partitions, size partitions, and type partitions.

6.8 restrict table content. For example, you can limit the number of log tables. When creating a table again. We use MAX_ROWS for restrictions.

7. Follow other table creation rules.

For example, three paradigms.

Now, let's take a look at my blog!

If you have any questions, please join the QQ group: 135430763 to learn together!

Click to download the document:
SQL statement Optimization

Select *
From PIXPatient
Where 1 = 2
Or exists (select 1
From DomainPatient ex
Where a. PIXPatientTID = ex. DomainPatientTID
And PatientBirthday in ('2017-01-01 ', '2017-01-09 ')
)
Or exists (select 1
From PersonName ex
Where a. PIXPatientTID = ex. DomainPatientTID
And (FamilyName = 'blood' or GivenName = 'bound ')
)
;

SQL statement Optimization

Select *
From PIXPatient
Where 1 = 2
Or exists (select 1
From DomainPatient ex
Where a. PIXPatientTID = ex. DomainPatientTID
And PatientBirthday in ('2017-01-01 ', '2017-01-09 ')
)
Or exists (select 1
From PersonName ex
Where a. PIXPatientTID = ex. DomainPatientTID
And (FamilyName = 'blood' or GivenName = 'bound ')
)
;