MySQL 基礎(chǔ)

數(shù)據(jù)庫的約束與范式？

七大約束：

• 檢查約束：以數(shù)據(jù)類型以及數(shù)據(jù)的長度進行約束，在一個表中， 所插入的數(shù)據(jù)，必須和數(shù)據(jù)類型匹配，并且范圍不能超過指定的長度。
• 非空約束 not null：非空約束用于確保當前列的值不為空值，非空約束只能出現(xiàn)在表對象的列上。 如果有非空約束，那么在插入插入數(shù)據(jù)時，必須插入一條數(shù)據(jù)，非空約束一般用于收集并存儲的字段數(shù)據(jù)。 所有的類型的值都可以是null，包括int、float 等數(shù)據(jù)類型。
• 默認值約束 default：創(chuàng)建列時可以指定默認值，當插入數(shù)據(jù)時如果未主動設(shè)置數(shù)據(jù)，則自動添加該默認值。
• 主鍵約束 primary key：確保每一行唯一，主鍵約束相當于唯一約束 + 非空約束的組合，是用于唯一識別一個實體的字段，不允許有空值，當創(chuàng)建主鍵的約束時，系統(tǒng)默認會在所在的列和列組合上建立對應(yīng)的唯一索引。 如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。
• 唯一約束 unique：保證每一行的數(shù)據(jù)是唯一的，沒有重復(fù)的數(shù)據(jù)，保證數(shù)據(jù)的唯一性。 唯一約束不允許出現(xiàn)重復(fù)的值，但是可以為多個null。 同一個表可以有多個唯一約束，多個列組合的約束。 在創(chuàng)建唯一約束時，如果不給唯一約束名稱，就默認和列名相同。 唯一約束不僅可以在一個表內(nèi)創(chuàng)建，而且可以同時多表創(chuàng)建組合唯一約束。
• 外鍵約束 foreign key：用于表與表之間的關(guān)聯(lián)，一張表中的外鍵一般都是另一張表中的主鍵， 并且加上外鍵約束之后，插入的數(shù)據(jù)就必須是該主鍵存在的值。
• 自增長約束：如果為某列設(shè)置自增列，插入數(shù)據(jù)時無需設(shè)置此列，默認將自增（一個表只能有一個自增列）。
• 自定義約束：自定義約束是使用存儲過程或者觸發(fā)器來進行約束。

三大范式：

• 確保每列保持原子性（不可拆分性）：確保一列只存放一個數(shù)據(jù)，即數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項，而不能是集合，數(shù)組，記錄等非原子數(shù)據(jù)項。 如果實體中的某個屬性有多個值時，必須拆分為不同的屬性。
• 確保每一行的唯一性：滿足第一范式的基礎(chǔ)上，確保每一行數(shù)據(jù)的唯一，能夠唯一區(qū)分一個實體，實現(xiàn)的方式可以是主鍵。
• 確保每列都和主鍵列直接相關(guān)，而不是間接相關(guān)：第三范式需要確保數(shù)據(jù)表除外鍵外，其他非主鍵屬性不得重復(fù)出現(xiàn)在第二張表上。

MySQL 默認端口？

Mysql 的默認端口是3306。

MySQL 和 PostgreSQL 的區(qū)別？

PostgreSQL相對于MySQL的優(yōu)勢：

1. 在SQL的標準實現(xiàn)上要比MySQL完善，而且功能實現(xiàn)比較嚴謹；
2. 存儲過程的功能支持要比MySQL好，具備本地緩存執(zhí)行計劃的能力；
3. 對表連接支持較完整，優(yōu)化器的功能較完整，支持的索引類型很多，復(fù)雜查詢能力較強；
4. PG主表采用堆表存放，MySQL采用索引組織表，能夠支持比MySQL更大的數(shù)據(jù)量。
5. PG的主備復(fù)制屬于物理復(fù)制，相對于MySQL基于binlog的邏輯復(fù)制，數(shù)據(jù)的一致性更加可靠，復(fù)制性能更高，對主機性能的影響也更小。
6. MySQL的存儲引擎插件化機制，存在鎖機制復(fù)雜影響并發(fā)的問題，而PG不存在。

MySQL相對于PG的優(yōu)勢：

1. innodb的基于回滾段實現(xiàn)的MVCC機制，相對PG新老數(shù)據(jù)一起存放的基于XID的MVCC機制，是占優(yōu)的。新老數(shù)據(jù)一起存放，需要定時觸 發(fā)VACUUM，會帶來多余的IO和數(shù)據(jù)庫對象加鎖開銷，引起數(shù)據(jù)庫整體的并發(fā)能力下降。而且VACUUM清理不及時，還可能會引發(fā)數(shù)據(jù)膨脹；
2. MySQL采用索引組織表，這種存儲方式非常適合基于主鍵匹配的查詢、刪改操作，但是對表結(jié)構(gòu)設(shè)計存在約束；
3. MySQL的優(yōu)化器較簡單，系統(tǒng)表、運算符、數(shù)據(jù)類型的實現(xiàn)都很精簡，非常適合簡單的查詢操作；
4. MySQL分區(qū)表的實現(xiàn)要優(yōu)于PG的基于繼承表的分區(qū)實現(xiàn)，主要體現(xiàn)在分區(qū)個數(shù)達到上千上萬后的處理性能差異較大。
5. MySQL的存儲引擎插件化機制，使得它的應(yīng)用場景更加廣泛，比如除了innodb適合事務(wù)處理場景外，myisam適合靜態(tài)數(shù)據(jù)的查詢場景。

獲取當前 Mysql 版本？

SELECT VERSION(); 用于獲取當前 Mysql 的版本。

主鍵和候選鍵區(qū)別？

表格的每一行都由主鍵唯一標識，一個表只有一個主鍵。

主鍵也是候選鍵，按照慣例，候選鍵可以被指定為主鍵，并且可以用于任何外鍵引用。

設(shè)置為 AUTOINCREMENT 的列達到最大值會發(fā)生什么？

它會停止遞增，任何進一步的插入都將產(chǎn)生錯誤，因為密鑰已被使用。

怎樣找出最后一次插入時分配了哪個自動增量？

LAST_INSERT_ID 將返回由 Auto_increment 分配的最后一個值，并且不需要指定表名稱。

Mysql 中的存儲引擎？

存儲引擎	描述
ARCHIVE	用于數(shù)據(jù)存檔的引擎，數(shù)據(jù)被插入后就不能在修改了，且不支持索引。
CSV	在存儲數(shù)據(jù)時，會以逗號作為數(shù)據(jù)項之間的分隔符。
BLACKHOLE	會丟棄寫操作，該操作會返回空內(nèi)容。
FEDERATED	將數(shù)據(jù)存儲在遠程數(shù)據(jù)庫中，用來訪問遠程表的存儲引擎。
InnoDB	具備外鍵支持功能的事務(wù)處理引擎
MEMORY	置于內(nèi)存的表
MERGE	用來管理由多個 MyISAM 表構(gòu)成的表集合，簡稱為索引順序訪問方法。它是由 IBM 開發(fā)的，用于在磁帶等輔助存儲系統(tǒng)上存儲和檢索數(shù)據(jù)。
MyISAM	主要的非事務(wù)處理存儲引擎
NDB	MySQL 集群專用存儲引擎

MySQL 的默認存儲引擎是 InnoDB。

InnoDB 和 MyISAM 區(qū)別？

	InnoDB	MyISAM
事務(wù)	支持	不支持
主鍵	一定要有	不一定要有
外鍵	支持	不支持
聚簇索引	是	不是
全文索引	不支持（5.7后開始支持）	支持
行數(shù)	不存儲	存儲
行鎖	支持	不支持
表鎖	不支持	支持
存儲文件	frm：表定義文件 ibd：數(shù)據(jù)文件	frm：表定義文件 myd：數(shù)據(jù)文件 myi：索引文件
MVCC（多版本并發(fā)控制）	支持	不支持

