MySql索引原理與操作
更新時間：2022年09月16日 11:29:02 作者：在人間負(fù)債^
索引是有雙面性的，合理的建立索引可以提高數(shù)據(jù)庫的效率。但是如果沒有合理的構(gòu)建索引和使用索引，可能會導(dǎo)致索引失效或者影響數(shù)據(jù)庫性能，這篇文章主要介紹了MySql索引原理與操作
1. 什么是索引
索引是在數(shù)據(jù)庫表的字段上添加的，是為了提高查詢效率存在的一種機制。
一張表的一個字段可以添加一個索引，當(dāng)然，多個字段聯(lián)合起來也可以添加索引。
索引相當(dāng)于一本書的目錄，是為了縮小掃描范圍而存在的一種機制。
索引相當(dāng)于一本書的目錄
通過索引查詢的方式被稱為索引查詢。
在 mysql 數(shù)據(jù)庫當(dāng)索引也是需要排序的，并且這個索引的排序和 TreeSet 數(shù)據(jù)結(jié)構(gòu)相同。TreeSet（TreeMap）底層是一個自平衡二叉樹！在 mysql 當(dāng)中索引是一個 B-Tree 數(shù)據(jù)結(jié)構(gòu)。
遵循左小右大原則存放。采用中序遍歷方式遍歷取數(shù)據(jù)。
2. 索引的實現(xiàn)原理
提醒：
在任何數(shù)據(jù)庫中主鍵上都會自動添加索引對象，id 字段上自動有索引，因為 id 是主鍵。另外在 mysql 中，如果一個字段有 unique 約束的話，也會自動創(chuàng)建索引對象。
任何數(shù)據(jù)庫中，然后一張表的任何一條記錄在硬盤存儲上都有一個硬盤的物理存儲編號。在 mysql 中，索引是一個單獨的對象，在不同的存儲引擎以不同的形式存在，
在 MyISAM 存儲引擎中，索引存儲在一個 .MYI 文件中。在 InnoDB 存儲引擎中，索引存儲在一個邏輯名叫做 tablespace 的當(dāng)中。在 MEMORY 存儲引擎中，索引存儲在內(nèi)存中。不管索引存儲在哪里，索引在 mysql 中都是一個樹的形式存在。
3. 添加索引的條件
- 數(shù)據(jù)量龐大（具體多么龐大算龐大，這個需要測試，因為每一個硬件環(huán)境不同）
- 該字段經(jīng)常出現(xiàn)在 where 的后面，以條件的形式存在，也就是說這個字段總是被掃描。
- 該字段很少的 DML（insert、delete、update）操作。（因為 DML 之后，索引需要重新排序）
建議不要隨意添加索引，因為索引也是需要維護的，太多的話反而會降低系統(tǒng)的性能。
建議通過主鍵查詢，建議通過 unique 約束的字段進行查詢，效率是比較高的。
4. 索引的操作
1. 創(chuàng)建索引
給 emp 表的 ename 字段添加索引，起名：emp_ename_index
mysql> create index emp_ename_index on emp(ename);
2. 刪除索引
將 emp 表上的 emp_ename_index 索引對象刪除
mysql> drop index emp_ename_index on emp;
3. 查看一個sql語句是否使用了索引進行檢索
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條記錄：說明沒有使用索引。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 上即使添加了索引，也不會走索引進行查詢，為什么？
因為模糊查詢匹配中以 “%” 開頭了。
盡量避免模糊查詢的時候以 “%” 開始。
這是一種優(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 的時候會失效，如果使用 or 那么要求 or 兩邊的條件字段都要有索引，才會進行索引查詢，如果其中一邊有一個字段沒有索引，那么另一個字段上的索引也會實現(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ù)合索引的時候，沒有使用左側(cè)的列查找，索引失效。
什么是復(fù)合索引？
兩個字段，或者更多的字段聯(lián)合起來添加一個索引，叫做復(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 中索引列參加了運算，索引失效。
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)
6. 索引的類型
- 單一索引：一個字段上添加索引
- 復(fù)合索引：兩個或多個字段上添加索引
- 主鍵索引：主鍵上添加索引
- 唯一性索引：具有 unique 約束的字段上添加索引
注意： 唯一性比較弱的字段上添加索引用處不大。
相對來說，唯一性越高，效率越高。
到此這篇關(guān)于MySql索引原理與操作的文章就介紹到這了,更多相關(guān)MySql索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL無服務(wù)及服務(wù)無法啟動的終極解決方案分享
又是MySQL的問題,之前已經(jīng)遇見過一次本地MySQL服務(wù)無法啟動的情況,現(xiàn)在又出現(xiàn)了,下面這篇文章主要給大家介紹了關(guān)于MySQL無服務(wù)及服務(wù)無法啟動的終極解決方案,需要的朋友可以參考下2022-06-06
MySQL之select、distinct、limit的使用
這篇文章主要介紹了MySQL之select、distinct、limit的使用，下面文章圍繞select、distinct、limit的相關(guān)資料展開聚集內(nèi)容,需要的朋友可以參考一下2021-11-11
在MySQL中可以使用ALTER?TABLE語句來修改表結(jié)構(gòu),包括添加自增屬性,下面這篇文章主要給大家介紹了關(guān)于Mysql修改字段類型、長度及添加刪除列的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-04-04
當(dāng)unique列在一個UNIQUE鍵上插入包含重復(fù)值的記錄時，默認(rèn)insert的時候會報1062錯誤，MYSQL有三種不同的處理方法，下面我們分別介紹。2012-09-09
這篇文章主要為大家介紹了Mysql?InnoDB?B+樹索引目錄項記錄頁管理，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-05-05
VS2019連接mysql8.0數(shù)據(jù)庫的教程圖文詳解
這篇文章主要介紹了VS2019連接mysql8.0數(shù)據(jù)庫的教程，本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-05-05
這篇文章主要給大家介紹了關(guān)于如何使用MySQL查詢一年中每月的記錄數(shù)的相關(guān)資料,文中通過實例代碼以及圖文介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2022-09-09
本文主要介紹了Mysql事物阻塞的實現(xiàn),阻塞并不是一件壞事,其是為了確保事務(wù)可以并發(fā)且正常地運行,具有一定的參考價值,感興趣的可以了解一下2024-04-04
SQL中distinct去重關(guān)鍵字使用和count統(tǒng)計組合使用方法
這篇文章主要給大家介紹了關(guān)于SQL中distinct去重關(guān)鍵字使用和count統(tǒng)計組合使用的相關(guān)資料,count()是SQL中提供的用于統(tǒng)計記錄數(shù)量的函數(shù),需要的朋友可以參考下2024-08-08
MySQL使用binlog日志做數(shù)據(jù)恢復(fù)的實現(xiàn)
這篇文章主要介紹了MySQL使用binlog日志做數(shù)據(jù)恢復(fù)的實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03