MySQL 索引总结

注意:Index(Name,Age)表示在Name,Age两列上建立联合索引

由于索引对数据库的查询性能有着至关重要的影响,下面是我的一些总结和体会:

  • 一个查询一次只能使用一个索引:select name from user where name=’plantegg’ and age>35 , 如果Index(name); Index(age)的话,MySQL查询优化器会自动选择一个索引来使用;
  • MySQL选择哪个索引,可以这样来看:mysql> show index from photo;
    +——-+————+————————+————–+—————+———–+————-+———-+——–+——+————+———+
    | Table | Non_unique | Key_name               | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +——-+————+————————+————–+—————+———–+————-+———-+——–+——+————+———+
    | photo |          0 | PRIMARY                |            1 | photo_id      | A         |      237871 |     NULL | NULL   |      | BTREE      |         |
    | photo |          1 | index_random           |            1 | random        | A         |      237871 |     NULL | NULL   | YES  | BTREE      |         |
    | photo |          1 | FK_photo_profile_id    |            1 | profile_id    | A         |      237871 |     NULL | NULL   |      | BTREE      |         |
    | photo |          1 | FK_photo_temp_photo_id |            1 | temp_photo_id | A         |      237871 |     NULL | NULL   | YES  | BTREE      |         |
    | photo |          1 | FK_photo_album_id      |            1 | album_id      | A         |      237871 |     NULL | NULL   | YES  | BTREE      |         |
    +——-+————+————————+————–+—————+———–+————-+———-+——–+——+————+———+
  • Cardinality越大表示索引候选分得越细(默认都是BTree索引);
  • 你也可以试试Force Index强制使用某个索引看看速度是不是MySQL是不是查询起来更快(如果真是这样的话你需要Analyze yourTable 了,MySQL重新计算你的Cardinality以帮助他正确地选择INDEX)
  • 仔细分析Explain的结果:重点留意Extra,Key,Rows,Select_type的结果!
  • 小心查询中的Group by 、order by之类的,基本上这样的查询在Explain的时候都会出现: Using where; Using temporary; Using filesort
  • 联合索引要小心使用,Index(Name,Age)时,如果where name=’pp’ 能使用索引,where age=25时不能使用索引;where name=’pp’ and age>25 能使用索引;    where name =’pp’  order by  age  能使用索引;  where  name>’pp’  order by age  不能使用索引,但是 where  name>’pp’  order by name,age  能使用索引,请仔细留意差异  ;  order by name asc age desc 将不能使用索引!
  • 索引只有被加入到内存里的时候对你的查询才有帮助,如果索引太大根本无法放入内存这样的索引失去了意义!访问索引的时候还需要Random  Aceess  Disk这比不用索引还慢!
  • select  的时候能不用select * 就不要用,也就是需要哪些列只拿那些列(Hibernate那些对性能没有啥好处的),比如:在Index(Name)的时候,select * from user where name like ‘pp%’ 和 select name from user where name like ‘pp%’ 两者性能千差万别,如果有10000条符合记录的结果的话(User表总共有10亿条记录)前一个查询可能需要2分钟(假设你的系统每秒100 IOPS的样子)后一个查询可能只需要0.01秒!因为前一个查询要从硬盘上取出散布在到处的这10000条记录,后一个查询直接从内存中的索引上拿Name就够了!后一个查询你explain的时候在Extra中会看到Using Index。
  • 永远要警惕对磁盘的随机访问,顺序读写和随机访问的性能差别是N个数量级的(顺序读写的时候你的OS、Dish Cache 这个时候大显身手)对这个问题如果感兴趣的话建议你去用C写个测试程序,随机读写的时候不断地fseek,相应地同样的功能你不要fseek而是通过顺序读写到内存中,在内存自己扔掉那些应该由磁盘去fseek的地方,应该明白我的意思吧!
  • 5.0.27后,MYSQL就支持set profling=1了,这样可以详细分析你的SQL语句每一步骤的时间消耗了
  • 如果order by 的时候有 limit + 索引配合的话,你会有意外惊喜的
  • 如果你能够看到一个查询能像MySQL查询优化器那样在大脑中分解他的话,我想对你来说没啥难题了,:-)
  • 待续,下次想到了再添加吧
觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>