文章目录前言1、索引的底层实现原理和优化1.1 B树。1.2 B树3、什么情况下设置了索引但无法使用4、简单描述mysql中索引主键唯一索引联合索引的区别对数据库的性能有什么影响从读写两方面5、 对于关系型数据库而言索引是相当重要的概念请回答有关索引的几个问题6、你怎么看到为表格定义的所有索引?**7、实现索引的方式? 索引的原理? 索引的代价? 索引的类型?**8、聚簇索引和普通索引在不同的SQL语句中哪个效率更高?聚簇索引和普通索引的区别**9、怎样创建一个一个索引,索引使用的原则,有什么优点和缺点**10、索引的实现方式11、Oracle索引分为哪几类说出唯一索引和位图索引的概念。**12、如何写 sql 能够有效的使用到复合索引。****13、当数据表中A、B字段做了组合索引那么单独使用A或单独使用B会有索引效果吗使用like查询如何有索引效果**14、数据库索引的实现(B树介绍、和B树、R树区别)15、索引的使用注意事项16、MYsql 的索引原理索引的类型有哪些如何创建合理的索引索引如何优化。**17、聚集索引和非聚集索引的区别。**18、数据库中 BTREE 和 Btree 区别。参考博客1参考博客2MySQL 索引类型有唯一索引聚集主键索引非聚集索引全文索引前言假设有联合索引scontent、score唯一主键索引则下面的查询都使用了联合索引EXPLAINselect*FROMuser where contentscore2and score90EXPLAINselect*FROMuser where scorecontent2and score1结果下面的查询没有使用联合索引EXPLAINselect*FROMuser where contentscore2结果解释1、id 是每个select查询的序列号一个sql中有几个查询就会有几个序列号id越大查询越先执行相同的id是从上往下执行id为nul则最后执行。2、select_type查询的类型1、SIMPLE:简单的查询不使用union或子查询2、PRIMARY:子查询中最外层的查询3、UNION:union中第二个或后面的查询语句4、DERIVEDfrom子句的子查询mysql会把子查询存放到一个临时表中叫做派生表5UNION RESULT查询所有union的合并结果6SUBQUERY子查询中的第一个查询结果不依赖于外部查询7DEPENDENT SUBQUERY子查询中的第一个查询依赖于外部查询。3、Table 表示执行的sql访问的表名称但有union时是union2,3,4有子查询时是4、Type 常用的类型有ALL、index、range、ref、eq_ref、const、system、NULL从左到右性能从差到好 ALL扫描全表进行查询访问的是聚集索引的所有叶子节点 Index扫描全索引拿到结果一般是扫描二级索引的的全部叶子节点这种一般是覆盖索引 Range:范围扫描通常出现在in、between、、之中使用一个索引来检索给定的行 Ref不使用唯一索引而是使用部分索引字段或不是唯一索引所的有字段或者唯一索引的前缀可以查询到多行记录。 eq_ref:使用唯一索引进行连接查询每个条件最多返回一行记录 Const、systemmysql对查询进行优化把查询结果转换为一个常量如将主键作为唯一一个查询条件mysql会把这个查询转变为常量。system是const类型的特列但表中只有一行记录的情况下就是system类型。NULLmysql在优化过程中分解语句在执行时甚至不用访问表和索引例如此哦那个一个索引列里选取最小值可以通过单独索引查找完成。5、possible_keys 可能被使用的索引但是不一定被用到因为mysql根据自己的优化器可能不使用该索引。如果为null,则表示没有相关的索引。6、Key 显示mysql实际采用的索引如果没有使用索引则该值是null如果想强制使用或忽略possible_keys中的索引可以在查询中使用force index、use index或者ignore index.7、key_len 表示索引中使用的字节数key_len计算规则如下 字符串char(n)和varchar(n)5.0.3以后版本中n均代表字符数而不是字节数如果是utf-8一个数字 或字母占1个字节一个汉字占3个字节 char(n)如果存汉字长度就是3n 字节 varchar(n)如果存汉字则长度是3n2字节加的2字节用来存储字符串长度因为 varchar是变长字符串 数值类型 tinyint1字节 smallint2字节 int4字节 bigint8字节 时间类型 date3字节timestamp4字节 datetime8字节 如果字段允许为NULL需要1字节记录是否为NULL索引最大长度是768字节当字符串过长时mysql会做一个类似左前缀索引的处理将前半部分的字符提取出来做索引。8、ref:列于索引的比较表示表的连接匹配条件即哪些列或者常量被用于查找索引列上的值常见的有const、字段名9、Rows 估算找到所需的记录所要读取的行数。10、Extra Using where:使用where语句来处理结果并且查询的列未被索引覆盖 Using index使用覆盖索引也就是要查询的字段都可以从被查询索引上获取一般是针对二级索引。 Using index condition:查询的列没有被使用的索引完全覆盖并且使用where条件中使用的是索引的范围查询。 Using temporarymysql创建一个临时表来处理查询比如distinct、group by、order by这种情况要进行优化一般用索引进行进行优化把把操作的字段建立索引。 Using filesort使用的是外部排序而不是索引排序数据较小时在内存排序否则在磁盘文件排序这种情况下应该给排序的字段建立索引来优化比如order by 排序的字段建索引。 Using join buffer连表查询时在获取连接条件时没有使用索引并且需要连接缓冲区来存储中间结果这种情况应该添加索引来优化。 Select tables optimized away:使用聚合函数min 、max来访问是索引的字段但是前提是没有where条件因为有查询条件聚合函数就不能直接找到其值了。1、索引的底层实现原理和优化推荐博客一文弄懂索引数据结构 BTreeInnoDB建议为大部分表使用默认自增的主键作为主索引。1.1 B树。图例说明键值key为记录的键值可理解为表中的主键值数据data为一行记录中除主键外的数据。B树特点每个节点占用一个盘块的磁盘空间一个节点上有两个升序排序的主键key、除主键外的数据data、三个指向子树根节点的指针p1 p2 p3。指针存储的是子节点所在磁盘块的地址。以根节点为例关键字为17和35P1指针指向的子树的数据范围为小于17P2指针指向的子树的数据范围为17~35P3指针指向的子树的数据范围为大于35。缺点每一个页的存储空间是有限的如果data数据较大时将会导致每个节点即一个页能存储的key的数量很小、当存储的数据量很大时同样会导致B-Tree的深度较大增大查询时的磁盘I/O次数影响查询效率。1.2 B树图例说明假设每个磁盘块能存储4个键值及指针信息。键值key为记录的键值可理解为表中的主键值数据data为一行记录中除主键外的数据。B树是B数的优化版特点如下1在同一层的叶子节点上存放键值和数据按照键值大小顺序存放所有数据记录节点2在所有的叶子结点中增加了指向下一个叶子节点的双向指针。 意味着所有的值都是按顺序存储的并且每一个叶子页到根的距离相同很适合查找范围数据。说明支持范围查询和天然排序。2非叶子节点上只存储key值信息、磁盘指针。可以看成是索引部分结点中仅含有其子树根结点中最大或最小关键字。B树一般有多大深度InnoDB存储引擎中页的大小为16KB一般表的主键类型为INT占用4个字节或BIGINT占用8个字节指针类型也一般为4或8个字节也就是说一个页BTree中的一个节点中大概存储16KB/(8B8B)1K个键值因为是估值为方便计算这里的K取值为103。也就是说一个深度为3的BTree索引可以维护103 * 103 * 103 10亿条记录。实际情况中每个节点可能不能填充满因此在数据库中BTree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。3、什么情况下设置了索引但无法使用以“%”开头的LIKE语句模糊匹配OR语句前后没有同时使用索引数据类型出现隐式转化如varchar不加单引号的话可能会自动转换为int型没有查询条件或者查询条件没有建立索引在查询条件上没有使用引导列查询的数量是大表的大部分应该是30以上。索引本身失效查询条件使用函数在索引列上或者对索引列进行运算运算包括(-*/! 等) 错误的例子select * from test where id-19; 正确的例子select * from test where id10;对小表查询提示不使用索引统计数据不真实CBO计算走索引花费过大的情况。其实也包含了上面的情况这里指的是表占有的block要比索引小。隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子select * from test where tu_mdn13333333333; 正确的例子select * from test where tu_mdn‘13333333333’;1, 2,单独的,,(有时会用到有时不会)like “%_” 百分号在前.表没分析.单独引用复合索引里非第一位置的索引列.字符型字段为数字时在where条件里不添加引号.对索引列进行运算.需要建立函数索引.not in ,not exist.当变量采用的是times变量而表的字段采用的是date变量时.或相反情况。B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走联合索引 is not null 只要在建立的索引列不分先后都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null但必须在所有列 都满足is null的时候,或者一个值 当建立索引的第一位置是一个值时,其他索引列可以是任何情况包括is null 一个值,以上两种情况索引都会走。其他情况不会走。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语句中哪个效率更高?聚簇索引和普通索引的区别概念类聚合函数? 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的表不要建立太多的索引不要将那些频繁修改的列作为索引列索引的优点创建唯一性索引保证数据库表中每一行数据的唯一性大大加快数据的检索速度这也是创建索引的最主要的原因加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。索引的缺点索引创建在表上不能创建在视图上创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大当对表中的数据进行增加、删除和修改的时候索引也要动态的维护降低了数据的维护速度10、索引的实现方式都是 B树索引 Innodb 是索引组织表 myisam 是堆表 索引组织表和堆表的区别要熟悉11、Oracle索引分为哪几类说出唯一索引和位图索引的概念。Oracle索引有B树索引位图索引函数索引簇索引等。唯一索引也是B树索引的一种它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。位图索引并不是采用象唯一索引那样存储索引字段值记录ROWID来创建索引段的而是为每一个唯一的字段值创建一个位图位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。12、如何写 sql 能够有效的使用到复合索引。由于复合索引的组合索引类似多个木板拼接在一起如果中间断了就无法用了所以要能用到复合索引首先开头(第一列)要用上比如index(a,b) 这种我们可以select table tname where aXX 用到第一列索引 如果想用第二列 可以 and bXX 或者and b like‘TTT%’要注意使用复合索引需要满足最左侧索引的原则也就是查询的时候如果where条件里面没有最左边的一到多列索引就不会起作用。13、当数据表中A、B字段做了组合索引那么单独使用A或单独使用B会有索引效果吗使用like查询如何有索引效果看A、B两字段做组合索引的时候谁在前面谁在后面如果A在前那么单独使用A会有索引效果单独使用B则没有反之亦然。同理使用like模糊查询时如果只是使用前面%那么有索引效果如果使用双%号匹配那么则无索引效果14、数据库索引的实现(B树介绍、和B树、R树区别)http://blog.csdn.net/kennyrose/article/details/7532032http://www.xuebuyuan.com/2216918.html15、索引的使用注意事项https://www.cnblogs.com/zlingh/p/3883716.html16、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.htmlhttp://www.cnblogs.com/cq-home/p/3482101.html17、聚集索引和非聚集索引的区别。聚集索引就是索引和记录紧密在一起。MySQL里主键就是聚集索引。 定义数据行的物理顺序与列值一般是主键的那一列的逻辑顺序相同一个表中只能拥有一个聚集索引。单单从定义来看是不是显得有点抽象打个比方一个表就像是我们以前用的新华字典聚集索引就像是拼音目录而每个字存放的页码就是我们的数据物理地址我们如果要查询一个“哇”字我们只需要查询“哇”字对应在新华字典拼音目录对应的页码就可以查询到对应的“哇”字所在的位置而拼音目录对应的A-Z的字顺序和新华字典实际存储的字的顺序A-Z也是一样的如果我们中文新出了一个字拼音开头第一个是B那么他插入的时候也要按照拼音目录顺序插入到A字的后面。数据行的物理顺序与列值的顺序相同如果我们查询id比较靠后的数据那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同所以也就只能建立一个聚集索引了。非聚簇索引 索引文件和数据文件分开存放索引文件的叶子页只保存了主键值要定位记录还要去查找相应的数据块。执行select * from user where id100;直接走到聚簇索引叶子直接拿到 id,name,age 全部字段无回表。18、数据库中 BTREE 和 Btree 区别。B是btree的变种本质都是btreebtree与B-Tree相比BTree有以下不同点每个节点的指针上限为2d而不是2d1。内节点不存储data只存储key叶子节点不存储指针。http://lcbk.net/9602.htmlBtree 怎么分裂的什么时候分裂为什么是平衡的。Key 超过1024才分裂B树为甚会分裂 因为随着数据的增多一个结点的key满了为了保持B树的特性就会产生分裂就向红黑树和AVL树为了保持树的性质需要进行旋转一样 索引（B+tree、联合唯一索引、）
文章目录前言1、索引的底层实现原理和优化1.1 B树。1.2 B树3、什么情况下设置了索引但无法使用4、简单描述mysql中索引主键唯一索引联合索引的区别对数据库的性能有什么影响从读写两方面5、 对于关系型数据库而言索引是相当重要的概念请回答有关索引的几个问题6、你怎么看到为表格定义的所有索引?**7、实现索引的方式? 索引的原理? 索引的代价? 索引的类型?**8、聚簇索引和普通索引在不同的SQL语句中哪个效率更高?聚簇索引和普通索引的区别**9、怎样创建一个一个索引,索引使用的原则,有什么优点和缺点**10、索引的实现方式11、Oracle索引分为哪几类说出唯一索引和位图索引的概念。**12、如何写 sql 能够有效的使用到复合索引。****13、当数据表中A、B字段做了组合索引那么单独使用A或单独使用B会有索引效果吗使用like查询如何有索引效果**14、数据库索引的实现(B树介绍、和B树、R树区别)15、索引的使用注意事项16、MYsql 的索引原理索引的类型有哪些如何创建合理的索引索引如何优化。**17、聚集索引和非聚集索引的区别。**18、数据库中 BTREE 和 Btree 区别。参考博客1参考博客2MySQL 索引类型有唯一索引聚集主键索引非聚集索引全文索引前言假设有联合索引scontent、score唯一主键索引则下面的查询都使用了联合索引EXPLAINselect*FROMuser where contentscore2and score90EXPLAINselect*FROMuser where scorecontent2and score1结果下面的查询没有使用联合索引EXPLAINselect*FROMuser where contentscore2结果解释1、id 是每个select查询的序列号一个sql中有几个查询就会有几个序列号id越大查询越先执行相同的id是从上往下执行id为nul则最后执行。2、select_type查询的类型1、SIMPLE:简单的查询不使用union或子查询2、PRIMARY:子查询中最外层的查询3、UNION:union中第二个或后面的查询语句4、DERIVEDfrom子句的子查询mysql会把子查询存放到一个临时表中叫做派生表5UNION RESULT查询所有union的合并结果6SUBQUERY子查询中的第一个查询结果不依赖于外部查询7DEPENDENT SUBQUERY子查询中的第一个查询依赖于外部查询。3、Table 表示执行的sql访问的表名称但有union时是union2,3,4有子查询时是4、Type 常用的类型有ALL、index、range、ref、eq_ref、const、system、NULL从左到右性能从差到好 ALL扫描全表进行查询访问的是聚集索引的所有叶子节点 Index扫描全索引拿到结果一般是扫描二级索引的的全部叶子节点这种一般是覆盖索引 Range:范围扫描通常出现在in、between、、之中使用一个索引来检索给定的行 Ref不使用唯一索引而是使用部分索引字段或不是唯一索引所的有字段或者唯一索引的前缀可以查询到多行记录。 eq_ref:使用唯一索引进行连接查询每个条件最多返回一行记录 Const、systemmysql对查询进行优化把查询结果转换为一个常量如将主键作为唯一一个查询条件mysql会把这个查询转变为常量。system是const类型的特列但表中只有一行记录的情况下就是system类型。NULLmysql在优化过程中分解语句在执行时甚至不用访问表和索引例如此哦那个一个索引列里选取最小值可以通过单独索引查找完成。5、possible_keys 可能被使用的索引但是不一定被用到因为mysql根据自己的优化器可能不使用该索引。如果为null,则表示没有相关的索引。6、Key 显示mysql实际采用的索引如果没有使用索引则该值是null如果想强制使用或忽略possible_keys中的索引可以在查询中使用force index、use index或者ignore index.7、key_len 表示索引中使用的字节数key_len计算规则如下 字符串char(n)和varchar(n)5.0.3以后版本中n均代表字符数而不是字节数如果是utf-8一个数字 或字母占1个字节一个汉字占3个字节 char(n)如果存汉字长度就是3n 字节 varchar(n)如果存汉字则长度是3n2字节加的2字节用来存储字符串长度因为 varchar是变长字符串 数值类型 tinyint1字节 smallint2字节 int4字节 bigint8字节 时间类型 date3字节timestamp4字节 datetime8字节 如果字段允许为NULL需要1字节记录是否为NULL索引最大长度是768字节当字符串过长时mysql会做一个类似左前缀索引的处理将前半部分的字符提取出来做索引。8、ref:列于索引的比较表示表的连接匹配条件即哪些列或者常量被用于查找索引列上的值常见的有const、字段名9、Rows 估算找到所需的记录所要读取的行数。10、Extra Using where:使用where语句来处理结果并且查询的列未被索引覆盖 Using index使用覆盖索引也就是要查询的字段都可以从被查询索引上获取一般是针对二级索引。 Using index condition:查询的列没有被使用的索引完全覆盖并且使用where条件中使用的是索引的范围查询。 Using temporarymysql创建一个临时表来处理查询比如distinct、group by、order by这种情况要进行优化一般用索引进行进行优化把把操作的字段建立索引。 Using filesort使用的是外部排序而不是索引排序数据较小时在内存排序否则在磁盘文件排序这种情况下应该给排序的字段建立索引来优化比如order by 排序的字段建索引。 Using join buffer连表查询时在获取连接条件时没有使用索引并且需要连接缓冲区来存储中间结果这种情况应该添加索引来优化。 Select tables optimized away:使用聚合函数min 、max来访问是索引的字段但是前提是没有where条件因为有查询条件聚合函数就不能直接找到其值了。1、索引的底层实现原理和优化推荐博客一文弄懂索引数据结构 BTreeInnoDB建议为大部分表使用默认自增的主键作为主索引。1.1 B树。图例说明键值key为记录的键值可理解为表中的主键值数据data为一行记录中除主键外的数据。B树特点每个节点占用一个盘块的磁盘空间一个节点上有两个升序排序的主键key、除主键外的数据data、三个指向子树根节点的指针p1 p2 p3。指针存储的是子节点所在磁盘块的地址。以根节点为例关键字为17和35P1指针指向的子树的数据范围为小于17P2指针指向的子树的数据范围为17~35P3指针指向的子树的数据范围为大于35。缺点每一个页的存储空间是有限的如果data数据较大时将会导致每个节点即一个页能存储的key的数量很小、当存储的数据量很大时同样会导致B-Tree的深度较大增大查询时的磁盘I/O次数影响查询效率。1.2 B树图例说明假设每个磁盘块能存储4个键值及指针信息。键值key为记录的键值可理解为表中的主键值数据data为一行记录中除主键外的数据。B树是B数的优化版特点如下1在同一层的叶子节点上存放键值和数据按照键值大小顺序存放所有数据记录节点2在所有的叶子结点中增加了指向下一个叶子节点的双向指针。 意味着所有的值都是按顺序存储的并且每一个叶子页到根的距离相同很适合查找范围数据。说明支持范围查询和天然排序。2非叶子节点上只存储key值信息、磁盘指针。可以看成是索引部分结点中仅含有其子树根结点中最大或最小关键字。B树一般有多大深度InnoDB存储引擎中页的大小为16KB一般表的主键类型为INT占用4个字节或BIGINT占用8个字节指针类型也一般为4或8个字节也就是说一个页BTree中的一个节点中大概存储16KB/(8B8B)1K个键值因为是估值为方便计算这里的K取值为103。也就是说一个深度为3的BTree索引可以维护103 * 103 * 103 10亿条记录。实际情况中每个节点可能不能填充满因此在数据库中BTree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。3、什么情况下设置了索引但无法使用以“%”开头的LIKE语句模糊匹配OR语句前后没有同时使用索引数据类型出现隐式转化如varchar不加单引号的话可能会自动转换为int型没有查询条件或者查询条件没有建立索引在查询条件上没有使用引导列查询的数量是大表的大部分应该是30以上。索引本身失效查询条件使用函数在索引列上或者对索引列进行运算运算包括(-*/! 等) 错误的例子select * from test where id-19; 正确的例子select * from test where id10;对小表查询提示不使用索引统计数据不真实CBO计算走索引花费过大的情况。其实也包含了上面的情况这里指的是表占有的block要比索引小。隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子select * from test where tu_mdn13333333333; 正确的例子select * from test where tu_mdn‘13333333333’;1, 2,单独的,,(有时会用到有时不会)like “%_” 百分号在前.表没分析.单独引用复合索引里非第一位置的索引列.字符型字段为数字时在where条件里不添加引号.对索引列进行运算.需要建立函数索引.not in ,not exist.当变量采用的是times变量而表的字段采用的是date变量时.或相反情况。B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走联合索引 is not null 只要在建立的索引列不分先后都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null但必须在所有列 都满足is null的时候,或者一个值 当建立索引的第一位置是一个值时,其他索引列可以是任何情况包括is null 一个值,以上两种情况索引都会走。其他情况不会走。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语句中哪个效率更高?聚簇索引和普通索引的区别概念类聚合函数? 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的表不要建立太多的索引不要将那些频繁修改的列作为索引列索引的优点创建唯一性索引保证数据库表中每一行数据的唯一性大大加快数据的检索速度这也是创建索引的最主要的原因加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。索引的缺点索引创建在表上不能创建在视图上创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大当对表中的数据进行增加、删除和修改的时候索引也要动态的维护降低了数据的维护速度10、索引的实现方式都是 B树索引 Innodb 是索引组织表 myisam 是堆表 索引组织表和堆表的区别要熟悉11、Oracle索引分为哪几类说出唯一索引和位图索引的概念。Oracle索引有B树索引位图索引函数索引簇索引等。唯一索引也是B树索引的一种它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。位图索引并不是采用象唯一索引那样存储索引字段值记录ROWID来创建索引段的而是为每一个唯一的字段值创建一个位图位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。12、如何写 sql 能够有效的使用到复合索引。由于复合索引的组合索引类似多个木板拼接在一起如果中间断了就无法用了所以要能用到复合索引首先开头(第一列)要用上比如index(a,b) 这种我们可以select table tname where aXX 用到第一列索引 如果想用第二列 可以 and bXX 或者and b like‘TTT%’要注意使用复合索引需要满足最左侧索引的原则也就是查询的时候如果where条件里面没有最左边的一到多列索引就不会起作用。13、当数据表中A、B字段做了组合索引那么单独使用A或单独使用B会有索引效果吗使用like查询如何有索引效果看A、B两字段做组合索引的时候谁在前面谁在后面如果A在前那么单独使用A会有索引效果单独使用B则没有反之亦然。同理使用like模糊查询时如果只是使用前面%那么有索引效果如果使用双%号匹配那么则无索引效果14、数据库索引的实现(B树介绍、和B树、R树区别)http://blog.csdn.net/kennyrose/article/details/7532032http://www.xuebuyuan.com/2216918.html15、索引的使用注意事项https://www.cnblogs.com/zlingh/p/3883716.html16、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.htmlhttp://www.cnblogs.com/cq-home/p/3482101.html17、聚集索引和非聚集索引的区别。聚集索引就是索引和记录紧密在一起。MySQL里主键就是聚集索引。 定义数据行的物理顺序与列值一般是主键的那一列的逻辑顺序相同一个表中只能拥有一个聚集索引。单单从定义来看是不是显得有点抽象打个比方一个表就像是我们以前用的新华字典聚集索引就像是拼音目录而每个字存放的页码就是我们的数据物理地址我们如果要查询一个“哇”字我们只需要查询“哇”字对应在新华字典拼音目录对应的页码就可以查询到对应的“哇”字所在的位置而拼音目录对应的A-Z的字顺序和新华字典实际存储的字的顺序A-Z也是一样的如果我们中文新出了一个字拼音开头第一个是B那么他插入的时候也要按照拼音目录顺序插入到A字的后面。数据行的物理顺序与列值的顺序相同如果我们查询id比较靠后的数据那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同所以也就只能建立一个聚集索引了。非聚簇索引 索引文件和数据文件分开存放索引文件的叶子页只保存了主键值要定位记录还要去查找相应的数据块。执行select * from user where id100;直接走到聚簇索引叶子直接拿到 id,name,age 全部字段无回表。18、数据库中 BTREE 和 Btree 区别。B是btree的变种本质都是btreebtree与B-Tree相比BTree有以下不同点每个节点的指针上限为2d而不是2d1。内节点不存储data只存储key叶子节点不存储指针。http://lcbk.net/9602.htmlBtree 怎么分裂的什么时候分裂为什么是平衡的。Key 超过1024才分裂B树为甚会分裂 因为随着数据的增多一个结点的key满了为了保持B树的特性就会产生分裂就向红黑树和AVL树为了保持树的性质需要进行旋转一样