SQL為什么需要優化？

對于初學者來說，能夠寫出實作功能的SQL陳述句而不出錯，查詢出所需要的結果，就已經能夠滿足日常使用了，但在某些場景，對性能的要求比較高，因此，要求SQL的執行回應速度快，就需要對SQL進行一定程度的優化，
在實際應用場景中，MySQL經常會存在諸如性能低、執行時間過長、等待時間過長、SQL陳述句欠佳（尤其是連接查詢）、索引失效、服務器引數設定不合理等問題，這時候就需要對SQL進行優化，從而達到我們所需要的的性能需求，

SQL決議程序

要對SQL進行優化，首先需要知道SQL的決議程序是什么樣子的，在此之前，我們要明確SQL撰寫程序和決議程序的區別，

SQL撰寫程序

select [distinct] ... from ... join ... on ... where ... group by ... having ... order by ... limit ...;

SQL決議程序

from ... on ... join ... where ... group by ... having ... select [distinct] ... order by ... limit ...;

以上語法中sql關鍵字的含義，不是本文的重點，網路上有很多教程，此處不再說明，我們只需要知道，SQL的撰寫程序和實際決議程序并不是一致的，這點在后續的相關優化中將會進一步說明，

索引

索引相當于字典的目錄，其目的是幫助在MySQL中更快的查詢到所需要的資料，其本質是一種BTREE的資料結構，
所以可以得出一個結論：索引是一種資料結構， 如果您對資料結構有所了解，可以更明白的講，索引是一種叫樹的資料結構，樹有很多種，如二叉樹，哈希樹等，索引是B樹（和二叉樹比較類似），

舉個例子說明：
假設我們有一張表student，其結構及其中的資料如下：

其中，score列是索引，那么，該索引的大致結構是如下圖所示的樣子：

因此，如果有這樣一條SQL:

select score from student where score = 77;

如果沒有索引，那么需要全表掃描，從第一條資料開始，需要到第5次才能查找到我們所需要的資料；而如果有了索引，則只需要3次就能查找到(75->62->77)，由此可見，索引確實能夠提升查詢效率，尤其是當表中的資料量特別大，達到了百萬級別，甚至千萬級別的時候，索引的優勢就更加明顯，

Btree除了常見的二叉樹，還有三叉樹，三叉樹的結構如下所示：

Btree一般指的都是B+樹，實際上，索引的資料全部存盤在葉節點中，這也就意味著，對于Btree中，查詢任意資料的次數都是n次（n為樹的深度），

由于客戶端和服務器之間主要是通過IO，所以索引會大大降低IO的使用率，并且能一定程度的降低CPU的使用率，（比如SQL陳述句中有order by，由于索引的資料結構本身就是排好序的，所以直接省去了這一步，從而降低CPU使用率），

索引固然有諸多好處，但也有一定的弊端：

• 索引本身很大，因為它本身也是資料結構，存盤時必然要占空間；
• 索引不是所有情況均適用
  • 少量資料（資料量少，比如只有一條資料，沒有必要通過索引查詢）
  • 頻繁更新的欄位（因為索引是B樹，頻繁更新的欄位除了要更新該欄位本身，還得更新索引的值，甚至會引起索引結構的變動）
  • 很少使用的列（很少使用，意味著不經常查詢，設定索引意義不大）
• 索引確實可以提高查詢的效率，但會降低增刪改的效率（因為對資料增刪改，同時會引起索引的變動，需要額外對索引進行增刪改，而實際應用中，因為查詢使用到的場景遠遠多于增刪改，所以索引還是有存在的必要的），

索引分類：

• 單值索引：單列的值，一張表可以有多個單值索引，
• 唯一索引：不能重復（unique index）
• 復合索引：多個列構成的索引，相當于書的二級目錄，（不是100%多個索引同時用）
• 主鍵索引：如果一個欄位設定為主鍵（primary key），則默認是主鍵索引，因此主鍵索引也不能重復，

主鍵索引和唯一索引的區別是：主鍵索引列的值不能為null，唯一索引列的值可以為null，

索引常見操作

創建索引：

create 索引型別 索引名 on 表（欄位）
alter table 表明 add 索引型別 索引名(欄位)

洗掉索引：

drop index 索引名 on 表名

查詢索引：

show index from 表名

以本文中的student表為例，加以說明：

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| score | double   | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

在student表上對name欄位創建單值索引stu_idx1：

mysql> create index stu_idx1 on student(name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

在student表上對id欄位創建唯一索引stu_idx2：

mysql> create unique index stu_idx2 on student(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

在student表上對name，score欄位創建復合索引stu_idx3:

mysql> create index stu_idx3 on student(name,score);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用alter的方式對student表的score欄位創建單值索引stu_idx4:

mysql> alter table student add index stu_idx4(score);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看創建的索引：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
     |
| student |          0 | stu_idx2 |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
     |
| student |          1 | stu_idx1 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            2 | score       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx4 |            1 | score       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

從以上表格，可以讀出以下資訊：

• 第一條資料，索引名是PRIMARY,這條索引不是手動創建的，而是建表時因為指定了id為primary key，因此自動創建的主鍵索引；
• 第11列Index_type可以看出索引的資料結構均為BTREE；
• 第4、5條資料索引名都為stu_idx3,索引序號Seq_in_index分別為1和2，因此，這兩個是一對，代表是一個復合索引，
• Non_unique值為0，代表是唯一索引（或主鍵索引），為1代表不是唯一索引，也就是說該索引列的值可以重復，

假如要洗掉索引stu_idx2和stu_idx4，則執行如下陳述句：

mysql> drop index stu_idx2 on student;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index stu_idx4 on student;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次查詢：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
     |
| student |          1 | stu_idx1 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            2 | score       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

索引的一些注意事項

創建索引需要注意的事項：

• 選擇表關聯（JOIN）的關聯條件列；
• 資料查詢（WHERE）過濾條件列；
• 重復鍵值數少的列；
• 復合索引應該將重復鍵值數少的列放在首欄位；
• 如果表的資料比較少（如少于1000行），應根據實際情況評估是否需要創建索引；
• 復合索引欄位的個數不建議超過3個；
• 不建議在大欄位（如char(100)等欄位型別）上創建索引；
• 對于頻繁訪問的業務表，索引數量不建議超過5個；
• 對于資料很少變化的靜態表、歷史表，索引數量不建議超過8個；

使用索引需要注意的事項：

• 避免在索引上進行運算；
• 避免使用 in 和 not in；
• 盡量不適用like；
• 避免在索引列上使用函式；
• 避免在索引上使用 is null或 is not null；
• 避免使用 !=, >, < 等符號；
• 避免改變索引列的型別；
• 避免使用having子句；
• 對于復合索引，應按照索引中欄位的順序編制查詢條件 ；
  以上這些已經涉及到后面的索引優化范疇，這里大概有個印象，在下一篇文章中會詳細講述，

唯一索引和主鍵的區別：

• 一張表里可以有多個唯一索引，但只能有一個主鍵；
• 主鍵保證記錄唯一且非空(null)，唯一索引只能保證記錄唯一，可以為空(null)；
• 主鍵一定是唯一索引，但唯一索引可以不是主鍵；
• 主鍵可以被其他表引為外鍵，唯一索引不可以；
• 主鍵是約束，不占空間，唯一索引是資料結構，是表的冗余結構，占存盤空間，這是二者的本質區別，

標籤：MySQL

上一篇：MySQL邏輯分層介紹

下一篇：MySQL優化之執行計劃