iT邦幫忙

0

MySQL 索引觀念詢問

chan15 2012-01-14 02:01:2312311 瀏覽

翻了索引相關的文
大致上歸類了兩個重要的觀念

  1. WHERE 或 ORDER 的項目需要索引
  2. 重複出現的值不需要索引,譬如說 Boolean,或者是固定 Y or N
    但常常會遇到一個欄位來決定是否上架,可能叫 on
    我是用 enum('1','2'),1 代表上架,2 代表下架
    固定都是 1 或者 2,但每次一定都會選擇 WHERE ON = 1
    這種情況的話到底需不需要做 index
10
builder
iT邦新手 2 級 ‧ 2012-01-14 21:24:59
最佳解答

enum('1','2') 只有 2 種變化~ 即使你加上索引了~ 效率也不高~
所以建議此欄位不加索引..

chan15 iT邦新手 5 級 ‧ 2012-01-15 03:16:43 檢舉

請教另一個問題,如果某個欄位 a 在某個單元跟欄位 b 會一起使用 WHERE 篩選
於是建立了 a 跟 b 的群組索引,但在另一個單元又是單獨篩選的話
要另外建 a 的單獨索引嗎?

wiseguy iT邦超人 1 級 ‧ 2012-01-15 21:48:36 檢舉

AB 索引可以供『where A=? and B=?』用,以及供『where A=?』用。
但不能供『where B=? and A=?』用,或者供『where B=?』用。
這是 B-tree 索引的特性。

13
wiseguy
iT邦超人 1 級 ‧ 2012-01-14 20:38:24

你的第1點與第2點不是衝突了嗎?
既然 where 用到,那麼不就是應該要遵循第1點,為它建立索引?

如果你的範圍放在 mysql,那我分享一些索引概念:

  1. 你的第1點是對的,應該說 WHERE 或 ORDER 有索引會更好,但沒索引一樣能做,只是比較差。

  2. 如果 WHERE 或 ORDER 用到複合條件,那麼就要建複合索引。如
    ... where A=AA and B=BB 就要建一個 index(A,B) 的索引。若沒有 AB 索引,但有 A 索引,mysql 會使用,但若只有 B 索引,則 mysql 不會使用。

  3. where 的順序應該跟索引順序一致。如上題所例,如果有 BA 索引,mysql 是不會使用的。因為AB 與 BA 索引建出來的順序完全不一樣,mysql 並不會因為你有 BA 索引就自動把 where 條件互換。

  4. 若對欄位做運算,則該欄位即使有索引也不用。比如『where COL-3 = 10』就不用 COL 索引,但『where COL = 13』就會用。

  5. mysql 主要有四種索引,primary key、unique key、index key、full text searching key。
    PK 是唯一且不能是NULL的欄位所組成;UK 是唯一且可以是 NULL 的欄位所組成;IK 是不唯一且可以是 NULL的欄位所組成。FK 則是字元型態且允許 NULL 的欄位所組成。

  6. my.cnf 中的 key_buffer 值能夠把所有索引 (*.MYI) 容納進去最好。

  7. 可以在 SQL 指令前加上 explain 來顯示該 SQL 是否用到索引。

chan15 iT邦新手 5 級 ‧ 2012-01-14 20:49:47 檢舉

您好,就是因為衝突所以我才上來詢問,因為固定出現值不需要建索引是很多文章都提到的
但像上架這種判斷就剛好衝突

wiseguy iT邦超人 1 級 ‧ 2012-01-15 21:44:39 檢舉

索引的目的,在於避免做 table full scan。
假設你的 table 有一萬筆,這欄位 1 與 2 各五千筆,在建立了 b-tree 索引之後,1 與 2 會被分成兩堆。當你下 WHERE ON = 1 時,只需要五千次 fetch 就能抓到所有的 ON=1 資料 (因為第一次 fetch 就會往 1 的葉節點去,已經去掉五千筆 ON=2 的資料不用 check。)
如果不建索引,那麼不管要找 ON=1 或 N=2 通通都得掃過一萬筆。講到這裡,你就知道索引該不該建了。

更何況建了索引,索引資料比較少,mysql 可以一次 load 進記憶體處理。沒有索引,那意謂著該 table 有多大,你這個 WHERE ON =1 的指令就要讀過那麼多的資料,效能之慘,可想而知。

Andreas iT邦新手 5 級 ‧ 2019-04-17 20:45:26 檢舉

表示,效果還是很好,除非2的比例極少。

2
一級屠豬士
iT邦高手 1 級 ‧ 2012-01-16 15:20:00

狗尾續貂,野人獻曝一下.

<pre class="c" name="code">建立table

CREATE TABLE t0116 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cola CHAR(5),
colb CHAR(5));

輸入資料

INSERT INTO t0116 (cola, colb) VALUES
('A0001', 'B0001'), ('A0002', 'B0002'), ('A0003', 'B0003'), 
('A0004', 'B0004'), ('A0005', 'B0005'), ('A0006', 'B0006');

目前沒有cola與colb的索引,查看簡單的查詢cola情況下的存取計畫.

EXPLAIN SELECT *
FROM t0116
WHERE cola = 'A0004';

test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE cola = 'A0004';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t0116 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
沒有使用索引!

建立cola與colb的復合索引.
CREATE INDEX idx_ab_t0116 ON t0116(cola, colb);

同樣的查詢,再看他的存取計畫.

test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE cola = 'A0004';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t0116 | ref  | idx_ab_t0116  | idx_ab_t0116 | 6       | const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+

可以看到有使用 idx_ab_t0116, 用在where.

試試看單獨colb的.
test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE colb = 'B0004';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t0116 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
沒有使用索引.

再來是組合型.先看cola開頭的.

test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE cola = 'A0003' AND colb = 'B0003';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t0116 | ref  | idx_ab_t0116  | idx_ab_t0116 | 12      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+

test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE cola = 'A0003' OR colb = 'B0006';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t0116 | ALL  | idx_ab_t0116  | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

都有使用索引.
再來看colb開頭的.
test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE colb = 'B0006' OR cola = 'A0003';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t0116 | ALL  | idx_ab_t0116  | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

觀察上面的存取計畫,cola擺前面的cola+colb複合索引,單獨要使用colb時不會引用,故要另外建立索引.
CREATE INDEX idx_onlyb_t0116 ON t0116(colb);

test[test]@localhost>EXPLAIN SELECT * FROM t0116 WHERE colb = 'B0004';
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t0116 | ref  | idx_onlyb_t0116 | idx_onlyb_t0116 | 6       | const |    1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
使用了新索引!

我要發表回答

立即登入回答