MYSQL優化主要分為以下四大方面：

設計：存儲引擎，字段類型，范式與逆范式

功能：索引，緩存，分區分表。

架構：主從復制，讀寫分離，負載均衡。

合理SQL：測試，經驗。

一、存儲引擎

在創建表的時候我們使用sql語句，Create table tableName () engine=myisam|innodb;

這里就指明了存儲引擎是myisam還是innodb。存儲引擎是一種用來存儲MySQL中對象（記錄和索引）的一種特定的結構（文件結構），處于MySQL服務器的最底層，直接存儲數據。導致上層的操作，依賴于存儲引擎的選擇。地位如下圖：

網絡接口層：與客戶端通信，比如傳輸數據等等。存儲引擎層：存儲數據的規則，方式。

本質：存儲引擎就是特定的數據存儲格式（方案）。

可以使用show engines命令來查看當前MySQL支持的存儲引擎列表。

1、InnoDB存儲引擎介紹

Mysql版本>=5.5 默認的存儲引擎，MySQL推薦使用的存儲引擎。支持事務，行級鎖定，外鍵約束。事務安全型存儲引擎。更加注重數據的完整性和安全性。

（1）存儲格式

數據，索引集中存儲，存儲于同一個表空間文件中。

數據：記錄行。索引：一種檢索機制，也需要一定的空間，就相當于一本字典的目錄。

示例： 創建一個test數據庫，新建一張student表，選擇存儲引擎為innodb, 然后打開mysql的data下的test目錄，發現有以下3個文件。

其中db.opt存放了數據庫的配置信息，比如數據庫的字符集還有編碼格式。student.frm是表結構文件，僅存儲了表的結構、元數據(meta)，包括表結構定義信息等。不論是哪個表引擎都會有一個frm文件。student.ibd是表索引文件，包括了單獨一個表的數據及索引內容。

如果往表里插入了新的數據，則在mysql的data目錄下會生成ibdata1文件，這個文件是存儲了所有innodb表的數據。

關于innodb引擎的詳細介紹：

使用innodb引擎時，需要理解獨立表空間、共享表空間。

獨立表空間：每個表都會生成以獨立的文件方式來存儲，每個表都一個.frm的描述文件，還有一個.ibd文件。其中這個文件包括了單獨一個表的數據及索引內容，默認情況下它的存儲在mysql指定的目錄下。

獨立表空間優缺點

優點：

每個表都有自己獨立的表空間；每個表的數據和索引都會存儲在各個獨立的表空間中；可以實現單表在不同的數據進行遷移；表空間可以回收（除了drop table操作，表空不能自己回收）；drop table 操作自動回收表空間，如果對統計分析或是日值表，刪除大量數據后可以通過 ：alter table tablename engin=innodb進行回縮不用的空間；對于使用inodb-plugin的innodb使用truncate table會使用空間收縮。；對于使用獨立表空間，不管怎么刪除，表空間的碎片都不會太嚴重。

缺點：

單表增加過大，如超過100G。對于單表增長過大的問題，如果使用共享表空間可以把文件分開，但有同樣有一個問題，如果訪問的范圍過大同樣會訪問多個文件，一樣會比較慢。對于獨立表空間也有一個解決辦法是：使用分區表，也可以把那個大的表空間移動到別的空間上然后做一個連接。其實從性能上出發，當一個表超過100個G有可能響應也是較慢了，對于獨立表空間還容易發現問題早做處理。

共享表空間：某一個數據庫所有的表數據，索引文件全部都放在一個文件中，默認這個共享表空間的文件路徑在data目錄下，默認的文件名為 ibdata1,初始化為10M。

共享表空間優缺點

優點：可以將表空間分成多個文件存放在各個磁盤上（表空間文件大小不受表大小的限制，如一個表可以分布在不同的文件上），數據和文件放在一起方便管理。

缺點：所有的數據和索引存放到一個文件中，將來會是一個很大的文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，這樣對一個表做了大量刪除操作后表空間將有大量的空隙，特別是對統計分析、日值系統這類應用最不適合用共享表空間。

如何開啟獨立表空間？

查看是否開啟獨產表空間：

mysql> show variables like '%per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

設置開啟：

在my.cnf文件中[mysqld] 節點下添加innodb_file_per_table=1

或者通過命令：set global innodb_file_per_table=1;

注：

innodb_file_per_table值來進行修改即可，但是對于之前使用過的共享表空間則不會影響，除非手動的去進行修改或者是

