本篇文章給大家分享的是有關(guān)MySQL中怎么優(yōu)化索引類型，小編覺得挺實用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),銅川企業(yè)網(wǎng)站建設(shè),銅川品牌網(wǎng)站建設(shè),網(wǎng)站定制,銅川網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,銅川網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

1 索引的類型

UNIQUE唯一索引

不可以出現(xiàn)相同的值，可以有NULL值。

INDEX普通索引

允許出現(xiàn)相同的索引內(nèi)容。

PRIMARY KEY主鍵索引

不允許出現(xiàn)相同的值，且不能為NULL值，一個表只能有一個primary_key索引。

fulltext index 全文索引

上述三種索引都是針對列的值發(fā)揮作用，但全文索引，可以針對值中的某個單詞，比如一篇文章中的某個詞，然而并沒有什么卵用，因為只有myisam以及英文支持，并且效率讓人不敢恭維，但是可以用coreseek和xunsearch等第三方應(yīng)用來完成這個需求。

2 索引的CURD

索引的創(chuàng)建

ALTER TABLE

適用于表創(chuàng)建完畢之后再添加。

ALTER TABLE 表名 ADD 索引類型 (unique,primary key,fulltext,index)[索引名](字段名)

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，當(dāng)前的索引名就是該字段名。 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

CREATE INDEX

CREATE INDEX可對表增加普通索引或UNIQUE索引。

--例：只能添加這兩種索引 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)

另外，還可以在建表時添加：

