一、數(shù)據(jù)庫(kù)引擎

選擇：MyISAM相對(duì)簡(jiǎn)單潭流，所以在效率上要優(yōu)于InnoDB竞惋。如果系統(tǒng)插入和查詢操作多，不需要事務(wù)和外鍵灰嫉，選擇MyISAM拆宛，如果需要頻繁的更新、刪除操作讼撒，或者需要事務(wù)浑厚、外鍵股耽、行級(jí)鎖的時(shí)候，選擇InnoDB钳幅。

二物蝙、事務(wù)

1、事務(wù)特性

• 原子性（Atomicity）：事務(wù)作為一個(gè)整體被執(zhí)行 敢艰，要么全部執(zhí)行诬乞，要么全部不執(zhí)行。事務(wù)執(zhí)行過程中出錯(cuò)盖矫，會(huì)回滾到事務(wù)開始前的狀態(tài)丽惭，所有的操作就像沒有發(fā)生一樣。
• 一致性（Consistency）：事務(wù)開始前和結(jié)束后辈双，數(shù)據(jù)庫(kù)的完整性約束沒有被破壞责掏，執(zhí)行結(jié)果符合預(yù)期規(guī)則 。比如A向B轉(zhuǎn)賬湃望，不可能A扣了錢换衬，B卻沒收到。
• 隔離性（Isolation）：同一時(shí)間证芭，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)瞳浦，不同的事務(wù)之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢废士，在A取錢的過程結(jié)束前叫潦，B不能向這張卡轉(zhuǎn)賬。
• 持久性（Durability）：一個(gè)事務(wù)一旦提交官硝，對(duì)數(shù)據(jù)庫(kù)的修改應(yīng)該永久保存矗蕊，不能回滾。

2氢架、事物的并發(fā)問題

• 臟讀：事務(wù)A讀取了事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù)傻咖。若事務(wù)B回滾數(shù)據(jù)，事務(wù)A的數(shù)據(jù)存在不一致性的問題岖研，那么A讀取到的數(shù)據(jù)就是臟數(shù)據(jù)卿操。【一致性】
• 不可重復(fù)讀：事務(wù)A在執(zhí)行過程中孙援，第一次讀取到的是原始數(shù)據(jù)害淤，第二次讀取到的是事務(wù)B已經(jīng)提交的修改后的數(shù)據(jù)。導(dǎo)致兩次讀取同一數(shù)據(jù)的值不一致拓售。不符合事務(wù)的隔離性窥摄。【隔離性】
• 幻讀：事務(wù)A根據(jù)相同條件第二次查詢到事務(wù)B提交的新增或刪除的數(shù)據(jù)邻辉，兩次數(shù)據(jù)結(jié)果集不一致溪王。不符合事務(wù)的隔離性≈岛В【隔離性】

小結(jié)：不可重復(fù)讀的和幻讀很容易混淆莹菱，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除吱瘩。解決不可重復(fù)讀的問題只需鎖住滿足條件的行道伟，解決幻讀需要鎖表。

3使碾、事物的隔離級(jí)別

4、總結(jié)

• 事務(wù)隔離級(jí)別為讀已提交時(shí)票摇，寫數(shù)據(jù)只會(huì)鎖住相應(yīng)的行拘鞋。
• 事務(wù)隔離級(jí)別為可重復(fù)讀時(shí)，若檢索條件有索引（包括主鍵索引）矢门，默認(rèn)加鎖方式是next-key 鎖【間隙鎖】盆色；若檢索條件沒有索引，則更新數(shù)據(jù)時(shí)會(huì)鎖住整張表祟剔。一個(gè)間隙被事務(wù)加了鎖隔躲，其他事務(wù)是不能在這個(gè)間隙插入記錄的，這樣可以防止幻讀物延。
• 事務(wù)隔離級(jí)別為串行化時(shí)宣旱，讀寫數(shù)據(jù)都會(huì)鎖住整張表
• 隔離級(jí)別越高，越能保證數(shù)據(jù)的完整性和一致性叛薯，但是對(duì)并發(fā)性能的影響也越大浑吟。
• MySQL默認(rèn)隔離級(jí)別是可重復(fù)讀。
• 查看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別：show variables like 'tx_isolation';
• MYSQL MVCC實(shí)現(xiàn)機(jī)制
• next-key 鎖【間隙鎖】

三案训、鎖

?1买置、行鎖

①. 優(yōu)勢(shì)：

• 鎖的粒度小强霎；
• 發(fā)生鎖沖突的概率低忿项；
• 處理并發(fā)的能力強(qiáng)。

②. 劣勢(shì)：

• 開銷大城舞；
• 加鎖慢轩触；
• 會(huì)出現(xiàn)死鎖。

③. 加鎖方式：

自動(dòng)加鎖家夺。

對(duì)于UPDATE脱柱、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及的數(shù)據(jù)集加排他鎖拉馋；對(duì)于普通SELECT語(yǔ)句榨为，InnoDB不會(huì)加任何鎖惨好；當(dāng)然我們也可以顯示的加鎖： 加共享鎖：select * from tableName where ... lock in share mode 加排他鎖：select * from tableName where ... for update

④. 間隙鎖【Next-Key鎖】：

當(dāng)我們用范圍條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)随闺，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖日川；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做"間隙(GAP)"矩乐。InnoDB也會(huì)對(duì)這個(gè)"間隙"加鎖龄句，這種鎖機(jī)制就是所謂的間隙鎖(Next-Key鎖)。

危害（坑）：若執(zhí)行的條件范圍過大散罕，則InnoDB會(huì)將整個(gè)范圍內(nèi)所有的索引鍵值全部鎖定分歇，很容易對(duì)性能造成影響。

Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;

Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)

