mysql 索引

部分来源：

1. mysql索引总结----mysql 索引类型以及创建 该来源，部分命令运行失败，故在本篇文章中进行了修改。所有命令，都已经进行过运行测试。
2. mysql 添加索引 mysql 如何创建索引
3. MySQL索引原理及慢查询优化

索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
假设要从10万条查询中查询某个数据，在没有索引的情况下，数据库会遍历全部10万条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。

索引分类及命令

以数据库data_index，表格test_index，字段name为例

1. 普通索引
   最基本的索引，它没有任何限制，索引类型为Normal,索引方法为BTREE。

    直接创建普通索引
    CREATE INDEX index_name ON test_index(name);
    修改表结构的方式添加普通索引
    ALTER TABLE test_index ADD INDEX index_name(name);
    创建表的时候同时创建普通索引
    CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title)
    );
    删除索引
    DROP INDEX index_name ON table_name;
   

2. 唯一索引
   与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。
   如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似,只是添加关键字UNIQUE。
   索引类型为Unique,索引方法为BTREE。

    直接创建唯一索引 
    CREATE UNIQUE INDEX index_name ON test_index(name);
    修改表结构的方式添加唯一索引 
    ALTER TABLE test_index ADD UNIQUE INDEX index_name(name);
    创建表的时候同时创建唯一索引
    CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE INDEX index_name (title)
    );
    删除索引
    DROP INDEX index_name ON table_name;
   

3. 全文索引（FULLTEXT）
   MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；
   他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。
   对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。
   不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
   索引类型为Full Text,索引方法为空。

    直接创建全文索引
    CREATE FULLTEXT INDEX index_name ON table_name_full(title);
    修改表结构的方式添加全文索引 
    ALTER TABLE table_name_full ADD FULLTEXT INDEX index_name(title);
    创建表的时候同时创建全文索引
    CREATE TABLE `table_name_full` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT INDEX index_name (title)
    );
    删除索引
    DROP INDEX index_name ON table_name;
   

4. 单列索引、多列索引
   多个单列索引与单个多列索引的查询效果不同
   因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

5. 组合索引（最左前缀）
   平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。
   现在对表格test_index删除所有索引，并且添加组合索引

    ALTER TABLE test_index ADD INDEX index_name_desp(name,desp);
   

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
name,desp
name
为什么没有desp这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引。
示例如下：

	mysql语句：
	select * from test_index where name = "cuiyf1465";
	分析结果：
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
	1	SIMPLE	test_index		ref	index_name_desp	index_name_desp	768	const	1	100	
	其中，key值为index_name_desp 表示使用到了索引

	mysql语句：
	select * from test_index where desp = "handsome092";
	分析结果：
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
	1	SIMPLE	test_index		ALL					96903	10	Using where
	其中，key值为空 表示未使用到索引
	
	mysql语句：
	select * from test_index where name = "cuiyf034" and desp = "handsome092";
	分析结果：
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
	1	SIMPLE	test_index		const	index_name_desp	index_name_desp	1536	const,const	1	100	
	其中，key值为index_name_desp 表示使用到了索引	

注意事项：

1. 索引不会包含有NULL值的列
   只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2. like语句操作
   一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
3. 不要在列上进行运算
   例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，
   因此我们可以改成：select * from users where adddate<’2007-01-01′。
4. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。
5. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
6. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

查询优化神器 – explain命令

官网介绍：EXPLAIN Output Format

这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外）。所以优化语句基本上都是在优化rows。

	Column	Meaning  
	id	The SELECT identifier  
	select\_type	The SELECT type  
	table	The table for the output row  
	partitions	The matching partitions  
	type	The join type  
	possible\_keys	The possible indexes to choose  
	key	The index actually chosen  
	key_len	The length of the chosen key  
	ref	The columns compared to the index  
	rows	Estimate of rows to be examined  
	filtered	Percentage of rows filtered by table condition  
	Extra	Additional information  

慢查询优化基本步骤

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4. order by limit 形式的sql语句让排序的表优先查
5. 了解业务方使用场景
6. 加索引时参照建索引的几大原则
7. 观察结果，不符合预期继续从1分析

具体优化实例参见链接:http://blog.jobbole.com/86594/