mysql基础(37)_索引

索引含义

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询速度。

索引分类

1、普通索引
2、唯一性索引
3、全文索引
4、单列索引
5、多列索引
6、空间索引

索引设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
1、选择唯一性索引
2、为经常需要排序、分组和联合操作的字段建立索引
3、为常作为查询条件的字段建立索引
4、限制索引的数目
5、尽量使用数据少的索引
6、尽量使用前缀来索引
7、删除不再使用或者很少使用的索引

创建索引

1、创建表的时候创建索引
create table 表名 （
                                   属性名 数据类型 [完整性的约束条件]，
                                   属性名 数据类型 [完整性的约束条件]，                        
                                   ......
                                   属性名 数据类型
                                   [unique|fulltext|spatial] index|key [别名] (属性名1 [(长度)] [asc|desc])
                             );

2、在已经存在的表上创建索引
create [unique|fulltext|spatial] index 索引名 on 表名 (属性名 [(长度)] [asc|desc]);

3、用alter table语句来创建索引
alter table 表名 add [unique|fulltext|spatial] index 索引名(属性名 [(长度)] [asc|desc]);

删除索引

对于已经存在的索引，可以通过drop语句来删除索引。基本形式如下：
drop index 索引名 on 表名;

mysql> create table index1(
    -> id int,
    -> name varchar(20),
    -> sex boolean,
    -> index (id)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into index1 values(1,'张三',1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into index1 values(2,'李四',0);
Query OK, 1 row affected, 1 warning (0.31 sec)

mysql> explain select * from index1 where id=2;
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | index1 | ref  | id            | id   | 5       | const |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.10 sec)
-- 唯一索引
mysql> create table index2(
    -> id int unique key,
    -> name varchar(20),
    -> unique index index_2(id)
    -> );
Query OK, 0 rows affected (0.38 sec)
-- 全文索引，mysql5.6中innodb也开始支持全文索引了，因此存储类型可以自行选择
mysql> create table index_3(
    -> id int,
    -> info varchar(20),
    -> fulltext index index3_info(info)
    -> )engine=myisam;
Query OK, 0 rows affected (0.15 sec)
-- 单列索引
mysql> create table index_4(
    -> id int,
    -> subject varchar(30),
    -> index index4_st(subject(10))
    -> );
Query OK, 0 rows affected (0.15 sec)
-- 多列索引
mysql> create table index_5(
    -> id int,
    -> name varchar(20),
    -> sex char(4),
    -> index index5_ns(name,sex)
    -> );
Query OK, 0 rows affected (0.14 sec)
-- 空间索引，必须非空约束，space该类型似乎是空间数据类型，用到的地方不多。
mysql> create table index_6(
    -> id int,
    -> space geometry not null,
    -> spatial index index6_sp(space)
    -> )engine=myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> drop index id on index1;
Query OK, 2 rows affected (0.18 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index index1_id on index1(id);
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop index id on index1;
ERROR 1091 (42000): Can't DROP 'id'; check that column/key exists
mysql> drop index index1_id on index1;
Query OK, 2 rows affected (0.42 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table index1 add index index1_id(id);
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

备注：
id:　SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现。在次语句中，select就只有一个，所以是1.
select_type:所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不实用UNION或子查询，就为简单的SELECT。也就是说在该SELECT查询时会使用索引。其他取值:
　　　　　　  PRIMARY：最外面的SELECT.在拥有子查询时，就会出现两个以上的SELECT。
                     UNION：union(两张表连接)中的第二个或后面的select语句  SUBQUERY：在子查询中，第二SELECT。
table：数据表的名字。
type：type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。
possible_keys：MySQL在搜索数据记录时可以选用的各个索引.
key：实际选用的索引
key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当 key 字段的值为 null时，索引的长度就是null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。这里就使用了1个索引，所以为1，
ref:给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的关联的id=2，就是常量。
rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
extra：提供了与关联操作有关的信息，没有则什么都不写。

索引的特点

索引优势：
1、可以提高检索数据的速度，这是创建索引最主要的原因。
2、对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度。
3、使用分组和排序子句进行数据查询时，可以显著节省查询中分组和排序的时间。

索引缺点：
1、创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加。
2、索引需要占用物理空间，每一个索引要占一定的物理空间。
3、增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

解决办法：
索引可以提高查询的速度，但是会影响插入记录的速度，因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序。这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据。插入完成后，再创建索引。