mysql索引怎么建立 mysql如何創(chuàng)建索引

Mysql建立索引經(jīng)驗

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

網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、成都微信小程序、集團企業(yè)網(wǎng)站建設(shè)等服務(wù)項目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了思明免費建站歡迎大家使用！

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Mysql索引

建立索引，要使用離散度（選擇度）更高的字段。

我們先來看一個重要的屬性列的 離散度，

count(distinct(column_name)) : count(*) -- 列的全部不同值個數(shù)：所有數(shù)據(jù)行行數(shù)

數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。簡單來說，如果列的重復(fù)值越多，離散度就越低，重復(fù)值越少，離散度就越高。

當(dāng)字段值比較長的時候，建立索引會消耗很多的空間，搜索起來也會很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引，這個就叫前綴索引。

創(chuàng)建一張商戶表，因為地址字段比較長，在地址字段上建立前綴索引

create table shop(address varchar(120) not null);

alter table shop add key(address(12));? // 截取12個字符作為前綴索引是最優(yōu)的嗎？

問題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲空間的目的，截取得少了，重復(fù)內(nèi)容太多，字段的散列度（選擇性）會降低。怎么計算不同的長度的選擇性呢？

先看一下字段在全部數(shù)據(jù)中的選擇度計算公式：

select count(distinct address) / count(*) from shop;

select count(distinct left(address, n)) / count(*) as subn from shop;

count(distinct left(address,n)) / count(*) 的結(jié)果是會隨著 n 的變大而變大。舉個例子，現(xiàn)在有兩個address（東大街長興小區(qū)，東大街福樂小區(qū)），那么 distinct(address，2) distinct(address，3）

==所以，截取的長度越長就會越接近字段在全部數(shù)據(jù)中的選擇度

==所以，我們要權(quán)衡索引大小和查詢速度。

舉個例子，通過不同長度去計算，與全表的選擇性對比：

SELECT? COUNT(DISTINCT(address))/COUNT(*) sub,? ? ? ? ? ? -- 字段在全部數(shù)據(jù)中的選擇度

COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,? -- 截取前5個字符的選擇度

COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,?

COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,

COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,? -- 截取前10個字符的選擇度

COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,

COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,

COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,

COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15

FROM shop;

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| sub? ? | sub5? | sub7? | sub9? | sub10? | sub11? | sub12? | sub13? | sub15? |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

可以看到在截取 11 個字段時 sub11(0.9993) 就已經(jīng)很接近字段在全部數(shù)據(jù)中的選擇度 sub(0.9958)了，而且長度也相較后面更短一些， 綜合考慮比較合適。

ALTER TABLE shop ADD KEY (address(11));

1.索引的個數(shù)不要過多（浪費空間，更新變慢）

2.在用于 where 判斷 order 排序和 join 的（on）字段上創(chuàng)建索引

3.區(qū)分度低的字段，例如性別，不要建索引（離散度太低，導(dǎo)致掃描行數(shù)過多）

4.更新頻繁的值，不要作為主鍵或者索引（頁分裂）

5.不建議用無序的值作為索引，例如身份證、UUID（在索引比較時需要轉(zhuǎn)為ASCII，并且插入時可能造成頁分裂）

6.若在多個字段都要創(chuàng)建索引的情況下，聯(lián)合索引優(yōu)于單值索引

7.聯(lián)合索引把散列性高（區(qū)分度高）的值放在前面

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)建索引的方法。

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

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

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

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

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

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

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

Comment 顯示評注。

mysql創(chuàng)建lower索引

mysql創(chuàng)建lower索引的方法：

1、自動創(chuàng)建。在表中定義一個primarykey或者unique時，MySQL數(shù)據(jù)庫會自動創(chuàng)建一個對應(yīng)的主鍵索引或者是唯一索引。

2、手動創(chuàng)建。在創(chuàng)建表的時候創(chuàng)建索引，即可給已經(jīng)有的表創(chuàng)建索引。

本文名稱：mysql索引怎么建立 mysql如何創(chuàng)建索引
轉(zhuǎn)載源于：http://m.jiaotiyi.com/article/dodpshj.html