MySQL Code Development considerations----Developing high-performance SQL

Preface

A service or a program, consisting of program + data. In this piece of data, the IO in the computer is much slower than the CPU, in order to reduce IO and reduce CPU operation. We think of the index the first time, but why the index is more efficient, because it reduces IO and does not have to traverse the entire table when querying.

A little knowledge of MySQL,

1) When creating a table, if a non-null field is guaranteed in the business, it is recommended to explicitly not NULL because NULL is required for a special designation in MySQL. Using NOT NULL fields is more space-saving. It's also good for the next index build.

2) The Count (*) and COUNT (ID) IDs represent a field. In MySQL, COUNT (*) will count Null in, and COUNT (ID) will not. If the statistics field contains null, the results of this two statistic are different.

3) using a function on the left side of the SQL statement equals the query to make the index unusable in that field. such as the length (str) function.

4) Indexes are also required to be stored in physical space, and often additions and deletions of the table does not fit too many indexes, because the maintenance of the index will be time consuming. A table builds up to 15 indexes. The smaller the length of the index, the better the index is ordered. If the query Max () and the like with the index, even the table does not have to query, fast to fly.

5) Null in MySQL does not participate in the comparison operation, name <> ' millet ' results are not included in the Name=null case. In cases where the business cannot guarantee that a field is NULL, you need to pay attention to the null pit when writing the code. Ensure that the data obtained is right and full, then consider the query rate problem

MySQL Code Development considerations----Developing high-performance SQL