⑤. 排他鎖：

排他鎖欧漱，也稱寫鎖职抡，獨(dú)占鎖，當(dāng)前寫操作沒有完成前硫椰，它會(huì)阻斷其他寫鎖和讀鎖繁调。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4000 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
# Transaction_B
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (9.53 sec)

⑥. 共享鎖：

共享鎖，也稱讀鎖靶草，多用于判斷數(shù)據(jù)是否存在蹄胰，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響。如果事務(wù)對(duì)讀鎖進(jìn)行修改操作奕翔，很可能會(huì)造成死鎖裕寨。如下圖所示。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# Transaction_B
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

⑦. 分析行鎖定：

通過檢查InnoDB_row_lock 狀態(tài)變量分析系統(tǒng)上的行鎖的爭(zhēng)奪情況派继，命令：

show status like 'innodb_row_lock%'

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量 innodb_row_lock_time: 從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度宾袜；【非常重要的參數(shù)】 innodb_row_lock_time_avg: 每次等待所花平均時(shí)間；【非常重要的參數(shù)】 innodb_row_lock_time_max: 從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花的時(shí)間驾窟； innodb_row_lock_waits: 系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)庆猫；非常重要的參數(shù)，直接決定優(yōu)化的方向和策略绅络。

⑧. 行鎖優(yōu)化：

• 盡可能讓所有數(shù)據(jù)檢索都通過索引來完成月培，避免無索引行或索引失效導(dǎo)致行鎖升級(jí)為表鎖。
• 盡可能避免間隙鎖帶來的性能下降恩急，減少或使用合理的檢索范圍杉畜。
• 盡可能減少事務(wù)的粒度，比如控制事務(wù)大小衷恭，而從減少鎖定資源量和時(shí)間長(zhǎng)度此叠，從而減少鎖的競(jìng)爭(zhēng)等，提供性能随珠。
• 盡可能低級(jí)別事務(wù)隔離灭袁，隔離級(jí)別越高猬错，并發(fā)的處理能力越低。

2茸歧、表鎖

①. 優(yōu)勢(shì)：

• 開銷型没辍；
• 加鎖快举娩；
• 無死鎖。

②. 劣勢(shì)：

• 鎖粒度大构罗；
• 發(fā)生鎖沖突的概率高铜涉；
• 并發(fā)處理能力低。

③. 加鎖方式：

自動(dòng)加鎖遂唧。

查詢操作（SELECT）芙代，會(huì)自動(dòng)給涉及的所有表加讀鎖，更新操作（UPDATE盖彭、DELETE纹烹、INSERT），會(huì)自動(dòng)給涉及的表加寫鎖召边。也可以顯示加鎖：

共享讀鎖：lock table tableName read; ? 獨(dú)占寫鎖：lock table tableName write; ? 批量解鎖：unlock tables;

④. 共享讀鎖：

對(duì)MyISAM表的讀操作（加讀鎖）铺呵，不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀操作，但會(huì)阻塞對(duì)同一表的寫操作隧熙。只有當(dāng)讀鎖釋放后片挂，才能執(zhí)行其他進(jìn)程的寫操作。

Transaction-A
mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
8 rows in set (0.01 sec)

mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)

⑤. 獨(dú)占寫鎖：

對(duì)MyISAM表的寫操作（加寫鎖）贞盯，會(huì)阻塞其他進(jìn)程對(duì)同一表的讀和寫操作音念，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的讀寫操作躏敢。

Transaction-A
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)

mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (42.83 sec)

小結(jié)：表鎖闷愤，讀鎖會(huì)阻塞寫，不會(huì)阻塞讀件余。而寫鎖則會(huì)把讀讥脐、寫都阻塞。

⑥. 查看加鎖情況:

show open tables; 1表示加鎖蛾扇，0表示未加鎖攘烛。

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

⑦. 分析表鎖定：

通過檢查table_locks_waited 和 table_locks_immediate 狀態(tài)變量分析系統(tǒng)上的表鎖定，命令：

show status like 'table_locks%';

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+

• table_locks_immediate: 表示立即釋放表鎖數(shù)镀首。
• table_locks_waited: 表示需要等待的表鎖數(shù)坟漱。此值越高則說明存在著越嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。

此外更哄，MyISAM的讀寫鎖調(diào)度是寫優(yōu)先芋齿，這也是MyISAM不適合做寫為主的存儲(chǔ)引擎腥寇。因?yàn)閷戞i后，其他線程不能做任何操作觅捆，大量的更新會(huì)使查詢很難得到鎖赦役，從而造成永久阻塞。

3栅炒、什么情況下用表鎖掂摔？

InnoDB默認(rèn)采用行鎖，在未使用索引字段查詢時(shí)升級(jí)為表鎖赢赊。MySQL這樣設(shè)計(jì)并不是給你挖坑乙漓。它有自己的設(shè)計(jì)目的。 ? 即便你在條件中使用了索引字段释移，MySQL會(huì)根據(jù)自身的執(zhí)行計(jì)劃叭披，考慮是否使用索引(所以explain命令中會(huì)有possible_key 和 key)。如果MySQL認(rèn)為全表掃描效率更高玩讳，它就不會(huì)使用索引涩蜘，這種情況下InnoDB將使用表鎖，而不是行鎖熏纯。因此同诫，在分析鎖沖突時(shí)，別忘了檢查SQL的執(zhí)行計(jì)劃樟澜，以確認(rèn)是否真正使用了索引剩辟。