• 表主鍵：MyISAM 允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。InnoDB 如果沒有設(shè)定主鍵或者非空唯一索引，就會自動生成一個 6 字節(jié)的主鍵(用戶不可見)，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。
• 可移植性、備份及恢復(fù)：MyISAM 數(shù)據(jù)是以文件的形式存儲，所以在跨平臺的數(shù)據(jù)轉(zhuǎn)移中會很方便。在備份和恢復(fù)時可單獨針對某個表進行操作。InnoDB 免費的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達到幾十 G 的時候就相對痛苦了

MySQL 驅(qū)動程序？

以下是 Mysql 中可用的驅(qū)動程序：

• PHP 驅(qū)動程序
• JDBC 驅(qū)動程序
• ODBC 驅(qū)動程序
• CWRAPPER
• PYTHON 驅(qū)動程序
• PERL 驅(qū)動程序
• RUBY 驅(qū)動程序
• CAP11PHP 驅(qū)動程序
• Ado.net5.mxj

MySQL 數(shù)據(jù)類型？

數(shù)值類型：

類型	大小	范圍（有符號）	范圍（無符號）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整數(shù)值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整數(shù)值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整數(shù)值
INT/INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數(shù)值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數(shù)值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度 浮點數(shù)值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度 浮點數(shù)值
DECIMAL		依賴于M和D的值，對 DECIMAL(M,D) 如果 M>D，為 M+2，否則為 D+2	依賴于M和D的值	小數(shù)值

FLOAT 和 DOUBLE 的區(qū)別：

• FLOAT 以8位精度存儲在 FLOAT 中，并且有四個字節(jié)。
• DOUBLE 存儲在 DOUBLE 中，精度為18位，有八個字節(jié)。

日期和時間類型：

類型	大小	范圍	格式	用途
DATE	3 bytes	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3 bytes	'-838:59:59'/'838:59:59'	HH:MM:SS	時間值或持續(xù)時間
YEAR	1 bytes	1901/2155	YYYY	年份值
DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP	4 bytes	1970-01-01 00:00:00/2038 結(jié)束時間是第 2147483647 秒 北京時間 2038-1-19 11:14:07 格林尼治時間 2038-1-19 03:14:07	YYYYMMDD HHMMSS	混合日期和時間值，時間戳。每當行被更改時，時間戳字段將獲取當前時間戳。

NOW() 和 CURRENT_DATE() 區(qū)別：

NOW() 命令用于顯示當前年份，月份，日期，小時，分鐘和秒。

CURRENT_DATE() 僅顯示當前年份，月份和日期。

字符串類型：

類型	大小	用途
CHAR	0-255 bytes	定長字符串
VARCHAR	0-65535 bytes	變長字符串
TINYBLOB	0-255 bytes	不超過 255 個字符的二進制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二進制形式的長文本數(shù)據(jù)
TEXT	0-65 535 bytes	長文本數(shù)據(jù)
MEDIUMBLOB	0-16 777 215 bytes	二進制形式的中等長度文本數(shù)據(jù)
MEDIUMTEXT	0-16 777 215 bytes	中等長度文本數(shù)據(jù)
LONGBLOB	0-4 294 967 295 bytes	二進制形式的極大文本數(shù)據(jù)
LONGTEXT	0-4 294 967 295 bytes	極大文本數(shù)據(jù)

CHAR 和 VARCHAR 區(qū)別：

• CHAR 是一種固定長度的類型，長度值范圍是1到255，VARCHAR 則是一種可變長度的類型。
• CHAR 和 VARCHAR 類型類似，在存儲和檢索方面有所不同。
• 當 CHAR 值被存儲時，它們被用空格填充到特定長度，檢索 CHAR 值時需刪除尾隨空格。

CHAR_LENGTH 和 LENGTH 區(qū)別：

• CHAR_LENGTH 是字符數(shù)，而 LENGTH 是字節(jié)數(shù)。
• Latin 字符的這兩個數(shù)據(jù)是相同的，但是對于 Unicode 和其他編碼，它們是不同的。

BLOB 和 TEXT 區(qū)別：

BLOB 是一個二進制對象，可以容納可變數(shù)量的數(shù)據(jù)。有四種類型的 BLOB：

• TINYBLOB
• BLOB
• MEDIUMBLOB 和
• LONGBLOB

它們只能在所能容納價值的最大長度上有所不同。

TEXT 是一個不區(qū)分大小寫的 BLOB。四種 TEXT 類型

• TINYTEXT
• TEXT
• MEDIUMTEXT 和
• LONGTEXT

它們對應(yīng)于四種 BLOB 類型，并具有相同的最大長度和存儲要求。

BLOB 和 TEXT 類型之間的唯一區(qū)別在于對 BLOB 值進行排序和比較時區(qū)分大小寫，對 TEXT

值不區(qū)分大小寫。

什么是非標準字符串類型？

以下是非標準字符串類型：

? TINYTEXT

? TEXT

? MEDIUMTEXT

? LONGTEXT

若一張表中只有一個字段 VARCHAR(N)類型，utf8 編碼，則 N 最大值為多少?

由于 utf8 的每個字符最多占用 3 個字節(jié)。而 MySQL 定義行的長度不能超過65535，因此 N 的最大值計算方法為：(65535-1-2)/3。減去 1 的原因是實際存儲從第二個字節(jié)開始，減去 2 的原因是因為要在列表長度存儲實際的字符長度，除以 3 是因為 utf8 限制：每個字符最多占用 3 個字節(jié)。

Mysql 里記錄貨幣用什么類型？

NUMERIC和DECIMAL類型被Mysql實現(xiàn)為同樣的類型，這在SQL92標準允許。他們被用于保存值，該值的準確精度是極其重要的值，例如與金錢有關(guān)的數(shù)據(jù)。當聲明一個類是這些類型之一時，精度和規(guī)模的能被(并且通常是)指定。

例如：salaryDECIMAL(9,2)

在這個例子中，9(precision) 代表將被用于存儲值的總的小數(shù)位數(shù)，而 2(scale) 代表將被用于存儲小數(shù)點后的位數(shù)。因此，在這種情況下，能被存儲在salary列中的值的范圍是從 -9999999.99 到 9999999.99。在 ANSI/ISOSQL92 中，句法 DECIMAL(p) 等價于 DECIMAL(p,0)。同樣，句法 DECIMAL 等價于 DECIMAL(p,0)，這里實現(xiàn)被允許決定值p。Mysql當前不支持 DECIMAL/NUMERIC 數(shù)據(jù)類型的這些變種形式的任一種。

這一般說來不是一個嚴重的問題，因為這些類型的主要益處得自于明顯地控制精度和規(guī)模的能力。DECIMAL 和 NUMERIC 值作為字符串存儲，而不是作為二進制浮點數(shù)，以便保存那些值的小數(shù)精度。一個字符用于值的每一位、小數(shù)點(如果 scale>0)和 “-” 符號(對于負值)。如果 scale 是0，DECIMAL 和 NUMERIC 值不包含小數(shù)點或小數(shù)部分。

DECIMAL 和 NUMERIC 值得最大的范圍與 DOUBLE 一樣，但是對于一個給定的 DECIMAL 或 NUMERIC 列，實際的范圍可由制由給定列的 precision 或 scale 限制。

當這樣的列賦給了小數(shù)點后面的位超過指定 scale 所允許的位的值，該值根據(jù) scale 四舍五入。

