精华内容
下载资源
问答
  • 数据库面试题索引sql优化.pdf+数据库SQL优化总结之百万级数据库优化.pdf 附赠Oracle高性能sql优化
  • Mysql索引优化面试题

    万次阅读 2019-07-19 08:45:28
    Mysql索引优化 一:索引介绍 索引是关系型数据库中给数据库表中一列或者多列的值排序后的储存结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引. MySql索引类型有:唯一索引,主键(聚集)...

    Mysql索引优化

    一:索引介绍

    索引是关系型数据库中给数据库表中一列或者多列的值排序后的储存结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引.

    MySql索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引.

    1.1:聚集索引

    聚集(clustered)索引,也叫做聚簇索引.

    定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引.

    注意:聚集索引做查询可以直接获取对应的全部列的数据.所以聚集查询较快.

    1.2非聚集索引

    定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引.

    除了聚集索引以外的索引都是非聚集索引,分成普通索引,唯一索引和全文索引.

    注意:非聚集索引查询在索引没覆盖到对应列的时候需要进行二次查询,索引非聚集查询较慢.

    1.2.1如何解决非聚集索引的二次查询问题
    复合索引(覆盖索引)

    建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1,col2),执行下面的语句

    select col1,col2 from 表名 where col1=‘xxx’;

    要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用.

    二:索引的存储机制

    无索引的表,查询时,是按照顺序存序的方法扫描每个记录来查询符合条件的记录,这样效率很低.

    聚集索引和非聚集索引的根本区别在于表记录的排列顺序和索引的排列顺序是否一致.

    聚集索引就是在数据库被开辟一个物理空间放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个物理空间,而非聚集索引其实可以看做是一个含有聚集索引的表,它只仅包含原表中非聚集索引的列和指向实际物理表的指针,它只记录一个指针,其实就有点和堆栈差不多的感觉.

    三:建立索引的原则

    1. 定义主键的数据列一定要建立索引。

    2. 定义有外键的数据列一定要建立索引。

    3. 对于经常查询的数据列最好建立索引。

    4. 对于需要在指定范围内的快速或频繁查询的数据列;

    5. 经常用在WHERE子句中的数据列。

    6. 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7. 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8. 对于定义为text、image和bit的数据类型的列不要建立索引。

    9. 对于经常存取的列避免建立索引

    10. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    11. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

    四:索引优化面试题

    select * from student s where s.stuName in(“张三”,“李四”,“王五”)and s.age>18 and s.sex=‘男’;

    思路:

    1.肯定要建立二级联合索引:index(stuName,age,sex)

    2.这里要优化一下sql语句中的in,改用union all

    优化后的sql:

    select * from student s where s.stuName=“张三” and s.age>18 and s.sex=‘男’ union all select * from student s where s.stuName=“李四” and s.age>18 and s.sex=‘男’ union all select * from student s where s.stuName=“王五” and s.age>18 and s.sex=‘男’ ;

    展开全文
  • mysql索引优化面试题

    2021-01-28 06:16:44
    曾经偷偷的面试了两个单位,都提到了Mysql的优化问题,所以以后要多多学习数据库优化知识了。建设数据库优化大概主要就是索引优化了吧,因为我们不可能修改数据结构的情况下,提高数据库的查询效率似乎也只能...

    曾经偷偷的面试了两个单位,都提到了Mysql的优化问题,所以以后要多多学习数据库的优化知识了。建设数据库的优化大概主要就是索引的优化了吧,因为我们不可能修改数据结构的情况下,提高数据库的查询效率似乎也只能用索引了。当然这也是建立在你sql语句写的比较科学的情况下,如果你的sql语句本身就写的比较垃圾,神仙也救不了你!

    下边是在网上找到的一些资料,保留下来备用吧

    1,创建索引

    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    2,复合索引

    比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,

    age,

    salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    3,索引不会包含有NULL值的列

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

    4,使用短索引

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

    5,排序的索引问题

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

    6,like语句操作

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

    7,不要在列上进行运算

    select * from users where YEAR(adddate)<2007;

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate

    8,不使用NOT IN和操作

    NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id<3来代替。

    展开全文
  • 数据库索引相关面试题

    万次阅读 多人点赞 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树为了保持树的性质需要进行旋转一样!

     

    展开全文
  • 数据库面试题索引sql优化

    热门讨论 2010-05-28 21:59:50
    数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引...
  • 数据库索引高频面试题(最新版)

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

    MySQL的索引是面试中的高频题目,将常见的索引面试题目总结了一下,如果有帮到你可以点赞收藏呦。

    微信搜索公众号路人zhang,回复面试手册,领取更多高频面试题PDF版及更多面试资料。

    推荐阅读:

    索引

    什么是索引?

    百度百科的解释:索引是对数据库表的一列或者多列的值进行排序一种结构,使用索引可以快速访问数据表中的特定信息。

    索引的优缺点?

    优点:

    • 大大加快数据检索的速度。
    • 将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)
    • 加速表与表之间的连接

    缺点:

    • 从空间角度考虑,建立索引需要占用物理空间
    • 从时间角度 考虑,创建和维护索引都需要花费时间,例如对数据进行增删改的时候都需要维护索引。

    索引的数据结构?

    索引的数据结构主要有B+树和哈希表,对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引,默认的索引类型为B+树索引。

    • B+树索引

      熟悉数据结构的同学都知道,B+树、平衡二叉树、红黑树都是经典的数据结构。在B+树中,所有的记录节点都是按照键值大小的顺序放在叶子节点上,如下图。

    在这里插入图片描述

    从上图可以看出 ,因为B+树具有有序性,并且所有的数据都存放在叶子节点,所以查找的效率非常高,并且支持排序和范围查找。

    B+树的索引又可以分为主索引和辅助索引。其中主索引为聚簇索引,辅助索引为非聚簇索引。聚簇索引是以主键作为B+ 树索引的键值所构成的B+树索引,聚簇索引的叶子节点存储着完整的数据记录;非聚簇索引是以非主键的列作为B+树索引的键值所构成的B+树索引,非聚簇索引的叶子节点存储着主键值。所以使用非聚簇索引进行查询时,会先找到主键值,然后到根据聚簇索引找到主键对应的数据域。上图中叶子节点存储的是数据记录,为聚簇索引的结构图,非聚簇索引的结构图如下:

    在这里插入图片描述

    上图中的字母为数据的非主键的列值,假设要查询该列值为B的信息,则需先找到主键7,在到聚簇索引中查询主键7所对应的数据域。

    • 哈希索引

      哈希索引是基于哈希表实现的,对于每一行数据,存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码,并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的,将哈希码的值作为哈希表的key值,将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1),一般多用于精确查找。

    Hash索引和B+树的区别?

    因为两者数据结构上的差异导致它们的使用场景也不同,哈希索引一般多用于精确的等值查找,B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下,会选择使用B+树索引。

    • 哈希索引不支持排序,因为哈希表是无序的。
    • 哈希索引不支持范围查找。
    • 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
    • 因为哈希表中会存在哈希冲突,所以哈希索引的性能是不稳定的,而B+树索引的性能是相对稳定的,每次查询都是从根节点到叶子节点

    索引的类型有哪些?

    MySQL主要的索引类型主要有FULLTEXT,HASH,BTREE,RTREE。

    • FULLTEXT

      FULLTEXT即全文索引,MyISAM存储引擎和InnoDB存储引擎在MySQL5.6.4以上版本支持全文索引,一般用于查找文本中的关键字,而不是直接比较是否相等,多在CHAR,VARCHAR,TAXT等数据类型上创建全文索引。全文索引主要是用来解决WHERE name LIKE "%zhang%"等针对文本的模糊查询效率低的问题。

    • HASH

      HASH即哈希索引,哈希索引多用于等值查询,时间复杂夫为o(1),效率非常高,但不支持排序、范围查询及模糊查询等。

    • BTREE

      BTREE即B+树索引,INnoDB存储引擎默认的索引,支持排序、分组、范围查询、模糊查询等,并且性能稳定。

    • RTREE

      RTREE即空间数据索引,多用于地理数据的存储,相比于其他索引,空间数据索引的优势在于范围查找

    索引的种类有哪些?

    • 主键索引:数据列不允许重复,不能为NULL,一个表只能有一个主键索引
    • 组合索引:由多个列值组成的索引。
    • 唯一索引:数据列不允许重复,可以为NULL,索引列的值必须唯一的,如果是组合索引,则列值的组合必须唯一。
    • 全文索引:对文本的内容进行搜索。
    • 普通索引:基本的索引类型,可以为NULL

    B树和B+树的区别?

    B树和B+树最主要的区别主要有两点:

    • B树中的内部节点和叶子节点均存放键和值,而B+树的内部节点只有键没有值,叶子节点存放所有的键和值。

    • B+树的叶子节点是通过相连在一起的,方便顺序检索。

      两者的结构图如下。

      在这里插入图片描述

      在这里插入图片描述

    数据库为什么使用B+树而不是B树?

    • B树适用于随机检索,而B+树适用于随机检索和顺序检索
    • B+树的空间利用率更高,因为B树每个节点要存储键和值,而B+树的内部节点只存储键,这样B+树的一个节点就可以存储更多的索引,从而使树的高度变低,减少了I/O次数,使得数据检索速度更快。
    • B+树的叶子节点都是连接在一起的,所以范围查找,顺序查找更加方便
    • B+树的性能更加稳定,因为在B+树中,每次查询都是从根节点到叶子节点,而在B树中,要查询的值可能不在叶子节点,在内部节点就已经找到。

    那在什么情况适合使用B树呢,因为B树的内部节点也可以存储值,所以可以把一些频繁访问的值放在距离根节点比较近的地方,这样就可以提高查询效率。综上所述,B+树的性能更加适合作为数据库的索引。

    什么是聚簇索引,什么是非聚簇索引?

    聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储

    • 聚簇索引:将数据和索引放到一起存储,索引结构的叶子节点保留了数据行。
    • 非聚簇索引:将数据进和索引分开存储,索引叶子节点存储的是指向数据行的地址。

    在InnoDB存储引擎中,默认的索引为B+树索引,利用主键创建的索引为主索引,也是聚簇索引,在主索引之上创建的索引为辅助索引,也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢,因为辅助索引中的叶子节点存储的是主键。

    在MyISAM存储引擎中,默认的索引也是B+树索引,但主索引和辅助索引都是非聚簇索引,也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

    可以从非常经典的两张图看看它们的区别(图片来源于网络):

    在这里插入图片描述

    在这里插入图片描述

    非聚簇索引一定会进行回表查询吗?

    上面是说了非聚簇索引的叶子节点存储的是主键,也就是说要先通过非聚簇索引找到主键,再通过聚簇索引找到主键所对应的数据,后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询,那么非聚簇索引就一定会进行回表查询吗?

    答案是不一定的,这里涉及到一个索引覆盖的问题,如果查询的数据再辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name、age等字段。假设聚簇索引是以ID为键值构建的索引,非聚簇索引是以name为键值构建的索引,select id,name from user where name = 'zhangsan';这个查询便不需要进行回表查询因为,通过非聚簇索引已经能全部检索出数据,这就是索引覆盖的情况。如果查询语句是这样,select id,name,age from user where name = 'zhangsan';则需要进行回表查询,因为通过非聚簇索引不能检索出age的值。那应该如何解决那呢?只需要将索引覆盖即可,建立age和name的联合索引再使用select id,name,age from user where name = 'zhangsan';进行查询即可。

    所以通过索引覆盖能解决非聚簇索引回表查询的问题。

    索引的使用场景有哪些?

    • 对于中大型表建立索引非常有效,对于非常小的表,一般全部表扫描速度更快些。
    • 对于超大型的表,建立和维护索引的代价也会变高,这时可以考虑分区技术。
    • 如何表的增删改非常多,而查询需求非常少的话,那就没有必要建立索引了,因为维护索引也是需要代价的。
    • 一般不会出现再where条件中的字段就没有必要建立索引了。
    • 多个字段经常被查询的话可以考虑联合索引。
    • 字段多且字段值没有重复的时候考虑唯一索引。
    • 字段多且有重复的时候考虑普通索引。

    索引的设计原则?

    • 最适合索引的列是在where后面出现的列或者连接句子中指定的列,而不是出现在SELECT关键字后面的选择列表中的列。
    • 索引列的基数越大,索引的效果越好,换句话说就是索引列的区分度越高,索引的效果越好。比如使用性别这种区分度很低的列作为索引,效果就会很差,因为列的基数最多也就是三种,大多不是男性就是女性。
    • 尽量使用短索引,对于较长的字符串进行索引时应该指定一个较短的前缀长度,因为较小的索引涉及到的磁盘I/O较少,并且索引高速缓存中的块可以容纳更多的键值,会使得查询速度更快。
    • 尽量利用最左前缀。
    • 不要过度索引,每个索引都需要额外的物理空间,维护也需要花费时间,所以索引不是越多越好。

    如何对索引进行优化?

    对索引的优化其实最关键的就是要符合索引的设计原则和应用场景,将不符合要求的索引优化成符合索引设计原则和应用场景的索引。

    除了索引的设计原则和应用场景那几点外,还可以从以下两方面考虑。

    • 在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数,因为这样无法使用索引。例如select * from table_name where a + 1 = 2
    • 将区分度最高的索引放在前面
    • 尽量少使用select*

    索引的使用场景、索引的设计原则和如何对索引进行优化可以看成一个问题。

    如何创建/删除索引?

    创建索引:

    • 使用CREATE INDEX 语句

      CREATE INDEX index_name ON table_name (column_list);

    • 在CREATE TABLE时创建

      	CREATE TABLE user(
      	id INT PRIMARY KEY,
      	information text,
      	FULLTEXT KEY (information)
      );
      
    • 使用ALTER TABLE创建索引

      ALTER TABLE table_name ADD INDEX index_name (column_list);

    删除索引:

    • 删除主键索引

      alter table 表名 drop primary key

    • 删除其他索引

      alter table 表名 drop key 索引名

    使用索引查询时性能一定会提升吗?

    不一定,前面在索引的使用场景和索引的设计原则中已经提到了如何合理地使用索引,因为创建和维护索引需要花费空间和时间上的代价,如果不合理地使用索引反而会使查询性能下降。

    什么是前缀索引?

    前缀索引是指对文本或者字符串的前几个字符建立索引,这样索引的长度更短,查询速度更快。

    使用场景:前缀的区分度比较高的情况下。

    建立前缀索引的方式

    ALTER TABLE table_name ADD KEY(column_name(prefix_length));

    这里面有个prefix_length参数很难确定,这个参数就是前缀长度的意思。通常可以使用以下方法进行确定,先计算全列的区分度

    SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

    然后在计算前缀长度为多少时和全列的区分度最相似。

    SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

    不断地调整prefix_length的值,直到和全列计算出区分度相近。

    什么是最左匹配原则?

    最左匹配原则:从最左边为起点开始连续匹配,遇到范围查询(<、>、between、like)会停止匹配。

    例如建立索引(a,b,c),大家可以猜测以下几种情况是否用到了索引。

    • 第一种

      select * from table_name where a = 1 and b = 2 and c = 3 
      select * from table_name where b = 2 and a = 1 and c = 3
      

      上面两次查询过程中所有值都用到了索引,where后面字段调换不会影响查询结果,因为MySQL中的优化器会自动优化查询顺序。

    • 第二种

      select * from table_name where a = 1
      select * from table_name where a = 1 and b = 2  
      select * from table_name where a = 1 and b = 2 and c = 3
      

      答案是三个查询语句都用到了索引,因为三个语句都是从最左开始匹配的。

    • 第三种

      select * from table_name where  b = 1 
      select * from table_name where  b = 1 and c = 2 
      

      答案是这两个查询语句都没有用到索引,因为不是从最左边开始匹配的

    • 第四种

      select * from table_name where a = 1 and c = 2 
      

      这个查询语句只有a列用到了索引,c列没有用到索引,因为中间跳过了b列,不是从最左开始连续匹配的。

    • 第五种

      select * from table_name where  a = 1 and b < 3 and c < 1
      

      这个查询中只有a列和b列使用到了索引,而c列没有使用索引,因为根据最左匹配查询原则,遇到范围查询会停止。

    • 第六种

      select * from table_name where a like 'ab%'; 
      select * from table_name where  a like '%ab'
      select * from table_name where  a like '%ab%'
      

      对于列为字符串的情况,只有前缀匹配可以使用索引,中缀匹配和后缀匹配只能进行全表扫描。

    索引在什么情况下会失效?

    在上面介绍了几种不符合最左匹配原则的情况会导致索引失效,除此之外,以下这几种情况也会导致索引失效。

    • 条件中有or,例如select * from table_name where a = 1 or b = 3
    • 在索引上进行计算会导致索引失效,例如select * from table_name where a + 1 = 2
    • 在索引的类型上进行数据类型的隐形转换,会导致索引失效,例如字符串一定要加引号,假设 select * from table_name where a = '1'会使用到索引,如果写成select * from table_name where a = 1则会导致索引失效。
    • 在索引中使用函数会导致索引失效,例如select * from table_name where abs(a) = 1
    • 在使用like查询时以%开头会导致索引失效
    • 索引上使用!、=、<>进行判断时会导致索引失效,例如select * from table_name where a != 1
    • 索引字段上使用 is null/is not null判断时会导致索引失效,例如select * from table_name where a is null
    展开全文
  • 1.1、B+Tree结构了解一下1.2、索引到底有哪些1.3、索引是把双刃剑2、索引优化2.1、其中最重要的就是这个explain关键字2.2、使用索引的注意事项2.3、导致索引失效的原因哪些(索引优化方案)? 1、什么是索引? 索引...
  • 数据库常见面试题——索引

    千次阅读 2019-07-31 16:38:02
    如何设计一个关系型数据库 索引模块 为什么要使用索引 快速查询数据 什么样的信息能够成为索引 主键、唯一键以及普通键等 索引的数据结构 生成索引,建立二叉查找树进行二分查找树(平衡二叉树、红黑树) ...
  • 那关于面试数据库优化面试题有哪些呢?下面是小编为你整理的数据库优化常见的一些面试题 ,希望对你有帮助。数据库优化面试题1. sql2000和sql2005有什么区别?a、 最直观的一点就是两者的界面有很明显的不同。sql...
  • 文章目录数据库基础知识为什么要使用数据库什么是SQL?什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式?分别有什么区别?数据类型mysql有哪些数据类型引擎MySQL存储...
  • 数据库优化相关面试题

    万次阅读 2019-01-05 10:51:07
    数据库优化,包括合理的事务隔离级别、SQL语句优化索引优化 使用缓存,尽量减少数据库 IO 分布式数据库、分布式缓存 服务器的负载均衡   2、实践中如何优化MySQL 四条从效果上第一条影响最大,后面...
  • mysql 索引优化面试题 问题 mysql 索引优化面试题 回答 曾经偷偷的面试了两个单位都提到了 Mysql 的优化问题所以以后要多多学习数据库的优化知识了 建设数据库的优化大概主要就是索引的优化了吧 因为我们不可能修改...
  • 面试求职:数据库常见面试题数据库优化思路)

    万次阅读 多人点赞 2018-08-06 22:52:04
    数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。 超 键: 在关系中能唯一标识元组的属性集称为关系模式的超键。一个...
  • 面试题数据库优化

    千次阅读 2019-04-25 17:18:58
    对于数据库优化可以从五个方面表述: 根据服务层面 配置mysql性能优化参数; 从系统层面增强mysql的性能:优化数据表结构 ① 将字段较多的表分解成多个表 对于字段较多的表,如果有些字段的使用...
  • sql优化索引常见的面试题(面试总结)

    万次阅读 多人点赞 2019-08-23 11:44:16
    索引数据库的目录,比如:字典上面的字母目录 (适用于大数据量) 2.建立索引的优缺点优点:查询速度快 缺点:增删改慢,因为数据库要同步去维护索引文件,所以速度慢 3.索引有哪些 普通 主键 唯一 组合 4. 索引...
  • 面试题:介绍mysql搜索引擎,主从配置描述,分库分表的介绍,mysql优化描述,数据备份方案,sql注入,事务,mysql线上部署用户的什么版本(线上需要空间付费,了解一下),熟悉的数据库高可用架构,索引,主键,唯一...
  • 数据库常见面试题(附答案)

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

    万次阅读 2021-03-04 17:51:08
    应 主键列 应 应 频繁修改索引列 不应 应 性能优化 数据库如何优化性能? SQL优化 加缓存 分库分表 读写分离 索引如何优化? 表的主键、外键必须有索引; 经常与其他表进行连接的表,在连接字段上应该建立索引; ...
  • MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。 例如,在定义邮政...
  • 数据库常见面试题

    2018-11-04 21:52:40
    1、数据库有哪几种范式? 2、Mysql提供了哪几种事务隔离级别? 3、MySQL中varchar与...16、数据库优化的思路? 17、视图的作用,视图可以更改么? 18、存储过程与触发器的区别? 19、如何在最快时间之内重启500台db?
  • Oracle数据库相关经典面试题

    千次阅读 2019-11-28 08:15:00
    金九银十的面试季节,最近我会多发一些面试题相关的文章,因为墨白也要开始找工作了大家一起加油哈 ! ! !oracle下有自动增长类型的字段吗?若无,如何实现自增长的功能 ...
  • mysql索引面试题

    万次阅读 2019-08-15 18:24:18
    2.索引的底层实现原理和优化 B+树,经过优化的B+树 主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用默认自增的主键作为主索引。 3.什么情况下设置了索引但无法使用 1.以...
  • 数据库工程师面试题

    千次阅读 2019-08-12 15:34:17
    问题 1: 为什么 group by 和 order by 会使查询变慢? 答: group by 和 order by 操作通常需要创建一个临时表...除了上述的这些问题,一定会问道数据库优化问题,包括一些分页,索引,查找等等的问题。。。  
  • 关于mysql联合索引面试题

    千次阅读 2020-08-11 21:05:05
    今天是我失业的第41天,这段时间面试了很多公司,却没有几个offer,因为没有学历没有入职成功,今天和大家分享一下今天的面试题,关于mysql联合索引的问题。 先跟大家描述一下面试官的问题,表中a,b两个字段创建...
  • MySQL数据库优化高频面试题(最新版)

    千次阅读 2021-01-21 20:21:50
    数据库索引高频面试题最新版 文章目录SQL语句基础知识及优化SQL语句主要分为哪几类 *SQL约束有哪些? **什么是子查询? **了解MySQL的几种连接查询吗? ***mysql中in和exists的区别? **varchar和...
  • MySQL索引面试题汇总

    千次阅读 2021-03-07 14:25:01
    目录 1.前言 2.索引数据结构分类 3.二叉查找树 4.红黑树(自平衡二叉查找树) ... 6.3 MyISAM存储引擎索引实现 ...7.索引有关面试题解析 7.1 什么是索引 7.2 索引的分类 7.3 索引的优势 7.4 索引的劣势...
  • SQL优化面试题

    万次阅读 多人点赞 2019-06-13 22:21:12
    最近在面试的时候经常被问到SQL优化,总结如下: 1、在表中建立索引,优先考虑where、group by使用到的字段。 2、尽量避免使用select *,返回无用的字段会降低查询效率。如下: SELECT * FROM t 优化方式:...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,126
精华内容 16,850
关键字:

数据库索引优化面试题