mysql 索引整理 - 普通索引

编辑开始：

7月16日 编辑 （翻了放很久的一本php书，看到 索引的最佳实战章节 于是就记录下来）

 

1：只对 where 和 order by 子句中需要的列添加索引，多余的索引智慧导致不必要的硬盘空间爱你消耗。

每次修改表信息时会更新索引，因此有索引的表性能会相应降低。

 

2：对于要使用索引的列要使用属性 NOT NULL ， 这样就永远不会存储 null 值。

 

3：最好用唯一化索引，一般情况下，当查询优化程序发现某个数据在超过30%的行里都有出现时，通常会不使用索引而进行全盘扫描。

 

4：索引长度尽量短。如果要对一个字符型添加索引，比如给 char(50) 字段添加索引，如果它的前10个字节不同而后面的基本相同，那么可以支队这前10个字节创建索引

这样能减少索引多占的空间，又可以加快检索速度。

 

5：要充分利用最左侧前缀，这是针对复合索引而言的，例如对某表中的 c1 , c2 , c3 三个列创建了一个复合索引，索引项的顺序为 c1 , c2 , c3

可在查询时支队 c2 , c3 作为条件，那么基本上是用不到所创建的复合索引，因为他们都没有做左侧的 c1 字段， 在对一下条件进行检索时会使用该索引

c1 , c2 , c3

c1 , c2

c1

编辑结束； 

 

从接触PHP以来 一直没做过真正的大型项目，顶多就做一些 网店啊 ~ 个人博客呀 ~ 中介网啊 这种小型网站。

最近在做一个音乐网，体积不大，但是我手里的数据量不大不小 ~

前几天为了一个查询 两表互联 例如 表 a 和表 b  进行 a right join b 的时候没什么异常。

但是由于查询结果不是很理想 应当用 a left join b 才是对的。结果这一个联表 给我联了 好几分钟都没有回应。

分析后发现，2个表相联的条件是 a 表的主键 和 b 表的一个存储 a 表id列 即 a left join b on a.id = b.aid

而b.aid 没有索引。 当我给 b.aid 加上索引 在查询结果发现 恢复了原来的速度。查询没有任何异常。

 

对索引 一直是 只是了解而并非真正的体验过。 因为当我学会索引的时候 我没有特意的用循环给2个表加10万条数据进行联表的测试。

这次的经验 我知道 原来 索引是这么的重要。

 

今天我想彻底对mysql索引进行了解。结果在网上搜集了一些资料 一下的我对索引的解说

  

mysql 索引

普通索引 index

唯一索引 uniqe

全文索引 fulltext

 

其中 主键也是唯一索引。这个应该谁都知道。

 

首先创建两个表 分别是 a1 和 a2 每个表中只有一个字段 id 没有设置索引 每个表有5000条记录

（其实最初我拿10000万条数据测试 结果等的不耐烦了，改了5000条也等了一小会呢 呵呵）

开始联表

select * from a1 left join a2 on a1.id = a2.id

 

耗费时间如图

 

 

现在给两个列增加索引 在试一试

alter table a1 add index(id);
alter table a2 add index(id);

 

结果很明显，快了多少我就不说了。想象一下，如果你的数据量达到几万甚至几十万的情况下

在你是初级程序员的阶段 还不了解 mysql索引的情况下 明明sql语句没有问题 一查就是漫长的进度条 会不会很郁闷啊。。 呵呵

 

就算有索引和无索引有如此大的差距 ， 但也别有 每个列 都要加索引的念头。因为索引坏处被他的好处给掩盖了。

 

索引列的信息是保存在 索引文件中，数据表中的数据是保存在数据文件中。

据我了解 联表带索引的表时速度快的原因 是因为 带有索引的列 在索引文件中会被排序。

例如数字1 - 5000 而没有索引的列在数据表中是没有排序的。

 

因此我在联两个表时 a1.id left a2.id 的时候 a2中的id 和 a1中的id 互相匹配的过程中 他们会来回 5000 X 5000 次

由于没有索引列没有排序过，在实际联表时 因为是 left join 所以 首先 a2.id 要和 a1.id 的 1号相匹配

那么在第一次匹配的时候 a2.id 的 1号 和 a1.id 的1号匹配了。但此时 匹配并非停止 a2表中的还会继续往下找

检查 1号下面的 id 是否还是 1 以此类推 他在和 a1.id 中的 1号相联的时候 他匹配了 5000次

当与 a1.id 的 2号匹配的时候 还是一样会去匹配 5000次。

 

而对有索引的列来说 因为索引列的数据排序过，会忽略不必要的匹配 所谓排序（据我了解） 当我给 a2.id 加索引的那一刻

索引文件中的 a2.id 这个列 会把所有数据 从大到小排序 即 1 - 5000

那么在联表时 当 a2.id 的 1号跟 a1.id 1号相匹配之后 明确知道 接下来在a2.id 中在没有 1号了 直接会跟

a1.id 的2号开始匹配 一次类推 结果 他只匹配5000次。

也就是说 在没有索引的情况下 上面的联表会进行 2500万次的匹配，而设置索引之后 仅匹配 5000次。

 

由于上述测试是使用 left join ，事实上只需要给 a2 的 id 加索引就可以了。给 a1.id 加索引是多余了。

 

谁适合加索引？ 简单来说的话 where 条件中的列 或 left join on 列 = 列 时 看联表方向 此时是 第二个列适合加索引。

 

那么谁又不适合加索引呢？ 列的数据重复率较高的列和修改率较高的列。

 

原因是，重复列较高的列，例如 性别，他只有 男 和 女 这2个数据 如果设置了索引，那么只会增加无用的磁盘空间。

把男女排序了 也就是 男 和 女 这2个数据，设置索引 没有任何意义。

 

修改率高的列，上面说过，索引是会排序的，那么如果把修改率高的列设了索引 那么每一次修改操作结束之后

索引文件也会重新排列所有数据，这样以来 虽然在查询时能提高性能上的问题，但是在修，增的时候并非如此。

 

多列索引

如果目前有一个用户表，分别有 姓名 ， 年龄 ， 电话 等列 （假设这些列是最常被查询的）

有一个用户管理页中有一个搜索部分 会根据 输入的 姓名 ， 年龄 ， 电话 来搜索此人的详细记录。

 

那么这个时候到底给那一个列加索引比较好呀？ （因为mysql一次只能使用一个索引，据我了解）

此时可以使用多列索引

alter table user add index user_age_phone(user , age , phone)

创建多列索引之后 如查询

where user='abc' and age = 25 and phone = 1234

mysql 直接会匹配 user = 'abc' 这一项 在去匹配 age = 25 这一项 接着去匹配 phone 这一样，三个条件 仅仅3次匹配就得到结果了。

 

那么如果不使用多列索引的话 我们也只能给其中的一项列创建索引，假设 我们给 user 列创建了索引，那么当执行上述查询之后

因为 user 被设有索引 因此 找到 abc 只需要一次匹配 之后的操作 他首先会排除 年龄不是 25岁的记录 在去排除 电话不是 1234 的记录。

这么以来 虽然 比 没有任何索引的情况好了很多 但是最终查询结果并非是理想。

 

至于什么列需要家索引，什么列不需要加索引 只能在实际情况中分析了。

 

 【索引的使用】

在一下几种情况下 mysql 在查询中即使有索引也不会去使用

1：在多列索引，查询条件中用的不是最坐标的列，那么此时是不会使用索引。

2：like查询时 % 出现在第一位也不会使用索引

3：条件中有 or 也不会使用索引。

4：索引列类型是字符串，可在查询中省略了 引号（有时字符串类型列也会存储数字），此时不会使用索引

5：如果 mysql 估计使用全表扫描比使用索引快，他也不会使用索引。

 

 

【如何检测所创建的索引是否有效（或者说索引使用情况）】

show status like 'Handler_read%'

 Handler_read_key 越高越好

Handler_read_rnd_next 越高越不好