當一個 DECIMAL 或 NUMERIC 列被賦給了其大小超過指定(或缺省的）precision 和 scale 隱含的范圍的值，Mysql 存儲表示那個范圍的相應(yīng)的端點值。

MySQL 中 ENUM 的用法？

ENUM 是一個字符串對象，用于指定一組預(yù)定義的值，并可在創(chuàng)建表時使用。

Create table size(nameENUM('Smail,'Medium','Large');

MySQL 索引

什么是索引？

索引是一種用于快速查詢和檢索數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。常見的索引結(jié)構(gòu)有: B 樹， B+樹和 Hash。

索引的作用就相當于目錄的作用。其本身是一種特殊的文件，它們包含著對數(shù)據(jù)表里所有記錄的引用指針，會占據(jù)一定的物理空間。

索引的優(yōu)缺點？

• 優(yōu)點 ：使用索引可以大大加快數(shù)據(jù)的檢索速度（大大減少檢索的數(shù)據(jù)量）
• 缺點 ：創(chuàng)建索引和維護索引需要耗費許多時間。當對表中的數(shù)據(jù)進行增刪改的時候，如果數(shù)據(jù)有索引，那么索引也需要動態(tài)的修改，會降低 SQL 執(zhí)行效率。另外，索引需要使用物理文件存儲，也會耗費一定空間。

索引的基本原理？

把無序的數(shù)據(jù)變成有序的查詢

• 把創(chuàng)建了索引的列的內(nèi)容進行排序
• 對排序結(jié)果生成倒排表
• 在倒排表內(nèi)容上拼上數(shù)據(jù)地址鏈
• 在查詢的時候，先拿到倒排表的內(nèi)容，再取出數(shù)據(jù)地址鏈，從而拿到具體數(shù)據(jù)

索引的類型？

索引類型?	索引描述
普通索引 (Index)	用來快速查詢數(shù)據(jù)，一張表允許創(chuàng)建多個普通索引，并允許數(shù)據(jù)重復(fù)和 NULL。
唯一索引 (Unique Key)	可以保證數(shù)據(jù)記錄的唯一性，允許數(shù)據(jù)為 NULL，一張表允許創(chuàng)建多個唯一索引。 建立唯一索引的目的大部分時候都是為了該屬性列的數(shù)據(jù)的唯一性，而不是為了查詢效率。
主鍵索引 (Primary Key)	數(shù)據(jù)表的主鍵列使用的就是主鍵索引。是一種特殊的唯一索引，一張數(shù)據(jù)表有只能有一個主鍵，并且主鍵不能為 null，不能重復(fù)。
前綴索引 (Prefix)	前綴索引也叫局部索引，前綴索引只適用于字符串類型的數(shù)據(jù)。前綴索引是對文本的前幾個字符創(chuàng)建索引，相比普通索引建立的數(shù)據(jù)更小， 因為只取前幾個字符。（前綴的標識度高。比如密碼就適合建立前綴索引，因為密碼幾乎各不相同）
全文索引 (Full Text)	全文索引主要是為了檢索大文本數(shù)據(jù)中的關(guān)鍵字的信息，通過建立倒排索引，可以極大提升索引效率，解決判斷字段是否包含問題，是目前搜索引擎使用的關(guān)鍵技術(shù)。
組合索引	多列值組成一個索引，專門用于組合搜索。

唯一索引比普通索引快嗎？

對于寫多讀少的情況， 普通索引利用 change buffer 有效減少了對磁盤的訪問次數(shù)，而唯一索引需要校驗唯一性，此時普通索引性能要高于唯一索引。

聚集索引與非聚集索引？

兩者都是B+樹的數(shù)據(jù)結(jié)構(gòu)，依賴于有序的數(shù)據(jù)。

聚集索引：聚集索引即索引結(jié)構(gòu)和數(shù)據(jù)一起存放，并按一定的順序進行排序的索引，找到了索引就找到了數(shù)據(jù)。主鍵索引屬于聚集索引。

• 優(yōu)點：
  • 聚集索引的范圍查詢速度非常的快，因為整個 B+樹本身就是一顆多叉平衡樹，葉子節(jié)點也都是有序的，定位到索引的節(jié)點，就相當于定位到了數(shù)據(jù)。
• 缺點：
  • 依賴于有序的數(shù)據(jù) ：因為 B+樹是多路平衡樹，如果索引的數(shù)據(jù)不是有序的，那么就需要在插入時排序，如果數(shù)據(jù)是整型還好，否則類似于字符串或 UUID 這種又長又難比較的數(shù)據(jù)，插入或查找的速度肯定比較慢。
  • 更新代價大： 如果對索引列的數(shù)據(jù)被修改時，那么對應(yīng)的索引也將會被修改， 而且聚集索引的葉子節(jié)點還存放著數(shù)據(jù)，修改代價肯定是較大的， 所以對于主鍵索引來說，主鍵一般都是不可被修改的。

非聚集索引：非聚集索引即索引結(jié)構(gòu)和數(shù)據(jù)分開存放的索引。非聚集索引的葉子節(jié)點并不存放數(shù)據(jù)，存儲的數(shù)據(jù)行地址，根據(jù)數(shù)據(jù)行地址再回表查數(shù)據(jù)。

• 優(yōu)點：
  • 更新代價比聚集索引要小 。非聚集索引的更新代價就沒有聚集索引那么大了，非聚集索引的葉子節(jié)點是不存放數(shù)據(jù)的
• 缺點：
  • 跟聚集索引一樣，非聚集索引也依賴于有序的數(shù)據(jù)
  • 可能會二次查詢(回表) ：這應(yīng)該是非聚集索引最大的缺點了。 當查到索引對應(yīng)的指針或主鍵后，可能還需要根據(jù)指針或主鍵再到數(shù)據(jù)文件或表中查詢。

MyISAM：采用非聚集索引， 索引文件的數(shù)據(jù)域存儲指向數(shù)據(jù)文件的指針。輔索引與主索引基本一致， 但是輔索引不用保證唯一性。

InnoDB：主鍵索引采用聚集索引（ 索引的數(shù)據(jù)域存儲數(shù)據(jù)文件本身）， 輔助索引的數(shù)據(jù)域存儲主鍵的值； 因此從輔助索引查找數(shù)據(jù)， 需要先通過輔助索引找到主鍵值， 再訪問輔助索引； 最好使用自增主鍵， 防止插入數(shù)據(jù)時， 為維持 B+樹結(jié)構(gòu)， 文件的大調(diào)整。

最左前綴匹配原則？

最左前綴匹配原則：最左優(yōu)先，在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求，where 子句中使用最頻繁的一列放在最左邊。

mysql 會一直從左向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。=?和?in?可以亂序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql 的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式。

索引失效的情況？

通過 explain 顯示出 MySQL 執(zhí)行的字段內(nèi)容：

• id：SELECT 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符
• select_type：SELECT 查詢的類型
• table：查詢的是哪個表
• partitions：匹配的分區(qū)
• type：join 類型
• possible_keys：此次查詢中可能選用的索引
• key：此次查詢中確切使用到的索引
• ref：哪個字段或常數(shù)與 key 一起被使用
• rows：顯示此查詢一共掃描了多少行，這個是一個估計值
• filtered：表示此查詢條件所過濾的數(shù)據(jù)的百分比
• extra：額外的信息

（1）like 以 % 開頭，索引無效；當 like 前綴沒有 %，后綴有 % 時，索引有效。

（2）or 語句前后沒有同時使用索引。

當 or 左右查詢字段只有一個是索引，該索引失效，只有當or左右查詢字段均為索引時，才會生效。

（3）組合索引，不是使用第一列索引，索引失效。

（4）數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)化。

如 varchar 不加單引號的話可能會自動轉(zhuǎn)換為 int 型，使索引無效，產(chǎn)生全表掃描。

（5）在索引字段上使用 not，<>，!=。

不等于操作符是永遠不會用到索引的，因此對它的處理只會產(chǎn)生全表掃描。 優(yōu)化方法： key <> 0 改為 key > 0 or key < 0。

（6）對索引字段進行計算操作、字段上使用函數(shù)。

（7）其他

• 當全表掃描速度比索引速度快時，MySQL 會使用全表掃描，此時索引失效。
• 左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣。
• 使用 NOT IN、NOT EXISTS 導(dǎo)致索引失效
• 在索引列上使用 IS NULL 或 IS NOT NULL 操作有可能會導(dǎo)致索引失效，IS NULL 不走索引，IS NOT NULL 走索引

可以使用多少列創(chuàng)建索引？

任何標準表最多可以創(chuàng)建16個索引列。

MySQL 事務(wù)

事務(wù)的特性？

ACID：原子性(Atomicity)、一致性(Correspondence)、隔離性(Isolation)、持久性(Durability)。

• 原子性：整個事務(wù)中的所有操作，要么全部成功，要么全部失敗，不可能停滯在中間某個環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯誤，會被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過一樣。
• 一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。
• 隔離性：隔離狀態(tài)執(zhí)行事務(wù)，使它們好像是系統(tǒng)在給定時間內(nèi)執(zhí)行的唯一操作。如果有兩個事務(wù)，運行在相同的時間內(nèi)，執(zhí)行 相同的功能，事務(wù)的隔離性將確保每一事務(wù)在系統(tǒng)中認為只有該事務(wù)在使用系統(tǒng)。這種屬性有時稱為串行化，為了防止事務(wù)操作間的混淆，必須串行化或序列化請求，使得在同一時間僅有一個請求用于同一數(shù)據(jù)。
• 持久性：在事務(wù)完成以后，該事務(wù)所對數(shù)據(jù)庫所作的更改便持久的保存在數(shù)據(jù)庫之中，并不會被回滾。

事務(wù)的分類？

事務(wù)可以分為很多中類型，一般分為：扁平事務(wù)、帶有保存點的扁平事務(wù)、鏈事務(wù)、嵌套事務(wù)、分布式事務(wù)。

（1）扁平事務(wù)：

扁平事務(wù)是最簡單的一種，在實際開發(fā)中也是使用的最多的一種事務(wù)。在這種事務(wù)中，所有操作都處于同一層次，最常見的方式如下：

BEGIN WORK

Operation 1

Operation 2

Operation 3

...

Operation N

COMMIT WORK

// 示例

begin work;

select * from user;

update user set name = 'sihai' where id = 1;

commit work;

扁平事務(wù)的主要缺點是不能提交或回滾事務(wù)的某一部分，或者分幾個獨立的步驟去提交。

（2）帶有保存點的扁平事務(wù)

這種事務(wù)除了支持扁平事務(wù)支持的操作外，這種事務(wù)跟扁平事務(wù)最大的區(qū)別就是允許在事務(wù)執(zhí)行過程中回滾到同一事務(wù)中較早的一個狀態(tài)，這是因為可能某些事務(wù)在執(zhí)行過程中出現(xiàn)的錯誤并不會對所有的操作都無效，放棄整個事務(wù)不合乎要求，開銷也太大。保存點用來通知系統(tǒng)應(yīng)該記住事務(wù)當前的狀態(tài)，以便以后發(fā)生錯誤時，事務(wù)能回到該狀態(tài)。

示例：

begin work;

select * from user;

savepoint t1;

update user set name = 'sihai' where id = 1;

savepoint t2;

commit work;

通過上面的方式我們就建立了兩個保存點 t1、t2，通過ROLLBACK TO SAVEPOINT t1，就可以返回到保存點 t1。

（2）鏈事務(wù)

鏈事務(wù)：在提交一個事務(wù)時，釋放不需要的數(shù)據(jù)對象，將必要的處理上下文隱式的傳給下一個要開始的事務(wù)。需要注意，提交事務(wù)操作和下一個事務(wù)操作將合并為一個原子操作，就是下一個事務(wù)可以看到上一個事務(wù)的結(jié)果。

鏈事務(wù)，就是指回滾時，只能恢復(fù)到最近一個保存點；而帶有保存點的扁平事務(wù)則可以回滾到任意正確的保存點。

示例：

begin work;

select * from user;

savepoint t1;

update user set name = 'sihai' where id = 1;

savepoint t2;

commit work;

還是這個例子，但是對于鏈事務(wù)來說，是不能直接rollback到保存點t1的，最能恢復(fù)到最近的一個保存點t2；另外我們需要注意，鏈事務(wù)在執(zhí)行 commit 后就會釋放當前事務(wù)所持有的所有鎖，而帶有保存點的扁平事務(wù)不會影響所持有的鎖。

（3）嵌套事務(wù)

在事務(wù)中再嵌套事務(wù)，這種結(jié)構(gòu)有點像一顆橫著的樹的結(jié)構(gòu)，位于根節(jié)點的事務(wù)稱為頂層事務(wù)。事務(wù)的前驅(qū)稱為父事務(wù)，其它事務(wù)稱為子事務(wù)。事務(wù)的前驅(qū)稱為父事務(wù)，事務(wù)的下一層稱為子事務(wù)。

子事務(wù)既可以提交也可以回滾，但是它的提交操作并不馬上生效，除非由其父事務(wù)提交。因此就可以確定，任何子事務(wù)都在頂層事務(wù)提交后才真正的被提交了。同理，任意一個事務(wù)的回滾都會引起它的所有子事務(wù)一同回滾。

BEGIN WORK

SubTransaction1:

BEGIN WORK

SubOperationX

COMMIT WORK

SubTransaction2:

BEGIN WORK

SubOperationY

COMMIT WORK

...

SubTransactionN:

BEGIN WORK

SubOperationN

COMMIT WORK

COMMIT WORK

（4）分布式事務(wù)

分布式事務(wù)通常是指在一個分布式環(huán)境下運行的扁平事務(wù)，因此需要根據(jù)數(shù)據(jù)所在位置訪問網(wǎng)絡(luò)中的不同節(jié)點。

在不同的物理地址，通過網(wǎng)絡(luò)訪問，執(zhí)行不同的事務(wù)，這就是分布式事務(wù)。

事務(wù)隔離級別？

數(shù)據(jù)庫事務(wù)的隔離級別有4個，由低到高依次為

1. Read uncommitted：讀到未提交數(shù)據(jù)
2. Read committed：臟讀，不可重復(fù)讀
3. Repeatable read：可重讀
4. Serializable：串行事物

這四個級別可以逐個解決臟讀 、不可重復(fù)讀 、幻讀這幾類問題。

	臟讀	不可重復(fù)讀	幻讀
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

臟讀 、不可重復(fù)讀 、幻讀？

（1）臟讀

臟讀發(fā)生在一個事務(wù)A讀取了被另一個事務(wù)B修改，但是還未提交的數(shù)據(jù)。假如B回退，則事務(wù)A讀取的是無效的數(shù)據(jù)。這跟不可重復(fù)讀類似，但是第二個事務(wù)不需要執(zhí)行提交。

（2）不可重復(fù)讀

在基于鎖的并行控制方法中，如果在執(zhí)行 select 時不添加讀鎖，就會發(fā)生不可重復(fù)讀問題。

在多版本并行控制機制中，當一個遇到提交沖突的事務(wù)需要回退但卻被釋放時，會發(fā)生不可重復(fù)讀問題。

在上面這個例子中，事務(wù)2提交成功，它所做的修改已經(jīng)可見。然而，事務(wù)1已經(jīng)讀取了一個其它的值。在序列化和可重復(fù)讀的隔離級別中，數(shù)據(jù)庫管理系統(tǒng)會返回舊值，即在被事務(wù)2修改之前的值。在提交讀和未提交讀隔離級別下，可能會返回被更新的值，這就是“不可重復(fù)讀”。

有兩個策略可以防止這個問題的發(fā)生：

• 推遲事務(wù)2的執(zhí)行，直至事務(wù)1提交或者回退。這種策略在使用鎖時應(yīng)用。
• 而在多版本并行控制中，事務(wù)2可以被先提交。而事務(wù)1繼續(xù)執(zhí)行在舊版本的數(shù)據(jù)上。當事務(wù)1嘗試提交時，數(shù)據(jù)庫會檢驗它的結(jié)果是否和事務(wù)1、事務(wù)2順序執(zhí)行時一樣。如果是，則事務(wù)1提交成功。如果不是，事務(wù)1會被回退。

（3）幻讀

幻讀發(fā)生在當兩個完全相同的查詢執(zhí)行時，第二次查詢所返回的結(jié)果集跟第一個查詢不相同。

發(fā)生的情況：沒有范圍鎖。

原子性、一致性和持久性實現(xiàn)原理？

原子性、穩(wěn)定性和持久性是通過 redo 和 undo 日志文件實現(xiàn)的，不管是 redo 還是 undo 文件都會有一個緩存我們稱之為 redo_buf 和 undo_buf。同樣，數(shù)據(jù)庫文件也會有緩存稱之為 data_buf。

undo 記錄了數(shù)據(jù)在事務(wù)開始之前的值，當事務(wù)執(zhí)行失敗或者 ROLLBACK 時可以通過 undo 記錄的值來恢復(fù)數(shù)據(jù)。

redo 日志記錄數(shù)據(jù)修改后的值，可以避免數(shù)據(jù)在事務(wù)提交之前必須寫入到磁盤的需求，減少 I/O。

MySQL 鎖

鎖的兩個概念?

（1）Lock

Lock 的對象是事務(wù)，用來鎖定的是數(shù)據(jù)庫中的對象，如表、頁、行。并且一般 Lock 的對象僅在事務(wù) commit 或 rollback 后進行釋放（不同事務(wù)隔離級別釋放的時間可能不同）。

查看方式：

show engine innodb status

（2）Latch

Latch 稱為閂鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短，若持續(xù)的時間長，則應(yīng)用的性能會非常差。在InnoDB引擎中，Latch 又可以分為 mutex（互斥量）和 rwlock（讀寫鎖）。其目的是用來保證并發(fā)線程操作臨界資源的正確性，并且通常沒有死鎖檢測的機制。

show engine innodb mutex

Lock 與 Latch 比較

	Lock	Latch
對象	事務(wù)	線程
保護	數(shù)據(jù)庫內(nèi)容	內(nèi)存數(shù)據(jù)結(jié)構(gòu)
持續(xù)時間	整個事務(wù)過程	臨界資源
模式	行鎖、表鎖鎖、意向鎖	讀寫鎖、互斥鎖
死鎖	通過 waits-for graph、time out 等機制進行死鎖檢測與處理	無死鎖檢測機制。僅通過應(yīng)用程序加鎖的順序保證無死鎖發(fā)生
存在于	Lock Manager 的哈希表中	每個數(shù)據(jù)結(jié)構(gòu)的對象中

MySQL 鎖分類？

基于鎖的屬性	共享鎖	共享鎖（Shared Lock）又稱讀鎖，簡稱S鎖。 當一個事務(wù)對數(shù)據(jù)加上讀鎖之后，其他事務(wù)只能對該數(shù)據(jù)加讀鎖，而不能加寫鎖，直到所有的讀鎖釋放之后其他事務(wù)才能對該數(shù)據(jù)加寫鎖。 共享鎖主要是為了支持并發(fā)讀取數(shù)據(jù)，讀取數(shù)據(jù)的時候不支持修改，避免出現(xiàn)重復(fù)讀的問題。
	排他鎖	排他鎖（Exclusive Lock）又稱寫鎖，簡稱X鎖。 當一個事務(wù)對數(shù)據(jù)加上寫鎖之后，其他事務(wù)將不能對該數(shù)據(jù)加任何鎖，直到該鎖釋放。 排他鎖的目的是在對數(shù)據(jù)修改時，不允許其他事務(wù)同時修改和讀取，避免出現(xiàn)臟讀和幻讀的問題。
基于鎖的狀態(tài)	意向共享鎖
	意向排他鎖
基于鎖的粒度	行級鎖	鎖定表中的一行或多行，沒有被鎖定的數(shù)據(jù)行可以正常訪問。 特點：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。 使用行級鎖：InnoDB
	頁級鎖	頁級鎖定的特點是鎖定顆粒度介于行級鎖定與表級鎖之間。 特點：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。 使用頁級鎖定的主要是 BerkeleyDB 存儲引擎。
	表級鎖	鎖定整個表。 特點：粒度大、加鎖解鎖簡單、不會出現(xiàn)死鎖，容易發(fā)生鎖沖突、并發(fā)性能低。 使用表級鎖的主要是 MyISAM，MEMORY，CSV 等一些非事務(wù)性存儲引擎。
	記錄鎖	記錄鎖屬于行鎖的一種，只不過記錄鎖的鎖定范圍只有表中的某一行。
	間隙鎖	間隙鎖屬于行鎖的一種，間隙鎖鎖住的是一個區(qū)間，而不僅僅是這個區(qū)間中的每一條數(shù)據(jù)。間隙鎖 是 InnoDB 在 RR(可重復(fù)讀) 隔離級別 下為了解決幻讀問題時引入的鎖機制。
	臨鍵鎖	臨鍵鎖屬于行鎖的一種，并且是 InnoDB 行鎖的默認算法。 臨鍵鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。 通過臨建鎖可以解決幻讀的問題。 每個數(shù)據(jù)行上的非唯一索引列上都會存在一把臨鍵鎖，當某個事務(wù)持有該數(shù)據(jù)行的臨鍵鎖時，會鎖住一段左開右閉區(qū)間的數(shù)據(jù)。 臨鍵鎖只與非唯一索引列有關(guān)，在唯一索引列（包括主鍵列）上不存在臨鍵鎖。
基于加鎖機制	樂觀鎖
	悲觀鎖

S or X (共享鎖、排他鎖)的兼容性：

	S鎖	X鎖
S鎖	√	×
X鎖	×	×

IS or IX (共享、排他)意向鎖的兼容性

	IS鎖	IX鎖	S鎖	X鎖
IS鎖	√	√	√	×
IX鎖	√	√	×	×
S鎖	√	×	√	×
X鎖	×	×	×	×

一致性鎖定讀/非鎖定讀？

（1）一致性鎖定讀（Locking Reads）

在一個事務(wù)中查詢數(shù)據(jù)時，普通的 SELECT 語句不會對查詢的數(shù)據(jù)進行加鎖，其他事務(wù)仍可以對查詢的數(shù)據(jù)執(zhí)行更新和刪除操作。因此，InnoDB 提供了兩種類型的鎖定讀來保證額外的安全性：

1. SELECT ... LOCK IN SHARE MODE：對讀取的行添加S鎖，其他事物可以對這些行添加S鎖，若添加X鎖，則會被阻塞。
2. SELECT ... FOR UPDATE：會對查詢的行及相關(guān)聯(lián)的索引記錄加X鎖，其他事務(wù)請求的S鎖或X鎖都會被阻塞。當事務(wù)提交或回滾后，通過這兩個語句添加的鎖都會被釋放。注意：只有在自動提交被禁用時，SELECT FOR UPDATE才可以鎖定行，若開啟自動提交，則匹配的行不會被鎖定。

一致性鎖定讀必須在一個事物中，當事務(wù)提交了，鎖就釋放了，因此使用這兩種方式加鎖的語句，必須加上begin、start transaction或者set autocommit = 0。

（2）一致性非鎖定讀（consistent nonlocking read）

一致性非鎖定讀是指 InnoDB 存儲引擎通過多版本控制(MVVC)讀取當前數(shù)據(jù)庫中行數(shù)據(jù)的方式。如果讀取的行正在執(zhí)行 DELETE 或 UPDATE 操作，這時讀取操作不會因此去等待行上鎖的釋放。相反地，InnoDB 會去讀取行的一個快照。所以，非鎖定讀機制大大提高了數(shù)據(jù)庫的并發(fā)性。

一致性非鎖定讀是 InnoDB 默認的讀取方式，即讀取不會占用和等待行上的鎖。在事務(wù)隔離級別READ COMMITTED和REPEATABLE READ下，InnoDB 使用一致性非鎖定讀。

然而，對于快照數(shù)據(jù)的定義卻不同。在READ COMMITTED事務(wù)隔離級別下，一致性非鎖定讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)。而在REPEATABLE READ事務(wù)隔離級別下，則讀取事務(wù)開始時的行數(shù)據(jù)版本。

行鎖的算法？

InnoDB 存儲引擎有3種行鎖的算法，其分別是：

1. Record Lock
   1. 記錄鎖（行鎖），單條索引記錄上加鎖，鎖住的永遠是索引，而非記錄本身。
   2. 行鎖的實現(xiàn)依賴于索引，一旦某個加鎖操作沒有使用到索引，那么該鎖就會退化為表鎖。記錄鎖存在于包括主鍵索引在內(nèi)的唯一索引中，鎖定單條索引記錄。
2. Gap Lock
   1. 間隙鎖，在索引記錄之間的間隙中加鎖（鎖定一個范圍），并不包括該索引記錄本身。
   2. 間隙鎖存在于非唯一索引中，鎖定開區(qū)間范圍內(nèi)的一段間隔，它是基于臨鍵鎖實現(xiàn)的。
3. Next-Key Lock
   1. 臨鍵鎖，Record lock 和 Gap lock 的結(jié)合，既除了鎖住記錄本身，也鎖住索引之間的間隙（一個范圍）。
   2. 臨鍵鎖存在于非唯一索引中，該類型的每條記錄的索引上都存在這種鎖，它是一種特殊的間隙鎖，鎖定一段左開右閉的索引區(qū)間。

舉個例子10，20，30，那么該索引可能被 Next-Key Locking 的區(qū)間為：

除了 Next-Key Locking，還有 Previous-Key Locking 技術(shù)，這種技術(shù)跟 Next-Key Lock正好相反，鎖定的區(qū)間是區(qū)間范圍和前一個值（左閉右開）。同樣上述的值，使用 Previous-Key Locking 技術(shù)，那么可鎖定的區(qū)間為：

不是所有索引都會加上 Next-key Lock 的，這里有一種特殊的情況，在查詢的列是唯一索引（包含主鍵索引）的情況下，Next-key Lock 會降級為 Record Lock。

用戶可以通過以下兩種方式來顯示的關(guān)閉 Gap Lock：

1. 將事務(wù)的隔離級別設(shè)為READ COMMITED。
2. 將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1。

Gap Lock 的作用：是為了阻止多個事務(wù)將記錄插入到同一個范圍內(nèi)，設(shè)計它的目的是用來解決 Phontom Problem（幻讀問題）。在MySQL默認的隔離級別（Repeatable Read）下，InnoDB 就是使用它來解決幻讀問題。

鎖的優(yōu)化策略？

• 讀寫分離
• 分段加鎖
• 減少鎖持有的時間

MySQL 優(yōu)化

數(shù)據(jù)庫設(shè)計方面

1. 范式優(yōu)化： 比如消除冗余
2. 反范式優(yōu)化：比如適當加冗余等（減少 join）
3. 拆分表： 分區(qū)將數(shù)據(jù)在物理上分隔開，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。拆分又分垂直拆分和水平拆分
4. 首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
5. 盡量不在包含大量重復(fù)值的字段上建立索引，如性別 sex。
6. 索引不是越多越好，一般考慮在6個左右。
7. 應(yīng)在查詢較多的字段上建立索引，而不是在頻繁更新的字段上建立索引。
8. 字段的值只有數(shù)值型時，應(yīng)設(shè)計為數(shù)字型字段。因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。
9. 盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
10. 盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）。
11. 避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗。
12. 臨時表并不是不可使用，適當?shù)厥褂盟鼈兛梢允鼓承├谈行?#xff0c;例如，當需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是，對于一次性事件，最好使用導(dǎo)出表。
13. 在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先 create table，然后insert。
14. 如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。

