轉自：https://m.2cto.com/database/201701/557910.html
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）存儲方式
資料和索引分别存儲于不同的檔案中。
插入速度快，空間占用量小。
（3）功能
a.全文索引支援。（mysql>=5.6時innodb 也支援）
b.資料的壓縮存儲。.MYD檔案的壓縮存儲。
壓縮前，資料是25600KB：
壓縮優勢：節省磁盤空間，減少磁盤IO開銷。特點：壓縮後的表變成了隻讀表，不可寫。
如果需要更新資料，則需要先解壓後更新。利用工具：myisamchk –unpack 表名 進行解壓
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 'time_zone';
//設定為東九區，查詢時間就會加1小時
SET time_zone='+9:00';
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→B→C"的決定關系，則C傳遞函數依賴于A。是以，滿足第三範式的資料庫表應該不存在如下依賴關系：
關鍵字段→非關鍵字段x→非關鍵字段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 SELECT * FROM think_user WHERE user='蠟筆老新';
//檢視表所有索引情況
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）列獨立
如果需要某個字段上使用索引，則需要在字段參與的表達中，保證字段獨立在一側。
（2）左原則
Like：比對模式必須要左邊确定不能以通配符開頭。
假如業務邏輯上出現: field like ‘%keywork%’;類似查詢，需要使用全文索引。
複合索引：一個索引關聯多個字段，僅僅針對左邊字段有效果。
示例：添加複合索引
（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字段排序查詢：
不會用到查詢檢索索引是因為沒有用where條件查詢，而真實執行時，就會用到排序索引。
Tip：對比以上兩個執行計劃：
extra位置：
其中：extra額外資訊。加了索引後就不用使用檔案排序了。
Using filesort，表示使用檔案排序（外部排序，記憶體外部）。
（3）索引覆寫
索引擁有的關鍵字内容，覆寫了查詢所需要的全部資料，此時，就不需要在資料區擷取資料，僅僅在索引區即可。覆寫就是直接在索引區擷取内容，而不需要在資料區擷取。
例如，利用名字檢索：
查詢的字段剛好是複合索引包含的字段。是以就使用了複合索引。
說明，不是非要查詢用到，才可以索引覆寫，隻要滿足要求都可以覆寫！
建立索引索引時，不要僅僅考慮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‘%keyword%’這類查詢的比對問題。（mysql的全文索引幾乎不用，因為它不支援中文，我們應該使用sphinx全文索引）。
示例：
假如有一張表，表中有标題和内容兩個字段，現在要查詢标題或者内容包含 “database” 關鍵字的記錄。
補充：text和varchar的差別是text的資料不存在記錄裡，一條記錄的最大空間是65535.
形成的SQL如下：
Select * from articles where title like ‘%database%’ or body like ‘%database%’;
此時不能建立普通索引，查詢不符合左原則，建立了也使用不了。
此時全文索引就可以發揮其作用了：
Tip: 該MYSQL提供的全文索引，不能對中文起作用！
使用Match() against() 傳回關鍵字的比對度（關鍵字與記錄的關聯程度）。
發現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一緻。
注意：這裡的緩存僅當資料表的記錄改變時，緩存才會被删除。而不是依靠過期時間的。
六、分區分表
日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大，導緻資料庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表和表分區的目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表的增删改查效率。
分區，partition，分區是将資料分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區後，表面上還是一張表，但資料散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的資料。
其實每個分區，就是獨立的表。都要存儲該分區資料的資料，索引等資訊。
建立分區：在建立表時，指定分區的選項：
Create table table_name (定義)
Partition by 分區算法 (參數) 分區選項。
例如：Partition by key (id) partitions 5;
采用key取餘算法，根據id的值進行取餘，即對5取餘，然後配置設定到5個區裡。
分區結果如下：myisam下
Tip：分區與存儲引擎無關，是MySQL邏輯層完成的。
可以通過變量檢視目前mysql是否支援分區：
1.分區算法
MySQL提供4種分區算法：取餘：Key，hash 條件：List，range 。
參與分區的參數字段需要為主鍵的一部分。
（1）KEY – 取餘 ，按照某個字段進行取餘
分成5個區，就是對5取餘。将id對5取餘。
（2）Hash – 取餘，按照某個表達式的值進行取餘
示例：學生表分區，按照生日的月份，劃分到12個表中。
注意：Key，hash都是取餘算法，要求分區參數（括号裡的），傳回的資料必須為整數。
（3）List – 條件 – 清單，需要指定的每個分區資料的存儲條件。
示例：按照生日中的月份，分成春夏秋冬四個分區。
List，條件依賴的資料是清單形式。
（4）Range - 條件 – 範圍， 條件依賴的資料是一個條件表達式。
邏輯：按照生日的年份分成不同的年齡段。
2.分區的管理與選擇
（1）取餘：key，hash
增加分區數量： add partition partitions N
采用取餘算法的分區數量的修改，不會導緻已有分區資料的丢失，因為會重新配置設定資料到新的分區。
（2）條件：list，range
添加分區
删除分區：
Drop partition partition_name;
注意：删除條件算法的分區，會導緻分區資料丢失。添加分區不會。
（3）選擇分區算法
平均配置設定：就按照主鍵進行key(primary key)即可(非常常見)
按照某種業務邏輯分區：選擇那種最容易被篩選的字段，整數型
3.分表
分表是将一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個檔案，MYD資料檔案，.MYI索引檔案，.frm表結構檔案。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。分表技術是比較麻煩的，需要手動去建立子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要建立子表和配置子表間的union關系。（需要手動分表）
分表是分區之前用的，MYSQL5.1後，就開始用分區代替分表了。分表很少用了。
（1）水準分表
建立結構相同的N個表；
（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;
| Page | offset |
| 5 | 40, 10 |
| 50 | 490, 10 |
| 5000 | 4990, 10 |
| 500000 | 499990, 10 |
Limit 的使用，會大大提升無效資料的檢索（被跳過），因為是先檢索，檢索會檢索全部，再取得想要的。好的做法是使用條件等過濾方式，将檢索到的資料盡可能精确定位到需要的資料上。
4.随機選一些資料，不要使用Order by Rand()
上面的查詢，會導緻每條記錄都執行rand()，成本很高！
建議，通過mt_rand()，先确定的随機主鍵，再從資料表中擷取資料。
九、慢查詢日志的使用
定位執行較慢的查詢語句方案。
show variables like 'slow_query%'; show variables like '%long_query%';
Slow_query_log = 0|1
Long_query_time = N 超過該時間臨界點，就為慢查詢。
開啟日志
set global slow_query_log=1; set long_query_time=0.5；
執行SQL，檢視：