innodb_file_per_table=1 為使用獨占表空間

innodb_file_per_table=0 為使用共享表空間

修改獨占空表空間的數據存儲位置

innodb_data_home_dir = "C:\mysql\data\"

innodb_log_group_home_dir = "C:\mysql\data\"

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

參數說明:

這個設置配置一個可擴展大小的尺寸為10MB的單獨文件，名為ibdata1。沒有給出文件的位置，所以默認的是在MySQL的數據目錄內。【對數據來進行初始化的設置】

innodb_data_home_dir 代表為數據庫文件所存放的目錄

innodb_log_group_home_dir 為日志存放目錄

innodb_file_per_table 是否使用共享以及獨占表空間來

以上的幾個參數必須在一起加入。

對于參數一些注意的地方

InnoDB不創建目錄，所以在啟動服務器之前請確認”所配置的路徑目錄”的確存在。這對你配置的任何日志文件目錄來說也是真實的。使用Unix或DOS的mkdir命令來創建任何必需的目錄。

通過把innodb_data_home_dir的值原原本本地部署到數據文件名，并在需要的地方添加斜杠或反斜杠，InnoDB為每個數據文件形成目錄路徑。

如果innodb_data_home_dir選項根本沒有在my.cnf中提到，默認值是“dot”目錄 ./，這意思是MySQL數據目錄。

（2）數據按照主鍵順序存儲

插入時做排序工作，效率低。

（3）特定功能

事務、外鍵約束 ： 都是為了維護數據的完整性。

并發性處理：

innodb擅長處理并發的。因為它使用了行級鎖定，只該行鎖了，其它行沒有鎖。

行級鎖定：row-level locking，實現了行級鎖定，在一定情況下，可以選擇行級鎖來提升并發性。也支持表級鎖定，Innodb會自帶鎖，不需要我們自己設置。

多版本并發控制, MVCC，效果達到無阻塞讀操作。

（4）總結：innodb擅長事務、數據的完整性及高并發處理，不擅長快速插入（插入前要排序，消耗時間）和檢索。

2.MyISAM存儲引擎介紹

MySQL<= 5.5 MySQL默認的存儲引擎。

ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統。

擅長與處理，高速讀與寫。

（1）存儲方式

數據和索引分別存儲于不同的文件中。

（2）數據的存儲順序為插入順序（沒有經過排序）

插入速度快，空間占用量小。

（3）功能

a.全文索引支持。（mysql>=5.6時innodb 也支持）

b.數據的壓縮存儲。.MYD文件的壓縮存儲。

壓縮前，數據是25600KB：

進行壓縮：使用工具 myisamPack完成壓縮功能：該工具mysql自帶

進入到需要壓縮表的數據目錄，執行壓縮指令 myisampack 表名。配置環境變量。

壓縮后：

注意，壓縮后，需要重新修復索引：

查看結果，發現現在的數據變成12741KB了，比之前的更小了：

壓縮優勢：節省磁盤空間，減少磁盤IO開銷。特點：壓縮后的表變成了只讀表，不可寫。

如果需要更新數據，則需要先解壓后更新。利用工具：myisamchk &ndash;unpack 表名 進行解壓

解壓后，變成了原來的25600KB

刷新表的狀態：flush table myisam_2

c.并發性：

僅僅支持表級鎖定，不支持高并發。

支持并發插入。寫操作中的插入操作，不會阻塞讀操作（其他操作）

（4）關于Innodb 和myisam的取舍：

Innodb ：數據完整性，并發性處理，擅長更新，刪除。

myisam：高速查詢及插入。擅長插入和查詢。

具體舉例：

那么對于微博項目來看，選擇哪一個存儲引擎呢？

a.微博主要是插入微博和查詢微博列表，較為適合MyISAM；

b.微博在更新微博和刪除微博，要少的多，較為適合MyISAM；

c.對數據完整性的需求并沒有那么強烈，比如用戶刪除微博，關聯的轉播和評論并不要求都做相應的行為，較為適合MyISAM；

那么對于記賬財務系統，選擇哪一款存儲引擎呢？

a.財務系統除了讀取和插入，經常要進行數據的修改和刪除，較為適合InnoDB；

b.在進行財務變更的時候，如果失敗需要回滾必須用到事務，較為適合InnoDB；

c.每個用戶的財務數據完整性和同步性非常重要，需要外鍵支持，否則財務將會混亂，較為適合InnoDB。

