MySql > Update MySql
MySQL logical architecture
First, the query flow of mysql is roughly as follows:
Mysql client establishes connection with mysql server through protocol, sends query statement, first checks query cache, if match, directly returns the result, otherwise carries on statement parsing, that is, before parsing query, sends query statement. The server first accesses the Query cache, which stores SELECT statements and the corresponding query result set. If a query result is already in the cache, the server will no longer parse, optimize, and execute the query. It simply returns the cached results to the user, which greatly improves the performance of the system.
Syntax parsers and preprocessing: First mysql parses SQL statements by keywords and generates a corresponding “parse tree”. The mysql parser validates and parses queries using mysql syntax rules; The preprocessor further checks that the parsed number is valid based on some mysql rules.
The query optimizer parses the tree when it is considered legitimate and converts it into an execution plan by the optimizer. A query can be executed in many ways, all returning the same result. The e optimizer’s job is to find the best execution plan.
However, mysql uses the BTREE index by default, and a general direction is that no matter what you do with SQL, at least for now, mysql only uses one index in a table at most.
Storage engines
Mysql storage engine show engines;Copy the code
# show variables like '%storage_engine%';Copy the code
2.1 Comparison between MyISAM and InnoDB
| Compare the item | MyISAM | InnoDB |
|---|---|---|
| A foreign key | Only indexes are cached, not real data | Caching not only indexes but also real data requires a lot of memory, and memory size has a decisive impact on performance |
| The transaction | Does not support | support |
| Line table lock | Table locks, which lock the entire table even when operating on a single record, are not suitable for high-concurrency operations | Row lock, which locks only one row and does not affect other rows.Suitable for highly concurrent operations |
| The cache | Only indexes are cached, not real data | Caching not only indexes but also real data requires a lot of memory, and memory size has a decisive impact on performance |
| concerns | performance | The transaction |
| The default installation | is | is |
| Use the default | no | is |
| Built-in system table for use | is | no |
3. Index optimization
3.1 Reasons for SQL performance decline
(1) Index failure
(2) Too many joins in associated query
(3) Server tuning and parameter setting
3.2 Loading sequence of SQL
3.2.1, handwritten
3.2.2, machine readable
3.3, seven Join
4. SQL preheating
4.1 create a table
CREATE TABLE `t_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, empno int not null, PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO T_dept (deptName,address) VALUES(' deptName ',' deptName '); INSERT INTO T_dept (deptName,address) VALUES(' gai ',' Shanghai '); INSERT INTO T_dept (deptName,address) VALUES(' deptName ',' deptName '); INSERT INTO T_dept (deptName,address) VALUES(' wudeng ',' wudeng '); INSERT INTO T_dept (deptName,address) VALUES(' deptName ',' deptName '); INSERT INTO T_dept (deptName,address) VALUES(' sdeptname ',' sdeptname '); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' empp ',90,1,100001); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' empp ',50,1,100002); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' 新 冠 ',24,1,100003); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' emp ',70,2,100004); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' Joe ',35,2,100005); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' emp ',70,3,100006); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' Chou Chou Chou ',20,3,100007); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' emp ',100,4,100008); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' emp ',25,5,100009); INSERT INTO T_emp (NAME,age,deptId,empno) VALUES(' emp ',18,null,100010);Copy the code4.2. Warm up the topic
SELECT * FROM t_dept INNER JOIN t_emp ON t_dept. 'id' = t_emp. 'deptId'; SELECT * FROM t_emp LEFT JOIN t_dept ON t_emp. 'deptId' =t_dept. 'id'; SELECT * FROM t_emp LEFT JOIN t_dept ON SELECT * FROM t_emp LEFT JOIN t_dept ON T_emp. 'deptId' =t_dept. 'id' WHERE t_emp. 'deptId' IS NULL SELECT * FROM T_dept LEFT JOIN t_emp ON T_deptid 'WHERE t_deptid.' deptId 'IS NULL #left JOIN + union + right JOIN SELECT * FROM t_emp A left JOIN t_dept B ON A.deptid = b.id union SELECT * FROM t_emp A RIGHT JOIN t_dept B ON a.deptid = b.id # SELECT * FROM t_emp LEFT JOIN t_emp B ON a.deptid = b.id t_dept ON t_emp.`deptId`=t_dept.`id` WHERE t_emp.`deptId` IS NULL UNION SELECT * FROM t_dept LEFT JOIN t_emp ON t_dept.`id`=t_emp.`deptId` WHERE t_emp.`deptId` IS NULLCopy the code