Mysql索引以及优化策略

为什么需要索引？

索引是任何数据库的灵魂，没有索引的数据库就像没有船桨的船只能用手划舟；没有索引的数据库就像一本没有目录的字典，你需要一个字一个字的寻找答案。没错，索引是为了提升mysql的性能，使用索引只需2-3次磁盘I/O就可以拿到数据，不使用索引磁盘I/O次数与数据量的次数是1：1的也就是说如果500万条数据就需要500万次I/O，如果访问量比较多且sql奇慢无比，那么很快服务器会因为打满磁盘I/O而崩溃。

Mysql的索引种类

1.Hash索引

Hash索引基于hash表实现，只在等值查询中有效(>=,<=,=,in)，hash索引效率极高，但Hash冲突比较高时，hash索引的效率也将降低。只有Memory引擎显示的支持hash索引，如果想在innodb中使用hash索引，可以创建自定义hash索引。

为field创建一个hash索引列，假如有url字段需要被检索，那么我们新加一个字段url_hash，在存储url同时为url计算出hash值并存储在url_hash中。

select url from table where url = 'xx.com' and url_hash = hash(xx.com)

2.Btree索引

Btree是二叉树的变种，旨在以降低二叉树的高度从而减少查询所需的磁盘I/O次数。

 Btree索引的限制在于

1. 最左前缀原则，如果where后边的条件不是按索引的顺序来的，那么我们无法使用索引匹配将导致全表扫描
2. 范围查询过后，所有的索引都将失效

3.Fulltext索引

全文索引是特殊的索引类型，它查找的是文本中的关键字，而不是直接比较索引中的值。

4.Rtree索引

只有MyIsam支持Rtree索引，Rtree无需遵从最左前缀原则，它会从所有的维度来索引数据。

---

Mysql的索引分类

1.普通索引

普通的索引，仅仅作用与加速。

CREATE INDEX index_name ON table_name (column_name)

2.唯一索引

不允许出现重复的值，但允许出现null。

CREATE UNIQUE INDEX index_name ON table_name (column_name)

3.复合索引

多个列组合而成的索引，可用于为特定的查询指定复合索引。

CREATE INDEX index_name ON table_name (col1 ASC, col2 DESC, col3 ASC)

4.聚簇索引

聚簇索引也叫主键索引，无需我们创建，聚簇索引的查询速度是极快的，因为它不需要回表查询数据，因为它的数据与索引是存在一起的，找到了索引就可以返回数据。 而非聚簇索引的非叶子节点是不存放数据的，也就存在一个回表查询数据的过程。

---

索引的优缺点

优点：

1.索引能大大提高查询效率，降低磁盘I/O次数。

2.索引能加速排序，减少排序成本（因为索引本来就是有序的~）。

3.索引使随机I/O变为顺序I/O。

缺点：

1.索引也是数据结构，且其中保存了主键、索引字段与数据地址，以空间换时间。

2.索引会降低Insert与Update操作的速度，因为这两个操作需要该索引Re-Build，以保证索引的顺序以及有效性。

---

索引的原理

索引的原理其实就是二分查找，在一棵二叉树中使用二分查找是十分快的。

为什么是B+树，不是其他树？

众所周知，如果百万级别的二叉树，就算完美平衡也有50万+的高度，发生如此多次磁盘I/O是我们所不能接受的..所以我们要尽量降低索引的高度，降低磁盘I/O的消耗。B+树的真实情况是三层就可以容纳上百万的数据。你可能会问："B-Tree，Btree的高度也很低啊，为什么不用B-tree与Btree？"确实是如此，但其实主要因为：

1.同数据量级，B+tree比B-tree与Btree的高低更低，因为B+tree的非叶子节点是不存放数据的，所以它可以放下更多的索引以降低树的高度。

2.Btree与B-tree的范围查询需要中序遍历发生多次磁盘I/O，但B+tree无需中序遍历一次回表即可查询出范围内所有数据

3.B+tree的查询时间比较稳定，经历2-3次磁盘I/O即可；但Btree与B-tree的查询时间可能存在较大的差异，如果范围查询需要中序遍历树，但如果只查询一条数据可以很快的返回数据。

---

什么时候需要索引，什么时候不要索引？

简单来说，如果你的列中含有大量的null、大量重复的值，或者你的表数据很少，那么此时就不要简索引了。反之，那么你需要索引。

---

mysql优化策略

1.最左前缀匹配

假如我们现在有a,b,c组合的复合索引，如果我们要完全匹配索引，必须按照a,b,c的顺序来查询数据，只有下面三种情况可以完全使用复合索引。

select * from table where a = 'a'
select * from table where a = 'a' and b = 'b'
select * from table where a = 'a' and b = 'b' and c = 'c'

ps:如果你使用where a = 'acb'也看到了使用索引的情况，那么一定是mysql帮你做了优化，调整了索引的顺序，我们一定不要存在侥幸心理哈。

2.范围查询后索引全失效

假设我们有a,b,c组合的复合索引，在其中一列使用了范围查询后，则后面所有的索引都失效

select * from table where a = 'a' and b > 100 and c ='c'

 此种情况会导致c列无法再使用索引。

我们可以调整索引顺序为a,c,b来解决这种问题

select * from table where a = 'a'  and c ='c' and b > 100

 3.索引列做计算导致索引失效

假设我们有索引列a，此时如果我们这样做

select * from table where count(a) = 15

这样做导致a无法用到索引列，索引是等值比较的，此时值做了改变当然用不到索引。

4.is not null , != null 导致索引失效

如果一个字段是NOT NULL ，就不要再使用 IS NULL 或者 IS NOT NULL 来作为查询条件，没有任何意义。

5.like 模糊查询导致索引失效

select * from table where name like '%abc%'# 失效
select * from table where name like '%abc'# 失效
select * from table where name like 'abc%' #有效

如果实在要使用like %%，那么请使用覆盖索引

查询的条件和查询的结果都是索引中的字段时我们将其称为覆盖索引。

6.select语句尽量不写*

mysql需要将结果序列化再返回给我们，如果用不到的字段就不要拿出来啦，减轻mysql序列化的压力，能省一点是一点！

---

 

索引的使用千变万化，笔者的学习不够深刻，有不足之处请各位指正，感谢！