3.其他存儲引擎

（1）Archive：存檔型，僅提供插入和查詢操作。非常高效阻塞的插入和查詢。

（2）Memory：內存型，數據存儲于內存中，存儲引擎。緩存型存儲引擎。

（3）插件式存儲引擎：用C和C++開發的存儲引擎。

4.鎖的概念：當客戶端操作表（記錄）時，為了保證操作的隔離性（多個客戶端操作不能互相影響），通過加鎖來處理。

操作方面：

讀鎖：讀操作時增加的鎖，也叫共享鎖，S-lock。特征是阻塞其他客戶端的寫操作，不阻塞讀操作。（并發讀）

寫鎖：寫操作時增加的鎖，也叫獨占鎖或排他鎖，X-lock。特征是阻塞其他客戶端的讀，寫操作。

鎖定粒度（范圍）：

行級：提升并發性，鎖本身開銷大

表級：不利于并發性，鎖本身開銷小。

二、字段類型選擇

字段類型應該要滿足需求，盡量要滿足以下需求。

盡可能小（占用存儲空間少）、盡可能定長（占用存儲空間固定）、盡可能使用整數。

1.列類型之數值

（1）整型

MySQL數據庫支持五種整型類型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五種。

整型類型占用空間和取值范圍

類型 字節 最小值 最大值

TINYINT 1 有符號：-128 無符號：0 有符號：127 無符號：255

SMALLINT 2有符號：-32768無符號：0有符號：32767無符號：65535

MEDIUMINT 3有符號：-8388608無符號：0有符號：8388607無符號：16777215

INT/INTEGER 4有符號：-2147483648無符號：0有符號：2147483647無符號：4294967295

BIGINT 8 有符號：-9223372036854775808無符號：0 有符號：9223372036854775807無符號：18446744073709551615

五種整型的適用場景：

TINYINT，年齡，包含在0~255之間；

SMALLINT，端口號，包含在0~65535之間；

MEDIUMINT，中小型網站注冊會員，1600萬夠用；

INT，身份證編號，42億可以用很久；

BIGINT，Twitter微博量，幾百億

（2）浮點型(非精確)

MySQL數據庫支持兩種浮點類型：FLOAT(單精度)和DOUBLE(雙精度)兩種

浮點型(非精確)占用空間和取值范圍

類型 字節 范圍

FLOAT 4 正數范圍：1.175494351E-38~3.402823466E+38，負數范圍：-3.402823466E+38~-1.175494351E-38

DOUBLE 8 正數范圍：1.7976931348623157E-308~2.2250738585072014E+308

負數范圍：-2.2250738585072014E+308~-1.7976931348623157E-308

（3）定點型(精確)

浮點型由于內部的存儲方式是數值，導致它在一定程度上取得的是近似值而非精確值。如果使用定點型，那么就可以精確取得小數部分，因為它內部存儲方式是字符串形式。

定點型(精確)占用空間和取值范圍

類型 字節 范圍

DECIMAL/NUMERIC M+2 M最大65位，D最大30位。

創建一個定點型格式：DECIMAL(M,D)，表示小數點D位，整數部分M位及M位內。

2.列類型之日期

MySQL數據庫中有五個可用的日期時間數據類型，分別為：DATE、DATETIME、TIME、YEAR、TIMESTAMP。

日期時間類型占用空間和取值范圍

類型 字節 最小值 最大值

YEAR 1 1901 2155

TIME 3 -838:59:59838:59:59

DATE 4 1000-01-01 9999-12-31

TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59

TIMESTAMP有幾個特點：

a.當更新一條數據的時候，設置此類型根據當前系統更新可自動更新時間；

b.如果插入一條NULL，也會自動插入當前系統時間；

c.創建字段時，系統會自動給一個默認值；

d.會根據當前時區來存儲和查詢時間，存儲時對當前時區進行轉換，查詢時再轉換為當前的時區。

//查看當前時區

SHOW VARIABLES LIKE &#39;time_zone&#39;;

//設置為東九區，查詢時間就會加1小時

SET time_zone=&#39;+9:00&#39;;

DATE占用3個字節，包含年月日，范圍和DATETIME一樣。DATE長度是0，無法設置。

YEAR占用1個字節，包年年份，長度默認為4位，無法設置。

TIME占用3個字節，包含時分秒，長度0到6之間，用于設置微秒。對于TIME的范圍的時是-838到838的原因，是因為TIME類型不但可以保存一天的時，還可以包含時間之間的間隔。

