CDA數據分析師 出品
大家好，歡迎來到小編的MySQL課堂。今天我們一起來看一下MySQL中的索引、關聯子查詢以及語句的優化技巧。
一、MySQL的索引
資料庫索引，是資料庫管理系統中一個排序的數據結構，以協助快速查詢資料庫表中數據。
1、索引的意義
索引用於快速找出在某個欄位中有特定值的行。如果不使用索引，MySQL必須從第一條記錄開始檢索表中的每一條記錄，直到找出相關的行，那麼表越大，查詢數據所花費的時間就越多。如果在表中查詢的欄位有索引，MySQL能夠快速到達一個位置去檢索數據文件，而不需要再去查看所有數據，那麼將會節省很大一部分查詢時間。比如說emp表中1W個員工的記錄，要查詢salesman的員工信息，如果沒有索引，伺服器會從表中第一條記錄開始，一條條往下遍歷，直到找到職位為salesman的員工信息。如果有了索引，它會把job這個欄位，通過一定的方法進行存儲，在查詢這個欄位上的信息時，能夠快速找到對應的數據，而不需要再遍歷1W條記錄了。
2、索引的優缺點
所有MySQL的欄位類型都可以添加索引，但是索引也不是越多越好，而是要根據業務數據合理的使用。
優點
· 通過索引對數據進行檢索，大大提高了數據的查詢效率。
缺點
· 創建索引和維護索引要耗費時間，並且隨著數據量的增加所耗費的時間也會增加。
· 索引也需要占空間的，創建的索引太多，索引文件也會占用資料庫的存儲空間。
· 當對表中的數據進行增加、刪除、修改時，索引也需要動態的維護，降低了數據的維護速度。
3、創建索引的原則
1）需要創建索引的情況：
· 主外鍵和唯一約束的欄位自動創建索引
· 頻繁作為查詢條件的欄位應該創建索引
· 查詢中排序的欄位應該創建索引
· 查詢中分組或統計的欄位應該創建索引
2）不需要創建索引的情況：
· 表中記錄太少不需要創建索引
· 需要頻繁增刪改的欄位不適合創建索引
· where子句中用不到的欄位不需要創建索引
· 重複值較多的欄位不需要創建索引
4、索引的結構
索引是在存儲引擎中實現的，使用不同的存儲引擎，所支持的索引也是不同的。在mysql中常用兩種索引結構BTree和Hash，兩種算法檢索方式不一樣，對查詢的作用也不一樣。MyISAM和InnoDB存儲引擎只支持BTREE索引，MEMORY/HEAP存儲引擎支持HASH和BTREE索引。
MySQL的InnoDB存儲引擎是支持hash索引的，不過我們必須啟用，hash索引的創建由InnoDB存儲引擎自動優化創建，我們干預不了。
5、索引的類型
索引的類型可以分類以下幾種：
· 普通索引：最基本的索引，沒有任何限制
· 唯一索引：索引欄位的取值不能重複，可以有空值，但空值也只能出現一次。
· 主鍵索引：索引欄位的取值不能為空，也不能重複。
· 組合索引：一個索引包含多個欄位，只有在查詢條件中使用了創建索引時的第一個欄位，索引才會被使用。
· 全文索引：通過關鍵字符，就能找到該欄位所屬的記錄行。僅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT類型的欄位上使用。
· 空間索引：對空間數據類型（GEOMETRY、POINT、LINESTRING、POLYGON）的欄位建立的索引，僅限MyISAM引擎，且要求索引欄位的取值不能為空。
6、索引的操作
實際上索引也是一張表，創建索引時，資料庫管理系統會在本地磁碟建立索引文件，裡面保存了索引欄位，並指向實體表的記錄。
1）創建索引
創建表的同時須指定索引名、表名和欄位名。
語法：
create index <索引名> on <表名>(<欄位名>);
自動創建索引
· 在表中定義了主鍵約束時，會自動創建一個對應的主鍵索引。
· 在表中定義了外鍵約束時，會自動創建一個對應的普通索引。
· 在表中定義了唯一約束時，會自動創建一個對應的唯一索引。
示例：emp表中的job添加普通索引
mysql> create index job_index on emp(job);
2）查看索引
語法：
show index from <表名>;
示例：查看emp表中的索引
mysql> show index from emp;+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | YES | NULL || emp | 0 | ename | 1 | ename | A | 14 | NULL | NULL | YES | BTREE | | | YES | NULL || emp | 1 | deptno | 1 | deptno | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL || emp | 1 | job_index | 1 | job | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3）使用索引
在查詢語句中使用索引會大大提升數據的檢索速度。示例：
mysql> select ename,job,deptno from emp where job='salesman';+--------+----------+--------+| ename | job | deptno |+--------+----------+--------+| allen | salesman | 30 || ward | salesman | 30 || martin | salesman | 30 || turner | salesman | 30 |+--------+----------+--------+
4）刪除索引
刪除索引只是刪除了表中的索引對象，表中的數據不會被刪除。語法：
drop index <索引名> on <表名>;
示例：
mysql> drop index job_index on emp;mysql> select ename,job,deptno from emp where job='salesman';+--------+----------+--------+| ename | job | deptno |+--------+----------+--------+| allen | salesman | 30 || ward | salesman | 30 || martin | salesman | 30 || turner | salesman | 30 |+--------+----------+--------+
二、SQL關聯子查詢
執行邏輯
對於外部查詢返回的每條數據，內部查詢都要執行一次。在關聯子查詢中是信息流是雙向的。外部查詢的每條記錄傳遞給子查詢，然後子查詢按照條件執行並返回它的記錄。然後，外部查詢根據返回的記錄做出決策。
例題精講
成績表主要信息如下：
要解決的問題是：查詢各科成績前三名的記錄。使用關聯子查詢解決，代碼如下：
select * from sc twhere (select sum(score>t.score) from sc where c_id=t.c_id)<3order by c_id,score desc;
第一步：先執行外層查詢
select * from sc t;
查詢結果也就是該表的全部記錄
第二步：因為子查詢中連接了這個表本身（where cid=t.cid），所以將第一條記錄轉到子查詢。子查詢是sum(score>t.score)即01課程中score>80的人數，所以先判斷score>t.score，滿足為1，不滿足為0，用sum求和即查詢出01課程中score>80的有幾個人。
相當於執行了：
select sum(score>80) from sc where c_id='01';
查詢結果如下：
01課程中score>80的人數為0，即80為第一名。第三步：子查詢的結果進入外部查詢where子句中和3比較，0<3,條件成立，最終輸出t表中的第一條記錄。循環執行：t表中第二、三條記錄轉到子查詢，執行判斷，輸出第二、三條記錄。t表中第四條記錄轉到子查詢，01課程中score>70的人數有3人,3<3條件不成立，所以不輸出t表中第四條記錄。
繼續循環直到t表中最後一條記錄，最終輸出結果按照課程編號和成績進行排序。
三、SQL語句的優化技巧
應用程式慢如牛，原因多多，可能是網絡的原因、可能是系統架構的問題，還有可能是資料庫的原因。那麼如何提高資料庫SQL語句執行速度呢？
程序中嵌入的SQL語句，如果使用了一些優化小技巧，定能達到事半功倍的效果。
1、where子句中不使用 != ,否則放棄索引全表掃描
比較運算符能用 "="就不用"!="，"="增加了索引的使用幾率。
2、儘量避免 NULL 值判斷,否則放棄索引全表掃描優化前 ：
select number from t1 where number is null;
優化後 ：在number列上設置默認值0,確保number列無NULL值
select number from t1 where number=0;
3、儘量避免 or 連接條件,否則放棄索引全表掃描優化前 ：
select id from t1 where id=10 or id=20 or id=30;
優化後：
select id from t1 where id=10union allselect id from t1 where id=20union allselect id from t1 where id=30;
4、模糊查詢儘量避免使用前置 % ,否則全表掃描
select name from t1 where name like "%c%";
5、儘量避免使用 in 和 not in,否則全表掃描
select id from t1 where id in(1,2,3,4);select id from t1 where id between 1 and 4;
6、儘量避免使用 select * ...;用具體欄位代替 * ,不要返回用不到的欄位
SQL調優方法有很多種，同樣的查詢結果可以有很多種不同的查詢方式。其實最好的方法就是在開發環境中用最貼近真實的數據集和硬體環境進行測試，然後再發布到生產環境中。