高性能MySql

?1、索引是對DB優化最有效的方式

? ?varchar(10)定義的是字符的個數，如果是utf-8的話，最大是3X10個字節

?二、索引類型

? ? ?1、MySql的索引是在存儲引擎層實現的，各個存儲引擎的的索引方式也是不同的

? ? ?2、B-Tree索引

? ? ? ? ?MyISAM索引通過數據的物理位置引用被索引的行（數據存儲位置變化時需要更新索引），INNODB則根據主鍵引用被索引的行（沒有主鍵默則根據默認的策略生成主鍵）。

? ? ? ? B+樹是平衡樹

? ? ? ? ? ? ? ? ?聚簇索引和非聚簇索引的分析：http://www.cnblogs.com/Arlen/articles/1605626.html

? ? ? ? ? ? ? ? ?主鍵索引是聚簇索引，二級索引是非聚簇索引。

? ? ? ?INNODB索引的使用

? ? ? ? ? ? ?1、全值匹配

? ? ? ? ? ? ?2、匹配最左前綴

? ? ? ? ? ? ?3、匹配列前綴

? ? ? ? ? ? ?4、匹配范圍值

? ? ? ? ? ? ?5、精確匹配某一列并范圍匹配另一列

? ? ? ? ? ? ?6、索引覆蓋

? ? ? ? ? ? ?7、因為索引樹的節點是有序的，所以除了按值查找之外，索引還能用于order by和group by操作。

? ? ? ? ? ? ? ? ? 一般，如果B樹可以按照某種方式找到值，那么也能按照這種方式排序。

? ? ? ? ? ? ?限制：

? ? ? ? ? ? ? ?1、必須匹配最左前綴

? ? ? ? ? ? ? ?2、不能跳過索引中的列

? ? ? ? ? ? ? ?3、如果某個列使用了范圍查詢，則其右邊的所有列都有不能使用索引了。

? ? ? ? ? ? ? ?4、order by 要么都是升序，要么都是降序

? ? ? ? ? ? ? ?5、如果是多個表，只有全部是第一個表才能使用

? ? ? 3、Hash 索引

? ? ? ? ? ? 在MySql里，只有Memory引擎顯式的支持hash索引。

? ? ? ? ? ? ?哈希索引自身只存儲對應的哈希值和行索引，而不存儲字段值，所以不能使用索引的值來避免讀取行。

? ? ? ? ? ? 哈希不支持部分索引列的查找，只支持全部匹配

? ? ? ? ? ? 哈希索引只支持列的全值匹配，只支持等值比較查詢。

? ? ? ? ? ? INNODB里面內置了自適應hash索引?

? ? ? ?4、全文索引

? ? ? 5、空間數據索引

? ? ? ?6、在INNODB里面模擬hash索引來優化

? ? ? ? ? ? ? 如url列的內容很大，創建的索引占用的磁盤塊數也很大。如果按照url列來等值查找數據（where url=www.yunzhu.com），則下面的優化方法較好：

? ? ? ? ? ? ? ? ? 加一列hash_url，該列存儲的是url的MD5值。同時對hash_url創建索引，然后這樣查找：where url=www.yunzhu.com and ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? hash_url=MD5(www.yunzhu.com)? ? ?

? ? ? ? ? ? ? ?則mysql會優先走hash_url索引，找到對應的行然后比較url是否相同。

? ? ? ? ? 原理：hash_url索引占的磁盤塊數很小，而且是是整數比較（比字符串比較快很多），所以會很快。

三、高性能索引策略

? ? 1、不是獨立的列（索引列是表達式或者函數的入參）

? ? ?2、前綴索引（原理：給索引瘦身）

? ? ? ?有時候某個列值是text或者varchar類型，而且值很大。這個時候如果直接對該列創建索引，磁盤塊數將會很大，可以采取前綴索引進行優化。

? ? ? ?前綴索引：只對某個列的前幾位做索引，而不是所有位。無法使用前綴索引進行order by和group by

? ? ? ? 創建前綴索引語法：alter table mytable add key city(n) ? ?則創建的索引對對city的前n個字符創建索引。

? ? ? ?如何選擇n

? ? ? ? 實驗找出選擇性較高的n。

? ? ? ?不能進行索引排序

? ? ?

? ? ? 3、多列索引

? ? ? ? ? 問題：index_merge

? ? ? ? ? ?在每個列上都創建索引，并不能提高mysql的查詢性能。