綜上考慮：使用datetime，當然也可以使用int（11）來保存時間戳。

關于INT(11)存放時間戳的優點如下：

a.INT占4個字節，DATETIME占8個字節；

b.INT存儲索引的空間比DATETIME小，查詢快，排序效率高；

c.在計算機時間差等范圍問題，比較方便。

3.列類型之字符

字符集校對規則utf8_general_ci表示校對時不區分大小寫，相對的cs表示區分大小寫。還有一個bin結尾的是字節比較。而general是地區名，這里是通用，utf8表示編碼。如果是gbk，可以使用gbk_chinese_ci，如果是utf8則用utf8_general。MySQL提供了多種對字符數據的存儲類型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多種字符類型。

（1）CHAR是保存定長字符串，而VARCHAR則是保存變長字符串。CHAR(5)表示必須保存5個字符，而VARCHAR(5)則表示最大保存字符為5。如果是UTF8編碼下，長度為5的CHAR類型，最多可以存儲15字節，也就是5個漢字的內容。因為一個漢字占3個字節。

由于CHAR類型是定長，MySQL會根據定義的長度進行分配空間，在處理速度上比VARCHAR快的多，所以適合存儲例如手機、身份證這種定長的字符，否則就會造成浪費。那么CHAR類型最大可以插入255個字符，最多可以存儲765個字節。

（2）BINARY和VARBINARY是采用二進制存儲的，沒有字符集概念，意義在于防止字符集的問題導致數據丟失，存儲中文會占用兩個字符，會亂碼，半截會問號。因為是采用二進制存儲，在比較字符和排序的時候，都是二進制進行的，所以只有需要操作二進制時才需要使用。

（3）八種適合文本內容的大數據類型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。

綜上：短文本定長用char，變長用varchar，長文本用text

4.列類型之屬性

無符號(UNSIGNED)和填充零(ZEROFILL)，還有是否為空、默認值、主鍵、自動編號。

嚴格模式

我們使用的是WAMP集成環境，默認安裝的情況下，是非嚴格模式，用于部署階段。而開發調試階段，強烈建議使用嚴格模式，方便開發中調試將問題及時暴露出來。因為在非嚴格模式下將NULL插入NOTNULL等非法操作都是被運行的。設置嚴格模式只要打開my.ini文件，在末尾添加一句：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后，重啟服務器即可。檢查SQL_MODE狀態

SELECT @@global.sql_mode;

三、范式與逆范式

為了建立冗余較小、結構合理的數據庫，設計數據庫時必須遵循一定的規則。在關系型數據庫中這種規則就稱為范式。范式是符合某一種設計要求的總結。要想設計一個結構合理的關系型數據庫，必須滿足一定的范式。

第一范式1NF，原子性

第二范式2NF，消除部分依賴

第三范式3NF，消除傳遞依賴

1、范式

（1）第一范式：具有原子性，確保每列保持原子性。

第一范式是最基本的范式。如果數據庫表中的所有字段值都是不可分解的原子值，就說明該數據庫表滿足了第一范式。第一范式的合理遵循需要根據系統的實際需求來定。比如某些數據庫系統中需要用到“地址”這個屬性本來直接將“地址”屬性設計成一個數據庫表的字段就行。但是如果系統經常會訪問“地址”屬性中的“城市”部分，那么就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行存儲，這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了數據庫的第一范式。
（2）第二范式：主鍵列與非主鍵列遵循完全函數依賴關系，確保表中的每列都和主鍵相關。

第二范式在第一范式的基礎之上更進一層。第二范式需要確保數據庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中。
（3）第三范式：非主鍵列之間沒有傳遞函數依賴關系索引，確保每列都和主鍵列直接相關,而不是間接相關。

所謂傳遞函數依賴，指的是如果存在"A&rarr;B&rarr;C"的決定關系，則C傳遞函數依賴于A。因此，滿足第三范式的數據庫表應該不存在如下依賴關系：

關鍵字段&rarr;非關鍵字段x&rarr;非關鍵字段y

比如在設計一個訂單數據表的時候，可以將客戶編號作為一個外鍵和訂單表建立相應的關系。而不可以在訂單表中添加關于客戶其它信息（比如姓名、所屬公司等）的字段。

先滿足第一范式，再滿足第二范式，才能滿足第三范式。

2、逆范式

逆范式是指打破范式，通過增加冗余或重復的數據來提高數據庫的性能。

