MySQL索引以及优化小计

  • 内容
  • 评论
  • 相关

什么是MySQL索引?

索引是对数据库表中一列或多列的值进行排序的一种结构。一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。索引是一个文件,它是要占据物理空间的。

MySQL索引有哪些类型?分别有什么区别

  • 主键索引: 数据列不允许重复,不允许为NULL.一个表只能有一个主键。
  • 唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引
  • 普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。
  • 全文索引: 是目前搜索引擎使用的一种关键技术。

如何创建MySQL索引?

ALTER TABLE table_name ADD UNIQUE (column); #创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column1,column2); #创建唯一组合索引
ALTER TABLE table_name ADD INDEX index_name (column); #创建普通索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); #创建组合索引
ALTER TABLE table_name ADD FULLTEXT (column); #创建全文索引

Coder们常说的最左前缀,最左优先是什么?

顾名思义,就是最左优先,在创建多列索引(又名联合索引)时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

还有一个就是生效原则 比如:

index(a,b,c)
where a=3	#只使用了a
where a=3 and b=5	#使用了a,b
where a=3 and b=5 and c=4	#使用了a,b,c
where b=3 and where c=4	#没有使用索引
where a=3 and c=4	#仅使用了a

注:以上where子句的字段条件顺序并不影响生效原则

面试中常问的MySQL索引算法是什么?

B-tree、B+tree(B树算法、B加树算法):

BTree是最常用的mysql数据库索引算法,也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量, 例如:

select * from user where name like 'jack%'; 
# 如果一通配符开头,或者没有使用常量,则不会使用索引,例如(mysql 5.6之后解决的这个问题,但是在某些情况下依然会索引失效): 
select * from user where name like '%jack'; 

Hash算法

Hash Hash索引只能用于对等比较,例如=,<=>(相当于=)操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。

索引有哪些设计原则?

  1. 适合索引的列是经常出现在where子句中的列,或者连接子句中指定的列
  2. 基数较小的列,索引效果较差,没有必要在此列建立索引(例如一些记录状态列的)
  3. 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间
  4. 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

哪些情况下索引会失效呢?

  1. 在索引上进行任何操作,使用函数、计算、类型转换都会导致索引失效,进而进行全表扫描
  2. msyql在使用不等于(!=、<>)的时候会导致索引失效。
  3. is null和is not null也无法使用索引
  4. like通配符在左边时不使用索引(5.6版本之前)
  5. 整数类型的列中查询加了引号时会导致索引失效(同1)
  6. 使用or查询会导致索引失效

MySQL的排序(ORDER BY)如何使用索引优化?

MySQL支持两种方式的排序,FileSort和Index,Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY 满足两种情况,会使用Index方式排序

  1. ORDER BY语句使用索引最左前列
  2. 使用WHERE 子句与ORDER BY子句条件列组合满足索引最左前列

提高Order By的速度

1、Order by时select *是一个大忌只Query需要的字段,这点非常重要。在这里的影响是:

1.1 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法-单路排序,否则用老算法一一多路排序。

1.2 两种算法的数据都有可能超出sort_buffe啲容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size

2、尝试提高 sort_buffer_size,不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的

3、尝试提高 max_length_for_sort_data,提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。

GROUP BY 关键字优化

  1. group by 实质是先排序后分组,遵照索引的最佳左前缀。
  2. 当无法使用索引列,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
  3. where 高于having,能写在where限定的条件就不要去having去限定了。

本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可,非商业性质可转载须署名链接,详见本站版权声明。

评论

0条评论

发表评论

邮箱地址不会被公开。 必填项已用*标注