CREATE TABLE `test1` ( 
  `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面創(chuàng)建了主鍵索引，這里就不用創(chuàng)建了 
  `username` varchar(64) NOT NULL COMMENT '用戶名', 
  `nickname` varchar(50) NOT NULL COMMENT '昵稱/姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名稱，可要可不要，不要就是和列名一樣 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后臺用戶表';

索引的刪除

DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 這兩句都是等價的,都是刪除掉table_name中的索引index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 刪除主鍵索引，注意主鍵索引只能用這種方式刪除

索引的查看

show index from tablename;

索引的更改

更改個毛線,刪掉重建一個既可

3 創(chuàng)建索引的技巧

• 維度高的列創(chuàng)建索引。

• 數(shù)據(jù)列中不重復(fù)值出現(xiàn)的個數(shù)，這個數(shù)量越高，維度就越高。

• 如數(shù)據(jù)表中存在8行數(shù)據(jù)a,b ,c,d,a,b,c,d這個表的維度為4。

• 要為維度高的列創(chuàng)建索引，如性別和年齡，那年齡的維度就高于性別。

性別這樣的列不適合創(chuàng)建索引，因為維度過低。

• 對 where,on,group by,order by 中出現(xiàn)的列使用索引。

• 對較小的數(shù)據(jù)列使用索引，這樣會使索引文件更小，同時內(nèi)存中也可以裝載更多的索引鍵。

• 為較長的字符串使用前綴索引。

• 不要過多創(chuàng)建索引，除了增加額外的磁盤空間外，對于DML操作的速度影響很大，因為其每增刪改一次就得從新建立索引。

• 使用組合索引，可以減少文件索引大小，在使用時速度要優(yōu)于多個單列索引。

4 組合索引與前綴索引

注意，這兩種稱呼是對建立索引技巧的一種稱呼，并非索引的類型。

組合索引

MySQL單列索引和組合索引究竟有何區(qū)別呢？

為了形象地對比兩者，先建一個表：

CREATE TABLE `myIndex` ( 
  `i_testID` INT NOT NULL AUTO_INCREMENT,  
  `vc_Name` VARCHAR(50) NOT NULL,  
  `vc_City` VARCHAR(50) NOT NULL,  
  `i_Age` INT NOT NULL,  
  `i_SchoolID` INT NOT NULL,  
  PRIMARY KEY (`i_testID`)  
);

假設(shè)表內(nèi)已有1000條數(shù)據(jù)，在這 10000 條記錄里面 7 上 8 下地分布了 5 條 vc_Name=”erquan” 的記錄，只不過 city,age,school 的組合各不相同。來看這條 T-SQL：

SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='鄭州' AND `i_Age`=25; -- 關(guān)聯(lián)搜索;

首先考慮建MySQL單列索引：

在 vc_Name 列上建立了索引。執(zhí)行 T-SQL 時，MYSQL 很快將目標(biāo)鎖定在了 vc_Name=erquan 的 5 條記錄上，取出來放到一中間結(jié)果集。在這個結(jié)果集里，先排除掉 vc_City 不等于”鄭州”的記錄，再排除 i_Age 不等于 25 的記錄，最后篩選出唯一的符合條件的記錄。雖然在 vc_Name 上建立了索引，查詢時MYSQL不用掃描整張表，效率有所提高，但離我們的要求還有一定的距離。同樣的,在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似。

為了進(jìn)一步榨取 MySQL 的效率，就要考慮建立組合索引。就是將 vc_Name,vc_City,i_Age 建到一個索引里：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建表時，vc_Name 長度為 50，這里為什么用 10 呢？這就是下文要說到的前綴索引，因為一般情況下名字的長度不會超過 10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高 INSERT 的更新速度。

執(zhí)行 T-SQL 時，MySQL 無須掃描任何記錄就到找到唯一的記錄！

如果分別在 vc_Name,vc_City,i_Age 上建立單列索引，讓該表有 3 個單列索引，查詢時和上述的組合索引效率一樣嗎？答案是大不一樣，遠(yuǎn)遠(yuǎn)低于我們的組合索引。雖然此時有了三個索引，但 MySQL 只能用到其中的那個它認(rèn)為似乎是最有效率的單列索引，另外兩個是用不到的，也就是說還是一個全表掃描的過程。

建立這樣的組合索引，其實是相當(dāng)于分別建立了：

• vc_Name,vc_City,i_Age

• vc_Name,vc_City

• vc_Name

這樣的三個組合索引！為什么沒有 vc_City,i_Age 等這樣的組合索引呢？這是因為 mysql 組合索引 “最左前綴” 的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引，下面的幾個 T-SQL 會用到：

SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”鄭州” SELECT * FROM myIndex WHREE vc_Name=”erquan”

而下面幾個則不會用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”鄭州” SELECT * FROM myIndex WHREE vc_City=”鄭州”

也就是，name_city_age(vc_Name(10),vc_City,i_Age) 從左到右進(jìn)行索引，如果沒有左前索引Mysql不執(zhí)行索引查詢。

前綴索引

如果索引列長度過長，這種列索引時將會產(chǎn)生很大的索引文件，不便于操作，可以使用前綴索引方式進(jìn)行索引前綴索引應(yīng)該控制在一個合適的點，控制在0.31黃金值即可（大于這個值就可以創(chuàng)建）。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 這個值大于0.31就可以創(chuàng)建前綴索引，Distinct去重復(fù) ALTER TABLE `user` ADD INDEX `uname`(title(10)); — 增加前綴索引SQL，將人名的索引建立在10，這樣可以減少索引文件大小，加快索引查詢速度。

5 什么樣的sql不走索引

要盡量避免這些不走索引的sql

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引，因為所有索引列參與了計算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引，因為使用了函數(shù)運算，原理與上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

-- 正則表達(dá)式不使用索引，這應(yīng)該很好理解，所以為什么在SQL中很難看到regexp關(guān)鍵字的原因 

-- 字符串與數(shù)字比較不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有字段，都必須建立索引，我們建議大家盡量避免使用or 關(guān)鍵字 

-- 如果mysql估計使用全表掃描要比使用索引快，則不使用索引

多表關(guān)聯(lián)時的索引效率

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不會使用索引，因為使用了函數(shù)運算，原理與上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE’后盾%’ — 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE “%后盾%” — 不走索引

從上圖可以看出，所有表的type為all，表示全表索引。也就是6 6 6，共遍歷查詢了216次。

除第一張表示全表索引（必須的，要以此關(guān)聯(lián)其他表），其余的為range（索引區(qū)間獲得），也就是6+1+1+1，共遍歷查詢9次即可。

所以我們建議在多表join的時候盡量少join幾張表，因為一不小心就是一個笛卡爾乘積的恐怖掃描，另外，我們還建議盡量使用left join，以少關(guān)聯(lián)多。因為使用join 的話，第一張表是必須的全掃描的，以少關(guān)聯(lián)多就可以減少這個掃描次數(shù)。

6 索引的弊端

不要盲目的創(chuàng)建索引，只為查詢操作頻繁的列創(chuàng)建索引，創(chuàng)建索引會使查詢操作變得更加快速，但是會降低增加、刪除、更新操作的速度，因為執(zhí)行這些操作的同時會對索引文件進(jìn)行重新排序或更新。

但是，在互聯(lián)網(wǎng)應(yīng)用中，查詢的語句遠(yuǎn)遠(yuǎn)大于DML的語句，甚至可以占到80%~90%，所以也不要太在意，只是在大數(shù)據(jù)導(dǎo)入時，可以先刪除索引，再批量插入數(shù)據(jù)，最后再添加索引。

以上就是Mysql中怎么優(yōu)化索引類型，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降摹ＯＭ隳芡ㄟ^這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

當(dāng)前標(biāo)題：Mysql中怎么優(yōu)化索引類型
網(wǎng)頁路徑：http://chinadenli.net/article18/ihogdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供關(guān)鍵詞優(yōu)化、網(wǎng)站收錄、定制網(wǎng)站、微信公眾號、全網(wǎng)營銷推廣、網(wǎng)站維護(hù)

廣告

聲明：本網(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)