SQL優化-索引
- 索引的優勢和劣勢
- 索引的分類和索引命令陳述句
- 索引分類
- 基本語法
- 判斷是否適合建索引
- 適合建立索引的情況
- 不適合建立索引的情況
- 性能分析
- MySql Query Optimizer
- MySql 常見瓶頸
- EXPLAIN(查看執行計劃)
- 如何使用？
- 包含資訊
- EXPLAIN實際運用
- 創表并插入資料
索引的優勢和劣勢
索引簡單來說是排好序的資料結構，所以它能大幅度的提高查詢效率，降低資料排序的成本，降低資料庫的IO成本，
但是索引也同樣占用很大空間，通常是以索引檔案的形式存盤在磁盤上，并且更新資料的同時得更新相應的索引，
總的來說：索引能提高查詢效率，但降低更新效率，所以經常更新的表盡量不要加索引
索引的分類和索引命令陳述句
索引分類
- 單值索引：一個索引只包含單個列，一個表可以有多個單值索引，
- 唯一索引：索引列的值必須唯一，但允許有空值
- 復合索引：一個索引包含多個列
一張表建議建立索引不要超過五個
基本語法
- 創建：
(1)CREATE [UNIQUE] INDEX indexname ON mytable(columnname(length));
(2)ALTER mytable ADD [UNIQUE] INDEX [indexname] ON (columnname(length)); - 洗掉：DROP INDEX [indexname] ON mytable;
- 查看：SHOW INDEX FROM table_name\G;
(\G存在的情況下列形式顯示，不存在的情況下行形式顯示) - 使用ALTER命令：ALTER TABLE mytable ADD (PRIMARYKEY)((UNIQUE|INDEX|FULLTEXT)indexname)(column_list);
PRIMARYKEY：唯一索引(主鍵索引，一般自動添加)，索引值唯一，且不能為null，
UNIQUE：唯一索引，索引值唯一，可出現null，且null可重復出現，
INDEX：普通索引，索引值可出現多次，
FULLTEXT：全文索引，
判斷是否適合建索引
適合建立索引的情況
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的欄位
- 查詢中與其它表關聯的欄位
- 查詢中排序的欄位
- 查詢中統計或分組的欄位
不適合建立索引的情況
- 頻繁更新的欄位（更新不止是更新記錄，還得更新索引）
- Where陳述句用不到的欄位
- 表的記錄不多
- 頻繁增刪改的表
- 資料列大量重復內容（如：性別，只有 男，女）
一般來說復合索引優于單值索引
性能分析
MySql Query Optimizer
MySql自帶的查詢優化器，MySql有專門負責優化SELECT陳述句的優化器模塊，
MySql 常見瓶頸
- CPU：CPU在飽和的時候，一般發生在資料裝入記憶體或者從磁盤讀取資料的時候，
- IO：磁盤I/O瓶頸常發生在裝入資料遠大于記憶體容量的時候，
- 服務器硬體問題，
EXPLAIN(查看執行計劃)
使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢陳述句，從而知道MySql是如何處理你的SQL陳述句，從中得出如何優化你的SQL陳述句，
如何使用？
在SQL陳述句前+EXPLAIN關鍵字（如：explain select * from userdb）
包含資訊
- id：select查詢的序列號，表示操作順序，id相同的情況下順序由上到下，id不同的情況下順序由大到小，
- select_type：查詢型別，主要用于區別普通查詢，聯合查詢，嵌套查詢等的復合查詢，
- table：顯示這一行資料是關于哪張表
- type：從好到懷：system(表只有一行記錄，系統表)>const(通過一次索引就找到)>eq_ref(唯一索引掃描)>ref(非唯一索引掃描)>range(只索引給定范圍)>index(全索引掃描)>all(全表掃描) 一般來說要保證達到range級別
- possible_keys：可能用到的哪些索引
- key：實際用到的索引
- key_len：索引中使用的位元組數，在不損失精確性的情況下，越小越好，
- ref：顯示索引的哪些列被使用
- rows：查找到所需資料的行數
- extra： 加分項：USING index , 扣分項：USING filesort、USING temporary（有扣分項的情況下盡量優化）
EXPLAIN實際運用
創表并插入資料
CREATE TABLE IF NOT EXISTS blogs(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category VARCHAR(20) NOT NULL,
views INT(20) NOT NULL,
assist INT(20) NOT NULL,
comments VARCHAR(20) NOT NULL,
title VARCHAR(255) NOT null,
content TEXT NOT NULL);
INSERT INTO blogs(author_id,category,views,assist,comments,title,content)VALUES
(1,'java',821,100,'1','java','1'),
(2,'sql',534,100,'2','sql','1'),
(3,'c',1928,100,'3','c','1'),
(4,'c++',851,100,'4','c++','1'),
(5,'c#',89,50,'5','c#','1');
- 沒有建立索引的情況下用EXPLAIN：
EXPLAIN SELECT id, author_id from blogs where comments = '1' and views > 1 ORDER BY assist desc LIMIT 1;
- 查找表中的索引
show index from blogs;
這里只有sql自主建的主鍵索引
- 建立索引
create index idx_blogs_cva on blogs (comments,views,assist);
- 再查找表中的索引
show index from blogs
- 再用EXPLAIN查看性能
EXPLAIN SELECT id, author_id from blogs where comments = '1' and views > 1 ORDER BY assist desc LIMIT 1;
這里很明顯已經用到了索引，但是Extra里面出現了Using filesort，SQL需要優化
- 優化方式：洗掉索引，再建立comments，assist索引（具體原因，后面會說到）
Drop index idx_blogs_cva on blogs;
create index idx_blogs_ca on blogs(comments,assist);
EXPLAIN SELECT id, author_id from blogs where comments = '1' and views > 1 ORDER BY assist desc LIMIT 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/3210.html
標籤：其他