mysql索引相关概念

MySQL目前主要有以下几种索引方法：B-Tree，Hash，R-Tree。

一、B-Tree

B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）
B-Tree在MyISAM里的形式和Innodb稍有不同：
MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址
InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录

二、Hash索引

1.仅支持"=","IN"和"<=>"精确查询，不能使用范围查询：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash
2.不支持排序：
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
3.在任何时候都不能避免表扫描：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
4.检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
5.只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引

三、R-Tree索引

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

1.什么是索引
在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

2.索引的分类
在MySQL中，通常我们所指的索引类型，有以下几种：

主键索引（PRIMARY KEY）

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

ADD PRIMARY KEY (`name`) USING BTREE;
1
全文索引（FULL TEXT）
旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。
不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引
添加全文索引

ADD FULLTEXT INDEX `idx_full`(`en_name`);
1
跟普通索引稍有不同
使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)
eg:

   SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')
1
常规索引(INDEX或KEY)

单列索引

 ALTER TABLE `testDB`.`user` 
 ADD INDEX `idx_name`(`name`) USING BTREE
1
2
组合索引

ADD INDEX `idx_mult`(`name`, `address`) USING BTREE
1
组合索引最左前缀原则
例如上面我们创建了一个name, address的组合索引
select * from user where name = ‘xxx’ 此时，会走索引
select * from user where address = ‘xxx’ 则不会走索引

唯一索引（UNIQUE KEY）

ADD UNIQUE INDEX `idx_unique`(`en_name`);
1
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

3.索引方式
一般有四种索引方式
BTREE，RTREE， HASH ，FULLTEXT。
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中
BTREE又分两种，一种是B-TREE，如下图

另外一种是B+TREE，结构如下图

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.
而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

一、MySQL索引类型

mysql里目前只支持4种索引分别是：full-text，b-tree，hash，r-tree

b-tree索引应该是mysql里最广泛的索引的了，除了archive基本所有的存储引擎都支持它.

1. full-text索引

full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。

full-text主要是用来代替like "%***%"效率低下的问题

2. b-tree索引

b-tree在myisam里的形式和innodb稍有不同

在 innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的b-tree差不多，只是还存放了指向主键的信息.

而在myisam里，主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

3. hash索引

目前我所知道的就只有memory和ndb cluster支持这种索引.

hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：

(1)由于存放的是hash值，所以仅支持<=>以及in操作.

(2)hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序.

(3)在组合所以里，无法对部分使用索引.

(4)不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值.

(5)当存在大量相同hash值得时候，hash索引的效率会变低.

4. r-tree索引

r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。

相对于b-tree，r-tree的优势在于范围查找.

二、mysql里sql语句值得注意的地方

1. myisam里所有键的长度仅支持1000字节，innodb是767.

2. blob和text字段仅支持前缀索引.

3. 使用!=以及<>不等于的时候，mysql不使用索引.

4. 当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索引里使用非第一个索引时也不使用索引.

5. 在使用like的时候，以%开头，即"%***"的时候无法使用索引；在使用or的时候，要求or前后字段都有索引.

有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引。可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽略掉index方法是ignore index.

关闭查询缓存sql_no_cache

select sql_no_cache * from table_name;

这样可以让一些很少使用的语句不放在缓存里，查找的时候不会去缓存里找；对应的是强制缓存sql_cache

select sql_cache * from table_name;

另外，在my.cnf中如果设置query_cache_type=2的话，那么只有在使用sql_cache后才会使用缓存;

还有mysql里的优先操作hight_priority让mysql优先操作这个语句

select high_priority * fromtable_name;

与其对应的是low_priority;

mysql里还有延时插入insert delayed

insert delayed into table_name....;

#当提交之后，mysql返回ok，但不立即插入，而是当mysql有空再插入。假如等待时服务器崩溃，那么所有数据丢失，并且插入不会返回自增id.

三、几个技巧

1. 强制连接顺序： STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

2. 强制使用临时表： SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT，选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

3. 分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构,索引类似一本书的目录,我们根据目录可以快速的查找到我们感兴趣的内容。索引就是存储引擎的目录,如果没有索引存储引擎必须遍历整个数据库表来查询符合条件的记录,索引的建立和优化应该是提升查询性能最有效的手段了。

索引的类型

索引是在MySQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持同一种类型的索引,其底层实现也可能不同。

B-Tree索引

B-Tree是MyISAM和InnoDB引擎默认索引类型,也可以在创建索引时通过USING BTREE来显示指定。B-Tree是一种多叉平衡树,B-Tree 结构可以显著减少定位记录时所经历的中间过程,从而加快存取速度。一般用于数据库的索引,综合效率较高。

B-Tree索引的应用场景

等值匹配

可用于= != <> IN NOT IN <=>查询语句的优化

范围匹配

可用于 > >= < <= BTEWEEN AND等范围查询语句的优化

匹配最左前缀

对于 name like bai% 这种后模糊匹配的查询,是可以利用name字段上建立的索引来优化查询的,但是对于name like %bai这种前模糊匹配的查询则没有办法使用索引了

覆盖索引

B-Tree索引的key存放的是字段的值,如果key中包含所有需要查询字段的值,我们就称之为覆盖索引,覆盖索引能够极大的提高性能。

排序

B-Tree索引是排好序的,所以MySQL可以用来优化ORDER BY 和 GROUP BY等操作。

哈希索引(HASH)

哈希索引基于哈希表实现,只有Memory引擎显示支持哈希索引,使用哈希索引可以一次定位,所以 Hash 索引的查询效率要远高于 B-Tree 索引。但是哈希索引是有很多限制的:

只有精确匹配索引所有列的查询才有效,因为哈希索引是利用索引的所有列的字段值来计算哈希值的。

只支持等值比较查询,不能用于范围查询。

哈希索引的只包含索引字段的哈希值和指向数据的指针,所以不能使用索引中的值来避免读取行。

哈希索引的数据并不是顺序存储的,无法用于排序。

全文索引(FULLTEXT)

全文索引,是一种通过建立倒排索引,快速匹配文档的方式。

空间索引(SPATIAL)

MyISAM支持空间索引,可以用作地理数据的存储。

聚集索引&非聚集索引

聚集索引

聚集索引并不是一种单独的索引类型,而是一种数据存储方式,Innode的聚集索引实际上是将主键(PRIMARY kEY )与数据行存放在同一个文件的,一张表只能有一个聚集索引。

InnoDB要求表必须有主键（MyISAM可以没有）,如果没有显式指定,则MySQL系统会用一个唯一且不为空的索引列做为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键。

InnoDB的普通索引(二级索引)的叶子节点中存放的是PRIMARY KEY的值,所以需要先查询普通索引(二级索引)的叶子节点找到对应的主键值,然后再根据主键值去聚集索引中查询到对应的数据。

InnoDB将主键与数据聚集在一起的方式,使得按主键顺序的插入和查询效率会很高,但是更新主键的字段或者不按主键的顺序插入数据的代价会比较高,所以主键的选取很重要(使用AUTO INCREMENT字段或者应用程序生成的顺序递增字段要比无序的UUID好的多)

二级索引会保存主键的值,所以主键的值不要太大。

非聚集索引

非聚集索引的索引与数据是存在在不同文件的,对于MyISAM引擎的一张表,会有三种文件:FRM(表结构)、MYD（数据,就是数据库中的每个行）、MYI（索引）。

MySQL使用索引查询数据时,先到MYI文件中找出数据存储的位置指针,然后再到MYD文件中读取数据。

MyISAM中主键索引和其他索引在结构上没有什么不同,主键索引就是一个名为PRIMARY的唯一非空索引。

索引操作