MySQL從刪庫到跑路_高級（六）——索引

来源:https://www.cnblogs.com/cxydczzl/archive/2018/11/08/9928554.html
-Advertisement-
Play Games

作者：天山老妖S 鏈接：http://blog.51cto.com/9291927 一、索引簡介 1、索引簡介 索引（Index）是幫助MySQL高效獲取數據的數據結構。 在MySQL中，索引屬於存儲引擎級別的概念，不同存儲引擎對索引的實現方式是不同的。MyISAM和InnoDB存儲引擎只支持BTR ...


作者：天山老妖S

鏈接：http://blog.51cto.com/9291927

 

一、索引簡介

1、索引簡介

索引（Index）是幫助MySQL高效獲取數據的數據結構。

在MySQL中，索引屬於存儲引擎級別的概念，不同存儲引擎對索引的實現方式是不同的。MyISAM和InnoDB存儲引擎只支持BTREE索引，MEMORY/HEAP存儲引擎支持HASH和BTREE索引。

2、索引的優點

A、提高數據檢索效率，降低資料庫的IO成本。

B、通過索引對數據進行排序，降低數據排序的成本降低了CPU的消耗。

C、大大加快數據的查詢數據。

3、索引的缺點

A、創建索引和維護索引要耗費時間，並且隨著數據量的增加所耗費的時間也會增加

B、索引也需要占空間，我們知道數據表中的數據也會有最大上線設置的，如果我們有大量的索引，索引文件可能會比數據文件更快達到上線值

C、當對錶中的數據進行增加、刪除、修改時，索引也需要動態的維護，降低了數據的維護速度。

4、索引的使用原則

A、主鍵自動建立唯一索引

B、頻繁作為查詢條件的欄位應該創建索引

C、查詢中與其他表關聯的欄位，外鍵關係建立索引

D、頻繁更新的欄位不適合建立索引，因為每次更新不單單是更新了記錄還會更新索引

E、WHERE條件里用不到的欄位不創建索引

F、單鍵/組合索引的選擇問題，who？（在高併發下傾向創建組合索引）

G、查詢中排序的欄位，排序的欄位若通過索引去訪問將大大提高排序速度

H、查詢中統計或者分組欄位

不適合使用索引的場合：

A、對經常更新的表就避免對其進行過多的索引，對經常用於查詢的欄位應該創建索引。

B、數據量小的表最好不要使用索引，由於數據較少，可能查詢全部數據花費的時間比遍歷索引的時間還要短，索引就可能不會產生優化效果。

C、在不同值少的列上不要建立索引，比如在學生表的"性別"欄位上只有男，女兩個不同值。在一個不同值較多的列可以建立索引。

二、索引的分類

1、單列索引

單列索引只包含單個列，但一個表中可以有多個單列索引。

A、普通索引

普通索引允許在定義索引的列中插入重覆值和空值。

B、唯一索引

索引列中的值必須是唯一的，但是允許為空值。

C、主鍵索引

主鍵索引是一種特殊的唯一索引，不允許有空值。

2、複合索引

在表中的多個欄位組合上創建的索引，只有在查詢條件中使用了組合的多個欄位的左邊欄位時，索引才會被使用，使用複合索引時遵循最左首碼集合。

3、全文索引

全文索引，只有MyISAM存儲引擎支持，只能在CHAR、VARCHAR、TEXT類型欄位上使用全文索引。

全文索引主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。在數據量較大時候，先將數據放入一個沒有全文索引的表中，然後再用CREATE index創建fulltext索引，要比先為一張表建立fulltext然後再將數據寫入的速度快很多。

4、空間索引

空間索引是對空間數據類型的欄位建立的索引，MySQL中的空間數據類型有四種：GEOMETRY、POINT、LINESTRING、POLYGON。

在創建空間索引時，使用SPATIAL關鍵字。

空間索引必須使用MyISAM存儲引擎，並且空間索引的欄位必須為非空。

三、索引的操作

1、索引的創建

創建表時創建索引的語法：

CREATE TABLE table_name[col_name data type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length]) [asc|desc]

在表上創建索引的語法：

ALTER TABLE tablename ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [indexname] (col_name) [ASC|DESC]; CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] indexname ON tablename(col_name[length]) [ASC|DESC];

uniquer|fulltext為何選參數，分別表示唯一索引、全文索引

col_name為需要創建索引的欄位列，該列必須從數據表中該定義的多個列中選擇

index_name指定索引的名稱，為可選參數，如果不指定，預設col_name為索引值

length為可選參數，表示索引的長度，只有字元串類型的欄位才能指定索引長度

asc或desc指定升序或降序的索引值存儲

在創建索引時如果不指定索引名，預設使用欄位名索引名。

2、普通索引的創建

直接創建索引

CREATE INDEX index_name ON tablename(column(length))

修改表結構

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

創建表時指定索引

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,INDEX index_name(col_name)
);

3、唯一索引的創建

索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

直接創建唯一索引

CREATE UNIQUE INDEX indexName ON tablename(column(length))

修改表結構

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

創建表時直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,UNIQUE INDEX index_name(col_name)
);

4、主鍵索引的創建

修改表結構

ALTER TABLE table_name ADD PRIMARY KEY(col_name)

創建表時直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
PRIMARY KEY(col_name)
);

5、複合索引的創建

直接創建複合索引

CREATE INDEX indexName ON tablename(col_name1,col_name2)

創建表時直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,INDEX index_name(col_name1,col_name2)
);

6、全文索引的創建

直接創建全文索引

CREATE FULLTEXT INDEX indexName ON tablename(col_name)

修改表結構

ALTER TABLE table_name ADD FULLTEXT INDEX indexName(col_name)

創建表時直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
FULLTEXT INDEX index_name(col_name)
);

在使用全文索引時，需要藉助MATCH AGAINST操作，而不是一般的WHERE語句加LIKE。全文索引的限制比較多，比如只能使用MyISAM存儲引擎，比如只能在CHAR、VARCHAR、TEXT上設置全文索引。比如索引的關鍵字預設至少要4個字元，比如搜索的關鍵字太短就會被忽略掉。

SELECT * FROM tablename WHERE MATCH(col_name) AGAINST('pattern');

col_name為全文索引列，'pattern'為匹配的字元串

7、索引的刪除

DROP INDEX [indexName] ON tablename;

ALTER TABLE tablename DROP INDEX indexname;

8、索引信息的查看

SHOW INDEX FROM table_name;

9、索引的註意事項

A、索引不會包含有null值的列

在資料庫設計時不要讓欄位的預設值為null。

B、使用短索引

C、索引列排序

因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列創建複合索引。

D、like語句操作

一般情況下不推薦使用like操作，如果非使用不可。like"%aaa%不會使用索引而like"aaa%"可以使用索引。

E、不要在列上進行運算

在索引列上進行運算將導致索引失效而進行全表掃描例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

F、不使用not in和<>操作

四、索引查詢速度比較

1、插入記錄

create procedure addStudent(in num int)
begin
declare i int;
set i=1;delete from TStudent;while num>=i doinsert TStudent values (
       LPAD(convert(i,char(10)),10,'0'),
       CreateName(),       if(ceil(rand()*10)%2=0,'男','女'),
       RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,'0'),
       Concat(convert(ceil(rand()*10)+1980,char(4)),'-',LPAD(convert(ceil(rand()*12),char(2)),2,'0'),'-',LPAD(convert(ceil(rand()*28),char(2)),2,'0')),
       Concat(PINYIN(sname),'@hotmail.com'),
       case ceil(rand()*3) when 1 then '網路與網站開發' when 2 then 'JAVA' ELSE 'NET' END,
       NOW()
);
set i=i+1;
end while;select * from TStudent;
end

修改addStuent存儲過程，插入500000條記錄

call addStudent(500000);

SQL語句查詢xxx號cardID以12345開頭的學生。

select * from TStudent where cardID like '12345%'

花費時間為1.27秒

2、給XXX列添加索引

alter table TStudnet add index cardidIndex(cardID);

SQL查詢xxx號cardID以12345開頭的學生。

select * from TStudent where cardID like'12345%'

花費時間31毫秒。

3、查看索引占用的磁碟空間

schoolDB資料庫索引占用的磁碟空間。

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') 
AS 'Total Index Size' FROM information_schema.TABLESWHERE table_schema LIKE 'schoolDB';

查看schoolDB資料庫數據占用的磁碟空間。

 

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') 
AS 'Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'schoolDB';

 

4、查看SQL語句執行計劃

EXPLAIN可以查看SQL查詢語句的查詢計劃，使用索引還是全表掃描，key顯示使用的索引。

explain select * from TStudent where cardid like '12345%' \G;

id：SELECT識別符，即SELECT的查詢序列號，一條語句中，select是第幾次出現。

select_type：所使用的SELECT查詢類型，SIMPLE表示為簡單的SELECT,不實用UNION或子查詢。其他取值，PRIMARY：最外面的SELECT在擁有子查詢時，就會出現兩個以上的SELECT。UNION：union(兩張表連接)中的第二個或後面的select語句 SUBQUERY：在子查詢中，第二SELECT。

table:數據表的名字。按被讀取的先後順序倒序排列。

type：指定本數據表和其他數據表之間的關聯關係，表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄做聯合。ref用於連接程式使用鍵的最左首碼或者鍵不是primary key或unique索引的情況。取值有system、const、eq_ref、index和AII。

