MySQL新增索引特點及優化問題

2022-07-22 22:03:15

一、索引的特點

當MySQL單表記錄數過大時，增刪改查效能都會急劇下降。MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。除非單表資料未來會一直不斷上漲，否則不要一開始就考慮拆分，拆分會帶來邏輯、部署、運維的各種複雜度。一般以整型值為主的表在千萬級以下，字串為主的表在五百萬以下是沒有太大問題的，而事實上很多時候MySQL單表的效能依然有不少優化空間，甚至能正常支撐千萬級以上的資料量。

索引優勢和劣勢：

優勢： 大大減少了伺服器需要掃描的資料量，可以幫助伺服器避免排序和臨時表，實現快速檢索，將隨機I/O變成順序I/O，減少I/O次數，加快檢索速度；根據索引分組和排序，可以加快分組和排序；
劣勢： 索引本身也是表，因此會佔用儲存空間，一般來說，索引表佔用的空間的資料表的1.5倍；索引表的維護和建立需要時間成本，這個成本隨著資料量增大而增大；構建索引會降低資料表的修改操作（刪除，新增，修改）的效率，因為在修改資料表的同時還需要修改索引表；建立索引時需要對錶加鎖，因此實際操作中需要在業務空閒期間進行。

二、索引型別

Mysql目前主要有以下幾種索引型別：FULLTEXT，HASH，BTREE，RTREE。

1.FULLTEXT

即為全文索引，目前只有MyISAM引擎支援。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。

全文索引並不是和MyISAM一起誕生的，它的出現是為了解決WHERE name LIKE “%word%"這類針對文字的模糊查詢效率較低的問題。

FULLTEXT（全文）索引，僅可用於MyISAM和InnoDB

對於較大的資料集，把資料新增到一個沒有FULLTEXT索引的表，然後新增FULLTEXT索引的速度比把資料新增到一個已經有FULLTEXT索引的錶快。
5.6版本前的MySQL自帶的全文索引只能用於MyISAM儲存引擎，如果是其它資料引擎，那麼全文索引不會生效。5.6版本之後InnoDB儲存引擎開始支援全文索引。
在MySQL中，全文索引支隊英文有用，目前對中文還不支援。5.7版本之後通過使用ngram外掛開始支援中文。
在MySQL中，如果檢索的字串太短則無法檢索得到預期的結果，檢索的字串長度至少為4位元組，此外，如果檢索的字元包括停止詞，那麼停止詞會被忽略。

2.HASH

雜湊索參照索引列的值計算該值的hashCode，然後在hashCode相應的位置存執該值所在行資料的物理位置，因為使用雜湊演演算法，因此存取速度非常快，但是一個值只能對應一個hashCode，而且是雜湊的分佈方式。由於HASH的唯一（幾乎100%的唯一）及類似鍵值對的形式，很適合作為索引。

HASH索引可以一次定位，不需要像樹形索引那樣逐層查詢,因此具有極高的效率。但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對於範圍查詢、排序及組合索引仍然效率不高。

3.BTREE

BTREE(B+TREE)索引就是一種將索引值按一定的演演算法，存入一個樹形的資料結構中（二元樹），每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。由於BTREE非葉子節點不儲存資料（data），因此所有的資料都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有資料的查詢速度都是一樣的。這是MySQL裡預設和最常用的索引型別。

4.RTREE

RTREE在MySQL很少使用，僅支援geometry資料型別，支援該型別的儲存引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對於BTREE，RTREE的優勢在於範圍查詢。

三、索引種類

普通索引：僅加速查詢。
唯一索引：加速查詢 + 列值唯一（可以有null）。
主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個。
組合索引：多列值組成一個索引，專門用於組合搜尋，其效率大於索引合併，遵循“最左字首”原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當於建立了col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的。
全文索引：對文字的內容進行分詞，進行搜尋。

四、索引的使用策略

1.什麼時候要使用索引？

主鍵自動建立唯一索引；經常作為查詢條件在WHERE或者ORDER BY；語句中出現的列要建立索引；作為排序的列要建立索引；查詢中與其他表關聯的欄位，外來鍵關係建立索引高並行條件下傾向組合索引；用於聚合函數的列可以建立索引，例如使用了max(column_1)或者count(column_1)時的column_1就需要建立索引。

2.什麼時候不要使用索引？

經常增刪改的列不要建立索引；有大量重複的列不建立索引；表記錄太少不要建立索引。只有當資料庫裡已經有了足夠多的測試資料時，它的效能測試結果才有實際參考價值。如果在測試資料庫裡只有幾百條資料記錄，它們往往在執行完第一條查詢命令之後就被全部載入到記憶體裡，這將使後續的查詢命令都執行得非常快–不管有沒有使用索引。只有當資料庫裡的記錄超過了1000條、資料總量也超過了MySQL伺服器上的記憶體總量時，資料庫的效能測試結果才有意義。

3.索引失效的情況?

在組合索引中不能有列的值為NULL，如果有，那麼這一列對組合索引就是無效的；在一個SELECT語句中，索引只能使用一次，如果在WHERE中使用了，那麼在ORDER BY中就不要用了；LIKE操作中，’%aaa%'不會使用索引，也就是索引會失效，但是’aaa%'可以使用索引；在索引的列上使用表示式或者函數會使索引失效，例如：

select * from table where ceate_time > unix_timestamp(curdate());

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成當前時間由程式作為引數傳入：

select * from table where ceate_time > 1524561911;