• 第一種情況：全表更新。事務(wù)需要更新大部分或全部數(shù)據(jù)往扔，且表又比較大贩猎。若使用行鎖，會(huì)導(dǎo)致事務(wù)執(zhí)行效率低萍膛，從而可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和更多的鎖沖突吭服。
• 第二種情況：多表查詢。事務(wù)涉及多個(gè)表蝗罗，比較復(fù)雜的關(guān)聯(lián)查詢艇棕，很可能引起死鎖，造成大量事務(wù)回滾串塑。這種情況若能一次性鎖定事務(wù)涉及的表沼琉，從而可以避免死鎖、減少數(shù)據(jù)庫(kù)因事務(wù)回滾帶來的開銷桩匪。

4打瘪、總結(jié)

• InnoDB 支持表鎖和行鎖，使用索引作為檢索條件修改數(shù)據(jù)時(shí)采用行鎖，否則采用表鎖闺骚。
• InnoDB 自動(dòng)給寫操作加鎖彩扔，讀操作不自動(dòng)加鎖。
• 行鎖可能因?yàn)槲词褂盟饕?jí)為表鎖僻爽，所以除了檢查索引是否創(chuàng)建的同時(shí)虫碉，也需要通過explain執(zhí)行計(jì)劃查詢索引是否被實(shí)際使用。
• 行鎖相對(duì)于表鎖來說胸梆，優(yōu)勢(shì)在于高并發(fā)場(chǎng)景下表現(xiàn)更突出敦捧，畢竟鎖的粒度小。
• 當(dāng)表的大部分?jǐn)?shù)據(jù)需要被修改碰镜，或者是多表復(fù)雜關(guān)聯(lián)查詢時(shí)绞惦，建議使用表鎖優(yōu)于行鎖。
• 為了保證數(shù)據(jù)的一致完整性洋措，任何一個(gè)數(shù)據(jù)庫(kù)都存在鎖定機(jī)制。鎖定機(jī)制的優(yōu)劣直接影響到一個(gè)數(shù)據(jù)庫(kù)的并發(fā)處理能力和性能杰刽。

四菠发、索引

1、原理

我們拿出一本新華字典贺嫂，它的目錄實(shí)際上就是一種索引：非聚集索引滓鸠。我們可以通過目錄迅速定位我們要查的字。而字典的內(nèi)容部分一般都是按照拼音排序的第喳，這實(shí)際上又是一種索引：聚集索引糜俗。聚集索引這種實(shí)現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵曲饱，然后用主鍵到主索引中檢索獲得記錄悠抹。

2、底層實(shí)現(xiàn)【數(shù)據(jù)結(jié)構(gòu)】

①扩淀、mysql主要使用B+樹來構(gòu)建索引楔敌，為什么不用二叉樹和紅黑樹？

• B+樹是多叉的驻谆，可以減少樹的高度卵凑。
• 索引本身較大，不會(huì)全部存儲(chǔ)在內(nèi)存中胜臊，會(huì)以索引文件的形式存儲(chǔ)在磁盤上勺卢，所以索引在查找數(shù)據(jù)的過程中會(huì)涉及到磁盤I/O操作。
• 因磁盤I/O效率低下象对，mysql為了盡量減少磁盤IO的存取次數(shù)黑忱，需要利用了磁盤存取的局部性原理進(jìn)行磁盤預(yù)讀。
  • 局部性原理：為了減少磁盤IO，磁盤往往會(huì)進(jìn)行數(shù)據(jù)預(yù)讀，會(huì)從某位置開始拄氯，預(yù)先順序向后讀取一定長(zhǎng)度的數(shù)據(jù)放入內(nèi)存驮捍。因?yàn)榇疟P順序讀取的效率較高，不需要尋道時(shí)間羊娃，因此可以提高IO效率。
  • 磁盤預(yù)讀長(zhǎng)度一般為頁(yè)的整數(shù)倍埃跷，主存和磁盤以頁(yè)作為單位交換數(shù)據(jù)蕊玷。當(dāng)需要讀取的數(shù)據(jù)不在內(nèi)存時(shí)，觸發(fā)缺頁(yè)中斷弥雹，系統(tǒng)會(huì)向磁盤發(fā)出讀取磁盤數(shù)據(jù)的請(qǐng)求垃帅，磁盤找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁(yè)或幾頁(yè)數(shù)據(jù)載入內(nèi)存，然后中斷返回剪勿，系統(tǒng)繼續(xù)運(yùn)行贸诚。
• mysql將B+數(shù)的一個(gè)節(jié)點(diǎn)的大小設(shè)為一個(gè)頁(yè)，這樣每個(gè)節(jié)點(diǎn)只需要一次I/O就可以完全載入內(nèi)存【由于節(jié)點(diǎn)中有若干個(gè)數(shù)組厕吉，所以地址連續(xù)】酱固。
• 紅黑樹的結(jié)構(gòu)深度更深，很多邏輯上很近的節(jié)點(diǎn)（如父子節(jié)點(diǎn)）在物理上可能很遠(yuǎn)头朱，無法利用局部性原理运悲。

在InnoDB里，每個(gè)頁(yè)默認(rèn)16KB项钮，假設(shè)索引的是8B的long型數(shù)據(jù)班眯，每個(gè)key后有個(gè)頁(yè)號(hào)4B，還有6B的其他數(shù)據(jù)（參考《MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎》P193的頁(yè)面數(shù)據(jù)）烁巫，那么每個(gè)頁(yè)的扇出系數(shù)為16KB/(8B+4B+6B)≈1000署隘，即每個(gè)頁(yè)可以索引1000個(gè)key。在高度h=3時(shí)亚隙，s=1000^3=10億６狻！也就是說恃鞋，InnoDB通過三次索引頁(yè)的I/O崖媚，即可索引10億的key。通常來說恤浪，索引樹的高度在2~4畅哑。

