为什么要索引


为什么要索引

没有索引查找数据要全表扫描

索引如何起作用

  • 一种使用索引提高效率的方法:我们可以得知匹配行在什么地方结束,从而跳过其它部分
  • 另一种使用索引提高效率的方法:利用定位算法,不用从索引开始位置进行线性扫描,即可直接找到第一个匹配项(例如二分查找比扫描要快很多)。

为什么不直接对数据排序

如果一个表只有一个索引这样做事可以的,但是可能我们还想添加第二索引。例如用户表的id和名称字段。

此外索引里的行通常比表里的数据行更短。当发生插入或者删除时,为保存排序顺序,来回移动较短的索引值,要比来回移动较长的数据行更加容易。

索引如何影响mysql查询

假设,你有3个表t1,t2,t3,其中每个表都包含一个列,它们分别为i1,i2,i3,并且每个表都有1000行记录。

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
  • 如果没有索引,需要尝试所有组合1000 * 1000 *1000
  • 如果i1,i2,i3都建有索引

    • 从表t1中选择第一个行,看该行包含的是什么值
    • 利用表2的索引,直接转到表t1的值相匹配的那个行。类似利用表t3的索引直接转到与表t1的值相匹配的那个行
    • 继续选择表t1的下一行,然后重复前面的过程。

    整个过程,只对表t1执行了全扫描操作。对表t2,t3执行的是索引查找。

因此我们要

  1. 为用于搜索、排序或者分组的列(候选列)创建索引,而对于用作输出显示的列则不用创建索引。
    1. where
    2. order by and group by
  2. 认真考虑数据列基数(列能容纳的所有非重复值得个数)。

    基数越高,重复率越低,索引的使用效果越好。并且查询优化程序确定出某个值在表的行里出现频率很大时,它会跳过索引,转而执行全表扫描操作(因此性别列建索引没啥用)

  3. 索引短小值

    1. 数据类型越小,内存占用越小
    2. 索引的比较操作更快
    3. 键缓存的索引块可能容纳更多的键值
    4. 对于innodb 使用聚簇索引,数据行和主键值存储在一起。在二级索引里进行查找,会先得到主键值,再通过主键定位到相应的行。因此主键值会在一个二级索引里都会重复出现
  4. 索引字符串的前缀。
  5. 利用最左前缀。

    当创建包含n个列的复合索引时,实际上会创建n个专供mysql使用的索引。如果对一个表添加一个(country/state/city)的复合索引,那么索引会根据country/state/city的顺序来进行索引排序。因此country也是顺序的,可以直接使用country进行检索。

  6. 不要建立过多的索引

    1. 索引占用空间
    2. 表的修改需要对索引进行维护
  7. 让参与比较的索引保存匹配。
    1. 哈希散列,适合精确查找
    2. B树索引适合范围查询
  8. 利用慢查询日志找出那些性能低劣的查询

,

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: