Shulou(Shulou.com)06/01 Report--

1. Force decimal to store decimals instead of float and double to prevent precision loss. If the stored data exceeds the range of decimal, it is recommended to split the data into integers and decimals and store them separately. This is also Ali's mandatory development specification.

2. Efficient paging

The essence of limit mdirection n is to execute limit msqun first, and then fetch n lines from the m line, so that when the limit page turns backwards, the m is larger, and the performance is lower. such as

Select * from A limit 100000d10, the performance of this sql statement is very poor, so it is recommended to change it to the following version:

Selec id,name,age from A where id > = (select id from A limit 100000Jing 1) limit 10

3. The row lock and deadlock detection mechanism of InnoDB will cause the database CPU to be full in a short time, resulting in almost no response of the whole database.

4. Summary of data packets:

1 grouping functions can only appear in select lists, having, order by clauses (not in where).

2 if where,group by, having and order by are included in the select statement at the same time, then their order is where,group by, having, order by.

3 if there are columns, expressions, and grouping functions in the selected columns, then one of these columns and expressions must appear in the group by clause, otherwise an error will occur.

For example, SELECT deptno, AVG (sal), MAX (sal) FROM emp GROUP by deptno HAVING AVG (sal) < 2000

Here deptno must appear in group by.

5. Sql sorting defaults to ascending order (from small to large), direct order by, and descending order to desc.

6. The subquery is more efficient than the associated query.

MySQL:

The underlying storage model of mysql's innodb is B+ tree, which uses the primary key as the clustering index and the inserted data as the leaf node. Through the primary key, the leaf node can be found quickly, thus the record can be quickly obtained. The primary key had better be self-increasing. Because the self-increasing primary key allows the inserted data to be inserted into the leaf nodes of the underlying B+ tree in the primary key order, because it is in order, this insertion hardly needs to move other existing data, so the insertion efficiency is very high. If the primary key is not self-increasing, then each time the value of the primary key is approximately random, it is possible to move a large amount of data to ensure the characteristics of the B+ tree, adding unnecessary overhead.

For mysql's join, it uses the Nested Loop Join algorithm, that is, to query in the latter table through the result set of the previous table query, for example, the result set of the former table is 100 pieces of data, and the latter table has 10W data, then you need to filter the final result set in the data set of 100W to 10W. Therefore, try to use the table of the small result set to do join with the large table, and at the same time build an index on the field of join. If you can't build the index, you need to set up a large enough join buffer size.

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.