②. B+Tree與B-Tree的區(qū)別

M階B-Tree

定義：

• 樹中每個(gè)結(jié)點(diǎn)至多有m個(gè)孩子；
• 除根結(jié)點(diǎn)和葉子結(jié)點(diǎn)外水由，其它每個(gè)結(jié)點(diǎn)至少有m/2個(gè)孩子荠呐；
• 若根結(jié)點(diǎn)不是葉子結(jié)點(diǎn)，則至少有2個(gè)孩子；
• 所有葉子結(jié)點(diǎn)都在同一層泥张；

特性：

• 關(guān)鍵字集合分布在整顆樹中呵恢；
• 任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)結(jié)點(diǎn)中；
• 搜索有可能在非葉子結(jié)點(diǎn)結(jié)束媚创；
• 其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找渗钉；
• 自動(dòng)層次控制；

M階B+Tree

定義：

• 有m個(gè)子樹的節(jié)點(diǎn)包含有m個(gè)元素（B-Tree中是m-1）;
• 非葉子節(jié)點(diǎn)不保存數(shù)據(jù)钞钙，只用于索引鳄橘，所有數(shù)據(jù)都保存在葉子節(jié)點(diǎn)中。
• 所有分支節(jié)點(diǎn)和根節(jié)點(diǎn)都同時(shí)存在于子節(jié)點(diǎn)中芒炼，在子節(jié)點(diǎn)元素中是最大或者最小的元素瘫怜。
• 葉子節(jié)點(diǎn)會(huì)包含所有的關(guān)鍵字，以及指向數(shù)據(jù)記錄的指針本刽，并且葉子節(jié)點(diǎn)本身是根據(jù)關(guān)鍵字的大小從小到大順序鏈接鲸湃。

特性：

• 所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中（稠密索引），且鏈表中的關(guān)鍵字是有序的子寓；
• 不可能在非葉子結(jié)點(diǎn)命中暗挑，因?yàn)榉侨~子節(jié)點(diǎn)只有Key，沒有Data别瞭；
• 非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引（稀疏索引），葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)所有關(guān)鍵字?jǐn)?shù)據(jù)的數(shù)據(jù)層株憾；
• 更適合文件索引系統(tǒng)蝙寨。

③. B+Tree與B-Tree的區(qū)別

• B+Tree有n棵子樹的結(jié)點(diǎn)中含有n個(gè)關(guān)鍵字； (而B樹是n棵子樹有n-1個(gè)關(guān)鍵字)嗤瞎。
• B+Tree所有Key（關(guān)鍵字）存儲(chǔ)在葉子節(jié)點(diǎn)墙歪，非葉子節(jié)點(diǎn)不存儲(chǔ)真正的data（數(shù)據(jù)）。
• B+Tree為所有葉子節(jié)點(diǎn)增加了一個(gè)鏈指針贝奇，且所有葉子節(jié)點(diǎn)的關(guān)鍵字按從小到大順序鏈接虹菲，增強(qiáng)了區(qū)間訪問性。

④. 為什么mysql的索引使用B+樹而不是B樹呢掉瞳？

• B+樹更適合外部存儲(chǔ)(一般指磁盤存儲(chǔ))毕源，由于內(nèi)節(jié)點(diǎn)(非葉子節(jié)點(diǎn))不存data（數(shù)據(jù)）只存Key（關(guān)鍵字），所以B+樹一個(gè)節(jié)點(diǎn)可以存儲(chǔ)更多的Key陕习，即每個(gè)節(jié)點(diǎn)能索引的范圍更大更精確霎褐。也就是說使用B+樹單次磁盤I/O的信息量相比較B樹更大，I/O效率更高该镣。
• mysql是關(guān)系型數(shù)據(jù)庫(kù)冻璃，經(jīng)常會(huì)按照區(qū)間來訪問某個(gè)索引列，B+樹的葉子節(jié)點(diǎn)間按Key的順序建立了鏈指針，加強(qiáng)了區(qū)間訪問性省艳，所以B+樹對(duì)索引列上的區(qū)間范圍查詢很友好娘纷。而B樹每個(gè)節(jié)點(diǎn)的key和data在一起，無法進(jìn)行區(qū)間查找跋炕。

3赖晶、索引優(yōu)點(diǎn)

• 快速讀取數(shù)據(jù)。
• 唯一性索引能保證數(shù)據(jù)記錄的唯一性枣购。
• 實(shí)現(xiàn)表與表之間的參照完整性【通過主鍵索引】嬉探。

4、索引缺點(diǎn)

• 索引需要占用物理空間棉圈。
• 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加涩堤、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)分瘾，降低了數(shù)據(jù)的維護(hù)速度胎围。

五、mysql性能分析

1德召、MySQL 自身瓶頸

MySQL自身常見的性能問題有磁盤空間不足白魂，磁盤I/O太大，服務(wù)器硬件性能低上岗。

• CPU瓶頸：CPU 飽和一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)的時(shí)候福荸。
• IO瓶頸：磁盤I/O 瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候。
• 服務(wù)器硬件的性能瓶頸：可通過top,free,iostat 和 vmstat來查看系統(tǒng)的性能狀態(tài)肴掷。

2敬锐、explain 分析sql語(yǔ)句

使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql查詢語(yǔ)句，從而得知MySQL 是如何處理sql語(yǔ)句呆瞻。

