mysql索引

在mysql中,索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中有某一特定值的记录。

通过索引,查询数据时不用读完记录的所有信息,而只是查询索引列即可。

通过索引,查询数据时不用读完记录的所有信息,而只是查询索引列。否则,数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如,要查“库”字,如果不使用音序,就需要从字典的 400 页中逐页来找。但是,如果提取拼音出来,构成音序表,就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此,使用索引可以很大程度上提高数据库的查询速度,还有效的提高了数据库系统的性能。

索引的优缺点

索引有其明显的优势,也有其不可避免的缺点。

优点

索引的优点如下:

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

2、可以给所有的 MySQL 列类型设置索引。

3、可以大大加快数据的查询速度,这是使用索引最主要的原因。

4、在实现数据的参考完整性方面可以加速表与表之间的连接。

5、在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面,主要如下:

1、创建和维护索引组要耗费时间,并且随着数据量的增加所耗费的时间也会增加。

2、索引需要占磁盘空间,除了数据表占数据空间以外,每一个索引还要占一定的物理空间。如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。

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

使用索引时,需要综合考虑索引的优点和缺点。

MySQL索引

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类

特点:B+树叶子节点存储行数据

一个表中,必须有一个聚集索引,只能有一个聚集索引,Innodb通常把一个表的主键索引作为聚集索引,如果没有主键InnoDB会选择一个唯一索引代替。如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚集索引,这个字段为6个字节,类型为长整形。

利用主键索引查找行数据是最快的,建议使用自增主键原因是利于索引树的构建(主键自增写入时新插入的数据不会影响到原有页,插入效率高;但是如果主键是无序的或者随机的,那每次的插入可能会导致原有页频繁的分裂,影响插入效率)

特点:B+树叶子节点存储主键ID

一个表中可以有多个非聚集索引,每个非聚集索引即是一棵B+树

通过非聚集索引查找数据时,需要先在非聚集索引上找到主键ID,再从聚集索引获取行数据,这个过程就称之为回表

B树索引中的B树实际上是B+树,至于为什么使用B+树而不使用B树或者红黑树的原因在另外的文章中有提及。

特点:

特点:类似JDK中的HashMap,但无法支持范围查询

特点:使用的算法仍然是B树索引,不同的就是索引列的值必须唯一

对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录。

对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索,提升索引性能

另外插入行时会构建该唯一索引,假如索引值重复将插入失败,适合业务上做唯一性检验

通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题,但是业务上一般都不采用这种索引,而是使用ES处理全文搜索需求

仅对某个特定字段建立的索引,如(biz_id)

对多个字段建立的索引,如(biz_id,type)

MySQL——关于索引的总结

首先说说索引的 优点 :最大的好处无疑就是提高查询效率。有的索引还能保证数据的唯一性,比如唯一索引。

而它的 坏处 也很明显:索引也是文件,我们在创建索引时,也会创建额外的文件,所以会占用一些硬盘空间。其次,索引也需要维护,我们在增加删除数据的时候,索引也需要去变化维护。当一个表的索引多了以后,资源消耗是很大的,所以必须结合实际业务再去确定给哪些列加索引。

再说说索引的基本结构。一说到这里肯定会脱口而出:B+树!了解B+树前先要了解二叉查找树和二叉平衡树。 二叉查找树 :左节点比父节点小,右节点比父节点大,所以二叉查找树的中序遍历就是树的各个节点从小到大的排序。 二叉平衡树 :左右子树高度差不能大于1。B+树就是结合了它们的特点,当然,不一定是二叉树。

为什么要有二叉查找树的特点?? 因为查找效率快,二分查找在这种结构下,查找效率是很快的。 那为什么要有平衡树的特点呢? 试想,如果不维护一颗树的平衡性,当插入一些数据后,树的形态有可能变得很极端,比如左子树一个数据没有,而全在右子树上,这种情况下,二分查找和遍历有什么区别呢?而就是因为这些特点需要去维护,所以就有了上面提到的缺点,当索引很多后,反而增加了系统的负担。

接着说B+树。 它的结构如下 :

可以发现,叶子节点其实是一个 双向循环链表 ,这种结构的好处就是,在范围查询的时候,我只用找到一个数据,就可以直接返回剩余的数据了。比如找小于30的,只用找到30,其余的直接通过叶子节点间的指针就可以找到。再说说其他特点: 数据只存在于叶子节点 。当叶子节点满了,如果再添加数据,就会拆分叶子节点,父节点就多了个子节点。如果父节点的位置也满了,就会扩充高度,就是拆分父节点,如25 50 75拆分成:25为左子树,75为右子树,50变成新的头节点,此时B+树的高度变成了3。它们的扩充的规律如下表,Leaf Page是叶子节点,index Page是非叶子节点。

