MySql索引提高查詢速度常用方法代碼示例

使用索引提高查詢速度

1.前言

　　在web開(kāi)發(fā)中，業(yè)務(wù)模版，業(yè)務(wù)邏輯（包括緩存、連接池）和數(shù)據(jù)庫(kù)這三個(gè)部分，數(shù)據(jù)庫(kù)在其中負(fù)責(zé)執(zhí)行SQL查詢并返回查詢結(jié)果，是影響網(wǎng)站速度最重要的性能瓶頸。本文主要針對(duì)Mysql數(shù)據(jù)庫(kù)，在淘寶的去IOE（I 代表IBM的縮寫(xiě)，即去IBM的存儲(chǔ)設(shè)備和小型機(jī)；O是代表Oracle的縮寫(xiě)，去Oracle數(shù)據(jù)庫(kù)，采用Mysql和Hadoop代替；E是代表EMC2，去EMC2的設(shè)備性，用PC server代替EMC2）,大量使用Mysql集群！而優(yōu)化數(shù)據(jù)的重要一步就是索引的建立，對(duì)于Mysql出現(xiàn)的慢查詢，可以用索引提升查詢速度。索引用于快速找出在某個(gè)列中有一特定值的行，不使用索引，Mysql將全表掃描，從第一條記錄開(kāi)始，然后讀完整個(gè)表直到找出相關(guān)的行。

2.Mysql索引類(lèi)型及創(chuàng)建

索引相關(guān)知識(shí)：

PRI主鍵約束；

UNI唯一約束；

MUL可以重復(fù)。


查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
· Table
表的名稱。
· Non_unique
如果索引不能包括重復(fù)詞，則為0。如果可以，則為1。
· Key_name
索引的名稱。
· Seq_in_index
索引中的列序列號(hào)，從1開(kāi)始。
· Column_name
列名稱。
· Collation
列以什么方式存儲(chǔ)在索引中。在MySQL中，有值‘A'（升序）或NULL（無(wú)分類(lèi)）。
· Cardinality
索引中唯一值的數(shù)目的估計(jì)值。通過(guò)運(yùn)行ANALYZE TABLE或myisamchk -a可以更新?；鶖?shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來(lái)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL使用該索引的機(jī) 會(huì)就越大。
· Sub_part
如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL。
· Packed
指示關(guān)鍵字如何被壓縮。如果沒(méi)有被壓縮，則為NULL。
· Null
如果列含有NULL，則含有YES。如果沒(méi)有，則該列含有NO。
· Index_type
用過(guò)的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment

1).主鍵索引

它是一種特殊的唯一索引，不允許為空。一般建表時(shí)同時(shí)創(chuàng)建主鍵索引：

CREATE TABLE user(
id int unsigned not null auto_increment,
name varchar(50) not null,
email varchar(40) not null,
primary key (id)
);

2).普通索引

這是最基本的索引，沒(méi)有任何限制：

create index idx_email on user(
email(20)
);create index idx_name on user(name(20));

mysql 支持索引前綴，一般姓名不超過(guò)20字符，所以建立索引限定20長(zhǎng)度，節(jié)省索引文件大小

3).唯一索引

它與前面的普通索引類(lèi)似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，列值的組合必須唯一。

CREATE UNIQUE INDEX idx_email ON user(
email
); 

4).組合索引

create table sb_man(
id int PRIMARY key auto_increment,
new_name char(30) not null,
old_name char(30) not null,
index name(new_name,old_name)
);# name索引是一個(gè)對(duì)new_name和old_name的索引。查詢方法：select * from sb_man where new_name='yu';select * from sb_man where new_name='yu' and old_name='yu1';提示:>>>>>>  組合索引是最左前綴創(chuàng)建, 所以不能用如下sqlselect * from sb_man where old_name='yu1';   錯(cuò)誤

3.什么時(shí)候用索引

1.索引引用

在索引列上，除了上面提到的有序查找之外，數(shù)據(jù)庫(kù)利用各種各樣的快速定位技術(shù)，能夠大大提高查詢效率。特別是當(dāng)數(shù)據(jù)量非常大，查詢涉及多個(gè)表時(shí)，使用索引往往能使查詢速度加快成千上萬(wàn)倍。

例如，有2個(gè)未索引的表t1、t2、分別只包含列c1、c2 每個(gè)表分別含有1000行數(shù)據(jù)組成，值為111的數(shù)值，然后設(shè)置三張表，不同的幾個(gè)值，

(這里我是用pymysql 執(zhí)行 while 創(chuàng)建的數(shù)據(jù))

查找對(duì)應(yīng)值相等行的查詢?nèi)缦滤尽?/p>

在無(wú)索引的情況下處理此查詢，必須尋找3個(gè)表所有的組合，以便得出與WHERE子句相配的那些行。

select c1,c2 from t1,t2 where c1!=c2

結(jié)果查詢過(guò)程>>

2.創(chuàng)建索引

在執(zhí)行CREATE TABLE語(yǔ)句時(shí)可以創(chuàng)建索引，也可以單獨(dú)用CREATE INDEX或ALTER TABLE來(lái)為表增加索引。

1．ALTER TABLE

ALTER TABLE用來(lái)創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引

刪除索引: alter table tab_name drop {index|key} index_name；
　　　　　alter table t1 drop index idx_c1;
添加索引: alter table t1 add index idx_c1(c1);
　　　　　alter table t2 add index idx_c2(c2);

查詢結(jié)果雖然感覺(jué)沒(méi)什么卵用。。。但是索引查詢就是如此了。。。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。