mysql---索引及explain的作用

索引：是一种数据结构，以增加存储开销和减慢DML(增、删、改)操作来提高查询速度。

常见的索引结构:btree索引（myisam，innodb，memory，heap），hash索引（memory，heap）。

常见的索引包括：主键索引、唯一索引、普通索引、全文索引

注意：查询一次后会将这次的查询结果缓存起来，若下次再查询则直接取结果

一、创建索引：

1、主键索引：每个表最多只能有一个主键。主键可以标识一行记录，因为不同行的主键字段值是不能相同的

1.1、建表时创建：

（1）在字段声明时创建

（2）在声明完字段类型后创建

1.2、建表后创建

2、唯一索引：值唯一的字段可以设置为唯一索引，允许插入空值，但不能是空串

2.1、建表时创建：

（1）在字段声明时创建

（2）在声明完字段类型后创建

2.2、建表后创建

或者

3、普通索引：

创建方法

或者

4、全文索引：

4.1、建表时创建

4.2、建表后创建

总结：

（1）这四种索引都可以利用alter table在建表后创建

（2）除了主键和全文索引外，都可以用create创建

（3）主键、全文索引和唯一索引可以在建表时创建。

二、删除索引：

删除普通索引、全文索引和唯一索引

删除主键

三、修改索引：

先删除索引再创建索引

四、索引使用注意事项：

1、全文索引的使用：

（1）只有myisam存储引擎才能使用全文索引

（2）全文索引只针对英文有效

（3）停止词：在英语里面会遇到很多a，the，or等使用频率很多的字或词。如果要将这些词都索引的话，工作量巨大。所以仅对出现不那么频繁的单词加索引，如‘he’是常用词，不加索引。

（4）使用方法：match（字段列表） against （'关键词'）

（5）匹配度：match（字段列表） against （'关键词'）返回的是各个字段对关键词的匹配度

（6）使用like时，若以“%”或者‘_’的开头的模糊查询，索引不生效。（%匹配任意个字符，_匹配但个字符）

举例说明：

新建一张表

插入数据，数据的部分截图如下

看一下各个行匹配单词‘pollution’的匹配度

再看一下对停止词的匹配度，这里匹配‘in’。第一行中有‘in’但匹配度依然为0。可见全文索引忽略停止词，即忽略出现频率高的词汇。

2、什么字段该加索引:

（1）查询频繁的字段

（2）唯一性强的字段

（3）更新不频繁的字段

3、使用or时索引不生效

五、用explain优化索引

explain 可以帮助我们分析 select 语句，让我们知道查询效率低下的原因，从而改进我们的查询

对之前的全文索引test库做测试

id：查询序列号。表示查询中执行 select 子句或操作表的顺序，id 值越大优先级越高，越先被执行。id 相同，执行顺序由上至下。 
select_type：查询类型
table：输出行所引用的表
possible_keys：指出 MySQL 能在该表中使用哪些索引有助于查询。如果为空，说明没有可用的索引  
key：MySQL 实际从 possible_key 选择使用的索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好 
ref：显示索引的哪一列被使用了 
rows：MYSQL 认为必须检查的用来返回请求数据的d行数