再说说B树 ,B树相比较B+树,它所有节点都存放数据,所以在查找数据时,B树有可能没到达叶子节点就结束了。再者,B树的叶子节点间不存在指针。

最后说说Hash索引 ,相较于B+树,Hash索引最大的优点就是查找数据快。但是Hash索引最大的问题就是不支持范围查询。试想,如果查询小于30的数据,hash函数是根据数据的值找到其对应的位置,谁又知道小于30的有哪几个数据。而B+树正好相反,范围查询是它的强项。

附录: Hash到底是啥?? 哈希中文名散列,哈希只是它的音译。 为啥都说Hash快?? 首先有一块哈希表(散列表),它的数据结构是个数组,一个任意长度的数据通过hash函数都可以变成一个固定长度的数据,叫hash值。然后通过hash值确定在数组中的位置,相同数据的hash值是相同的,所以我们存储一个数据以后,只需O(1)的时间复杂度就可以找到数据。 那hash函数又是啥?? 算术运算或位运算,很多应用里都有hash函数,但实际运算过程大不一样。这是Java里String的hashCode方法:

publicint hashCode() {

}

还有一个问题,hash函数计算出来的hash值有可能存在碰撞,即两个不同的数据可能存在相同的hash值,在MySQL或其他的应用中,如Java的HashMap等,如果存在碰撞就会以当前数组位置为头节点,转变成一个链表。

说到这里也清楚了为啥Java中引用类型要同时重写hashCode和equals了。两个对象,实例就算一模一样,它们的hash值也不相等, 为啥不相等?? 默认的Object的hashCode方法会根据对象来计算hash值的,实例相同,但它们还是两个不同的对象啊,所以我们重写hashCode时,最简单的方法就是调用Object的hashCode方法,然后传入该引用类型的属性,让hashCode方法只根据这几个属性来计算,那么实例相同的话,它们的hash值也会相等。等hashCode比较完后,如果相等再比较实例内容,也就是equals,确保不是hash碰撞。

索引的分类

如果我们指定了一个主键,那么这个主键就是主键索引。如果我们没有指定,Mysql就会自动找一个非空的唯一索引当主键。如果没有这种字段,Mysql就会创建一个大小为6字节的自增主键。如果有多个非空的唯一索引,那么就让第一个定义为唯一索引的字段当主键,注意,是第一个定义,而不是建表时出现在前面的。

对于辅助索引来说,它们的B+树结构稍微有点特殊,它们的叶子节点存储的是主键,而不是整个数据。所以在大部分情况下,使用辅助索引查找数据,需要二次查找。但并不是所有情况都需要二次查找。比如查找的数据正好就是当前索引字段的值,那么直接返回就行。这里提一句,B+树的key就是对应索引字段的内容。

而辅助索引又有一些分类:唯一索引:不能出现重复的值,也算一种约束。普通索引:可以重复、可以为空,一般就是查询时用到。前缀索引:只适用于字符串类型数据,对字符串前几个字符创建索引。全文索引:作用是检测大文本数据中某个关键字,这也是搜索引擎的一种技术。

注意,聚集索引、非聚集索引和前面几个索引的分类并不是一个层面上的。上面的几个分类是从索引的作用来分析的。聚集、非聚集索引是从索引文件上区分的。主键索引就属于聚集索引,即索引和数据存放在一起,叶子节点存放的就是数据。数据表的.idb文件就是存放该表的索引和数据。

辅助索引属于非聚集索引,说到这也就明白了。索引和数据不存放在一起的就是非聚集索引。在MYISAM引擎中,数据表的.MYI文件包含了表的索引, 该表的 叶子节点存储索引和索引对应数据的指针,指向.MYD文件的数据。

索引的几点使用经验

经常被查询的字段;经常作为条件查询的字段;经常用于外键连接或普通的连表查询时进行相等比较字段;不为null的字段;如果是多条件查询,最好创建联合索引,因为联合索引只有一个索引文件。

经常被更新的字段、不经常被查询的字段、存在相同功能的字段

在mysql数据库中为字段添加索引,是什么意思,有什么好处,谢谢!!

