mysql如何建立索引

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

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

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

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 語句和運行結果如下所示。

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ù)越大，當進行聯(lián)合時，MySQL 使用該索引的機會就越大。

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

Packed 指示關鍵字如何被壓縮。若沒有被壓縮，值為 NULL。

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

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

Comment 顯示評注。

mysql有幾種索引類型？使用索引時都有那些地方要注意？sql優(yōu)化原則是什么？

mysql的索引類型及使用索引時的注意事項有：

一、普通索引。這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：

1、創(chuàng)建索引

代碼如下:

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

2、修改表結構

代碼如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 創(chuàng)建表的時候直接指定

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? INDEX [indexName] (username(length))?? );?

-- 刪除索引的語法：

DROP INDEX [indexName] ON mytable;

二、唯一索引。它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：

代碼如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 修改表結構

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- 創(chuàng)建表的時候直接指定

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? UNIQUE [indexName] (username(length))?? );

三、主鍵索引。它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：

代碼如下:

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? PRIMARY KEY(ID)?? );

當然也可以用 ALTER 命令。記?。阂粋€表只能有一個主鍵。

四、組合索引。為了形象地對比單列索引和組合索引，為表添加多個字段：

代碼如下:

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? city VARCHAR(50) NOT NULL,?? age INT NOT NULL? );

為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：

代碼如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]

建表時，usernname長度為 16，這里用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。

如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引，其實是相當于分別建立了下面三組組合索引：usernname,city,age?? usernname,city?? usernname? 為什么沒有 city，age這樣的組合索引呢？這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引，下面的幾個SQL就會用到這個組合索引：

[code]

SELECT * FROM mytable WHREE username="admin" AND city="鄭州"? SELECT * FROM mytable WHREE username="admin"

mysql索引

二叉搜索樹、N叉樹

頁分裂：B+樹的插入可能會引起數(shù)據(jù)頁的分裂，刪除可能會引起數(shù)據(jù)頁的合并，二者都是比較重的IO消耗，所以比較好的方式是順序插入數(shù)據(jù)，這也是我們一般使用自增主鍵的原因之一。

頁分裂逆過程：頁合并，當刪除數(shù)據(jù)后，相鄰的兩個數(shù)據(jù)頁利用率很低的時候會做數(shù)據(jù)頁合并

主鍵索引：key：主鍵，value：數(shù)據(jù)頁，存儲每行數(shù)據(jù)

非主鍵索引：key：非主鍵索引，value：主鍵key，導致回表

最左匹配：優(yōu)先將區(qū)分度高的列放到前面，這樣可以高效索引，

最左匹配原則遇到范圍查詢就停止匹配，范圍查詢(、、between、like)為什么？因為出現(xiàn)范圍匹配后，后面的索引字段無法保證有序，局部有序失去，順序失去則無法提高查詢效率

SELECT * FROM table WHERE a IN (1,2,3) and b 1;

如何建立索引？

還是對(a，b)建立索引，因為IN在這里可以視為等值引用，不會中止索引匹配，所以還是(a,b)!

索引組織表

索引用頁存儲：key【10】-point【6】，通過調整key大小，當頁大小固定的情況下，通過調整key大小，使得N叉樹變化；

如key 10, point 6則單個索引16字節(jié)，頁大小為16k，則頁面總共可以存儲1024個索引，即N大小

覆蓋索引: 二級索引的信息已經(jīng)存在想要的列，例如主鍵

如果現(xiàn)在有一個高頻請求，要根據(jù)市民的身份證號查詢他的姓名，這個聯(lián)合索引就有意義了。它可以在這個高頻請求上用到覆蓋索引，不再需要回表查整行記錄，減少語句的執(zhí)行時間。

索引下推優(yōu)化：可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

整理索引碎片，重建表：alter table T engine=InnoDB

??首先是看key的大小，另外是數(shù)據(jù)頁的大小，如果需要改變N，則需要從這兩個方面做改動；

一個innoDB引擎的表，數(shù)據(jù)量非常大，根據(jù)二級索引搜索會比主鍵搜索快，文章闡述的原因是主鍵索引和數(shù)據(jù)行在一起，非常大搜索慢，我的疑惑是：通過普通索引找到主鍵ID后，同樣要跑一邊主鍵索引，對于使用覆蓋索引的情況下，使用覆蓋索引可以直接解決問題

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建立索引經(jīng)驗

