Some suggestions for Mysql to write high-quality SQL statements: mysqlsql

Some suggestions for Mysql to write high-quality SQL statements: mysqlsql

CleverCode has written some inefficient SQL statements in actual work. These statements put a lot of pressure on the database. The most important manifestation is that SQL statements run slowly, and then they are optimized and tried gradually. Summarizes the writing of Some high-quality SQL statements. Here, we will share the CleverCode with you.

[Do not repost the original files posted by CleverCode on the csdn blog. Original address: http://blog.csdn.net/clevercode/article/details/46341147]

1. Recommendation 1: Avoid Column Operations as much as possible to avoid column operations, which will cause index failure.
1.1 before date calculation optimization:

select * from system_user where date(createtime) >= '2015-06-01'

After optimization:

select * from system_user where createtime >= '2015-06-01'

1.2 plus, minus, multiplication, before optimization:

select * from system_user where age + 10 >= 20

After optimization:

select * from system_user where age >= 10

2. Recommendation 2: design an index using an integer type. The index takes less bytes and is much faster than the string type index. Especially when creating primary key indexes and unique indexes. 1) We recommend that you replace char (8) with int at design date ). For example, integer: 20150603. 2) When designing an IP address, you can use bigint to convert the IP address to long integer storage.

3. Recommendation 3: When Using join to drive a large result set using a small result set, we should try to make a small result set drive a large result set, splits a complex join query into multiple queries. When multiple tables are joined, tables may be locked or blocked. If the large result set is large and locked, the statement will wait. This is also a common mistake for beginners! 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 < 200

After optimization:

select*from (select*from table_awhere id > 100) aleft join(select*from table_bwhere id < 200)bon a.id = b.idleft join table_con a.id = c.id

4. Recommendation 4: only list the fields to be queried. Only the fields to be queried are listed. Generally, * is used for query by beginners. In fact, this is not good. This will not significantly affect the speed. 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 5: Before using batch insert to save Interactive Optimization:

insert into system_user(username,passwd) values('test1','123456')insert 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 6: explain is often used to analyze SQL statements.


7. Recommendation 7: use profiling to analyze the time overhead of SQL statements. For details about how to use profiling to analyze slow SQL statements in Mysql, see http://blog.csdn.net/clevercode/article/details/46310435.


Copyright statement: 1) original works are from the "CleverCode blog". Do not reprint them. Otherwise, the copyright is held legally liable.
2) original address: http://blog.csdn.net/clevercode/article/details/46341147.
3) classification address (Mysql database summary): http://blog.csdn.net/clevercode/article/category/3262205 (blog continues to increase, follow the favorite)
4) Welcome to my blog for more highlights: http://blog.csdn.net/clevercode.