1、你一般怎么建索引的? 去my.cnf里配置三个配置 打开慢查询日志slow_query_log=1慢查询日志存储路径slow_query_log_file=/var/log/mysql/log-slow-queries.logSQL执行时间大于3秒,则记录日志long_query_time=3 监控到慢SQL后,就马上开始建索引? NO,NO,NO….这种时候,应该先考虑你的SQL能不能进行SQL优化。 例如,当只要一行数据时使用 limit 1 查询时如果已知会得到一条数据,这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索,而不是继续查询下一条是否符合标准直到所有记录查询完毕。 然而大多数情况下,业务SQL十分复杂,没法优化。所以就要建立索引了。这个时候,参照如下规则建立索引
2、讲讲索引的分类?你知道哪些? 从物理存储角度: 聚簇索引和非聚簇索引 从数据结构角度: B+树索引、hash索引、FULLTEXT索引、R-Tree索引 从逻辑角度:
3、如何避免回表查询?什么是索引覆盖? 这个问题,如果要看详细版,请参阅文章《Innodb中索引的原理》 这里简单说一下。 当能通过读取索引就可以得到想要的数据,那就不需要回表读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做索引覆盖。 例如此时有一张表table1,有一个联合索引(a,b) 执行如下SQL select a,b from table1 在索引上就能找到结果,就不用回表去查询! 而你执行的是 select a,b,c from table2 c列在索引上不存在,就需要回表查询。 需要说明的是覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B+ tree索引做覆盖索引。 4、现在我有一个列,里头的数据都是唯一的,需要建一个索引,选唯一索引还是普通索引? 答唯一索引! 首先,在孤尽出的《阿里巴巴JAVA开发规范》中有这么一段话
那好,下一问出现了! 为什么唯一索引的插入速度比不上普通索引?为什么唯一索引的查找速度比普通索引快? 这个问题就要从Insert Buffer开始讲起了,在进行非聚簇索引的插入时,先判断插入的索引页是否在内存中。如果在,则直接插入;如果不在,则先放入Insert Buffer 中,然后再以一定频率和情况进行Insert Buffer和原数据页合并(merge)操作。 这么做的优点:能将多个插入合并到一个操作中,就大大提高了非聚簇索引的插入性能。 InnoDB 从 1.0.x 版本开始引入了 Change Buffer,可以算是对 Insert Buffer 的升级。从这个版本开始,InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。 唯一速度的插入比普通索引慢的原因就是:
于是乎下一问又来了! 为什么唯一索引的更新不使用 Change Buffer? 因为唯一索引为了保证唯一性,需要将数据页加载进内存才能判断是否违反唯一性约束。但是,既然数据页都加载到内存了,还不如直接更新内存中的数据页,没有必要再使用Change Buffer。 最后回答一下,唯一索引的搜索速度比普通索引快的原因就是:
5、mysql索引是什么结构的?用红黑树可以么? 这个妥妥答最常见的B+ Tree。 AVL树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据数据存储的时候,显然不能将全部数据全部加载进内存,因此如果采用红黑树,就会造成频繁IO,效率低下。 那为啥不用B Tree,而选择B+ tree呢? 这就需要贴一下经典的两张图。B tree是长下面这样的 注意一下B tree的两个明显特点
而B+ tree长下面这样的 注意一下B+ tree的两个明显特点
接下来就可以开始编了~~比如数据库索引采用B+ tree的主要原因是B Tree在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题,B+ tree应运而生。B+ tree只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,如果使用B Tree,则需要做局部的中序遍历,可能要跨层访问,效率太慢。 提示,我下一问就是: 你刚才说了这么多B tree不行,那你知道为啥Mongodb用B Tree当索引,而不用B+ Tree么? (从关系数据库和非关系数据库的区别角度去答,不拓展了!仔细想想,在Mongodb里表示二者的关系,你会怎么处理!) 6、mysql某表建了多个单索引,查询多个条件时如何走索引的? 其实,我看到这题的时候,内心一抖。这题让后端开发来答,真的很拼功底! 这里希望大家先看看我的另一篇文章《我是一条DQL》。此题在考优化器的知识!此题是在考察优化器如何抉择索引的!优化器会评估出走哪个索引最优,然后执行。 Mysql在优化器中有一个优化器称为Range 优化器,负责进行范围查询的优化! 那么该优化器计算执行成本有两种方式index dive与index statistics。 它们是MySQL优化器对开销代价的估算方法,前者统计速度慢但是能得到精准的值,后者统计速度快但是数据未必精准。 坦白说写到这里,我内心痛哭流涕,要把index dive和index statistics写明白,真不是一件容易的事,这里只能稍微扯扯。 对于index dive: 计算成本的方式为 COST = CPU COST + IO COST 其中CPU COST指的是处理返回记录所花的开销。而IO COST指的是读取页面的开销。 mysql会对每种索引的执行情况,进行上述成本计算,最后以成本小的方式进行执行。 但是呢,在某些情况下mysql执行index dive的成本太大。因此优化器会选择以index statistics方式进行估算成本。 具体如下: SHOW INDEX FROM tbl_name [FROM db_name] 此时出来的结果中,有一列名为Cardinality,该值表示索引列中不重复值的个数。 简单来说就是,索引列的唯一值的个数,如果是复合索引就是唯一组合的个数。 这个数值将会作为mysql优化器对语句执行计划进行判定时依据。如果唯一性太小,那么优化器会认为,这个索引对语句没有太大帮助,而不使用索引。 Cardinality值越大,就意味着,使用索引能排除越多的数据,执行也更为高效。 |
|