精华内容
下载资源
问答
  • 什么时候才需要添加索引? 首先创建 drop table if exists index_test01; create table `index_test01`( `id` int(11) NOT NULL AUTO_INCREMENT,/*AUTO_INCREMENT表示自增*/ `a` int(11) , `b` int(11) , `c` int...

    最近在学习深入MySQL,记录一下学习历程和一些问题,下面的几种情况是我从网上搜到的,但是在实际测试中却和网上的结果有出入,希望得到大佬的指点

    什么时候才需要添加索引?

    首先创建表

    drop table if exists index_test01;
    create table `index_test01`(
    	`id` int(11) NOT NULL AUTO_INCREMENT,/*AUTO_INCREMENT表示自增*/
    `a` int(11) ,
    `b` int(11) ,
    `c` int(11) ,
    `d` int(11) ,
    PRIMARY KEY (`id`),/*主键*/
    KEY `idx_a` (`a`),/*为a字段创建索引*/
    KEY `idx_b_c` (`b`,`c`)/*为b,c字段创建联合索引*/
    );
    drop procedure if exists  insert_index_test01;  /*如果存储过程insert_index_test01存在就删除它*/
     delimiter $$ /*改变结束符  MYSQL的默认结束符为";"*/
    create procedure insert_index_test01()  /*创建存储过程insert_index_test01*/
    begin
    declare i int;/*声明变量i*/
    set i=1;/*设置i的初始值为1*/
    while (i<100000)do /*循环10万次*/
    insert into index_test01(a,b,c,d) values(i,i,i,i); /*自定义SQL  这里是为index_test01添加10万条数据*/
    set i=i+1;
    end while;
    end $$
    delimiter ;/*还原结束符*/
    call insert_index_test01(); /* 运行存储过程insert_t9_1 */
    
    

    想要看到明显的差距的话需要加表的数据
    反复执行

    insert into index_test01(a,b,c,d)
    select a,b,c,d from index_test01;
    

    情况1:条件字段加索引

    现在对比一下不同SQL的查询速度
    这个是不走索引的SQL

    select * from index_test01 where c=9000
    

    查询结果:
    耗时0.45S

    这个是走索引的SQL

    select * from index_test01 where a=9000
    

    耗时0.065S

    我们再用explain在看一下

     explain select * from index_test01 where a=9000
    

    在这里插入图片描述
    可以看到rows字段只有16行,代表只扫描了一行

     explain select * from index_test01 where c=9000
    

    在这里插入图片描述
    可以看到rows字段只有1596288行,进行了一个全表扫描

    情况2:聚合函数

    直接上SQL

    select max(a) from index_test01 
    
    select max(d) from index_test01 
    

    第一条耗时0.033s
    第二条耗时0.47s
    差距还是非常明显的

    在看在explain下两条SQL的差距

    explain select max(a) from index_test01 ;
    

    在这里插入图片描述

    explain select max(d) from index_test01 ;
    

    在这里插入图片描述
    这是也是进行了一个全表的扫描

    情况3 排序

    当我在进行排序时出现了一个疑问,我的语句是这样的

     select a from index_test01
    where a<5000
     ORDER BY a
    

    查询耗时4.248S
    在这里插入图片描述
    这里可以看到只扫描了17万行,是走了索引的
    第二条SQL

    select d from index_test01
    where d<5000
     ORDER BY d
    

    耗时4.625S
    在这里插入图片描述
    扫描了150万行,进行了一个全表的扫描,但是时间却和第一条差不多,不知道是什么原因,希望有大佬解惑。

    情况4 避免回表

    什么是避免回表呢?以下是个人理解,如果有不对的地方欢迎指正
    我们索引所存储的只是有索引的那个字段和他的行id,当我们使用索引查出来之后是获取她的行id,在通过行id去进行扫描获得完整的数据。

    select a,d from index_test01 where a<5000
    

    因为我们只有a字段有索引
    所以这里我们需要分为两步
    1.通过a字段索引找到行id
    2.通过行id再去扫描一次磁盘获取d字段的数据再返回

    select b,c from index_test01 where b<5000
    

    这里我们是有b、c字段的联合索引
    索引我们只要通过索引就可以直接获取到b、c两个字段的值 直接返回出去
    减少了一次磁盘的扫描,所以会快一些。

    但是我在实际的测试中发现两条SQL的执行时间都是7秒左右,并没有太大差别,甚至会出现 联合索引比单字段索引更慢的情况,希望能有大佬解惑。

    情况5 关联查询

    创建表index_test02,表结构与index_test01一致

    drop table if exists index_test02;
    create table index_test02 like index_test01; /* 创建表index_test02,表结构与index_test01一致 */
    insert into index_test02 
    select * from index_test01 LIMIT 2000; /*添加表数据*/
    

    走索引的关联查询

    select a.a,b.a from index_test01 a
    inner join index_test02 b on a.a=b.a
    

    耗时2.402S
    在这里插入图片描述
    不走索引的关联查询

    select a.d,b.d from index_test01 a
    inner join index_test02 b on a.d=b.d
    

    耗时152.070S
    在这里插入图片描述
    相差了一百多秒

    到此就结束了,我们做一个总结
    一共有5种情况

    1. 数据检索时在条件字段添加索引
    2. 聚合函数对聚合字段添加索引
    3. 对排序字段添加索引
    4. 为了防止回表添加索引
    5. 关联查询在关联字段添加索引
      其中3、4这两种情况我的测试并不符合网上所述,具体原因等我日后更加深入学习后或者有大佬指出问题后再做解答吧
    展开全文
  • 导读随着业务的快速增长,用户中心的用户user单数据量越来越大,此时,如果我们想给user表添加索引,数据规模对添加过程的影响势必要考虑在内,但是,单数据规模对添加索引会产生什么样的影响呢,我们在什么样...

    导读

    随着业务的快速增长,用户中心的用户表user单表数据量越来越大,此时,如果我们想给user表添加索引,数据规模对添加过程的影响势必要考虑在内,但是,单表数据规模对添加索引会产生什么样的影响呢,我们在什么样的数据库请求状态下给大表添加索引比较好呢?

    今天,我就详细回答一下上面两个问题:

    1. 单表数据规模对添加索引会产生什么样的影响?
    2. 在什么样的数据库请求状态下给大表添加索引比较好?

    我们先来看下第一个问题,当我们回答了第一个问题,那么,第二个问题的答案也就浮出水面了。

    Row Log

    我们先来看一个结构,它叫Row Log,用于在DDL过程中记录DML操作的日志文件。

    0385220d8c8756f55730ebd63ccb4116.png

    我以user表为例,讲解一下Row Log。它有如下特点:

    1. 每个索引对应一个Row Log,如上图为user表的索引index_age_birth对应的Row Log。

    Row Log在逻辑上由多个Block组成,每个Block可以存储多个DML操作、一个DML操作也会落在多个Block中。如上图中的Log代表DML操作:

    1. 最前面两个Log存在第二个Block中
    2. 第3个Log和第4个Log的前半部分存在第三个Block中
    3. 第4个Log的后半部分和第5个Log存在最后一个Block中

    在物理存储上Row Log分为两部分:

    1. 内存日志:内存中会存放一个总大小等于inndob_sort_buffer_size的Block,用于写入DML操作
    2. 文件日志:当内存中的Block写满,也就是大小大于innodb_sort_buffer_size,且小于innodb_online_alter_log_max_size时,写满的Block会刷到磁盘上,空出内存中的Block给后续的Log写入,日志文件中,所有Block总大小如果超过innodb_online_alter_log_max_size,写入就会报错

    Row Log的核心结构如下:

    1. Log:表示DML操作日志,它的结构为操作flag + 事务id + 操作记录,其中,操作flag包含两种:INSERT和DELETE,UPDATE看作是先DELETE,再INSERT。比如,上图第一个Log中包含一条记录<0x61 + 1234 + <25, 1998-01-02, 1>>,其中,0x61代表这是一个插入操作,1234表示这个操作的事务id,<25, 1998-01-02, 1>表示操作的记录。
    2. head:这是用于将Block中的Log回放到索引树时,用来扫描Block中Log的指针,扫完一个Log,head指针向后移到下一个Log。如上图,因为从Block的头部开始扫描,head指针在回放前处在Block的第一个Log的位置。
    3. tail:这是用于将DML操作写入一个Block时,用来定位Block中Log插入位置的指针,插入完一个Log,tail指针向后移动到新插入的Log。如上图,因为从Block的头部开始插入Log,所以,tail指针在插入前处在Block的第一个Log的位置。
    4. blocks:无论是head还是tail指针,都包含一个blocks字段,表示Row Log日志文件中包含的Block数量

    Row Log追加

    下面我们再来看下Log是如何追加到Row Log的?我以user表的index_age_birth索引的Row Log为例来说明:

    d719427ef1507488b1a2af02fcbeff54.png

    见上图,从上到下,我们来看下这个追加的过程:

    1. 如果内存中没有Block,创建一个innodb_sort_buffer_size大小的Block,tail指针指向Block中的第一个Log,如果有Block,tail指针指向Block中最后一个Log。如上图,内存中有Block,tail指向Block中最后一个Log,也就是虚线框前面那个Log
    2. 根据即将插入的DML操作日志大小,得到Block中下一个Log相对最后一个Log的偏移量。如上图中的offset,这里分两种情况:

    (1) 如果DML操作日志大小 >= innodb_sort_buffer_size - 当前Block中已有Log的总大小,则偏移量为innodb_sort_buffer_size - 当前Block中已有Log的总大小

    (2) 如果DML操作日志大小 < innodb_sort_buffer_size - 当前Block中已有Log的总大小,则偏移量为DML操作日志大小

    1. 根据tail指针和偏移量,将插入的DML操作日志拷贝到内存的Block。这里同样分两种情况:

    (1) 半拷贝

    ​ a. 如果DML操作日志大小 >= innodb_sort_buffer_size - 当前Block中已有Log的总大小,拷贝DML操作日志的前面部分到tail后面偏移量大小的空间。如上图半拷贝里的上半部分,将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的前半部分拷贝到末尾Log,然后,将tail移到被拷贝的Log上

    ​ b. 将内存中整个Block写入Row Log日志文件。如上图,半拷贝里上半部分大括号包含了整个Block,同时将该Block通过箭头,写入row_log_file

    ​ c. 重新将tail移到内存空Block的头部,将DML操作的后半部分拷贝到tail后面偏移量大小的空间。如上图半拷贝里的下半部分,将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的后半部分拷贝到Block的头部

    ​ d. 如上图,tail.blocks + 1,代表Row Log日志文件中新增了一个Block。

    (2) 全拷贝

    ​ a. 如果DML操作日志大小 < innodb_sort_buffer_size - 当前Block中已有Log的总大小,将DML操作日志全部拷贝到Block中末尾Log。如上图,全拷贝最右侧,将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>完整拷贝到末尾Log,然后,将tail移到被拷贝的Log上

    Row Log回放

    MySQL将DML日志写到Row Log只是为了在执行DDL期间,可以并行执行DML,最后,这些DML日志还是要更新到索引树上的,所以,同样以索引index_age_birth为例,我们再来看下Row Log中的日志是如何更新到索引树的?

    bd24f8e3c28bc1e781ac709afe04c709.png

    从上到下,我们来看上面这张图:

    1. MySQL先扫描磁盘上的Row Log文件,遍历文件中的Block,如上图,文件扫描部分为一个Block的遍历:

    (1) head指针指向Block的头部Log,从该Log开始,将头部Log写入索引树。如上图,文件扫描中的最上面部分,将DML日志0x61 + 3355 + <25, 1998-01-02, 1>>中的记录写入索引树index_age_birth的第一个叶子节点。

    (2) 头部Log清空,将head指针移到后面一个Log。如上图,文件扫描中的第二块长方框。

    (3) 重复(1)和(2)两步,直到head指针移到Block中最后一个Log,然后,将该Log中的记录写入索引树index_age_birth。如上图,文件扫描中的第三个长方框及方框中最后一个Log中的记录写入索引树index_age_birth的第二个叶子节点。。

    1. 重复步骤1,将Row Log文件中所有Block内的Log全部写入索引树index_age_birth,至此,Row Log文件清空。如上图,文件扫描中最后一个虚线长方框,表示Row Log文件清空。
    2. 由于DML日志写Row Log和DDL同时进行,结合《Row Log追加》中的过程,我们会发现大部分Block写入了Row Log文件,但是,还会存在小部分DML日志留存在内存的Block中,所以,MySQL需要将这部分留存的Log再写入索引树中,具体过程如下:

    (1) 对数据字典加排它锁,禁止新的DML操作,ps:如果不加锁,会导致内存中Block不断更新,无法判断DML操作何时结束。

    (2) 执行步骤1,将内存Block中的Log全部写入索引树index_age_birth,如上图,内存扫描部分。

    Bulk Load

    在讲解添加索引的过程之前,还有一个概念再讲解一下,这就是Bulk Load,在添加索引的过程中,会将已排序的记录批量插入索引树的叶子节点中,这个批量插入的过程就叫做Bulk Load,我以索引index_age_birth为例,讲解一下这个过程,见下图:

    2937bec559744669095845f39b748cb2.png
    1. 从已排序的记录集中分多批写入内存的bulk中。如上图,MySQL将最左边已排序的记录集拆分成两批写入2个bulk中,上面的bulk包含15, 2008-02-03, 215, 2008-02-06, 5两条记录,下面的bulk包含16, 2007-06-06, 617, 2006-03-03, 418, 2002-06-07, 3 三条记录。
    2. 以bulk为单位,将bulk中的记录集一次插入索引树中。如上图,上面的bulk记录集插入到索引树index_age_birth的第三个叶子节点,下面的bulk记录集插入到索引树index_age_birth的倒数第二个叶子节点。

    添加索引

    Row Log的追加和回放,以及Bulk Load是添加索引过程中的核心步骤,讲完这三个步骤,下面我再来看一下InnoDB引擎中MySQL添加索引的过程就比较容易理解了,该过程主要分三个阶段,我以user表为例详细讲解一下:

    Prepare阶段

    1. 根据旧表user的表结构文件frm,创建一个副本表结构frm文件,将新索引添加到副本中
    2. 获得MDL排他锁,禁止读写数据字典及旧user表,关于MDL锁,我会在《MySQL锁全解析》详细讲解
    3. 根据alter类型,确定执行方式,一共两种执行方式:COPY、INPLACE
    4. 更新内存中的数据字典,标记user表所有索引online_statusONLINE_INDEX_CREATION,表示该表索引都处在在线DDL状态。关于数据字典的结构,我在《我们可以干预MySQL选择正确的执行计划吗?》中有讲解过。
    5. 根据旧表user的ibd文件,创建副本ibd文件

    DDL执行阶段:

    1. 降级MDL锁为共享锁,允许读写数据字典及旧user表
    2. 扫描旧表user的聚集索引中叶子节点每一条记录

    (1) 申请一个sort_buffer,大小为innodb_sort_buffer_size/索引叶子节点中最小的记录的大小

    (2) 将每一条记录写入sort_buffer

    (3) sort_buffer写满后对里面的记录进行升序排序

    (4) sort_buffer写满了,如果临时文件不存在,就创建一个临时文件

    (5) 遍历sort_buffer记录,将sort_buffer中的记录写入文件中

    ​ a. 生成一个block,将记录添加到block

    (6) 将block写入临时文件

    1. 遍历旧表聚簇索引的记录完成后,临时文件中就包含多个block,每个block包含已排序的记录
    2. 使用归并排序对临时文件中的block内记录进行排序
    3. 遍历副本frm中的聚集索引和辅助索引

    (1) 搜索索引树,定位到树种最右边的叶子节点

    (2) 判断该节点是否可以有足够空间批量插入记录,如果没有就创建一个新的叶子节点,执行步骤(3),否则,执行步骤(4)

    (3) 将新节点接到索引树的右下角,执行步骤(4)

    (4) 遍历临时文件中的记录,将记录通过bulk load方式写入叶子节点

    (5) 调整插入记录的叶子节点内记录的slot信息,关于slot,我在《InnoDB是顺序查找B-Tree叶子节点的吗?》中详细讲解过。

    1. 在这个阶段,与此同时,user表的所有DML操作日志写入Row Log,即《Row Log追加》中讲解的过程
    2. 重放该阶段产生的user表的Row Log日志到索引中,直到Row Log中的最后一个block,即《Row Log回放》中讲解的过程。

    Commit阶段

    1. 升级MDL锁为排它锁,禁止读写数据字典及旧user表
    2. 将Row Log中最后一个block,即内存中Block对应的DML日志插入索引树,过程参见DDL执行阶段中的步骤(7)
    3. 更新内存中的数据字典,关于数据字典的结构,我在《我们可以干预MySQL选择正确的执行计划吗?》中有讲解过。
    4. 将DDL执行操作记录redo日志
    5. rename副本ibd文件和frm文件为旧表名,即原user表的frm和ibd文件名

    在讲解完添加索引的过程后,我们发现影响业务DML操作的环节包含:

    1. 循环遍历旧表聚簇索引叶子节点的所有记录,如果表记录非常多,非常消耗CPU,如果DDL长时间占用CPU资源,势必会影响MySQL的连接数,导致MySQL处理DML操作的并发请求数下降
    2. 归并排序使用的磁盘临时文件做记录排序,如果文件中的已排序记录集非常多,那么,归并排序过程中产生大量的磁盘IO,在MySQL处理查询时,如果内存中没有查询的结果,此时,buffer pool又满了,触发刷脏行为,这时就会出现查询请求等待刷脏结束,查询响应变慢。

    可能这时候你会问,Prepare阶段和Commit阶段都加了排它锁,为什么这两个环节不影响DML操作呢?因为虽然这两个阶段都加了排它锁,但是,加锁后的操作都是小数据规模的操作,所以,加锁时间很短,对DML的影响不大,所以,可以忽略不计。

    那么,我们看看上面两个问题怎么解决呢?

    针对第一个问题,由于表中的原有记录的数量是由业务发展决定的,业务发展快,记录数就会多,这点我们无法控制,所以,针对表数据量大导致扫描聚簇索引变慢,我们只能规避DDL带来的风险,规避方法如下:

    1. 评估表中的数据量
    2. 观察MySQL的CPU使用率

    结合上面两个因素,如果数据量不大,那么,只要在非极端高峰期执行DDL,对DML的 影响是不大的。如果数据量很大,建议找到MySQL的CPU使用率比较低的情况下做DDL,保证不影响DML操作.。

    针对第二个问题,我们可以通过调整参数innodb_sort_buffer_size,将其调大,使归并排序来源的临时文件中已排序的block数量尽可能少,减少大量block的合并,从而降低磁盘IO

    主从模式下的问题

    平时我们用的最多的MySQL架构就是主从模式,所以,我们来看一下在这种模式下,在线DDL的过程是怎么样的呢?

    d99c0dc9c3029d48c4f4da22ae4a711e.png
    1. 结合《添加索引》中的过程,我们知道DDL和DML并行阶段,DDL一边执行,DML一边写入Row Log。如上图,左边在master中,DDL和INSERT,以及UPDATE并行执行,DDL在执行的同时,INSERT和UPDATE并行写入Row Log
    2. DDL和DML并行过程中,将DDL操作和并行的DML按序写入binlog。如上图,左边master将DDL和INSERT、UPDATE操作按序写入binlog,DDL第一、其次是INSERT,最后是UPDATE
    3. DDL执行结束,将master的binlog同步到slave上。如上图,将左边master的binlog中的三条操作同步到slave上
    4. 在slave上依次回放DDL和DML。如上图,右边在slave中依次执行DDL、INSERT和UPDATE

    通过上面这个过程,你应该已经想到,在DDL和DML并行的阶段,如果产生大量的DML操作,那么,在slave端回放这些DML操作会耗费大量的时间,会影响从库读的数据一致性。所以,这就是主从模式下,在线DDL的问题和风险。

    小结

    通过本章的讲解,我想你应该对MySQL的在线DDL的机制有了清晰的认识,同时,通过在线DDL机制的讲解,我们也发现了一些优化的方法:

    目的解决方法
    减少业务影响调大innodb_sort_buffer_size,降低磁盘IO
    避免DDL过程中写Row Log溢出调大innodb_online_alter_log_max_size
    一定要在高峰期做DDL建议使用第三方工具,比如,gh-ost,它是通过binlog完成DDL的,避免了扫描聚簇索引带来的CPU开销

    欢迎小伙伴们加入知识星球《看透MySQL》,提出宝贵的意见:

    https://t.zsxq.com/EQrBAYrt.zsxq.com
    展开全文
  • 1、什么索引(本质:数据结构)  索引是帮助MySQL高效获取数据的数据结构... 降低更新的速度,如对表进行update 、delete、insert等操作时,MySQL不急要保存数据,还要保存一下索引文件每次添加索引列的字...

    1、什么事索引(本质:数据结构)

      索引是帮助MySQL高效获取数据的数据结构。

    2、优势:

      1、提高数据检索的效率,降低数据库IO成本

      2、通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗

    3、劣势:

      降低更新表的速度,如对表进行update 、delete、insert等操作时,MySQL不急要保存数据,还要保存一下索引文件每次添加了索引列的字段,都会调整因为更新带来的键值变化后的索引信息。

    4、适合创建索引条件

      1.、主键自动建立唯一索引

      2、频繁作为查询条件的字段应该建立索引

      3、查询中与其他表关联的字段,外键关系建立索引

      4、单键/组合索引的选择问题,组合索引性价比更高

      5、查询中排序的字段,排序字段若通过索引去访问将大大提高排序效率

      6、查询中统计或者分组字段

    5、不适合创建索引条件

      1、表记录少的

      2、经常增删改的表或者字段

      3、where条件里用不到的字段不创建索引

      4、过滤性不好的不适合建索引

    转载于:https://www.cnblogs.com/karrya/p/11289073.html

    展开全文
  • 这是在创建的同时创建了组合索引,并且添加索引长度100,然后提示 ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the ...
  • 索引是一个排序的列表,这个列表中存储着索引的值和包含这个值的数据所在的物理地址,数据量庞大的时候索引可以快速定位需要查找的数据对应的物理地址,不需要扫描全的数据。下面以MySQL为例带你了解索引。1. ...
    22cdaffb5f2395ca54a7ac23aa403809.png

    索引

    索引是数据库查询操作中提升速度的一种手段,索引是一种数据结构。
    索引是一个排序的列表,这个列表中存储着索引的值和包含这个值的数据所在的物理地址,数据量庞大的时候,索引可以快速定位需要查找的数据对应的物理地址,不需要扫描全表的数据。

    下面以MySQL为例带你了解索引。

    1. 建表时创建索引

    123456
    CREATE TABLE t_table(    ID INT NOT NULL,    USER_NAME VARCHAR(16) NOT NULL,    INDEX USER_NAME_INDEX (USER_NAME), #单列索引    INDEX (ID,USER_NAME) #组合索引) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '注释';

    2. 建表后创建索引

    123
    ALTER TABLE t_TABLE ADD UNIQUE INDEX (ID);ALTER TABLE T_TABLE ADD INDEX (ID,USER_NAME);ALTER TABLE T_TABLE ADD PRIMARY KEY (ID);

    3. 查看已经创建的索引

    1
    show index from t_table;

    4. 删除索引

    12
    drop index user_name_index on t_table;alter table t_table drop index user_name_index;

    5. 查看索引使用情况(执行计划)

    1
    explain select * from t_table where user_name = 'Tom';
    1234567
    mysql> explain select * from t_test where username = 'Tom';+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | t_test | NULL       | ref  | t_test_index_username | t_test_index_username | 67      | const |    1 |   100.00 | Using index |+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

    说明:

    id:SELECT识别符。这是SELECT的查询序列号。

    select_type:SELECT类型。

    SIMPLE:简单SELECT(不使用UNION或子查询)PRIMARY:最外面的SELECTUNION:UNION中的第二个或后面的SELECT语句DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询UNION RESULT:UNION的结果SUBQUERY:子查询中的第一个SELECTDEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询DERIVED:导出表的SELECT(FROM子句的子查询)

    table:表名

    type:联接类型。是SQL性能的非常重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
    一般来说,得保证查询至少达到range级别。

    system:表仅有一行(=系统表)。这是const联接类型的一个特例。const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用rangeindex:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    Extra:该列包含MySQL解决查询的详细信息。

    Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    6. 模糊查询时,%如果在前面,那么不会使用索引。涉及到多个索引字段时,如果这些索引字段中,不存在主键索引的话,那么就会使用该使用的索引。多个索引时,先使用哪个索引后使用哪个索引,是由MySQL的优化器经过一些列计算后作出的抉择。当对索引字段进行 >, =, <=,not in,between …… and ……,函数(索引字段),like模糊查询%在字段前时,不会使用该索引.在实际使用时,如果涉及到多列,我们一般都不会将这些列一 一创建为单列索引,而是将这些列创建为组合索引。

    7. 组合索引的使用
    最左原则
    假设组合索引为:a,b,c的话;那么当SQL中对应有:a或a,b或a,b,c的时候,可称为完全满足最左原则;当SQL中对应只有a,c的时候,可称为部分满足最左原则;当SQL中没有a的时候,可称为不满足最左原则。
    注:SQL语句中的对应条件的先后顺序与创建组合索引中列的顺序无关。如果完全满足最左原则,所有的列都会走索引,部分满足最左原则,那么最左的列会走索引,剩下的不会走索引。不满足最左原则的话就不会走索引。

    8. 索引无法存储null值

    a. 单列索引无法储null值,复合索引无法储全为null的值。
    b. 查询时,采用is null条件时,不能利用到索引,只能全表扫描。
    为什么索引列无法存储Null值?
    a.索引是有序的。NULL值进入索引时,无法确定其应该放在哪里。(将索引列值进行建树,其中必然涉及到诸多的比较操作,null值是不确定值,无法比较,无法确定null出现在索引树的叶子节点位置。) 
    b.如果需要把空值存入索引,方法有二:其一,把NULL值转为一个特定的值,在WHERE中检索时,用该特定值查找。其二,建立一个复合索引。例如create index ind_a on table(col1,1);通过在复合索引中指定一个非空常量值,而使构成索引的列的组合中,不可能出现全空值。 

    9. 不适合键值较少的列(重复数据较多的列)
    假如索引列TYPE有5个键值,如果有1万条数据,那么WHERE TYPE = 1将访问表中的2000个数据块。再加上访问索引块,一共要访问大于200个的数据块。如果全表扫描,假设10条数据一个数据块,那么只需访问1000个数据块,既然全表扫描访问的数据块少一些,肯定就不会利用索引了。

    10. 前导模糊查询不能利用索引(like ‘%XX’或者like ‘%XX%’)
    假如有这样一列code的值为’AAA’,’AAB’,’BAA’,’BAB’ ,如果where code like '%AB'条件,由于前面是模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like 'A%',就可以查找CODE中A开头的CODE的位置,当碰到B开头的数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。

    11. 索引失效的几种情况
    a.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
    b.对于多列索引,不是使用的第一部分,则不会使用索引
    c.like查询以%开头
    d.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
    e.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

    12. MySQL主要提供2种方式的索引:B-Tree索引,Hash索引
    B树索引具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN)。相当于二分查找。哈希索引只能做等于查找,但是无论多大的Hash表,查找复杂度都是O(1)。
    显然,如果值的差异性大,并且以等值查找(=、 、in)为主,Hash索引是更高效的选择,它有O(1)的查找复杂度。
    如果值的差异性相对较差,并且以范围查找为主,B树是更好的选择,它支持范围查找。

    展开全文
  • 因为更新时候,Mysql不仅要保存数据,还要保存索引文件每次更新添加索引的字段,都会调整因为更新所带来的键值变化后的索引信息 索引也是一张,该保存了主键与索引字段,并指向实体的记录,所以索引列也...
  • 数据库添加索引什么能加快查询速度

    万次阅读 多人点赞 2018-03-23 09:47:06
    首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的...
  • 应该使用索引的情况 1.较频繁地作为查询条件的字段 2.经常用连接(join)的字段 3.经常需要根据范围进行搜索的字段 4.需要排序的字段 ...字段不在where语句出现时不要添加索引 数据量少的不要使用索引 ...
  • 什么样的和列需要建立索引

    千次阅读 2018-04-08 17:24:20
    数据量大的,经常进行查询操作的要建立索引1)连接用于多联合查询的约束条件的字段应当建立索引;...3)如果有些注定只会进行查询所有,也就没必要添加索引,因为查询全部只能进行全量搜索即扫描全。...
  • 问题:为什么数据库中增加索引会增快速度。 解答:数据库中本身存储的是数据的有结构的文件(为什么...但是前提是定长文件,但是对于变长文件就需要一样索引表索引表本身是定长文件。所以还是按照前面说的基础地...
  • 一、什么索引索引是对数据库中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库中的特定信息。二、索引的作用?索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能...
  • mysql如何添加索引

    2019-09-18 15:36:21
    当一个数据库中的数量足够大的时候索引就对快速搜索数据起到很重要的作用,当然规范的sql语句也对数据查询起到很重要的作用比如我这里有一张数据库le_lommo 里面有数据 二千多万条 select count(1) from le_...
  • 创建时候创建索引 格式: CREATE TABLE 表名[字段名 字段类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC] 普通创建语句 设置什么样的索引(唯一、...
  • 说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据...创建索引在创建时候添加索引 在创建以后添加索引 注意:1、索引需要占用磁盘空间
  • MySQL中,使用like关键字进行模糊查询到时候,是十分常见的应用场景,我们应该注意什么?1. 如果通配符(% _)在匹配的第一个字符,则进行全扫描。例如:explain extended select * from like1 where name like '%...
  • 如果select的时候不加remark这一列,就能正常完成查询。 . 那么问题来了,我不可能不用remark这一列的,也不可能改动说把remark这一列挪到新的一张中。 . 好了小伙伴们,我只想SELECT * FROM `xxx_...
  • MySQL索引到底是干什么的?这个问题自己一直理解的很模糊,只知道它相当于书的目录,能加快数据检索速度。但是要深入一点去说,它...之前我对MySQL索引的理解是在设计时候给字段添加一个索引的属性,然后查询的...
  • 索引

    2017-05-13 08:36:22
    索引作用: 提高查询速度   确保数据的唯一性 可以加速之间的连接,实现之间的...什么时候添加索引? 在WHERE,ORDER BY 子句中经常使用的字段 字段的值是多个 字段内容不是经常变化的 不宜过多添加索引
  • Coreseek多表索引搜索(转载)

    千次阅读 2016-08-02 11:23:11
    之前搭建的裤子库是单的,建索引时候也没考虑什么后续扩展,有小伙伴表示要玩多查询,于是研究了下…… 为嘛不用增量索引呢?本来一个就10G多够大了,而且增量索引还得不时添加&合并索引……只是本机搭着...
  • 生产上为了高效地查询数据库中的数据,我们常常会给中的字段添加索引,大家是否有考虑过如何添加索引才能使索引更高效。图片来自 Pexels添加的索引是越多越好吗?为啥有时候明明添加了索引却不生效?索引有哪些类型?...
  • 数据库索引是:数据库索引就像是一本书的目录一样,使用它可以让你在数据库里搜索查询的速度大大提升。而我们使用索引的目的就是,加快中的查找和排序。...聚集索引:我们在添加数据的顺序,...
  • 首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的...
  • 转自:http://blog.csdn.net/pengsidong/article/details/62104703,有添加索引好比书的目录,好比新华字典的拼音、偏旁部首查字,可以帮助人快速查找到需要的内容,当数据记录达到几十w级别的时候,索引的作用...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 405
精华内容 162
关键字:

表什么时候添加索引