索引相关

一、索引分类

1.单列索引：一个索引包含一列，一个表可以包含多个单列索引；分为：
[list]
[*]普通索引
[*]主键索引
[*]唯一索引：与主键索引相似，区别：可以为NULL，主键索引不可以
[/list]
2.组合索引：一个组合索引包含两个或两个以上的列

二、索引创建、删除

1.创建索引

-- 索引类型：primary key 主键索引、UNIQUE INDEX 唯一索引、INDEX 普通索引、组合索引；
-- 索引名称：见名知意，不易过长；唯一索引 unique_index_column_name 、组合索引 a_b_c_d 依次列出字段名称 union_index ; 普通索引 index_(idx_简写为)

-- 创建索引
create INDEX index_name on table_name(column_name);
CREATE INDEX account_Index ON `award`(`account`); -- 注意字段上的符号
alter table table_name add INDEX index_name(column_name);
ALTER TABLE award ADD INDEX account_Index(`account`);

2.删除索引

DROP INDEX IndexName ON `TableName`；

3.查看索引

show INDEX from `TableName`；

三、存储引擎：InnoDB，基于BTree索引，最左匹配原则，以组合索引为例：

1.建表

show databases ; -- 显示数据库

use test ; -- 使用 test 数据库

show tables ; -- 显示数据库中的数据表

 CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

desc award ; -- 查看表结构

show columns from award ; -- 查看表结构

alter table award add index nickname_account_create_time(nickname,account,created_time); -- 添加索引，组合索引

2.最左匹配原则， a_b_c_index(a,b,c) 匹配 a , b , abc 三种索引，组合查询时查询条件应与索引顺序大致相同

MySQL有查询优化器，会自动将查询条件按照索引的顺序进行执行；但最好是按照索引的顺序排列查询条件，便于使用查询缓存；[url=http://blog.csdn.net/czhphp/article/details/45222253]mysql查询缓存配置和查看[/url]
且SQL查询中保持大小写一致，不要既有大写又有小写的

-- 查询时使用到索引

-- 匹配a
explain select * from award where nickname = "1" ;

[img]http://dl2.iteye.com/upload/attachment/0127/6660/4f10d13d-c5f6-34d1-a6e8-5955243ea096.png[/img]

-- 匹配ab
explain select * from award where nickname = "1" and account = "2" ;

[img]http://dl2.iteye.com/upload/attachment/0127/6662/294adac9-7035-3c01-af3a-c659a7c6476a.png[/img]

-- 匹配abc
explain select * from award where nickname = "1" and account = "2" and created_time = "2017-10-01";

[img]http://dl2.iteye.com/upload/attachment/0127/6664/4ba42275-b1e3-3e43-92a8-1a51c390cdb7.png[/img]

-- 查询时使用不到索引

-- 最左匹配原则，虽然组合索引中包含account 字段，但并不匹配它
explain select * from award where account = "2" ;

[img]http://dl2.iteye.com/upload/attachment/0127/6666/382df99c-d107-372c-9b8d-15060695914b.png[/img]

3.范围查询 like

-- 可以使用
explain select * from award where nickname like "1" ;
-- 不可以
explain select * from award where nickname like "%1%" ; -- "%1" 同样
-- 可以
explain select * from award where nickname like "1%" ;
-- 最左匹配，最左侧不可以为模糊的数据

[img]http://dl2.iteye.com/upload/attachment/0127/6670/2c7523c7-ce65-37bc-9abb-9a4823e44fdd.png[/img]

4.索引范围查询与位置有关，若最左侧的字段使用范围查询将导致组合索引中后续的字段在索引中失效；后面的字段同样，第二个字段按照范围查询，则第三个至最后一个的字段索引失效

[img]http://dl2.iteye.com/upload/attachment/0127/6672/6f3a0715-8b73-3318-a091-6bda117f406d.png[/img]

根据范围进行查询，索引依然可以用到，但通过ref 为null 可知，查询效率要低好多
[img]http://dl2.iteye.com/upload/attachment/0127/6723/ab7cdb4a-6b90-340a-8fb7-b69b6d797b86.png[/img]

另外，>= 与 > 在使用上有区别，前者可用到全部的索引字段，后者只能用到当前字段左侧的所有字段

[img]http://dl2.iteye.com/upload/attachment/0127/6813/683f1804-0c4a-36bc-94a5-ac127e47f634.png[/img]

5.单独使用order by 索引，不起作用；必须配合 where 条件共同使用
a.未使用到索引的情况
[img]http://dl2.iteye.com/upload/attachment/0127/6674/f6c66060-6704-33f6-91b4-125239f73ad3.png[/img]
b.使用了索引的情况
[img]http://dl2.iteye.com/upload/attachment/0127/6680/34564b07-a133-38b7-9085-aa7b497b5526.png[/img]

6.


四、explain

1.如何判断是否使用到了索引？
type = all 全表检索
possible_keys 可能使用到的索引
key 使用到的索引
key_len 索引长度

2.如何判断使用到的索引的字段个数？key_len

key_len长度的计算
[list]
[*]所有的索引字段，如果没有设置not null，则需要加一个字节。
[*]定长字段，int占四个字节、date占三个字节、char(n)占n个字符。
[*]对于变成字段varchar(n)，则有n个字符+两个字节。
[*]不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。
[/list]

-- 三个索引字段
`nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
`account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',     
`created_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',     
-- 按照上述的计算方式：
-- 使用了a索引：key_len = 12 * 3 + 2 = 38 
-- 使用了ab索引：key_len = 24 * 3 + 2*2 = 76 
-- 使用了abc索引：key_len = 24*3 + 2*2 + 4 = 80 

博文参考：
[url=https://www.2cto.com/database/201610/555295.html]MySQL中计算索引长度[/url]

五、索引优点
1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件 可以加速表与表直接的相连
4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引 可以提高性能

六、索引缺点
1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。
（在某个字段建立索引后，会开辟一段物理空间存放该字段中的元素按照BTREE的数据结构，当数据新增、更新、删除后， BTree结构 需要重新变更，故后期维护需要一定的成本）

七、注意事项
1.在经常需要搜索的列上,可以加快索引的速度
-- 开发过程中将使用的SQL依次列出，然后按照查询条件共同使用的字段，添加索引

2.主键列上可以确保列的唯一性
-- 主键，若为int类型，需指定 unsigned 无符号， primary key auto_increment 自增

3.在表与表的而连接条件上加上索引,可以加快连接查询的速度

4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)
-- 多表关联查询，当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。


5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
-- >= 与 > 有一定区别，组合索引中，最左字段使用>= 依然可以使用整个索引；但 > 将导致其右侧字段索引失效

6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick '%ABC%' 那么这个索引讲不会起到作用.而nickname lick 'ABC%' 那么将可以用到索引

7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串
-- 索引不包含NULL，指的是该列的内容中不可以包含NULL，字段本身可以为NULL，计算key_len时为NULL的字段需要加一个字节

8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.

9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描

10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

八、不使用索引
1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.

2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
-- 即区分度不高的字段不使用索引

3.定义为text和image和bit数据类型的列不应该增加索引,

4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

博文转载：
[url=http://www.cnblogs.com/chenshishuo/p/5030029.html]细说mysql索引[/url]

博文参考：
[url=http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html]理解MySQL——索引与优化[/url]