mysql的索引

什么是索引

  索引是创建在表上的，是对数据表中一列或多列值进行排序的一种结构。索引能提高数据查询的速度。通过索引，查询数据时可以不必读完记录的所有信息而只是查询索引列。
  索引有两种存储类型，包括B型树（BTREE）和哈希（HASH）索引。InnoDB和MyISAM存储引擎都支持BTREE索引，MEMORY存储引擎支持HASH和BTREE索引，默认为前者。
  在需要经常使用ORDER BY 、GROUP BY、DISTINCT和UNION等操作的字段，排序字段会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。索引的缺点是创建和维护索引需要耗费时间，耗费时间随着数据量的增加而增加，索引需要占用物理空间。而且向有索引的表中插入记录时，会降低插入数据的速度。因为数据库系统会按照索引进行排序。

索引的分类

1. 普通索引
   在创建表的时候，加入以下sql语句。
   INDEX 别名 (key [长度] [ASC|DESC])
2. 唯一性索引
   在创建该索引时，限制该索引的值必须是唯一值。主键就是一种特殊的唯一索引
   UNIQUE INDEX 别名 (key [长度] [ASC|DESC])
3. 全文索引
   全文索引只能创建在CHAR、VARCHA或TEXT类型的字段上。在查询数据量较大的字符串类型时，使用该索引能提高查询速度。5.6的MySQL版本的InnoDB支持全文索引。
   FULLTEXT INDEX 别名 (key [长度] [ASC|DESC])
4. 单列索引
5. 多列（联合）索引
   在一个表的多个字段上建立一个索引。
   例如：INDEX (A,B,C)
   只有使用了索引的第一个字段时，索引才会生效。
6. 空间索引
   使用SPTIAL参数可以设置索引为空间索引。一般很少用到这种索引。

索引操作

1. 在未存在索引的表中添加索引
   CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 别名 ON 表名 (key [长度] [ASC|DESC])

   ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 别名 ON 表名 (key [长度] [ASC|DESC])

2. 删除索引
   DROP INDEX 索引名 ON 表名

关于查询中的索引使用

可以在查询语句的前面加上EXPLAIN查看语句的执行情况来判读索引是否被使用。

创建如下的测试数据表

CREATE TABLE test(
    a int,
    b int, 
    c int,
    d int,
    index (a),
    index (b,c,d),
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后在表里插入测试数据
INSERT INTO test (a, b, c, d) VALUES (1, 2, 3, 4),(11, 2, 3, 4),(1, 22, 3, 4),(1, 2, 33, 4),(1, 2, 3, 44);
当使用explain select * from test where a = 1;可以看到该sql语句使用到了索引。
当使用explain select * from test where b=22;可以看到该sql语句使用到了索引。
当时用explain select * from test where b=22 and c=3;也是用到了索引。
但是用到explain select * from test where b=2 or c=3;时并没有用到索引。
联合索引如果要使用的话，查询语句必须要包含前面的索引字段，否则并不能用到索引，如expalin select * from test where c=33;就没办法用到索引，因为这个联合索引的顺序是b,c,d。要用c的话就要包含b。