MySQL Technology Insider Notes

1.innodb for independent subqueries, the execution will be converted to the associated subquery and then executed, this performance is very poor, such as

SELECT * from Upload_files where file_time in (select Max (file_time) file_time from Upload_files Group by From_unixtime (fi Le_time, '%y%m ')); For more than 2000 data to execute 14 seconds to complete, obviously too slow;

Directly written within the associated query select *,from_unixtime (a.file_time, '%y%m ') TT from Upload_files a INNER join (select MAX (file_time) file_time From Upload_files GROUP by From_unixtime (File_time, '%y%m ')) b on A.file_time =b.file_time is much better, the execution time is only 0.34 seconds;

Directly to the associated query can also (more than one layer) Select *,from_unixtime (file_time, '%y%m ') TT from Upload_files A where exists (SELECT * FROM (select Max (f Ile_time) File_time from Upload_files Group by From_unixtime (File_time, '%y%m ')) b where a.file_time = B.file_time); This performance and internal The association is the same.
2. Check the order for each employee's maximum order date (both the employee number and the order Date Master and subquery must match):

Although you can get the right results, the performance is poor; using derived tables avoids too many comparison operations for subqueries and out-of-the-way queries, derived tables are:

This method improves performance a lot. If everyone wants to display only one piece of data, the SQL statement (need to find the largest order number) becomes

Derived tables have better performance than subqueries

But this doesn't have any effect on execution.

3.

Therefore, when a conditional column contains a null value, in must filter the null value in order to derive the same result as exists, such as SELECT * from Site_article_channel where article_id not in (10,12, Null

ARTICLE_ID no data exists for 10,12, but the value of not in (10,12,null) is false or unknown, so no results are obtained

4.

A 5.natural join can be understood as associating a column with the same name as two tables, without having to be specified;

The 6.straight join function is equivalent to join, which provides a way for the user to control the optimizer,

This situation applies only when the optimizer does not provide the correct optimization scheme when a multi-table association query is used, and the user can force the optimizer to correlate the table in the order specified by the user

7.self join is the table itself to connect itself, self does not appear

8.unoin (set operation, vertical operation), MySQL support Unoin all and unoin distinct two kinds of collection operation, the collection operation of the two table columns must be the same, if the data type is different, the database is implicitly converted, and the name of the result column is determined by the first vote , Unoin distinct is generally written as unoin, distinct will be omitted, so all in unoin all must be carried to differentiate.

9.pivoting: technology refers to a technique that converts rows into columns, andunpivoting can be seen as a reverse operation of pivoting, turning columns into rows

10.rollup is an aggregation operation based on dimensions in a data result set, which has the advantage of getting N group by at a time; the case of single-row execution of rollup
At this point the result is

Compared to the case of not adding rollup, there is one more summary row, which is necessary when you need to summarize the data.

Multi-column execution of the rollup case,
The results were as follows

The result is the same as

But the performance is much better, only need a table scan, and the latter need to do four of the table scan; It is important to note that rollup cannot be used with order by, but it is generally meaningless to use with the limit, and the other is to handle NULL, If the aggregated column contains a null value, it may be an error, and it is best to ifnull (list, replace value) processing.

11. Transactions (flat transactions, flat transactions with SavePoint, chain transactions, nested transactions, where flat transactions are most commonly used):

Transaction

The use of transactions for INSERT or update, delete, and so on, the operation completed one commit, than the situation of non-applicable transactions performance is better, because the transaction only write once the log, but not the use of the case will have multiple write log operations

InnoDB Storage Engine automatically commits the operation, sometimes this is not a good thing, for the transaction is turned on after the automatic function is disabled, such as the end of the transaction system will automatically open, for "small transaction, cannot have long running large transaction" This is not applicable on MySQL InnoDB.

12. The secondary index is generally much smaller than the clustered index (it does not contain all the columns), and if the query condition can hit the secondary index performance is generally better, you do not need to use a clustered index, known as the Overwrite index.

In some special cases, you can force the query to use an index by following the query statement with the Forces Index (index name), for example
；
For example
Force is different, he's mandatory.

13. Partitioning: Innodb,myisam and NDB three engines support partitioning, CSV, etc. do not support partitioning

Local partitioning: Data and related indexes are stored in a single partition

Global partitioning: Data is stored in each partition and the index is stored separately

Horizontal partitioning: partition by row, same row must be within one partition

Vertical partitioning: Partitioning by column, the same column must be within a single partition

Currently, MySQL only supports local partitioning, and only horizontal partitioning is not supported for vertical partitioning, and MySQL partitions are divided into four types.

• Range (data that belongs to a continuous interval of a condition into a partition),
• List (for discrete values),
• Hash (partition according to the value of the user-defined expression, the return value cannot be a negative number),
• Key (partition according to the hash function of the database)

Partition columns must be part of a table, regardless of the type of partition, if there are primary keys or unique indexes, and if there is no primary key or unique index, then any column can be a partition column.

Note: For a range type partition, if the query condition is a date, then all partitions will still be scanned if they are partitioned by year, and if you use
This partition is appropriate, so partitioning takes into account the actual usage scenario. The best query conditions are consistent with the partitioning criteria.

The list type partition is similar to the one that uses the values in (such as the hash value of the three-and-four values);

When inserting a value that does not conform to a partition, the range and list partition types are the same, they are error-able to insert, and if you insert multiple data at one time, there is a difference between the different engines:

INNODB will insert multiple inserts as a single transaction, encountering an error, a whole rollback, a different myisam, an insertion before the error, an inability to insert after the error, or, in fact, a difference in the nature of whether the transaction is supported.

mysql5.5 Previous versions, the partition's condition must be an integer value (if it is not required to convert to an integer value through a function); After 5.5, you can use integer values, time, and string fields as criteria.

Sub-partition: If you are creating a child partition, the number of sub-partitions on all partitions must be the same and the name cannot be duplicated.

Null value: MySQL treats null values as smaller than any non-null values, as well as partitions;

B + Tree can do a good job of large data table, do not need to help the partition, if the partition is unreasonable, it will also degrade performance, relatively speaking, partitioning technology is more suitable for OLAP business, rather than OLTP business.

In general, queries that meet zoning criteria will be fast, and query costs will multiply for non-conforming conditions.

MySQL Technology Insider Notes