Viewing: raw.githubusercontent.com

[ Web Proxy ]

Viewing: https://raw.githubusercontent.com/suckthegithub/Interview-Notebook/master/notes/MySQL.md

* [](#) * [InnoDB](#innodb) * [MyISAM](#myisam) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [B-Tree B+Tree ](#b-tree--b+tree-) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) * [](#) # ## InnoDB InnoDB MySQL InnoDB MVCC ## MyISAM MyISAM GIS MyISAM DELAY_KEY_WRITE MyISAM MyISAM MyISAM ## 1. InnoDB 2. InnoDB 3. MyISAM InnoDB 4. MyISAM InnoDB 5. MyISAM # ## TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 8, 16, 24, 32, 64 INT(11) ## FLOAT DOUBLE DECIMAL CPU DECIMAl DECIMAL FLOATDOUBLE DECIMAL DECIMAL(18, 9) 18 9 9 ## CHAR VARCHAR VARCHAR UPDATE MyISAM InnoDB VARCHAR CHAR ## MySQL DATATIME TIMESTAMP ### 1. DATATIME 1001 9999 8 MySQL DATATIME 2008-01-16 22:37:08 ANSI ### 2. TIMESTAMP UNIX 1970 1 1 4 1970 2038 MySQL FROM_UNIXTIME() UNIX UNIX_TIMESTAMP() UNIX TIMESTAMP TIMESTAMP DATETIME # ## ### 1. B+Tree

[c23957e9-a572-44f8-be15-f306c8b92722.jpg]

MySQL B-Tree B+Tree B+Tree MySQL B+Tree ### 2. MySQL Memory InnoDB B+Tree B+Tree ### 3. R-Tree MyISAM ### 4. MyISAM MATCH AGAINST WHERE ## - - - I/O I/O ## ### 1. actor_id ```sql SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; ``` ### 2. BLOBTEXT VARCHAR **** 1 ### 3. actor_id film_id ```sql SELECT film_id, actor_ id FROM sakila.film_actor WhERE actor_id = 1 AND film_id = 1; ``` ### 4. customer_id staff_id customer_id ```sql SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, COUNT(*) FROM payment; ``` ```html staff_id_selectivity: 0.0001 customer_id_selectivity: 0.0373 COUNT(*): 16049 ``` ### 5.

[e800b001-7779-495b-8459-d33a7440d7b8.jpg]

InnoDB B-Tree **** 1. I/O 2. B-Tree **** 1. I/O 2. 3. 4. 5. ### 6. **** 1. 2. MyISAM 3. InnoDB ## B-Tree B+Tree ### 1. B-Tree

[5ed71283-a070-4b21-85ae-f2cbfd6ba6e1.jpg]

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

[63cd5b50-d6d8-4df6-8912-ef4a1dd5ba13.jpg]

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

[1ee5f0a5-b8df-43b9-95ab-c516c54ec797.jpg]

B+Tree B+Tree ### 4. B-Tree B+Tree B-/+Tree 4k I/O I/O B-Tree h-1 I/O O(h)=O(logdN) d 100 h 3h B-Tree B+Tree d B+Tree data # ### Explain SQL - select_type : - key : - rows : ### SELECT * ### LIMIT Explain rows ```sql SELECT * FROM sakila.film_actor WHERE film_id = 1; ``` ### DELETE INSERT ```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 ``` # ## payDB userDB ## id ## ## ### 1. ### 2. ### 3. # ### - ### IP IP MySQL IP MySQL IP MySQL ### ### # - MySQL - [How Sharding Works](https://medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6) - [MySQL ](http://blog.codinglabs.org/articles/theory-of-mysql-index.html) - [20+ MySQL ](https://www.jfox.info/20-tiao-mysql-xing-nen-you-hua-de-zui-jia-jing-yan.html) - [mysql](https://www.i3geek.com/archives/1108)

Web Proxy Viewer | New URL | Original Page