? ? ? ? ? ?當出現服務器對多個索引做相交操作的時候（多個and條件），通常意味著需要一個包含所有相關列的索引，而不是多個獨立的單列索引。

? ? ? ? ? ? 當服務器出現對多個索引做相交操作的時候（多個or條件），需要消耗大量的CPU資源去重，排序等操作上。

? ? ? ?

? ? 4、選擇合適的索引列順序（下面的兩種條件都需要綜合考慮）

? ? ? ? ?1、在不考慮排序和分組的時候，將選擇性較高的列放在前面通常是很好的。

? ? ? ? ? ? ? 考慮下面的一種極端的情況：

? ? ? ? ? ? ? ? ? ?雖然列A的選擇性很高，但是A的列有個值B重復率很大，如果where A=B來查詢的話，則效率會很低。此時應該由上層的程序來控制不走這個索引。

? ? ? ? ? ? ? ? ? 極端的情況下B值的重復lv很高，高到和不走索引是一樣的。

? ? ? ? ?2、需要根據運行頻率最高的查詢來調整索引列的順序。

? ? 三、聚簇索引

? ? ? 不是一種索引方式而是一種存儲方式，代表主鍵和數據緊湊的存儲在一起。思考下聚簇索引的優點和缺點：

? ? 優點：1、覆蓋索引查詢可以直接使用主鍵值

? ? ? ? ? ? ?2、因為索引和數據存儲在同一個樹上，因此查找快。

? ? ? ? ? ? ? 3、聚簇

? ? ?缺點：1、主鍵需要遞增，否則會造成頁分裂和碎片等。

? ? ? ? MySql的數據文件就是索引文件，非葉子節點存儲的是主鍵，葉子節點存儲了全部的數據。

? ? ? ? 非聚簇索引也叫輔助索引。查詢的時候一般是先根據輔助索引查詢到主鍵之后再根據聚簇索引查詢到所有的數據行。

? ? ? ? ?如果沒有定義主鍵，INNODB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，INNODB會隱式的定義一個主鍵作為聚簇索引。

? ? ? ? 聚簇索引的插入速度嚴重依賴插入順序。

? ? ? ? 更新聚簇索引的成本好高，因為需要將索引移到新的位置。

? ? ? ? 基于聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能導致列分裂的問題。

? ? ? ? 使用optimize table命令來重建表并進行優化頁的填充。

? ? ? ? INNODB應該盡可能的按照主鍵的順序插入數據，并且盡可能的使用單調增加的聚簇值來插入新行，避免更新主鍵值。（否則需要移動聚簇索引，同時造成頁分裂，造成碎

? ? ? ? ? ?片）。

? ? ?四、索引覆蓋 ?

? ? ? 如果二級索引能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢

? ? ? 在發起一個覆蓋查詢的時候，explain的extrea列可以看到using index的信息

? ? ? 很多的查詢語句可以通過部分走索引覆蓋而進行優化。

? ? ? 可以通過擴展索引來實現索引覆蓋從而進行優化

? ? ?五、使用索引排序

? ? ? ? ?如果explain的type值為index，則說明mysql可能使用了索引掃描來排序。

? ? ? ? ?MySql可以使用同一個索引既滿足排序，也能用于查找行。

? ? ? ? ?只有當索引的列順序和order by自居的順序完全一致，并且所有的列的排序方向都一致時，MySql才能使用索引來對結果進行排序。

? ? ? ? 如果查詢需要關聯多個表，只有當order by子句引用的字段全部為第一個表時（優化執行器選擇執行的第一個表，不是sql的第一個select表），才能使用索引做排序。

? ? ? ? order by子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴要求，否則mysql都需要執行排序操作，而無法利用索引排序。

? ?六、 kengyu索引

? ? ? ? ?有時候坑與索引能在一定的程度上優化查詢。

? 七、連接查詢

? ? ? ? ?Inner Join

Natural Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join

?八、查詢性能優化

? ? 1、主要是兩個方面進行性能優化

? ? ? ? 1、客戶端到服務器端之間的性能優化（減少數據包的傳輸等）

? ? ? ? ? ? 1、客戶端是否請求了過多的行和列（列少的話可能會走覆蓋索引，同時能減小數據包的大小）

? ? ? ? ? ? 2、客戶端可以先查緩存減少查db的次數

? ? ? ? 2、mysql服務器端和存儲引擎端的優化

? ? ? ? ? ? ?主要是根據3個指標

? ? ? ? ? ? ? ? ?1、響應時間（請求鎖時間，排隊時間，IO時間）