其它萬用字元同樣，也就是說，在查詢條件中使用正規表示式時，只有在搜尋模板的第一個字元不是萬用字元的情況下才能使用索引；
在查詢條件中使用不等於，包括<符號、>符號和！=會導致索引失效。特別的是：如果對主鍵索引使用！=則不會使索引失效，如果對主鍵索引或者整數型別的索引使用<符號或者>符號也不會使索引失效。（不等於，包括<符號、>符號和！，如果佔總記錄的比例很小的話，也不會失效）；
在查詢條件中使用IS NULL或者IS NOT NULL會導致索引失效；
字串不加單引號會導致索引失效。更準確的說是型別不一致會導致失效，比如欄位mobile是字串型別的，使用WHERE mobile=99999 則會導致失敗，應該改為WHERE mobile=‘99999’；
在查詢條件中使用OR連線多個條件會導致索引失效，除非OR連結的每個條件都加上索引，這時應該改為兩次查詢，然後用UNION ALL連線起來；
如果排序的欄位使用了索引，那麼select的欄位也要是索引欄位，否則索引失效。特別的是：如果排序的是主鍵索引則select * 也不會導致索引失效；
儘量不要包括多列排序，如果一定要，最好為這佇列構建組合索引。

4.mysql查詢優化?

欄位:

儘量使用TINYINT、SMALLINT、MEDIUM_INT作為整數型別而非INT，如果非負則加上UNSIGNED；
VARCHAR的長度只分配真正需要的空間；
使用列舉或整數代替字串型別；
儘量使用TIMESTAMP而非DATETIME；
單表不要有太多欄位，建議在20以內；
避免使用NULL欄位，很難查詢優化且佔用額外索引空間；
用整型來存IP。

索引：

索引並不是越多越好，要根據查詢有針對性的建立，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據EXPLAIN來檢視是否用了索引還是全表掃描；
應儘量避免在WHERE子句中對欄位進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描；
值分佈很稀少的欄位不適合建索引，例如“性別”這種只有兩三個值的欄位；
字元欄位只建字首索引；
字元欄位最好不要做主鍵；
不用外來鍵，由程式保證約束；儘量不用UNIQUE，由程式保證約束；
使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引。

查詢sql：

可通過開啟慢查詢紀錄檔來找出較慢的SQL；
不做列運算：SELECT id WHERE age + 1 = 10，任何對列的操作都將導致表掃描，它包括資料庫教學函數、計算表示式等等，查詢時要儘可能將操作移至等號右邊；
sql語句儘可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫；
不用SELECT *；
OR改寫成IN：OR的效率是n級別，IN的效率是log(n)級別，IN的個數建議控制在200以內；
不用函數和觸發器，在應用程式實現；
避免%xxx式查詢，’%xxx%'不會使用索引,可以使用全文索引，然後：
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
少用JOIN；
使用同型別進行比較，比如用’123’和’123’比，123和123比；
組合索引要遵循最做字首原則，排序分組頻率最高的列放在最左邊，以此類推；
儘量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描；
對於連續數值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；
列表資料不要拿全表，要使用LIMIT來分頁，每頁數量也不要太大；
使用短索引，如果可能應該指定一個字首長度。例如，如果有一個CHAR(255)的列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

5.索引的常見問題

1.索引是幹什麼的？

索參照於快速找出在某個列中有一特定值的行。不使用索引，mysql必須從第一條記錄開始讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，mysql能快速到達一個位置搜尋到資料檔案的中間，沒有必要檢視所有資料。

大多數mysql的索引(primary key、index、unique、fulltext)在B樹中儲存，只是空間列型別的索引使用R樹，並且memory表還支援hash索引。

2.索引好複雜，我該怎麼理解索引，有沒有一個更形象的例子？

索引就像是一本書的目錄。

3.索引越多越好?

大多數情況下，索引能大幅提高查詢效率。但是:資料變更(增刪改)都需要維護索引，因此更多索引意味著更多維護成本；也意味著需要更多控制元件空間(一本書100頁，卻有50頁目錄？)；過小的表，建索引可能會更慢。

4.索引的欄位型別問題

text型別，也可建索引(需要指定長度)；MyISAM儲存引擎長度綜合不能超過1000位元組；用來篩選的值儘量保持和索引列同樣的資料型別。

5.like能用到索引？

儘量減少like查詢，但是也不是絕對不可用，'xxx%'是可以用到索引的。除了like，以下操作符也可以用到索引：

<，<=，=，>，>=，between，in

這些用不到索引：

<>，not in，!=

6.什麼樣的欄位不適合建索引？

列的值唯一性太小(比如性別，型別)，不適合建索引。(什麼叫大小？一般來說，同值的資料超過表的15%，那就沒有必要建索引了)更新非常頻繁的資料不適合建索引。

7.一次查詢能用多個索引？

不能

8.多列查詢該如何建索引？

一次查詢只能用到一個索引， a列建索引還是b列建索引？誰的區分度（同值的少）更高，建誰！當然，聯合索引也是個不錯的方案。

9.聯合索引的問題

-- 命中col1、col2聯合索引
select col1,col2 from test where col1 = 'xxx';
-- 不能命中col1、col2聯合索引
select col1,col2 from test where col2 = 'xxx';

所以大多數情況下，有col1、col2索引了，就不用再去建col1索引了

10.哪些常見的情況不能用到索引？

like '%xxx'
not in
!=

對列進行函數運算，如：

where md5(password) = "xxx"

存了數值的字串型別欄位(如手機號)，查詢是記得不要丟掉值的引號，否則無法命中索引：

select * from test where mobile = 13800002222;

如果mobile欄位是char或者varchar型別，則上面查詢無法命中索引,應為：

select * from test where mobile = '13800002222';

11.NULL的問題

Null會導致索引形同虛設，所以在設計表結構應避免NULL的存在。
可用其他方式來表達，比如-1。

到此這篇關於MySQL新增索引特點及優化問題的文章就介紹到這了,更多相關MySQL索引優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！