mysql索引

何为索引，

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。如图：

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，由于索引直接指向数据，那么查找数据的时间复杂度就等于索引表的时间复杂度

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

(一)创建索引

1、首先新建员工表和部门表，并加载数据，新建mysql.sql，如下：

create table emp(
empno int,
ename varchar(20),
job varchar(20),
mgr int,
hiredate varchar(20),
sal double,
comm double,
deptno int
);

create table dept(
deptno int,
dname varchar(20),
loc varchar(20)
);

load data local infile "/opt/datas/hive/emp.txt" into table emp; 

load data local infile "/opt/datas/hive/dept.txt" into table dept; 

2、创建单列索引

create index index_sal on emp(sal); 或使用 alter table emp add index index_sal(sal);

3、检索语句的时候使用explain查看索引是不是生效：

explain select * from emp where sal=1400;显示结果如下：

+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | ref  | index_sal     | index_sal | 9       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------

其中key的值为index_sal，说明索引生效，

使用范围查询：explain select * from emp where sal > 1400;

结果如下：

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | index_sal     | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------

可以看到key为空，说明使用范围查询时索引没有生效

4、创建组合索引，即索引包含多个字段

create index combine_index on emp(deptno,hiredate,sal);  如果创建组合索引，则实际上包含了4个索引(deptno),(deptno,hiredate),(deptno,sal),(deptno,hiredate,sal)

使用explain查看索引是否生效：

 explain select * from emp where deptno=20 and sal>1100 and hiredate=1981-4-2; 如下所示：

 | possible_keys | key           |     
+----+-------------+-------+------+
 | combine_index | combine_index

说明索引创建成功，我们把第一个字段进行范围查询试一下：

explain select * from emp where deptno>10 and sal>1100 and hiredate=1981-4-2;显示如下：

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | combine_index | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

说明第一个字段不能使用范围查询。组合索引遵循的原则如下：

在使用组合索引查询时，第一个索引列必须存在。

a、没有加索引最左列开始查询的， 例如index(‘c1’, ‘c2’, ‘c3’) 。那么where ‘c2’ = ‘aaa’ 不能使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引，而where `c2` = `aaa` and `c1`=`sss`则会生效，也就是说使用c2或c3索引列的时候必须同时加上c1索引列，索引才会生效。

b、查询中某个列有范围查询，则其右边的所有列都无法使用索引（多列查询），这个是根据B树的机制（得深入了解一下）

假设有连个组合索引，index1(c1,c2,c3)，index2(c1,c4)，使用如下查询条件

select * from table Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ ，此时只有c1索引列生效，且不会使用index1而会使用index2。因为凡是索引列使用范围查询，该索引列和后边的索引列失效，而index2更加简便，故会使用index2。

(二)删除索引

DORP INDEX IndexName ON TableName

(三)使用索引的优点

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件 可以加速表与表直接的相连 
4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引 可以提高性能

 

(四)使用索引的缺点

1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

(五)使用索引需要注意的地方

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
一般来说,
1.在经常需要搜索的列上,可以加快索引的速度
2.主键列上可以确保列的唯一性
3.在表与表的而连接条件上加上索引,可以加快连接查询的速度
4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,  (单独order by 用不了索引，索引考虑加where 或加limit)
5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)

6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick '%ABC%' 那么这个索引讲不会起到作用.而nickname lick 'ABC%' 那么将可以用到索引

7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串

8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.

9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描

10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

(六)什么情况下不创建索引

1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系