精华内容
下载资源
问答
  • 数据库——索引

    2020-02-04 21:53:21
    数据库——索引 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。...小数据量的表需要加索引; 索引一般加在常用来查询的字段上。 二、索引的分类 在一个表中,主键索引只...

    数据库——索引

    MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构

    获取数据需要0.5s和获取数据需要0.001s,是有很大区别的!

    因此,学习索引是很有必要的!

    索引的本质:索引是数据结构

    一、索引的原则

    • 索引不是越多越好;
    • 不要对进程变动数据加索引;
    • 小数据量的表不需要加索引;
    • 索引一般加在常用来查询的字段上。

    二、索引的分类

    在一个表中,主键索引只能有一个,唯一索引可以有多个

    • 主键索引 (PRIMARY KEY )
      • 唯一的标识,主键不可重复,只能有一个列作为主键;
    • 唯一索引 (UNIQUE KEY)
      • 避免重复的列出现,唯一索引可以重复,多个列都可以标识 唯一索引;
    • 常规索引 (KEY/INDEX)
      • 默认的 index 。通过 key 关键字来设置;
    • 全文索引 (FullText)
      • 在特定的数据库引擎下才有,MyISAM;
      • 快速定位数据;

    基础语法

    -- 索引的使用
    -- 1、在创建表的时候给字段增加索引
    -- 2、创建完毕后,增加索引
    
    -- 显示所有的索引信息
    SHOW INDEX FROM student
    
    -- 增加一个全文索引 (索引名) 列名
    ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
    
    -- EXPLAIN 分析sql执行的状况
    EXPLAIN SELECT * FROM student; -- 非全文索引
    
    EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
    

    三、测试索引

    先创建一个用户表

    CREATE TABLE `app_user` (
    	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
    	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
    	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
    	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男;1:女)',
    	`password` VARCHAR(100) NOT NULL COMMENT '密码',
    	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
    	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
    	CURRENT_TIMESTAMP,
    	PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
    

    插入100万条数据(用到了数据库编程)

    DELIMITER $$ -- 写函数之前必须要写,标志
    CREATE FUNCTION mock_data()
    RETURNS INT
    BEGIN
    	DECLARE num INT DEFAULT 1000000;
    	DECLARE i INT DEFAULT 0;
    	WHILE i<num DO
    		INSERT INTO
    app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用
    户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-
    100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    		SET i = i+1;
    	END WHILE;
    	RETURN i;
    END;
    
    SELECT mock_data(); -- 我用时 1min 3sec
    
    SELECT * FROM app_user WHERE `name` = '用户9999'; -- 我用时1.220sec
    

    创建索引

    -- id _ 表名 _ 字段名
    -- CREATE INDEX 索引名 on 表(字段)
    CREATE INDEX id_app_user_name ON app_user(`name`);
    
    SELECT * FROM app_user WHERE `name` = '用户9999'; -- 用时0.001sec
    EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 唯一定位了,用时0.001sec
    

    索引缺点

    • 索引在小数据量的时候,用处不大,优点显现不明显,还可能降低查询速度;
    • 但是在大数据量的时候,优点十分明显,查询速度大大提高。
    展开全文
  • 最近面试老是被问到数据库的优化方面的问题,推荐大家看《高性能mysql》 在数据库优化方面,随着业务量的增大,可以做一下优化: 索引的优化&...主键索引和唯一索引的区别? 怎么使用索引才能...

    最近面试老是被问到数据库的优化方面的问题,推荐大家看《高性能mysql》

    在数据库优化方面,随着业务量的增大,可以做一下优化:

    索引的优化>读写分离>集群

    作为一个小菜鸡,自然没有做过集群了,不过索引和读写分离还是可以聊一聊的。

    答案在网上很好找,就不写了。

    作为面试常问的索引优化,主要问一下几个方面:

    都在哪些字段加索引?

    主键索引和唯一索引的区别?

    怎么使用索引才能提高索引的命中?

    索引的最左前缀了解吗?

    索引是越多越好吗?为什么?

    索引的底层数据结构知道吗?推荐大家看看B-Tree和Hash,以及磁盘的存储结构?

    怎么查看是否命中了索引?

    读写分离,常见面试题:

    读写分离分离为什么能够提高性能?

    主从之间怎么实现同步?

    还有一些手写简单的SQL,连表查询等必会的基础知识就不过多的介绍了,相信大家肯定都会。

     

     

     

     

     

    展开全文
  • 数据库索引

    2020-03-03 09:28:30
    数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。 知道大家是不是跟我想得一样,我最想写的是索引,为啥呢? ...聚合索引和非聚合索引的区别?为什么说索引会降...

    写数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。

    不知道大家是不是跟我想得一样,我最想写的是索引,为啥呢?

    以下这个面试场景,不知道大家熟悉不熟悉:

    面试官:数据库有几千万的数据,查询又很慢我们怎么办?

    面试者:加索引

    面试官:那索引有哪些数据类型?索引是怎么样的一种结构?哪些字段又适合索引呢?B+的优点?聚合索引和非聚合索引的区别?为什么说索引会降低插入、删除、修改等维护任务的速度?……..

    面试者:面试官怎么出我们公司门来着😂。

    是的大家可能都知道慢了加索引,那为啥加,在什么字段上加,以及索引的数据结构特点,优点啥的都比较模糊或者甚至不知道。

    那我们也不多BB了,直接开始这次的面试吧。正文:

        1. 我看你简历上写到了熟悉MySQL数据库以及索引的相关知识,我们就从索引开始,索引有哪些数据结构?: Hash、B+

        2. 为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询,为何Mysql独独喜欢B+树?

    我先聊一下Hash:

    字段值所对应的数组下标是哈希算法随机算出来的,所以可能出现哈希冲突

    那么对于这样一个索引结构,现在来执行下面的sql语句:

    select * from sanguo where name='鸡蛋'

    可以直接对‘鸡蛋’按哈希算法算出来一个数组下标,然后可以直接从数据中取出数据并拿到所对应那一行数据的地址,进而查询那一行数据, 那么如果现在执行下面的sql语句:

    select * from sanguo where name>'鸡蛋'

    则无能为力,因为哈希表的特点就是可以快速的精确查询,但是不支持范围查询

    如果做成了索引,那速度也是很慢的,要全部扫描。

        3. 问个题外话,那Hash表在哪些场景比较适合?

    等值查询的场景,就只有KV(Key,Value)的情况,例如Redis、Memcached等这些NoSQL的中间件。

        你说的是无序的Hash表,那有没有有序的数据结构?

    有序数组,它就比较优秀了呀,它在等值查询的和范围查询的时候都很Nice。

        那它完全没有缺点么?

    不是的,有序的适合静态数据,因为如果我们新增、删除、修改数据的时候就会改变他的结构。

    比如你新增一个,那在你新增的位置后面所有的节点都会后移,成本很高。

        那照你这么说他根本就不优秀啊,特点也没地方放。

    此言差矣,可以用来做静态存储引擎啊,用来保存静态数据,例如你2019年的支付宝账单,2019年的淘宝购物记录等等都是很合适的,都是不会变动的历史数据。

        有点东西啊小伙子,那二叉树呢?

    二叉树的新增和结构如图:

    二叉树的结构我就不在这里多BB了,不了解的朋友可以去看看数据结构章节。

    二叉树是有序的,所以是支持范围查询的。

    但是他的时间复杂度是O(log(N)),为了维持这个时间复杂度,更新的时间复杂度也得是O(log(N)),那就得保持这棵树是完全平衡二叉树了。

        怎么听你一说,平衡二叉树用来做索引还不错呢?

    此言差矣,索引也不只是在内存里面存储的,还是要落盘持久化的,可以看到图中才这么一点数据,如果数据多了,树高会很高,查询的成本就会随着树高的增加而增加。

        如果用B树呢?

    同理来看看B树的结构:

    可以发现同样的元素,B树的表示要比完全平衡二叉树要“矮”,原因在于B树中的一个节点可以存储多个元素

    B树其实就已经是一个不错的数据结构,用来做索引效果还是不错的。

        那为啥没用B树,而用了B+树?

    一样先看一下B加的结构:

    我们可以发现同样的元素,B+树的表示要比B树要“胖”,原因在于B+树中的非叶子节点会冗余一份在叶子节点中,并且叶子节点之间用指针相连。

        那么B+树到底有什么优势呢?

    其实很简单,我们看一下上面的数据结构,最开始的Hash不支持范围查询,二叉树树高很高,只有B树跟B+有的一比。

    B树一个节点可以存储多个元素,相对于完全平衡二叉树整体的树高降低了,磁盘IO效率提高了。

    而B+树是B树的升级版,只是把非叶子节点冗余一下,这么做的好处是为了提高范围查找的效率

    提高了的原因也无非是会有指针指向下一个节点的叶子节点。

    小结:到这里可以总结出来,Mysql选用B+树这种数据结构作为索引,可以提高查询索引时的磁盘IO效率,并且可以提高范围查询的效率,并且B+树里的元素也是有序的

        那么,一个B+树的节点中到底存多少个元素最合适你有了解过么?

    额这个这个?卧*有点懵逼呀。

    过了一会还是没想出,只能老实交代:这个不是很了解咳咳。

        你可以换个角度来思考B+树中一个节点到底多大合适?

    B+树中一个节点为一页或页的倍数最为合适。

        为啥?

    因为如果一个节点的大小小于1页,那么读取这个节点的时候其实也会读出1页,造成资源的浪费。

    如果一个节点的大小大于1页,比如1.2页,那么读取这个节点的时候会读出2页,也会造成资源的浪费。

    所以为了不造成浪费,所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

        你提到了页的概念,能跟我简单说一下么?

    首先Mysql的基本存储结构是页(记录都存在里边):

       

    各个数据页可以组成一个双向链表

        而每个数据页中的记录又可以组成一个单向链表

        - 每个数据页都会为存储在它里边儿的记录生成一个页目录,在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录

        以其他列(非主键)作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录。

    所以说,如果我们写 select * from user where username='丙丙'这样没有进行任何优化的sql语句,默认会这样做:

        定位到记录所在的页

        - 需要遍历双向链表,找到所在的页

        从所在的页内中查找相应的记录

        - 由于不是根据主键查询,只能遍历所在页的单链表了

    很明显,在数据量很大的情况下这样查找会很慢!看起来跟回表有点点像。

        哦?回表你聊一下。

    卧槽,该死,我嘴干嘛。

    回表大概就是我们有个主键为ID的索引,和一个普通name字段的索引,我们在普通字段上搜索:

    select * from table where name = '丙丙'

    执行的流程是先查询到name索引上的“丙丙”,然后找到他的id是2,最后去主键索引,找到id为2对应的值。

    回到主键索引树搜索的过程,就是回表。不过也有方法避免回表,那就是覆盖索引

        哦?那你再跟我聊一下覆盖索引呗?

    !!! 我这个嘴。。。

    这个其实比较好理解,刚才我们是 select * ,查询所有的,我们如果只查询ID那,其实在Name字段的索引上就已经有了,那就不需要回表了。

    覆盖索引可以减少树的搜索次数,提升性能,他也是我们在实际开发过程中经常用来优化查询效率的手段

    很多联合索引的建立,就是为了支持覆盖索引,特定的业务能极大的提升效率。

        索引的最左匹配原则知道么?

    最左匹配原则

        索引可以简单如一个列 (a),也可以复杂如多个列 (a,b,c,d),即联合索引
        如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在(相等)遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了后续退化为线性查找
        因此,列的排列顺序决定了可命中索引的列数。

    例子:

        如有索引 (a,b,c,d),查询条件 a=1 and b=2 and c>3 and d=4,则会在每个节点依次命中a、b、c,无法命中d。(c已经是范围查询了,d肯定是排不了序了)

    总结

    索引在数据库中是一个非常重要的知识点!

    上面谈的其实就是索引最基本的东西,N叉树,跳表、LSM我都没讲,同时要创建出好的索引要顾及到很多的方面:

        最左前缀匹配原则。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则,MySQL会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE)就停止匹配。
        尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少。
       
        尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
        单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引(经指正,在MySQL5.0以后的版本中,有“合并索引”的策略,翻看了《高性能MySQL 第三版》,书作者认为:还是应该建立起比较好的索引,而不应该依赖于“合并索引”这么一个策略)。
        “合并索引”策略简单来讲,就是使用多个单列索引,然后将这些结果用“union或者and”来合并起来

    原文链接:https://blog.csdn.net/qq_35190492/article/details/104346265

    展开全文
  • mysql查看Date地址 第一步:就是查询data文件的位置, 但是根据网上的方法没有找到data这个文件夹 。 如图一 说明这个这个路径是错误 然后又找了些资料发现果然路径找错了 在...MySQL聚集索引和非聚集索引的区别 图四

    mysql查看Date地址

    第一步:就是查询data文件的位置, 但是根据网上的方法没有找到data这个文件夹 。
    如图一 说明这个这个路径是错误 然后又找了些资料发现果然路径找错了 在本地磁盘C的文件夹不一样 仔细看图二的文件夹是ProgramData这个名称
    图一在这里插入图片描述
    图二
    在这里插入图片描述
    仔细核对对了本地的文件名和数据库名发现是一致的 如图三

    图三
    在这里插入图片描述
    但是发现一个数据库的一个文件夹,在本地的数据有两个文件夹名称,但是名称的后缀不一样,那这个这两个文件的后缀有什么用呢?

    MySQL聚集索引和非聚集索引的区别

    图四
    在这里插入图片描述
    我们看到这个数据库的搜索引擎是innoDB
    innoDB对应文件夹的意义:
    *.frm 表的结构
    *.ldb 索引和数据

    图五
    在这里插入图片描述
    图五为了方便我就把本地文件夹的数据和数据库截图到一起了。
    我们可以看到 表名为test0001有三个对应的文件夹,有没看出两个表有什么不同吗?对的 是搜索引擎不同表名为test0001的搜索引擎为MyISAM
    MyISAM对应文件夹的意义:
    *.frm 表的结构
    *.MYD 表的数据
    .MYI 表的索引
    是的MyISAM比InnoDB和区别就是数据和索引分开用文件夹存储了起来,这就是我主要讲的聚集索引和非聚集索引的区别,聚集索引就是InnoDB这种数据和索引在一起的称为聚集索引,而MyISAM索引和数据分开的就是非聚集索引。
    ps:文件夹
    .opt记录数据库的选项,数据库的字符集设置。

    展开全文
  • 聚合索引和非聚合索引的区别?为什么说索引会降低插入、删除、修改等维护任务的速度?……… 面试者:面试官怎么出我们公司门来着?。 是的大家可能都知道慢了加索引,那为啥加,在什么字段...
  • 数据库索引-2

    2021-01-05 15:23:04
    前言 写数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询...聚合索引和非聚合索引的区别?为什么说索引会降低插入、删除、修改等维护任务的速度?…….. 正文 索引有哪些数据结构 Hash,BTree 哈
  • 用index加索引,再用show create table命令查看得到是key,不是index了,这原因是什么。 这两者之间有什么区别没哦? 附上代码: create table tbl01( id int(10), key key_id (id) ); show create table tbl01...
  • 援引自添加链接描述 1、索引的底层实现原理优化 B+树,经过优化的B+树,主要是在...4、简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面) 索引是一种特殊的文件(Inn
  • 前言 写数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。 知道大家是不是跟我想得一样,我最想写的是索引,为啥呢? 以下这个面试场景,...聚合索引和非聚合索引的区别?为什么说...
  • 数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。 知道大家是不是跟我想得一样,我最想写的是索引,为啥呢? ...聚合索引和非聚合索引的区别?为什么说索引会降...
  • 今天创建了一个分区表content,随后在分区列上创建了本地索引和全局索引,但是在执行语句“ select id,book_num from content where book_num=50 ;”时发现,若创建为本地索引执行计划走“ INDEX RANGE SCAN”,若...
  • 点赞再看,养成习惯,微信搜索【三太子敖丙】我所有文章都在这里,本文 GitHub https://github.com/JavaFamily 已收录,有一线大厂面试完整考点,文末...聚合索引和非聚合索引的区别?为什么说索引会降低插入、删除、修
  • 前言 写数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。 知道大家是不是跟我想得一样,我最想写的是索引,为啥呢? 以下这个面试场景,...聚合索引和非聚合索引的区别?为什么说索...
  • sql语句删除数据库几种方式及区别deletetruncatedrop使用场景 delete 只删除表中数据,删除表结构,可以与where连用,删除特定行; 记录日志 可以rollback恢复数据 删除索引 返回删除记录数 eg: delete ...
  • 聚合索引和非聚合索引的区别?为什么说索引会降低插入、删除、修改等维护任务的速度?…….. 面试者:面试官怎么出我们公司门来着?。 是的大家可能都知道慢了加索引,那为啥加,在什么字段上加,以及索引的数据...
  • mysql数据库的优化

    2019-02-12 10:18:04
    MyISAMInnoDB的区别: InnoDB支持事务,MyISAM支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begincommit之间,组成一个事务; InnoDB支持外键,...
  • 索引相关总结

    2019-09-02 18:57:53
    4.哪些情况需要加索引 5.聚集索引和非聚集索引 6.MyIsAm和InnoDB的区别 7.MyIsAm和InnoDB的索引实现 8.数据库中的表连接 9.count(1)、count(*)、count(列名)的区别 1.索引的优点 避免全表扫描去查询数据,...
  • mysql数据库优化

    2020-02-26 18:07:18
    一、为什么要进行数据库的优化 二、优化的方式 ...然后给大家看一下添加索引和不加索引运行的区别,以下面SQL语句为例(我这里使用的表都是之前创建好的,数据结构都是一样的,都有两千万条数据)...
  • 数据库优化

    2018-11-22 10:48:43
    MyISAMInnoDB的区别: InnoDB支持事务,MyISAM支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begincommit之间,组成一个事务; InnoDB支持外键,而...
  • MyisamInnodb的区别

    2019-06-19 20:38:00
    1、myisam只支持表级锁 Innodb支持...5、myisam本身存储了表总数据行,Innodb没有存储,查总行数myisam更快,如果了查询条件两者就没有区别了 MyisamInnodb究竟该怎么选择 数据库有外键吗? 需要事务支持吗...
  • 并且,最终说来,产生价值是绚丽界面现代化输入方式,而是存放在数据库数据。不幸是,虽然关系型数据库历经了约30年发展,有成熟理论大量实践基础,但是,大多数设计、开发人员在设计...
  • 并且,最终说来,产生价值是绚丽界面现代化输入方式,而是存放在数据库数据。不幸是,虽然关系型数据库历经了约30年发展,有成熟理论大量实践基础,但是,大多数设计、开发人员在设计...
  • ER模型ER图,数据抽象语义数据建模,UML类图表示法,基本关系模型,关系代数关系演算,SQL,规范化,磁盘上组织记录文件主要方法,文件的索引技术,查询处理与优化,以及物理数据库的设计与调优。...
  • 5.5.1 ODB与RDB概念设计的区别 99 5.5.2 EER模式到ODB模式的映射 100 小结 101 复习题 102 练习题 102 参考文献 102 第6章 对象-关系扩展-关系系统 104 6.1 SQL及其对象-关系特性概述 104 ...
  • hash二叉树红黑树B树B+树InnoDBMyISAM文件存储索引区别MySQL文件存储结构索引InnoDBMyISAM存储引擎对比索引的分类索引基本知识 开篇 一起来看一下我们工作中常见的几个数据库问题 数据库常见的慢查询优化方式...

空空如也

空空如也

1 2 3 4 5 ... 11
收藏数 219
精华内容 87
关键字:

数据库加索引和不加索引的区别