创建索引
- 创建表的时候创建索引
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 | ref | yearPublication | yearPublication | 1 | const | 1 |
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: 提供与关联操作有关的信息。