在實際開發(fā)中使用數(shù)據(jù)庫時，難免會遇到一些大表數(shù)據(jù)，對這些數(shù)據(jù)進行查詢時，有時候SQL會查詢得特別慢，這時候，有經(jīng)驗的老師傅會告訴你，你看一下哪幾個字段查的多，加一個索引就好了。

那么，怎么合理地建立索引呢？這里分享一下我的一些經(jīng)驗，如有不妥之處，歡迎批評指正。

1、不要盲目建立索引 ， 先分析再創(chuàng)建

索引雖然能大幅度提升我們的查詢性能，但也要知道，在你進行增刪改時，索引樹也要同樣地進行維護。所以，索引不是越多越好，而是按需建立。最好是在一整塊模塊開發(fā)完成后，分析一下，去針對大多數(shù)的查詢，建立聯(lián)合索引。

2、使用聯(lián)合索引盡量覆蓋多的條件

這是說在一個慢sql里假如有五個where ，一個 order by ，那么我們的聯(lián)合索引盡量覆蓋到這五個查詢條件，如果有必要，order by 也覆蓋上 。

3、小基數(shù)字段不需要索引

這個意思是，如果一張表里某個字段的值只有那么幾個，那么你針對這個字段建立的索引其實沒什么意義，比如說，一個性別字段就兩種結果，你建了索引，排序也沒什么意思（也就是索引里把男女給分開了）

所以說，索引盡量選擇基數(shù)大的數(shù)據(jù)去建立，能最大化地利用索引

4、長字符串可以使用前綴索引

我們建立索引的字段盡量選擇字段類型較小的，比如一個varchar(20)和varchar(256)的，我們在20的上面建立的索引和在256上就有明顯的差距（字符串那么長排序也不好排呀，唉）。

當然，如果一定是要對varchar(256)建立索引,我們可以選擇里面的前20個字符放在索引樹里（這里的20不絕對，選擇能盡量分辨數(shù)據(jù)的最小字符字段設計），類似這樣KEY index(name(20),age,job) ，索引只會對name的前20個字符進行搜索，但前綴索引無法適用于order by 和 group by。

5、對排序字段設計索引的優(yōu)先級低

如果一個SQL里我們出現(xiàn)了范圍查找，后邊又跟著一個排序字段，那么我們優(yōu)先給范圍查找的字段設置索引，而不是優(yōu)先排序。

6、如果出現(xiàn)慢SQL，可以設計一個只針對該條SQL的聯(lián)合索引。

不過慢SQL的優(yōu)化，需要一步步去進行分析，可以先用explain查看SQL語句的分析結果，再針對結果去做相應的改進。explain的東西我們下次再講。

PS：在 select 語句之前增加 explain 關鍵字，MySQL 會在查詢上設置一個標記，執(zhí)行查詢會返回執(zhí)行計劃的信息，而不是 執(zhí)行這條SQL。

MySQL中如何設置唯一索引，聯(lián)合索引？

1.UNIQUE　關鍵字建唯一索引\x0d\x0amysql CREATE TABLE `wb_blog` ( \x0d\x0a - `id` smallint(8) unsigned NOT NULL, \x0d\x0a - `catid` smallint(5) unsigned NOT NULL DEFAULT '0', \x0d\x0a - `title` varchar(80) NOT NULL DEFAULT '', \x0d\x0a - `content` text NOT NULL, \x0d\x0a - PRIMARY KEY (`id`), \x0d\x0a - UNIQUE KEY `catename` (`catid`) \x0d\x0a - ) ; \x0d\x0a如果建好表了，可以用以下語句建\x0d\x0a mysql CREATE UNIQUE INDEX catename ON wb_blog(catid); \x0d\x0a\x0d\x0a2.聯(lián)合索引\x0d\x0aALTER TABLE `tasks`\x0d\x0aADD INDEX `testabc` (`title`, `created`) ;\x0d\x0a\x0d\x0a3聯(lián)合唯一索引（假設有這個需求，在同一天內不能建兩個tiltle一樣的任務）\x0d\x0aALTER TABLE `tasks`\x0d\x0aADD UNIQUE INDEX `testabc` (`title`, `created`) ;\x0d\x0a\x0d\x0a數(shù)據(jù)庫建索引的科學性事關數(shù)據(jù)庫性能，索引也不是越多越好。