五 索引分類
成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比盤龍網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式盤龍網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋盤龍地區(qū)。費(fèi)用合理售后完善，十多年實(shí)體公司更值得信賴。
直接創(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
一共3張表 knowledge , knowledge_question , knowledge_answer ，數(shù)據(jù)在 6000~10000 之間。
執(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 。
MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲(chǔ)數(shù)據(jù)對(duì)性能有很大的影響。通常來說，可以遵循以下一些指導(dǎo)原則：
(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。
(2)簡(jiǎn)單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因?yàn)樽址谋容^更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類型，而不是用字符串來存儲(chǔ)時(shí)間；以及用整型數(shù)據(jù)類型存儲(chǔ)IP地址。
(3)盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲(chǔ)NULL。在MySQL中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪沟盟饕?、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值。
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ù)合索引。
CREATE
[UNIQUE]
INDEX
ON
(字段
[ASC|DESC]);
UNIQUE
--確保所有的索引列中的值都是可以區(qū)分的。
[ASC|DESC]
--在列上按指定排序創(chuàng)建索引。
(創(chuàng)建索引的準(zhǔn)則：
1.如果表里有幾百行記錄則可以對(duì)其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。
2.不要試圖對(duì)表創(chuàng)建兩個(gè)或三個(gè)以上的索引。
3.為頻繁使用的行創(chuàng)建索引。
)
示例
create
index
i_1
on
emp(empno
asc);
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)。
重慶分公司
重慶分公司
