在學(xué)習(xí)SQL優(yōu)化之前，我們先來學(xué)習(xí)一下存儲引擎，因為不同的存儲引擎在增刪改查方面的性能有所不同。

存儲引擎

存儲引擎就是存儲數(shù)據(jù)、建立索引、更新查詢數(shù)據(jù)等等技術(shù)的實現(xiàn)方式。存儲引擎是基于表的，而不是基于庫的，所以存儲引擎也可被稱為表類型。
Oracle、SqlServer等數(shù)據(jù)庫只有一種存儲引擎，Mysql提供了插件式的存儲引擎架構(gòu)，所以Mysql存在多種存儲引擎，可以自由選擇。
Mysql5.5之前的默認(rèn)存儲引擎是MyISAM，5.5之后就改為了InnoDB。

InnoDB

InnoDB存儲引擎是Mysql5.5之后默認(rèn)的存儲引擎，InnoDB存儲引擎提供了具有提交、回滾、崩潰恢復(fù)能力的事務(wù)安全，但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。

InnoDB相比其他的存儲引擎的特點：

1、支持事務(wù)；
2、支持行鎖；
3、支持外鍵：Mysql中唯一支持外鍵的存儲引擎；
4、存儲方式：表結(jié)構(gòu)保存在.frm文件中，表數(shù)據(jù)和索引保存在.ibd文件中。

MyISAM

MyISAM不支持事務(wù)，也不支持外鍵，其優(yōu)勢是訪問的速度快，對事務(wù)的完整性沒有要求或者以select,insert為主的應(yīng)用基本上都可以使用這個引擎來創(chuàng)建表。

MyISAM的表有以下特點：

1、不支持事務(wù)；
2、不支持外鍵；
3、支持表鎖；
4、存儲方式：表結(jié)構(gòu)保存在.frm文件中，表數(shù)據(jù)保存在.MYD文件中，索引保 存在.MYI文件中。

接下來我們來學(xué)習(xí)SQL優(yōu)化

SQL優(yōu)化

在對sql語句進行優(yōu)化之前，我們需要使用explain來分析它的執(zhí)行計劃，找到需要優(yōu)化的地方。

explain分析執(zhí)行計劃

explain select * from test where id = 1;

執(zhí)行結(jié)果如下：

explain 之 type

type顯示的是訪問類型，是較為重要的一個指標(biāo)，可取值為：
1、NULL：Mysql不訪問任何表、索引，直接返回結(jié)果。例如：explain select now();
2、system：表只有一行記錄，這是const類型的特例，一般不會出現(xiàn)。
3、const: 表示通過索引一次就找到了，const用于根據(jù)primary key主鍵 或者unique唯一索引查詢。因為只匹配一行數(shù)據(jù)，所以很快。
4、eq_ref: 類似ref，區(qū)別在于使用的是唯一索引，使用主鍵的關(guān)聯(lián)查詢， 查詢出的記錄只有一條。常見于主鍵或唯一索引掃描。
5、ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質(zhì)上也 是一種索引訪問。
6、range: 只檢索給定返回的行，使用一個索引來選擇行。Where之后出 現(xiàn)between,<,>,in等操作。
7、index：索引，index與ALL的區(qū)別為，index類型只是遍歷了索引樹，通常比 ALL快，ALL是遍歷數(shù)據(jù)文件。
8、ALL：將遍歷全表以找到匹配的行。

結(jié)果值從最好到最壞依次是：
NULL--> system--> const --> eq_ref --> ref --> range --> index --> ALL
一般來說，我們需要保證查詢至少達到range級別，最好達到ref。

explain 之extra

using filesort:說明Mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照 表內(nèi)的索引順序進行讀取，稱為“文件排序”，效率低。
using temporary:使用了臨時表保存中間結(jié)果，Mysql在對查詢結(jié)果排序時 使用臨時表，常見于order by 和 group by，效率低。
using index:表示相應(yīng)的select操作使用了覆蓋索引，避免訪問表的數(shù)據(jù)行， 效率不錯。

索引的使用

索引是數(shù)據(jù)庫優(yōu)化最常用也是最有效的手段之一，我們在寫sql語句時盡量能使用索引，避免索引失效，這樣就能有效的提高sql語句的執(zhí)行效率，從而提高系統(tǒng)性能。

避免索引失效：

1、全值匹配，對復(fù)合索引中所有列都指定具體值，該情況下，索引生效， 執(zhí)行效率高；

2、最左前綴法則：如果對多個列建立的復(fù)合索引，要遵守最左前綴法則， 指的是查詢條件中必須帶索引的最左列，并且不跳過索引中的列；

3、范圍查詢右邊的列，不能使用索引：指在復(fù)合索引中，一個索引的列 使用的是between、>、<等范圍查詢條件，則該條件后的列索引失效；

4、如果在索引列上進行運算操作，索引將失效；

5、字符串不加單引號，造成索引失效；

6、盡量使用覆蓋索引（查詢結(jié)果只包含索引的列），避免select *；

7、用or分割開的條件，如果or之前的條件列有索引，之后的列沒有索引，那么涉及的索引都不會被用到；

8、以%開頭的like模糊查詢，索引失效，如果是尾部模糊匹配，索引不會失效，如果是開頭模糊匹配，索引失效，可以使用覆蓋索引解決該問題；

9、如果Mysql評估使用索引比全表掃描慢，則不使用索引；

10、is null和is not null有時索引失效，是根據(jù)數(shù)據(jù)庫中的數(shù)據(jù)量來判斷 的，如果索引列NULL值比較多，占據(jù)了絕大多數(shù)，那么is not null就走索引，反之，如果NULL值比較少，那么is null就走索引；

11、in走索引，not in索引失效；

12、單列索引和復(fù)合索引，盡量使用復(fù)合索引，而少使用單列索引。單列索引在查詢時，數(shù)據(jù)庫會選擇一個最優(yōu)的索引（辨識度最高索引來使用，并不會使用全部索引。