MySQL High performance

First chapter:
Shared lock (read lock), exclusive lock (write lock)

Query the storage engine used by the data table:
Show table status like ' table name ' \g

To convert a data table's storage engine:
ALTER TABLE name engine= engine name
As
ALTER TABLE MyTable ENGINE=INNODB;
---------
Chapter III
Char,varchar Brothers Type: binary, varbinary, used to hold binary strings, but bytes are saved, not characters

Blob and text save a large amount of data in binary and character format, respectively
Have their own family of data types:
Text:tinytext, Smalltext, Text,mediumtext, Longtext, (text equivalent to smalltext)
Blob:tinyblob, Smallblob, Blob, Mediumblob,longblob (BLOB equivalent to Smallblob)

MySQL cannot index these (Blob,text ...) The full length, also cannot be sorted using the index

Enum type, as:
CREATE TABLE Enum_table (
The e enum (' Yes ', ' no ') is not NULL,
....
);

Date and Time type
DateTime: Precision is seconds, formatted as YYMMDDHHMMSS, independent of time zone, default format is as follows:
2014-09-18 21:38:08
Time range is 1001-9,999 years

Timestamp: Maintains the number of seconds since midnight 1970-1-1 (Greenwich Mean Time), same as Unix timestamp, related to MySQL server, operating system, client time zone settings (recommended)

A range of queries that would lose the meaning of the index, such as
SELECT * from where id>100 ORDER by date ASC

Regularization (according to paradigm characteristics, sub-tables) and non-regularization (non-canonical)

Fourth Chapter
Mysql
Select T1.id from test.t1 inner join TEST.T2 using (ID) where t1.id > 500
==
where T1.id > T2.id > 500
The equivalent of the using (ID) is actually two tables all use the subsequent where condition, but must be the same field name of the two tables, confined to the MySQL database

Statistical color values
Select SUM (if (color= ' Blue ', 1, 0)) as blue,
SUM (if (color= ' Red '), 1, 0) as red from table name
==
Select count (color= ' blue ' or null) as Blue,count (color= ' red ' or null) as red from table name

Custom variables, but can only be numeric
TT (ID, name);
As
SET @id: = 1; Indicates starting from 1
Select Name, @id: = @id +1 as myID from TT limit 3;
That means ID = 2 starts with the next three data

Fifth Chapter

MySQL High performance