mysql索引

一、索引简介

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，

尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

二、索引的分类

普通索引：加速查询，生成目录，占用空间，非where列不建议使用索引

唯一索引：unique 唯一索引，可以为空

全完索引：文章索引及其有效

单列索引：同普通索引

多列索引：单列的一种形式

空间索引：适用于空间数据

三、索引测试实验：

准备素材：

mysql> create database school;

mysql> create table school.t2(id int,name varchar(30));

Query OK, 0 rows affected (1.33 sec)

查询表结构：

mysql> desc school.t2;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)    | YES  |    | NULL    |      |

| name  | varchar(30) | YES  |    | NULL    |      |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

通过存储过程，循环输入海量数据：

mysql> delimiter $$

mysql> use school

mysql> create procedure autoinsert1()

BEGIN

declare i int default 1;

while(i<20000)do

insert into school.t2 values(i,'ccc');

set i=i+1;

end    while;

END$$

mysql> delimiter ;

mysql> call autoinsert1();

未创建索引，测试查询过程：

mysql> explain select * from school.t2 where id=20000;

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra      |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 44848 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

（explain 查询mysql使用何种查询方法，目前咩有

44848 需要查询的行数）

创建索引：create index index_id on school.t2(id);

已创建索引，测试查询过程： explain select * from school.t2 where id=20000;

（explain 查询mysql使用何种查询方法，目前是index_id默认索引方式）

创建索引后，实时查询。观察用时：

mysql> select * from school.t2 where id=20000;

+-------+------+

| id    | name |

+-------+------+

| 20000 | ccc  |

+-------+------+

1 row in set (0.00 sec)

四、创建索引：

===创建表时,同时创建索引：

语法：

CREATE TABLE 表名 (

字段名1  数据类型 [完整性约束条件…],

字段名2  数据类型 [完整性约束条件…],

[UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY

[索引名]  (字段名[(长度)]  [ASC |DESC])

);

创建普通索引示例：

CREATE TABLE department10 (

dept_id INT,

dept_name VARCHAR(30) ,

comment VARCHAR(50),

INDEX index_dept_name (dept_name)

);

（index_dept_name 索引名称可以省略）

创建唯一索引示例：

CREATE TABLE department11 (

dept_id INT,

dept_name VARCHAR(30) ,

comment VARCHAR(50),

UNIQUE INDEX index_dept_name (dept_name)

);

创建全文索引示例：

CREATE TABLE department12 (

dept_id INT,

dept_name VARCHAR(30) ,

comment VARCHAR(50),

log text,

FULLTEXT INDEX index_log (log)

);

创建多列索引示例：

CREATE TABLE department13 (

dept_id INT,

dept_name VARCHAR(30) ,

comment VARCHAR(50),

INDEX index_dept_name_comment (dept_name, comment)

);

===CREATE在已存在的表上创建索引：

语法：CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名

ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

创建普通索引示例：

CREATE INDEX index_dept_name ON department (dept_name);

（创建 索引 索引名 在 表 （列）；）

创建唯一索引示例：

CREATE UNIQUE INDEX index_dept_name ON department (dept_name);

创建全文索引示例：

CREATE FULLTEXT INDEX index_dept_name ON department (dept_name);

创建多列索引示例：

CREATE INDEX index_dept_name_ comment ON department (dept_name, comment);

（创建 索引

索引名

给谁创

表

列

）

===ALTER TABLE在已存在的表上创建索引

语法：

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX

索引名 (字段名[(长度)]  [ASC |DESC]) ;

创建普通索引示例：

ALTER TABLE department ADD INDEX index_dept_name (dept_name);

（修改 表 表1 新建 索引 索引名 （列）；）

创建唯一索引示例：

ALTER TABLE

department

ADD UNIQUE INDEX

index_dept_name

(dept_name);

创建全文索引示例：

ALTER TABLE department ADD FULLTEXT INDEX index_dept_name (dept_name);

创建多列索引示例：

ALTER TABLE department ADD INDEX index_dept_name_comment (dept_name,comment);

五、管理索引

查看索引：SHOW CRETAE TABLE 表名\G

测试示例：

EXPLAIN SELECT * FROM department WHERE dept_name=‘hr’;

（请关注查询记录的数量）

删除索引：

show create table employee6;

  DROP INDEX 索引名 ON 表名;