SQL 方面

1. 不要使用 select * from t，用具體的字段列表代替*，不要返回用不到的任何字段。
2. 盡量避免在 where 子句中對字段進行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描。
3. 盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
4. 盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，可以用 union 代替 （Innodb）。
5. 盡量避免在 where 子句中對字段進行表達式操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。
6. 盡量避免在 where 子句中對字段進行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。
7. 慎用 in 和 not in，否則會可能導(dǎo)致全表掃描，可以用 exists 代替 in。
8. 用 where 子句替換 having 子句 因為 having 只會在檢索出所有記錄之后才對結(jié)果集進行過濾。
9. 避免使用隱式轉(zhuǎn)換：如 varchar 類型字段在用where name = 1，而要用where name = '1'，否則MySQL 會自動進行數(shù)據(jù)類型轉(zhuǎn)換再執(zhí)行，函數(shù)作用于表字段時，索引就會失效。
10. 使用 like 時，應(yīng)使用右模糊 name like 'zhang%'，而不是name like '%zhang%'或者name like '%zhang'。
11. 排序請盡量使用升序 。
12. 復(fù)合索引高選擇性的字段排在前面。
13. 根據(jù)聯(lián)合索引的第二個及以后的字段單獨查詢用不到索引。
14. 字符串比較長的可以考慮索引一部份減少索引文件大小，提高寫入效率。
15. 如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描。因為SQL只有在運行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描： select id from t where num=@num 可以改為強制查詢使用索引： select id from t with(index(索引名)) where num=@num。
16. 盡量避免使用游標，因為游標的效率較差，如果游標操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。
17. 盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。
18. 盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

Java 方面

1. 盡可能的少造對象。
2. 合理擺正系統(tǒng)設(shè)計的位置。大量數(shù)據(jù)操作，和少量數(shù)據(jù)操作一定是分開的。大量的數(shù)據(jù)操作，肯定不是ORM框架搞定的。使用jDBC鏈接數(shù)據(jù)庫操作數(shù)據(jù)
3. 控制好內(nèi)存，讓數(shù)據(jù)流起來，而不是全部讀到內(nèi)存再處理，而是邊讀取邊處理；
4. 合理利用內(nèi)存，有的數(shù)據(jù)要緩存

MySQL 底層

B 樹和 B+ 樹的區(qū)別？

• B 樹也稱 B-樹，全稱為多路平衡查找樹 ，在B樹中，所有節(jié)點既存放鍵(key) 也存放數(shù)據(jù)(data)，葉子節(jié)點各自獨立。
• B+樹是B樹的一種變體，內(nèi)部節(jié)點都是鍵(key)，沒有值，葉子節(jié)點同時存放鍵(key)和值(value)。而且所有的葉子結(jié)點中增加了指向下一個葉子節(jié)點的指針， 因此 InnoDB 建議為大部分表使用默認自增的主鍵作為主索引。

B 樹的優(yōu)點？

• B樹可以在內(nèi)部節(jié)點同時存儲鍵和值，因此，把頻繁訪問的數(shù)據(jù)放在靠近根節(jié)點的地方將會大大提高熱點數(shù)據(jù)的查詢效率。這種特性使得B樹在特定數(shù)據(jù)重復(fù)多次查詢的場景中更加高效。

B+ 樹的優(yōu)點？

• 由于B+樹的內(nèi)部節(jié)點只存放鍵，不存放值，因此，一次讀取，可以在內(nèi)存頁中獲取更多的鍵，有利于更快地縮小查找范圍。 B+樹的葉節(jié)點由一條鏈相連，因此，當需要進行一次全數(shù)據(jù)遍歷的時候，B+樹只需要使用O(logN)時間找到最小的一個節(jié)點，然后通過鏈進行O(N)的順序遍歷即可（檢索效率很穩(wěn)定）。而B樹則需要對樹的每一層進行遍歷(當于對范圍內(nèi)的每個節(jié)點的關(guān)鍵字做二分查找)，這會需要更多的內(nèi)存置換次數(shù)，因此也就需要花費更多的時間

數(shù)據(jù)庫為什么使用 B+ 樹而不是 B 樹？

• B樹只適合隨機檢索，而B+樹同時支持隨機檢索和順序檢索；
• B+樹空間利用率更高，可減少I/O次數(shù)，磁盤讀寫代價更低。B+樹的內(nèi)部結(jié)點并沒有指向關(guān)鍵字具體信息的指針，只是作為索引使用，其內(nèi)部結(jié)點比B樹小，盤塊能容納的結(jié)點中關(guān)鍵字數(shù)量更多，一次性讀入內(nèi)存中可以查找的關(guān)鍵字也就越多，相對的，IO讀寫次數(shù)也就降低了。
• B+樹的查詢效率更加穩(wěn)定，B樹搜索有可能會在非葉子結(jié)點結(jié)束，越靠近根節(jié)點的記錄查找時間越短，只要找到關(guān)鍵字即可確定記錄的存在，其性能等價于在關(guān)鍵字全集內(nèi)做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關(guān)鍵字的查找都必須走一條從根節(jié)點到葉節(jié)點的路，所有關(guān)鍵字的查找路徑長度相同，導(dǎo)致每一個關(guān)鍵字的查詢效率相當。
• B-樹在提高了磁盤IO性能的同時并沒有解決元素遍歷的效率低下的問題。B+樹的葉子節(jié)點使用指針順序連接在一起，只要遍歷葉子節(jié)點就可以實現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的，而B樹不支持這樣的操作。
• 增刪文件（節(jié)點）時，效率更高。因為B+樹的葉子節(jié)點包含所有關(guān)鍵字，并以有序的鏈表結(jié)構(gòu)存儲，這樣可很好提高增刪效率。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作為索引結(jié)構(gòu)，但是，兩者的實現(xiàn)方式不太一樣。

