【MYSQL】order by 和group by索引優化

order by

• 1.order by 子句，盡量使用index方式排序，避免使用 filesort方式排序（即explain select...,查看Extra的欄位，using index 優於 using filesort）
• 2.盡可能在索引列上完成排序操作，遵照索引的最佳左前綴
• 3.如果不在索引列上，filesort會使用到sort_buffer的容量去進行排序，如果查詢的字段的總數據超出了sort_buffer的容量，就會導致每次只能取sort_buffer容量大小的數據，進行排序（創建tmp文件，再合併），排完後再次取sort_buffer容量大小，再排.....大量的I/O操作。解決：增大max_length_for_sort_data參數的設置＋增大sort_buffer_size參數的設置

提高order by 的速度

• order by 時 大忌是使用 select * 
• 當query的字段大小總和小於 max_length_for_sort_data 而且排序字段不是TEXT | BLOB類型時，會用上列第三個算法排序
• 當嘗試提高sort_buffer_size，此參數會提高效率，但實際還是要根據系統的能力去提高，因為此值針對每個進程的
• 當嘗試提高max_length_for_sort_data，如果設太高，數據總容量會超出sort_buffer_size的概率就會增大，反而是提高硬碟I/O活動和降低處理器使用率

範例：order by為排序使用索引

MYSQL兩種排序方式：結果直接或結果字段使用到索引即產生有效排序

MYSQL能為排序與查詢使用相同的索引

key a_b_c(a,b,c)

order by 可以使用到索引最左前綴

-order by a

-order by a,b

-order by a,b,c

-order by a desc, b desc , c desc

如果where使用索引的最左前綴定義為常量(const)，則order by 能使用索引

-where a = const order by b,c

-where a = const and b = const order by c

-where a = const order by b,c

-where a = const and b > const order by b,c

不能使用索引進行排序

-order by a asc, b desc , c desc  (因排序不一致)

-where f = const order by b,c  (a索引丟失)

-where a = const order by c  (b索引丟失)

-where a = const order by a,d  (d非索引)

-where a in (...)  order by b,c  (a = const order by a,d (d非索引)

-where a in (...) order by b,c (對於排序來說，多個相等條件也是範圍查詢)

group by 同order by

• group by 是先排序後再進行分組，遵照索引建的最佳左前綴原則
• 當無法使用索引列時，增大max_length_for_sort_data參數的設置＋增大sort_buffer_size參數的設置
• where 高於having，能寫在where限定的條件就不要去having限定了

【MYSQL】EXPLAIN - 欄位說明解釋

EXPLAIN - 欄位說明解釋

mysql> explain select * from PROCESSLIST;
+----+------------+------+------------+------+--------------+----+---------+----+-----+--------+-------+
| id | select_type| table| partitions | type | possible_keys| key| key_len | ref| rows|filtered| Extra |
+----+------------+------+------------+------+--------------+----+---------+----+-----+--------+-------+
1 row in set, 1 warning (0.00 sec)

欄位及欄位值說明：
【id】 : 此數字表示執行select 操作表的順序，id越大最優先123的順序就是321

• 情況1：id相同，執行順序由上至下
• 情況2：id不同，如果是子查詢，id的序號會遞增，id值越大優先執行
• 情況3：id相同與不同都有，可認為是同一組，從上往下順序執行，id值越大則優先執行

【select_type】:

• SIMPLE：簡單查詢，查詢中不含子查詢或UNION

• PRIMARY：查詢中若包含任何複雜的子部份，最外層的查詢則被標記為primary

• SUBQUERY：在select或where列表中包含子查詢

• DERIVED：衍生表，在FROM列表中包含的子查詢被標記為DERIVED，MYSQL會將歸類這些子查詢，於臨時表中

【MYSQL】索引種類與說明

索引可以提高查詢的速度，對於常常查詢的欄位使用索引，有無設定的速度差可能會好幾倍。所以
範例1~例如：若資料上萬筆

SELECT * FROM table_name where datetime>= '2019-03-22 00:00:00'



AND datetime <= '2019-03-22 23:59:59';

如果在datetime欄位加上索引，和一個沒加索引若資料量龐大，兩個查詢的速度會差很多!

加上索引的，MYSQL會優先針對datetime去查詢，沒加索引的表，則是會全文搜尋。

索引(index)的類型

1.主鍵索引(primary key)。

語法：ALTER TABLE table_name ADD primary key (column) ;

• 不能為NULL
• 不能重覆
• 一個表只能有一個主鍵索引。

例如：用於，自動編號、身份證字號....等

2.唯一索引(unique index)。

語法：ALTER TABLE table_name ADD unique (column) ;
或CREATE UNIQUE INDEX index_name ON table_name(column);

• 可以為NULL 
• 不能重覆

例如：像是手機有號碼，E-MAIL....等，有些人不一定有手機或E-MAIL

3.一般索引、普通索引。

語法：（填加或直接創建）ALTER TABLE table_name ADD INDEX index_name (column) ;
或CREATE INDEX index_name ON table_name (column);

• 最基本的索引，無任何限制

4.組合索引。

語法：ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

• 組合索引中最左邊的欄位column1一定要出現在條件中，才會使用索引

例如：以下兩個查詢會用到索引

SELECT * FROM table_name WHERE column1=val1;


SELECT * FROM table_name WHERE column1=val1 AND column2=val2;

以下兩個查詢不會用到索引，請注意條件中 column2並非索引的第一個欄位

SELECT * FROM tbl_name WHERE column2=val2;


SELECT * FROM tbl_name WHERE column2=val2 AND column3=val3;

5.全文索引。

語法：ALTER TABLE table_name ADD FULLTEXT (column);


或CREATE FULLTEXT INDEX index_name ON table_name(column);

查看索引，可以用三種方法：

SHOW {INDEX | INDEXES | KEYS} from table_name;

或是查看資料庫內的索引：

SHOW {INDEX | INDEXES | KEYS} from database_name;

或是針對表查看進行管理：

show create table 表名\G

刪除索引：

DROP INDEX index_name ON table_name;

特殊案例
測試EXPLAIN - 效能分析語句
在SELECT 前加入EXPLAIN 可以查出語句的執行效能
以下為例當我在ID上加上索引，實際查詢where id=20067，查詢時間很快

mysql> select id from users where id = 20067;
+-------+
| id    |
+-------+
| 20067 |
+-------+
1 row in set (0.00 sec)

mysql> explain select id,loginname from users where id = 20067;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | id            | id   | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

注意ID+1，其實都是查同一筆資料，但索引未產生效用MYSQL無法解析程式

mysql> select id from users where id +1 = 20068;
+-------+
| id    |
+-------+
| 20067 |
+-------+
1 row in set (0.88 sec)

mysql> explain select id,loginname from users where id +1 = 20068;
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | users | index | NULL          | id   | 4       | NULL | 576128 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)