MySql 学习笔记二：索引

一、索引的类型及添加

1、主键索引，主键自动的为主索引 (类型Primary)

当一张表，把某个列设为主键的时候，则该列就是主键索引。
如果你创建表时，没有指定主键索引，也可以在创建表后，再添加, 语句如下:

alter table 表名 add primary key (列名);

2、唯一索引 (UNIQUE)

当表的某列被指定为unique约束时，这列就是一个唯一索引。

CREATE TABLE ddd (    id INT PRIMARY KEY auto_increment,    NAME VARCHAR (32) UNIQUE);

这时, name 列就是一个唯一索引。
在创建表后，再去创建唯一索引：

create unique index 索引名  on 表名 (列表..);

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.
主键字段，不能为NULL,也不能重复.

3、 普通索引 (INDEX)

一般来说，普通索引的创建，是先创建表，然后在创建普通索引。

create index 索引名 on 表 (列1,列名2);

4、全文索引 (FULLTEXT)

[适用于MyISAM]sphinx + 中文分词 coreseek [sphinx 的中文版 ]
全文索引，主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.
如何使用全文索引？
错误用法（不会使用到全文索引）：

select * from articles where body like '%mysql%'; 

证明:

explain  select * from articles where body like '%mysql%'

正确的用法是：

select * from articles where match(body) against('mysql');

1. 在mysql中fulltext 索引只针对 myisam生效
2. mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
3. 使用方法是 match(字段名..) against(‘关键字’)
4. 全文索引一个 叫 停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.

综合使用=>复合索引

二、查询索引

1、desc 表名

desc 表名

该方法的缺点是：　不能够显示索引名

2、show index(es) from 表名

show index from 表名;show indexes from 表名;

3、show keys from 表名

show keys from 表名;

三、删除索引

DROP INDEX index_name ON tbl_name;alter table table_name drop index index_name;

四、修改索引

先删除，再重新创建

五、为什么创建索引后，速度就会变快?

log 2 N 比如N=10 就表示 该算法搜索10次，这10次可以搜索的范围是2的10次方

btree 方式检索 次数 log2N 次数

六、索引的代价

1、占用磁盘空间
2、对dml操作有影响，变慢

七、在哪些列上适合添加索引？

1、较频繁的作为查询条件字段应该创建索引

select * from emp where empno = 1

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件（二叉树没法很好的起作用）。
更新非常频繁的字段不适合创建索引（更改后需要重建索引）。

2、不会出现在WHERE子句中字段不该创建索

3、总结:

满足以下条件的字段，才应该创建索引：
a: 肯定在where条经常使用
b: 该字段的内容不是唯一的几个值(比如：性别)
c: 字段内容不是频繁变化.、

八、使用索引的注意事项

1、对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

// dname 左边的列,loc就是右边的列alter table dept add index my_ind (dname,loc); //不会使用到索引explain select * from dept where loc='aaa';//会使用到索引explain select * from dept where dname='aaa';

2、对于使用like的查询，查询如果是：’%aaa’不会使用到索引’aaa%’ 会使用到索引.

比如：

//不会使用索引explain select * from dept where dname like '%aaa';

即：在like查询时，关键的’关键字’ , 最前面，不能使用 % 或者 _这样的字符， 如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.

3、如果条件中有or，即使其中有条件带索引也不会使用。

换言之，就是要求or所使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字。

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。

否则不使用索引。(添加时,字符串必须”), 也就是，如果列是字符串类型，就一定要用 ” 把他包括起来.

5、如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

九、explain

explain 以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令.

id:SELECT识别符。这是SELECT的查询序列号
select_type:表示查询的类型。
- PRIMARY :子查询中最外层查询
- SUBQUERY : 子查询内层第一个SELECT，结果不依赖于外部查询
- DEPENDENT SUBQUERY:子查询内层第一个SELECT，依赖于外部查询
- UNION :UNION语句中第二个SELECT开始后面所有SELECT，
- SIMPLE
- UNION RESULT UNION 中合并结果
table:显示这一步所访问数据库中表名称
type:对表访问方式
- ALL：完整的表扫描 通常不好
- system：表仅有一行(=系统表)。这是const联接类型的一个特例。
- const：表最多有一个匹配行
Possible_keys：该查询可以利用的索引，如果没有任何索引显示 null
key:Mysql 从 Possible_keys 所选择使用索引，表示实际使用的索引
Rows:估算出结果集行数
Extra:执行情况的描述和说明即查询细节信息
- No tables :Query语句中使用FROM DUAL 或不含任何FROM子句
- Using filesort :当Query中包含 ORDER BY 操作，而且无法利用索引完成排序
- Using temporary:某些操作必须使用临时表，常见 GROUP BY ; ORDER BY
- Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据
- Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer:通过收集统计信息不可能存在结果

十、索引的使用

查看索引的使用情况：


show status like 'Handler_read%';

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。

本文部分内容整理自itcast讲义，在此表示感谢。
作者：jiankunking 出处：http://blog.csdn.net/jiankunking