mysql教程：索引的使用
1. 索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
它對(duì)于高性能非常關(guān)鍵,，但人們通常會(huì)忘記或誤解它,。
索引在數(shù)據(jù)越大的時(shí)候越重要。規(guī)模小,、負(fù)載輕的數(shù)據(jù)庫(kù)即使沒(méi)有索引,，也能有好的性能， 但是當(dāng)數(shù)據(jù)增加的時(shí)候,，性能就會(huì)下降很快,。
Tip:蠕蟲(chóng)復(fù)制,可以快速?gòu)?fù)制大量的數(shù)據(jù)
例：insert into emp select * from emp;
2. MySQL中常見(jiàn)的索引
◆普通索引
◆唯一索引
◆主鍵索引
◆組合索引
◆全文索引
◆外鍵 （只有innodb存儲(chǔ)引擎才支持）
2.1普通索引：
這是最基本的索引，它沒(méi)有任何限制,。有以下幾種創(chuàng)建方式：
有以下幾種創(chuàng)建方式：
◆創(chuàng)建索引
| CREATE INDEX indexName ON tablename(username(length)); |
◆修改表結(jié)構(gòu)
| ALTER tablename ADD INDEX indexName (username(length)) |
Tip：length可以小于字段實(shí)際長(zhǎng)度;如果是BLOB 和 TEXT 類型,，必須指定length ,下同
◆創(chuàng)建表的時(shí)候直接指定
| CREATE TABLE mytableuuu( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username(length)) ); CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL); create index index1 on mytable(id); //創(chuàng)建普通索引 |
◆刪掉索引：
| drop index index1 on mytable; |
有一個(gè)概念，
行定義：在聲明字段（列）的時(shí)候定義的,，比如primary key
表定義：在所有字段（列）聲明完之后定義的,，比如primary key，index
CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username));
3.0唯一索引（unique）
索引列的值必須唯一,，但允許有空值,。
1）創(chuàng)建索引：Create UNIQUE INDEX indexName ON tableName(tableColumns(length))
2）修改表結(jié)構(gòu)：Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)
3）創(chuàng)建表的時(shí)候直接指定：Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length));
4.0主鍵索引（primary key）
它是一種特殊的唯一索引，不允許有空值,。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
當(dāng)然也可以用 ALTER 命令,。
Tip：記住：一個(gè)表只能有一個(gè)主鍵,。主鍵索引就是我們所說(shuō)的主鍵,。在一個(gè)表中，主鍵只能有一個(gè)，但是普通索引和唯一索引可以有多個(gè),。
5.0組合索引
5.1多列索引,，由多個(gè)列共同來(lái)組成一個(gè)索引。
增加組合索引
alter table mytable add index name_city_age(username,city,age);
對(duì)于組合索引,，必須以最左索引為前綴,，依次排列的，才可以使用到組合索引,，中間不能有間隔,。
下面的可以使用到組合索引：
◆ username,city,age
◆ usernname,city
◆ usernname
下面的不可以使用到組合索引：
◆ city,age
◆ city
◆ age
注意組合索引，如果在某個(gè)表中,，有多個(gè)索引,，我們可以考慮一下，使用組合索引來(lái)優(yōu)化,。
5.2 Explain語(yǔ)句：可以查詢sql 查詢語(yǔ)句使用的索引類型
6. 為什么有索引,，查詢加快？
6.1在MySQL中,，BTREE,，二叉樹(shù)
6.2索引的優(yōu)點(diǎn)：
加快查詢速度。
6.2 索引的缺點(diǎn)：
占用大量的磁盤(pán)空間,。
但是對(duì)插入,、刪除和更新有影響。
6.3使用索引時(shí),，有以下一些技巧和注意事項(xiàng)：
◆索引不會(huì)包含有NULL值的列
只要列中包含有NULL值都將不會(huì)被包含在索引中,，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引就是無(wú)效的,。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL,。
◆使用短索引
對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度,。例如,，如果有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi),，多數(shù)值是惟一的,，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤(pán)空間和I/O操作,。
◆索引列排序
MySQL查詢只使用一個(gè)索引,，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的,。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作,；盡量不要包含多個(gè)列的排序,，如果需要最好給這些列創(chuàng)建復(fù)合索引。
◆like語(yǔ)句操作
一般情況下不鼓勵(lì)使用like操作,，如果非使用不可,，如何使用也是一個(gè)問(wèn)題。like “%aaa%” 不會(huì)使用索引,，而like “aaa%”可以使用索引,。
◆不要在列上進(jìn)行運(yùn)算
select * from users where YEAR(adddate)<2007; 將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描,，因此我們可以改成
select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
