索引与查询优化
网易数据库 潘巍
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