? ? ? ? ? ? ? ? ?2、掃描的行數和返回的行數（一般這個比例是10:1甚至更大）

? ?2、where條件的利用好壞，從好到壞排序

? ? ? ? 1、where用于索引

? ? ? ? 2、服務器層做過濾

? 3、切分查詢（講一個大的查詢切分成小的）

? ? ? ?為什么切分查詢

? ? ? ? ?1、現在的網絡帶寬比較大，所以將一個查詢切分成多個是可行的

? ? ? ? 2、一次執行大量數據的刪除和查詢，會一次占用很多的資源和鎖，給DB造成很大的壓力。這樣做可以將DB的壓力分散到不同的時間。

? ? ? ? 3、將一個大的查詢（如：連接查詢)切分成小的查詢能更好的利用緩存和操作緩存。

? ? ? ? 4、在應用層做關聯，可以更好的對數據庫進行切分，更容易做到高性能和可擴展。

? ? ? ?5、在應用層做關聯查詢，在一個事務中，可以把某個請求結果存儲起來，然后再找個事務的執行過程中就不斷的重復去DB查了，這樣就減少了請求DB的次數。

? ? ? ? ? ?如：如果直接請求mysql執行一個個大sql，返回的結果是沒有子查詢的結果的，如果下次需要子查詢的結果還需要如DB查。

? ?4、客戶端/服務器端的通信協議

? ? ? ?1、半雙工，這導致兩方對通訊過程不能有很好的控制能力，在發送出去報文之后就只能干等著了。

? ? ? 2、客戶端用一個單獨的數據包將查詢發送給服務器。max_allowed_packet就規定了這個數據包的大小，客戶端發送完請求之后只能等著了。

? ? ? 3、服務器端的響應由多個數據包組成，客戶端只有接收到完整的全部結果之后，服務器端才會釋放資源。

? ? ? 4、客戶端默認是先緩存全部的結果集，使得mysql服務器端能盡快的釋放掉資源。客戶端可以通過參數決定自己是不是緩存全部的結果集。jdbc也有api能設置。

? ? ? 5、服務器得到第一條數據的時候就可以返回數據了，這樣客戶端也可以同時處理數據了。服務器不是先查詢到所有的數據然后才返回數據，是查詢到第一條有效數據就返回數

? ? ? ? ? ?據，這樣可以避免即使服務器端需要返回大量的數據，也不會占用大量的內存。mysql會針對每一行使用通訊協議進行包裝然后寫到socket，當然tcp可能會把多行以一個批次傳輸。問題：客戶端可以指定是否緩存服務器端的數據，那么這個時候服務器端返回數據是不是就

? ? ? ? ? ? 受到限制了？

SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client. This option can be used only for top-level SELECT statements, not for subqueries or following UNION.

這樣能盡快的釋放鎖，但是會占用mysql的大量內存和資源

? ? ? ? ?

九、其他

? ? 1、mysql在生成執行計劃的時候，5.6版本之前如果一個語句里面含有子查詢時，子查詢是需要執行的，但是5.6解除了這個限制。

? ?2、當掃描大量的行數時，可以采取下面的措施（p201）

? ? ? ?1、使用索引覆蓋

? ? ? ?2、使用匯總表

? ? ? 3、重寫查詢

? 3、查看各個連接的狀態

? ?show ? full ?processlist

? ? ? 在command列能看到各個查詢現在的狀態

? ? ? 1、sleep

? ? ?2、query

? ? ?3、locked

? ? ?4、analysing and statistics

? ? ?5、copying to tmp table[on disk]

? ? ? ? group by,sort，union的時候會使用臨時文件

? ? ? 6、sorting ? result

? ? ?7、sending ?data

十、explain和explain extend

? ? ? explain extand能額外的查到mysql執行優化器優化之后的sql語句

? ? ? 1、type（訪問類型）

? ? ? ? ? ?all ? 聚簇索引去掃描整個表（limit時不會掃描整個表），此時磁盤是順序訪問的。

? ? ? ? ? ? ? ? ?1、只有聚簇索引 2、不能走索引覆蓋?

? ? ? ? ? ?index ? 索引掃描整個表（使用索引排序的時候會走）

? ? ? ? ? ? ? ? ? 1、extra列里面有using index時只是磁盤順序讀取所有的索引，并不會再根據聚簇索引去磁盤隨機讀取其他列的數據

