索引
1 索引 :查询数据的一种方式 索引的主要目的就是为了提高查询数据的效率。我们在做数据查询时,使用索引和不使用索引,效率是千差万别。
所以索引可以分为两种: 聚集索引 非聚集索引
- 聚集索引 : 索引页的数据排序和数据页的排序采用同种的排序方式
典型的聚集索引 比如 新华字典的按拼音查询 - 非聚集索引: 他们的排序方式不一样 偏旁部首查询
我们都知道,数据库都是一 ”数据表“ 来管理数据,一张数据表可以有多个索引,但一张数据表只存在一个聚集索引。
2 索引的分类
- 普通索引 index
- 唯一性索引 unique index
- 主键索引
- 外键索引
- 全文索引 fulltext index
- 组合索引
3 查看索引的类型的SQL 语句
- explain select *from 表名
4 在查询数据的过程中 也可能没有用到索引或者索引失效
- a 没有用到索引
查询时,查询的数据列上没有索引 - 索引失效
(1) 查询语句中不能使用带有 like 关键字的语句
(2)查询时索引列不能参与运算 (算数 逻辑)
例如:explain select * from student where stuId=stuId+2
(3) 查询时不能有分页语句的查询 即带有 limit的查询
都会引起索引的失效
5 索引按照产生的方式可以分为
- Hash索引 效率高但不能用作范围查询(无序)
- B-tree索引 效率低但可以用作范围查询(有序)
- 两者的区别
1、B-Tree索引
索引存储的值按索引列中的顺序排列。可以用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。如果使用索引,必须保证按索引最左边前缀进行查询。由于B树中节点是顺序存储的,可以对查询结果进行order by。限制:1)查询必须从索引的最左边的列开始2)不能跳过某一索引列。3)存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23’,则该查询只会使用索引中的前两列,因为LIKE是范围查询。
2、Hash索引
MySQL中只有Memory存储引擎显示支持hash索引,是默认索引类型,它也支持B-Tree索引。如果多个值有相同的hash code,索引把它们的行指针用链表保存到同一个hash表项中。
因为索引自己仅仅存储很短的值,所以索引非常紧凑,hash值不取决于列的数据类型,
int列的索引和长字符串列的索引一样大。
3、总结
HASH索引适合等式比较的操作,不能用来加速order by操作,也不能确定在两个值之间大约有多少行,会影响一些查询的执行效率。而且只能使用整个关键字来搜索一行。BTREE索引,使用大于,小于,BETWEEN,不等于,LIKE等操作符的时候都可以用。对索引字段进行范围查询的时候,只有BTREE索引可以通过索引访问。HASH索引实际上是全表扫描的。
6 创建索引的方式
-
普通索引: create index 索引名 on 表名
-
唯一性索引和主键索引 可以添加主键约束和唯一性约束的方式添加
也额可以:create unique index 索引名 on 表名 -
全文索引: create fulltext index 索引名 on 表名(索引列)
-
组合索引: create index 索引名 on 表名(索引列,索引列)
** 索引的使用是DBMS自动匹配的,当一个数据列上有多个索引时 ,若该查询列上有索引,则使用该索引;若该列上有多个索引,则使用效率高的索引;若没有索引,则不使用 **