MySQL索引的理解和应用

一、索引的概述

索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引， 迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。由于引擎的关系，MyISAM 表是将数据行存放在数据文件里，索引值存放在索引文件里。索引就是一个排好序的键值对数组，查询时通过这种键值对快速找到对应的数据。对于InnoDB 表来说，它的索引也是排好序的数组，但它的数据行与索引值存放在同一个文件里。

二、索引种类

从索引的定义方式和用途中来看，所以一共有四种索引：
1.普通索引(Index)，用于提升查询效率；
2.唯一索引(Unique)，除了提升查询效率还要求字段值不得重复；
3.主键索引(Primary Key)，唯一性且不得为空的索引；
4.全文索引(Fulltext)，用于在大量文本搜索中建立的索引。

这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引， 但唯一索引不一定是主键索引。

三、索引应用

//1.添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
2.添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( 
`column` 
) 
//3.添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
//4.添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
//5.添加多列索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

四、索引原则

1、索引需要使用更多的磁盘空间，索引越多，占用磁盘越多，有时甚至比表占用的多；
2、索引在提升查询速度的同时，降低了插入、更新和删除的操作速度，因为它们多了一项工作，就是在增删改的过程中更新索引。索引越多，速度越慢。
3、只对 WHERE 子句中频繁使用的建立索引；
4、尽可能使用唯一索引，重复值越少，索引效果越强；
5、使用短索引，如果 char(255)太大，应该给它指定一个前缀长度，大部分情况下前10 位或 20 位值基本是唯一的，那么就不要对整个列进行索引；
6、充分利用左前缀，这是针对复合索引，因为 WHERE 语句如果有 AND 并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将 WHERE最频繁的放置在左边。