Some suggestions for MySQL to write high-quality SQL statements

Clevercode also wrote some inefficient SQL statements in the actual work. These statements will put a lot of pressure on the database, the main manifestation is that SQL statements run slowly, and then gradually to optimize and try. Summarizes some of the high-quality SQL statement notation. Here Clevercode summarizes to share to everybody.

"Clevercode published in the CSDN blog original works, please do not reprint, original address: http://blog.csdn.net/clevercode/article/details/46341147 "

1 Recommendation one: Try to avoid calculations on columnstry to avoid operations on columns, which can cause indexing to fail.
1.1 Date Arithmeticbefore optimization:

SELECT * from System_user where date (createtime) >= ' 2015-06-01 '

after optimization:

SELECT * from System_user where createtime >= ' 2015-06-01 '

1.2 Add, subtract, multiply, dividebefore optimization:

SELECT * from System_user where age + >= 20

After optimization:

SELECT * from System_user where age >= 10

2 recommendation two: Design index with integral typeAn integer-designed index consumes fewer bytes and is much faster than a string index. Especially when creating a primary key index and a unique index. 1) When designing the date, it is recommended to replace char (8) with Int. For example, integral type: 20150603. 2) When designing IP, you can convert IP to long storage with bigint.

3 recommendation Three: Use small result sets to drive large result sets when joinwhen you use join, you should try to get a small result set to drive a large result set and split the complex join query into multiple queries. Because joins multiple tables, there may be locking and blocking of the table. If the large result set is very large and is locked, then the statement waits. This is also a mistake the novice often made! before optimization:

Select*from table_a aleft Join table_b bon a.id = b.idleft join Table_c con a.id = c.idwhere a.id > 100and b.id < 20 0

After optimization:

Select*from (select*from table_awhere ID >) aleft join (Select*from table_bwhere ID < $) bon a.id = B.idleft Joi n Table_con a.id = c.id

4 Recommendation Four: List only the fields that need to be queriedonly the fields that need to be queried are listed, and the novice usually queries are *, in fact, this is not good. This does not have a noticeable effect on speed, and the main consideration is to save memory. before optimization:

SELECT * from System_user where age > 10

After optimization:

Select Username,email from System_user where age > 10

5 recommendation Five: Use BULK insert to save interactivitybefore optimization:

Insert into System_user (USERNAME,PASSWD) VALUES (' test1 ', ' 123456 ') inserts into System_user (USERNAME,PASSWD) VALUES (' Test2 ', ' 123456 ') insert into System_user (USERNAME,PASSWD) VALUES (' Test3 ', ' 123456 ')

After optimization:

Insert into System_user (USERNAME,PASSWD) VALUES (' test1 ', ' 123456 '), (' Test2 ', ' 123456 '), (' Test3 ', ' 123456 ')

6 recommendation Six: How to use explain to parse SQL statements


7 recommendation Seven: Use profiling to analyze SQL statement time overheadUse of profiling please check out my other blog, "Why MySQL uses profiling to analyze slow SQL statements": http://blog.csdn.net/clevercode/article/details/46310835.


Copyright Notice:1) original works, from the "Clevercode blog", please do not reprint, otherwise hold the copyright legal responsibility.
2) Original address: http://blog.csdn.net/clevercode/article/details/46341147.
3) category address (MySQL database summary): http://blog.csdn.net/clevercode/article/category/3262205 (blog continues to increase, concern please bookmark)
4) Welcome everyone to pay attention to my blog more wonderful content: Http://blog.csdn.net/CleverCode.

Some suggestions for MySQL to write high-quality SQL statements