MySQL 索引的优缺点以及创建索引的准则

一、为什么要创建索引呢（优点）

这是因为，创建索引可以大大提高系统的性能。

  • 第一， 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 第二， 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 第三， 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  • 第四， 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  • 第五， 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

二、建立方向索引的不利因素（缺点）

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。

  • 第一， 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  • 第二， 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  • 第三， 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

三、设计索引的准则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引。

  • 第一， 在经常需要搜索的列上，可以加快搜索的速度；
  • 第二， 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
  • 第三， 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 第四， 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
  • 第五， 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  • 第六， 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

  • 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  • 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
  • 第三， 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
  • 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

以上就是MySQL 索引的优劣以及创建索引的准则的详细内容，更多关于MySQL 索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • 导致MySQL索引失效的一些常见写法总结

    前言 最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验. 这次的话简单说下如何防止你的索引失效. 再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单.快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用

  • MySQL 函数索引的优化方案

    很多开发人员在使用MySQL时经常会在部分列上进行函数计算等,导致无法走索引,在数据量大的时候,查询效率低下.针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化. 1. MySQL5.7 MySQL5.7版本中不支持函数索引,因此 遇到函数索引的时候需要进行修改,否则即使查询的字段上有索引,执行时也无法使用索引而进行全表扫描,数据量大的表查询时间会比较长.具体案例如下: 1.1 创建测试表及数据 mysql> use testdb; Database changed

  • 数据库中聚簇索引与非聚簇索引的区别[图文]

    数据库中聚簇索引与非聚簇索引的区别[图文]

    在<数据库原理>里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关.正式因为如此,所以一个表最多只能有一个聚簇索引. 不过这个定义太抽象了.在SQL Server中,索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点.而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块.如下图: 非聚簇索引 聚簇索引 聚簇索引与非聚簇索引的本质区别到底是什么?什么时候用聚簇索引,什么时候用非

  • mysql聚簇索引的页分裂原理实例分析

    mysql聚簇索引的页分裂原理实例分析

    本文实例讲述了mysql聚簇索引的页分裂.分享给大家供大家参考,具体如下: 在MySQL中,MyISAM采用的是非聚簇索引的,InnoDB存储引擎是采用聚簇索引的. 聚簇结构的特点: 根据主键查询条目时,不用回行(数据就在主键节点下) 如果碰到不规则数据插入时,造成频繁的页分裂 为什么会产生页分裂? 这是因为聚簇索引采用的是平衡二叉树算法,而且每个节点都保存了该主键所对应行的数据,假设插入数据的主键是自增长的,那么根据二叉树算法会很快的把该数据添加到某个节点下,而其他的节点不用动:但是如果插入的

  • Mysql索引性能优化问题解决方案

    mysql 创建的优化就是加索引,可是有时候会遇到加索引都没法达到想要的效果的情况, 加上了所以,却还是搜索的全数据,原因是sql EXPLAIN SELECT cs.sid, -- c.courseFrontTitle, -- c.imgBig, cs.studyStatus, coi.fee, -- act.PROC_INST_ID_ AS processId, cs.createDTM, cs.payStatus, cs.isCompleted, cs.saleChannel, cs.is

  • MySQL btree索引与hash索引区别

    在MySQL中,大多数索引(如 PRIMARY KEY,UNIQUE,INDEX和FULLTEXT)都是在BTREE中存储,但使用memory引擎可以选择BTREE索引或者HASH索引,两种不同类型的索引各自有其不同的使用范围. B树索引具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN).相当于二分查找. 哈希索引只能做等于查找,但是无论多大的Hash表,查找复杂度都是O(1). 显然,如果值的差异性大,并且以等值查找(=. <.>.in)为主,Hash索引

  • 详解MySQL 聚簇索引与非聚簇索引

    1.聚集索引 表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致.对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页. 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种. 从物理文件也可以看出 InnoDB(聚集索引)的数据文件只有数据结构文件.frm和数据文件.idb 其中.idb中存放的是数据和索引信息 是存放在一起的. 2.非聚集索引 表数据存储顺序与索引顺序无关.对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,

  • MySQL 索引的优缺点以及创建索引的准则

    一.为什么要创建索引呢(优点) 这是因为,创建索引可以大大提高系统的性能. 第一, 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性. 第二, 可以大大加快数据的检索速度,这也是创建索引的最主要的原因. 第三, 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义. 第四, 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间. 第五, 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能. 二.建立方向索引的不利因素(缺点) 也许会有人

  • SQLSERVER 创建索引实现代码

    什么是索引 拿汉语字典的目录页(索引)打比方:正如汉语字典中的汉字按页存放一样,SQL Server中的数据记录也是按页存放的,每页容量一般为4K.为了加快查找的速度,汉语字(词)典一般都有按拼音.笔画.偏旁部首等排序的目录(索引),我们可以选择按拼音或笔画查找方式,快速查找到需要的字(词). 同理,SQL Server允许用户在表中创建索引,指定按某列预先排序,从而大大提高查询速度. • SQL Server中的数据也是按页(4KB)存放 • 索引:是SQL Server编排数据的内部方法.它

  • MySQL怎么给字符串字段加索引

    MySQL怎么给字符串字段加索引

    假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的: create table SUser(  ID bigint unsigned primary key,  email varchar(64),  ...  )engine=innodb; 由于要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句: select f1, f2 from SUser where email='xxx'; 如果 email 这个字段上没有索引,那么这个语句就只能做全表扫描. 1)那我可以在邮箱地址这个

  • python elasticsearch从创建索引到写入数据的全过程

    python elasticsearch从创建索引到写入数据 创建索引 from elasticsearch import Elasticsearch es = Elasticsearch('192.168.1.1:9200') mappings = { "mappings": { "type_doc_test": { #type_doc_test为doc_type "properties": { "id": { "

  • MySQL下使用Inplace和Online方式创建索引的教程

    MySQL各版本,对于add Index的处理方式是不同的,主要有三种: (1)Copy Table方式 这是InnoDB最早支持的创建索引的方式.顾名思义,创建索引是通过临时表拷贝的方式实现的. 新建一个带有新索引的临时表,将原表数据全部拷贝到临时表,然后Rename,完成创建索引的操作. 这个方式创建索引,创建过程中,原表是可读的.但是会消耗一倍的存储空间. (2)Inplace方式 这是原生MySQL 5.5,以及innodb_plugin中提供的创建索引的方式.所谓Inplace,也就是

  • 图文详解MySQL中两表关联的连接表如何创建索引

    图文详解MySQL中两表关联的连接表如何创建索引

    本文介绍了MySQL中两表关联的连接表是如何创建索引的相关内容,分享出来供大家参考学习,下面来看看详细的介绍: 问题介绍 创建数据库的索引,可以选择单列索引,也可以选择创建组合索引. 遇到如下这种情况,用户表(user)与部门表(dept)通过部门用户关联表(deptuser)连接起来,如下图所示: 表间关系 问题就是,在这个关联表中该如何建立索引呢? 针对该表,有如下四种选择: 针对于user_uuid建立单列索引idx_user 针对于user_dept建立单列索引idx_dept 建立组合

  • MySQL字符串索引更合理的创建规则讨论

    MySQL字符串索引更合理的创建规则讨论

    前言 针对使用MySQL的索引,我们之前介绍过索引的最左前缀规则,索引覆盖,唯一索引和普通索引的使用以及优化器选择索引等概念,今天我们讨论下如何更合理的给字符串创建索引. 如何更好的创建字符串索引 我们知道,MySQL中,数据和索引都是在一颗 B+树 上,我们建立索引的时候,这棵树所占用的空间越小,检索速度就会越快,而varchar格式的字符串有些会很长,那么在效率为上的今天,我们如何更加合理的建立字符串的索引呢? 假如说我们一张表中存在 email 字段,现在要给 email 字段创建索引,e

  • MySQL创建索引需要了解的

    前言: 在 MySQL 中,基本上每个表都会有索引,有时候也需要根据不同的业务场景添加不同的索引.索引的建立对于数据库高效运行是很重要的,本篇文章将介绍下创建索引相关知识及注意事项. 1.创建索引方法 创建索引可以在建表时指定,也可以建表后使用 alter table 或 create index 语句创建索引.下面展示下几种常见的创建索引场景. # 建表时指定索引 CREATE TABLE `t_index` (   `increment_id` int(11) NOT NULL AUTO_I

  • MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

    MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

    目录 多值索引简介 创建多值索引 JSON对象字段索引 JSON数组对象索引 在组合索引中创建多值索引 多值索引的局限 应用场景 多值索引简介 从MySQL 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录.此类索引特定的语法定义: CAST(expression AS type ARRAY),例如CAST(data->'$.zipcode' AS UNSIGNED

  • 浅谈Mysql主键索引与非主键索引区别

    目录 什么是索引 主键索引和普通索引的区别 索引具体采用的哪种数据结构 InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点? B+ Tree的叶子节点都可以存哪些东西? 聚簇索引和非聚簇索引,在查询数据的时候有区别? Index Condition Pushdown(索引下推) 查询优化器 关于索引的题 什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典:实际上

随机推荐