Mysql-索引

发表于 更新于 分类于

一直是想知道一条SQL语句是怎么被执行的,它执行的顺序是怎样的。本文将从MySQL总体架构—>查询执行流程—>语句执行顺序来探讨一下其中的知识

索引类型（按用途非严格划分）

  • 普通索引，这是最基本的索引，无任何限制
  • 唯一索引，与普通索引类似，索引列值必须唯一，允许NULL值
  • 全文索引，基于词干方式创建索引，多用于BLOB数据类型
  • 单列索引，仅基于一列创建的索引
  • 多列索引，基于多列创建的索引，列顺序非常重要
  • 空间索引，用作地理数据存储
  • 主键索引，是一种特殊的唯一索引，不允许有NULL值，通常在建表时创建。

索引的优缺点

索引的优点

  • 大大减少了服务器需要扫描的数据量
  • 可以帮助服务器避免排序或减少使用临时表排序
  • 索引可以随机I/O变为顺序I/O

索引的缺点

  • 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间
  • 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂
  • 索引会产生相应的碎片，产生维护开销

索引失效的情形

  • 请表上的数据行超出表总记录数30%，变成全表扫描
  • 谓词上的索引列上存在NULL值
  • 谓词上的索引列条件使用函数
  • 谓词上的索引列条件进行了相关运算
  • 负向查询（not , not in, not like, <>, != ,!>,!< ） 不会使用索引
  • 复合索引中，第一个索引列使用范围查询–只能用到部份或无法使用索引
  • 复合索引中，第一个查询条件不是最左索引列
  • 模糊查询条件列最左以通配符%开始
  • 内存表（HEAP表）使用HASH索引时，使用范围检索或者ORDER BY
  • 表关联字段类型不一样（包括某些长度不一样，但像varchar(10)与char(10)则可以，MYSQL经过内部优化处理）
  • 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
  • 如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
  • 在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用

ps: 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

疑问: http://www.cnblogs.com/jyk/archive/2010/04/10/1708945.html
http://www.cnblogs.com/yuerdongni/p/4255395.html

补充: http://blog.sina.com.cn/s/blog_6e322ce7010101i7.html
https://www.2cto.com/database/201208/145888.html