示例： 假如有一個商品表Goods：

字段有Goods_id(商品表), goods_name(商品名稱), cat_id(所屬類別的id)。

還有一個分類表Category：

字段有Cat_id(類別id), cat_name(類別名稱)。

現在要查詢類別id為3的商品的數量，例如分類列表查詢：

分類ID 分類名稱 商品數量

3 計算機 567

可以使用下列sql語句：

Select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id;

但是，假如商品數量較大，那么就比較耗性能了。這時，我們可以考慮重新設計Category表：增加存當前分類下商品數量的字段。

Cat_id, cat_name, goods_count

每當商品改動時，修改對應分類的數量信息。

再查詢分類列表時：Select * from category;

此時額外的消耗，出現在維護該字段的正確性上，保證商品的任何更新都正確的處理該數量才可以。

四、索引

1．索引概述

利用關鍵字，就是記錄的部分數據（某個字段，某些字段，某個字段的一部分），建立與記錄位置的對應關系，就是索引。索引的關鍵字一定是排序的。索引本質上是表字段的有序子集，它是提高查詢速度最有效的方法。一個沒有建立任何索引的表，就相當于一本沒有目錄的書，在每次查詢時就會進行全表掃描，這樣會導致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過目錄的指引，迅速翻閱到指定的章節，提升的查詢性能，節約了查詢資源。

測試查詢，添加索引前后比對執行時間：

2．索引種類

從索引的定義方式和用途中來看：主鍵索引，唯一索引，普通索引，全文索引。

無論任何類型，都是通過建立關鍵字與位置的對應關系來實現的。索引是通過關鍵字找對應的記錄的地址。

以上類型的差異：對索引關鍵字的要求不同。

關鍵字：記錄的部分數據（某個字段，某些字段，某個字段的一部分）。

普通索引,index：對關鍵字沒有要求。

唯一索引,unique index：要求關鍵字不能重復。同時增加唯一約束。

主鍵索引,primary key：要求關鍵字不能重復，也不能為NULL。同時增加主鍵約束。

全文索引,fulltext key：關鍵字的來源不是所有字段的數據，而是從字段中提取的特別關鍵詞。

關鍵字含義：可以是某個字段，也可以是某些字段。如果一個索引通過在多個字段上提取的關鍵字，稱之為復合索引。 命令:alter table exp add index (field1, field2);

PS：這里主鍵索引和唯一索引的區別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內只能創建一個，唯一索引可以創建多個。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。

3.索引操作

(1)創建主鍵索引

創建一個無符號整型且自動增長的列，然后設置成主鍵即可。

//通過EXPLAIN語句查看索引狀態

EXPLAIN SELECT * FROM think_user WHERE id=1;

(2)創建普通或唯一索引

直接進入navicat設計表的第二欄，選擇一個字段(比如user字段)，添加一個Nomral(普通索引)或Unique(唯一索引)。

//通過EXPLAIN語句查看索引狀態

EXPLAIN SELECT * FROM think_user WHERE user=&#39;蠟筆老新&#39;;

//查看表所有索引情況

SHOW INDEX FROM think_user;

（3）使用sql語句的方式建立索引----建表時就創建索引

注意：索引可以起名字，但是主鍵索引不能起名字，因為一個表僅僅可以有一個主索引，其他索引可以出現多個。名字可以省略，mysql會默認生成，通常使用字段名來充當。

（4）使用sql語句的方式建立索引----更新表時創建索引

注意：如果表中存在數據，數據符合唯一或主鍵的約束才可能創建成功。auto_increment屬性，依賴于一個KEY。

（5）使用sql語句的方式刪除索引，auto_increment依賴于KEY。

（6）Explain 執行計劃

可以通過在select語句前使用 explain，來獲取該查詢語句的執行計劃，而不是真正執行該語句。

刪除索引時，再看執行計劃：

從查詢的行數可知，有索引時查詢會快的多，因為它只需要查找一行，而沒有索引時，會造成全表掃描。

注意：select語句才能獲取到執行計劃。（新版本5.6會擴展其他語句的執行計劃的獲取）

4.索引原則

如果索引不遵循使用原則，則可能導致索引無效。

（1）列獨立

如果需要某個字段上使用索引，則需要在字段參與的表達中，保證字段獨立在一側。

第三個語句 empno-1就不是列獨立：就不能用索引。類似函數等等。（write_time < unix_timestamp()-$gc_maxlifetime）

其他兩個列獨立可以使用：

（2）左原則

Like：匹配模式必須要左邊確定不能以通配符開頭。

假如業務邏輯上出現: field like &lsquo;%keywork%&rsquo;;類似查詢，需要使用全文索引。

復合索引：一個索引關聯多個字段，僅僅針對左邊字段有效果。

示例：添加復合索引

對Ename的查詢，使用了索引，結果如下：

Empno的查詢沒有使用索引，結果如下：

（3）OR的使用

必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。

為后面的條件增加可以使用的索引后，再查看執行計劃：

（4）MySQL智能選擇

即使滿足了上面說原則，MySQL也能棄用索引：如下圖

棄用索引的主要原因：

查詢即使使用索引，會導致出現大量的隨機IO，相對于從數據記錄的第一條遍歷到最后一條的順序IO開銷，還要大。

綜上歸納：

a、不要過度索引。索引越多，占用空間越大，反而性能變慢；

b.只對WHERE子句中頻繁使用的建立索引；

c.盡可能使用唯一索引，重復值越少，索引效果越強；

d.使用短索引，如果char(255)太大，應該給它指定一個前綴長度，大部分情況下前10位或20位值基本是唯一的，那么就不要對整個列進行索引；

e.充分利用左前綴，這是針對復合索引，因為WHERE語句如果有AND并列，只能識別一個索引(獲取記錄最少的那個)，索引需要使用復合索引，那么應該將WHERE最頻繁的放置在左邊。

f.索引存在，如果沒有滿足使用原則，也會導致索引無效：

5.索引的使用場景

（1）索引檢索：檢索數據時使用索引。

（2）索引排序

如果order by 排序需要的字段上存在索引，則可能使用到索引。

例如，按照ename字段排序查詢：

此時，沒有任何索引。在ename字段上建立索引后：

不會用到查詢檢索索引是因為沒有用where條件查詢，而真實執行時，就會用到排序索引。

Tip：對比以上兩個執行計劃：

extra位置：

其中：extra額外信息。加了索引后就不用使用文件排序了。

Using filesort，表示使用文件排序（外部排序，內存外部）。

（3）索引覆蓋

索引擁有的關鍵字內容，覆蓋了查詢所需要的全部數據，此時，就不需要在數據區獲取數據，僅僅在索引區即可。覆蓋就是直接在索引區獲取內容，而不需要在數據區獲取。

例如，利用名字檢索：

可以在ename字段建立索引：

分析執行：

再增加一個索引：

完成相同的查詢：

查詢的字段剛好是復合索引包含的字段。所以就使用了復合索引。

說明，不是非要查詢用到，才可以索引覆蓋，只要滿足要求都可以覆蓋！

建立索引索引時，不要僅僅考慮where檢索，同時考慮其他的使用場景。（在所有的where字段上增加索引，就是不合理的）

6.前綴索引

前綴索引是建立索引關鍵字一種方案。通常會使用字段的整體作為索引關鍵字。有時，即使使用字段前部分數據，也可以去識別某些記錄。就比如一個班級里，我要找王xx，假如姓王的只有1個人，那么就可以建一個前綴索引，就是王。

語法：

Index `index_name` (`index_field`(N))使用index_name前N個字符建立的索引。

那么N究竟是多少？使用N長度所達到的辨識度，極限接近于使用全部長度的辨識度即可！

先計算最大的辨識度M：

公式：先計算總的記錄數m，再求該字段不重復的記錄數q，那么M=m/q。然后依次取得前N個字符，N逐步增加，進行對比，直到找到極限接近于M的，那么最后的N就是我們要找的N。

求得辨識度為1.4774.，也就是說一個前綴索引可以對應1.4774條記錄。

然后依次取得前N個字符，進行對比，找到極限接近的：

可見，9 時，已經極限接近，提高長度，不能明顯提升辨識度，因此可以使用前9個字符：

Tip：前綴索引不能用于索引覆蓋！

7.全文索引

該類型的索引特殊在：關鍵字的創建上。是為了解決 like&lsquo;%keyword%&rsquo;這類查詢的匹配問題。（mysql的全文索引幾乎不用，因為它不支持中文，我們應該使用sphinx全文索引）。

示例：

假如有一張表，表中有標題和內容兩個字段，現在要查詢標題或者內容包含 “database” 關鍵字的記錄。

補充：text和varchar的區別是text的數據不存在記錄里，一條記錄的最大空間是65535.

