一, 引入
对于数据库来说, 随着数据量的增大, 查询会成为影响数据库性能的主要操作, 除了分库分表之外, 优化查询语句成了我们提高查询效率的重点, 而优化查询就不得不提索引.
二, 索引的原理
2.1, 索引的结构
假如我们要寻找表中的某一条数据, 基本的方法是顺序查找每一条数据, 这就是全表扫描, 时间复杂度为 O(n). 如果使用二分查找或二叉树查找能极大地提高查找效率, 但是二分查找要求数据按顺序排列, 二叉树只能应用于二叉树, 而数据本身的组织结构不可能同时满足各种数据结构 (例如, 理论上不可能同时将两列都按顺序进行排列). 所以在数据库保存的数据之外, 数据库系统还维护着满足特定查找算法的数据结构. 这些数据结构以某种方式引用(指向) 数据, 这样就可以在这些数据结构上实现高级查找算法. 这种数据结构就是索引.
2.2, 存储引擎与索引
MySQL 索引的具体实现与存储引擎相关, 不同的存储引擎其索引的实现不一定相同. 这里只讨论 MyISAM 和 InnoDB.
它们的索引类型均为 B + 树, 其叶节点为 [key,data] 形式.
MyISAM 的所有索引里 data 保存的都是数据记录的地址, 索引和数据文件是分离的, 称为非聚集索引.
对于 InnoDB 来说, 它的 data 保存了完整的数据记录, 它的 key 是表的主键, 因此它的数据文件本身便是主索引, 称为聚集索引. 因为 InnoDB 的数据文件本身要按主键聚集, 所以 InnoDB 要求表必须有主键(MyISAM 可以没有), 如果没有显式指定, 则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键, 如果不存在这种列, 则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键, 这个字段长度为 6 个字节, 类型为长整形.
第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储的是相应记录主键的值而不是地址.
聚集索引这种实现方式使得按主键的搜索十分高效, 但是按辅助索引搜索需要检索两遍索引: 首先检索辅助索引获得主键, 然后用主键到主索引中检索获得记录.
2.3, 是否使用索引
既然索引可以加快查询速度, 那么是不是只要是查询语句需要, 就建上索引? 答案是否定的. 因为索引虽然加快了查询速度, 但索引也是有代价的: 索引文件本身要消耗存储空间, 同时索引会加重插入, 删除和修改记录时的负担(B + 树的维护), 另外, MySQL 在运行时也要消耗资源维护索引, 因此索引并不是越多越好. 一般两种情况下不建议建索引.
2.3.1,
第一种情况是表记录比较少, 例如一两千条甚至只有几百条记录的表, 没必要建索引, 让查询做全表扫描就好了. 至于多少条记录才算多, 这个个人有个人的看法, 我个人的经验是以 2000 作为分界线, 记录数不超过 2000 可以考虑不建索引, 超过 2000 条可以酌情考虑索引.
2.3.2,
另一种不建议建索引的情况是索引的选择性较低. 所谓索引的选择(Selectivity), 是指不重复的索引值与表记录数的比值, 选择性越高的索引价值越大, 这是由 B+Tree 的性质决定的.
因此性别列不适合建立索引.
2.4,InnoDB 的主键选择与优化
经常看到有帖子或博客讨论主键选择问题, 有人建议使用业务无关的自增主键, 有人觉得没有必要, 完全可以使用如学号或身份证号这种唯一字段作为主键. 不论支持哪种论点, 大多数论据都是业务层面的. 如果从数据库索引优化角度看, 使用 InnoDB 引擎而不使用自增主键绝对是一个糟糕的主意.
上文讨论过 InnoDB 的索引实现, InnoDB 使用聚集索引, 数据记录本身被存于主索引, 因此每当有一条新的记录插入时, MySQL 会根据其主键将其插入适当的节点和位置.
如果表使用自增主键, 那么每次插入新的记录, 记录就会顺序添加到当前索引节点的后续位置, 这样就会形成一个紧凑的索引结构, 近似顺序填满. 由于每次插入时也不需要移动已有数据, 因此效率很高, 也不会增加很多开销在维护索引上.
如果使用非自增主键(如果身份证号或学号等), 由于每次插入主键的值近似于随机, 此时 MySQL 不得不为了将新记录插到合适位置而移动数据, 这增加了很多开销, 同时频繁的移动, 分页操作造成了大量的碎片, 得到了不够紧凑的索引结构, 后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面.
因此, 只要可以, 请尽量在 InnoDB 上采用与业务无关的自增字段做主键.
三, 索引与执行计划
2.1, 索引分类
1, 单索引
index|unique|primary|fulltext
2, 联合索引
index(id,name)|unique(id,name)|primary(id,name)|fulltext(id,name)
3, 前缀索引
用列的前缀代替整个列作为索引 key, 当前缀长度合适时, 可以做到既使得前缀索引的选择性接近全列索引, 同时因为索引 key 变短而减少了索引文件的大小和维护开销.
2.2, 索引语法
2.1,
1)创建:
建表时创建:
- CREATE TABLE t_test (
- KEY|INDEX index_name (name(4))
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在已存在的表上创建:
ALTER TABLE t_test ADD UNIQUE INDEX index_name (name(4))
2)删除:
DROP INDEX index_name ON t_test;
2.3, 执行计划
使用很简单, 在查询语句之前添加 EXPLAIN 即可.
EXPLAIN SELECT name from t_tests WHERE name='zhangsan', 显示如下信息:
对各列进行简要分析:
官方链接:
(1)id: 表格查询的顺序编号.
(2)select_type: 查询的类型, 主要是用于区分普通查询, 联合查询, 子查询等复杂的查询
1,SIMPLE: 简单的 select 查询, 查询中不包含子查询或者 union
2,PRIMARY: 查询中包含任何复杂的子部分或 UNION 查询, 则被标记为 primary
3,SUBQUERY: 子查询, 结果不依赖于外部查询结果集(不会被数据库引擎改写的情况);
4,DEPENDENT SUBQUERY: 子查询, 依赖于外部查询的结果集, 由于 MySQL 优化导致, 通常查询效率非常低(即使采用了索引).
4,DERIVED: 连接查询 (join) 的表替换为了查询语句, 这个查询就被标记为 DERIVED.
5,UNION: 使用 union 连表, 第一个 SELECT 为 PRIMARY, 第二个及之后的所有 SELECT 为 UNION.
6,DEPENDENT UNION: 子查询中的 UNION 操作, 子查询中所有 SELECT 语句的 SELECT TYPE 为 DEPENDENT UNION.
7,UNION RESULT: 每个结果集取出来后, 会做合并操作, 这个操作就是 UNION RESULT;
...
(3)table: 数据行所在的表的名称或别名.
(4)partitions: 对于分区表, 显示查询的分区 ID, 对于非分区表, 显示为 NULL
(5)type: 访问类型, sql 查询优化中一个很重要的指标, 结果值从好到坏依次是:
null->system->const->eq-ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL
0,null: 不访问任何一个表格.
1,system: 表只有一行记录.
2,const: 表示通过索引一次就找到了, 很快.
官网解释: The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the REST of the optimizer. const tables are very fast because they are read only once.
3,eq_ref:join 查询过程中, 关联条件为主键或者唯一索引, 关联数据只有一条.
4,ref: 非唯一性索引扫描, 返回匹配某个单独值的所有行. 本质是也是一种索引访问, 它返回所有匹配某个单独值的行, 然而他可能会找到多个符合条件的行, 所以它应该属于查找和扫描的混合体
5,range: 一般就是在 where 语句中出现了 bettween,<,>,in 等的查询. 这种索引列上的范围扫描比全索引扫描要好. 只需要开始于某个点, 结束于另一个点, 不用扫描全部索引
6,index:Full Index Scan,index 与 ALL 区别为 index 类型只遍历索引树. 索引树上的键值只有索引列键值 + 主键, 而全表扫描则是在 聚集索引树 (主键 + 所有列) 上进行扫描.
7,ALL:Full Table Scan, 遍历全表以找到匹配的行
(6)possible_keys
查询涉及到的字段上存在的索引, 但不一定被查询实际使用.
(7)key
实际使用的索引, 如果为 NULL, 则没有使用索引.
(8)key_len
查询中使用到的索引的长度, 比如多列索引, 只用到最左的一列, 那么使用到索引的长度则为该列的长度, 故该值不一定等于索引的长度.
(9)rows
该表格扫描到的行数. 这里注意在 MySQL 里边是嵌套链接, 所以, 需要把所有 rows 相乘就会得到查询数据行关联的次数.
(10)filtered
实际显示行数占扫描 rows 的比例.
(11)extra
额外信息
1,Using index:
索引覆盖, 也就是不止要使用到索引, 而且没有回表查询, 效率高.
2,Using filesort :
无法利用索引来完成的排序.
2,Using temporary:
使用到临时表, 表数量较少的情况下, 临时表使用缓存, 但是比较大的时候, 则会磁盘存储, 这种情况下, 性能将会急剧下降
3,Using where :
根据 where 条件, 先取出数据, 再跟其他表格关联查询
4,Using index condition
使用到索引过滤
四, 索引实践
来源: http://www.jianshu.com/p/71516ebe8d1f