Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of MySQL optimization, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Recently, I decided to take a look at the stored procedures written by my former colleagues, looking for some places in the code that are not very well written, and trying to make changes to make these procedures run at a good speed. Here are some of the most common problems encountered.

I came across a SQL like this:

Select name, count (*) from (select name from table_1) a group by a.name

MySQL's execution plan is very unfriendly to the interpretation of this derived table, but it is intuitive that the execution of this SQL is particularly slow. Looking at this table, table_1 finds that the name field is indexed. Looking at this code, we can infer that the programmer's idea at the time was to have the database scan a smaller result set, because select * is a very bad habit. However, he should have overlooked a very important feature of MySQL is the index. The index of MySQL is a very interesting thing, and it's something I feel like playing with when I switch from Oracle. The fun part is that I can optimize group by. When I change this SQL to the following SQL:

Select name, count (*) from table_1 group by name

In this way, the execution speed of this section of SQL is very fast, and the extra column clearly shows using index, index coverage query, and speed leverage.

In fact, this kind of mistake should be often made by programmers, because programmers are super familiar with the code such as Java, but for SQL, basically they all learn SQL in college, trained with SQLServer, and basically did not conduct very in-depth research on the database. In fact, in each kind of database, the implementation plan of the same SQL is different, which is a role of a professional DBA in the enterprise.

Here is a troublesome mistake:

Select name, userid from table_1 where name = null

Whether it is MySQL or Oracle, the specification for writing this kind of SQL is where name is (not) null. The value of null is a headache for people (including programmers and DBA) in any database. My understanding of MySQL is not deep enough, but according to one of the "Oracle DBA Notes", the null of each data type in Oracle represents a different meaning.

The following experiment was done:

As you can see, whether it is "= null" or "null", the resulting value is actually uncertain, that is, null. Therefore, it must be written as is (not) null. The null value of Oracle is also described in detail in the book "Sword Breaking the Iceberg".

Summing up my recent work, I studied MySQL for less than half a year and found that this open source database is not an Internet database as I used to think. It is true that there is a big gap between this database and commercial databases such as Oracle,DB2 in the aspect of complex computing for OLAP, but this part has made great progress in the branch of MariaDB, and I believe that it will get better and better in the later version of MySQL. In fact, what interests me most about this database is not open source, because I really can't understand that long source code, and my C language level is college graduation level. This database interests me most (at least for now) is its index, its index and Oracle are very different, especially InnoDB table is organized by index, in a simple query, an index coverage query can be invincible, in group by and order by, if it is an index field, the efficiency will be quite high.

In fact, what I also want to say is that in a team, if a large number of stored procedures are involved, there must be a professional DBA staff involved. SQL is a standard that spans all relational databases, but each relational database has a different implementation of SQL, so the same piece of SQL is executed on different databases, the efficiency will be very different. On the other hand, SQL is very easy to write with the most accustomed and simplest way of thinking, such as searching for order information generated by American employees in an order table. SQL may be like this:

Select * from orders t1where t1.employee_id in (select employee_id from employee T2 where t2.nation = 'USA')

If it is a commercial database such as Oracle, the execution efficiency of this SQL may be better, but it should not be as efficient as using exists's SQL. But when this SQL is executed in MySQL, the efficiency is very low, because many people know that the subquery efficiency of MySQL is really unflattering. This code will be changed to a related subquery, and as the amount of data increases, the execution time will become longer and longer. If this code is changed to the following SQL, the effect will be better:

Select T1. * from orders t1inner join employee T2 on t1.employee_id = t2.employee_idwhere t2.nation = 'USA'

If there is an index on the table, the execution is extremely fast.

To write SQL, you should first study the principle of this database, and then write it carefully.

Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL Optimization" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.