mysql創(chuàng)建索引的三種辦法

一、CREATE INDEX方法

創(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è)形象加強企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

CREATE INDEX 索引名 ON 表名 (列名 [長度] [ ASC | DESC])

限制：只能增加普通索引INDEX和UNIQUE INDEX索引這兩種；不能創(chuàng)建PRIMARY KEY索引

二、ALTER TABLE方法

ALTER TABLE 表名 ADD INDEX [索引名] (列名,…)

ALTER TABLE 表名 ADD UNIQUE [ INDEX | KEY] [索引名] (列名,…)

ALTER TABLE 表名 ADD PRIMARY KEY (列名,…)

ALTER TABLE 表名 ADD FOREIGN KEY [索引名] (列名,…)

三、CREATE TABLE時候指定

創(chuàng)建一般索引

CREATE TABLE tb_stu_info

(

id INT NOT NULL,

name CHAR(45) DEFAULT NULL,

dept_id INT DEFAULT NULL,

age INT DEFAULT NULL,

height INT DEFAULT NULL,

INDEX(height)

);

創(chuàng)建唯一索引

mysql CREATE TABLE tb_stu_info2

(

id INT NOT NULL,

name CHAR(45) DEFAULT NULL,

dept_id INT DEFAULT NULL,

age INT DEFAULT NULL,

height INT DEFAULT NULL,

UNIQUE INDEX(height)

);

創(chuàng)建主鍵（雖然ALTER TABLE也能創(chuàng)建，但主鍵一般都在創(chuàng)表時建立）

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);

Mysql建索引的注意事項

1.最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(、、between、like)就停止匹配，比如a = 1 and b = 2 and c 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式。

3.盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會問，這個比例有什么經(jīng)驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄。

4.索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’)。

5.盡量的擴展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

1."一個頂三個"。建了一個(a,b,c)的復(fù)合索引，那么實際等于建了(a),(a,b),(a,b,c)三個索引，因為每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數(shù)據(jù)的表，這可是不小的開銷！

2.覆蓋索引。同樣的有復(fù)合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優(yōu)化策略。所以，在真正的實際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一

3.索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W*10%=100w 條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合b=2 and c= 3的數(shù)據(jù)，然后再排序，再分頁；如果是復(fù)合索引，通過索引篩選出1000w *10% *10% *10%=1w，然后再排序、分頁，哪個更高效，一眼便知

mysql數(shù)據(jù)庫怎么建索引？

MySQL數(shù)據(jù)庫建立索引步驟：

1、使用SQLPlus或者PLSQL登錄MySQL數(shù)據(jù)庫。

2、執(zhí)行語句create

index

indexname

on

table(col)

tablespace

tablespacename;其中

"indexname"為需要建立索引的索引名；

"table"是需要建立索引的表；

"col"為需要建立索引的字段列;

”tablespacename“為建立索引存放的空間(如果不需要制定表空間則使用“create

index

indexname

on

table(col)”)。

mysql聯(lián)合索引如何創(chuàng)建？

CREATE TABLE `test` ('aaa' varchar(16) NOT NULL default '', 'bbb' varchar(16) NOT NULL default '', 'ccc' int(11) UNSIGNED NOT NULL default 0, KEY `sindex` (`aaa`,`bbb`,`ccc`) ) ENGINE=MyISAM COMMENT='';\x0d\x0a\x0d\x0a這樣就在 aaa、bbb、ccc 3列上建立聯(lián)合索引了。\x0d\x0a\x0d\x0a如果表已經(jīng)建好了，那么就在phpmyadmin里面執(zhí)行：\x0d\x0aalert table test add INDEX `sindex` (`aaa`,`bbb`,`ccc`) \x0d\x0a\x0d\x0a就可以在這3列上建立聯(lián)合索引了。

mysql創(chuàng)建索引的原則

1．選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學(xué)生表中學(xué)號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學(xué)生的信息。如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。

2．為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引

經(jīng)常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

3．為常作為查詢條件的字段建立索引

如果某個字段經(jīng)常用來做查詢條件，那么該字段的查詢速度會影響整個表的查詢速度。因此，為這樣的字段建立索引，可以提高整個表的查詢速度。

4．限制索引的數(shù)目

索引的數(shù)目不是越多越好。每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。修改表時，對索引的重構(gòu)和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

5．盡量使用數(shù)據(jù)量少的索引

如果索引的值很長，那么查詢的速度會受到影響。例如，對一個CHAR(100)類型的字段進行全文檢索需要的時間肯定要比對CHAR(10)類型的字段需要的時間要多。

6．盡量使用前綴來索引

如果索引字段的值很長，最好使用值的前綴來索引。例如，TEXT和BLOG類型的字段，進行全文檢索會很浪費時間。如果只檢索字段的前面的若干個字符，這樣可以提高檢索速度。

7．刪除不再使用或者很少使用的索引

表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫管理員應(yīng)當(dāng)定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

8 . 最左前綴匹配原則，非常重要的原則。

mysql會一直向右匹配直到遇到范圍查詢(、、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“ 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

9 .=和in可以亂序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式

。

10 . 盡量選擇區(qū)分度高的列作為索引。

區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就 是0，那可能有人會問，這個比例有什么經(jīng)驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條 記錄

11 .索引列不能參與計算，保持列“干凈”。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本 太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

12 .盡量的擴展索引，不要新建索引。

比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可

注意：選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的準(zhǔn)則，但不能拘泥于上面的準(zhǔn)則。讀者要在以后的學(xué)習(xí)和工作中進行不斷的實踐。根據(jù)應(yīng)用的實際情況進行分析和判斷，選擇最合適的索引方式。

網(wǎng)頁名稱：mysql索引應(yīng)該怎么建,mysql索引怎么做
轉(zhuǎn)載注明：http://chinadenli.net/article18/dsshhgp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應(yīng)式網(wǎng)站、網(wǎng)站制作、搜索引擎優(yōu)化、云服務(wù)器、定制網(wǎng)站、動態(tài)網(wǎng)站

廣告

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