主流数据库之索引及其例子

文章目录

前言

大家好，我是ice三分颜色。

个人主页：ice三分颜色的博客

本文讲了索引的相关知识，索引的概念，索引的分类，设置的基本原则，创建索引的三个方式和注意事项，索引的性能分析，删除索引等知识点。索引是重难点呀，很重要。

走过路过的小伙伴们点个赞和关注再走吧，欢迎评论区交流，努力什么时候开始都不算晚，那不如就从这篇文章开始！

大家一起成长呀！笔芯

索引

概述

概念

1.索引，是由数据库表中一列或多列组合而成的一种特殊的数据结构，利用索引可以快速查询数据库表中的特定记录信息（索引类似于字典中的目录，可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。多列索引类似于先按照一个条件，这个条件相同，那么就继续判断第二个条件，就是第二列索引以此类推）

2.索引影响数据性能，高效的索引能提高查询速度和性能。MySQL默认查询是根据搜索条件进行全表扫描（从第一个到最后一个叫全表扫描），遇到匹配条件的记录就加入到结果集合当中。若涉及多表连接、查询条件复杂、数据量大的时候，没有索引扫描执行的数据量就会很大，很慢。

3.索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据行的存储位置指针。（数据库中索引的形式与书的目录相似，键值就像目录中的标题，指针相当于页码。索引能像目录一样快速查找内容（表数据），不必扫描整个数据表找。）

4.索引是依赖于表建立的，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。

5.索引一旦创建，将由数据库自动管理和维护。（例如，向表中插入、更新和删除一条记录时，数据库会自动在索引中做出相应的修改。执行查询时，查询优化器会对可用的多种数据检索方法的成本进行估计，从中选用最有效的查询计划。）

6.索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。（数据量很少的时候，没必要加索引）

在数据库中使用索引的优缺点:

优点：1.加速数据检索:索引能够以—列或多列值为基础实现快速查找数据行。2.优化查询:查询优化器依赖于索引起作用，索引能够加速连接、排序和分组等操作。3.强制实施行的唯一性:通过给列创建唯一索引，可以保证表中的数据不重复。

缺点：1.虽然索引提高了查询速度，却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存索引。2.建立索引会占用磁盘空间的索引文件。

索引分类

MySQL的索引可分为如下5类：普通索引、唯一性索引、主键索引、全文索引、空间索引。（一般来说前三种就可以了，常用的是主键索引和唯一性索引）

普通索引

普通索引(index)是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。索引的关键字是index。

唯─性索引

唯一索引(unique)列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。在一个表上可以创建多个unique索引。

主键索引

主键索引(primary key)是一种特殊的唯一索引，不允许有空值。一般是在建表的同时创建主键索引。也可通过修改表的方法增加主键，但一个表只能有一个主键索引。（一般是和实际物理顺序保持一致的）

**全文索引 **

全文索引(fulltext)是指在定义索引的列上支持值的全文查找，允许在索引列中插入重复值和空值。该索引只对char、varchar和text类型的列编制索引，并且只能在

MylSAM存储引擎表中编制。在MySQL默认情况下，对于中文作用不大。

空间索引

空间索引(spatial)是对空间数据类型的字段建立的索引。MySQL中的空间数据类型有4种:geometry、point、linestring和polygon。空间索引只有在存储引擎MyISAM的表中创建。对于初学者来说，这类索引很少会用到。（我们存储引擎用的是innoDB）

其他分类

如果按照创建索引键值的列数分类，索引还可以分为单列索引和复合索引。

如果按照存储方式分类，可分为二叉树（B-Tree）索引和Hash索引。

索引设置的基本原则

在数据表中创建索引时，为使索引的使用效率更高，必须考虑在哪些字段上创建索引和创建什么类型的索引。原则如下：

1.一个表创建大量索引，会影响insert、update和delete语句的性能。应避免对经常更新的表创建过多的索引，要限制索引的数目。

2.若表的数据量大，对表数据的更新较少而查询较多，可以创建多个索引来提高性能。

3.经常需要排序、分组和联合操作的字段一定要建立索引，即将用于join、where判断和order by排序的字段上创建索引。

4.在视图上创建索引可以显著的提升查询性能。

5.尽量不要对数据库中含有大量重复值的字段建立索引，在这样的字段上建立索引有可能降低数据库的性能。

