精华内容
下载资源
问答
  • 为时间字段加索引

    千次阅读 2020-03-24 18:29:58
    文章目录为时间字段加索引(待更进)一、问题1、描述:日期不一致2、原因:时区不同3、解决方法: 时区修改二、datetime和varchar类型效率比较1、背景2、开始三、时间字段加索引1、聚集索引2、非聚集索引3、时间字段...

    为时间字段加索引(待更进)

    一、问题

    1、描述:日期不一致
    • 在centos7中运行docker,docker中运行mysql,在IDEA中将日期数据写入dates表中

    • dates表
      在这里插入图片描述

    • 插入日期数据

    @Test
    void contextLoads() {
      Date date = new Date(System.currentTimeMillis());
      System.out.println(date);   //Tue Mar 24 13:08:49 CST 2020
      questionMapper.insertdateTime(date);
    }
    
    • mysql中dates数据

    在这里插入图片描述

    2、原因:时区不同

    ​ 发现后台输入和数据库中的数据相差了8个小时,因为系统时区和docker中的mysql时区相差8个时区

    [root@localhost ~]# docker exec -it mysql01 /bin/bash
    root@1c202aea2496:/# mysql -uroot -p  
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2604
    Server version: 5.7.29 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | SYSTEM |
    +------------------+--------+
    2 rows in set (0.01 sec)
    

    备注:

    • 在连接mysql时记得有"-u"参数,否则会出现Ignoring query to other database错误
    3、解决方法: 时区修改
    mysql> set global time_zone = '+8:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | SYSTEM |
    +------------------+--------+
    2 rows in set (0.02 sec)
    
    • 再次插入日期数据
    Tue Mar 24 13:32:39 CST 2020
    
    • 数据查看
      在这里插入图片描述

    二、datetime和varchar类型效率比较

    ​ 我记得之前我是通过修改了my.conf文件,进而修改了docker中mysql的日期格式,起效果后,但是下次重启mysql时,启动不容器。打开错误日志后,告诉我my.conf里出现错误,我迫不得已将日期格式改了回来,将datetime改成了varchar类型,来存储时间。

    ​ 至于如何让docker中的mysql重启的,详见这里

    ​ 那么datetime和varchar在查找和内存消耗方面有什么区别吗?

    以下测试摘录于这里

    1、背景

    大家都知道数据库表字段设计得是否合理,对查询速度的快慢至关重要,下面做个简单的测试,看下差距有多大

    2、开始

    1、在数据库中新建两个表 test1(不合理的表)test2(合理表),两张表的 send_time 字段的类型不一样

    CREATE TABLE `test1` (
    	 `id` int(11) NOT NULL AUTO_INCREMENT,
    	`send_time` varchar(20) DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    CREATE TABLE `test2` (
    	 `id` int(11) NOT NULL AUTO_INCREMENT,
    	`send_time` datetime DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    

    2、新建存储过程,导入10 000 000 条测试数据(运行过程可能需要几分钟),运行结束后再将数据导入表test2保证两张表的数据一致

    BEGIN
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    	WHILE i<10000000 DO
    INSERT INTO test1(send_time) VALUES(from_unixtime(1541302365+FLOOR(rand()*154130236),"%Y-%m-%d %H:%i:%s"));
    SET i=i+1;
    END WHILE;
    COMMIT;
    END
    
    

    3、查看表信息可得:
    在这里插入图片描述
    在这里插入图片描述

    结论:varchar 表字段占用存储空间 是 datetime 表的三倍左右
    

    4、查询速度比较

    	SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	
    	结果比较
    	test1			test2
    	2.653s			1.833s
    	2.650s			1.866s
    
    

    5、给表添加索引(执行过程需要几分钟),并查询速度比较

    	-- 添加索引		
    	ALTER TABLE `test1` ADD INDEX `n_sendtime` (`send_time`) ;
    	ALTER TABLE `test2` ADD INDEX `n_sendtime` (`send_time`) ;
    
    	-- 查询速度比较:
    	SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
    	
    	结果比较
    	test1			test2
    	0.084s			0.048s
    	0.062s			0.046s
    	0.062s			0.048s
    	0.066s			0.047s
    
    

    4、结论

    • 合理的字段类型不论对 查询速度 或是 数据存储 都至关重要
    • 时间字段用dateTime等时间类型,不要用varchar类型

    三、时间字段加索引

    1、聚集索引
    • 表记录的排列顺序和与索引排列顺序一致

    • 一个表中只能拥有一个聚集索引

    • 修改慢。为了保证表中记录的物理和索引顺序一致,在记录插入的时候,会对数据页重新排序

    • 聚集索引的叶节点就是最终的数据节点

    2、非聚集索引
    • 逻辑顺序与磁盘上行的物理存储顺序不同
    • 一个表中可以拥有多个非聚集索引
    • 非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针
    动作描述使用聚集索引使用非聚集索引
    列经常被分组排序
    返回某范围内的数据不应
    一个或极少不同值不应不应
    小数目的不同值不应
    大数目的不同值不应
    频繁更新的列不应
    外键列
    主键列
    频繁修改索引列不应
    3、时间字段是否适合加索引

    以下摘自此处

    • 可以建立索引的;至于建立聚集索引或者是非聚集索引,那要看你这个时间字段的具体情况以及使用或变更频繁程度。

    • 一般来说,适合建立聚集索引的要求:“既不能绝大多数都相同,又不能只有极少数相同”的规则。

    • 先说说一个误区:有人认为:只要建立索引就能显著提高查询速度。这个想法是很错误的。建立非聚集索引,确实,一般情况下可以提高速度,但是一般并不会达到你想要的速度。只有在适当的列建立适当的(聚集)索引,才能达到满意的效果

    • 考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

    聚集索引和非聚集索引根本区别以及使用方式

    ​ 这就得看看项目中对该时间字段的具体操作了。

    ---- 待更新

    四、参考文档

    1、聚集索引和非聚集索引根本区别以及使用方式

    2、mysql索引的应用

    3、聚集索引和非聚集索引 简析与对比

    4、索引深入浅出(3/10):聚集索引的B树结构

    展开全文
  • oracle数据库给表加索引

    千次阅读 2021-03-26 09:44:01
    CREATE INDEX IDEN_IDNUMBER–>索引名称 ON SIDENTITY–>表名 ( IDNUMBER–>字段名 );
    12.创建索引
    create index 索引名称 on 表名 (字段名称);
    13.删除索引
    drop index 索引名; 
    14.创建组合索引
    create index 索引名 on 表名(列名1,,列名2);
    15.在数据库中查找表名
    select * from user_tables where  table_name like 'tablename%';
    16.查看该表的所有索引
    select * from all_indexes where table_name = 'tablename';
    17.查看该表的所有索引列
    select* from all_ind_columns where table_name = 'tablename';
     
    
    展开全文
  • 目录 前言: 1. 实例分析 1.1 如何创建循环函数 1.2 进行查询操作 2. 索引选择 2.1 优化器 ...2.3 Mysql怎么得到索引的基数的呢?...2.4 选错索引的原因?...2.4.2 为什么选错了索引?...3. 选错索引,处理方...

    目录

    前言:

    1. 实例分析

    1.1 如何创建循环函数

    1.2 进行查询操作

    2. 索引选择

    2.1 优化器

    2.2 扫描行数是怎么判断的?

    2.3 Mysql怎么得到索引的基数的呢?

    2.4 选错索引的原因?

    2.4.1 怎么看预计扫描的行数?

    2.4.2 为什么选错了索引?

    2.4.3 如果表的统计数据不准确,使用analyze table t

    3. 选错索引,处理方式

    3.1 使用force index 强制使用索引。

    3.2 在数据库内部修改解决

    4.  字符串加索引(使用前缀索引)

    4.1 怎么确定前缀索引要多少位?

    4.2 前缀索引对覆盖索引的影响?

    4.3 如果索引值大影响什么?

    4.4 倒叙存储?(这个可以结合项目来谈)

    4.5 使用hash来处理身份证(使用crc32来进行操作)

    4.6 varchar(m) 与字节数的关系?

    4.7 倒叙存储和hash字段存储处理身份证


    前言:

    Mysql一张表有多个索引,但是如果我们sql语句不强制使用哪个索引。

    那么mysql 就会自己去选取。

    但是有的时候,为什么执行很快的语句,mysql却执行的很慢?

    下面是学习Mysql实战45讲的笔记。

    1. 实例分析

    下面是Mysql实战45讲中的实例内容

    CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`)) ENGINE=InnoD

     然后插入10w的数据(1,1,1) (2,2,2)...(10w,10w,10w)这样。

    1.1 如何创建循环函数

    
    delimiter ;;
    create procedure idata()
    begin
      declare i int;
      set i=1;
      while(i<=100000)do
        insert into t values(i, i, i);
        set i=i+1;
      end while;
    end;;
    delimiter ;
    call idata();

    这个语句中我看的有点迷糊哈。

    delimiter 作用是改变结束的字符。

    比如,

    DELIMITER $$,那么

    DROP TRIGGER IF EXISTS `updateegopriceondelete`$$

    这个符号表示sql语句这句话已经结束执行。

    在上面代码这样就比较清晰了,;;代表着语句结束。

    最后还需要改回定义";", MYSQL的默认结束符为";"

    其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。 

    因为循环到end;; 这里才写完。

    1.2 进行查询操作

    select * from t where a between 10000 and 20000;

    因为a上面有索引,所以用到了索引a。

    之后模拟情况:

     

    B事务的话,把数据全删了,在执行idata().

    这时候,session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了.

    为什么这时候的B就不走索引了?

    如果使用force index(a)可以强制使用索引 速度会加快。

    知识点:


    set long_query_time=0;  //这个语句会让下面的语句都会被记录到慢查询日志里面
    select * from t where a between 10000 and 20000; /*Q1*/  //session b的查询 ,这个的结果是进行了全表扫描
    select * from t force index(a) where a between 10000 and 20000;/*Q2*/ //强制使用索引

    结论:mysql选错了索引。用了更长的执行时间。

    场景:因为我们不停的删除历史数据和新增数据。

     

    2. 索引选择

    2.1 优化器

    选择索引是优化器的工作。优化器是找到一个最优执行方案。

    在数据库中,扫描行数是最影响速度的因素之一。扫描行数越少,消耗的CPU资源也就越少。

    扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

    2.2 扫描行数是怎么判断的?

    MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。

    统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。

    总之 索引是有区分度的~

    可以使用 show index 方法,看到一个索引的基数。

     应该是cardinality,英文翻译也是基数。

    并不是很准确。但是这三个字段其实是一样的。

    2.3 Mysql怎么得到索引的基数的呢?

    因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择采样统计。

    有点像统计学的抽样,采用统计的时候,InnoDB默认会选择N个数据页,统计这些页面的不同值,得到一个平均值,

    然后乘上这个索引的页面数,就得到了索引的基数。

    而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。

    在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:设置为 on 的时候,表示统计信息会持久化存储。默认的 N 是 20,M 是 10。设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。

    2.4 选错索引的原因?

    有的时候不使用索引的原因是优化器判断的结果。

    2.4.1 怎么看预计扫描的行数?

    这个row就是预计扫描的行数。

    自己试一下。

    还是有一定的差距的,但是我没明白为什么count(*)不用主键的key来进行索引?

    count(*)不是全表扫描吗?之后研究一下

    2.4.2 为什么选错了索引?

    如果使用索引 a,每次从索引 a 上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的。

    而如果选择扫描 10 万行,是直接在主键索引上扫描的,没有额外的代价。

    优化器会估算这两个选择的代价,从结果看来,优化器认为直接扫描主键索引更快。当然,从执行时间看来,这个选择并不是最优的。

    但是为什么发生了这个问题,还是因为MYSQL扫描行数错误了,本来应该10000条数据,竟然认为扫描行数10W???

     

    所以可以说:优化器会看回表的代价和直接扫描的代价。(这个是根据扫描行数也有关)

    2.4.3 如果表的统计数据不准确,使用analyze table t

    重新统计索引信息之后,执行便正确了。

    在实践中,如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以采用这个方法来处理。

    3. 选错索引,处理方式

    3.1 使用force index 强制使用索引。

    MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价。

    但是force不优雅,而且索引改了名字可能会出现错误

    而且线上爆出的问题,如果修改这个SQL,加上force index 还要测试和筏板,不够迅速。

    3.2 在数据库内部修改解决

    有时候通过SQL语句来诱导数据库使用那个索引。

    在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。

    4.  字符串加索引(使用前缀索引)

    场景比如邮箱上面需要邮箱登录,邮箱就需要加上索引。

    mysql是支持前缀索引的,所以可以定义字符串的一部分作为索引。

    所以创建字符串索引的时候,如果不指定索引的长度,那么就会包含整个字符串。

    比如把邮箱的前六位作为索引

    alter table SUser add index index2(email(6));

    如果这样就是全部作为索引:alter table SUser add index index1(email);

    如果取前六位作为索引,占用空间会很小,这就是使用前缀索引的优势。

    但是使用前缀索引的缺点是会增加额外的记录扫描次数。

    select * from user where email = 'hehehelaozhu@xxx.com';

    使用整个字符串作为索引:

    先从索引树找到等于这个邮箱的记录,然后取出主键值然后去主键的树里面拿这个记录,进行比较是否满足条件,

    加入到结果集里面。

    然后找下一个记录,发现如果不满足,循环结束。

    只需要回表取一次数据,那么这时候系统认为扫描了一行。

    如果使用前缀索引:

    找到一个值就回表去判断一下是不是符合的,符合加入结果集。

    重复到前6个不是的时候停止循环。

    使用前缀索引后,可能会导致查询语句读数据的次数变多。

    使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。(实战)

    4.1 怎么确定前缀索引要多少位?

    区分度越好则重复键值越好,那么也就可以说越准确。

    select count(distinct email) as L from user;

    这样可以算出列上面有多少不同的值

    比如:

    可以根据这个查看区分度。

    4.2 前缀索引对覆盖索引的影响?

    因为前缀索引不全,所以覆盖索引并不能用到,得回表去判断。

    所以是否使用前缀索引最重要的因素是看用没用到覆盖索引

    PS: 突然有个问题,如果我这个字段是varchar(70),但是实际存的都只有5个长度的字符,那么给这个列加上索引,实际占用的是多少呢?

    搜集资料的结果:

    MySQL建立索引时如果没有限制索引的大小,索引长度会默认采用的该字段的长度,也就是说varchar(100)建立的索引存储大小要比varchar(10)建立索引存储大小大的多,加载索引使用的内存也更多。

    4.3 如果索引值大影响什么?

    如果索引值太大,那么一次取出来的数据页也就能放下的索引也就越少,那么搜索的效率就会很低。

    4.4 倒叙存储?(这个可以结合项目来谈)

    比如身份证号类,如果使用前缀索引,那么区分度会很低。

    正好结合我们的项目,进行身份证核验的需要身份证号。

    表里海量数据。

    那么如果倒叙存储一个列的话,那么就会可以增加一定的区分度,

    select field_list from t where id_card = reverse('input_id_card_string')

    实践中使用 count(distinct) 方法进行区分。

    4.5 使用hash来处理身份证(使用crc32来进行操作)

    在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。

    alter table t add id_card_crc int unsigned, add index(id_card_crc);

    因为整数索引会很快,所以建立整型会好很多,

    然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突,也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。


    mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

    如何将一个字符型的数据,唯一性地转为数值型呢?

    一般我们会采用CRC32函数进行这个转换,CRC32会把字符串,生成一个long长整形的唯一性ID(虽然科学证明不绝对唯一,但是还是可用的)。

    因为并不一定一定唯一,所以还需哟啊用全部数值来校验一下。

    PS:

    int 为4字节,那么比字符串就小了很多。

    • 最小值是 -2,147,483,648(-2^31);
    • 最大值是 2,147,483,647(2^31 - 1);
    • 相当于10位,但是数据库中还有一位存放符号。

    4.6 varchar(m) 与字节数的关系?

    汉字和字母在MYSQL里长度是不一样的。

    一个汉字占多少长度与编码有关:

    UTF-8:一个汉字=3个字节

    GBK:一个汉字=2个字节

    经过试验,varchar(1) 可以存放一个英文,也可以存一个中文字符。

    但是他们占用的字节数是不一样的。

    UTF-8编码:一个英文字符等于一个字节,一个中文(含繁体)等于三个字节。中文标点占三个字节,英文标点占一个字节

    4.7 倒叙存储和hash字段存储处理身份证

    都不支持范围查询。

    只能等值。

    不过真的需要查询某一个市的这种操作,我觉得可以用hash + 给身份证的前缀索引

    这样就完美解决了哈~

    我更倾向于使用hash~

    在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。

    从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

     

    展开全文
  • 在我还是个mysql新手的时候,看到有的同事给字段值分布很少的字段也加索引。 例如:订单状态字段只有6个值:0 待确认,1 已确认,2 已收货,3 已取消,4 已完成,5 已作废 在我理解mysql B+tree的原理后,很有必要...

    在我还是个mysql新手的时候,看到有的同事给字段值分布很少的字段也加索引,这违背了我看过的大部分mysql索引优化的文章内容,甚是疑惑。

    例如:订单状态字段只有6个值: 0 待确认,1 已确认,2 已收货,3 已取消,4 已完成,5 已关闭

    在我理解mysql B+tree的原理后,很有必要去实战这种情况到底有没有必要加索引。

    • 建立相关表数据

    建立带索引的表

    DROP TABLE if EXISTS `bool_index`;
    CREATE TABLE `bool_index` (
    	`id` INT (11) NOT NULL AUTO_INCREMENT,
    	`rand_id` VARCHAR (200) COMMENT '随机数',
    	`order_status` TINYINT (1) NOT NULL DEFAULT '0' COMMENT '订单状态.0待确认,1已确认,2已收货,3已取消,4已完成,5已作废',
    	`created_at` datetime NOT NULL,
    	PRIMARY KEY (`id`),
    	KEY `idx_order_status` (`order_status`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8;

    建立不带索引的表

    DROP TABLE if EXISTS `bool_no_index`;
    CREATE TABLE `bool_no_index` (
    	`id` INT (11) NOT NULL AUTO_INCREMENT,
    	`rand_id` VARCHAR (200) COMMENT '随机数',
    	`order_status` TINYINT (1) NOT NULL DEFAULT '0' COMMENT '订单状态.0待确认,1已确认,2已收货,3已取消,4已完成,5已作废',
    	`created_at` datetime NOT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8;

    通过存储过程造一些测试数据

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `proc_index`$$
    CREATE PROCEDURE proc_index()
    BEGIN
       DECLARE rand_id VARCHAR(120);
       DECLARE order_status INT(1);
       DECLARE i INT DEFAULT 0;
       DECLARE createtime DATETIME;
       -- 调试过程, 插入一些数据
       WHILE i < 10000 DO
         SET rand_id= SUBSTRING(MD5(RAND()),1,28);
    			-- 生成 订单状态值.0待确认,1已确认,2已收货,3已取消,4已完成,5已关闭
    		 SET order_status = FLOOR(RAND()*10)%6;
         SET createtime = NOW();
         INSERT INTO  `bool_index`(`rand_id`,`order_status`,`created_at`) VALUES(rand_id,order_status,createtime);
         INSERT INTO  `bool_no_index`(`rand_id`,`order_status`,`created_at`) VALUES(rand_id,order_status,createtime);
         SET i=i+1;
         END WHILE;
    END$$
    call proc_index();
    • 在表数据量不同的情况下测试结果:
    表数据量/耗时

    select * from bool_index

    where order_status=3 and rand_id='bd0bcd23960dbe0140bea563e7bd';

    select * from bool_no_index

    where order_status=3 and rand_id='bd0bcd23960dbe0140bea563e7bd';

    order_status=3数据总量
    1W0.002s0.002s约2000
    4W0.011s0.009s约8000
    8W0.021s0.021s约1.6W
    16W0.059s0.040s约3.2W
    32W0.142s0.110s约6.3W
    64W1.194s0.383s约12W
    100W2.761s0.563s约20W
    200W7.025s1.158s约40W

    通过比较,在数据量小于16W时,加索引和不加索引查询速度差别不大,数据大于16W时,随着数据量的增大,加索引的查询速度相对会越来越慢。

    • 为什么随着数据量的增加,反而加索引的查询比没加索引的更慢呢?

    如:第20001万条记录rand_id='56079ad22da839c1a00bd812a191'  order_status=3

    通过explain分析执行情况

    加索引扫描的数据rows=366798,不加索引rows=997976 (全表扫描),明明加索引的扫描条目更少,为何反而变慢了呢?

    举一个非常好理解的场景(通过索引读取表中20%的数据)解释一下这个有趣的概念:(例子来源 http://blog.itpub.net/519536/viewspace-612715/

    假设一张表含有10万行数据--------100000行
    我们要读取其中20%(2万)行数据----20000行
    表中每行数据大小80字节----------80bytes
    数据库中的数据块大小8K----------8000bytes
    所以有以下结果:
    每个数据块包含100行数据---------100行
    这张表一共有1000个数据块--------1000块

    上面列出了一系列浅显易懂的数据,我们挖掘一下这些数据后面的故事:

    通过索引读取20000行数据 = 约20000个table access by rowid = 需要处理20000个块来执行这个查询
    但是,请大家注意:整个表只有1000个块!

    所以:如果按照索引读取全部的数据的20%相当于将整张表平均读取了20次!!So,这种情况下直接读取整张表的效率会更高。)(索引还涉及多次回表查询问题)

    总结:禁止在更新十分频繁、区分度不高的属性上建立索引

    具体深层次的原因请先了解B+tree的底层原理

    https://blog.csdn.net/qq_24935119/article/details/108185311

    -

    展开全文
  • 4、给name字段不加索引加索引分别统计执行时间 查询结果10条记录 select * from big_data where name='lisi10'; 查询结果100条记录 select * from big_data where name='lisi100'; 查询结果1000条...
  • 时间字段加索引

    万次阅读 2018-11-14 22:05:20
    微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:...
  • 哪些字段可以加索引

    千次阅读 2019-03-27 10:05:58
    1.数据量超过300的表应该有索引; 2.经常与其他表进行连接的表,在连接字段上应该建立索引; 3.复合索引的几个字段是否经常同时以AND方式出现在Where子句中?如果是,则可以建立复合索引; 4.一个表如果是复合...
  • mybatis怎么加索引

    千次阅读 2019-03-01 11:18:12
    1,点表,点右键,选设计表,进入如下图的步骤,就了普通索引: 2,下面这种组合索引,是什么意思? 这种将两个字段都设为索引的作用是: 第一:唯一性。即例如当code和pid的值为3和4的时候,不能再有一条记录...
  • 大表数据加索引,加字段

    千次阅读 2017-11-20 23:54:56
    由于之前的经验,一张5000W的表,orderby 一个timestamp 字段,只要加了 tree 索引,分页10条的速度也是非常快的,于是决定对这张800W表的 timestamp加索引。但这个表正在运行,有大量的更新,在这个过程停掉服务去...
  • 如何给字符串字段加索引

    千次阅读 2019-10-31 17:15:16
    如果有这样一个场景,某个字段保存的是邮箱,然后需要给这个字段建立索引,有如下几种解决方案: 全字段索引 alter table user add index index1(email); 不建议用这种方式,因为索引树需要存储字段的全部值造成...
  • 现在有个需求,需要给该表的 username 字段加索引。 方案1: 利用凌晨系统不活跃时间,进行索引创建。 优点:简单方便 缺点:创建索引会锁表,会阻塞业务的执行,数据量越多,创建索引的时间越久,当有些业务要求24...
  • mysql为字段加索引

    千次阅读 2018-09-14 16:44:46
    1、添加普通索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 案例:ALTER TABLE ts_storage_partinfo_order_batch ADD INDEX IDX_ISB (id_source_bill); 2、添加主键索引 ALTER TABLE `table...
  • Oracle 表加索引

    万次阅读 2018-09-14 16:11:04
    首先,查看目前已经建立的索引 select index_name from all_indexes where table_name = 'table1'; 接着,建立索引 create index table1_album_idx on table (aid); create index table1_user_idx on table (userid)...
  • Mysql中使用sql语句加索引

    千次阅读 2019-02-27 11:38:16
    Mysql中使用sql语句对已有的表加索引: ALTER TABLE 表名 ADD index 索引名 ( `列名` ); 如果此文章有帮助到您,还请施舍施舍
  • 这里有一张表,并且准备往里面插入一些数据。如何给这张表加上合适的索引使得如下的...此时陷入了一个死胡同,求各位指教该如何加索引。 create table a( name varchar(128) default '', sex char(4) , grade ...
  • [InnoDB]性别字段为什么不适合加索引

    千次阅读 2018-11-01 21:06:12
    可以看到相同的sql,加索引之后比不加索引慢许多。 原因 在InnoDB中每一个表都会有聚集索引,如果表定义了主键,则主键就是聚集索引。一个表只有一个聚集索引,其余为普通索引。 索引的结构是B+树,非叶子节点...
  • status字段0或者1,判断是否审核通过,0只是最新几行会出现,后面几十万行,都是1.请问是否需要创建索引
  • Mysql时间字段加索引是否生效的问题

    千次阅读 2020-04-17 19:07:14
    在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。 参考1:https://www.cnblogs.com/tiancai/p/9518730.html 参考2:...
  • mysql加索引,数据库卡死

    万次阅读 2019-01-17 10:16:59
    通过sql日志看到主要是由于慢查询引起的,通过explain这个sql,发现主要是由于这个SQL没有命中索引,进行了全表扫描,慢是肯定了的。 为了优化这个页面,主要想到了从以下方法进行解决: 1)重写Sql,让查询命中...
  • Mysql-线上大表加索引

    千次阅读 2019-07-15 16:22:46
    题:给一个线上环境的大表,如何给它加索引? 数据量十几万以内时: 1.直接创建索引 ALTER TABLE table_name ADD INDEX index_name (column_list) 数据量过大时,直接执行加字段操作就会锁表,过大的表可能会达到数...
  • 近期在更新数据库时,经常会遇到一个问题,先插入数据在加索引,还是先加索引再插入数据 以下通过测试了几次得到如下结果如图: 这个表大概是200多万行,加了两个组合索引,最终数据长度是190MB,索引长度是6MB...
  • 现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:mysql> create table SUser( ID bigint ...
  • mysql为什么加索引就能快

    千次阅读 2019-04-22 01:47:07
    平时我们要优化 mysql 查询效率的时候,最常见的就是给表加上合适的索引了,那今天就来聊聊为什么索引就快了呢。 很多人会说索引就相当于一本书的目录,通过目录来找书中的某一页,确实是很快的,如果没有目录,...
  • MySQL 加索引 加字段 锁表问题 线上数据库 用的是阿里云 版本是5.6的 前两天 给文章表某个字段加注释 表竟然锁死了 (后来杀掉进程才恢复) 今天 给一张有4W条记录的表加唯一索引 也锁死了 卡了大约30分钟 也没执行...
  • mysql给表的字段加索引

    千次阅读 2019-03-22 10:55:35
    1、添加普通索引 ALTERTABLE`table_name`ADDINDEX index_name (`column`) 2、添加主键索引 ALTERTABLE`table_name`ADDPRIMARYKEY(`column`) 3、添加唯一索引 (UNIQUE) ALTERTABLE`table_name`ADDUNIQUE(`...
  • MySQL如何快速的给表加索引

    千次阅读 2018-01-26 18:17:06
    加索引时间久可能因为如下原因: 1:添加索引的字段表大 索引构建过程中需要操作的数据量较大 2:物理磁盘性能较差 索引结构构建的效率低 3:alter事务可能在等待其他锁释放 4:系统资源被其他服务占用,发生资源...
  • 所以大表加索引还是在没人用的时间加比较安全,要不就是先创建副本,再将表名改掉。加索引要避免锁表,需要先确定此时没有慢查询事务未提交,如果这个查询卡了30分钟,那么整个表的所有业务都会卡30分钟,这是很变态...
  • 时间字段都要加索引

    千次阅读 2018-01-04 17:30:00
    时间字段都要加索引

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 604,542
精华内容 241,816
关键字:

加索引