MYSQL索引的使用

2018年5月25日09:07:38 发表评论

MYSQL索引的使用

PHP博客分享关于MYSQL数据库中索引的使用,以及各索引的区别。在开发时经常遇到大数据查询缓慢等情况,这时候需要给相对应的字段添加索引来增加sql的执行速度。

一.索引概述

索引本质上是表字段的有序子集,它是提高查询速度最有效的方法。一个没有建立任何 索引的表,就相当于一本没有目录的书,在每次查询时就会进行全表扫描,这样会导致查询 效率极低、速度也极慢。如果建立索引,那么就好比一本添加的目录,通过目录的指引,迅 速翻阅到指定的章节,提升的查询性能,节约了查询资源。 由于引擎的关系,MyISAM 表是将数据行存放在数据文件里,索引值存放在索引文件 里。索引就是一个排好序的键值对数组,查询时通过这种键值对快速找到对应的数据。对于 InnoDB 表来说,它的索引也是排好序的数组,但它的数据行与索引值存放在同一个文件里。

二.索引种类

从索引的定义方式和用途中来看,所以一共有四种索引:

1.普通索引(Index),用于提升查询效率;

2.唯一索引(Unique),除了提升查询效率还要求字段值不得重复;

3.主键索引(PrimaryKey),唯一性且不得为空的索引;

4.全文索引(Fulltext),用于在大量文本搜索中建立的索引。

PS:这里主键索引和唯一索引的区别在于:主键索引不能为空值,唯一索引允许空值; 主键索引在一张表内只能创建一个,唯一索引可以创建多个。主键索引肯定是唯一索引,但 唯一索引不一定是主键索引。

相比单表查询,如果没有建立索引,最多就是全表扫描一边。但对于多表查询时,索引 的重要度就要大很多。比如三张表的联合查询时,如果没有建立索引,将是三张表数据行的 乘积。比如:

//假设每个表有 1000 条数据,那么需要扫描 1000*1000*1000=10 亿次 WHEREt1.c1=t2.c2ANDt1.c1=t3.c3;
如果给 c2,c3 分别建立了索引,那么充其量只扫描 t1 一个全表扫描,这样另外两张表 的 1000*1000=100 万次的乘积扫描量就减少了,大大的提高了查询速度。

虽然索引看上去那么美好,但凡事都有两面性,在提高效率和速度的同时却增加了某些负担:

1.索引需要使用更多的磁盘空间,索引越多,占用磁盘越多,有时甚至比表占用的多;

2.索引在提升查询速度的同时,降低了插入、更新和删除的操作速度,因为它们多了一 项工作,就是在增删改的过程中更新索引。索引越多,速度越慢。 所以,索引优化是一门比较大的学问,需要根据业务、环境、配置等一些列问题进行最 右设计,需要较长时间的学习和实践才能有所体会。

三.索引操作

1.创建主键索引 这个索引比较简单,我们一直在使用,只要创建一个无符号整型且自动增长的列,然后 设置成主键即可。

//通过 EXPLAIN 语句查看索引状态 EXPLAIN SELECT * FROM think_user WHERE id=1;
2.创建普通或唯一索引 直接进入 navicat 设计表的第二栏,选择一个字段(比如 user 字段),添加一个 Nomral(普 通索引)或 Unique(唯一索引)。

//通过 EXPLAIN 语句查看索引状态 EXPLAIN SELECT * FROM think_user WHERE user='蜡笔老新';
//查看表所有索引情况 SHOW INDEX FROM 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 最频繁的放置在左边。

x

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: