mysql索引创建及使用

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

普通索引

1、创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

• indexName :索引名
• username：列名

表如下：

CREATE INDEX indexName ON mytable(username(length));

eg: create INDEX index_name on student(name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2、修改表结构(添加索引)

• tableName ：表名
• indexName：索引名
• columnName：列名

ALTER table tableName ADD INDEX indexName(columnName)

eg： ALTER table student ADD INDEX index_name(name)

3、创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

4、删除索引的语法

DROP INDEX [indexName] ON mytable;

eg： DROP index index_name on student;

5、查看索引

show index from 表名;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
1、创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

2、修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

3、创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

接下来就来分析有无索引的区别了

EXPLAIN详解

下面看一个例子：
1、先查看表中有无索引：

这时候是没有索引的。从student表中可以看出有5条数据。
我们来进行普通where查询：

从上图中红色框圈住rows字段可以看出检查了5条数据

type:表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如下(从上到下，性能由差到好)

ALL index range ref eq_ref const,system NULL

ALL: 全表扫描

index: 索引全扫描

range:索引范围扫描

ref:使用非唯一索引扫描

eq_ref:使用唯一索引扫描

const，system:单表中最多只有一个匹配行

可理解为const是最优化的。

以上:通过explain分析type是ALL 是性能最差的一种 以下:开始优化。

2、我们创建索引，并查看索引，如下：

红色框内就是我们创建的索引,这时候我们来查询下，看看效率如何？

我们可以清楚的看到，type是ref，rows是1（只检查了1条数据），由此可见，有无索引的区别，百万数据的话，效率更明显。

使用索引查询需要注意

索引可以提供查询的速度，但并不是使用了带有索引的字段查询都会生效，有些情况下是不生效的，需要注意！

1、使用LIKE关键字的查询

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。
如下：

2、使用or关键字的查询

查询语句的查询条件中只有or关键字，且or前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

略。。。这两天完善

3、子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询虽然很灵活，但是执行效率并不高。
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

优化：

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。