MySQL之索引

索引是存储引擎用于快速查找记录的一种数据结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

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

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。遍历索引找到主键后，通过回表的方式，使用主键在主表中找到对应数据。

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

总的来说，索引的建立使查询非常迅速。但索引仍有它的缺点。一是索引本身占据存储空间，二是索引需要维护。所以索引不能随意建立，只需在经常使用的字段上建立。

普通索引

创建索引

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

CREATE INDEX indexName ON mytable(username); 

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

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

删除索引的语法

DROP INDEX [indexName] ON mytable; 

---

唯一索引

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

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username) 

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username)

创建表的时候直接指定

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

---

MySQL索引优化策略

1、索引列上不能使用表达式或者函数

例子：select … from product where to_days(out_date) - to_days(current_data)<=30 to_days就是使用了函数，out_date就是索引列。

优化后：select … from product where out_date<=data_add(current_data,interval 30 day) 这样对out_date索引列就没有使用函数。

2、联合索引，如何选择索引的顺序

• 经常会被使用到的列优先
• 选择性高的列优先
• 宽度小的列优先

3、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4、like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 会使索引失效而like “aaa%”可以使用索引。

5、索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。