6.在主键上创建索引。在InnoDB中如果通过主键来访问数据效率是非常高的。每个表只能创建一个主键索引。

7.要限制索引的数目。对于不再使用或者很少使用的索引要及时删除。

创建索引

创建索引通常有3种命令方式：使用CREATE INDEX语句来创建索引、创建表时附带创建索引、通过修改表来创建索引。

使用CREATE INDEX语句建立索引

格式：CREATE [unique|fulltext|spatial] INDEX 索引的名称 ON 表名（表名的哪一列的列名1，列名2…）

例：为employee表的ename列上建立一个升序普通索引ename_index。

CREATE INDEX ename_index ON employee(ename ASC);

例：在employee表的ename列上建立一个唯一性索引ename_uni_index。

CREATE UNIQUE INDEX ename_uni_index ON employee(ename);

例：在employee表的empno和ename列上建立一个复合索引empno_ename_index。

CREATE INDEX empno_ename_index ON employee(empno,ename);

可以使用show index from table_name语句查看表中已创建的索引

SHOW INDEX FROM employee;如下

我们也可直接在navicat中employee表右键设计表处查看索引，如下

创建表时创建索引

例：创建dept_index表时为dname字段建立一个唯一性索引dname_index，为loc字段的前3个字符创建一个前缀索引loc_index。

CREATE TABLE IF NOT EXISTS dept_index (

deptno INT(2),

dname VARCHAR(14),

loc VARCHAR(13),

PRIMARY KEY (deptno),

UNIQUE INDEX dname_index(dname),

INDEX loc_index(loc(3))

);

修改表时创建索引

例:在dept_index表上建立deptno和dname的复合索引。

ALTER TABLE dept_index ADD INDEX deptno_dname_index(deptno, dname);

查看dept_index表已创建的索引

Show index from dept_index;

创建索引注意事项

1.只有表的所有者才有权限给表创建索引。

2.索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。(主键索引必定是唯一的，唯一性索引不一定是主键。一张表上只能一个主键，但可以有一个或者多个唯—性索引。)

3.当给表创建unique约束时，MySQL会自动创建唯一索引。创建唯一索引时，应保证创建索引的列不包括重复的数据，并且不要有两个及以上的空值(null)。因为创建索引时将两个空值也视为重复的数据，如果有这种数据，必须先将其删除，否则索引不能被成功创建。也就是说唯一索引只允许有一个空值。

索引性能分析

MySQL5.0之后自带query诊断分析工具“Show Profiles”,可以定位出一条SQL语句执行的各种资源消耗情况（如CPU，IO等)，以及该SQL执行所耗费的时间。

要使用首先需要开启profile

默认数据库是不开启的，且变量profiling是用户变量，每次都要重新启用

查看profile是否开启的

show variables like "%pro%";

设置开启profile

（因为查看，知道我的是开启的，所以就不需要再进行这一步）

set profiling = 1;

查看SQL执行的耗时详情

show profiles; -- 查看所有SQL的总的执行时间。

例：对员工姓名在加入索引前后进行执行时间性能分析（过程如下）

-- 通过employee表创建表emp_index

CREATE TABLE emp_index AS SELECT * FROM employee;

-- 开启profile

set profiling = 1;

-- 查询未加索引前的员工姓名

SELECT ename FROM emp_index;

-- 查看sQL的执行时间

show profiles;

-- 为ename添加索引

CREATE INDEX ename_index ON emp_index(ename);

-- 查询加索引后的员工姓名

SELECT ename FROM emp_index;

-- 查看最近一条SQL的执行时间

show profiles;

结果如下：

删除索引

删除索引是不用的索引，要及时进行更新和维护，因为索引是有上限的。可以通过DROP语句和ALTER TABLE语句删除。

利用drop index语句删除索引

格式： DROP INDEX 索引名 ON 表名 ;

例:

DROP INDEX ename_index ON employee;

利用alter table语句删除索引

可以先用CREATE INDEX ename_index ON employee(ename ASC);重新创建一下索引ename_index,之后再作为测试用alter table删除

格式：

ALTER TABLE 表名 [DROP PRIMARY KEY| DROP INDEX 索引名|DROP FOREIGN KEY fk_symbol

例：

ALTER TABLE employee DROP INDEX ename_index;