为什么要索引

为什么要索引

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

索引如何起作用

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

为什么不直接对数据排序

如果一个表只有一个索引这样做事可以的，但是可能我们还想添加第二索引。例如用户表的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. 利用慢查询日志找出那些性能低劣的查询