MySQL索引的使用及原理

简述 MySQL 索引的使用及原理

---

---

前言

作为一个出色的rd对数据库的使用，优化，管理必须是信手拈来否则怎么能配得起出色二字。那么接下来就以MySQL为标准数据库了解一下关于它的索引以及其原理

一、索引能干什么？

当数据库数据量很大时，进行查询操作时会进行全盘扫描尽管MySQL是给予b+tree的结构来设计的但也顶不住频繁IO（内存从磁盘的读操作）处理导致数据缓慢，当数据量较少时不会感觉到有何异常。从而需要对数据库进行优化。当然优化的方式有很多这里就不一一介绍了。这儿选择了用的方式索引。

二、索引的类型及使用

1.主键索引

parimary key
索引唯一，且自增， 通常一张表中只存在一个主键索引

2.唯一索引

unique index
索引对因的字段值唯一，不可重复，常用于用户账户，订单号等

3.普通索引

index
索引可多次出现，没有次数限制，常用于多检索字段

4.全文索引

fulltext index 
索引用于文本检索，常在varchar, char, text 字段类型上增加索引，当使用时需考虑使用场景，因该索引机制不同导致查询精度不同
语法:
	 match(fields) against('value')
	 查询类型默认为 自然语言查询, 
	 另
	 	布尔全文检索模式 in boolen mode 等
	 	待扩展
 注:
 	全文索引本身不支持中文， 需使用第三方工具或高版本的查询方式, mysql-version > 5.7 可使用内置ngram全文解析器, 具体使用请自行百度
	

5.联合索引

顾名思义，由多个字段索引进行组合
使用索引满足条件:最左匹配原则	
最左匹配原则 : 有最左前缀的原则即最左优先, 是给予b+tree的底层原来实现的
	b+tree 的存储机制是复合的数据结构且b+叔是按照从左到右的顺序来建立搜索树的，当按照顺序检索时会减少搜索时的回表(IO对写)操作，
从而提高检索速率

6.聚簇索引与非聚簇索引

 聚簇索引 
 	常用于Innodb中且一张表中有且只有一个，
 	定义范围：
 		1，当表中定义PK(primary key)后，主键索引便为聚簇索引。
 		2，当未定义PK, 便选择第一个不为空且定义唯一索引的为聚簇索引
 		3，若都未定义，则mysql创建一个隐藏的row-id列为聚簇索引
 	注意事项：
 		1，不建议用较长的字段列做主键，因为普通索引中都会存储主键，会导致普通索引过于庞大
 		2，建议使用趋势递增的 key 做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动
 	存储结构：
 		1，没有单独的区域进行存储
 		2，主键索引的叶子节点，存储主键与对应行记录(而不是存储空间)
 	存储图片：

 非聚簇索引 
 	常用于MyISAM, 
 	含义 : 索引与行记录是分开存储的
 	定义范围：
 		1，有连续聚集的区域单独存储行记录
 		2，主键索引的叶子节点，存储索引，行记录对应的存储指针
 		3，普通索引的叶子节点，存储索引列，行记录对应的存储指针
 	存储图片

---

三、explain 索引详解

四、索引失效情况

1，当检索字段为字符类型的索引时，检索值需要加引号
2，使用 like 查找时，除使用该 xx% 外全部失效
3，使用 >, <>, != , 查询时时失效的
	 <, in, between and 索引不是效
4，or 查询索引失效，除非检索项都有索引
5，全文索引使用使用索引应使用相应语法，否则索引失效
6，联合索引(组合索引)使用时应满足最左匹配原则，否则索引失效。
注: mysql 会自动优化where条件顺序,  用于理解最左匹配原则
7，可使用explain 对sql 语句进行诊断

五、转载博客

  聚簇索引与非聚簇索引
  http://www.gameboys.cn/article/149

补录

第一节，MySQL索引的使用及原理
第二节，MySQL常用语法