MySQL聯(lián)表查詢的索引使用

一共3張表 knowledge , knowledge_question , knowledge_answer ，數(shù)據(jù)在 6000~10000 之間。

成都創(chuàng)新互聯(lián)公司長期為上千多家客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為秀洲企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、成都做網(wǎng)站，秀洲網(wǎng)站改版等技術(shù)服務(wù)。擁有十年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

執(zhí)行的語句：

執(zhí)行時間約 10分鐘 ，查看執(zhí)行計劃如下：

全部都是全表掃描，根據(jù)MySQL聯(lián)表查詢的算法 Nested-Loop Join ，MySQL查詢的結(jié)果集是3張表的笛卡爾積，所以效率特別低。

耗時變成 20毫秒

給Where條件建立索引，并不一定會使用。

比如：在表 knowledge 的字段 update 上建立索引 idx_time ：

結(jié)果執(zhí)行上來看，并沒有使用索引 idx_time 。

如果where條件從 k.update_time'2019-01-03 12:00:00' 修改為 k.update_time='2019-01-03 12:00:00' （從 變成 = ）

則會使用索引 idx_time

在建立索引的時候，會遇到 Table Metadata Lock 的問題，可以先 show processlist ，找到占用表鎖的連接，然后 kill 。

關(guān)于MySQL復(fù)合索引的使用方法

MySQL的復(fù)合索引可以創(chuàng)建多個，每個復(fù)合索引可以包含一列或多列。復(fù)合索引使用的基本原則是左側(cè)對齊原則。例如，復(fù)合索引包含A，B，C字段，實際相當(dāng)于創(chuàng)建了5個索引，即：

那么問題來了，如果我們創(chuàng)建兩個復(fù)合索引，復(fù)合索引1：包含A，B，C列和復(fù)合索引2：包含B，C列，MySQL如何執(zhí)行呢？

按照正常的邏輯，和復(fù)合索引的原則，應(yīng)該能命中的索引是A_B_C_index，讓我們拭目以待吧！

結(jié)果：和上次測試的不一致，這次雖然包含ABC三個列，但命中的索引是B_C_index

重要結(jié)論：當(dāng)命中兩個或者多個不同的復(fù)合索引時，按照創(chuàng)建順序不同，MySQL會有不同策略來選取其中的一個復(fù)合索引。

MySql是怎么使用的索引，在哪些情況下會使用

MySql為以下這些操作使用索引：

1、為了快速查找匹配WHERE條件的行。

2、為了從考慮的條件中消除行。如果在多個索引之間選擇一個，正常情況下，MySql使用找到行的最小數(shù)量的那個索引。

3、如果表有一個multiple-column索引，任何一個索引的最左前綴可以通過使用優(yōu)化器來查找行。例如，如果你有一個 three-column索引在(col1, col2, col3)，你能搜索索引在(col1), (col1, col2),和 (col1, col2, col3)。

mysql索引

在mysql中，索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中有某一特定值的記錄。

通過索引，查詢數(shù)據(jù)時不用讀完記錄的所有信息，而只是查詢索引列即可。

通過索引，查詢數(shù)據(jù)時不用讀完記錄的所有信息，而只是查詢索引列。否則，數(shù)據(jù)庫系統(tǒng)將讀取每條記錄的所有信息進行匹配。

可以把索引比作新華字典的音序表。例如，要查“庫”字，如果不使用音序，就需要從字典的 400 頁中逐頁來找。但是，如果提取拼音出來，構(gòu)成音序表，就只需要從 10 多頁的音序表中直接查找。這樣就可以大大節(jié)省時間。

因此，使用索引可以很大程度上提高數(shù)據(jù)庫的查詢速度，還有效的提高了數(shù)據(jù)庫系統(tǒng)的性能。

索引的優(yōu)缺點

索引有其明顯的優(yōu)勢，也有其不可避免的缺點。

優(yōu)點

索引的優(yōu)點如下：

1、通過創(chuàng)建唯一索引可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

2、可以給所有的 MySQL 列類型設(shè)置索引。

3、可以大大加快數(shù)據(jù)的查詢速度，這是使用索引最主要的原因。

4、在實現(xiàn)數(shù)據(jù)的參考完整性方面可以加速表與表之間的連接。

5、在使用分組和排序子句進行數(shù)據(jù)查詢時也可以顯著減少查詢中分組和排序的時間

缺點

增加索引也有許多不利的方面，主要如下：

1、創(chuàng)建和維護索引組要耗費時間，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加。

2、索引需要占磁盤空間，除了數(shù)據(jù)表占數(shù)據(jù)空間以外，每一個索引還要占一定的物理空間。如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達到最大文件尺寸。

3、當(dāng)對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)維護，這樣就降低了數(shù)據(jù)的維護速度。

使用索引時，需要綜合考慮索引的優(yōu)點和缺點。

mysql索引的創(chuàng)建和使用

我們可以通過查看索引的屬性來判斷創(chuàng)建索引的方法。

查看索引的語法格式如下：

SHOW INDEX FROM 表名 [ FROM 數(shù)據(jù)庫名]

語法說明如下：

表名：指定需要查看索引的數(shù)據(jù)表名。

數(shù)據(jù)庫名：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫，可省略。比如，SHOW INDEX FROM student FROM test; 語句表示查看 test 數(shù)據(jù)庫中 student 數(shù)據(jù)表的索引。

示例

使用 SHOW INDEX 語句查看《MySQL創(chuàng)建索引》一節(jié)中 tb_stu_info2 數(shù)據(jù)表的索引信息，SQL 語句和運行結(jié)果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要參數(shù)說明如下：

參數(shù) 說明

Table 表示創(chuàng)建索引的數(shù)據(jù)表名，這里是 tb_stu_info2 數(shù)據(jù)表。

Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。

Key_name 表示索引的名稱。

Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。

Column_name 表示定義索引的列字段。

Collation 表示列以何種順序存儲在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無分類。

Cardinality 索引中唯一值數(shù)目的估計值?；鶖?shù)根據(jù)被存儲為整數(shù)的統(tǒng)計數(shù)據(jù)計數(shù)，所以即使對于小型表，該值也沒有必要是精確的。基數(shù)越大，當(dāng)進行聯(lián)合時，MySQL 使用該索引的機會就越大。

Sub_part 表示列中被編入索引的字符的數(shù)量。若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目；若整列被編入索引，則該列的值為 NULL。

Packed 指示關(guān)鍵字如何被壓縮。若沒有被壓縮，值為 NULL。

Null 用于顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒有，則該列的值為 NO。

Index_type 顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 顯示評注。

數(shù)據(jù)庫基礎(chǔ)：講解MySQL索引的概念及數(shù)據(jù)庫索引的應(yīng)用[2]

五 索引分類

直接創(chuàng)建索引和間接創(chuàng)建索引

直接創(chuàng)建索引 CREATE INDEX mycolumn_index ON mytable (myclumn)

間接創(chuàng)建索引 定義主鍵約束或者唯一性鍵約束 可以間接創(chuàng)建索引

普通索引和唯一性索引

普通索引 CREATE INDEX mycolumn_index ON mytable (myclumn)

唯一性索引 保證在索引列中的全部數(shù)據(jù)是唯一的 對聚簇索引和非聚簇索引都可以使用

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

單個索引和復(fù)合索引

單個索引 即非復(fù)合索引

復(fù)合索引 又叫組合索引 在索引建立語句中同時包含多個字段名 最多 個字段

CREATE INDEX name_index ON username(firstname lastname)

聚簇索引和非聚簇索引(聚集索引 群集索引)

聚簇索引 物理索引 與基表的物理順序相同 數(shù)據(jù)值的順序總是按照順序排列

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH

ALLOW_DUP_ROW(允許有重復(fù)記錄的聚簇索引)

非聚簇索引 CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

六 索引的使用

當(dāng)字段數(shù)據(jù)更新頻率較低 查詢使用頻率較高并且存在大量重復(fù)值是建議使用聚簇索引

經(jīng)常同時存取多列 且每列都含有重復(fù)值可考慮建立組合索引

復(fù)合索引的前導(dǎo)列一定好控制好 否則無法起到索引的效果 如果查詢時前導(dǎo)列不在查詢條件中則該復(fù)合索引不會被使用 前導(dǎo)列一定是使用最頻繁的列

多表操作在被實際執(zhí)行前 查詢優(yōu)化器會根據(jù)連接條件 列出幾組可能的連接方案并從中找出系統(tǒng)開銷最小的最佳方案 連接條件要充份考慮帶有索引的表 行數(shù)多的表;內(nèi)外表的選擇可由公式 外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定 乘積最小為最佳方案

where子句中對列的任何操作結(jié)果都是在sql運行時逐列計算得到的 因此它不得不進行表搜索 而沒有使用該列上面的索引;如果這些結(jié)果在查詢編譯時就能得到 那么就可以被sql優(yōu)化器優(yōu)化 使用索引 避免表搜索(例 select * from record where substring(card_no )=

select * from record where card_no like % )任何對列的操作都將導(dǎo)致表掃描 它包括數(shù)據(jù)庫函數(shù) 計算表達式等等 查詢時要盡可能將操作移至等號右邊

where條件中的 in 在邏輯上相當(dāng)于 or 所以語法分析器會將in ( ′ ′)轉(zhuǎn)化為column= ′ or column= ′來執(zhí)行 我們期望它會根據(jù)每個or子句分別查找 再將結(jié)果相加 這樣可以利用column上的索引;但實際上它卻采用了 or策略 即先取出滿足每個or子句的行 存入臨時數(shù)據(jù)庫的工作表中 再建立唯一索引以去掉重復(fù)行 最后從這個臨時表中計算結(jié)果 因此 實際過程沒有利用column上索引 并且完成時間還要受tempdb數(shù)據(jù)庫性能的影響 in or子句常會使用工作表 使索引失效;如果不產(chǎn)生大量重復(fù)值 可以考慮把子句拆開;拆開的子句中應(yīng)該包含索引

要善于使用存儲過程 它使sql變得更加靈活和高效

lishixinzhi/Article/program/MySQL/201311/29603

當(dāng)前名稱：mysql中怎么使用索引 mysql如何使用索引
標(biāo)題鏈接：http://jinyejixie.com/article40/dosedeo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、網(wǎng)站制作、網(wǎng)站改版、軟件開發(fā)、網(wǎng)頁設(shè)計公司、App設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)