索引

概述

索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。

EXPLAIN

用于观察SQL执行计划，主要看有没有执行索引
主要看两列的结果(key和possible_keys)

分类

  1. 单值索引
    一个索引只包括一个列，一个表可以有多个列
    1. CREATE INDEX 索引名 ON dept(表名)#普通索引即为单值索引
  1. 唯一索引
    索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
    1. -- 唯一索引,修改表结构,添加唯一索引
    2. ALTER TABLE emp ADD UNIQUE(ename)
  1. 复合索引
    一个索引同时包括多列
    1. -- 复合索引
    2. ALTER TABLE emp ADD INDEX 索引名(要添加索引的列，····，···)
    3. ALTER TABLE emp ADD INDEX many_index(ename,job,hiredate)

最左特性


复合索引中所查询的语句必须要包含最左边的字段，否则索引不会生效

索引的语法

创建索引

主键会自动创建索引

  1. CREATE INDEX job_index ON dept(job)
  2. CREATE INDEX 索引名 ON dept(表名)

查看索引

  1. SHOW INDEX FROM dept

使用索引

  1. EXPLAIN #用于观察SQL执行计划，主要看有没有执行索引
  2. #主要看两列的结果(key和possible_keys)
  3. SELECT * FROM emp WHERE job = '经理' #底层使用job索引
  4. EXPLAIN
  5. SELECT * FROM emp WHERE empno = 100 #底层使用主键索引
  6. EXPLAIN
  7. SELECT * FROM emp WHERE ename = 'jack' #底层使用唯一索引
  8. EXPLAIN
  9. SELECT * FROM emp WHERE ename = 'jack' AND job = '副总'  #复合索引生效
  10. EXPLAIN
  11. SELECT * FROM emp WHERE hiredate = '2019-03-19' #复合索引失效，没有使用最左特性

优缺点

优点

  • 索引是数据库优化
  • 表的主键会默认自动创建索引
  • 每个字段都可以被索引
  • 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
  • 索引事先对数据进行了排序，大大提高了查询效率

缺点

  • 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
  • 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
    虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需
  • 更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
  • 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去