索引概述
索引本质上是表字段的有序子集,它是提高查询速度最有效的方法。而索引即目录。
MyISAM引擎是将数据行放在数据文件里,索引值存放在索引文件里。
InnoDB引擎它是索引也是排好序的数组,但它的数据行与索引值存放在同一个文件里。
索引种类
一般有四种索引:
1、普通索引(Index),用于提示查询效率;
2、唯一索引(Unique),除了提升查询效率还要求字段值不得重复;
3、主键索引(Primary Key),唯一性且不得为空的索引;
4、全文索引(Fulltext),用于在大量文章搜索中建立的索引。
PS:这里主键索引和唯一索引的区别在于:
主键索引不能为空值,唯一索引允许控制;
主键索引在一张表内只能创建一个,唯一索引可以创建多个;
主键索引肯定是唯一索引,但唯一索引不一定是主键索引。
相比单表查询,如果没有建立索引,最多就是全表扫描一遍。但对于多表查询时,索引的重要度就要大很多,比如三张表的联合查询时,如果没有建立索引,将会是:
//假设每张表有1000条数据,那么需要扫描1000*1000*1000=10亿次。
WHERE t1.c1=t2.c2 AND t1.c1=t3.c3;
如果给c2,c3分别建立了索引,那么只扫描t1一个全表即可。
索引在 提高效率和速度的同时也增加了某些负担:
1、索引需要使用更多的磁盘空间,索引越多,占用磁盘越多,有时候甚至比表占用的多;
2、索引在提升查询速度的同时,降低了插入、更细和删除的操作速度,因为它们多了一项工作,就是在增删改的过程中更新索引。索引越多,速度越慢;
所以,索引优化很重要,需要根据业务、环境、配置等一系列问题进行最优设计。
索引操作
1、创建主键索引
该索引较简单,我们一直在使用,只要创建一个无符号整型且自动增长的列,然后设置成主键即可
//通过EXPLAIN语句查看索引状态
EXPLAIN SELECT * FROM think_user WHERE id =1;
PS:索引的查询使用也很简单,比如用主键索引:
EXPLAIN SELECT * FROM think_user WHERE id=1;
//此时就会显示使用了主键索引。
2、创建普通或唯一索引
直接进入navicat设计表的第二栏,选择一个字段(比如user字段),添加一个Normal(普通索引)或Unique(唯一索引)。
//通过Explain语句查看索引状态
EXPLAIN SELECT * FROM think_user WHERE user='张三';
//查看表所有索引状态
SHOW INDEX FORM think_user;
PS:在添加主键、唯一、普通索引的时候,我们会需要选择一种数据结构,默认为B-Tree(二叉树)结构,还有一种为HASH(哈希)结构。从理论上看,HASH索引的效率比B-Tree高,但常用的还是B-Tree,因为两种在各自查询的方式和范围有所不同。
1、Hash索引仅仅能满足“=”,“IN”,“<=>”查询,不能使用范围查询。
2、Hash索引无法被用来避免数据的排序操作;
3、Hash索引不能利用部分索引键查询;
4、Hash索引在任何时候都不能避免表扫描;
5、Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。索引,要使用HASH,就必须结合具体业务和大量经验。
3、全文索引
该索引一般来说对于我们用处不大,属于在大文章里进行对某些关键字进行索引,但由于中文分词识别问题,基本无法使用,如果是英文状态的话,建立全文索引后,可以依次SQL查询。
//SELECT * FROM think_user WHERE MATCH (intro) AGAINST ('teacher');
PS:如果搜索is,则无效,因为这是最常见的却无意义的词,被MySQL设置成停止词。
索引原则
1、不要过度索引,索引越多,占用空间越大,反而性能越慢;
2、只对WHERE子句中频繁使用的建立索引;
3、尽可能使用唯一索引,重复值越少,索引效果越强;
4、使用短索引,如果char(255)太大,应该给它指定一个前缀长度,大部分情况下前10位或20位的值基本是唯一的,那么久不要对整改列进行索引;
5、充分利用左前缀,这是针对复合索引,因为WHERE语句如果有AND并列,只能识别一个索引(获取记录最少的那个),索引需要使用复合索引,那么应该将WHERE最平凡的设置在左边。