详解mysql建立索引的使用办法及优缺点分析

前言

索引（index）是帮助MySQL高效获取数据的数据结构。 它对于高性能非常关键，但人们通常会忘记或误解它。 索引在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引，也能有好的性能， 但是当数据增加的时候，性能就会下降很快。

为什么要创建索引呢？

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

第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

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

这是因为，增加索引也有许多不利的一个方面:

第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

什么样的字段适合创建索引:

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

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

第一、在经常需要搜索的列上，可以加快搜索的速度；

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

第六、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照select的where条件来建立，比如：select的条件是wheref1andf2，那么如果我们在字段f1或字段f2上简历索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。

什么样的字段不适合创建索引:

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

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，

并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，

在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。

增加索引，并不能明显加快检索速度。

第三，对于那些定义为text,image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

因此，当修改性能远远大于检索性能时，不应该创建索引。

创建索引的方法:

1、创建索引，例如createindex<索引的名字>ontable_name(列的列表);

2、修改表，例如altertabletable_nameaddindex[索引的名字](列的列表);

3、创建表的时候指定索引，例如createtabletable_name([...],INDEX[索引的名字](列的列表));

查看表中索引的方法:

showindexfromtable_name;查看索引

索引的类型及创建例子:

1.PRIMARYKEY（主键索引）

mysql>altertabletable_nameaddprimarykey(`column`)

2.UNIQUE或UNIQUEKEY(唯一索引)

mysql>altertabletable_nameaddunique(`column`)

3.FULLTEXT(全文索引)

mysql>altertabletable_nameaddfulltext(`column`)

4.INDEX(普通索引)

mysql>altertabletable_nameaddindexindex_name(`column`)

5.多列索引(聚簇索引)

mysql>altertable`table_name`addindexindex_name(`column1`,`column2`,`column3`)

修改表中的索引：

altertabletablenamedropprimarykey,addprimarykey(fileda,filedb)

总结

有了索引，对于记录数量很多的表,可以提高查询速度。但是索引是占用空间的，所以在建立索引的时候可以根据本文来参考，或许对你有所帮助。

赞 (0)

相关推荐

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

  • MySQL索引用法实例分析

    本文实例分析了MySQL索引用法.分享给大家供大家参考,具体如下: MYSQL描述: 一个文章库,里面有两个表:category和article.category里面有10条分类数据.article里面有20万条.article里面有一个"article_category"字段是与category里的"category_id"字段相对应的.article表里面已经把 article_category字义为了索引.数据库大小为1.3G. 问题描述: 执行一个很普通的查

  • Mysql判断表字段或索引是否存在

    判断字段是否存在: DROP PROCEDURE IF EXISTS schema_change; DELIMITER // CREATE PROCEDURE schema_change() BEGIN DECLARE CurrentDatabase VARCHAR(); SELECT DATABASE() INTO CurrentDatabase; IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schem

  • Mysql使用索引实现查询优化

    索引的目的在于提高查询效率,可以类比字典,如果要查"mysql"这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql.如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的. 1.索引的优点 假设你拥有三个未索引的表t1.t2和t3,每个表都分别包含数据列i1.i2和i3,并且每个表都包含了1000条数据行,其序号从1到1000.查找某些值匹配的数据行组合的查询可能如下所示: SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2,

  • Mysql性能优化案例 - 覆盖索引分享

    Mysql性能优化案例 - 覆盖索引分享

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录 现在有一个根据user_id建立的索引:uid 查询语句也很简单:取得某用户的图片集合 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查

  • Mysql数据库之索引优化

    Mysql数据库之索引优化

    MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,

  • mysql索引必须了解的几个重要问题

    本文讲述了mysql索引必须了解的几个重要问题.分享给大家供大家参考,具体如下: 1.索引是做什么的? 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行. 表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据. 大多数MySQL索引(PRIMARY KEY.UNIQUE.INDEX和FULLTEXT)在B树中存储.只是空间列类型的索引使用R-树,并且MEMORY

  • mysql 索引详细介绍

    mysql 索引详解: 在mysql 中,索引可以分为两种类型 hash索引和 btree索引. 什么情况下可以用到B树索引?  1.全值匹配索引 比如: orderID="123" 2.匹配最左前缀索引查询  比如:在userid 和 date字段上创建联合索引. 那么如果输入 userId作为条件,那么这个userid可以使用到索引,如果直接输入 date作为条件,那么将不能使用到索引. 3.匹配列前缀查询 比如: order_sn like '134%' 这样可以使用到索引. 4

  • 详解mysql建立索引的使用办法及优缺点分析

    前言 索引(index)是帮助MySQL高效获取数据的数据结构. 它对于高性能非常关键,但人们通常会忘记或误解它. 索引在数据越大的时候越重要.规模小.负载轻的数据库即使没有索引,也能有好的性能, 但是当数据增加的时候,性能就会下降很快. 为什么要创建索引呢? 这是因为,创建索引可以大大提高系统的性能. 第一.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性. 第二.可以大大加快数据的检索速度,这也是创建索引的最主要的原因. 第三.可以加速表和表之间的连接,特别是在实现数据的参考完整性方

  • 详解MySQL单列索引和联合索引

    详解MySQL单列索引和联合索引

    目录 一.简介 二.单列索引 三.最左前缀原则 四.同时存在联合索引和单列索引(字段有重复) 五.联合索引本质 六.索引失效 七.其它知识点 八.MySQL存储引擎简介 九.索引结构(方法.算法) 一.简介 利用索引中的附加列,可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引. 联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序.如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓

  • 详解mysql中的冗余和重复索引

    mysql允许在相同列上创建多个索引,无论是有意还是无意,mysql需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能. 重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引,应该避免这样创建重复索引,发现以后也应该立即删除.但,在相同的列上创建不同类型的索引来满足不同的查询需求是可以的. CREATE TABLE test( ID INT NOT NULL PRIMARY KEY, A INT NOT NULL, B INT NOT NULL, UNI

  • 详解mysql权限和索引

    详解mysql权限和索引

    mysql权限和索引 mysql的最高用户是root, 我们可以在数据库中创建用户,语句为CREATE USER 用户名 IDENTIFIED BY '密码',也可以执行CREATE USER 用户名 语句来创建用户,不过此用户没有密码,可以将用户登录后进行密码设置:删除用户语句为DROP USER 用户:更改用户名的语句为RENAME USER 老用户名 to 新用户名: 修改密码语句为set password=password('密码'): 高级用户修改别的用户密码的语句为SET PASSW

  • 详解MySQL InnoDB的索引扩展

    索引扩展,InnoDB通过将主键列附加到每个辅助索引中来自动扩展该索引.创建如下表结构: mysql> CREATE TABLE t1 ( -> i1 INT NOT NULL DEFAULT 0, -> i2 INT NOT NULL DEFAULT 0, -> d DATE DEFAULT NULL, -> PRIMARY KEY (i1, i2), -> INDEX k_d (d) -> ) ENGINE = InnoDB; Query OK, 0 rows

  • 详解MySQL 8.0 之不可见索引

    言 MySQL 8.0 从第一版release 到现在已经走过了4个年头了,8.0版本在功能和代码上做了相当大的改进和重构.和DBA圈子里的朋友交流,大部分还是5.6 ,5.7的版本,少量的走的比较靠前采用了MySQL 8.0.为了紧追数据库发展的步伐,能够尽早享受技术红利,我们准备将MySQL 8.0引入到有赞的数据库体系. 落地之前 我们会对MySQL 8.0的新特性和功能,配置参数,升级方式,兼容性等等做一系列的学习和测试.以后陆陆续续会发布文章出来.本文算是MySQL 8.0新特性学习的

  • 详解MySQL的字段默认null对唯一索引的影响

    目录 正文 看一下为何唯一索引为影响insert速度 MySQL版本:在docker中启动一个mysql 假设只存在邮箱注册: insert数据 经验 正文 在日常业务开发中,会经常遇到需要保证唯一性的数据业务,如用户注册业务.一般注册业务中允许用户以手机号或email注册账号,且需要保证唯一,不允许重复注册.当用户输入手机号或email登录时,程序会判定输入信息的存在与否性,存在则走登录,不存在则走注册.而保证唯一性就不仅仅需要在程序端做判断,还需要MySQL的唯一索引去做最后一道防线.那么唯

  • 详解 Mysql 事务和Mysql 日志

    事务特性 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,B却没收到. 3.隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰.比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账. 4.持久性(Durability):事务完成后,事务对数据库的所有更新

  • 详解MySQL 外键约束

    官方文档: https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html 1.外键作用: MySQL通过外键约束来保证表与表之间的数据的完整性和准确性. 2.外键的使用条件 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持) 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示建立: 外键关系的两个表的列必须

  • 详解MySQL中的数据类型和schema优化

    最近在学习MySQL优化方面的知识.本文就数据类型和schema方面的优化进行介绍. 1. 选择优化的数据类型 MySQL支持的数据类型有很多,而如何选择出正确的数据类型,对于性能是至关重要的.以下几个原则能够帮助确定数据类型: 更小的通常更好 应尽可能使用可以正确存储数据的最小数据类型,够用就好.这样将占用更少的磁盘.内存和缓存,而在处理时也会耗时更少. 简单就好 当两种数据类型都能胜任一个字段的存储工作时,选择简单的那一方,往往是最好的选择.例如整型和字符串,由于整型的操作代价要小于字符,所

随机推荐