Viewing: raw.githubusercontent.com

[ Web Proxy ]

Viewing: https://raw.githubusercontent.com/htGoSpace/fullstack-tutorial/master/notes/MySQL.md

- [](#) - [MySQL](#mysql) - [MySQL](#mysql) - [1. ](#1-) - [AUTOCOMMIT](#autocommit) - [2. ACID](#2-acid) - [1. Atomicity](#1-atomicity) - [2. Consistency](#2-consistency) - [3. Isolation](#3-isolation) - [4. Durability](#4-durability) - [3. ](#3-) - [1. (1NF)](#1--1nf) - [2. (2NF)](#2--2nf) - [3. (3NF)](#3--3nf) - [4. ](#4-) - [1. ](#1-) - [2. ](#2-) - [3. ](#3-) - [4. ](#4-) - [5. ](#5-) - [1. (Serializable)](#1--serializable) - [2. (Repeated Read)](#2--repeated-read) - [3. (Read Committed)](#3--read-committed) - [4. (Read Uncommitted)](#4--read-uncommitted) - [6. ](#6-) - [](#) - [1. MyISAM](#1-myisam) - [2. InnoDB](#2-innodb) - [3. CSV](#3-csv) - [4. Archive](#4-archive) - [5. Memory](#5-memory) - [6. Federated](#6-federated) - [](#) - [](#) - [MyISAMInnoDB](#myisaminnodb) - [ InnoDB ](#-innodb-) - [7. MySQL](#7-mysql) - [1. ](#1-) - [2. ](#2-) - [3. ](#3-) - [4. ](#4-) - [DATETIME](#datetime) - [TIMESTAMP](#timestamp) - [8. ](#8-) - [1. ](#1-) - [2. B Tree ](#2-b-tree-) - [B-Tree](#b-tree) - [B+Tree](#btree) - [](#) - [](#) - [3. ](#3-) - [B+Tree ](#btree-) - [](#) - [](#) - [R-Tree](#r-tree) - [4. ](#4-) - [5. ](#5-) - [6. ](#6-) - [7. ](#7-) - [8. ](#8-) - [9. B+B-](#9-bb-) - [10. ](#10-) - [1. ](#1-) - [2. ](#2-) - [3. ](#3-) - [4. ](#4-) - [5. ](#5-) - [6. ](#6-) - [11. ](#11-) - [12. ](#12-) - [13. ](#13-) - [14. Redis](#14-redis) - [15. Redis](#15-redis) - [16. ](#16-) - [1. ](#1-) - [](#) - [](#) - [2. ](#2-) - [](#) - [](#) - [](#) - [3. Sharding ](#3-sharding-) - [4. Sharding ](#4-sharding-) - [](#) - [JOIN](#join) - [ID ](#id-) - [17. ](#17-) - [](#) - [](#) - [18. ](#18-) - [1. Explain ](#1--explain-) - [2. ](#2-) - [1. ](#1-) - [2. ](#2-) - [3. ](#3-) - [1. ](#1-) - [2. ](#2-) - [19. ](#19-) - [1. ](#1-) - [2. ](#2-) - [3. ](#3-) - [4. ](#4-) - [5. ](#5-) - [6. ](#6-) - [7. ](#7-) - [MySQL](#mysql) - [1. ](#1-) - [1](#1) - [2](#2) - [3](#3) - [2. ](#2-) - [](#) # MySQL MySQL **MySQL** **MySQL** - MySQL - [CyC2018/Interview-Notebook](https://github.com/CyC2018/Interview-Notebook/blob/master/notes/MySQL.md) @CyC2018 - MySQL - MySQL - MySQL # MySQL ## MySQL

[MySQL.png]

MySQL Server[MySQL](http://zhaox.github.io/2016/06/24/mysql-architecture) ## 1.

[185b9c49-4c13-4241-a848-fbff85c03a64.png]

ACID Commit Rollback ### AUTOCOMMIT MySQL `START TRANSACTION` ## 2. ACID ### 1. Atomicity SQL ### 2. Consistency AB2000ABAB2000 ### 3. Isolation T1 T2 T1 T2 T1 T1 ### 4. Durability ACID - - - -

[a58e294a-615d-4ea0-9fbf-064a6daec4b2-1534474592177.png]

## 3. 1NF2NF (3NF - 1NF - 2NF [] - 3NF [] - BCNF-1NF[3NF] - 4NF - 5NF 1NF

[c2d343f7-604c-4856-9a3c-c71d6f67fecc.png]

### 1. (1NF) ### 2. (2NF) **** | Sno | Sname | Sdept | Mname | Cname | Grade | | ---- | ------ | ------ | ------ | ------ | ----- | | 1 | -1 | -1 | -1 | -1 | 90 | | 2 | -2 | -2 | -2 | -2 | 80 | | 2 | -2 | -2 | -2 | -1 | 100 | | 3 | -3 | -2 | -2 | -2 | 95 | {Sno, Cname} - Sno -> Sname, Sdept - Sdept -> Mname - Sno, Cname-> Grade Grade Sname, Sdept Mname **** -1 | Sno | Sname | Sdept | Mname | | ---- | ------ | ------ | ------ | | 1 | -1 | -1 | -1 | | 2 | -2 | -2 | -2 | | 3 | -3 | -2 | -2 | - Sno -> Sname, Sdept - Sdept -> Mname -2 | Sno | Cname | Grade | | ---- | ------ | ----- | | 1 | -1 | 90 | | 2 | -2 | 80 | | 2 | -1 | 100 | | 3 | -2 | 95 | - Sno, Cname -> Grade ### 3. (3NF) -1 - Sno -> Sdept -> Mname -11 | Sno | Sname | Sdept | | ---- | ------ | ------ | | 1 | -1 | -1 | | 2 | -2 | -2 | | 3 | -3 | -2 | -12 | Sdept | Mname | | ------ | ------ | | -1 | -1 | | -2 | -2 | ## 4. ### 1. T1 T2 T1 T2 T2 T1

[88ff46b3-028a-4dbb-a572-1f062b8b96d3.png]

### 2. **** ** ** **** Mary 1000, Mary 8000 () Mary 8000 Mary 1000 Mary8000 **** READ_COMMITTED **** T1 T2 T1 T2

[dd782132-d830-4c55-9884-cfac0a541b8e.png]

### 3. **12 ** **** 11Mary 1000 ```sql con1 = getConnection(); select salary from employee empId ="Mary"; ``` 22 Mary 2000. ```sql con2 = getConnection(); update employee set salary = 2000; con2.commit(); ``` 31Mary 2000 ```sql //con1 select salary from employee empId ="Mary"; ``` **** REPEATABLE_READ **** T2 T1 T2

[c8d18ca9-0b09-441a-9a0c-fb063630d708-1534474726485.png]

### 4. T1 Where T2 T1 T1 T2 ** T2 ** () 12 **** 100010 11 1000 10 ```sql con1 = getConnection(); Select * from employee where salary =1000; ``` 2 employee 1000 ```sql con2 = getConnection(); Insert into employee(empId,salary) values("Lili",1000); con2.commit(); ``` 1 1000 11 ```sql //con1 select * from employee where salary =1000; ``` **** SERIALIZABLE_READ **** T1 T2 T1

[72fe492e-f1cb-4cfc-92f8-412fb3ae6fec.png]

## 5. ### 1. (Serializable) (phantom read) ### 2. (Repeated Read) Select MVCC RR ### 3. (Read Committed) () SQL Server ### 4. (Read Uncommitted) **** - (Read committed ) | | | | | | -------- | ---- | ---------- | ------ | | | | | | | | | | | | | | | | | | | | | ## 6. MySQL MySQL ### MySQL > MySQL `MySQL 5.5` `MyISAM`, `MySQL 5.5` `InnoDB` ### 1. MyISAM MySQL 5.5 `5.0` `4G``5.0` `256TB` Myisam `.myd` `.myi``.frm` **** - - - Myisam - Myisam myisampack **** - - - - count ### 2. InnoDB MySQL 5.5 **** - InnoDB - ACID - Redo log Undo loglog - InnoDB - - **** - - ### 3. CSV **** - - `.csv` - `.csm` - `.frm` **CSV** - CSV - NULL - - **** - ### 4. Archive **** - zlib I/O - ARZ `a.arz``a.frm` - insert select delete update - ID **** - ### 5. Memory - HEAP - HASH HASH BTree - varchar(10) == char(10) - BLOG TEXT - Memory innodb - `max_heap_table_size` - Memory `16M` `max_heap_table_size` - - - **** Memory ### 6. Federated **** - MySQL - ** Federated** Federated ### **** - ibdata1 - optimize table - I/O - **** Innodbmysql5.6 **** - mysqldump - mysql Innodb - mysql mysql - ### **** - - - - **** **** Innodb ### MyISAMInnoDB **** - MyISAM InnoDB - MyISAM InnoDB - MyISAM InnoDB - MyISAM InnoDB - MyISAM InnoDB MyISAM - MyISAM MyISAM - InnoDB MyISAM alter table tablename type=innodb **** - MyISAM SELECT MyISAM - InnoDB ACID INSERT UPDATE InnoDB ### InnoDB [innodb | ](http://xiaorui.cc/2016/12/08/%E4%B8%BA%E4%BB%80%E4%B9%88%E4%B8%8D%E5%BB%BA%E8%AE%AEinnodb%E4%BD%BF%E7%94%A8%E4%BA%BF%E7%BA%A7%E5%A4%A7%E8%A1%A8/) ## 7. MySQL ### 1. | | | | | | | --------- | ---- | ---- | ----------------------------- | ----------------------------- | | | byte | bit | signed | signed | | TINYINT | 1 | 8 | -27 = -128 | 27-1 = 127 | | SMALLINT | 2 | 16 | | | | MEDIUMINT | 3 | 24 | | | | INT | 4 | 32 | -231 = -2147483648 | 231-1 = 2147483647 | | BIGINT | 8 | 64 | | | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 8, 16, 24, 32, 64 INT(11) ### 2. FLOAT DOUBLE DECIMAL CPU DECIMAl DECIMAL FLOATDOUBLE DECIMAL DECIMAL(18, 9) 18 9 9 ### 3. CHAR VARCHAR VARCHAR UPDATE MyISAM InnoDB VARCHAR CHAR ### 4. MySQL DATETIME TIMESTAMP #### DATETIME 1001 9999 8 MySQL DATATIME 2008-01-16 22:37:08 ANSI #### TIMESTAMP UNIX 1970 1 1 4 1970 2038 MySQL FROM_UNIXTIME() UNIX UNIX_TIMESTAMP() UNIX TIMESTAMP TIMESTAMP DATETIME ## 8. ### 1. 1. 2. 3. ### 2. B Tree #### B-Tree

[06976908-98ab-46e9-a632-f0c2760ec46c.png]

[key, data]B-Tree - B-Tree - key - key keyi keyi+1 null key keyi key keyi+1 data B-Tree B-Tree #### B+Tree

[7299afd2-9114-44e6-9d5e-4025d0b2a541.png]

B-Tree B+Tree - 2d 2d+1d - data key - ####

[061c88c1-572f-424f-b580-9cbce903a3fe.png]

B+Tree B+Tree #### B Tree **** h O(h)=O(logdN) d 2 B Tree h B Tree B+Tree B-Tree B+Tree data **** I/O I/O [MySQL ](http://blog.codinglabs.org/articles/theory-of-mysql-index.html) ### 3. | | | InnoDB | MyISAM | MEMORY | | ---------------------------- | ---------------------------------------------------- | ------ | ------ | ------ | | B (B-tree indexes) | ID
| | | | | R (R-tree indexes) | | | | | | (Hash indexes) | | | | | | (Full-text indexes) | | | | | #### B+Tree B+Tree MySQL B+Tree InnoDB B+Tree ******** data

[c28c6fbc-2bc1-47d9-9b2e-cf3d4034f877.jpg]

data

[7ab8ca28-2a41-4adf-9502-cc0a21e63b51.jpg]

#### InnoDB B+Tree B+Tree O(1) - - #### MyISAM MATCH AGAINST WHERE InnoDB MySQL 5.6.4 #### R-Tree MyISAM GIS ### 4. - - - - - - SQL ### 5. - - - - - ### 6. - - - ### 7. > - MySQL**** key1100select * from table_name where key>1 and key explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ``` [MySQL Explain ](https://segmentfault.com/a/1190000008131735) ### 2. #### 1. SELECT * WHERE LIMIT #### 2. ### 3. #### 1. ```sql DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH); ``` ```sql rows_affected = 0 do { rows_affected = do_query( "DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000") } while rows_affected > 0 ``` #### 2. JOIN - - - - - IN() MySQL ID ```sql SELECT * FROM tab JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=post.id WHERE tag.tag='mysql'; SELECT * FROM tag WHERE tag='mysql'; SELECT * FROM tag_post WHERE tag_id=1234; SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904); ``` ## 19. MySQL/InnoDB MySQL InnoDB **** ```mysql select version(); ``` **** MySQL MySQL5.6.4 ```mysql SHOW ENGINES ``` ### 1. Version version versionversion1versionversion **** 1 id,value,version ```mysql select id,value,version from TABLE where id=#{id} ``` 2value ```mysql update TABLE set value=2,version=version+1 where id=#{id} and version=#{version}; ``` ### 2. Java synchronized ************ mysql MySQL autocommit MySQL MySQL autocommit ```mysql set autocommit=0; # autocommit # 1. () begin; / begin work; / start transaction; # 2. select status from TABLE where id=1 for update; # 3. insert into TABLE (id,value) values (2,2); # 4. update TABLE set value=2 where id=1; # 5. commit;/commit work; ``` ### 3. ****read lock T A A ```mysql # begin; / begin work; / start transaction; SELECT * from TABLE where id = 1 lock in share mode; ``` id 1 ```mysql update TABLE set name="www.souyunku.com" where id =1; ``` `commit` ```mysql [SQL]update test_one set name="www.souyunku.com" where id =1; [Err] 1205 - Lock wait timeout exceeded; try restarting transaction ``` ```mysql update test_one set name="www.souyunku.com" where id =1 lock in share mode; [SQL]update test_one set name="www.souyunku.com" where id =1 lock in share mode; [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1 ``` `lock in share mode`MySQL `update,insert,delete` ### 4. exclusive lock writer lock**** **** 1 A X 1 A A A 1 A 1 A A `for update` ### 5. ********, ****SQL **** ```mysql # SELECT * from TABLE where id = "1" lock in share mode; ``` **** ```mysql select status from TABLE where id=1 for update; ``` id id 1 ### 6. innodb ,. **Innodb** Innodb InnoDB InnoDB InnoDB SQL ### 7. Deadlock **** 1. ```mysql show OPEN TABLES where In_use > 0; ``` 2. SUPER ```mysql show processlist ``` 3. idid ```mysql kill id ``` **** 1. ```mysql SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; ``` 2. ```mysql SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; ``` 3. ```mysql SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; ``` **** ```mysql kill ID ``` 1. 2. 3. 4. **** 1. 2. 3. 4. 5. - [Mysql - Java - SegmentFault ](https://segmentfault.com/a/1190000015219003#articleHeader0) - [ - - SegmentFault ](https://segmentfault.com/a/1190000015815061#articleHeader7) # MySQL ## 1. 2 1 10w 2 ### 1 MySQLRedisRedisRedisMySQLDB DBDBRedis ### 2 **DB** DB ### 3 **RedisMySQLRedis**DB DB0 - [ | ](https://mp.weixin.qq.com/s/waGRvyhab2z8b-BIw9bJeA) - [ - CSDN](https://blog.csdn.net/zhoudaxia/article/details/38067003) ## 2. 1 2 3cache - [](https://mp.weixin.qq.com/s/5JYtta9aMGcic7o_ejna-A) # - [MySQL -](http://www.jikexueyuan.com/course/1524.html) - [mysql-tutorial/3.5.md at master jaywcjlove/mysql-tutorial](https://github.com/jaywcjlove/mysql-tutorial/blob/master/chapter3/3.5.md) - [Mysql_ (-) ~-bilibili](https://www.bilibili.com/video/av17252271?from=search&seid=3701018912873961528) - [](https://tech.meituan.com/dianping_order_db_sharding.html) - [ | ](https://mp.weixin.qq.com/s/Lfy7ek-vArVBTaUYfl64Bg) - [](https://www.toutiao.com/a6545626478447428103/?tt_from=weixin&article_category=stock×tamp=1524029012&app=news_article&iid=26214166927&wxshare_count=1) - [Java-_ (-) ~-bilibili](https://www.bilibili.com/video/av20966672?from=search&seid=6900253656657206494) - [MySQL_ITPUB](http://blog.itpub.net/29254281/viewspace-1819422/) -

Web Proxy Viewer | New URL | Original Page