Mysql NOTE _ MySQL

Mysql note myisam innoDB is a common storage engine for mysql.

MyISAM does not support transactions or foreign keys, but it has fast access speed and has no requirements on transaction integrity.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the MyISAM storage engine, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.

There are two types of innodb indexes: the first index and the second index. Some are also called clustered indexes and secondary indexes. The clustered index stores the records in the table. during query, you do not need to perform a back-to-table scan, and the index items are large. the location information of the secondary index requires a back-to-table scan, i/0 times will increase.

It is best to retrieve data from the index and reduce the number of returned tables. the discrete I/0,

MYISAM has no clustered index. Physical location of stored records

The OLTP (online transaction processing) name-based transaction mainly emphasizes transactions, such as (changes to bank deposits and user orders) for applications.

OLAP (online analytical processing) is mainly used as a data warehouse for decision-making and analysis.

Join algorithm:

The main idea of nested-loops join is to compare a data entry from the external table with that of the internal table, O (M * N ). When there is an index: the internal table only needs to compare the index height, which is similar to O (M * H)

The main idea of Block nested -- loops join is to improve the nested-loops join external table to get a certain amount of data to the buffer every time, such as 10, then, the 10 records are compared with the data in the internal table to reduce the number of scans on the internal table.

Hash join can only be = and! =, Cannot be partially compared (why ?, Hash refers to the hash of the entire string) the main idea is: put the data of the external table into the join buffer, and then hash, this stage

Is the build and probe phase, and the data hash and comparison are obtained from the internal table.

Basic tests:

Create TABLE myorder
(Id int not null auto_increment,
Userid int not null,
Orderdate date,
Comein int DEFAULT 0,
Comeout int DEFAULT 0,
PRIMARY key (id)
);

SELECT userid, orderdate, comein-comeout as rest
From myorder
Group by userid, orderdate;

Create index myorderindex on myorder (id, userid );

Explain SELECT userid, orderdate, comein-comeout as rest
From myorder
Group by userid, orderdate;