MySql之索引

通常小型项目涉及数据量比较小，数据查询频率不高，索引通常会被忽略。但当数据量较大，涉及多个表，查询较为频繁时，我们需要对查询性能进行优化，此时，建立合适的索引确是主要的方法之一。

通常，mysql数据库查询有两种方式：

一种是：全表扫描；

另外一种是：利用数据表中建立的索引进行扫描。

索引可以提高查询效率，下面我们来举例说明如何做到的。

例如：有三个表，table1，table2， table3，分别只含有一列c1、c2、c3，每个表中分别含有N行记录，现要查询对应值相等的行。

SQL查询语句如下：

SELECT c1,c2,c3 FROM table1，table2， table3 WHERE c1=c2 AND c1=c3；

不建索引时，需要从遍历table1、table2、table3中的所有记录组合，查询次数为：N*N*N，如下图。

 

如果对三个表都建索引，那么从table1中的第一行记录中的c1的值开始，如值为value，通过table2的索引表，直接通过value的指针找到value在table2中的记录，然后同理通过table3的索引表，通过value的指针找到value在table3中的记录；接着继续查询table1中的下一行记录，重复上面的步骤。最终扫描次数为：N。如下图所示。

 由以上可以看出，建立索引查询效率大大提高。

 常用索引类型:

主键索引：系统自动创建的，唯一，并且列值不能为空。创建使用关键字，PRIMARY KEY;

唯一索引：索引列值必须唯一，不能重复，但可以为空值。创建使用关键字，UNIQUE;

普通索引：索引列值可以重复，可以取空值。创建使用关键字，INDEX或者KEY;

聚簇索引：就是数据存储的物理存储顺序，非聚簇索引就是索引顺序与数据的物理顺序无关。一个表只能有一个聚簇索引。目前只有InoDB和solidDB支持。

全文索引：只能创建在varchar或text的列上；建立全文索引能够在全文索引的列上进行查找。

（1）单列索引：一个索引只包含表中的一个列；比创建一个学号ID的索引；以name再创建一个姓名的单列索引。即每个索引包含一个列。

（2）组合索引（复合索引或多列索引）：就是表中的两个列或多个列来创建成一个索引；比如；以用户ID、用户名Name、用户年龄Age来创建的索引就是联合索引。

索引SQL语句

# 创建索引

mysql> create index index_sname on student(sname);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

# 查询表索引

mysql> show index from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY     |            1 | sid         | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| student |          1 | FK_CID      |            1 | cid         | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| student |          1 | index_sname |            1 | sname       | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

# 删除索引

mysql> drop index index_sname on student;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | sid         | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| student |          1 | FK_CID   |            1 | cid         | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set