MySQL里建立索引应该考虑数据库引擎的类型

心已赠人 2022-08-12 14:58 16阅读 0赞

比方说有一个文章表，我们要实现某个类别下按时间倒序列表显示功能：BSD爱好者乐园\]1X/w\*s0p  
  
j+^1o\#~"r\#zcYK f ~SELECT \* FROM articles WHERE category\_id = ... ORDER BY created DESC LIMIT ...  
Vs/JFBSD爱好者乐园/R8R |t f @Vtrn  
这样的查询很常见，基本上不管什么应用里都能找出一大把类似的SQL来，学院派的读者看到上面的SQL，可能会说SELECT \*不好，应该仅仅查询需要的字段，那我们就索性彻底点，把SQL改成如下的形式：BSD爱好者乐园 V\}%/x7~9E8H  
BSD爱好者乐园D1vm9J0KGBt  
SELECT id FROM articles WHERE category\_id = ... ORDER BY created DESC LIMIT ...BSD爱好者乐园7K1J^|w F y  
  
5X\` lWY\#kK我们假设这里的id是主键，至于文章的具体内容，可以都保存到memcached之类的键值类型的缓存里，如此一来，学院派的读者们应该挑不出什么毛病来了，下面我们就按这条SQL来考虑如何建立**索引**：BSD爱好者乐园Gzl \[-N H  
BSD爱好者乐园-csMzlMp4w)jH  
不考虑数据分布之类的特殊情况，任何一个合格的WEB开发人员都知道类似这样的SQL，应该建立一个”category\_id, created“复合索引，但这是最佳答案不？不见得，现在是回头看看标题的时候了：**MySQL**里建立索引应该考虑数据库**引擎**的类型！  
fV)i?d\#\{   
Z;^3s\[(J-lD如果我们的数据库引擎是InnoDB，那么建立”category\_id, created“复合索引是最佳答案。让我们看看InnoDB的索引结构，在InnoDB里，索引结构有一个特殊的地方：非主键索引在其BTree的叶节点上会额外保存对应主键的值，这样做一个最直接的好处就是Covering Index，不用再到数据**文件**里去取id的值，可以直接在索引里得到它。BSD爱好者乐园v4i%n4v9A(s,cl-K  
  
\[.DEB Lj\},x如果我们的数据库引擎是MyISAM，那么建立"category\_id, created"复合索引就不是最佳答案。因为MyISAM的索引结构里，非主键索引并没有额外保存对应主键的值，此时如果想利用上Covering Index，应该建立"category\_id, created, id"复合索引。BSD爱好者乐园!^"D SyqL6B  
  
%Ugm/zJ)~y唠完了，应该明白我的意思了吧。希望以后大家在考虑索引的时候能思考的更全面一点，实际应用中还有很多类似的问题，比如说多数人在建立索引的时候不从Cardinality（SHOW INDEX FROM ...能看到此参数）的角度看是否合适的问题，Cardinality表示唯一值的个数，一般来说，如果唯一值个数在总行数中所占比例小于20%的话，则可以认为Cardinality太小，此时索引除了拖慢insert/update/delete的速度之外，不会对select产生太大作用；还有一个细节是建立索引的时候未考虑字符集的影响，比如说username字段，如果仅仅允许英文，下划线之类的符号，那么就不要用gbk，utf-8之类的字符集，而应该使用latin1或者ascii这种简单的字符集，索引文件会小很多，速度自然就会快很多。这些细节问题需要读者自己多注意，我就不多说了。