先说索引的优略势
索引是一组排好序的数据结构
优势:检索速度快,减少磁盘i/o的交互,根据索引(B+树)的分组排序特点,能够提高范围查询和分组查询,排序的效率。
劣势:索引本身也是一张表,因此也会占用存储空间。索引的维护,创建都需要时间成本,如果表数据过大,成本也会随之增加。所以索引会降低添加。删除,修改的效率。
索引的分类
- 主键索引(聚簇索引)
- 组合索引
- 唯一索引
- 普通索引
- 全文索引
聚簇索引与非聚簇索引
聚簇索引又称为主键索引。因此一个表里面只能有一个聚簇索引。在聚簇索引中,索引是和数据行一块存储的。因此便可以进行一次 查询得到数据行。
非聚簇索引中索引和数据行是分开存储的,在查询的时候需要先查询到非聚簇索引,然后进行回表,通过非聚簇索引查询到主键进而查询到行。所以聚簇索引的查询效率高于非聚簇索引。
还有一种覆盖索引 是用来减少回表的产生。上面说到,如果使用非聚簇索引的时候,会产生回表。但是若是通过非聚簇索引就能直接得到想要查询的数据时,就不需要进行回表。比如下面的sql语句
select id from test where a = 1;
此时就用到了覆盖索引
联合索引(组合索引)
联合索引的使用需要注意最佳左前缀原则
例如创建(classID,name)联合索引,在执行一下sql语句的时候,索引就会失效
select className from stu where name = "zhangsan"
因为不满足索引的最佳做前缀原则,进而走全表扫描。
但是若是下面的sql语句,索引便会正常的执行
select className from stu where name = "zhangsan" and classID = "1";
这是因为全职匹配的时候,mysql优化器会为我们自动的优化
索引还有一下注意点
创建(a,b,c)索引,你可以这样用(a),(a,b),(a,b,c),但是不能这样用(b),(b,c),(c)
遇到范围查询的时候,索引也会失效
适合/不适合创建索引的情况
适合
- 频繁作为查询的字段
- 多表关联查询时,为关联查询的字段设置索引
- 查询排序的字段,应该设置索引
- 统计或分组函数创建索引
不适合
- 表记录少
- 频繁作为增删改的表
- 频繁修改的字段
- where字句用到不多的字段
- 字段过大
MyISAM与InnoDB区别
- InnoDB支持事务,MyISAM不支持事务
- InnoDB支持外键,MyISAM不支持
- InnoDB支持聚簇索引,MyISAM支持非聚簇索引
- InnoDB支持行锁,MyISAM支持表锁