①. id
select 查詢的序列號(hào)台夺，包含一組可以重復(fù)的數(shù)字，表示查詢中sql語(yǔ)句的執(zhí)行順序痴脾。一般有三種情況： 第一種：id全部相同颤介，sql的執(zhí)行順序是由上至下； 第二種：id全部不同赞赖，sql的執(zhí)行順序是根據(jù)id大的優(yōu)先執(zhí)行（如果是子查詢滚朵，id的序號(hào)會(huì)遞增）； 第三種：id既存在相同前域，又存在不同的始绍。先根據(jù)id大的優(yōu)先執(zhí)行，再根據(jù)相同id從上至下的執(zhí)行话侄。

②. select_type

select 查詢的類型亏推，主要是用于區(qū)別普通查詢学赛，聯(lián)合查詢，嵌套的復(fù)雜查詢：

• simple：簡(jiǎn)單的select 查詢吞杭，查詢中不包含子查詢或者union盏浇。
• primary：查詢中若包含任何復(fù)雜的子查詢，最外層查詢則被標(biāo)記為primary芽狗。
• subquery：在select或where 列表中包含了子查詢绢掰。
• derived：在from列表中包含的子查詢被標(biāo)記為derived（衍生），MySQL會(huì)遞歸執(zhí)行這些子查詢童擎，把結(jié)果放在臨時(shí)表里滴劲。
• union：若第二個(gè)select出現(xiàn)在union之后，則被標(biāo)記為union【聯(lián)合查詢】顾复，若union包含在from子句的子查詢中班挖，外層select將被標(biāo)記為：derived。
• union result：從union表獲取結(jié)果的select芯砸。

subquery和union 還可以被標(biāo)記為dependent和uncacheable萧芙。 dependent意味著select依賴于外層查詢中發(fā)現(xiàn)的數(shù)據(jù)。 uncacheable意味著select中的某些特性阻止結(jié)果被緩存于一個(gè)item_cache中假丧。

③. table

查詢結(jié)果來自于哪個(gè)表双揪。

④. partitions

表所使用的分區(qū)，如果要統(tǒng)計(jì)十年公司訂單的金額包帚，可以把數(shù)據(jù)分為十個(gè)區(qū)渔期，每一年代表一個(gè)區(qū)。這樣可以大大的提高查詢效率渴邦。

⑤. type

這是一個(gè)非常重要的參數(shù)疯趟，連接類型，常見的有：all , index , range , ref , eq_ref , const , system , null 八個(gè)級(jí)別几莽。

性能從最優(yōu)到最差的排序：null > system > const > eq_ref > ref > range > index > all

對(duì)java程序員來說迅办，若保證查詢至少達(dá)到range級(jí)別或者最好能達(dá)到ref則算是一個(gè)優(yōu)秀而又負(fù)責(zé)的程序員宅静。

• all：（full table scan）全表掃描無疑是最差章蚣，若是百萬千萬級(jí)數(shù)據(jù)量，全表掃描會(huì)非常慢姨夹。
• index：（full index scan）全索引文件掃描比all好很多纤垂，畢竟從索引樹中找數(shù)據(jù)，比從全表中找數(shù)據(jù)要快磷账。
• range：只檢索給定范圍的行峭沦，使用索引來匹配行。范圍縮小了逃糟，當(dāng)然比全表掃描和全索引文件掃描要快吼鱼。sql語(yǔ)句中一般會(huì)有between蓬豁，>，< 等查詢菇肃，IN()和OR列表地粪，也會(huì)顯示range。
• ref：非唯一性索引掃描琐谤，本質(zhì)上也是一種索引訪問蟆技，返回所有匹配某個(gè)單獨(dú)值的行。比如查詢公司所有屬于研發(fā)團(tuán)隊(duì)的同事斗忌，匹配的結(jié)果是多個(gè)并非唯一值质礼。

explain select * from t1 where name='yayun';

• eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵织阳，表中有一條記錄與之匹配眶蕉。比如用主鍵或唯一字段作為判斷條件。

explain select t1.name from t1, t2 where t1.id=t2.id;

• const：表示通過索引一次就可以找到陈哑，const用于比較primary key 或者unique索引妻坝。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快惊窖，若將主鍵至于where列表中刽宪，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量。

explain select * from t1 where id = 1

• system：表只有一條記錄（等于系統(tǒng)表）界酒，這是const類型的特列圣拄，平時(shí)不會(huì)出現(xiàn)，了解即可毁欣。
• NULL：MySQL在優(yōu)化過程中分解語(yǔ)句庇谆，執(zhí)行時(shí)甚至不用訪問表或索引，例如從一個(gè)索引列里選取最小值可以通過單獨(dú)索引查找完成凭疮。

explain select * from t1 where id = (select min(id) from t2);

⑥. possible_keys

顯示查詢語(yǔ)句可能用到的索引(即查詢涉及字段中存在索引的字段饭耳，可能為一個(gè)、多個(gè)或?yàn)閚ull)执解，不一定被查詢實(shí)際使用寞肖，僅供參考使用。

⑦. key

顯示查詢語(yǔ)句實(shí)際使用的索引字段衰腌。若為null新蟆，則表示沒有使用索引。

⑧. key_len

顯示索引中使用的字節(jié)數(shù)右蕊，可通過key_len計(jì)算查詢中使用的索引長(zhǎng)度琼稻。

? 在不損失精確性的情況下索引長(zhǎng)度越短越好。key_len 顯示的值為索引字段的最可能長(zhǎng)度饶囚，并非實(shí)際使用長(zhǎng)度帕翻，即key_len是根據(jù)表定義計(jì)算而得鸠补，并不是通過表內(nèi)檢索出的。

⑨. ref

表示上述表的連接匹配條件嘀掸，即哪些列或常量被用于查找索引列上的值莫鸭。即顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

