2_索引与查询优化

索引与查询优化

网易数据库 潘巍
SQL语言部分与程序SQL

什么是索引

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

• 索引通过各种数据结构实现，是（值=>行位置）映射
• 索引的作用：
  提高访问速度
  实现主键、唯一键逻辑
• 索引的使用场景
  避免全表扫描
  进行范围、区间查询

mysql中的索引类型

*索引类型指的是实现索引的具体数据结构
*Btree索引
实际上是B+tree,mysql,甚至绝大部分RDBMS最主要的索引结构
B-tree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高。
*Hash索引
mysql中主要用于InnoDB一些内存索引结构，以及MEMORY存储引擎

• Rtree索引 用于地理位置检索，myisam引擎专有
• fulltext 目前主要用于myisam引擎
• Bitemap索引 mysql不支持位图索引
• innobd聚簇索引与二级索引

索引的代价

插入、修改、删除每一个索引行都变成一个内部事务，索引越多，事务越长

索引的优势

*减少查询IO
*优化等值查询、范围查询
*利用有序特性 order by /group by /distinct/max/min

如何用好索引

*依据where查询条件建立索引
-select a,b from table_a where c= ?;

*使用联合索引，而不是多个单列索引
-select * from tab_a where a=? and b=?;
-idx_a_b(a,b)
*联合索引中索引字段的顺序根据区分度排，区分度大的放在前面
-idx——smp(name,gender)
-idx_smp(gender,name) 效率更高
*联合索引能为前缀单列，复列查询提供帮助
*合理创建联合索引，避免冗余
*order by ,group by distinct 字段添加索引

查看索引

explain是确定一个查询如何走索引最简有效的方法
关注的项目

• type：查询access的方式
• key:本次查询最终选择使用哪个索引，null为未使用索引
• key_len :选择的索引使用的前缀长度或整个长度
• rows：可以理解为查询逻辑读，需要扫描过的记录行数
• extra:额外信息，主要指的是fetch data的具体方式

小结

• 对于不确定的关键语句上线前五笔进行explain
• type为all的格外注意，避免全表扫描
• key_len只能用很少一部分前缀的要注意索引字段顺序等
• extra 里看到Using filesort和Using temporary都要尽量优化，这两种fetch方式都不应该出现在任何执行频繁的关键语句中
• 强制使用索引hint

  select * from tabel1 force index(field1)   #只使用建立在field1上的索引，而不使用其他字段上的索引
  select * from table1 ignore index(field1,field2)  #table1表中field1和field2上的索引不被使用
  select sql_no_cache field1，field2 from table； #关闭查询缓存sql_no_cache
  select sql_calhe * fron table1;  #强制查询缓存 sql_calhe
  select high_priority * from table1; # 优先操作 high_priority
  update low_priority table1 set field1 = where field1= ... #操作滞后
  insert delayed into table1 set field1= ...  #延时插入
  SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE … # 强制连接顺序 straight_join
  SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …   #强制使用临时表 SQL_BUFFER_RESULT
  SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1; #分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT