索引优化案例

我就以书中的案例给大家介绍一下。

假设要设计一个在线约会网站，用户信息表包含这些列：国家、地区、城市、性别、眼睛颜色等。其需要设计根据用户各种信息进行搜索，还需要根据用户的最后上线时间、其他会员对用户的评分排序。

尽量用到索引排序

使用索引排序会严格限制到索引的设计与查询。如果希望根据用户评分排序，就没法使用索引查询年龄范围；如果使用范围查询，就没法使用其他索引（其后面的索引）进行排序了。如果这个where条件很常用，那还是数据查出来在排序吧。

支持多种过滤条件

country、sex选择性其实并不高，国家多数就那么几个，sex一般情况也没几个，但是这种查询条件基本上都会用到，甚至有的时候sex都是单选的，所以这些选项加入到索引中也是可以的，索引为(sex, country)。如果需要查多个情况的时候，我们可以使用in查询，这时候可以查询到需要的数据，也可以用到前缀索引。

但是in的条件还是不宜过长。虽然年龄也可以用in完成，使之不用变为范围索引，但是年龄的范围多数不是很短。

所以我们应该尽量把age放到最后，因为一般会用age >= xx and age =< xx，这样age索引用后，再往后的索引就不能用了，很少会出现age=xx的使用方式。

同样我们可以把眼睛颜色、头发颜色使用in来处理，但是这种方式实际上会出现指数增长现象。等于实际上的组合形式是各种in的数量相乘。

避免多个范围条件

比如我们想查询近一周（7天）登录过的用户，以及年龄范围的用户，这样无论如何都只能使用一个索引了。因为范围索引后的索引列都不能使用了。当然我们可以把年龄用in了，但是年龄的范围数量真的有点多。

这里同样提供了一种方式，我们可以设置不让用户输入时间段，而采用近1天登录过、近3天登录过、近7天登录过、近半月登录过、近1月登录过等选项让用户选择。

然后增加一个字段，每次用户登录把值设置为0，然后由定时任务处理，把符合上述要求的用户分别赋值为1、2、3等，这样只需使用=条件即可完成，而非范围索引。

优化排序

当我们执行排序的时候，我们使用select * from xxx order by xxx limit xx, xx的时候，就算有索引，翻页到很后面性能会很低，由于每次都会去关联到指定行去判断数据，所以IO很高。

这时候可以使用延迟关联的方式来更高效的使用索引。

select * from xxx inner join (

select pk from xxx order by xxx limit xx, xx

) as x using(pk)

这样可以利用二级索引自带主键索引，进行二次查询完成优化。

索引与表的维护

维护表有三个主要目的：找到并修复损坏的表、维护准确的索引统计信息、减少碎片。

找到并修复损坏的表

表损坏通常都是系统或者硬件问题，如果你发现了什么莫名其妙的问题，试着使用check table命令来检查是否发生了表损坏。一般innodb引擎的表，我们使用如下命令可以修复表：

ALTER TABLE xxx ENGINE=INNODB;

如果innodb的表发生了损坏，一般是很严重的问题，因为innodb一般不会损坏。如果损坏了，要么是硬件问题、要么是DBA的一些错误操作，比如在Mysql外部操作的文件。

更新统计信息

可以通过命令ANALYZE TABLE来重新生成统计信息（比如行数，INNODB通常是不准的）。

可以使用SHOW INDEX FROM xx来查看索引的基数（Cardinality）。这个会有很多信息，需要注意的是，Cardinality给出了（估算）这个索引列有多少不同的值。

减少索引和数据碎片

BTree所以可能会碎片化，这会严重影响查询的效率。碎片化的索引可能会以很差或无序的方式存储在磁盘上。

有三种类型的数据碎片：

行碎片：这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能降低；
行间碎片：行碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的；
剩余空间碎片：剩余空间碎片是指数据页中有大量的空余空间，这会导致服务器读取大量不需要的数据，从而造成浪费。

对于MySIAM存储引擎，这三种碎片都会出现。

对于InnoDB，行碎片不会出现，InnoDB会移动短小的行并重写到片段中。

可以使用Optimize table 表名来整理数据。如果不支持这个命令，可以使用ALTER TABLE xxx ENGINE=INNODB;这个命令来重建表。

索引小结

在创建索引或者利用这些索引编写查询语句的时候，应注意以下三点：

单行访问是很慢的。如果服务器从存储中读取一个数据块只为一行数据，那么就浪费了很多的工作。最好读取的块能够尽可能多的包含需要的行数据。
按顺序访问范围数据是很快的，并且GROUP BY操作也无须为排序和按组聚合消耗太多性能了。
索引覆盖查询是很快的。如果查询的结果可以直接从索引中得到，可以省去二次查询获取行数据。

其实多数情况我们都希望能够设计一个完美的三星索引适合所有查询，但是这真的很难，我们需要取舍，对于常用查询必须有很好的索引，对于一些比较少用的查询，我们可以容许对查询出的数据块做排序、筛选。

本文原创于赵伊凡BLOG

©原创文章，转载请注明来源： 赵伊凡's Blog
©本文链接地址: 高性能mysql读书笔记3（索引2）