possible_keys：MySQL在搜索數據記錄時可以選用的各個索引

key：實際選用的索引

key_len：顯示MySQL使用索引的長度（使用的索引個數），當key欄位的值為null時，索引的長度就是null。

ref：給出關聯關係中另一個數據表中數據列的名字。

rows：MySQL在執行查詢時預計會從數據表裡讀出的數據行的個數。

extra：提供與關聯操作有關的信息。

五、覆蓋索引

1、覆蓋索引

一個包含查詢所需的欄位的索引稱為覆蓋索引（covering index)。MySQL只需要通過索引就可以返回查詢所需要的數據，而不必在查到索引之後進行回表操作，減少IO，提供效率。

通過EXPLAIN查看SQL語句的執行計劃時，說明SQL查詢使用覆蓋索引。

2、使用覆蓋索引的SQL語句

Tstudent表cardID列創建了索引，SQL語句查找的列是cardID，就會使用cardID索引進行查找，不需要掃描表的頁。

explain select sname from TStudent where sname like '劉%';

執行結果Extra出現using index，說明是使用覆蓋索引查找。

3、使用覆蓋索引實現order by排序

在MySQL中的ORDER BY有兩種排序實現方式：

A、利用有序索引獲取有序數據

B、文件排序

使用EXPLAIN分析SQL查詢時，利用有序索引獲取有序數據顯示Using index。而文件排序顯示Using filesort。

explain select email from TStudent order by email;

email列沒有索引，SQL語句的查詢計劃可以看到Extra是using filesort，說明是將結果在記憶體中排序，需要額外時間開銷。

給Email列添加索引後，

alter table TStudent add index emailIndex(email);explain select email from TStudent order by email;

再次執行，可以看到Extra列是Using index，說明使用索引排序，沒有額外時間開銷。

 

 

喜歡的小伙伴們可以搜索我們個人的微信公眾號“程式員的成長之路”點擊關註或掃描下方二維碼


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • redis學習（四）redis事務
    redis事務 1.redis事務介紹 redis的事務可以理解為一系列串列命令的集合。redis的事務和單條命令一樣，都是redis的最小執行單位，因此一個事務內的命令，要麼全部執行，要麼全部不執行。事務的概念對於熟悉資料庫的人們並不陌生，而redis作為一個資料庫系統，必然會對事務進行一定的支持 ...
  • MySQL分頁查詢性能優化
    當需要從資料庫查詢的表有上萬條記錄的時候，一次性查詢所有結果會變得很慢，特別是隨著數據量的增加特別明顯，這時需要使用分頁查詢。對於資料庫分頁查詢，也有很多種方法和優化的點。下麵簡單說一下我知道的一些方法。 準備工作 為了對下麵列舉的一些優化進行測試，下麵針對已有的一張表進行說明。 表名：order_ ...
  • 表的轉置 行轉列： DECODE(Oracle) 和 CASE WHEN 的異同點
    一. DECODE方法 (Oracle公司獨家) decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,預設值) 二. CASE WHEN Case函數只返回第一個符合條件的值，剩下的Case部分將會被自動忽略 Case具有兩種格式,簡單Case函數的寫法相對比較簡潔，但是和Ca ...
  • sql server 臨時表(中) Tempdb監控
    一. 監控概述 Tempdb庫空間使用的一大特點，是只有一部分對象，例如用戶創建的臨時表、table變數等，可以用sys.allocation_units和sys.partitions這樣的管理視圖來管理，許多內部對象和版本存儲在這些管理視圖中沒有體現，所以sp_spaceused的結果和真實的使用 ...
  • [20181108]12c sqlplus rowfetch參數4.txt
    [20181108]12c sqlplus rowfetch參數4.txt--//12cR2 可以改變預設rowfetch參數.11g之前預設是1.通過一些測試說明問題.--//前幾天做的測試有點亂,鏈接http://blog.itpub.net/267265/viewspace-2219004/. ...
  • Redis 哨兵節點之間相互自動發現機制（自動重寫哨兵節點的配置文件）
    Redis的哨兵機制中，如果是多哨兵模式，哨兵節點之間也是可以相互感知的，各種搜索之後出來的是千篇一律的一個基礎配置文件，在配置當前哨兵節點的配置文件中，並沒有配置其他哨兵節點的任何信息。如下是一個哨兵節點的配置信息，可以看到，哨兵與哨兵之間沒有任何配置，死活想不明白，哨兵之間是如何自動識別的。 參 ...
  • 一圖看懂hadoop MapReduce工作原理
    MapReduce執行流程及單詞統計WordCount示例 ...
  • hadoop2-HBase的Java API操作
    Hbase提供了豐富的Java API，以及線程池操作，下麵我用線程池來展示一下使用Java API操作Hbase。 項目結構如下： ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類