专栏首页A2DataMySQL 之索引

MySQL 之索引

本文标识 : MQ0022

本文编辑 : 长安月下赏美人儿

编程工具 : MySQL、DBeaver

阅读时长 : 9分钟

前言

之所以现在才来分享索引,完全是因为小编的过错。在一开始接触到 MySQL 学习的时候,看到了索引的相关内容。但着实吓人了些许,小编就这样怂怂的被吓跑了,想着先分享一些容易操作的,再来进行系统化的梳理。不然的话,很容易在一开始,就被错综复杂的基础信息吓跑,且还不明白说得是啥。

什么是索引

通俗的讲,索引相当于数据表的目录。譬如,咱们买到一本书,会先看目录,看看都写了啥,都有啥,对啥感兴趣。这是一个道理,拥有了索引,就可以非常快速的找到感兴趣的内容。

建立索引有啥好处

索引的优点是可以提高检索数据的速度,但是会减慢写入速度。毕竟世界上任何事物都具有两面性,哪有完美的事儿!此岸永远是残缺的,否则彼岸就要坍塌。

索引选取原则

不是每个人都适合当皇帝!

欲戴皇冠,必承其重!

且,索引必须“短而精,勿过度!”

1、索引选取类型

  • 占用空间较少的数据型优选
  • 简单的整型数据优于字符型
  • 避开神坑 NULL 以及它的兄弟 NOT NULL

2、索引选取字段

  • 数据表的主键、外键
  • 常与其他数据表进行连接的字段
  • 常出现在 WHERE 子句中的字段
  • 常用于排序的字段

3、索引不适用场景

  • 数据表中数据未超过 300 条
  • 查询中很少使用到的数据列
  • 数据值很少的列
  • 数据类型为 TEXT 、IMAGE、BIT 的数据
  • 字段经常性修改,且很少用于检索数据
  • 不常出现在 WHERE 子句及排序的字段

索引的类型

单列索引

1、普通索引(加速查询,无限制,最常用)

#基本语法
CREATE INDEX index_name ON test_table(username(length)); 

注意:如果是 CHAR,VARCHAR 类型,length 可以小于字段实际长度;如果是 BLOB 和 TEXT 类型,必须指定 length 。

  • 创建添加索引

(1)第一种情况:在创建数据表的同时创建索引

