精华内容
下载资源
问答
  • Oracle与MySQL数据库索引设计与优化 PDF 扫描版 有目录
  • 主要介绍了mysql数据库索引损坏及修复经验分享,需要的朋友可以参考下
  • 查看mySQL数据库索引

    2014-04-11 11:41:23
    mySQL索引查看 select * from information_schema.statistics where table_schema='数据库名称' and table_name = '表名称'
  • MySQL数据库索引

    万次阅读 多人点赞 2018-09-23 09:31:41
    数据库有哪些索引 唯一索引 聚簇索引与非聚簇索引 全文索引 使用索引一定能提高查询性能吗? 哪些情况下设置了索引但是无法使用 哪些情况下需要设置索引、哪些情况下不需要 什么情况下应该使用组合索引而非...

    目录

    索引是什么

    索引有哪些结构/索引常见的模型

    B+树索引

    数据库有哪些索引

    唯一索引

    聚簇索引与非聚簇索引

    全文索引

    索引的最左前缀原则

    索引下推

    使用索引一定能提高查询性能吗?

    哪些情况下设置了索引但是无法使用

    哪些情况下需要设置索引、哪些情况下不需要

    什么情况下应该使用组合/联合索引而非单独索引

    MySQL中索引是如何组织数据的存储的

    Mysql索引原理

    Mysql是如何根据索引查询数据的

    普通索引和唯一索引,应该怎么选择?


    索引是什么

    索引是对数据库表中一个或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构

    你也可以这样理解:索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整本书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

    MySQL数据库几个基本的索引类型:普通索引、唯一索引、主键索引、全文索引

    1.索引加快数据库的检索速度

    2.索引降低了插入、删除、修改等维护任务的速度

    3.唯一索引可以确保每一行数据的唯一性

    4.通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能

    5.索引需要占物理和数据空间

    索引有哪些结构/索引常见的模型

    1. 哈希表:一种以键-值(key-value)存储数据的结构,我们只要输入待查找的值即key,就可以找到其对应的值即Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置。如果出现hash冲突,则在冲突的value位置使用链表进行连接。

        适用场景:等值查询,Memcached及其他一些NoSQL引擎

        

    2. 有序数组

        适用场景:只适用于静态存储引擎。用于等值查询和范围查询(ID值必须是递增的)

        

    3. 搜索树:左子节点小于父节点、父节点小于右子节点。Innodb使用B+树,为什么数据库使用B+树作为索引?

    问:为什么采用B+树?这和Hash索引比较起来有什么优缺点吗?

    答:因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+树是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描

    1、哈希索引适合等值查询,但是无法进行范围查询 

    2、哈希索引没办法利用索引完成排序 

    3、哈希索引不支持多列联合索引的最左匹配规则 

    4、如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题

    B+树索引

    我们举个例子,假设我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。

    表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6),两棵树的示例示意图如下。

    主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

    非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

        PS:主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

    基于主键索引和普通索引的查询有什么区别?

    • 如果语句是select * from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
    • 如果语句是select * from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。

    也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

    索引维护

        对于主键不是递增的表,在插入数据时,如新插入数据ID值为400,则需要逻辑上挪动后面的数据,空出位置。

        而更糟的情况是,如果R5所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂

        除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。当

        然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

    数据库有哪些索引

    在MySql数据库中,有四种索引:聚集索引(主键索引)(聚簇索引)、普通索引唯一索引以及全文索引(FUNLLTEXT INDEX)

    索引又可分为聚簇索引非聚簇索引两种

    唯一索引

    一种索引,不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

    CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

    聚簇索引与非聚簇索引

    可以理解为主键索引与普通索引

    聚簇索引:是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致,且一个表只能有一个聚簇索引,因为物理存储只能有一个顺序。主键索引一般都是聚簇索引

    非聚簇索引:表数据存储顺序与索引顺序无关。对于非聚簇索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;

    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。因为物理存储只能有一个顺序。

    聚簇索引的叶子节点就是数据节点(Innodb的B+树的主键对应的数据节点),而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

    相比之下,聚簇索引适合排序,非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的,排序很快。非聚簇索引则没有按序存放,需要额外消耗资源来排序。

    建立聚簇索引的语句:

    CREATE CLUSTER INDEX index_name ON table_name(column_name1,...);

    问:主键索引查询只会查一次,而非主键索引一定需要回表查询多次吗?

    答:通过覆盖索引也可以只查询一次

    覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。

    当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。

    如,表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。

    当我们通过SQL语句:select key2 from covering_index_sample where key1 = 'keytest';的时候,就可以通过覆盖索引查询,无需回表。

    注:如果这个覆盖索引是一个前缀索引,那么它依然需要回表,因为系统并不确定前缀索引的定义是否截断了完整信息。

    问:以下重建索引的步骤是否存在问题?

    重建普通索引 k

    alter table T drop index k;
    alter table T add index(k);
    

    重建主键索引

    alter table T drop primary key;
    alter table T add primary key(id);

    答:重建索引k的做法是合理的,可以达到省空间的目的。但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,可以用这个语句代替 : alter table T engine=InnoDB。

    全文索引

    全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

    select * from 表名 where 标题 like '%xxx%' or 内容 like '%xxx%' or 作者 like '%xxx%';

    这种搜索效率无比底下

    全文索引是为了使得“关键词搜索”功能更加的高效能。

    我们有这么一张数据表: 

    文章id 文章标题 文章内容

    1 超级塞亚人  我是超级塞亚人我喜欢吃苹果,我不是233大国的人,也不是地球人

    2 我233大国威武,我233大国13亿人,我233大国

    3 我喜欢游泳 游泳有很多好方法

    4 动画片 我儿子喜欢看动画片,尤其是七龙珠,因为里面有塞亚人,而且塞亚人喜欢吃苹果,他们不是地球人

    5 运动 我喜欢运动,喜欢跑步,喜欢游泳,喜欢健身,喜欢xxoo

    6 打炮 我是一个二战的老兵,这是我的回忆录,我最幸福的时光就是在233大国吃着苹果打炮

    7 。。。  

    8 。。。  

    9 。。。  

    然后,根据以上的文章内容,如果建立了一个索引文件(这里忽略索引文件的数据结构,仅仅以一种易于理解的方式呈现): 

    关键词   文章id

    塞亚人    1,4

    苹果      1,4,6

    233大国      1,2,6

    地球        1,4

    游泳        3,5

    七龙珠      4

    喜欢     1,4,5,6   

    那么当我想搜索  “塞亚人”的时候,这个索引文件直接告诉我在文章id为1和4的文章里有这个词。 

    这个索引文件就是“全文索引”。

    如何使用全文索引和分词的方式来帮助优化你的搜索呢?

    需要工作的程序:索引程序,分词程序,数据库。 

    工作原理: 

    1、索引程序从数据库读取数据,比如上面例子中的数据表,索引程序通过sql语句:select 文章id,文章标题,文章内容 from 文章表.获得文章的相关数据 

    2、索引程序对需要索引的内容进行“分词”,而这里的分词就是调用分词程序啦! 

    3、索引程序对分好词的一个个词条加入索引文件。

    在你写的代码里,原来到数据库----like %xxx%-----的语句就变成了到索引文件里去查找,从而找到相应的数据(这点相信你已经理解啦!)

    创建全文索引的两种方法:

    1.在建表语句中

    2.在已知表中

    ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

    具体如何使用全文索引呢?

    不用全文索引时的写法:SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

    使用全文索引:SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

    注意:

    1、MySql自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

    2、使用MySql自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

    3、如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。

    索引的最左前缀原则

    在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

    索引下推

    Index Condition Pushdown (ICP) ,Mysql 5.6添加,用于优化数据查询。

    索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。 

    用下面这种场景进行介绍

    假设有如下查询语句:select * from tuser where name like '张%' and age=10 and ismale=1;  (有联合索引 name,age)

    我们知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”

    以下是Mysql 5.6 之前的查询流程:

    以下是Mysql 5.6 时的查询流程:(使用了索引下推

    InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。在我们的这个例子中,只需要对ID4、ID5这两条记录回表取数据判断,就只需要回表2次。

    使用索引一定能提高查询性能吗?

    通常,通过索引查询数据比全表扫描要快,但是我们也必须注意到它的代价.

    索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.

    索引不但会使得插入和修改的效率降低,而且在查询的时候,有一个查询优化器,太多的索引会让优化器困惑,可能没有办法找到正确的查询路径,从而选择了慢的索引。

    索引范围查询(INDEX RANGE SCAN)适用于两种情况:

        1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

        2.基于非唯一性索引的检索

        3.直接晋升为覆盖索引,避免多次查表

    哪些情况下设置了索引但是无法使用

    根本原因是查询优化器决定不使用索引:

    一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下:

    1、根据搜索条件,找出所有可能使用的索引 

    2、计算全表扫描的代价 

    3、计算使用不同索引执行查询的代价 

    4、对比各种执行方案的代价,找出成本最低的那一个

    有时候查询语句没有按照索引的要求来也会导致无法使用索引,如下:

    1. 建立组合索引,where条件单字段。INDEX(a,b,c),当条件为a或a,b或a,b,c或a,c时都可以使用索引,但是当条件为b,c时将不会使用索引。也就是说不是使用的第一部分,则不会使用索引。如果是INDEX(a,b),即使查询的where是b,a,由于sql优化器的优化作用,会把b,a换成a,b,这样就可以走索引了。如果是index(a,b,c),查询是(a,b,c,d)不会走索引
    2. 条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in)(注意:使用or,又想索引生效,只能将or条件中的每个列都加上索引,这样查询时每个列都会单独使用它们自己的索引
    3. like的模糊查询的模糊词在字符串前面,比如以%或_开头,索引失效。
    4. 在使用不等于(is null、is not null、!= 、<>)的时候无法使用索引会导致全表扫描。
    5. 类型错误,如字段类型为varchar,where条件用number。
    6. 对索引应用内部函数,这种情况下应该建立基于函数的索引。
    7. 索引列不能是表达式(id+1=5)的一部分,也不能是函数的参数
    8. 如果MySQL预计使用全表扫描要比使用索引快,则不使用索引

    哪些情况下需要设置索引、哪些情况下不需要

    需要:

    1).主键自动建立唯一索引
    2).频繁作为查询条件的字段应该创建索引
    3).查询中与其它表关联的字段,外键关系建立索引
    4).单键/组合索引的选择问题(在高并发下倾向创建组合索引)
    5).查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
    6).查询中统计或者分组字段

    不需要:

    1).表记录太少
    2).经常增删改的表(因为不仅要保存数据,还要保存一下索引文件) 索引本来是一种事先在写的阶段形成一定的数据结构,从而使得在读的阶段效率较高的方式,但是如果一个字段是写多读少,则会降低写的速度。
    3).数据重复且分布平均的表字段(比如性别),因此应该只为最经常查询和最经常排序的数据列建立索引。

    4).where条件里用不到的字段不创建索引

    什么情况下应该使用组合/联合索引而非单独索引

    假设有条件语句A=a AND B=b,如果A和B是两个单独的索引,在AND条件下只有一个索引起作用,对于B则要逐个判断,而如果使用组合索引(A, B),只要遍历一棵树就可以了,大大增加了效率。但是对于A=a OR B=b,由于是 或 的关系,因而组合索引是不起作用的,此时可以使用单独索引,这个时候,两个索引可以同时起作用。

    在建立联合索引的时候,如何安排索引内的字段顺序?

        评估标准是:索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。

        因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

        那么,如果既有联合查询,又有基于a、b各自的查询呢?查询条件里面只有b的语句,是无法使用(a,b)这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护(a,b)、(b) 这两个索引。

        这时候,我们要考虑的原则就是空间了。也就是说,如果b的大小是比较小的,如boolean、int类型, 那么可以再多建立一个b索引

    下面通过一个例子来加深理解

    假设有这么一个表:

    CREATE TABLE `geek` (
      `a` int(11) NOT NULL,
      `b` int(11) NOT NULL,
      `c` int(11) NOT NULL,
      `d` int(11) NOT NULL,
      PRIMARY KEY (`a`,`b`),
      KEY `c` (`c`),
      KEY `ca` (`c`,`a`),
      KEY `cb` (`c`,`b`)
    ) ENGINE=InnoDB;

    有以下经常使用的查询语句
    select * from geek where c=N order by a limit 1;
    select * from geek where c=N order by b limit 1;

    这里我们需要思考,ca与cb索引是否都是必要的?

        索引 ca 的组织是先按c排序,再按a排序,同时记录主键(b),根据最左前缀原则,实际上,ca索引的功能同c索引的功能是差不多的,因此可以得出ca索引不是必要的

        索引 cb 的组织是先按c排序,再按b排序,同时记录主键(a),因此该索引需要保留


    MySQL中索引是如何组织数据的存储的

    假如有如下数据表:

    对于表中每一行数据,索引中包含了last_name、first_name、dob列的值,下图展示了索引是如何组织数据存储的。

    https://upload-images.jianshu.io/upload_images/175724-3ba760afbae4a52d.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/700

    可以看到,索引首先根据第一个字段来排列顺序,当名字相同时,则根据第三个字段,即出生日期来排序,正是因为这个原因,才有了索引的“最左原则”。

    普通索引和唯一索引,应该怎么选择?

    在介绍这两者的区别之前,我们先来介绍change buffer:

    什么是change buffer?
        当需要更新一个记录,就是要更新一个数据页:

        1. 如果数据页在内存中(buffer pool中时)就直接更新
        2. 如果这个数据页还没有在内存中(没有在buffer pool中)。InooDB 会将这些更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页时,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作
        将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge,它可以避免大量的磁盘随机访问I/O,merge的流程如下(并不会直接把数据写会磁盘):

               1、从磁盘读入数据页到内存(老版本的数据页);

               2、从change buffer里找出这个数据页的change buffer 记录(可能有多个),依次应用,得到新版数据页;

               3、写redo log。这个redo log包含了数据的变更和change buffer的变更。

        而唯一索引的更新就不能使用 change buffer:对于唯一索引,所有的更新操作都要先判断这个操作是否违反唯一性约束。那么必须将数据页读入内存才能判断。比如,要插入(4,400) 这个记录,就要先判断现在表中是否已经存在 k=4 的记录

        都已经读入内存中了,那直接更新内存会更快,没有必要使用change buffer了。

        所以,只有普通索引才能使用change buffer,考虑使用普通索引还是唯一索引,如果能保证不会数据重复,那么最好使用普通索引(可以使用change buffer,且两类索引查询能力没有区别)

    注意:不是所有的场景用change buffer都能加速:

        1. 设想一个对于写多读少的业务来说,change buffer 记录的变更越多越划算,例如账单类日志类

        2. 反过来,一个业务的更新模式是写入之后马上会做查询,change buffer里的内容不多,由于马上做查询要访问数据页,这样的io次数不会减少

    如果某次写入使用了change buffer机制,之后主机异常重启,是否会丢失change buffer和数据?

        虽然写入时只更新了内存,但是在事务提交的时候,change buffer的操作也会记录到redo log,所以崩溃恢复的时候,change buffer也能找回来,即数据可以找回来。

    所以普通索引和唯一索引,应该怎么选择?

        查询时:两种索引查询性能几乎没差别

        更新时:大部分场景下,因为有change buffer的存在,普通索引的更新速度会比唯一索引的快(特别适用于写多读少的场景)(如果所有的更新后面,都马上伴随着对这个记录的查询,那么应该关闭change buffer)

    扩展阅读:

    Mysql索引原理icon-default.png?t=LA92https://mp.weixin.qq.com/s/9yeModGuGvDu5S0bW9sU6w

    Mysql是如何根据索引查询数据的icon-default.png?t=LA92https://mp.weixin.qq.com/s/ymWeGlaBYWYmfogVDFHo5w

    展开全文
  • MySQL数据库索引优化

    2018-04-17 21:13:20
    介绍BTree索引和Hash索引,详细介绍索引的优化策略等等 1.Btree索引和Hash索引 2.安装演示数据库 3.索引优化策略上 4.索引优化策略中 5.索引优化策略下
  • MySql数据库索引介绍

    千次阅读 多人点赞 2019-05-27 17:08:27
    数据库索引对我们来说是透明的,因为数据库表创建索引前后,SQL语句都可以正常运行,索引的运用只是数据库引擎工作时候的优化手段。但是,这并不是说数据库索引仅仅是数据库设计开发人员和运维人员的事情,对于一个...
    • 写在前面

              数据库索引对我们来说是透明的,因为数据库表创建索引前后,SQL语句都可以正常运行,索引的运用只是数据库引擎工作时候的优化手段。但是,这并不是说数据库索引仅仅是数据库设计开发人员和运维人员的事情,对于一个测试人员,如果对数据库中已有的索引有所了解,可以对测试过程中发现的涉及数据库操作的问题进行深入分析,知其所以然。

    • 什么是索引

      索引是一种特殊的文件(MySql数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针,直接在索引中查找符合条件的选项,加快数据库的查询速度,而不是一行一行去遍历数据后才选择出符合条件的。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。

      举个例子,假设我们创建了一个名为people的表:

      CREATE TABLE people ( id SMALLINT NOT NULL, name CHAR(50) NOT NULL );

      然后,我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分:

      id

      name

      1Mike
      2Ella
      ............
      1000Bruce

      可以看到,在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引,MySQL将在索引中排序name列:

      Index

      ......
      Bruce
      Ella
      Evelyn
      ......

      如果我们要查找name等于“Ella”记录的id(SQL命令为“SELECT id FROM people WHERE name='Ella';”),MySQL能够在name的索引中查找“Ella”值,然后直接转到数据文件中相应的行,准确地返回该行的id(2)。在这个过程中,MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引,MySQL要扫描数据文件中的所有记录,即1000个记录!显然,通过索引查找记录至少要比顺序扫描记录快100倍。

    • 索引类型

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

               MySQL支持的索引包括INDEX、UNIQUE、PRIMARY KEY、FULLTEXT类型的索引。

             1、普通索引INDEX
                这是最基本的索引类型,而且它没有唯一性之类的限制,多行记录可以包含相同值。普通索引可以通过以下几种方式创建:

      • 创建索引,例如CREATE INDEX indexName ON mytable(username(length));
        (如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。)
      • 修改表,例如ALTER mytable ADD INDEX [indexName] ON (username(length))
      • 创建表的时候指定索引,例如CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );

              2、唯一性索引

                   和“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

                    唯一性索引可以用以下几种方式创建:

      • 创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
      • 修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
      • 创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
          
    •     创建了一个 mytable表,来进一步说明下组合索引:
    •     CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  ); 

                  为了进一步提供MySQL的效率,就要考虑建立组合索引。如下语句将 name, city, age建到一个索引里:

                   ALTER TABLE mytable ADD INDEX name_city_age (username(10),city,age);
                   建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

                   如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

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

                   usernname,city,age ;usernname,city;  usernname。

                   为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:
                   SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin"

                   而下面几个则不会用到:

                   SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州"

              3、主键
             主键是一种特殊的唯一性索引,不允许有空值,但它必须指定为“PRIMARY KEY”。一般是在建表的时候同时创建主键索引,例如CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) );”。也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。注意:每个表只能有一个主键。

              4、全文索引
             全文索引FULLTEXT可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。

    • 建立索引的时机
      一般我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:
      SELECT t.Name  FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'
      此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。
      刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:
      SELECT * FROM mytable WHERE username like'admin%'
      而下句就不会使用:
      SELECT * FROM mytable WHERE Name like'%admin'
      因此,在使用LIKE时应注意以上的区别。
    • 索引的不足之处

      上说了一些使用索引的好处,但索引也不是建立的越多、越长越好,过多的使用索引将会造成滥用。因此索引也会有它的缺点:

       1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
       2.建立索引会占用磁盘空间的索引文件,对后续数据库的增删改都需由额外的操作来更新索引。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
       索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
    • 使用索引的注意事项

      使用索引时,有以下一些技巧和注意事项:

      1.索引不会包含有NULL值的列

      只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

      2.使用短索引

      对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 

      3.索引列排序

      MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

      4.like语句操作

      一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

      5.索引列不能参与计算,保持列“干净”

      比如select * from users where YEAR(adddate)<2007;   
      将在每个行上进行应用函数YEAR(adddate)运算后才能比较,这将导致索引失效而进行全表扫描,因此语句应该改成:select * from users where adddate<‘2007-01-01';

      6.不使用NOT IN和<>操作

      7.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

      8.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

    展开全文
  • MySQL数据库索引教程(超详细)

    千次阅读 多人点赞 2020-12-19 14:30:41
    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。 索引分单列索引...

    索引初步

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度

    拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。

    索引分单列索引、唯一索引、组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引;唯一索引,即索引列的值必须唯一,但允许有空值。 创建主键的时候回自动创建唯一索引。组合索引,即一个索引包含多个列。

    创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

    实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    缺点

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

    建立索引会占用磁盘空间的索引文件。

    索引结构

    在这里插入图片描述


    实验准备(以下实验都在VMware中进行)

    1台MySQL主机(5.7)

    准备写一个2千万左右的数据表(当然不是手写)

    创建索引:Navicat 15 for MySQL (图形化工具)


    1、新建一个数据库

    create database test default character set utf8; 
    

    2、新建一个数据表

    CREATE TABLE testsql (
        id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '自增主键',
        dept tinyint not null comment '部门id',
        name varchar(30) comment '用户名称',
        create_time datetime not null comment '注册时间',
        last_login_time datetime comment '最后登录时间' 
    ) comment '测试表';
    
    

    3、写入数据

    insert into testsql values(1,1,'user_1', '2018-01-01 00:00:00', '2018-03-01 12:00:00');
    

    4、赋值

    set @i=1;
    

    5、做一个表的倍数增加(不然几千万的数据写一年都写不完)

    insert into testsql(dept, name, create_time, last_login_time) 
    select left(rand()*10,1) as dept,   #随机生成1~10的整数
            concat('user_',@i:=@i+1),   #按序列生成不同的name
            date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), #生成有时间大顺序随机注册时间
            date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) #生成有时间大顺序的随机的最后登录时间
    from testsql;
    
    

    一直重复运行上面的命令

    到后面的速度会越来越慢,可能要几分钟,这是正常的

    6、查看

    select count(*) from testsql;
    
    

    这行命令可以查看数据表已经达到了多少列

    直到数字出现了几千万行就可以停止了

    (实几百万行已经可以看到效果了)

    查看日志

    show variables like "%_buffer%";
    
    

    7、实验

    这个时候数据表已经达到了几千万
    可以查询以下几千万数据中的某一行

    select COUNT(*) from t where name='user_500000';
    
    

    可以看到搜索速度很慢

    8、创建索引

    选中数据表,单机右键
    选中数据表,单击右键
    找到索引
    名字可以随便取
    字段我选择的是name(其他也可以)
    选择索引,名字随便取,字段我选的是name
    类型我选的是NORMAL,方法用的是BTREE
    类型我选的是NORMAL,方法用的是BTREE
    保存,可能要等几分钟,这时候不要乱动
    保存,可能要等几分钟,这时候不要乱动

    9、实验效果

    这个时候重新搜索,可以看到延迟已经没有了

    select COUNT(*) from t where name='user_500000';
    

    以上就是MySQL数据库索引的教程

    展开全文
  • mysql数据库索引实现原理

    千次阅读 2019-06-01 21:27:11
    在介绍索引实现之前,我们先来了解下几种树的数据结构。 二叉搜索树 二叉搜索树有以下性质 1.每个节点有一个关键字 2.左右孩子至多有一个。 3.关键字大于左孩子,小于右孩子。 正因为二叉搜索树的特性,所以...

    1. B-树

    在介绍索引实现之前,我们先来了解下几种树的数据结构。
    二叉搜索树
    二叉搜索树有以下性质
    1.每个节点有一个关键字
    2.左右孩子至多有一个。
    3.关键字大于左孩子,小于右孩子。
    正因为二叉搜索树的特性,所以这种数据结构很适合用来做搜索,效率等同于二分查找,时间复杂度为log2(n),
    在这里插入图片描述
    但是这种最原始的二叉树有一个弊端,在极端的情况下会退化成链表
    在这里插入图片描述
    所以基础二叉搜索树有很多变种,例如红黑树和AVL树,基于特定的策略避免了树退化成链表,红黑树做数据量不大的搜索还是应用很广泛的,但是要基于文件索引系统。却不合适,因为红黑树虽然搜索效率高,但是树的高度比B-树和B+树要高,需要进行的磁盘IO就多,相比之下后者优势就比较明显了。

    2.B-树

    一种多路搜索树,不是二叉树
    我们来看一个m阶B树
    它有如下几个性质
    1.根节点至少有2个孩子,根节点孩子数为[2,m]。
    2.除根节点外每个非叶子节点的孩子数为[m/2,m](向上取整)。
    3.非叶子节点的关键字个数=指向孩子的指针树-1。
    4.所有叶子节点在同一层且关键字个数为k-1, 其中 m/2 <= k <= m
    5.节点之间关键字的大小,类比二叉搜索树,即关键字的值按大小排列,pi的关键字的值属于(k[i],k[i+1])开区间,例如 p2的关键字要大于17,小于35。
    图片来自网络
    图片来自网络
    B树相对与二叉搜索树来说,变得矮胖,这样能减少io读取的次数,其搜索效率也是log2(n)但这还不足以用来做文件索引。因为B树有可能在非叶子节点命中数据,耗费存贮。所以文件索引一般用B+树。

    3.B+树

    B+树建立在B-树的基础之上,更改了几条性质
    1.非叶子节点的关键字和孩子指针数相同
    2.pi的关键字的值属于[k[i],k[i+1]]闭区间。
    3.为叶子节点添加一个指针
    图片来自网络
    在这里插入图片描述
    B+树在B-树的基础上添加了叶子链表指针,方便查找相邻的数据。
    所有数据只有在叶子节点才会被命中,非叶子节点只提供索引,这样非叶子节点可以存贮更多的数据,索引B+树很适合做文件索引,

    4.Mysql的B+索引实现原理

    mysql存贮索引的文件以页为单位,一个节点相当于一页。不同的存贮引擎,索引实现也不同,就以innodb来说,默认一个索引页为16K,mysql在查询索引时,会预先把多个页加载到内存中,所以如果索引的键很大的话,会引起索引的列表,产生很多的碎片,因为如果一页存储不下一个节点的话,就会新开另外一页。

    4.1索引查找模拟

    首先我们看下mysql索引能存贮多少数据,
    一页为16K,
    1.我们假设以int(4B)大小的自增主键,一个子节点指针为6B
    2.行数据大小假设为1KB
    第一层:根节点
    161024/(4+6)≈1638个索引键
    第二层:非叶子节点
    如果第二层也只存索引键,1638
    1638≈2683044,能存200多万个索引键。
    第三层如果只存数据:一页能存16行数据
    1638163816=42928704,
    三次io能查询4000多万数据,这就是为什么数据库为什么采取B+索引的原因,因为能减少IO的读取次数。

    InnoDB使用的是聚簇索引,即数据文件就是索引文件,这里又分主键索引和非主键索引。
    主键索引:将主键映射到B+树中,而叶子节点就是行数据。
    非主键索引:存贮的是主键值,再通过主键的值,去搜索主键索引获得数据。

    MyISM使用的是非聚簇索引:即索引文件和数据文件分开,叶子节点仅仅存贮了数据文件的地址。这点和oracle的B树索引实现类似,都是存贮了地址。myIsm的主键索引和非主键索引没什么区别,只是存贮的键值列不同,叶子节点都是存贮的数据地址。

    展开全文
  • mysql数据库索引的使用及介绍

    千次阅读 2018-06-07 16:55:43
    数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录...
  • 本篇中记录下数据库索引相关的知识点! 索引是什么? 举个例子;大家去图书馆借书时,会先在电脑检索书名或作者等关键字信息,查询出该本书对应的一个图书索引后,紧接着就可以拿着这个图书索引去精确定位存放该索引...
  • mysql数据库索引的创建语句

    千次阅读 2017-07-29 10:54:07
    单列索引 CREATE INDEX 索引名 ON 表名(列名); 组合索引创建 create unique index 索引名 on 表名(列名1,列名2……)
  • 为什么MySQL数据库索引选择使用B+树?

    万次阅读 多人点赞 2018-03-05 21:19:57
    在进一步分析为什么MySQL数据库索引选择使用B+树之前,我相信很多小伙伴对数据结构中的树还是有些许模糊的,因此我们由浅入深一步步探讨树的演进过程,在一步步引出B树以及为什么MySQL数据库索引选择使用B+树!...
  • mysql数据库索引的创建及使用

    千次阅读 2015-09-22 08:48:48
    本文主要针对MySql数据库,双十一的电商大战,引发了淘宝技术热议,而淘宝现在去IOE(I代表IBM的缩写,即去IBM的存储设备和小型机;O是代表Oracle的缩写,也即去Oracle数据库,采用MySQL和Hadoop替代的解决方案,;...
  • mysql数据库索引学习总结

    千次阅读 2017-03-20 16:11:32
    人们时常把索引比作是书本的目录,是一种用来提高数据库查询效率的方法,通过在指定字段上创建索引来提高查询效率;那么为什么使用索引可以提高效率,以及如何添加,删除和查询索引,查询创建的索引是否真正起作用,...
  • 数据库索引是如何实现的呢?底层使用的是什么数据结构和算法? 从执行效率方面,我们希望通过索引,查询效率尽可能的高;在存储空间方面,我们希望索引不要消耗太多的空间。 1.解决问题的前提是定义清楚问题 ...
  • 包含mysql简介、索引介绍、索引引用策略、索引失效场景等
  • MySQL数据库索引 理论+实例 (1 + 1 != 2)

    千次阅读 2020-09-12 21:25:13
    数据库索引 是一个排序的列表,存储着索引值和这个值所对应的物理地址 无须对整个表进行扫描,通过物理地址就可以找到所需数据 是表中一列或者若干列值排序的方法 需要额外的磁盘空间 2. 索引的作用 数据库利用...
  • Mysql数据库索引的理解及聚簇索引和非聚簇索引的区别 概念 索引是帮助Mysql搞笑获取数据的数据结构 对Mysql数据库来讲,其核心就是存储引擎,而索引就是属于存储引擎级别的概念,不同的存储引擎对索引的实现方式是...
  • MYSQL数据库四种索引类型的简单使用

    万次阅读 2017-09-18 13:38:09
    MYSQL数据库索引类型包括普通索引,唯一索引,主键索引与主键索引,这里对这些索引的做一些简单描述: (1)普通索引 这是最基本的MySQL数据库索引,它没有任何限制。它有以下几种创建方式: 创建索引 CREATE ...
  • mysql数据库创建索引和使用

    千次阅读 2018-11-08 13:43:46
    1. 2 ...需要注意索引需要的不同数据库引擎 alter table user add fulltext(字段名); 创建语句: CREATE TABLE zy_article( art_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, typ...
  • MySQL数据库索引的实现原理

    万次阅读 多人点赞 2018-06-25 10:22:41
    一、MySQL索引的数据结构: 常见的索引的数据结构有:B+Tree、Hash索引。 1、Hash索引MySQL中,只有Memory存储引擎支持hash索引,是Memory表的默认索引类型。hash索引把数据以hash值形式组织起来,因此检索...
  • MYSQL数据库四种索引类型介绍

    千次阅读 多人点赞 2020-04-20 18:24:46
    MYSQL数据库四种索引类型 主键索引: 主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键。 唯一索引: 索引列的所有值都只能出现一次,即必须唯一,值可以为空。 普通索引 : 基本的...
  • mysql数据库索引损坏及修复

    千次阅读 2012-06-18 16:32:26
    mysql索引被破坏的问题及解决 下午上班,惊闻我的dedecms的网站出问题了,访问一看,果然全屏报错,检查mysql日志,错误信息为:  Table '.\dedecmsv4\dede_archives' is marked as crashed and should be ...
  • MySQL数据库从入门到实战课

    万人学习 2019-12-31 14:09:39
    根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。 使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。 学习编程...
  • Mysql数据库中的索引

    万次阅读 多人点赞 2019-03-24 15:34:24
    索引是对数据库表中的一列或者多列的值进行排序的一种数据结构,如果把数据库中的表比作一本书,索引就是这本书的目录,通过目录可以快速查找到书中指定内容的位置 索引也是一张表,该表中存储着索引的值和这个值的...
  • MySQL数据库面试题(2020最新版)

    万次阅读 多人点赞 2020-03-10 17:20:40
    数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别?InnoDB引擎的4大特性存储引擎选择索引什么是索引索引有哪些优缺点?索引使用场景(重点)...
  • 第7章 索引 索引是一种特殊的数据库结构可以用来快速查询数据库表中的特定记录索引是提高数据库性能的重要方式MySQL中所有的数据类型都可以被索引MySQL索引包括普通索引惟一性索引全文索引单列索引多列索引和空间...
  • mysql 数据库索引b+树深度问题

    千次阅读 2020-11-05 16:36:00
    常见面试题:mysql数据库一颗b+数据深度大概是多少? 这要从b+树的结构和数据库索引大小去分析问题,索引字段占内存大小,指针占内存大小(6字节 6Byte,或者6B) 1、先看看b+树结构 下面看一颗树 第一行中,1...
  • 数据库,如果仅仅是用来存个数据、建建表、建建索引、做做增删改查,那么也许觉得数据结构和这东西没什么关系。不过要是想知道的多一点,想...如果想把数据库索引学个明明白白,就必须将数据结构和算法作为切入点去学习
  •   说到数据库优化脱口而出就是添加索引,如果不会用请移步《解锁数据库系列》数据库索引已为你备好!掌握数据结构,就可以掌握索引的底层原理,我们应当有**路漫漫其修远兮,吾将上下而求索**的态度,本文将探究...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 334,603
精华内容 133,841
关键字:

mysql数据库索引

mysql 订阅