文章目录
Mysql官方为什么推荐使用自增 id作为表的主键
自增 id 可以避免页分裂。
创建索引的原则和说明
- 选择唯一性索引:唯一索引的值是唯一的。可以更快的通过该索引来确定某条记录。
- 为经常需要排序、分组、以及联合查询的列创建索引。
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。 - 为经常作为查询条件的列创建索引。
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。 - 最左前缀匹配原则
非常重要的原则,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的顺序可以任意调整。 - =和in可以乱序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式 - 尽量选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 - 索引列不能参与计算
保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); - 尽量的扩展索引,不要新建索引。
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可 - 选择唯一性索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。 - 限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。 - 尽量使用数据量少的索引
如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。 - 尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。 - 删除不再使用或者很少使用的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
索引失效的情况和说明
- 索引无法存储null值
a.单列索引无法储null值,复合索引无法储全为null的值。
b.查询时,采用is null条件时,不能利用到索引,只能全表扫描。 - 为什么索引列无法存储Null值?
a.索引是有序的,NULL值进入索引时,无法确定其应该放在哪里。
将索引列值进行建树,其中必然涉及到诸多的比较操作,null 值是不确定值无法比较,无法确定null出现在索引树的叶子节点位置。
b.如果需要把空值存入索引,
方法有二:
其一,把NULL值转为一个特定的值,在WHERE中检索时,用该特定值查找。
其二,建立一个复合索引。
例如:create index ind_a on table(col1,1); 通过在复合索引中指定一个非空常量值,而使构成索引的列的组合中,不可能出现全空值。 - 不适合键值较少的列(重复数据较多的列)
假如索引列TYPE有5个键值,如果有1万条数据,那么 WHERE TYPE = 1将访问表中的2000个数据块。再加上访问索引块,一共要访问大于200个的数据块。如果全表扫描,假设10条数据一个数据块,那么只需访问1000个数据块,既然全表扫描访问的数据块少一些,肯定就不会利用索引了。 - 前导模糊查询不能利用索引(like ‘%XX’或者like ‘%XX%’)
假如有这样一列code的值为’AAA’,‘AAB’,‘BAA’,‘BAB’ ,如果where code like '%AB’条件,由于前面是
模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。 - 条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引。 - 对于多列索引,不是使用的第一部分,则不会使用索引。
- like查询以%开头的不会走索引。
- 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
- 如果Mysql估计使用全表扫描要比使用索引快,则不使用索引。
- MySQL主要提供2种方式的索引:B-Tree索引,Hash索引
B树索引具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN)。相当于二分查找。
哈希索引只能做等于查找,但是无论多大的Hash表,查找复杂度都是O(1)。
显然,如果值的差异性大,并且以等值查找(=、 <、>、in)为主,Hash索引是更高效的选择,它有O(1)的查找复杂度。
如果值的差异性相对较差,并且以范围查找为主,B树是更好的选择,它支持范围查找
索引的分类
- 普通索引:仅加速查询
- 唯一索引:加速查询 + 列值唯一(可以有null)
- 主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个
- 组合索引:多列值组成一个索引,专门用于组合索引,其效率大于索引合并
- 全文索引:对文本的内容进行分词,进行搜索
索引采用的数据结构
Mysql主要两种数据结构:hash索引和B+Tree索引。我们使用的是innoDB引擎,默认是B+树索引。
InnoDB为什么采用B+树的索引模型,为什么不用Hash索引
因为Hash索引底层是哈希表,哈希表是一种以key-values存储数据的结构,所以多个数据存储关系上是完全没有任何顺序关系的,所以,对于对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以哈希索引只适用等值查询的场景。而B+树是一种多路平衡查询树,所以他的节点天然有序的(左子节点小于父节点,右子节点大于父子节点),所以对于范围查询的时候不需要做全表扫描。
B+Tree索引 和 Hash索引的区别
-
hash索引适合等值查询,无法进行范围查询。
-
hash索引无法利用索引完成排序。
联合索引的最左匹配原则 什么是最左匹配原则? 最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
-
hash索引不支持多列联合索引的最左匹配规则。
-
如果有大量重复键值的情况下,hash索引的效率会很低,因为哈希碰撞问题。
B+Tree的叶子节点都可以存放哪些东西
innoDB的B+Tree可能存储的是整行数据,也有可能是主键的值?
innoDB的B+Tree存储整行数据和主键的值的区别?
- 整行数据:innoDB的B+Tree存储了整行数据的主键索引,也被称为聚合索引。
- 存储主键的值:成为非主键索引,也被称为非聚合索引
聚簇索引和非聚簇索引,在查询数据的时候有什么区别?为什么?
聚簇索引查询会更加快一些。因为主键索引树的叶子节点存储的是整行数据。也就是我们需要得到的数据。
而非主键索引的叶子节点是主键的值,查询主键之后我们还需要通过主键再次查询数据(这个过程被称之为回表)。
非主键索引一定会查询多次吗?
不一定的,因为通过覆盖索引也可以只查询一次。
怎么查询SQL语句是否使用了索引查询?
使用explain查询SQL语句的执行计划,通过执行计划来分析索引的使用情况。
优化器的执行过程?
- 根据搜索条件,找出可能使用的索引。
- 计算全表扫描的代价。
- 计算使用不同索引执行查询的代价。
- 对比各种执行方案的代价,找出成本最低的一个。