什么是索引? 为什么要建立索引
索引用于快速找出在某个列中有一特定值的行, 不使用索引 MySQL 必须从第一条记录开始读完整个表, 直到找出相关的行, 表越大查询数据所花费的时间就越多, 如果表中查询的列有一个索引, MySQL 能够快速到达一个位置去搜索数据文件, 而不必查看所有数据, 那么将会节省很大一部分时间.
例如: 有一张 person 表, 其中有 2W 条记录, 记录着 2W 个人的信息. 有一个 Phone 的字段记录每个人的电话号码, 现在想要查询出电话号码为 xxxx 的人的信息.
如果没有索引, 那么将从表中第一条记录一条条往下遍历, 直到找到该条信息为止.
如果有了索引, 那么会将该 Phone 字段, 通过一定的方法进行存储, 好让查询该字段上的信息时, 能够快速找到对应的数据, 而不必在遍历 2W 条数据了. 其中 MySQL 中的索引的存储类型有两种: BTREE,HASH. 也就是用树或者 Hash 值来存储该字段, 要知道其中详细是如何查找的, 需要一定的算法知识了.
B-Tree
B-Tree 索引, 它是目前关系型数据库中查找数据最为常用和有效的索引, 大多数存储引擎都支持这种索引. 使用 B-Tree 这个术语, 是因为 MySQL 在 CREATE TABLE 或其它语句中使用了这个关键字, 但实际上不同的存储引擎可能使用不同的数据结构, 比如 InnoDB 就是使用的 B+Tree.
B+Tree 中的 B 是指 balance, 意为平衡. 需要注意的是, B + 树索引并不能找到一个给定键值的具体行, 它找到的只是被查找数据行所在的页, 接着数据库会把页读入到内存, 再在内存中进行查找, 最后得到要查找的数据.
image.PNG
InnoDB 聚簇索引(clustered index)
聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索引, 特别是随机字符串, 会使得系统进行大量的移动操作), 且一个表只能有一个聚簇索引. 因为由存储引擎实现索引, 所以, 并不是所有的引擎都支持聚簇索引.
聚簇索引:
image.PNG
二级索引:
image.PNG
索引的优点和缺点
优点
1, 所有的 MySQL 列类型 (字段类型) 都可以被索引, 也就是可以给任意字段设置索引
2, 大大加快数据的查询速度
缺点
1, 创建索引和维护索引要耗费时间, 并且随着数据量的增加所耗费的时间也会增加
2, 索引也需要占空间, 我们知道数据表中的数据也会有最大上线设置的, 如果我们有大量的索引, 索引文件可能会比数据文件更快达到上线值
3, 当对表中的数据进行增加, 删除, 修改时, 索引也需要动态的维护, 降低了数据的维护速度.
使用原则
通过上面说的优点和缺点, 我们应该可以知道, 并不是每个字段度设置索引就好, 也不是索引越多越好, 而是需要自己合理的使用.
并不是所有索引对查询都有效
并不是所有索引对查询都有效, SQL 是根据表中数据来进行查询优化的, 当索引列有大量数据重复时, SQL 查询可能不会去利用索引, 如一表中有字段 sex,male,female 几乎各一半, 那么即使在 sex 上建了索引也对查询效率起不了作用.
索引并不是越多越好
索引固然可以提高相应的 select 的效率, 但同时也降低了 insert 及 update 的效率, 因为 insert 或 update 时有可能会重建索引, 所以怎样建索引需要慎重考虑, 视具体情况而定. 一个表的索引数较好不要超过 6 个, 若太多则应考虑一些不常使用到的列上建的索引是否有必要.
避免更新聚簇索引数据列
应尽可能的避免更新 clustered 索引数据列, MySQL 默认的 clustered 索引为主键, 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序, 一旦该列值改变将导致整个表记录的顺序的调整, 会耗费相当大的资源. 若应用系统需要频繁更新 clustered 索引数据列, 那么需要考虑是否应将该索引建为 clustered 索引.
经常更新的表就避免对其进行过多的索引
对经常更新的表就避免对其进行过多的索引, 对经常用于查询的字段应该创建索引.
数据量小的表最好不要使用索引
数据量小的表最好不要使用索引, 因为由于数据较少, 可能查询全部数据花费的时间比遍历索引的时间还要短, 索引就可能不会产生优化效果.
避免在不同值少的列上加索引
在一不同值少的列上 (字段上) 不要建立索引, 比如在学生表的 "性别" 字段上只有男, 女两个不同值. 相反的, 在一个字段上不同值较多可以根据需要建立索引.
根据业务需求建立索引
索引的建立要根据业务特点进行, 不能凭空想象的设置索引. 经常作为查询条件的列才有建立索引的必要性.
来源: http://www.jianshu.com/p/0a0d0d1cb717