**⑩. rows

根據(jù)表統(tǒng)計(jì)信息及索引選用情況横殴，大致估算出找到所需的記錄所需要讀取的行數(shù)被因，值越大越不好。

即根據(jù)查詢語(yǔ)句及索引選用情況衫仑，大致估算出要得到查詢結(jié)果梨与，所需要在表中讀取的行數(shù)。

?. filtered

一個(gè)百分比的值文狱，和rows 列的值一起使用粥鞋，可以估計(jì)出查詢執(zhí)行計(jì)劃(QEP)中的前一個(gè)表的結(jié)果集，從而確定join操作的循環(huán)次數(shù)瞄崇。小表驅(qū)動(dòng)大表呻粹，減輕連接的次數(shù)。

?. extra

包含不適合在其他列中顯示但又十分重要的額外信息苏研。

• Using filesort： “文件排序”等浊，說明MySQL中無法利用索引完成的排序操作 ，MySQL會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序摹蘑，而不是按照表內(nèi)的索引順序進(jìn)行讀取筹燕。出現(xiàn)這個(gè)就要立刻優(yōu)化sql。
• Using temporary： 使用了臨時(shí)表保存中間結(jié)果衅鹿，說明MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表撒踪。常見于排序 order by 和 分組查詢 group by時(shí)，需要借助輔助表再進(jìn)行排序的情況（這種情況是多個(gè)表都涉及到排序字段才會(huì)引起的）大渤。 出現(xiàn)這個(gè)更要立刻優(yōu)化sql制妄。
• Using index： 表示相應(yīng)的select 操作中使用了覆蓋索引（Covering index），避免訪問了表的數(shù)據(jù)行泵三，效果不錯(cuò)耕捞！如果同時(shí)出現(xiàn)Using where，表明索引被用來執(zhí)行索引鍵值的查找切黔。如果沒有同時(shí)出現(xiàn)Using where砸脊，表示索引只是用來讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作具篇。 覆蓋索引（Covering Index） ：也叫索引覆蓋纬霞，就是select 的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行驱显，MySQL可以利用索引返回select 列表中的字段诗芜，而不必根據(jù)索引再次讀取數(shù)據(jù)文件瞳抓。
• Using index condition： 在5.6版本后加入的新特性，優(yōu)化器會(huì)在索引存在的情況下伏恐，通過符合RANGE范圍的條數(shù) 和 總數(shù)的比例來選擇是使用索引還是進(jìn)行全表遍歷孩哑。
• Using where： 表明使用了where 過濾。
• Using join buffer： 表明使用了連接緩存翠桦。
• impossible where： where 語(yǔ)句的值總是false横蜒，不可用，不能用來獲取任何元素销凑。
• distinct： 優(yōu)化distinct操作丛晌，在找到第一匹配的元組后即停止找同樣值的動(dòng)作。

3斗幼、explain總結(jié)

通過explain的參數(shù)介紹澎蛛，我們可以得知:

• sql的查詢順序（根據(jù)id，id越大越先執(zhí)行）蜕窿。
• 數(shù)據(jù)讀取操作的操作類型(type)
• 哪些索引被實(shí)際使用(key)
• 表之間的引用(ref)
• 每張表有多少行被優(yōu)化器查詢(rows)

六谋逻、數(shù)據(jù)庫(kù)性能優(yōu)化

1、優(yōu)化思路

可從以下幾個(gè)方面對(duì)數(shù)據(jù)庫(kù)性能進(jìn)行優(yōu)化：

• 優(yōu)化數(shù)據(jù)庫(kù)與索引的設(shè)計(jì)桐经。
• 優(yōu)化SQL語(yǔ)句毁兆。
• 加緩存【Memcached, Redis】
• 主從復(fù)制，讀寫分離阴挣。
• 垂直拆分荧恍，其實(shí)就是根據(jù)你模塊的耦合度，將一個(gè)包含多個(gè)字段的表分成多個(gè)小的表屯吊，將一個(gè)大的系統(tǒng)分為多個(gè)小的系統(tǒng)送巡，也就是分布式系統(tǒng)。
• 水平切分盒卸，針對(duì)數(shù)據(jù)量大的表骗爆，這一步最麻煩，最能考驗(yàn)技術(shù)水平蔽介，要選擇一個(gè)合理的sharding key,為了有好的查詢效率摘投，表結(jié)構(gòu)也要改動(dòng)，做一定的冗余虹蓄，應(yīng)用也要改犀呼，sql中盡量帶sharding key，將數(shù)據(jù)定位到限定的表上去查薇组，而不是掃描全部的表外臂；

2、數(shù)據(jù)庫(kù)與索引設(shè)計(jì)

①. 數(shù)據(jù)庫(kù)設(shè)計(jì)

• 表字段避免null值出現(xiàn)律胀，null值很難進(jìn)行查詢優(yōu)化且占用額外的索引空間宋光，推薦默認(rèn)數(shù)字0代替null貌矿。
• 盡量使用INT而非BIGINT，如果非負(fù)則加上UNSIGNED（這樣數(shù)值容量會(huì)擴(kuò)大一倍）罪佳，當(dāng)然能使用TINYINT逛漫、SMALLINT、MEDIUM_INT更好赘艳。
• 使用枚舉或整數(shù)代替字符串類型酌毡。
• 盡量使用TIMESTAMP而非DATETIME。
• 單表不要有太多字段蕾管，建議在20以內(nèi)阔馋。
• 用整型來存IP〗刻停【可去搜索IP地址轉(zhuǎn)為整型】
  等呕寝。

②. 索引設(shè)計(jì)

