Mysql-索引

索引

简介

无索引的表是一组无序的行，当我们在查询表中的某条数据时，它会逐行查询去找到匹配的行，

这是一个全表扫描操作，效率很低。

用来加速查询的技术有很多，其中最为重要的就是索引，通常情况下，查询速度差异的最大因素

就是索引的使用是否得当。此外需要注意，有些查询的编写方法会阻碍你对索引的有效利用。

 

索引提高效率的做法

1）可以知道匹配行在什么位置结束，可以跳过其余的部分

2）利用定位算法，不用从索引开始位置进行线性扫描即可找到匹配项，比如二分法

 

为什么不直接对数据排序，省掉索引操作？

 

分析：

 

　　直接对数据排序，省掉索引的操作也能加快数据检索的速度

　　　　　　　　　　　　V

　　如果表中只有一个索引，那么这是肯定的

　　　　　　　　　　　　V

　　但是如果你还想添加第二个索引，同时按两种方式对数据进行排序

　　比如，一个有关客户名字的索引，一个有关客户电话号码的索引

　　　　　　　　　　　　V

　　将索引从数据行中分离出来便可解决这个问题，而且还可以创建多个索引

　　　　　　　　　　　　V

　　索引里的数据通常比表中的数据行更短，当对索引增删值时，为保持排序顺序，

　　来回移动索引的较短的索引值比移动较长的数据行更加容易

 

案例：有索引和无索引的查询分析

假设，你有3个无索引表，t1,t2和t3，其中每个表都包含一个列，分别为i1,i2和i3，并且每个列有1000行，包含的是1~1000的数字。

我们现在想要在这些表的各个行的组合中，将彼此相等的行找出来。

用如下sql语句进行查询

SELECT t1.i1, t2.i2, t3.i3 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.i1=t2.i2 AND t2.i2=t3.i3;

 

有索引查询

处理过程

1）从表t1中选择第一个行，看该行包含的数据

2）利用表t2的索引，直接转到与表t1的值匹配的那行，类似t3的索引直接转到与表t1匹配的那行

3）继续选择t1的下一行，重复前面的过程直到t1所有的行都被检查完为止

 

虽然对表t1执行的仍然是全表扫描，但是为了将t2,t3中的数据查询出来，t2和t3执行的是索引操作

 

无索引查询

如果我们不使用索引进行查询，我们根本不知道哪行包含了哪些值，于是我们必须尝试所有的组合以得到与WHERE

匹配的行，可能的组合总共有1000*1000*1000种，这比有索引查询的匹配数多了100万倍，所花费的时间将大大增加。

 

索引的缺点

1）索引会降低大部分与写相关的操作的速度

因为写入一行不仅仅是写入一行数据，还要更改索引，表的索引越多，需要做的修改就越多，

大部分表的操作都是读多写少，但是对于写操作较多的表来说，索引在更新方面的开销会很大

2）索引会占用磁盘空间

建议：如果不需要某个特定的索引来加快查询速度，那么就不要创建它

 

索引的选择

我们需要考虑各个表的具体用途，才能选择合适的索引

1）为用于搜索、排序或分组的列创建索引，而对于用作输出的列则不用创建索引

最佳索引列是那些出现在WHERE字句中的列、连接子句中的列或者出现在ORDERBY 或 GROUP BY子句中的列，

只出现在SELECT后面的输出列则不是好的索引候选列

SELECT 
    col_a                            --不是索引候选列
FROM 
    tb1 LEFT JOIN tb2
    ON tb1.col_b = tb2.colc          --索引候选列
WHERE
    col_d = exp_value;               --索引候选列

 如果mysql使用多个连接列来优化查询，那么它不会使用全表扫描，从而大大减少了“表-行”组合。

 

2）索引值应该尽量选择较小的数据类型

比如，当使用MEDIUMINT足以容纳需要存储的数据时，就不要选用BIGINT

比如，如果值的长度不会超过25个字符，那么就不要用CHAR（100）

索引较小值在多个方面提高索引的处理性能

a.让操作更快，从而加快索引查找速度

b.让索引短小，从而减少对磁盘的IO请求

c.对于更短小的键值，键缓存里的索引块里可以容纳更多的键值，在不从磁盘读取索引块的前提下，提高找到键值的几率

 

3）不要建立过多的索引

每增加一个索引都需要占据额外的磁盘空间，影响写入操作的性能，

对表修改后，索引会更新，可能还会重组，索引越多，整个过程耗时就越长，

mysql对索引推敲生成执行计划的耗时会增加

 

参考资料：《Mysql技术内幕》