? ? ? ? ? ? ? ? ? 2、extra列里面沒有using index時，此時還需要根據聚簇索引去隨機讀取其他列，比較好性能。

? ? ? ? ? ?range

? ? ? ? ? ? ? ? ? ?使用索引范圍查詢

? ? ? ? ? ? ? ? ? ?IN，OR也是顯示的range，但是和正常的>這種范圍查詢有區別

? ? ? ? ? ref（非唯一性索引或者使用索引前綴查詢）

? ? ? ? ? ? ? ? ?索引等值查詢，但是可能查詢多多行

? ? ? ? ? eq_ref（唯一性索引，例如主鍵id）

? ? ? ? ? ? ?索引等值查詢，但是只可能查詢到一條語句。

? ? ? ? ? ?const,system

? ? ? ? ? ? ? ?不用執行能根據SQL和mysql統計信息就能得到結果，如：select id from ?XXX where id=1;

? ? ? ? ? NULL

? ? ? ? ? ? 不用執行就能得到結果

? ? ? ? ?分析：all和index？

? ? ? ? ? ? ? ? ?all和index都是掃表。當是索引覆蓋查詢，或者能利用索引排序的時候才會走

? ?2、rows

? ? ? 大概顯示會掃描到多少行（如果索引覆蓋查詢的話，一個索引也算一行，所以這里rows指的不是掃描行同時得到所有的列）

? ? ? ?rows也不能表明limit，如select * from XX limit 1,實際上mysql只會查詢掃描一列就結束。

? ?3、key_len（所使用索引的最大程度）

? ? ? ?索引的長度，能根據這個值確定最終sql在聯合索引里面到底使用了前幾個列走索引

? ?4、possible_keys

? ? ? 可能走的索引，具體的例子見案例分析的第6個

? ?5、key

? ? ?優化器從possible_keys里最終選擇的索引（根據IO次數選擇合適的，具體的例子見案例分析的第6個）

? ?6、id

? ? ? ?根據sql里面的id的順序由1遞增排列

? ?7、explain的執行結果順序

? ? ? ?explain的執行結果里面的id可能不是遞增的，explain顯示的列的順序就是mysql的執行順序。

? ?8、select_type

? ? ? 1、simple

? ? ? 2、SUBQUERY（DEPENDENT_SUBQUERY）

? ? ? ? ?select （select XX from XX） from ?XX

? ? ? ? ? 括號里面的是SUBQUERY

? ? ?3、UNION

? ? ?4、DERIVED

? ? ? ? from (select XXX from XXX) 括號里面的是DERIVED

? ? ?5、PRIMARY

? ? ? ? 最外層循環

? ?9、table?

? ? ?1、DERIVED

? ? ?2、真實表

? ? 3、UNION之后的表

? ? ? ? ? ? ?<UNION 1,2>

? 10、extra

? ? ?using ?index （說明只使用該索引，不用再根據索引獲得的id去查詢聚簇索引獲得數據，也就是索引覆蓋）

? ? ? ?,using ? where （不帶where的也可能出現using where，說明查詢可收益于不同的索引）

? ? ? using tempory（臨時表）

? ? ? ? ?using ? filesore（可能是在內存或者磁盤進行排序）

十一、案例分析

? ? ?1、status命令

? ? ? ? ? ?能查看mysql的版本號和服務器的運行快照信息，以及mysql客戶端的信息

? ? ?2、show ?create table;

? ? ? ? ? ?查看建表語句

? ? ?3、索引覆蓋案例

? ? ? ? ? 表結構：

    CREATE TABLE `test` (`id` int(11) NOT NULL,`name` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from test;
執行結果如下：
可以看出執行優化器選擇了索引覆蓋查詢。
注意：任何的索引默認主鍵都是索引的一部分
    當sql沒有指定order by的時候，查詢出的結果是無序的。

select * from test order by id;
執行結果如下：
可以看出優化器選擇了使用索引排序

? ?4、聚簇索引和不是聚簇索引的選擇

? ? ? select ?count(*) from test;

? ? ?可以看出count(*)也是掃描整個索引

? ? ? count(*)因為只用統計數據，肯定走索引覆蓋（聚簇索引或者非聚簇索引）。

? ? ? ?selct count(*)和select a,b基本類似，但是count(*)一般可以走索引覆蓋,extra 肯定包含using index。

? ? ?

?CREATE TABLE `AA` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',`gmt_create` datetime NOT NULL COMMENT '創建時間',`gmt_modified` datetime NOT NULL COMMENT '修改時間',`appkey` varchar(32) NOT NULL COMMENT ,`std_api_id` varchar(64) NOT NULL COMMENT ,`cfg_h_group` varchar(128) NOT NULL COMMENT ,`cfg_key` varchar(128) NOT NULL COMMENT ,`cfg_value` varchar(300) NOT NULL COMMENT '配置項value',`creator` varchar(32) NOT NULL COMMENT '創建人',`modifier` varchar(32) NOT NULL COMMENT '修改人',`cfg_v_group` varchar(32) NOT NULL COMMENT ,PRIMARY KEY (`id`),KEY `index_appkey_std_api_id` (`appkey`,`std_api_id`),KEY `index_appkey_h_group` (`appkey`,`cfg_h_group`)
) ENGINE=InnoDB AUTO_INCREMENT=490004 DEFAULT CHARSET=utf8 COMMENT=''

