数据库操作之——索引

一 索引的概念

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

 

二 B-tree

　　B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检 索中有非常优异的表现。 
      一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree ，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个 
Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。 

 

三 　索引的具体介绍

　　mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

　　单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

　　组合索引:一个组合索引包含两个或两个以上的列。

　　1 索引的创建

　　1）单列索引

　　1-1 普通索引，这是最基本的索引

　　方法一

mysql> create index in_id on s1(id);
Query OK, 0 rows affected (0.82 sec)

　　方法二

mysql> alter table s1 add index in_id (id);
Query OK, 0 rows affected (0.79 sec)

　　1-2 唯一索引

　　创建方法与普通索引类似

　　方法一

mysql> create unique index uni_id on s1(id);
Query OK, 0 rows affected (0.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　方法二

mysql> alter table s1 add unique index uni_id (id);
Query OK, 0 rows affected (0.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　1-3 主键索引

　　主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

　　主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的。

 

　　2）组合索引

　　一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称，期格式是CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...)。　　

 

　　使用多列所需需要按照最左索引列查找；不能跳过中间列（跳过就会将遍历被跳过的索引字段，会浪费大量时间）；如果某一列是范围查询，那么其右边所有列无法使用索引。

 

　　列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

　　示例：

mysql> create  index mul_index on s1(id,name,email);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

　　如示例所示创建了一个组合索引（id,name,email），实际上是创建了一下这几种索引。

　　（id），（id，name，），（id，email），（id，name，email）。

mysql> select count(1) from s1 where name='egon' and email = 'egon160000@oldboy';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.08 sec)

mysql> select count(1) from s1 where id =830 and email = 'egon160000@oldboy';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

　　演示结果，带有id的查询命令会快很多。

 

　　重点：最左前缀原则。

　　所谓最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列，以此类推。有位网友描述得很形象： 你可以认为联合索引是闯关游戏的设计例如你这个联合索引是state/city/zipCode那么state就是第一关 city是第二关， zipCode就是第三关你必须匹配了第一关，才能匹配第二关，匹配了第一关和第二关，才能匹配第三关你不能直接到第二关的索引的格式就是第一层是state，第二层才是city索引是因为B+树结构 所以查找快 如果单看第三列 是非排序的。 

　　多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。 
　　所以如果不是在前面列的基础上而是但看后面某一列，索引是失效的。

　　示例：

　　　　

 

　　

 

二 索引的删除

　　不管是普通索引还是唯一索引，固定格式

　　DORP INDEX IndexName ON `TableName`

mysql> drop index mul_index on s1;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0