索引是一種特殊的數(shù)據(jù)結(jié)構(gòu)，可以用來(lái)快速查詢數(shù)據(jù)庫(kù)的特定記錄，建立是一中提高數(shù)據(jù)庫(kù)性能的重要方式。
?
內(nèi)容：索引的意義，索引的設(shè)計(jì)，創(chuàng)建和刪除
?
索引簡(jiǎn)介
索引是建立在表上的，有一列或者多列組成，并對(duì)這一列或者多列進(jìn)行排序的一種結(jié)構(gòu)。
?
所有存儲(chǔ)引擎對(duì)每個(gè)表至少支持16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié)，索引有兩種存儲(chǔ)類型，包括
B型樹索引
和
哈希索引
。
?
索引的優(yōu)點(diǎn)是可以提高檢索的速度，但是創(chuàng)建和維護(hù)索引需要耗費(fèi)時(shí)間，這個(gè)時(shí)間隨著數(shù)據(jù)量的增加而增加。
?
索引可以提高查詢的速度，但是會(huì)影響插入的速度，當(dāng)要插入大量的數(shù)據(jù)時(shí)，最好的辦法是先刪除索引，插入數(shù)據(jù)后再建立索引。
?
MySQL的索引分為：普通索引，唯一性索引，全文索引，單列索引，多列索引和空間索引。
?
目前只有MyISAM存儲(chǔ)引擎支持全文索引，InnoDB引擎還不支持全文索引。
?
索引的設(shè)計(jì)原則
- 選擇唯一性索引。
- 為經(jīng)常需要排序，分組和聯(lián)合操作的字段建立索引。
- 為常作為查詢條件的字段建立索引。
- 限制索引的數(shù)目。
- 盡量使用數(shù)據(jù)量少的索引。
- 盡量使用前綴來(lái)索引。如果字段的值很長(zhǎng)，最好使用值的前綴來(lái)索引，如果只檢索子酸的前面的若干字符，可以提高檢索的速度。
- 刪除不再使用或者很少使用的索引。
原則只是參考而不能拘泥。
?
創(chuàng)建索引
三種方式：在創(chuàng)建表是創(chuàng)建索引，在已存在的表上創(chuàng)建索引和使用alter table語(yǔ)句創(chuàng)建索引。
?
mysql
>
show
tables;
+ - - - - - - - - - - - - - - - - +
| Tables_in_kiwi |
+ - - - - - - - - - - - - - - - - +
| stu |
+ - - - - - - - - - - - - - - - - +
1 row in set ( 0 .00 sec)
mysql > create table indexTest(id int , name varchar ( 20 ), sex boolean , index index_id(id));
Query OK, 0 rows affected ( 0 . 08 sec)
mysql > desc indextest;
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field | Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| id | int ( 11 ) | YES | MUL | NULL | |
| name | varchar ( 20 ) | YES | | NULL | |
| sex | tinyint( 1 ) | YES | | NULL | |
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
3 rows in set ( 0 . 01 sec)
mysql > explain select * from indextest where id = 1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
id: 1
select_type: SIMPLE
table : indextest
type : ref
possible_keys: index_id
key : index_id
key_len: 5
ref : const
rows : 1
Extra: Using where
1 row in set ( 0 .00 sec)
+ - - - - - - - - - - - - - - - - +
| Tables_in_kiwi |
+ - - - - - - - - - - - - - - - - +
| stu |
+ - - - - - - - - - - - - - - - - +
1 row in set ( 0 .00 sec)
mysql > create table indexTest(id int , name varchar ( 20 ), sex boolean , index index_id(id));
Query OK, 0 rows affected ( 0 . 08 sec)
mysql > desc indextest;
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field | Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| id | int ( 11 ) | YES | MUL | NULL | |
| name | varchar ( 20 ) | YES | | NULL | |
| sex | tinyint( 1 ) | YES | | NULL | |
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
3 rows in set ( 0 . 01 sec)
mysql > explain select * from indextest where id = 1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
id: 1
select_type: SIMPLE
table : indextest
type : ref
possible_keys: index_id
key : index_id
key_len: 5
ref : const
rows : 1
Extra: Using where
1 row in set ( 0 .00 sec)
?
創(chuàng)建單列索引，subject(10)是為了不查詢?nèi)啃畔⒍岣邫z索的速度。
mysql > create table singleRow(id int , name varchar ( 20 ),subject varchar ( 30 ), index index_st(subject( 10 )));
Query OK, 0 rows affected ( 0 . 17 sec)
mysql > show create table singlerow\ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : singlerow
Create Table : CREATE TABLE `singlerow` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
`subject` varchar ( 30 ) DEFAULT NULL ,
KEY `index_st` (`subject`( 10 ))
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
多列索引，空間索引類似。
?
在已存在的表上建立索引
語(yǔ)法為： create [unique|fulltext|spatial] index index_name on table_name (property_name[length] [asc|desc]);
mysql
>
desc
stu;
+ - - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
| Field | Type | Null | Key | Default | Extra |
+ - - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
| id | int ( 10 ) | NO | PRI | NULL | auto_increment |
| s_num | int ( 10 ) | YES | MUL | NULL | |
| course | varchar ( 20 ) | YES | | NULL | |
| score | varchar ( 4 ) | YES | | NULL | |
+ - - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
4 rows in set ( 0 . 05 sec)
mysql > show create table stu \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : stu
Create Table : CREATE TABLE `stu` (
`id` int ( 10 ) NOT NULL AUTO_INCREMENT,
`s_num` int ( 10 ) DEFAULT NULL ,
`course` varchar ( 20 ) DEFAULT NULL ,
`score` varchar ( 4 ) DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index_id` (`id`),
KEY `grade_fk` (`s_num`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
+ - - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
| Field | Type | Null | Key | Default | Extra |
+ - - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
| id | int ( 10 ) | NO | PRI | NULL | auto_increment |
| s_num | int ( 10 ) | YES | MUL | NULL | |
| course | varchar ( 20 ) | YES | | NULL | |
| score | varchar ( 4 ) | YES | | NULL | |
+ - - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
4 rows in set ( 0 . 05 sec)
mysql > show create table stu \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : stu
Create Table : CREATE TABLE `stu` (
`id` int ( 10 ) NOT NULL AUTO_INCREMENT,
`s_num` int ( 10 ) DEFAULT NULL ,
`course` varchar ( 20 ) DEFAULT NULL ,
`score` varchar ( 4 ) DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index_id` (`id`),
KEY `grade_fk` (`s_num`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
?
使用alter table創(chuàng)建索引
語(yǔ)法為：
alter
table
table_name
add
[
unique
|fulltext|spatial]
index
index_name(property_name[
length
] [
asc
|
desc
]);
?
mysql
>
create
table
index_1(id
int
,
name
varchar
(
20
),
class
int
);
Query OK, 0 rows affected ( 0 . 11 sec)
mysql > show tables;
+ ----------------+
| Tables_in_kiwi |
+ ----------------+
| index_1 |
| singlerow |
| stu |
+ ----------------+
3 rows in set ( 0 .00 sec)
mysql > show create table index_1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
mysql > alter table index_1 add fulltext index index_alter ( name desc );
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
mysql > alter table index_1 engine = myisam;
Query OK, 0 rows affected ( 0 . 36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > alter table index_1 add fulltext index index_alter ( name desc );
Query OK, 0 rows affected ( 0 . 13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > show create table index_1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL ,
FULLTEXT KEY `index_alter` (` name `)
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
Query OK, 0 rows affected ( 0 . 11 sec)
mysql > show tables;
+ ----------------+
| Tables_in_kiwi |
+ ----------------+
| index_1 |
| singlerow |
| stu |
+ ----------------+
3 rows in set ( 0 .00 sec)
mysql > show create table index_1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
mysql > alter table index_1 add fulltext index index_alter ( name desc );
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
mysql > alter table index_1 engine = myisam;
Query OK, 0 rows affected ( 0 . 36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > alter table index_1 add fulltext index index_alter ( name desc );
Query OK, 0 rows affected ( 0 . 13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > show create table index_1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL ,
FULLTEXT KEY `index_alter` (` name `)
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
?
刪除索引
語(yǔ)法：
drop
index
index_name
on
table_name
;
mysql
>
show
create
table
index_1 \
G
;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL ,
FULLTEXT KEY `index_alter` (` name `)
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
ERROR:
No query specified
mysql > drop index index_alter on index_1;
Query OK, 0 rows affected ( 0 . 11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > show create table index_1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL ,
FULLTEXT KEY `index_alter` (` name `)
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
ERROR:
No query specified
mysql > drop index index_alter on index_1;
Query OK, 0 rows affected ( 0 . 11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > show create table index_1 \ G ;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Table : index_1
Create Table : CREATE TABLE `index_1` (
`id` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 20 ) DEFAULT NULL ,
` class ` int ( 11 ) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1
1 row in set ( 0 .00 sec)
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