explain select count(*) from AA

該表存在聚簇索引還有兩個聯合索引，因為肯定要全部掃描所有的行（但不一定是所有的列），優化執行器會比較怎么掃描讀的磁盤塊數（IO數）更少。

1、聚簇索引：葉子部分存儲的是全部的列，所以pass。所以一般count(*)都不會走聚簇索引掃描

2、聯合索引 ? 選擇聯合索引數據量較小的，所以最后優化執行器選擇了下面的執行計劃

select_type

SIMPLE

table

AA

type

index

possible_keys

key

index_appkey_std_api_id

key_len

ref

rows

Extra

Using?index

? ? ??

? ??

?6、索引的選擇

? ? ?explain select count(*) from AA where appkey='aa';

? ? 從執行計劃可以看出，優化執行器鎖定兩個索引（從possible_keys上看出），但是兩個聯合索引都只能選擇使用第一列，所以在兩個聯合索引中選擇索引占的數據較小的那個索

? ? 引，最終生成了下面的執行計劃

select_type

SIMPLE

table

AA

type

ref

possible_keys

index_appkey_std_api_id,index_appkey_h_group

key

index_appkey_std_api_id

key_len

ref

const

rows

Extra

Using?where;?Using?index

? 7、 ? ?select (select XX from XX) from XX類型分析

? ? ? ? ? ?返回的行數？ ? ?等于select count(*) from XX;?

? ? ? ? ? ? select (select id from s) from test;

? ? ? ? ? ? 本質上是個循環嵌套查詢

? ? 8、select常量

? ? ? ? ? 返回多少行數？ ? ?select count(*) from XX; ?

? ? ? ? ? 從執行計劃可以看出，這種sql肯定走索引覆蓋（using index，因為查的是常量）。分析這種sql時和普通的sql一樣對待，只是查的列比較特殊，肯定走索引覆蓋

? ? ? ??

? ? ?9、連接查詢（只支持左外連接，右外連接，內連接（等值連接））

? ? ?雖然沒有 join 關鍵字，但是下面的查詢也是連接查詢。

? ? 下面的查詢沒有where語句，實際是得到一個笛卡爾乘積，執行引擎在執行的時候是遞歸執行，隨意所以外層循環的數量盡可能少，所以查執行table s，然后再執行table

? ? ?test。table ?S只有主鍵，所以type是all。table test可以進行索引覆蓋，所以type是index。?

?10、雖然select可能是全部數據，或者部分數據但是聯合索引能做到覆蓋查詢，而且可以使用索引排序。

11、IN查詢

注意：using where

12、derived深入分析

1、生成derived表的查詢是可以走索引的

2、primary對derived的查詢時不能走索引的，只能走where。

十二、具體的優化

? ? 1、limit分頁

? ? ? ? limit 10000,10，此時mysql需要讀取很多的行去獲得偏移量為10000的數據，但是之前讀取過的10000個數據是無用的。

? ? ? ? 方案1：

? ? ? ? ? ? ? ? select film_id,description from sakila.film order by title limit 50,5

? ? ? ? ? ? ? ? 可以看出需要全表掃描，然后再根據title排序，然后在取偏移量為50的5條數據。性能弱爆了。

? ? ? ?優化成：select film.film_id,film.dscription from sakila.film inner join (select film_id from sakila.film order by title limit 50,5)as lim using(fim_id);

? ? mysql雖然查的前50個沒有用，但是因為using index，只是浪費了50次查一個索引，沒有查到一個索引得到主鍵再去查所有的數據。這里實際上用了延遲技術。

? ? ??