1.索引概述

索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

2.索引分类

普通索引INDEX: 最基本的索引，没有任何限制
唯一索引UNIQUE: 与”普通索引”类似，不同的是索引列的值必须唯一，但允许有空值。
全文索引FULLTEXT：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
主键索引PRIMARY KEY:它 是一种特殊的唯一索引，不允许有空值。

3.索引环境

1.准备表

mysql> create table t5 (id int, name varchar(30));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.使用存储过程(函数), 批量插入数据
1.创建存储过程

mysql> delimiter $$
mysql> create procedure autoinsert()
BEGIN
declare i int default 1;
while (i<200000)do
insert into bgx.t5 values(i,'bgx');
set i = i+1;
end while;
END $$
mysql> delimiter ;

2.查看存储过程

mysql> show procedure status\G
mysql> show create procedure autoinsert\G
*************************** 1. row ***************************
           Procedure: autoinsert
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`()
BEGIN
declare i int default 1;
while (i<200000)do
insert into bgx.t5 values(i,'bgx');
set i = i+1;
end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

3.调用存储过程,执行

mysql> call autoinsert();

4.索引创建

1.创建表时创建索引
语法：CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]));

1.创建普通索引示例
CREATE TABLE tt ( id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
'INDEX' index_tt_name (name) );

2.创建唯一索引示例
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
'UNIQUE INDEX' index_tt_name (name) );

3.创建全文索引示例myisam
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
log text,
'FULLTEXT INDEX' index_tt_log (log);

4.创建多列索引示例
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
'INDEX' index_tt_name_comment (name, comment));

2.在已存在的表上创建索引
语法:CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]);

1.创建普通索引示例
CREATE 'INDEX' index_name ON product(name);

2.创建唯一索引示例
CREATE 'UNIQUE INDEX' index_name ON product(name);

3.创建全文索引示例
CREATE 'FULLTEXT INDEX' index_dept_name ON product (name);

4.创建多列索引示例
CREATE 'INDEX index_dept_name_comment' ON product (name, id);

5.索引测试

1.未建立索引

花费时长
mysql> select * from t5 where id=199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
1 row in set (0.08 sec)

explain查看查询优化器如何决定执行查询
mysql> explain select * from t5 where id=199999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 199949
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

2.建立索引

对id字段进行索引创建
mysql> create index index_t5_id on bgx.t5(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引后花费时长
mysql> select * from t5 where id=199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
1 row in set (0.00 sec)

建立索引后, 再次查看查询优化器如何执行查询
mysql> explain select * from t5 where id=200000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
   partitions: NULL
         type: ref
possible_keys: index_t5_id
          key: index_t5_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

6.索引管理

1.查看索引

SHOW CRETAE TABLE 表名\G
EXPLAIN SELECT * FROM t5 WHERE id='19999';

2.删除索引

查看索引名称
mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `index_t5_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

删除索引语法: DROP INDEX 索引名 ON 表名
mysql> drop index index_t5_id on t5;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0