mysql中怎么使用索引 mysql如何使用索引

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

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

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

執(zhí)行的語句：

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

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

耗時(shí)變成 20毫秒

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

比如：在表 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' （從 變成 = ）

則會(huì)使用索引 idx_time

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

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

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

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

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

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

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

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

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

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

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

3、如果表有一個(gè)multiple-column索引，任何一個(gè)索引的最左前綴可以通過使用優(yōu)化器來查找行。例如，如果你有一個(gè) 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í)不用讀完記錄的所有信息，而只是查詢索引列即可。

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

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

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

索引的優(yōu)缺點(diǎn)

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

優(yōu)點(diǎn)

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

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

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

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

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

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

缺點(diǎn)

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

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

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

3、當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

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

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 語句和運(yùn)行結(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 表示列以何種順序存儲(chǔ)在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無分類。

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

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

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

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

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

Comment 顯示評(píng)注。

數(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ù)是唯一的 對(duì)聚簇索引和非聚簇索引都可以使用

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

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

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

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

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)常同時(shí)存取多列 且每列都含有重復(fù)值可考慮建立組合索引

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

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

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

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

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

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

lishixinzhi/Article/program/MySQL/201311/29603

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

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

廣告

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