• 索引要占用物理內(nèi)存，并不是越多越好婴梧，要根據(jù)查詢有針對(duì)性的創(chuàng)建下梢，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描塞蹭。
• 應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行NULL值判斷孽江，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
• 值分布很稀少的字段不適合建索引番电，例如"性別"這種只有兩三個(gè)值的字段岗屏。
• 字符字段只建前綴索引。
• 字符字段最好不要做主鍵漱办。
• 不用外鍵这刷，由程序保證約束。
• 盡量不用UNIQUE娩井，由程序保證約束暇屋。
• 使用多列索引時(shí)主意順序和查詢條件保持一致，同時(shí)刪除不必要的單列索引洞辣。

③. 總結(jié)

就一句話：使用合適的數(shù)據(jù)類型咐刨，選擇合適的索引：

• 使用合適的數(shù)據(jù)類型
  • 使用可存下數(shù)據(jù)的最小的數(shù)據(jù)類型，整型 < date扬霜，time < char定鸟，varchar < blob
  • 使用簡(jiǎn)單的數(shù)據(jù)類型，整型比字符處理開銷更小著瓶，因?yàn)樽址谋容^更復(fù)雜联予。如，int類型存儲(chǔ)時(shí)間類型，bigint類型轉(zhuǎn)ip函數(shù)躯泰。
  • 使用合理的字段屬性長(zhǎng)度，固定長(zhǎng)度的表會(huì)更快华糖。使用enum麦向、char而不是varchar。
  • 盡可能使用not null定義字段客叉。
  • 盡量少用text诵竭，非用不可最好分表。
• 選擇合適的索引列（即哪些列適合添加索引）
  • 查詢頻繁的列兼搏，在where卵慰，group by，order by佛呻，on從句中出現(xiàn)的列裳朋。
  • where條件中<，<=吓著，=鲤嫡，>，>=绑莺，between暖眼，in，以及l(fā)ike 字符串+通配符（%）出現(xiàn)的列纺裁。
  • 長(zhǎng)度小的列诫肠，索引字段越小越好，因?yàn)閿?shù)據(jù)庫(kù)的存儲(chǔ)單位是頁(yè)欺缘，一頁(yè)中能存下的數(shù)據(jù)越多越好栋豫。
  • 離散度大（不同的值多）的列，放在聯(lián)合索引前面谚殊。查看離散度笼才，通過統(tǒng)計(jì)不同的列值來實(shí)現(xiàn)，count越大络凿，離散程度越高骡送。

3、sql優(yōu)化

• 使用limit對(duì)查詢結(jié)果的記錄進(jìn)行限定絮记。
• *避免select 摔踱，將需要查找的字段列出來。
• 使用連接（join）來代替子查詢怨愤。
• 拆分大的delete或insert語(yǔ)句派敷。
• 可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL。如何開啟mysql慢查詢?nèi)罩荆?/li>
• 不做列運(yùn)算：SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描篮愉，它包括數(shù)據(jù)庫(kù)教程函數(shù)腐芍、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊试躏。
• sql語(yǔ)句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算猪勇；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間颠蕴；一條大sql可以堵死整個(gè)庫(kù)泣刹。
• OR改寫成IN：OR的效率是O(n)級(jí)別，IN的效率是O(logn)級(jí)別犀被，in的個(gè)數(shù)建議控制在200以內(nèi)椅您。
• 不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)寡键。
• 避免%xxx式查詢掀泳。
• 少用JOIN。
• 使用同類型進(jìn)行比較西轩，比如用'123'和'123'比开伏，123和123比。
• 盡量避免在WHERE子句中使用 != 或 <> 操作符遭商，否則導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描固灵。
• 對(duì)于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5劫流。
• 列表數(shù)據(jù)不要拿全表巫玻，要使用LIMIT來分頁(yè)，每頁(yè)數(shù)量也不要太大祠汇。

4仍秤、分區(qū)、分庫(kù)可很、分表

①. 分區(qū)

把一張表的數(shù)據(jù)分成N個(gè)區(qū)塊诗力，在邏輯上看最終只是一張表，但底層是由N個(gè)物理區(qū)塊組成的我抠，通過將不同數(shù)據(jù)按一定規(guī)則放到不同的區(qū)塊中提升表的查詢效率苇本。

②. 分表

• 水平分表：為了解決單表數(shù)據(jù)量過大（數(shù)據(jù)量達(dá)到千萬級(jí)別）問題。所以將固定的ID hash之后mod菜拓，取若0~N個(gè)值瓣窄，然后將數(shù)據(jù)劃分到不同表中，需要在寫入與查詢的時(shí)候進(jìn)行ID的路由與統(tǒng)計(jì)纳鼎。
• 垂直分表：為了解決表的寬度問題俺夕，同時(shí)還能分別優(yōu)化每張單表的處理能力裳凸。所以將表結(jié)構(gòu)根據(jù)數(shù)據(jù)的活躍度拆分成多個(gè)表，把不常用的字段單獨(dú)放到一個(gè)表劝贸、把大字段單獨(dú)放到一個(gè)表姨谷、把經(jīng)常使用的字段放到一個(gè)表。

③. 分庫(kù)

面對(duì)高并發(fā)的讀寫訪問映九，當(dāng)數(shù)據(jù)庫(kù)無法承載寫操作壓力時(shí)梦湘，不管如何擴(kuò)展slave服務(wù)器，此時(shí)都沒有意義了氯迂。因此需對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分践叠，從而提高數(shù)據(jù)庫(kù)寫入能力言缤，這就是分庫(kù)嚼蚀。

④. 問題

