索引

索引就是给数据加目录

一、索引优缺点

优点: 加快查询速度, 特别是数据量非常大时

缺点: 占空间, 增大了数据文件大小, 增删改数据时, 索引也要跟着更新且排序, 维护数据的速度降低了

二、索引类型

1.  B-tree  （B是balance）

B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。

 MyIsam、innodb存储引擎的表默认支持B-tree索引

2.  hash索引

hash索引检索效率非常高，索引的检索可以一次定位；

但由于hash索引自身的缺陷，比如它仅仅能满足“=”、“IN”、“<=>”查询，不能使用范围查询；而且它无法被用来避免数据的排序操作；不能利用部分索引键查询；在任何时候都不能避免扫描；并且遇到大量Hash值相等的情况后性能并不一定不B-tree索引高。故而一般情况下B-tree索引要比hash索引使用频繁些。

Memory存储引擎的表默认支持hash

三、索引分类

1、唯一索引

主键是一种特殊的唯一索引

unique(id), unique index(id), unique index ix1(id asc),

create table d1(f1 int unique)也可, unique约束, 即unique索引

2、全文索引

myisam支持

5.6的innodb也支持

fulltext index ix1(contents)

3、多列索引

index ix1(name, sex)

where name='tom'\G

where sex='m'\G

四、建索引原则

order by字段

group by字段

where 字段

过于集中的值(唯一性太差)不要索引, 如给性别'男''女'加索引, 没用；

数据量太小不建索引, 数据量大就建；

删除不再使用或很少使用的索引。

五、索引用法

1、大批量插入数据前先禁用索引(仅对MyIsam)

alter table d1 disable keys;        #禁用非唯一索引

load data;

alter table d1 enable keys;

2、Btree或hash(存储类型|搜索算法)的创建

create index ix1 using hash on d1(id);

create index ix1 using btree on d1(id);

3、通过修改表来创建索引:

alter table d1 add index ix1(name(20));

alter table d1 add unique index ix1(id);

alter table d1 add fulltext index ix1(name);

alter table d1 add index ix1(name,address);

4、删除索引:

drop index ix1 on d1;

六、单表/多表索引

1.1  单表索引

mysql> insert stu select * from stu;     \\多次重复可以成倍的增加数据内容

mysql> alter table stu add index ix1(id,name);       \\这里id在前面设置的索引，从下面的对比来看，id的索引起作用了，而name的索引是不起效果的

mysql> explain select * from stu where name='a' ;

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

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

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

|  1| SIMPLE      | stu   | ALL | NULL          | NULL | NULL    | NULL | 20357 | Using where |

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

1 row in set (0.00 sec)

 

mysql> explain select * from stu where id=1 ;

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

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

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

|  1| SIMPLE      | stu   | ref | ix1           | ix1  | 5      | const | 4095 | NULL  |

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

 

mysql> explain select * from stu where name='a' and id=1;

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

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

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

|  1| SIMPLE      | stu   | ref | ix1           | ix1  | 104    | const,const |    1 | Using indexcondition |

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

1 row in set (0.01 sec)

 

mysql> explain select * from stu where id=4 and name='a' ;         \\另附上：表中的id=4直接对应name='a'

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

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

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

|  1| SIMPLE      | stu   | ref | ix1           | ix1  | 104    | const,const | 4095 | Using index condition |

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

 

2.2多表索引     ---》哪张表的数据量大就会使用索引来查询

【在所查询的条件中都要设置索引】

如:以下三表的数据都是很大的，但最少数据的key是没有索引