創(chuàng)建索引
- 創(chuàng)建表的時(shí)候創(chuàng)建索引
CREATE TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
UNIQUE、FULLTEXT和SPATIAL為可選參數(shù),分別表示唯一索引、全文索引和空間索引；INDEX與KEY為同義詞，兩者作用相同，用來(lái)指定創(chuàng)建索引；col_name為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中定義的多個(gè)列中選擇；index_name指定索引的名稱(chēng)，為可選參數(shù)，如果不指定，MySQL默認(rèn)col_name為索引值；length為可選參數(shù)，表示索引的長(zhǎng)度，只有字符串類(lèi)型的字段才能指定索引長(zhǎng)度；ASC或DESC指定升序或者降序的索引值存儲(chǔ)
- 查看索引
CREATE TABLE `book`(
`bookId` INT NOT NULL,
`bookName` VARCHAR(255) NOT NULL,
`authors` VARCHAR(255) NOT NULL,
`info` VARCHAR(255),
`comment` VARCHAR(255),
`yearPublication` YEAR NOT NULL,
INDEX(yearPublication)
);
創(chuàng)建唯一索引
CREATE TABLE t1(
`id` INT NOT NULL,
`name` CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
創(chuàng)建單列索引
CREATE TABLE t1(
`id` INT NOT NULL,
`name` CHAR(30) NOT NULL,
INDEX SingleIdx(name(20))
);
創(chuàng)建索引長(zhǎng)度為20的單列索引
創(chuàng)建組合索引
CREATE TABLE t3(
`id` INT NOT NULL,
`name` CHAR(20) NOT NULL,
`age` INT NOT NULL,
`info` VARCHAR(255),
INDEX MultiIdx(`id`, `name`, `age`)
);
- 查看表的結(jié)構(gòu)
SHOW CREATE TABLE t3;
CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`name` char(20) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由結(jié)果可以看出,創(chuàng)建了id、name、age名為MultiIdx的組合索引
組合索引可起到幾個(gè)索引的作用，但是使用時(shí)并不是隨便查詢(xún)哪個(gè)字段都可以使用索引，而是要遵循“最左前綴”原則：利用索引中最左邊的列集來(lái)匹配行，這樣的列集稱(chēng)為最左前綴。例如，這里有id、name和age三個(gè)字段構(gòu)成的索引，索引行中按照id/name/age的順序存放，索引可以搜索下面的字段組合：（id，name，age），（id，name）或者id。
EXPLANIN語(yǔ)句--查看索引
EXPLAIN SELECT * FROM book WHERE yearPublication=2016;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | book | ref | yearPublication | yearPublication | 1 | const | 1 |
EXPLANIN語(yǔ)句輸出結(jié)果的各行解釋如下:
(1) select_type: 指定所使用的SELECT查詢(xún)類(lèi)型，這里值為SIMPLE，表示簡(jiǎn)單的SELECT，不使用UNION或子查詢(xún)。其他可能的取值有PRIMARY、UNION、SUBQUERY等。
(2) table: 指定數(shù)據(jù)庫(kù)讀取的數(shù)據(jù)表的名字，它們按被讀取的先后順序排列。
(3) type: 指定本數(shù)據(jù)庫(kù)表與其他數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系，可能的取值有system、const、eq_ref、ref、range、index、和all。
(4) possible_keys: 給出MySQL在搜索數(shù)據(jù)記錄時(shí)可選用的各個(gè)索引。
(5) key: MySQL實(shí)際選用的索引。
(6) key_len: 給出索引按字節(jié)計(jì)算的長(zhǎng)度，key_len的數(shù)值越小，表示查詢(xún)速度越快
(7) ref: 給出關(guān)聯(lián)關(guān)系中另一個(gè)數(shù)據(jù)表里的數(shù)據(jù)列的名字。
(8) rows: MySQL在執(zhí)行這個(gè)查詢(xún)時(shí)預(yù)計(jì)會(huì)從這個(gè)數(shù)據(jù)表里讀出的數(shù)據(jù)行的個(gè)數(shù)。
(9) extra: 提供與關(guān)聯(lián)操作有關(guān)的信息。
