当前位置:首页 > 编程教程 > mysql技术文章 > 简单介绍下MYSQL的索引类型

MYSQL的索引类型知识点总结

  • 发布时间:
  • 作者:码农之家
  • 点击:87

这篇文章主要知识点是关于MYSQL、索引类型、Mysql索引的类型和优缺点详解 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

MySQL王者晋级之路
MySQL王者晋级之路高质量扫描版
  • 类型:MySQL数据库大小:76.5 MB格式:PDF作者:张甦
立即下载

简单介绍下MYSQL的索引类型

一、介绍一下索引的类型

Mysql常见索引有:主键索引、唯一索引、普通索引、全文索引、组合索引
PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)      ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引    ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

Mysql各种索引区别:

普通索引(INDEX):最基本的索引,没有任何限制
唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。
全文索引(FULLTEXT ):仅可用于 MyISAM 表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时好空间。
组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

举个例子来说,比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段:
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号,作为主键,使用 PRIMARY
会员姓名 如果要建索引的话,那么就是普通的 INDEX
会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)
会员备注信息 , 如果需要建索引的话,可以选择 FULLTEXT,全文搜索。

不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。
用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。

 创建索引:CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

 删除索引的语法:DROP INDEX index_name ON tableName

二、索引分单列索引和组合索引

   单列索引:即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
   组合索引:即一个索包含多个列。

为了形象地对比两者,再建一个表:

CREATE TABLE myIndex (

i_testID INT NOT NULL AUTO_INCREMENT,

vc_Name VARCHAR(50) NOT NULL,

vc_City VARCHAR(50) NOT NULL,

i_Age INT NOT NULL,

i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID)

);

在这10000条记录里面七上八下地分布了5条vc_Name="erquan"的记录,只不过city,age,school的组合各不相同。
来看这条T-SQL:

 

复制代码 代码如下:
SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;

 

首先考虑建单列索引:

    在vc_Name列上建立了索引。执行T-SQL时,MYSQL很快将目标锁定在了vc_Name=erquan的5条记录上,取出来放到一中间结果集。在这个结果集里,先排除掉vc_City不等于"郑州"的记录,再排除i_Age不等于25的记录,最后筛选出唯一的符合条件的记录。

    虽然在vc_Name上建立了索引,查询时MYSQL不用扫描整张表,效率有所提高,但离我们的要求还有一定的距离。同样的,在vc_City和i_Age分别建立的单列索引的效率相似。

    为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将vc_Name,vc_City,i_Age建到一个索引里:
    ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--注意了,建表时,vc_Name长度为50,这里为什么用10呢?因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

    执行T-SQL时,MySQL无须扫描任何记录就到找到唯一的记录!!

    肯定有人要问了,如果分别在vc_Name,vc_City,i_Age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率一样吧?嘿嘿,大不一样,远远低于我们的组合索引~~虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

    建立这样的组合索引,其实是相当于分别建立了

    vc_Name,vc_City,i_Age
    vc_Name,vc_City
    vc_Name

    这样的三个组合索引!为什么没有vc_City,i_Age等这样的组合索引呢?这是因为mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个T-SQL会用到:

  SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"
  SELECT * FROM myIndex WHREE vc_Name="erquan"

而下面几个则不会用到:

  SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"
  SELECT * FROM myIndex WHREE vc_City="郑州"

三、使用索引

    到此你应该会建立、使用索引了吧?但什么情况下需要建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对 <,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE(后面有说明)才会使用索引。
    SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='郑州'  时,有对myIndex表的vc_City和i_Age建立索引的需要,由于testIndex表的vc_Name开出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到了,只有某些时候的LIKE才需建立索引?是的。因为在以通配符 % 和 _ 开头作查询时,MySQL不会使用索引,如

  SELECT * FROM myIndex WHERE vc_Name like'erquan%'

会使用索引,而

  SELECT * FROM myIndex WHEREt vc_Name like'%erquan'

    就不会使用索引了。

四、索引的不足之处

    上面说了那么多索引的好话,它真的有像传说中那么优秀么?当然会有缺点了。

    1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件

    2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

篇尾:

    讲了这么多,无非是想利用索引提高数据库的执行效率。不过索引只是提高效率的一个因素。如果你的MySQL有大数据的表,就需要花时间研究建立最优秀的索引或优化查询语句。

以上所述就是本文的全部内容了,希望大家能够喜欢。

Mysql索引的类型和优缺点详解

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
注:
[1]索引不是万能的!索引可以加快数据检索操作,但会使数据修改操作变慢。每修改数据记录,索引就必须刷新一次。为了在某种程序上弥补这一缺陷,许 多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进 行刷新,对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合,DELAY_KEY_WRITE选项的作用将非 常明显。
[2]另外,索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内 容,为它建立索引就没有太大的实际效果。
从理论上讲,完全可以为数据表里的每个字段分别建一个索引,但MySQL把同一个数据表里的索引总数限制为16个。

1. InnoDB数据表的索引

与MyISAM数据表相比,索引对InnoDB数据的重要性要大得多。在InnoDB数据表上,索引对InnoDB数据表的重要性要在得多。在 InnoDB数据表上,索引不仅会在搜索数据记录时发挥作用,还是数据行级锁定机制的苊、基础。”数据行级锁定”的意思是指在事务操作的执行过程中锁定正 在被处理的个别记录,不让其他用户进行访问。这种锁定将影响到(但不限于)SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。
出于效率方面的考虑,InnoDB数据表的数据行级锁定实际发生在它们的索引上,而不是数据表自身上。显然,数据行级锁定机制只有在有关的数据表有一个合 适的索引可供锁定的时候才能发挥效力。

2. 限制

如果WEHERE子句的查询条件里有不等号(WHERE coloum != …),MySQL将无法使用索引。
类似地,如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = …),MySQL也将无法使用索引。
在JOIN操作中(需要从多个数据表提取数据时),MySQL只有在主键和外键的数据类型相同时才能使用索引。
如果WHERE子句的查询条件里使用比较操作符LIKE和REGEXP,MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说, 如果查询条件是LIKE ‘abc%',MySQL将使用索引;如果查询条件是LIKE ‘%abc',MySQL将不使用索引。
在ORDER BY操作中,MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。(虽然如此,在涉及多个数据表查询里,即使有索引可用,那些索引在加快 ORDER BY方面也没什么作用)
如果某个数据列里包含许多重复的值,就算为它建立了索引也不会有很好的效果。比如说,如果某个数据列里包含的净是些诸如”0/1″或”Y/N”等值,就没 有必要为它创建一个索引。 

普通索引、唯一索引和主索引

1. 普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

2. 唯一索引
普通索引允许被索引的数据列包含重复的值。比如说,因为人有可能同名,所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处:一是简 化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在 某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯 一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。

3. 主索引

在前面已经反复多次强调过:必须为主键字段创建一个索引,这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是:前者在定义时使用的关键字是 PRIMARY而不是UNIQUE。

4. 外键索引

如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

5. 复合索引

索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引,就可以使 用复合索引INDEX(columnA, columnB)。不过,这种用法仅适用于在复合索引中排列在前的数据列组合。比如说,INDEX(A, B, C)可以当做A或(A, B)的索引来使用,但不能当做B、C或(B, C)的索引来使用。

6. 索引的长度

在为CHAR和VARCHAR类型的数据列定义索引时,可以把索引的长度限制为一个给定的字符个数(这个数字必须小于这个字段所允许的最大字符个数)。这 么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里,数据库中的字符串数据大都以各种各样的名字为主,把索引的长度设置 为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。
在为BLOB和TEXT类型的数据列创建索引时,必须对索引的长度做出限制;MySQL所允许的最大索引长度是255个字符。
全文索引

   文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数 据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用

下面这条命令添加:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

注解:InnoDB数据表不支持全文索引。

查询和索引的优化

      只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令 之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。

      在不确定应该在哪些数据列上创建索引的时候,人们从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键 字,MySQL将不是去执行那条SELECT命令,而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出 来。
在EXPLAIN命令的输出结果里,第1列是从数据库读取的数据表的名字,它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关联 关系(JOIN)。在各种类型的关联关系当中,效率最高的是system,然后依次是const、eq_ref、ref、range、index和 All(All的意思是:对应于上一级数据表里的每一条记录,这个数据表里的所有记录都必须被读取一遍–这种情况往往可以用一索引来避免)。

possible_keys数据列给出了MySQL在搜索数据记录时可选用的各个索引。key数据列是MySQL实际选用的索引,这个索引按字节计算的长 度在key_len数据列里给出。比如说,对于一个INTEGER数据列的索引,这个字节长度将是4。如果用到了复合索引,在key_len数据列里还可 以看到MySQL具体使用了它的哪些部分。作为一般规律,key_len数据列里的值越小越好(意思是更快)。
ref数据列给出了关联关系中另一个数据表里的数据列的名字。row数据列是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。row 数据列里的所有数字的乘积可以让我们大致了解这个查询需要处理多少组合。
最后,extra数据列提供了与JOIN操作有关的更多信息,比如说,如果MySQL在执行这个查询时必须创建一个临时数据表,就会在extra列看到 using temporary字样。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持码农之家。

以上就是本次给大家分享的关于java的全部知识点内容总结,大家还可以在下方相关文章里找到相关文章进一步学习,感谢大家的阅读和支持。

MYSQL索引 相关电子书
学习笔记
网友NO.490145

mysql中索引使用不当速度比没加索引还慢的测试

下面是我们插入到这个tuangou表的数据: id web city type 1 拉手网 北京 餐饮美食 2 拉手网 上海 休闲娱乐 3 百分团 天津 餐饮美食 4 拉手网 深圳 网上购物 5 百分团 石家庄 优惠卷票 6 百分团 邯郸 美容保健 .. 4999 百分团 重庆 旅游酒店 5000 拉手网 西安 优惠卷票 执行mysql语句: $sql = "select from tuangou where web='拉手网' and city='上海'"; (1)如果没有加索引,执行时间是:0.0041秒 (2)如果只给web加索引,执行时间:0.0064秒 (3)如果web和city都加上索引,执行时间:0.0007 秒 在(2)中虽然加上索引,但查询时间比不加索引用的时间还多。所以我建议大家,需要经常查询的字段都加上索引。……

网友NO.439667

MySQL中索引优化distinct语句及distinct的多字段操作

MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;insert into m11 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8);explain select distinct(a) from m11; mysql explain select distinct(a) from m11; 复制代码 代码如下: +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------……

网友NO.385838

Mysql实验之使用explain分析索引的走向

概述 索引是mysql的必须要掌握的技能,同时也是提供mysql查询效率的手段。通过以下的一个实验可以理解?mysql的索引规则,同时也可以不断的来优化sql语句 实验目的 本实验是为了验证组合索引的 最左原则 说明 此实验只是为了验证实际使用索引的结果,请忽略设计的合理性 准备工作 1、用户表一张,有uid ,user_name,real_name ,eamil等字段,详细见建表语句 2、在user_name字段下增加一个简单索引user_name,在email,mobile,age三个字段下增加索引complex_index 3、表引擎使用MyISAM,增加 4、准备97000条数据(具体的可以根据实际情况来定数据量,这里准备的是97000+) 5、实验工具Navcat 建表语句 DROP TABLE IF EXISTS `qz_users`;CREATE TABLE `qz_users` ( `uid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户的 UID', `user_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名', `real_name` varch……

网友NO.177113

MySQL的id关联和索引使用的实际优化案例

昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s 优化点一: SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price='2.00′ ORDER BY tran_id DESC LIMIT 10; 表结构为: CREATE TABLE `game_shares_buy_list` (`tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`………..'PRIMARY KEY (`tran_id`),KEY `ind_username` (`username`)) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8; 执行计划: root@127.0.0.1 : sitevipdb 09:10:22 explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price='2.00′ ORDER BY tran_id DESC LIMIT 10;+—-+————-+———————-+——-+—————+———+———+——+——+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+———————-+——-+—————+———+———+——+——+——……

网友NO.568537

mysql中的冗余和重复索引知识点

mysql允许在相同列上创建多个索引,无论是有意还是无意,mysql需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能。 重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引,应该避免这样创建重复索引,发现以后也应该立即删除。但,在相同的列上创建不同类型的索引来满足不同的查询需求是可以的。 CREATE TABLE test( ID INT NOT NULL PRIMARY KEY, A INT NOT NULL, B INT NOT NULL, UNIQUE(ID), INDEX(ID),) ENGINE=InnoDB; 这段SQL创建了3个重复索引。通常并没有理由这么做。 冗余索引和重复索引有一些不同,如果创建了索引(a,b),再创建索引(a)就是冗余索引,因为这只是前面一个索引的前缀索引,因此(a,b)也可以当作(a)来使用,但是(b,a)就不是冗余索引,索引(b)也不是,因为b不是索引(a,b)的最左前缀列,另外,……

<
1
>

Copyright 2018-2020 www.xz577.com 码农之家

投诉 / 推广 / 赞助:QQ:520161757