翼度科技»论坛 编程开发 mysql 查看内容

索引初识

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
索引(基础)

一、索引介绍

1.1、前言

在数据库中,执行如下语句时:
  1. select * from emp where id=1000;
复制代码
mysql 是从第一条记录开始遍历,直至找到 id = 1000 的数据,然而这样查询的效率低,所以 mysql 允许通过建立索引来加快数据表的查询和排序。
1.2、索引概念

数据库的索引类似字典中的拼音,是对数据库表中一列或多列的值进行排序后的一种结构。
作用:就是提高表中数据的查询速度。
1.3、索引分类


  • 普通索引:
    不加任何参数,直接通过 key 或 index 来创建的索引。是 mysql 中的基本索引类型,可创建在任意数据类型中。其值是否唯一、非空,则由字段本身的约束决定。
  • 唯一索引:
    在定义索引时,加上 unique 即可,该索引所在字段的值必须唯一。但允许有空值。在一张数据表里可以有多个唯一索引
  • 全文索引
    在定义索引时,加上 fulltext 即可,该索引只能创建在 char、varchar 、text 类型的字段上。
  • 单列索引
    是在表中单个字段上创建索引,可以为普通索引、唯一索引等,只要保证该索引只对应表中一个字段即可。
  • 多列索引
    是在表中多个字段上创建索引,但是只有在查询条件中使用了这些字段中的第一个字段时,该索引才会被使用。
  • 空间索引
    在定义索引时,加上 spatial 即可,该索引只能创建在空间数据类型的字段上。
    mysql 中空间类型索引有 4 中:GEOMETRY、POINT、LINESTRING 和 POLYGON 。但是当使用空间索引时,该字段的约束必须为 not null且数据库的存储引擎为  MyISAM 中使用。
注:主键:primary key 也可作为索引
但本质上看其是一种约束,而索引是一种数据结构,用来提升查询效率
二、创建索引

2.1、创建表时

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.     [unique|fulltext|spatial] index|key [别名] (字段) [ASC|DESC]
  4. );
复制代码
[]:中的值表示可选项
[unique|fulltext|spatial]:分别表示:唯一索引、全文索引、空间索引
[ASC|DESC]:升序、降序
2.1.1、创建普通索引

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.      index|key [别名] (字段) [ASC|DESC]
  4. );
复制代码
例如这里需要在 t1 表中的 id  创建索引:
  1. create table t1(
  2.         id int,
  3.     name varchar(10),
  4.     age int,
  5.     index(id)
  6. );
复制代码
通过下述命令查看是否创建成功:
  1. show create table t1\g
复制代码

通过上图可知,t1 表中成功创建索引。
可再通过下述命令查看是否使用:
  1. explain select * from t1 where id=1\g
复制代码

通过图中的 possible_keys 与 key 知 其值都为 id ,表明 id 索引已经存在并开始使用。
2.1.2、创建唯一索引

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.      unique index|key [别名] (字段) [ASC|DESC]
  4. );
复制代码
例如这里需要在 t2 表中在 id 上创建 unique_id 索引:
  1. create table t2(
  2.         id int,
  3.     name varchar(7),
  4.     score float,
  5.     unique index unique_id (id ASC)
  6. );
复制代码

2.1.3、创建全文索引

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.      fulltext index|key [别名] (字段) [ASC|DESC]
  4. );
复制代码
例如这里需要在 t3 表上根据 name 字段来创建全文索引:
  1. create table t3(
  2.         id int,
  3.     name varchar(7),
  4.     score float,
  5.     fulltext index fulltext_name (name)
  6. );
复制代码

2.1.4、创建单列索引

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.      index|key [别名] (字段1,字段2......) [ASC|DESC]
  4. );
复制代码
例如需要在 t4 表上根据 name 创建索引
  1. create table t4(
  2.         id int,
  3.     name varchar(7),
  4.     score float,
  5.     index single_name (name(7))
  6. );
复制代码

2.1.5、创建多列索引

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.      index|key [别名] (字段1,字段2......) [ASC|DESC]
  4. );
复制代码
例如需要在 t5 表上创建多列索引:
  1. create table t5(
  2.         id int,
  3.     name varchar(7),
  4.     score float,
  5.     index multi (id,name(7))
  6. );
复制代码

注意:只有当在查询条件中使用索引字段的第一个字段时,才会有效。验证如下:
这里通过 id 来查询:
  1. explain select * from t5 where id=1\g
复制代码

当通过 name 来查询时:
  1. explain select * from t5 where name='张三'\g
复制代码

这时,发现通过 name 查询的结果中:possible_keys | key 皆为 null,表明索引并为使用。
2.1.6、创建空间索引

语法:
  1. create table 表名(
  2.         字段 数据类型 约束
  3.      spatial index|key [别名] (字段1,字段2......) [ASC|DESC]
  4. );
复制代码
例如需要在 t6 表上创建空间索引:
  1. create table t6(
  2.         id int,
  3.     space geometry not null,
  4.     spatial index sp (space)
  5. );
复制代码

2.2、创表后

1、通过 create 创建

语法:
  1. create [unique|fulltext|spatial] index 索引名 on 表明 (字段名[长度]) [asc|desc];
复制代码
这里以创建唯一索引为例:
需要在 t7 表上根据 bookid 创建唯一索引
  1. create unique index unique_id on book(bookid);
复制代码
其余类似。
2、通过 alter 创建

语法:
  1. alter table 表名 add [unique|fulltext|spatial] index 索引名 (字段名[长度]) [asc|desc];
复制代码
这里以创建唯一索引为例:
需要在 t7 表上根据 bookid 创建唯一索引
  1. alter table book add unique index unique_id (bokid);
复制代码
其余类似。
三、删除索引

由于索引会占用一定的磁盘空间,所以为了避免影响数据库性能,需删除不使用的索引。
3.1、alter

语法:
  1. alter table 表名 drop index 索引名
复制代码
这里以删除唯一索引为例:
需要在 t7 表上根据 bookid 删除唯一索引:
  1. alter table book drop index unique_id;
复制代码
其余类似。
3.2、drop

语法:
  1. drop index 索引名 on 表名;
复制代码
这里以删除唯一索引为例:
需要在 t7 表上根据 bookid 删除唯一索引:
  1. drop index unique_id on book;
复制代码
其余类似。

来源:https://www.cnblogs.com/xiarongblogs/p/17397801.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

上一篇: 索引初识

下一篇: 数据操作(基础)

举报 回复 使用道具