CREATE TABLE test_table (
  id int(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(50) NOT NULL COMMENT '姓名',
  email VARCHAR(72) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

(2)第二种情况:在已经存在的数据表上创造索引

CREATE INDEX index_name ON test1.test_table1(name);

#注意:创建索引时数据类型为 BLOB 或 TEXT,必须指定length。
CREATE INDEX index_name ON test1.test_table2(message(200));

(3)第三种情况:修改表结构添加索引

ALTER TABLE test1.test_table3 ADD INDEX index_name(name);
  • 查看索引
SHOW INDEX FROM test1.test_table;

数据结果:

  • 删除索引
DROP INDEX index_name ON  test1.test_table;
#或
ALTER TABLE test1.test_table DROP INDEX index_name;

输入删除表索引指令数据结果:

2、唯一索引(加速查询,列值唯一,可为 NULL)

#基本语法
CREATE UNIQUE INDEX index_name ON test_table(username(length)) 
  • 创建和添加唯一索引

(1)第一种情况:在创建数据表的同时创建唯一索引

CREATE TABLE test_table (
  id int(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(50) NOT NULL COMMENT '姓名',
  UNIQUE index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

(2)第二种情况:在已经存在的数据表上创造唯一索引

CREATE UNIQUE INDEX index_name ON test1.test_table1(name);

(3)第三种情况:修改表结构添加索引

ALTER TABLE test1.test_table3 ADD UNIQUE index_name(name);

3、主键索引(唯一索引的特例,不允许有空值)

注:一个数据表只有一个主键,且会在创建数据表的时候直接创建主键索引

CREATE TABLE table2(
id INT NOT NULL,
name CHAR(10),
PRIMARY KEY(id)
);

输入查看表索引指令数据结果:

组合索引

通俗的讲,就是在多个字段上创建一个索引

(1)第一种情况:创建数据表的同时创建组合索引

CREATE TABLE table3(
id INT NOT NULL,
name CHAR(20) NOT NULL,
age INT NOT NULL,
message VARCHAR(255),
INDEX index_name(id,name,age)
);

输入查看表索引指令数据结果:

(2)第二种情况:在已经存在的数据表上创建组合索引

ALTER TABLE table4 ADD INDEX index_name(id, age, message);

输入查看表索引指令数据结果:

全文索引

用于全文搜索,只有 MyISAM 存储引擎支持全文索引,且只为 CHAR、VARCHAR 和 TEXT 列服务。索引总是对整个列进行,不支持前缀索引。

  • 创建或添加全文索引

(1)第一种情况:创建数据表的同时创建全文索引

CREATE TABLE table5(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX index_name(info)
)ENGINE=MyISAM;

(2)第二种情况:在已经存在的数据表上创建全文索引

ALTER TABLE table_name ADD FULLTEXT (column);
  • 全文搜索实例

在使用全文搜索时,需要借助 MATCH 函数,搜索的关键字默认至少要4个字符,如果搜索的关键字太短就会被忽略掉。

SELECT * FROM table5 WHERE MATCH(info) AGAINST('Zhangsan');

数据结果:

空间索引

小编查询了很多资料,也是对这个应用感到困惑。查看了大量的习题,也鲜少遇到相关信息。但还是想放在这里,大家一起简单的学习一下,这样子,今后遇到相关的问题至少有一个印象。

(1)第一种情况:创建数据表的同时创建空间索引

CREATE TABLE table6(
hahaha GEOMETRY NOT NULL,
SPATIAL INDEX index_name(hahaha)
) ENGINE = MyISAM;

(2)第二种情况:在已经存在的数据表上创建空间索引

ALTER TABLE table6 ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY] [索引名] (索引字段名)[ASC|DESC];

写在后面,关于索引,是一个非常重要且非常有趣的版块,小编目前还在持续挖掘这份快乐,希望你也是!


本文分享自微信公众号 - DataScience(gh_b52493d7af49),作者:长安月下赏美人儿

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-11-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Apache Hive Index

    A2Data
  • 【Python基础】一个字符串的N种玩法

    一般大家写程序,都是用来做一些爱做的事情,其中少不了的是对数据进行分类。今儿个咱们开始认认识python中的第一个数据类型:字符串。

    A2Data
  • 【Python基础】函数的返回

    很多时候,函数并非直接输出,相反,它可以处理一些数据,并返回一个值或者一组值,称之为返回值。

    A2Data
  • mysql数据库的索引类型

    CREATE TABLE mytable ( name VARCHAR(32) , INDEX index_mytable_name (name) );

    哲洛不闹
  • 玩转Mysql系列 - 第24篇:如何正确的使用索引?

    学习索引,主要是写出更快的sql,当我们写sql的时候,需要明确的知道sql为什么会走索引?为什么有些sql不走索引?sql会走那些索引,为什么会这么走?我们需...

    路人甲Java
  • 零基础Python教程039期 重修列表基本操作

    上一节中,我们对python的循环控制语句做了基本的练习,也掌握了一些技巧。本节开始,我们继续新的知识点。

    刘金玉编程
  • 【Oracle】-【创建索引】-创建索引的操作原理与一些体会

    1、将index key的data读到cache。如果之前这部分数据未读到DB Cache,那么此时可能有db file scatter read write的...

    bisal
  • 函数索引构成虚拟隐藏列

    《enq: TM - contention锁争用的解决》中提到了SYS_NC00004$,理解不是很明白,于是在OTN上发了帖子咨询了下,

    bisal
  • 使用TensorFlow一步步进行目标检测(3)

    进行到这一步,我们已选择了预训练模型,并将现有数据集转化为单个TFRecord文件。但是,如果我们找到的数据集与即将使用的目标检测模型不完全匹配,而我们希望获得...

    云水木石
  • 跟松哥学 SpringMVC(1/2)

    Spring Web MVC 是一种基于 Java 的实现了 Web MVC 设计模式的请求驱动类型的轻量级 Web 框架,即使用了 MVC 架构模式的思想,将...

    江南一点雨

扫码关注云+社区

领取腾讯云代金券