在mysql数据库中为字段添加索引,意思是对数据库某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页进行排序,它是逻辑指针清单。

索引提供指向存储在表的指定列中的数据值的指针,然后根据指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。

扩展资料:

当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在 employee 表中职员的姓 (lname) 上创建了唯一索引,则任何两个员工都不能同姓。

对某个列建立UNIQUE索引后,插入新记录时,数据库管理系统会自动检查新纪录在该列上是否取了重复值,在CREATE TABLE 命令中的UNIQE约束将隐式创建UNIQUE索引。

MySql索引之哪些情况适合创建索引

说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。

某个字段在SELECT语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在

数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。

比如student_info数据表(含100万条数据),假设我们想要查询 student_id=123110 的用户信息。

索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者

使用 ORDER BY 对数据进行排序的时候,就需要 对分组或者排序的字段进行索引 。如果待排序的列有多

个,那么可以在这些列上建立 组合索引 。

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作,如果对 WHERE 字段创建了索引,就

能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或

删除。如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更

新不需要对索引进行维护。

有时候我们需要对某个字段进行去重,使用 DISTINCT,那么对这个字段创建索引,也会提升查询效率。

比如,我们想要查询课程表中不同的 student_id 都有哪些,如果我们没有对 student_id 创建索引,执行

SQL 语句:

运行结果(600637 条记录,运行时间 0.683s ):

如果我们对 student_id 创建索引,再执行 SQL 语句:

运行结果(600637 条记录,运行时间 0.010s ):

你能看到 SQL 查询效率有了提升,同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因

为索引会对数据按照某种顺序进行排序,所以在去重的时候也会快很多。

首先, 连接表的数量尽量不要超过 3 张 ,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增

长会非常快,严重影响查询的效率。

其次, 对 WHERE 条件创建索引 ,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,

没有 WHERE 条件过滤是非常可怕的。

最后, 对用于连接的字段创建索引 ,并且该字段在多张表中的 类型必须一致 。比如 course_id 在

student_info 表和 course 表中都为 int(11) 类型,而不能一个为 int 另一个为 varchar 类型。

举个例子,如果我们只对 student_id 创建索引,执行 SQL 语句:

运行结果(1 条数据,运行时间 0.189s ):

这里我们对 name 创建索引,再执行上面的 SQL 语句,运行时间为 0.002s 。

创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引

问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的;截取得少了,重复内容太多,字

段的散列度(选择性)会降低。 怎么计算不同的长度的选择性呢?

先看一下字段在全部数据中的选择度:

通过不同长度去计算,与全表的选择性对比:

公式:

例如:

引申另一个问题:索引列前缀对排序的影响

拓展:Alibaba《Java开发手册》

【 强制 】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本

区分度决定索引长度。

说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会 高达

90% 以上 ,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

这样也可以较少的建立一些索引。同时,由于”最左前缀原则”,可以增加联合索引的使用率。

结论:在数据表中的数据行数比较少的情况下,比如不到 1000 行,是不需要创建索引的。

举例1:要在 100 万行数据中查找其中的 50 万行(比如性别为男的数据),一旦创建了索引,你需要先

访问 50 万次索引,然后再访问 50 万次数据表,这样加起来的开销比不使用索引可能还要大。

举例2:假设有一个学生表,学生总数为 100 万人,男性只有 10 个人,也就是占总人口的 10 万分之 1。

学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1,0 代表女性,1 代

表男性。

如果我们要筛选出这个学生表中的男性,可以使用:

运行结果(10 条数据,运行时间 0.696s ):

结论:当数据重复度大,比如 高于 10% 的时候,也不需要对这个字段使用索引。

例如身份证、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字

符串等。

① 冗余索引

举例:建表语句如下

我们知道,通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索,再创建一

个专门针对 name 列的索引就算是一个 冗余索引 ,维护这个索引只会增加维护的成本,并不会对搜索有

什么好处。

② 重复索引

另一种情况,我们可能会对某个列 重复建立索引 ,比方说这样:

我们看到,col1 既是主键、又给它定义为一个唯一索引,还给它定义了一个普通索引,可是主键本身就

会生成聚簇索引,所以定义的唯一索引和普通索引是重复的,这种情况要避免。

欢迎共同进步:

QQ群:1007576722

MySQL如何为表字段添加索引

1.添加PRIMARY KEY(主键索引):

2.添加UNIQUE(唯一索引) :

3.添加INDEX(普通索引) :

4.添加FULLTEXT(全文索引) :

5.添加多列索引: