MySql索引原理與操作

更新時(shí)間：2022年09月16日 11:29:02 作者：在人間負(fù)債^

索引是有雙面性的，合理的建立索引可以提高數(shù)據(jù)庫(kù)的效率。但是如果沒(méi)有合理的構(gòu)建索引和使用索引，可能會(huì)導(dǎo)致索引失效或者影響數(shù)據(jù)庫(kù)性能，這篇文章主要介紹了MySql索引原理與操作

1. 什么是索引

索引是在數(shù)據(jù)庫(kù)表的字段上添加的，是為了提高查詢效率存在的一種機(jī)制。

一張表的一個(gè)字段可以添加一個(gè)索引，當(dāng)然，多個(gè)字段聯(lián)合起來(lái)也可以添加索引。

索引相當(dāng)于一本書的目錄，是為了縮小掃描范圍而存在的一種機(jī)制。

索引相當(dāng)于一本書的目錄

通過(guò)索引查詢的方式被稱為索引查詢。

在 mysql 數(shù)據(jù)庫(kù)當(dāng)索引也是需要排序的，并且這個(gè)索引的排序和 TreeSet 數(shù)據(jù)結(jié)構(gòu)相同。TreeSet（TreeMap）底層是一個(gè)自平衡二叉樹！在 mysql 當(dāng)中索引是一個(gè) B-Tree 數(shù)據(jù)結(jié)構(gòu)。

遵循左小右大原則存放。采用中序遍歷方式遍歷取數(shù)據(jù)。

2. 索引的實(shí)現(xiàn)原理

提醒：

在任何數(shù)據(jù)庫(kù)中主鍵上都會(huì)自動(dòng)添加索引對(duì)象，id 字段上自動(dòng)有索引，因?yàn)?id 是主鍵。另外在 mysql 中，如果一個(gè)字段有 unique 約束的話，也會(huì)自動(dòng)創(chuàng)建索引對(duì)象。

任何數(shù)據(jù)庫(kù)中，然后一張表的任何一條記錄在硬盤存儲(chǔ)上都有一個(gè)硬盤的物理存儲(chǔ)編號(hào)。在 mysql 中，索引是一個(gè)單獨(dú)的對(duì)象，在不同的存儲(chǔ)引擎以不同的形式存在，

在 MyISAM 存儲(chǔ)引擎中，索引存儲(chǔ)在一個(gè) .MYI 文件中。在 InnoDB 存儲(chǔ)引擎中，索引存儲(chǔ)在一個(gè)邏輯名叫做 tablespace 的當(dāng)中。在 MEMORY 存儲(chǔ)引擎中，索引存儲(chǔ)在內(nèi)存中。不管索引存儲(chǔ)在哪里，索引在 mysql 中都是一個(gè)樹的形式存在。

3. 添加索引的條件

數(shù)據(jù)量龐大（具體多么龐大算龐大，這個(gè)需要測(cè)試，因?yàn)槊恳粋€(gè)硬件環(huán)境不同）
該字段經(jīng)常出現(xiàn)在 where 的后面，以條件的形式存在，也就是說(shuō)這個(gè)字段總是被掃描。
該字段很少的 DML（insert、delete、update）操作。（因?yàn)?DML 之后，索引需要重新排序）

建議不要隨意添加索引，因?yàn)樗饕彩切枰S護(hù)的，太多的話反而會(huì)降低系統(tǒng)的性能。

建議通過(guò)主鍵查詢，建議通過(guò) unique 約束的字段進(jìn)行查詢，效率是比較高的。

4. 索引的操作

1. 創(chuàng)建索引

給 emp 表的 ename 字段添加索引，起名：emp_ename_index

mysql> create index emp_ename_index on emp(ename);

2. 刪除索引

將 emp 表上的 emp_ename_index 索引對(duì)象刪除

mysql> drop index emp_ename_index on emp;

3. 查看一個(gè)sql語(yǔ)句是否使用了索引進(jìn)行檢索

mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

掃描14條記錄：說(shuō)明沒(méi)有使用索引。type = ALL

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

5. 索引的失效

失效的第1種情況：

mysql> select * from emp where ename like '%T';

ename 上即使添加了索引，也不會(huì)走索引進(jìn)行查詢，為什么？

因?yàn)槟：樵兤ヅ渲幸?“%” 開頭了。

盡量避免模糊查詢的時(shí)候以 “%” 開始。

這是一種優(yōu)化的手段。

mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

失效的第2種情況：

使用 or 的時(shí)候會(huì)失效，如果使用 or 那么要求 or 兩邊的條件字段都要有索引，才會(huì)進(jìn)行索引查詢，如果其中一邊有一個(gè)字段沒(méi)有索引，那么另一個(gè)字段上的索引也會(huì)實(shí)現(xiàn)。索引這就是為什么不建議使用 or 的原因。

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 |    16.43 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

失效的第3種情況：

使用復(fù)合索引的時(shí)候，沒(méi)有使用左側(cè)的列查找，索引失效。

什么是復(fù)合索引？

兩個(gè)字段，或者更多的字段聯(lián)合起來(lái)添加一個(gè)索引，叫做復(fù)合索引。

mysql> explain select * from emp where job = 'MANAGER';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_job_sal_index | emp_job_sal_index | 39      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

失效的第4種情況：

在 where 中索引列參加了運(yùn)算，索引失效。

mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from emp where sal + 1 = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

失效的第5種情況：

在 where 中索引列使用了函數(shù)

mysql> explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)