MySql 查询索引对应的字段 mysql 使用索引查询

一. 什么是索引
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B树， B+树和Hash。
索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询数据所花费的时间就越多。如果表中查询的列有索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
例如：有一个Student的表，表中有10w条数据。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。
　　如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。
　　如果有了索引，那么会将 Phone 字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历10W条数据了。
二.索引的优缺点
优点：
可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的。 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：
1. 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低SQL执行效率。
2. 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

三.索引的使用

1. 在创建表的时候创建索引
   创建索引：单列索引（普通、唯一、主键）、组合索引、全文索引和空间索引。
   格式：CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|…] [INDEX|KEY] [索引名字] (字段名[length])

a. 创建普通索引
创建普通索引，创建索引时未指定索引的名，会自动帮我们用字段名当作索引名

CREATE TABLE book(
id INT not NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
info  VARCHAR(200) NOT NULL,
INDEX(author)
);

执行：

EXPLAIN SELECT * FROM book WHERE author = 'gsy';

这里主要是看possible_keys和key

possible_keys：MySQL在搜索数据记录时可以选用的各个索引。目前表里有两个索引一个是主键一个是anthor。因为目前表里没有数据，所以主键索引未被使用。

key：实际选用的索引。

b.创建唯一索引

CREATE TABLE student(
id int NOT NULL,
name CHAR(20) NOT null,
UNIQUE INDEX uniqid(id)
);

EXPLAIN SELECT * FROM student WHERE id = 1;

可以看到，通过id查询时，会使用唯一索引。并且还实验了查询一个没有的id值，则不会使用索引，我觉得原因是所有的id应该会存储到一个const tables中，如果没有该id值，那么就没有查找的必要了。

c.创建主键索引

CREATE TABLE tab2(
id INT(4) NOT NULL,
name char(20) DEFAULT NULL,
PRIMARY KEY(id));

插入一条数据，查看索引的使用，否则没有id的话索引不会被使用

INSERT INTO tab1 VALUES(1,'gsy');

d. 创建组合索引：

CREATE TABLE tab2(
id INT(4) NOT NULL,
name CHAR(20) NOT NULL,
age INT(3) NOT NULL,
info VARCHAR(255),
INDEX multiIdx(id,name,age)
);

EXPLAIN SELECT * FROM tab2 WHERE id = 1 AND name = 'gsy';

EXPLAIN SELECT * FROM tab2 WHERE age = 3 AND name = 'gsy';

最左前缀：组合索引遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引组合中的字段可以是(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成最左面的前缀原则，那么就不会用索引，比如，age或者（name，age）组合就不会使用索引查询。

2. 在创建表后创建索引
   在已经存在的表上创建索引
   ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名(长度))

查看表的索引:

show index from book;

Table：创建索引的表。
Non_unique：表示索引是否唯一，其中1代表：非唯一索引， 0代表：唯一索引。
Key_name：索引名称。
Seq_in_index：表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要知道单列索引该值为1，组合索引为别的)。
Column_name：表示定义索引的列字段。 Sub_part：表示索引的长度，当字段值为null时，索引长度为null。
Null：表示该字段是否能为空值。
Index_type：表示索引类型。

为表增加索引

ALTER TABLE book ADD INDEX BkNameIdx(name(30));

查看新的索引

SHOW INDEX FROM book;

使用CREATE INDEX创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])

为book表增加一个普通索引info。字段为CHAR，VARCHAR类型时，索引length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

CREATE INDEX BkInfoIdx ON book(info(10));

查看表的索引

SHOW INDEX FROM book;

3. 删除索引
   使用ALTER DROP删除索引
   ALTER TABLE 表名 DROP INDEX 索引名
   删除book表中的名称为BkInfoIdx的索引

ALTER TABLE book DROP INDEX BkInfoIdx;

查看表的索引

SHOW INDEX FROM book;

使用DROP INDEX删除索引

DROP INDEX 索引名 ON 表名;

删除book表中的名称为BkNameIdx的索引

DROP INDEX BkNameIdx ON book;

查看索引

show INDEX from book;