一、索引的类型及添加

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

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

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

2、唯一索引 (UNIQUE)

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

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

这时, 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');

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

综合使用=>复合索引

二、查询索引

1、desc 表名

desc 表名

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

2、show index(es) from 表名

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

3、show keys from 表名

show keys from 表名;

三、删除索引

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

四、修改索引

先删除，再重新创建

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

这里写图片描述

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

btree 方式检索次数 log2N 次数
这里写图片描述

六、索引的代价

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

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

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

select * from emp where empno = 1
2

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

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
3
4
5
6

这里写图片描述

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

比如：

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

即：在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:通过收集统计信息不可能存在结果