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

    千次阅读 2019-07-09 20:35:35
    文章地址: https://blog.csdn.net/hollis_chuang/article/details/95167242
    展开全文
  • 数据库面试题索引sql优化

    热门讨论 2010-05-28 21:59:50
    数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引...
  • 数据库索引相关面试题

    万次阅读 多人点赞 2019-01-05 10:50:18
    1、索引的底层实现原理和优化 B+树,经过优化的B+树,主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用默认自增的主键作为主索引。   3、什么情况下设置了索引但无法使用...

    1、索引的底层实现原理和优化

    B+树,经过优化的B+树,主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

     

    3、什么情况下设置了索引但无法使用 

    • 以“%”开头的LIKE语句,模糊匹配
    • OR语句前后没有同时使用索引
    • 数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)

     

    4、简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

    普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说, 唯一索引可以保证数据记录的唯一性。

    主键,是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字PRIMARY KEY 来创建。

    索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引,这就是联合索引。

    索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件。

     

    5、 对于关系型数据库而言,索引是相当重要的概念,请回答有关索引的几个问题:

    a)、索引的目的是什么?

    快速访问数据表中的特定信息,提高检索速度

    创建唯一性索引,保证数据库表中每一行数据的唯一性。

    加速表和表之间的连接

    使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间

    b)、索引对数据库系统的负面影响是什么?

    负面影响:

    创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。

    c)、为数据表建立索引的原则有哪些?

    在最频繁使用的、用以缩小查询范围的字段上建立索引。

    在频繁使用的、需要排序的字段上建立索引

    d)、 什么情况下不宜建立索引?

    对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。

    对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等

     

    6、你怎么看到为表格定义的所有索引?

    索引是通过以下方式为表格定义的:

    SHOW INDEX FROM <tablename>;

     

    7、实现索引的方式? 索引的原理? 索引的代价? 索引的类型?

    实现索引的方式有两种:针对一张表的某些字段创建具体的索引,如对oracle: create index 索引名称 on 表名(字段名);在创建表时为字段建立主键约束或者唯一约束,系统将自动为其建立索引。

    索引的原理:根据建立索引的字段建立索引表,存放字段值以及对应记录的物理地址,从而在搜索的时候根据字段值搜索索引表的到物理地址直接访问记录。

    引入索引虽然提高了查询速度,但本身占用一定的系统存储容量和系统处理时间,需要根据实际情况进行具体的分析.

    索引的类型有:B树索引,位图索引,函数索引等。

     

    8、聚簇索引和普通索引在不同的SQL语句中哪个效率更高?(聚簇索引和普通索引的区别)

    概念类:

    聚合函数? Session的定义和用法? Oracle的存储过程 ? 什么是构造函数? 

     

    9、怎样创建一个一个索引,索引使用的原则,有什么优点和缺点 

    创建标准索引: CREATE  INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 

    创建唯一索引: CREATE unique INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 

    创建组合索引: CREATE INDEX 索引名 ON 表名 (列名1,列名2)  TABLESPACE 表空间名; 

    创建反向键索引: CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名; 

    索引使用原则: 

    索引字段建议建立NOT NULL约束 

    经常与其他表进行连接的表,在连接字段上应该建立索引; 

    经常出现在Where子句中的字段且过滤性很强的,特别是大表的字段,应该建立索引; 

    可选择性高的关键字 ,应该建立索引; 

    可选择性低的关键字,但数据的值分布差异很大时,选择性数据比较少时仍然可以利用索引提高效率 

    复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替: 

    A、正确选择复合索引中的第一个字段,一般是选择性较好的且在where子句中常用的字段上; 

    B、复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引;否则单字段索引; 

    C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引; 

    D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段; 

    E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引; 

    频繁DML的表,不要建立太多的索引; 

    不要将那些频繁修改的列作为索引列; 

    索引的优缺点: 

    有点: 

    1. 创建唯一性索引,保证数据库表中每一行数据的唯一性 

    2. 大大加快数据的检索速度,这也是创建索引的最主要的原因 

    3. 加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 

    4. 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 

    缺点: 

    1. 索引创建在表上,不能创建在视图上 

    2. 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加 

    3. 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大 

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

     

    10、索引的实现方式

    都是 B+树索引, Innodb 是索引组织表, myisam 是堆表, 索引组织表和堆表的区别要熟悉

     

    11、Oracle索引分为哪几类,说出唯一索引和位图索引的概念。

    Oracle索引有B树索引,位图索引,函数索引,簇索引等。

    唯一索引也是B树索引的一种,它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。

    位图索引并不是采用象唯一索引那样存储(索引字段值,记录ROWID)来创建索引段的,而是为每一个唯一的字段值创建一个位图,位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。

     

    12、如何写 sql 能够有效的使用到复合索引。

    由于复合索引的组合索引,类似多个木板拼接在一起,如果中间断了就无法用了,所以要能用到复合索引,首先开头(第一列)要用上,比如index(a,b) 这种,我们可以select table tname where a=XX 用到第一列索引 如果想用第二列 可以 and b=XX 或者and b like‘TTT%’

     

    13、当数据表中A、B字段做了组合索引,那么单独使用A或单独使用B会有索引效果吗?(使用like查询如何有索引效果)

    看A、B两字段做组合索引的时候,谁在前面,谁在后面,如果A在前,那么单独使用A会有索引效果,单独使用B则没有,反之亦然。同理,使用like模糊查询时,如果只是使用前面%,那么有索引效果,如果使用双%号匹配,那么则无索引效果

     

    14、数据库索引的实现(B+树介绍、和B树、R树区别)

    http://blog.csdn.net/kennyrose/article/details/7532032

    http://www.xuebuyuan.com/2216918.html

     

    15、索引的使用注意事项

    https://www.cnblogs.com/zlingh/p/3883716.html

     

    16、MYsql 的索引原理,索引的类型有哪些,如何创建合理的索引,索引如何优化。

    索引是通过复杂的算法,提高数据查询性能的手段。从磁盘io到内存io的转变 

    普通索引,主键,唯一,单列/多列索引建索引的几大原则 

    1.最左前缀匹配原则,非常重要的原则,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的顺序可以任意调整。 

    2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式 

    3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 

    4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 

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

    http://tech.meituan.com/mysql-index.html 

    http://www.cnblogs.com/cq-home/p/3482101.html

     

    17、聚集索引和非聚集索引的区别。

    “聚簇”就是索引和记录紧密在一起。 

    非聚簇索引 索引文件和数据文件分开存放,索引文件的叶子页只保存了主键值,要定位记录还要去查找相应的数据块。

     

    18、数据库中 BTREE 和 B+tree 区别。

    B+是btree的变种,本质都是btree,btree+与B-Tree相比,B+Tree有以下不同点: 

    每个节点的指针上限为2d而不是2d+1。 

    内节点不存储data,只存储key;叶子节点不存储指针。

    http://lcbk.net/9602.html 

    Btree 怎么分裂的,什么时候分裂,为什么是平衡的。 

    Key 超过1024才分裂B树为甚会分裂? 因为随着数据的增多,一个结点的key满了,为了保持B树的特性,就会产生分裂,就向红黑树和AVL树为了保持树的性质需要进行旋转一样!

     

    展开全文
  • 数据库常见面试题——索引

    千次阅读 2019-07-31 16:38:02
    如何设计一个关系型数据库 索引模块 为什么要使用索引 快速查询数据 什么样的信息能够成为索引 主键、唯一键以及普通键等 索引的数据结构 生成索引,建立二叉查找树进行二分查找树(平衡二叉树、红黑树) ...

    如何设计一个关系型数据库

    索引模块

    为什么要使用索引

    快速查询数据

    什么样的信息能够成为索引

    主键、唯一键以及普通键等

    索引的数据结构

    • 生成索引,建立二叉查找树进行二分查找树(平衡二叉树、红黑树)
    • 生成索引,建立B-Tree结构进行查找
    • 生成索引,建立B+-Tree结构进行查找(MySQL
    • 生成索引,建立Hash结构进行查找

    B-Tree

    定义:

    • 根节点至少包括两个孩子
    • 树中每个节点最多含有m个孩子(m>=2)
    • 除根节点和叶节点外,其他每个节点至少有ceil(m/2)个孩子
    • 所有叶子节点都位于同一层

    B-Tree举例

    B+-Tree

    • 非叶子节点的子树指针与关键字个数相同
    • 非叶子节点的子树指针p[i],指向关键字值[k[i],k[i+1])子树
    • 非叶子节点仅用来索引,数据都保存在叶子节点中
    • 所有叶子节点均有一个链指针指向下一个叶子节点

    B+Tree更适合用来做存储索引

    • B+树的磁盘读写代价更低
    • B+树的查询效率更加稳定
    • B+树更有利于对数据库的扫描

    Hash索引

    缺点:

    • 仅仅能满足“=”,“IN”,不能使用范围查询
    • 无法被用来避免数据的排序操作
    • 不能利用部分索引键查询
    • 不能避免表扫描
    • 遇到大量Hash值相同的情况后性能并不一定就会比B+Tree高

    BitMap(了解)

    所以主流的数据库采用的是B+树做索引

    密集索引和稀疏索引的区别

    • 密集索引文件中的每个搜索码值都对应一个索引值(叶子节点保存的不仅仅是键值还保存了位于同一行记录里的其他列的信息,由于密集索引决定了表的排列顺序,一个表只能有一个排列顺序,所以一个表只能有一个密集索引)
    • 稀疏索引文件只为索引码的某些值建立索引项

    MySQL主要有两种存储引擎:InnoDB(有且仅有一个密集索引,索引和数据存储在一个文件)和MyISAM(全部是稀疏索引,索引和数据存储在不同文件)

    InnoDB

    • 若一个主键被定义,该主键则作为密集索引
    • 若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
    • 若不满足以上条件, innodb內部会生成一个隐藏主键(密集索引)
    • 非主键索引存储相关键位和其对应的主键值,包含两次查找

    索引部分常见面试题

    如何定位并优化慢查询Sql

    (1)根据慢日志定位慢查询sql

    慢日志的定义:用来记录我们执行的比较慢的SQL

    我们可以通过如下语句查询MySQL对数据库慢查询的设置

    show variables like '%query%'

    查询结果:

    查询慢查询的数量:

    show status like '%slow_queries%'

    我们修改一下设置:

    set global slow_query_log = on;--设置慢查询日志为开启状态
    set global long_query_time = 1;--设置慢查询日志时间阈值为1s

    注意:这样修改只会在本次服务器开启有用,服务器重启之后又会变为默认值,我们将配置文件修改了就可以永久生效了

    这样我们就会记录下所有运行时间超过1s的SQL语句,可以在上述的慢日志存储路径中查看

    我们先来制造一个慢查询用到的数据库(共40W条数据)

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Random;
    
    /**
     * @author lmm E-mail:violet_mmhh@163.com
     * @time 时间:2019年7月31日
     * @function 功能:制造10万条数据库用于测试SQL慢查询
     */
    public class SlowqueryTest {
    	public static Connection getConnection() {
    		Connection conn;
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		}
    		try {
    			String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
    			String user = "root";
    			String pwd = "数据库密码";
    			conn = DriverManager.getConnection(url, user, pwd);
    		} catch (SQLException e) {
    			e.printStackTrace();
    			conn = null;
    		}
    		return conn;
    	}
    
    	public static void main(String[] args) {
    		Connection conn = getConnection();
    		String sql = null;
    		Random rand = new Random();
    		try {
    			Statement stat = conn.createStatement();
    			for (int i = 0; i < 200000; i++) {
    				System.out.println(i);
    				int x = rand.nextInt(100000);
    				// sql = "insert into slowquerytest values(default,'光头" + i +
    				// "')";
    				// sql = "insert into slowquerytest values(default,'有头发" + i +
    				// "')";
    				sql = "insert into slowquerytest values(default,'流浪" + x + "')";
    				stat.execute(sql);
    			}
    		} catch (Exception e) {
    			// TODO: handle exception
    		}
    
    	}
    }
    

    我们尝试形成一个慢查询记录:

    select name from slowquerytest order by name desc

     

    (2)使用 explain等工具分析sql

    explain select name from slowquerytest order by name desc

    Explain关键字段:

    • id:表示SQL的执行顺序,id越大越先执行(复合查询里面的语句id大、先执行)
    • type:表示MySQL找到需要的数据行的方式(system>const>eq_ref>ref>fulltext>ref_or_null>index_merge
    • >unique_subquery>index_subquery>range>index>all,其中index和all表示本次查询是全表扫描,因此很有可能可以(需要)优化)
    • extra:如下图所示

    (3)修改sql或者尽量让sql走索引

    我们把查询语句改为使用索引来看一下效果

    select id from slowquerytest order by id desc

    运行结果

    可以看到查询效率直接有1.495秒变成了0.187秒,提升了87.5%的效率(TQL)

    可惜的是,我们有时候查的必须要是按name来排序,这是我们可以考虑给字段加索引

    我们尝试以下语句

    alter table slowquerytest add index index_name(name)
    --给name字段加索引,这条语句虽然执行慢但不是查询语句,不会进入慢查询记录

    再次查询name,结果如下

    注意其他一些内容:

    explain select count(id) from slowquerytest force index (primary)--force index 强制使用指定索引

    联合索引的最左匹配原则的成因

    什么是联合索引的最左匹配原则

    参考

    • 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a=3 and b=4 and c>5 and d=6 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以仼意调整。
    • =和in可以乱序,比如a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,mysq的查询优化器会帮你优化成索引可以识别的形式

    成因

    • MySQL创建符合索引的规则是:首先会对复合索引的最左边(第一个索引)字段的数据进行排序(第一个字段绝对有序)
    • 在对第一个字段排序的基础上,在对第二个字段进行排序(第二个字段相对于第一个字段有序,事实上它本身是无序的,这就是为什么他不能越过第一个字段来使用符合索引的原因了)
    • 依次类推

    索引是建立的越多越好越吗

    答案当然是否定的,因为:

    • 数据量小的表不需要建立索引,建立会增加额外的索引开销(就像我们读一个只有两页的书,没有必要建立目录)
    • 数据变更需要维护索引,因此更多的索引意味着更多的维护成本
    • 更多的索引意味着也需要更多的空间

    数据库索引什么时候会失效

    1. 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
    2. 对于多列索引,不是使用的第一部分,则不会使用索引
    3. like查询是以%开头
    4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
    5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
    展开全文
  • 54、数据库索引 索引的优缺点   优点:   1、大大加快数据的检索速度;   2、创建唯一性索引,保证数据库表中每一行数据的唯一性;   3、加速表和表之间的连接;   4、在使用分组和排序子句进行数据检索时...

    54、数据库索引

    索引的优缺点

      优点:
      1、大大加快数据的检索速度;
      2、创建唯一性索引,保证数据库表中每一行数据的唯一性;
      3、加速表和表之间的连接;
      4、在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。
      缺点:
      1、索引需要占物理空间;
      2、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低数据的维护速度。

    索引的分类

      1、唯一索引: 是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中不可以重复。

      2、非唯一索引: 是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中可以重复,不要求唯一。

      3、主键索引(主索引): 是唯一索引的特定类型。表中创建主键时自动创建的索引,一个表只能建立一个主索引。

      4、聚集索引(聚簇索引、Innodb):表中记录的物理顺序与键值的索引顺序相同。 因为真实数据的物理顺序只有一种,所以一个表只能有一个聚集索引。叶子节点(B+树)存储真实的数据行,不再有另外单独的数据页。

      5、非聚集索引(Mylsam):表中记录的物理顺序与键值的索引顺序不同。这也是非聚集索引与聚集索引的根本区别。叶子节点并非数据节点,而是每一个指向真正数据行的指针。
      聚集索引与非聚集索引的区别:
      1)、聚集索引的优缺点:优点是查询速度快,因为一旦具有第一个索引值的记录被找到,具有连续索引值的记录也一定物理的紧跟其后。缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引顺序的一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据重排,降低了执行速度。在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效。
      建议使用聚集索引的场合为:A.某列包含了小数目的不同值。B.排序和范围查找。
      2)、聚集索引和非聚集索引都采用了B+树的结构,但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点,而非聚集索引的叶节点仍然是索引节点。
      3)、非聚集索引添加记录时,不会引起数据顺序的重组。

      看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次 B+树查找,这不是多此一举吗?聚簇索引的优势在哪?
      1)、由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键 Id 来组织数据,获得数据更快。
      2)、辅助索引使用主键作为"指针",而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时,辅助索引的维护工作,InnoDB 在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置会随着数据库里数据的修改而发生变化,使用聚簇键索引就可以保证不管这个主键 B+ 树的节点如何变化,辅助索引树都不受影响。

      6、组合索引: 基于多个字段而创建的索引就称为组合索引,组合索引的使用要遵从最左前缀。在最左前缀原则中,范围查询会导致组合索引半生效,where子句有or出现还是会遍历全表。

    Mysql怎么增加一个索引

      创建索引:create index idx1 on table(col1, col2, col3);
      添加索引:alter table tablename add index indexname(col1, col2);



    55、数据库索引的实现

      目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree 是数据库系统实现索引的首选数据结构。
      在 MySQL 中,索引属于存储引擎级别的概念, 不同存储引擎对索引的实现方式是不同的。

    MyISAM 索引实现(非聚集索引)

      MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。

      图8是一个MyISAM表的主索引(Primary Key)示意,可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示:

      同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法会首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

    InnoDB 索引实现(聚集索引)

      MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶节点data 域保存了完整的数据记录(第一个重大区别)。 这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。

      上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。
      1、InnoDB 要求表必须有主键(MyISAM 可以没有), 如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。
      2、尽量在 InnoDB 上采用自增字段做表的主键。 因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。
      这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
      第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。 换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。
      聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
      引申:为什么不建议使用过长的字段作为主键?因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

    总结


      InnoDB使用的是聚簇索引,将主键组织到一棵 B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照 B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对 Name 列进行条件搜索,则需要两个步骤:第一步在辅助索引 B+树中检索 Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次 B+树检索操作,最终到达叶子节点即可获取整行数据。
      MyISAM 使用的是非聚簇索引,非聚簇索引的两棵 B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引 B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗 B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。



    56、为什么使用B+树作为索引

    B/B+ 树性能分析

      1、n 个节点的平衡二叉树的高度为 H(即 logn),而 n 个节点的 B/B+树的高度为logt((n+1)/2)+1;
      2、若要作为内存中的查找表,B 树却不一定比平衡二叉树好,尤其当 m 较大时更是如此。因为查找操作 CPU 的时间在 B-树上是 O(mlogtn)=O(lgn(m/lgt)),而 m/lgt>1;所以 m较大时O(mlogtn)比平衡二叉树的操作时间大得多。因此在内存中使用B树必须取较小的m。(通常取最小值 m=3,此时 B-树中每个内部结点可以有 2 或 3 个孩子,这种 3 阶的 B-树称为 2-3 树)。

    为什么说 B+tree比 B 树更适合实际应用中操作系统的文件索引和数据索引。

      B+tree 的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对 B 树更小, 如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对 IO 读写次数就降低了。
      由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
      也有人认为数据库索引采用 B+树的主要原因是:B 树在提高了 IO 性能的同时并没有解决元素遍历的效率低下的问题,正是为了解决这个问题,B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而 B树不支持这样的操作(或者说效率太低,需要中序遍历)。

    展开全文
  • 数据库面试题索引sql优化.pdf+数据库SQL优化总结之百万级数据库优化.pdf 附赠Oracle高性能sql优化
  • MySQL数据库面试题(2020最新版)

    万次阅读 多人点赞 2020-03-10 17:20:40
    文章目录数据库基础知识为什么要使用数据库什么是SQL?什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式?分别有什么区别?数据类型mysql有哪些数据类型引擎MySQL存储...
  • 这几道带你了解索引的几个重要知识点 1. 什么是最左前缀原则? 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话,采用B+树的结构,大概的索引结构如下 如果...
  • MYSQL常见索引面试题及答案

    千次阅读 2020-01-01 22:31:10
    索引的分类 1、唯一索引:确保数据唯一性 2、非唯一索引:这些字段可以重复,不要求唯一. 3、主键索引:是唯一索引的特定类型,创建主键时自动创建. 4、聚簇索引: 表中记录的物理顺与键值顺序相同,表数据和主键一起...
  • 数据库常见面试题(附答案)

    万次阅读 多人点赞 2019-03-13 00:54:20
    2.数据库隔离级别,每个级别会引发什么问题,mysql默认是哪个级别 脏读:事务B读取事务A还没有提交的数据 不可重复读:两次事务读的数据不一致 幻读:事务A修改了数据,事务B也修改了数据,这时在事务A看
  • 数据库索引高频面试题(最新版)

    千次阅读 2020-12-29 12:18:11
    MySQL的索引是面试中的高频题目,将常见的索引面试题目总结了一下,如果有帮到你可以点赞收藏呦。 文章目录索引什么是索引?索引的优缺点?索引的数据结构?Hash索引和B+树的区别?索引的类型有哪些?索引的种类有...
  • Oracle数据库面试题总结

    万次阅读 多人点赞 2017-04-27 22:41:44
    1)调整数据库结构的设计:需要考虑是否使用分区功能、是否建立索引等 2)调整数据库的SQL语句 3)调整服务器的内存分配 4)调整硬盘I/O 5)调整操作系统参数   12、关系型数据库的关系操作有...
  • 数据库常见面试题

    2018-11-04 21:52:40
    8、mysql有哪几种索引? 9、innodb的事务与日志的实现方式? 10、简述Mysql Innodb引擎和MyIASM引擎的区别? 11、innodb的读写参数优化? 12、备份计划,mysqldump以及xtranbackup的实现原理? 13、MySQL binlog的几...
  • 面试题:介绍mysql搜索引擎,主从配置描述,分库分表的介绍,mysql优化描述,数据备份方案,sql注入,事务,mysql线上部署用户的什么版本(线上需要空间付费,了解一下),熟悉的数据库高可用架构,索引,主键,唯一...
  • oracle数据库常见面试题

    热门讨论 2011-09-02 15:34:31
    这是我搜集的很多关于oracle面试的一些常见问题,对于学习oracle和找工作来说,都有所帮助
  • 数据库工程师面试题

    千次阅读 2019-08-12 15:34:17
    答: 对于本地索引,每一个表分区对应一个索引分区,当表的分区发生变化时,索引的维护由数据库自动进行。对于全局索引,可以选择是否分区,而且索引的分区可以不与表分区相对应。当对分区进行维护操作时,通常会...
  • mysql索引面试题

    万次阅读 2019-08-15 18:24:18
    1.可以使用多少列创建索引? 任何标准表最多可以创建16个索引列。 2.索引的底层实现原理和优化 B+树,经过优化的B+树 主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用...
  • Mysql的常见面试题 + 索引原理分析

    万次阅读 多人点赞 2019-03-07 21:31:00
    今天给大家分享一篇干货,面试必备之Mysql索引底层原理分析 Mysql索引的本质 Mysql索引的底层原理 Mysql索引的实战经验 面试 问:数据库中最常见的慢查询优化方式是什么? 同学A:加索引。 问:...
  • MySQL数据库常见面试题

    万次阅读 2019-04-18 19:21:48
    1. 怎么优化数据库的查询? 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 应尽量避免在 where 子句中使用!=或<>操作符。 应尽量避免在 where 子句中对字段进行...
  • sql优化和索引常见的面试题(面试总结)

    万次阅读 多人点赞 2019-08-23 11:44:16
    索引数据库的目录,比如:字典上面的字母目录 (适用于大数据量) 2.建立索引的优缺点优点:查询速度快 缺点:增删改慢,因为数据库要同步去维护索引文件,所以速度慢 3.索引有哪些 普通 主键 唯一 组合 4. 索引...
  • 27.什么是索引Index? A. SQL数据库里的表管理工具 B. SQL数据库里的查询工具 C. SQL数据库里的目录工具 D. SQL数据库用来加速数据查询的特殊的数据结构 ...数据库基础面试32 ...
  • 前言100道MySQL数据库经典面试题解析,已经上传github啦https://github.com/whx123/JavaHome/tree/master/Java%E9%9D%A2...
  • Java基础知识面试题(2020最新版)

    万次阅读 多人点赞 2020-02-19 12:11:27
    Java面试总结(2021优化...https://thinkwon.blog.csdn.net/article/details/101292950 11 Redis面试题(2020最新版) https://thinkwon.blog.csdn.net/article/details/103522351 12 MySQL数据库面试题(2020最新版) ...
  • Oracle数据库常见面试题

    千次阅读 2019-03-29 20:03:46
    数据库SQL优化技巧: 1.DDL优化:①通过禁用索引来导入数据,导入完成再恢复索引; ②关闭唯一校验 ③倒入时候修改事物提交方式(改成批量插入) 2.DQL优化:①Order By优化 多用索引排序 ②Group By优化 使用...
  • 索引 区别 聚集索引 数据按索引顺序存储,中子结点存储真实的物理数据 非聚集索引 存储指向真正数据行的指针
  • 1.InnoDB支持的索引算法是? A. B+树 B. B-树 C. B*树 D. B/树 正确答案 2.MySQL如何修改一个表a的名字为b A. change table a to b; B. rename table a to b; C. update table a to b; D. alter table a to...
  • 3、视图对重构数据库提供了一定程度的逻辑独立性 4、视图能够对机密数据提供安全保护 5、适当的利用视图可以更清晰的表达查询, 2.索引的必要性有哪些?怎么合理添加索引索引的优势? 1、表的主键、外键必须有索引...
  • 选择(20分,每4分) 1. 利用游标来修改数据时,所用的..FOR UPDATE充分利用了事务的哪个特性?() A、原子性 B、一致性 C、永久性 D、隔离性下列说法,正确的说法是() A、只要在存储过程中有增删改语句...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 59,966
精华内容 23,986
关键字:

数据库索引面试题