• MyISAM 引擎中，B+Tree 葉節(jié)點的 data 域存放的是數(shù)據(jù)記錄的地址。在索引檢索的時候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，則取出其 data 域的值，然后以 data 域的值為地址讀取相應(yīng)的數(shù)據(jù)記錄。這被稱為“非聚簇索引"。
• InnoDB 引擎中，其數(shù)據(jù)文件本身就是索引文件。相比 MyISAM，索引文件和數(shù)據(jù)文件是分離的，其表數(shù)據(jù)文件本身就是按 B+Tree 組織的一個索引結(jié)構(gòu)，樹的葉節(jié)點 data 域保存了完整的數(shù)據(jù)記錄。這個索引的 key 是數(shù)據(jù)表的主鍵，因此 InnoDB 表數(shù)據(jù)文件本身就是主索引。這被稱為“聚簇索引（或聚集索引）"，而其余的索引都作為輔助索引，輔助索引的 data 域存儲相應(yīng)記錄主鍵的值而不是地址，這也是和 MyISAM 不同的地方。在根據(jù)主索引搜索時，直接找到 key 所在的節(jié)點即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時，則需要先取出主鍵的值，在走一遍主索引。 因此，在設(shè)計表的時候，不建議使用過長的字段作為主鍵，也不建議使用非單調(diào)的字段作為主鍵，這樣會造成主索引頻繁分裂。

Hash 索引與 B+ 樹？

在大多數(shù)情況下，直接選擇B+樹索引可以獲得穩(wěn)定且較好的查詢速度，而不需要使用hash索引。

• hash 索引底層就是 hash 表，進行查找時，調(diào)用一次 hash 函數(shù)就可以獲取到相應(yīng)的鍵值，之后進行回表查詢獲得實際數(shù)據(jù)。B+樹底層實現(xiàn)是多路平衡查找樹，對于每一次的查詢都是從根節(jié)點出發(fā)，查找到葉子節(jié)點方可以獲得所查鍵值，然后根據(jù)查詢判斷是否需要回表查詢數(shù)據(jù)。
• hash 索引進行等值查詢更快(一般情況下)，但是卻無法進行范圍查詢。因為在 hash 索引中經(jīng)過 hash 函數(shù)建立索引之后，索引的順序與原順序無法保持一致，不能支持范圍查詢。而B+樹的的所有節(jié)點皆遵循(左節(jié)點小于父節(jié)點，右節(jié)點大于父節(jié)點，多叉樹也類似)，支持范圍查詢。
• hash 索引任何時候都避免不了回表查詢數(shù)據(jù)，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時候可以只通過索引完成查詢。
• hash 索引雖然在等值查詢上較快，但是不穩(wěn)定。性能不可預(yù)測，當某個鍵值存在大量重復(fù)的時候，發(fā)生 hash 碰撞，此時效率可能極差。而B+樹的查詢效率比較穩(wěn)定，對于所有的查詢都是從根節(jié)點到葉子節(jié)點，且樹的高度較低。

MySQL 綜合

MySQL 中控制內(nèi)存分配的全局參數(shù)?

• Keybuffersize：keybuffersize 指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Keyreadrequests 和 Keyreads，可以知道 keybuffersize 設(shè)置是否合理。比例 keyreads /keyreadrequests 應(yīng)該盡可能的低，至少是1:100，1:1000 更好。keybuffersize 只對 MyISAM 表起作用。即使你不使用 MyISAM 表，但是內(nèi)部的臨時磁盤表是 MyISAM 表，也要使用該值。keybuffersize 設(shè)置注意事項：
  • 單個 keybuffer 的大小不能超過 4G
  • 建議 keybuffer 設(shè)置為物理內(nèi)存的 1/4(針對 MyISAM 引擎)，甚至是物理內(nèi)存的 30%~40%，如果 keybuffersize 設(shè)置太大，系統(tǒng)就會頻繁的換頁，降低系統(tǒng)性能。因為 MySQL 使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，所以我們得為系統(tǒng)留夠足夠的內(nèi)存；在很多情況下數(shù)據(jù)要比索引大得多。
  • 如果機器性能優(yōu)越，可以設(shè)置多個keybuffer,分別讓不同的 keybuffer 來緩存專門的索引
• innodbbufferpool_size：表示緩沖池字節(jié)大小，InnoDB 緩存表和索引數(shù)據(jù)的內(nèi)存區(qū)域。mysql 默認的值是 128M。最大值與你的CPU 體系結(jié)構(gòu)有關(guān)，在 32 位操作系統(tǒng)，最大值是 4294967295(2^32-1) ，在 64 位操作系統(tǒng)，最大值為18446744073709551615 (2^64-1)。在 32 位操作系統(tǒng)中，CPU 和操作系統(tǒng)實用的最大大小低于設(shè)置的最大值。如果設(shè)定的緩沖池的大小大于 1G，設(shè)置 innodbbufferpoolinstances 的值大于 1。數(shù)據(jù)讀寫在內(nèi)存中非?？? innodbbufferpoolsize 減少了對磁盤的讀寫。 當數(shù)據(jù)提交或滿足檢查點條件后才一次性將內(nèi)存數(shù)據(jù)刷新到磁盤中。然而內(nèi)存還有操作系統(tǒng)或數(shù)據(jù)庫其他進程使用, 一般設(shè)置 bufferpool 大小為總內(nèi)存的 3/4 至 4/5。 若設(shè)置不當, 內(nèi)存使用可能浪費或者使用過多。 對于繁忙的服務(wù)器, buffer pool 將劃分為多個實例以提高系統(tǒng)并發(fā)性, 減少線程間讀寫緩存的爭用。buffer pool 的大小首先受 innodbbufferpool_instances 影響, 當然影響較小。
• querycachesize：當 mysql 接收到一條 select 類型的 query時，mysql 會對這條 query 進行 hash 計算而得到一個 hash 值，然后通過該 hash 值到 query cache 中去匹配，如果沒有匹配中，則將這個hash 值存放在一個 hash 鏈表中，同時將 query 的結(jié)果集存放進cache 中，存放 hash 值的鏈表的每一個 hash 節(jié)點存放了相應(yīng) query結(jié)果集在 cache 中的地址，以及該 query 所涉及到的一些 table 的相關(guān)信息；如果通過 hash 值匹配到了一樣的 query，則直接將 cache 中相應(yīng)的 query 結(jié)果集返回給客戶端。如果 mysql 任何一個表中的任何一條數(shù)據(jù)發(fā)生了變化，便會通知 query cache 需要與該 table 相關(guān)的query 的 cache 全部失效，并釋放占用的內(nèi)存地址。query cache優(yōu)缺點
  • query 語句的 hash 計算和 hash 查找?guī)淼馁Y源消耗。mysql 會對每條接收到的 select 類型的 query 進行 hash 計算然后查找該 query 的 cache 是否存在，雖然 hash 計算和查找的效率已經(jīng)足夠高了，一條 query 所帶來的消耗可以忽略，但一旦涉及到高并發(fā)，有成千上萬條 query 時，hash 計算和查找所帶來的開銷就的重視了；
  • query cache 的失效問題。如果表變更比較頻繁，則會造成 query cache 的失效率非常高。表變更不僅僅指表中的數(shù)據(jù)發(fā)生變化，還包括結(jié)構(gòu)或者索引的任何變化；
  • 對于不同 sql 但同一結(jié)果集的 query 都會被緩存，這樣便會造成內(nèi)存資源的過渡消耗。sql 的字符大小寫、空格或者注釋的不同，緩存都是認為是不同的 sql（因為他們的 hash 值會不同）；
  • 相關(guān)參數(shù)設(shè)置不合理會造成大量內(nèi)存碎片，相關(guān)的參數(shù)設(shè)置會稍后介紹。
• readbuffersize：是 MySQL 讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySQL 會為它分配一段內(nèi)存緩沖區(qū)。readbuffersize 變量控制這一緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。

Mysql 表中允許的觸發(fā)器？

在 Mysql 表中允許有六個觸發(fā)器（TRIGGERS），如下：

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

在 Mysql 中運行批處理模式？

以下命令用于在批處理模式下運行：

mysql;

mysqlmysql.out

Mysql 有關(guān)權(quán)限的表？

Mysql 服務(wù)器通過權(quán)限表來控制用戶對數(shù)據(jù)庫的訪問，權(quán)限表存放在 mysql 數(shù)據(jù)庫里，由 mysql_install_db 腳本初始化。這些權(quán)限表分別為 user，db，table_priv，columns_priv 和 host。

MyISAM 表格將存儲在哪里？

每個 MyISAM 表格以三種格式存儲在磁盤上：

? .frm文件存儲表定義

? 數(shù)據(jù)文件具有.MYD（MYData）擴展名

? 索引文件具有.MYI（MYIndex）擴展名

MyISAM Static 和 Dynamic 區(qū)別？

MyISAM Static 上的所有字段有固定寬度。

動態(tài) MyISAM 表將具有像 TEXT，BLOB 等字段，以適應(yīng)不同長度的數(shù)據(jù)類型。

MyISAM Static 在受損情況下更容易恢復(fù)。

myisamchk 是用來做什么的？

它用來壓縮 MyISAM 表，這減少了磁盤或內(nèi)存使用。

什么是訪問控制列表？

ACL（訪問控制列表）是與對象關(guān)聯(lián)的權(quán)限列表，這個列表是Mysql服務(wù)器安全模型的基礎(chǔ)，它有助于排除用戶無法連接的問題。

Mysql將ACL（也稱為授權(quán)表）緩存在內(nèi)存中，當用戶嘗試認證或運行命令時，Mysql會按照預(yù)定的順序檢查ACL的認證信息和權(quán)限。

使用 Unix shell 登錄 Mysql？

通過以下命令登錄：

#[mysqldir]/bin/mysql-hhostname-u<UserName>-p<password>

Unix 和 Mysql 之間時間戳的轉(zhuǎn)換？

UNIX_TIMESTAMP 是從 Mysql 時間戳轉(zhuǎn)換為 Unix 時間戳的命令

FROM UNIXTIME 是從 Unix 時間戳轉(zhuǎn)換為 Mysql 時間戳的命令

federated 表是什么？

federated 表，允許訪問位于其他服務(wù)器數(shù)據(jù)庫上的表。

Mysql中有哪些不同的表格？

共有5種類型的表格：

• MyISAM
• Heap
• Merge
• INNODB
• ISAM

MyISAM 是 Mysql 的默認存儲引擎。

Heap 表是什么？

HEAP 表存在于內(nèi)存中，用于臨時高速存儲。

• BLOB或TEXT字段是不允許的
• 只能使用比較運算符=，<，>，=>，=<
• HEAP表不支持AUTO_INCREMENT
• 索引不可為NULL

如何控制 HEAP 表的最大尺寸？

Heal 表的大小可通過稱為 max_heap_table_size 的 Mysql 配置變量來控制。

MySQL binlog？

binlog 是 mysql 用來記錄數(shù)據(jù)庫表結(jié)構(gòu)變更以及表數(shù)據(jù)修改的的二進制日志，它只會記錄表的變更操作，但不會記錄 select 和 show 這種查詢操作。

binlog 比較常用的場景有以下3種：

1. 數(shù)據(jù)恢復(fù)：誤刪數(shù)據(jù)之后可以通過 mysqlbinlog 工具恢復(fù)數(shù)據(jù)。
2. 主從復(fù)制：主庫將 binlog 傳給從庫，從庫接收到之后讀取內(nèi)容寫入從庫，實現(xiàn)主庫和從庫數(shù)據(jù)一致性。
3. 審計：可以通過二進制日志中的信息進行審計，判斷是否對數(shù)據(jù)庫進行注入攻擊。

binlog 文件包含兩種類型：

1. 索引文件（文件名后綴為.index）用于記錄哪些日志文件正在被使用
2. 日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫所有的DDL和DML(除了數(shù)據(jù)查詢語句)語句事件。

binlog 提供了3種記錄模式：

1. ROW：記錄的是每一行被修改的數(shù)據(jù)
2. STATEMENT：記錄的是執(zhí)行的SQL語句
3. MIXED：statement 和 row 模式的混合

記錄模式	優(yōu)點	缺點
ROW	能清楚記錄每一個行數(shù)據(jù)的修改細節(jié)	批量操作，會產(chǎn)生大量的日志，尤其是 alter table 會讓日志文件大小暴漲
STATEMENT	日志量小，減少磁盤IO，提升存儲和恢復(fù)速度	在某些情況下會導(dǎo)致主從數(shù)據(jù)不一致，比如 Sql 語句中有 last_insert_id()、now() 等函數(shù)。
MIXED	準確性強，文件大小適中	當 binlog format 設(shè)置為 mixed 時，普通復(fù)制不會有問題，但是級聯(lián)復(fù)制在特殊情況下會 binlog 丟失。

Binlog文件結(jié)構(gòu)：

MySQL的binlog文件中記錄的是對數(shù)據(jù)庫的各種修改操作，用來記錄修改操作的數(shù)據(jù)結(jié)構(gòu)是Log event。不同的修改操作對應(yīng)的不同的log event。比較常用的log event有：Query event、Row event、Xid event等。binlog文件的內(nèi)容就是各種Log event的集合。

Binlog文件中Log event結(jié)構(gòu)如下圖所示：

event字段名	描述
timestamp	4字節(jié) 事件開始執(zhí)行時間
event_type	1字節(jié) 指定該事件的類型
event size	4字節(jié) 該事件的長度
server_id	1字節(jié) 服務(wù)器的serverId
next log pos	4字節(jié) 下一個事件開始的位置
flag	2字節(jié) event flags

binlog 的寫入機制：

根據(jù)設(shè)置的記錄模式和操作生成相應(yīng)的log event

事務(wù)執(zhí)行過程中產(chǎn)生log event會先寫入緩沖區(qū)，每個事務(wù)線程都有一個緩沖區(qū)，Log Event保存在一個binlog_cache_mngr數(shù)據(jù)結(jié)構(gòu)中，在該結(jié)構(gòu)中有兩個緩沖區(qū)，一個是stmt_cache，用于存放不支持事務(wù)的信息；另一個是trx_cache用于存放支持事務(wù)的信息。

事務(wù)在提交階段會將產(chǎn)生的log event寫入到外部binlog文件中。不同事務(wù)以串行方式將log event寫入binlog文件中，所以一個事務(wù)包含的log event信息在binlog文件中是連續(xù)的，中間不會插入其他事務(wù)的log event。

一張表有ID自增主鍵，當 insert 17條記錄后刪除第15,16,17條記錄，重啟Mysql，再 insert 一條記錄，這條記錄的ID是18還是15？

• 如果表的類型是 MyISAM，那么是18。因為 MyISAM 表會把自增主鍵的最大ID記錄到數(shù)據(jù)文件里，重啟MySQL自增主鍵的最大ID也不會丟失
• 如果表的類型是 InnoDB，那么是15。InnoDB 表只是把自增主鍵的最大ID記錄到內(nèi)存中，所以重啟數(shù)據(jù)庫或者是對表進行OPTIMIZE操作，都會導(dǎo)致最大ID丟失。