形成的SQL如下：

Select * from articles where title like &lsquo;%database%&rsquo; or body like &lsquo;%database%&rsquo;;

此時不能建立普通索引，查詢不符合左原則，建立了也使用不了。

此時全文索引就可以發揮其作用了：

直接使用上面的SQL，需要使用特殊的全文索引匹配語法才可以生效： Match() against();

Tip: 該MYSQL提供的全文索引，不能對中文起作用！

使用Match() against() 返回關鍵字的匹配度（關鍵字與記錄的關聯程度）。

停止詞 in：

發現in這個詞，是不能被全文索引所檢索到的。因為in這個詞是不可以用在全文索引的關鍵詞里的，沒有誰會在一段文本里檢索這樣一個詞。

思考：與 like %in% 是否相同？不同。

原因何在呢？全文索引，索引的的關鍵字，不是整個字段數據，而是從數據中提取的關鍵詞。

8.索引結構-b-tree介紹

Hash、B-Tree（B樹）兩種數據結構。指的是mysql存儲索引所采用的數據結構。其中，用戶所維護的所有的索引結構 B-Tree結構。

B-Tree的結構如下：

每個節點，存儲多個關鍵字。關鍵字也會對應記錄地址

以上設計為了解決一次性磁盤IO開銷，可以讀取到更多的關鍵字數量。

每個關鍵字之間，存在子節點指針：

如果是復合索引：

關鍵字的排序先排左側字段，在左側字段相同的情況下，再排序右側字段：

9.聚集索引(聚簇索引)

B+Tree(B-Tree的變種)

在innodb的存儲引擎上，主鍵索引是與數據記錄存儲在一起的（聚簇在一起的）。

帶來的問題：

Innodb的其他索引，非主鍵索引（二級索引）：

關鍵字對應的不再是記錄的地址，而是記錄的主鍵。

可見，檢索需要二次檢索。先檢索到主鍵ID，再檢索記錄。

五、查詢緩存query_cache

將select的結果，存取起來共二次使用的緩存區域：

MySQL提供的緩存區：

未開啟前：

兩次查詢時間消耗一致。

開啟查詢緩存，通過變量控制：

開啟并設置大小：

再次執行查詢：

可見，第二次查詢，使用了開啟的緩存！

注意事項：查詢緩存存在判斷是嚴格依賴于select語句本身的：嚴格保證SQL一致。

如果查詢時包含動態數據，則不能被緩存。

一旦開啟查詢緩存，MySQL會將所有可以被緩存的select語句都緩存。如果存在不想使用緩存的SQL執行，則可以使用 SQL_NO_CACHE語法提示達到目的：

注意：這里的緩存僅當數據表的記錄改變時，緩存才會被刪除。而不是依靠過期時間的。

六、分區分表

日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大，導致數據庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表和表分區的目的就是減少數據庫的負擔，提高數據庫的效率，通常點來講就是提高表的增刪改查效率。

分區，partition，分區是將數據分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區后，表面上還是一張表，但數據散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的數據。

其實每個分區，就是獨立的表。都要存儲該分區數據的數據，索引等信息。

創建分區：在創建表時，指定分區的選項：

Create table table_name (定義)

Partition by 分區算法 (參數) 分區選項。

例如：Partition by key (id) partitions 5;

采用key取余算法，根據id的值進行取余，即對5取余，然后分配到5個區里。

分區結果如下：myisam下

Innodb下

Tip：分區與存儲引擎無關，是MySQL邏輯層完成的。

可以通過變量查看當前mysql是否支持分區：

1.分區算法

MySQL提供4種分區算法：取余：Key，hash 條件：List，range 。

參與分區的參數字段需要為主鍵的一部分。

（1）KEY &ndash; 取余 ，按照某個字段進行取余

分成5個區，就是對5取余。將id對5取余。

（2）Hash &ndash; 取余，按照某個表達式的值進行取余

示例：學生表分區，按照生日的月份，劃分到12個表中。

注意：Key，hash都是取余算法，要求分區參數（括號里的），返回的數據必須為整數。

（3）List &ndash; 條件 &ndash; 列表，需要指定的每個分區數據的存儲條件。

示例：按照生日中的月份，分成春夏秋冬四個分區。

List，條件依賴的數據是列表形式。

（4）Range - 條件 &ndash; 范圍， 條件依賴的數據是一個條件表達式。

邏輯：按照生日的年份分成不同的年齡段。

2.分區的管理與選擇

