Mysql資料庫應用--索引(二)

建立索引

• 建立表的時候建立索引

CREATE TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]

UNIQUE、FULLTEXT和SPATIAL為可選參數,分别表示唯一索引、全文索引和空間索引；INDEX與KEY為同義詞，兩者作用相同，用來指定建立索引；col_name為需要建立索引的字段列，該列必須從資料表中定義的多個列中選擇；index_name指定索引的名稱，為可選參數，如果不指定，MySQL預設col_name為索引值；length為可選參數，表示索引的長度，隻有字元串類型的字段才能指定索引長度；ASC或DESC指定升序或者降序的索引值存儲

• 檢視索引

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) 
);
           

建立唯一索引

CREATE TABLE t1(
`id` INT NOT NULL,
`name` CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
           

建立單列索引

CREATE TABLE t1(
`id` INT NOT NULL,
`name` CHAR(30) NOT NULL,
INDEX SingleIdx(name(20))
);
           

建立索引長度為20的單列索引

建立組合索引

CREATE TABLE t3(
`id` INT NOT NULL,
`name` CHAR(20) NOT NULL,
`age` INT NOT NULL,
`info` VARCHAR(255),
INDEX MultiIdx(`id`, `name`, `age`)
);
           

• 檢視表的結構

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
           

由結果可以看出,建立了id、name、age名為MultiIdx的組合索引

組合索引可起到幾個索引的作用，但是使用時并不是随便查詢哪個字段都可以使用索引，而是要遵循“最左字首”原則：利用索引中最左邊的列集來比對行，這樣的列集稱為最左字首。例如，這裡有id、name和age三個字段構成的索引，索引行中按照id/name/age的順序存放，索引可以搜尋下面的字段組合：（id，name，age），（id，name）或者id。

EXPLANIN語句--檢視索引

EXPLAIN SELECT * FROM book WHERE yearPublication=2016;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	book		yearPublication			const

EXPLANIN語句輸出結果的各行解釋如下:

(1) select_type: 指定所使用的SELECT查詢類型，這裡值為SIMPLE，表示簡單的SELECT，不使用UNION或子查詢。其他可能的取值有PRIMARY、UNION、SUBQUERY等。

(2) table: 指定資料庫讀取的資料表的名字，它們按被讀取的先後順序排列。

(3) type: 指定本資料庫表與其他資料表之間的關聯關系，可能的取值有system、const、eq_ref、ref、range、index、和all。

(4) possible_keys: 給出MySQL在搜尋資料記錄時可選用的各個索引。

(5) key: MySQL實際選用的索引。

(6) key_len: 給出索引按位元組計算的長度，key_len的數值越小，表示查詢速度越快

(7) ref: 給出關聯關系中另一個資料表裡的資料列的名字。

(8) rows: MySQL在執行這個查詢時預計會從這個資料表裡讀出的資料行的個數。

(9) extra: 提供與關聯操作有關的資訊。