• 事務(wù)問題。在執(zhí)行分庫(kù)之后管挟，由于數(shù)據(jù)存儲(chǔ)到了不同的庫(kù)上轿曙，數(shù)據(jù)庫(kù)事務(wù)管理出現(xiàn)了困難。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)僻孝，將付出高昂的性能代價(jià)导帝；如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)穿铆，又會(huì)造成編程方面的負(fù)擔(dān)您单。
• 跨庫(kù)跨表的join問題。在執(zhí)行了分庫(kù)分表之后荞雏，難以避免會(huì)將原本邏輯關(guān)聯(lián)性很強(qiáng)的數(shù)據(jù)劃分到不同的表虐秦、不同的庫(kù)上，我們無法join位于不同分庫(kù)的表凤优，也無法join分表粒度不同的表悦陋，結(jié)果原本一次查詢能夠完成的業(yè)務(wù)，可能需要多次查詢才能完成筑辨。
• 額外的數(shù)據(jù)管理負(fù)擔(dān)和數(shù)據(jù)運(yùn)算壓力俺驶。額外的數(shù)據(jù)管理負(fù)擔(dān)，最顯而易見的就是數(shù)據(jù)的定位問題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問題棍辕，這些都可以通過應(yīng)用程序解決暮现，但必然引起額外的邏輯運(yùn)算。

七楚昭、緩存

對(duì)于很多的數(shù)據(jù)庫(kù)系統(tǒng)都能夠緩存執(zhí)行計(jì)劃送矩，對(duì)于完全相同的sql, 可以使用已經(jīng)已經(jīng)存在的執(zhí)行計(jì)劃，從而跳過解析和生成執(zhí)行計(jì)劃的過程哪替。MYSQL提供了更為高級(jí)的查詢結(jié)果緩存功能栋荸，對(duì)于完全相同的SQL (字符串完全相同且大小寫敏感) 可以執(zhí)行返回查詢結(jié)果。

MySQL緩存機(jī)制簡(jiǎn)單的說就是緩存sql文本及查詢結(jié)果，如果運(yùn)行相同的sql晌块，服務(wù)器直接從緩存中取到結(jié)果爱沟，而不需要再去解析和執(zhí)行sql。如果表更改了匆背，那么使用這個(gè)表的所有緩沖查詢將不再有效呼伸，查詢緩存值的相關(guān)條目被清空。更改指的是表中任何數(shù)據(jù)或是結(jié)構(gòu)的改變钝尸，包括INSERT括享、UPDATE、 DELETE珍促、TRUNCATE（截?cái)啵┝逑健LTER TABLE、DROP TABLE或DROP DATABASE等猪叙，也包括那些映射到改變了的表的使用MERGE表的查詢娇斩。顯然，這對(duì)于頻繁更新的表穴翩，查詢緩存是不適合的犬第，而對(duì)于一些不常改變數(shù)據(jù)且有 大量相同sql查詢的表，查詢緩存會(huì)節(jié)約很大的性能芒帕。

八歉嗓、面試真題

問：有個(gè)表特別大，字段是姓名背蟆、年齡鉴分、班級(jí)，如果調(diào)用select * from table where name = xxx and age = xxx該如何通過建立索引的方式優(yōu)化查詢速度淆储？

答：由于mysql查詢每次只能使用一個(gè)索引冠场，如果在name、age兩列上創(chuàng)建復(fù)合索引的話將帶來更高的效率本砰。如果我們創(chuàng)建了(name, age)的復(fù)合索引碴裙，那么其實(shí)相當(dāng)于創(chuàng)建了(name)、(name, age)兩個(gè)索引点额，這被稱為最佳左前綴特性舔株。因此我們?cè)趧?chuàng)建復(fù)合索引時(shí)應(yīng)該將最常用作限制條件的列放在最左邊，依次遞減还棱。其次還要考慮該列的數(shù)據(jù)離散程度载慈，如果有很多不同的值的話建議放在左邊，name的離散程度也大于age珍手。

問：max(xxx)如何用索引優(yōu)化办铡？

答：在xxx列上建立索引辞做，因?yàn)樗饕荁+樹順序排列的，鎖在下次查詢的時(shí)候就會(huì)使用索引來查詢到最大的值是哪個(gè)寡具。

問：如何對(duì)分頁(yè)進(jìn)行優(yōu)化秤茅？

答：SELECT * FROM big_table order by xx LIMIT 1000000,20，這條語(yǔ)句會(huì)查詢出1000020條的所有數(shù)據(jù)然后丟棄掉前1000000條童叠，為了避免全表掃描的操作框喳，在order by的列上加索引就能通過掃描索引來查詢。但是這條語(yǔ)句會(huì)查詢還是會(huì)掃描1000020條厦坛，還能改進(jìn)成select id from big_table where id >= 1000000 order by xx LIMIT 0,20五垮，用ID作為過濾條件將不需要查詢的數(shù)據(jù)直接去除。

九杜秸、讀者福利

針對(duì)Java程序員放仗，筆者最近整理了一份完整的一線互聯(lián)網(wǎng)大廠面試真題，包含了Kafka亩歹、Mysql匙监、Tomcat凡橱、Docker小作、Spring、MyBatis稼钩、Nginx顾稀、Netty、Dubbo坝撑、Redis静秆、Netty、Spring cloud巡李、分布式抚笔、高并發(fā)、性能調(diào)優(yōu)侨拦、微服務(wù)等架構(gòu)技術(shù)殊橙。

需要的朋友請(qǐng)點(diǎn)擊下方傳送門免費(fèi)領(lǐng)取

傳送門

以下是部分面試題截圖

十、架構(gòu)師成長(zhǎng)路線圖