（1）取余：key，hash

增加分區數量： add partition partitions N

減少分區數量： COALESCE partition N

采用取余算法的分區數量的修改，不會導致已有分區數據的丟失，因為會重新分配數據到新的分區。

（2）條件：list，range

添加分區

刪除分區：

Drop partition partition_name;

注意：刪除條件算法的分區，會導致分區數據丟失。添加分區不會。

（3）選擇分區算法

平均分配：就按照主鍵進行key(primary key)即可(非常常見)

按照某種業務邏輯分區：選擇那種最容易被篩選的字段，整數型

3.分表

分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然后去操作它。分表技術是比較麻煩的，需要手動去創建子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要創建子表和配置子表間的union關系。（需要手動分表）

分表是分區之前用的，MYSQL5.1后，就開始用分區代替分表了。分表很少用了。

（1）水平分表

創建結構相同的N個表；

再創建用于管理學生ID的表student_id：(該表是為了提供自增的ID)

PHP客戶端邏輯：

Merge，mrg_myisam

是MySQL提供一個可以將多個結構相同的myisam表，合并到一起的存儲引擎：

（2）垂直分表

一張表中存在多個字段。這些字段可以分為常用字段和非常用字段，為了提高查表速度，我們可以把這兩類字段分開來存儲。主要目的，減少每條記錄的長度。

通常我們按以下原則進行垂直拆分:把不常用的字段單獨放在一張表;把text，blog等大字段拆分出來放在附表中;經常組合查詢的列放在一張表中;

例如學生表可以分成：

基礎表(Student_base)和額外表(Student_extra)，兩張表中記錄為1:1的關系。

基礎信息表Student_base

Id name age

額外信息表Student_extra

Id 籍貫 政治面貌

七、服務器架構介紹

服務器架構，不僅僅是用一臺MySQL

主從復制：

Mysql服務器內部支持復制功能，僅僅需要通過配置完成下面的拓撲結構。一主多從典型結果：主服務器負責寫數據。從服務器負責讀數據。復制功能mysql會自帶。

讀寫分離，負載均衡：

php不再操作MYSQL數據庫服務器，而是去操作讀寫分離、負載均衡服務器，只要服務器安裝了mysql proxy或Ameoba軟件就可以實現讀寫分離和負載均衡，讀寫分離是指該服務器會判斷客戶端的操作是讀還是寫，從而選擇操作mysql主服務器還是從服務器。負載均衡算法是指，客戶端讀操作時，該服務器會根據取余算法去選擇一臺從服務器。

上面的架構可以提升整體服務器的效率，高性能。

同時，服務器架構需要保證，高可用（穩定），7x24不宕機。因此需要增加一些冗余服務器以便備用。時時檢測正在用的服務器。

八、SQL優化

1.對于并發性的SQL

少用（不用）多表操作（子查詢，聯合查詢），而是將復雜的SQL拆分多次執行。如果查詢很原子（很小），會增加查詢緩存的利用率。

2.大量數據的插入

多條 insert或者Load data into table（從文件里載入數據到表里）

建議，先關閉約束及索引，完成數據插入，再重新生成索引及約束。

針對于myisam，步驟：

Alter table table_name disable keys; 禁用索引約束

大量的插入

Alter table table_name enable keys; 啟用

針對innodb，步驟：

Drop index, drop constraint 刪除索引及約束，要保留主鍵

Begin transaction|set autocommit=0; 開啟事務，不讓他自動提交

[數據本身已經按照主鍵值排序]

大量的插入

Commit;

Add index, add constraint

3.分頁

分頁假定Limit offset, size; size = 10;

Limit 的使用，會大大提升無效數據的檢索（被跳過），因為是先檢索，檢索會檢索全部，再取得想要的。好的做法是使用條件等過濾方式，將檢索到的數據盡可能精確定位到需要的數據上。

4.隨機選一些數據，不要使用Order by Rand()

上面的查詢，會導致每條記錄都執行rand()，成本很高！

建議，通過mt_rand()，先確定的隨機主鍵，再從數據表中獲取數據。

九、慢查詢日志的使用

定位執行較慢的查詢語句方案。

show variables like &#39;slow_query%&#39;; show variables like &#39;%long_query%&#39;;

Slow_query_log = 0|1

Long_query_time = N 超過該時間臨界點，就為慢查詢。

開啟日志

set global slow_query_log=1; set long_query_time=0.5；

執行SQL，查看：