什么是索引

MySQL查询必须从第一条记录开始读完整个表,直到找出相关的行。
表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,
MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

建立索引会占用磁盘空间的索引文件

索引的作用

  • 加快搜索,快速找出在某个列中有一特定值的行
  • mysql中的primary key,unique,联合唯一也都是索引,
    这些索引除了加速查找以外,还有约束的功能

索引类型

normal,unique,fulltext,spatial 的区别是什么?
索引的类别由建立索引的字段内容特性来决定,通常normal最常见
索引.jpg

normal:普通索引

unique:唯一索引,不允许重复的索引
如果该字段信息保证不会重复,例如身份证号用作索引时,可设置为 unique

主键索引:primary key :加速查找+约束(不为空且唯一)
唯一索引:unique:加速查找+约束 (唯一)
组合索引,即一个索引包含多个列。
primary key(id,name) 联合主键索引
unique(id,name) 联合唯一索引
index(id,name) 联合普通索引

fulltext:全文搜索的索引
用于搜索很长一篇文章的时候效果最好
搜索比较短的文本,如果就一两行字的,普通的 INDEX 也可以

spatial:空间索引

索引方法:

  1. BTREE B树,可以是多叉树,主流使用
  2. b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
  3. BTree可用于 “>、 < 、=” 查询,例如 where id = 100
  4. HASHkey,value 这种方式对范围查询支持得不是很好
  5. 查询单条快,范围查询慢
  6. Hash 不能做order by 排序,不能做 like模糊查询
  7. RTREE
  8. 不同引擎对索引方法的支持
  9. InnoDB 支持事务,支持行级别锁定,支持 B-treeFull-text 等索引,不支持 Hash 索引;
  10. MyISAM 不支持事务,支持表级别锁定,支持 B-treeFull-text 等索引,不支持 Hash 索引;
  11. Memory 不支持事务,支持表级别锁定,支持 B-treeHash 等索引,不支持 Full-text 索引;
  12. NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-treeFull-text 等索引;
  13. Archive 不支持事务,支持表级别锁定,不支持 B-treeHashFull-text 等索引;

慢查询的优化步骤

  1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  2. 1. where条件单表查,锁定最小返回记录表
  3. * 把查询语句的 where都应用到表中返回的记录数最小的表开始查起,
  4. * 单表每个字段分别查询,看哪个字段的区分度最高
  5. 2. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  6. 3. order by limit 形式的sql语句让排序的表优先查
  7. 4. 了解业务方使用场景
  8. 5. 加索引时参照建索引的几大原则
  9. 6. 观察结果,不符合预期继续从0分析

在实际操作应该选取表中哪些字段作为索引?

为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:

  • 选择唯一性索引 UNIQUE
  • 为经常需要排序、分组和联合操作的字段建立索引
  • 为常作为查询条件的字段建立索引
  • 限制索引的数目
  • 尽量使用数据量少的索引
  • 尽量使用前缀来索引
  • 删除不再使用或者很少使用的索引
  • 不推荐在同一列建多个索引

经常更新修改的字段不要建立索引针对 mysql的,因为字段更改同时索引就要重新建立,排序,
而Orcale好像是有这样的机制字段值更改了,它不立刻建立索引,排序索引,而是根据更改个数,时间段去做平衡索引这件事的。