本文共 4722 字,大约阅读时间需要 15 分钟。
程序员小新人学习 2018-08-02 11:58:55
MYSQL索引与查询优化
一、什么是索引
1、简单定义
索引通过各种数据结构实现值到行(值=》行)位置的映射,没有索引会全表扫描;
2、索引的作用
(1)提高访问速度;
(2)实现主键、唯一键逻辑;
3、索引类型(数据结构)
索引类型是指实现索引的具体数据结构,mysql的索引类型主要包括:Btree索引、hash索引等,重点是B-tree索引
(1)Btree索引
MYSQL里的Btree索引,实际上是B+tree索引,是在Btree上(平衡二叉树)改良的。
Btree上每个节点只有一个值,而B+tree的每个节点上可以保存很多个值,且值是按照索引键值顺序来排序的,这是因为innodb每次读取数据节点的数据块是以page(页,16k)为单位读取数据,如果尽可能多的将数据值放入一个page可以极大减少IO次数。
补充:innodb存储引擎表,没有显示定义主键,也没有非空的唯一索引,那么innodb存储引擎会自动创建一个隐藏的主键。
这个自动创建的隐藏主键是6字节大小的指针,范围大小有限,只可以到21亿 如果到达了21亿后不会报错, 会重新生成就会把之前的数据覆盖掉;且这个隐藏主键是无序的聚集索引。也就是说不显示定义自主主键,指望innodb表自动创建隐藏主键,在数据插入时是随机写入。没有主键有唯一索引的插入,也是随机插入。只有显示定义自增主键,插入才是有序插入。
(2)innodb的表是以索引组织表形式存储的,MYISAM的表是以堆表形式存储
innodb存储类型的表都是以索引组织表形式存储的,也就是整个数据表的存储都是B+tree结构的,其中查询键是主键(如果没有显示定义自主主键,就用不为空的唯一索引来做聚簇索引,如果也没有唯一索引,则innodb内部会自动生成6字节的隐藏主键来做聚簇索引),叶子节点存储了完整的数据行信息(以主键+row_data形式存储)。
因为innobd表是索引组织表,也就是B+tree结构的,因此如果innodb表有自主主键,则数据写入是有序写入的,效率会很高;如果innodb表没有自增的主键,那插入的话就是随机插入。这也是为什么会建议innodb表要有无业务意义的自增主键,可以大大提高数据插入效率。
4、聚簇索引和二级索引(非聚簇索引,一般索引)
在《数据库原理》里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此,所以一个表最多只能有一个聚簇索引。
(1)聚簇索引:主键就是聚簇索引,将主键作为键值把整个表建成一个索引。也就意味着,整个表的所有数据的物理存储都是按照主键的索引顺序来排序的(叶子节点根据主键顺序,依次存储数据行);
(2)二级索引:除了主键以外,用户创建的其他索引,也是B+tree结构,键值就是建立了索引的那个字段,叶子节点的值(指针指向的)其实就是主键,一般查询会先根据二级索引(建立了二级索引的字段)查找对应的主键,再根据主键查找数据表的B+tree,找到对应的记录行数据。
二级索引并不是说层高只有两层,二级索引也就是非聚簇索引,和聚簇索引相区别。
建立索引是有代价的,在增删改操作时都可能要更新索引的B+tree来保证树的平衡,导致每一个索引行的更新都变成了一个内部事务,索引越多,事务越长。因此索引不是越多越好;
5、索引的优势
(1)减少查询IO;
(2)优化等值查询或者范围查询;也即是范围查询和等值查询都是会走索引的;
(3)利用有序特性(例如:order by/group by/distinct/max/min等函数);这些操作都是利用排序技术来实现的,而索引天然就是有序的,因此使用到order by等有序操作时,对相关字段建立索引会提高效率。
6、如何用好索引
(1)依据where查询条件建立索引;
(2)使用联合索引,而不是多个单列索引;
例如:select * from tab_a where b=? and c=?这个SQL,对b c字段建立联合索引的效率比单列的索引效率更高。
(3)联合索引中索引的顺序根据区分度排,区分度大的放在前面。区分度是指字段值的种类,字段值种类越多的字段要放在前面,例如:idx_smp(name,gender)的效率要比idx_smp(gender,name)的效率高
(4)联合索引能为前缀单列、复列查询提供帮助;
例如:
有idx_smp(a,b,c)这样的索引,where a=?或者where a=? and b=?都可以使用该索引,但是where c=?就无法使用该索引。
(5)同样的,要合理创建联合索引,避免冗余
例如建立了idx_smp(a,b,c)就不需要建立idx_smp(a)、idx_smp(a,b)索引了。
(6)order by group by distinct等需要排序的操作,在没有索引的大数据量情况下需要排序,对IO和CPU性能消耗很大。如果有类似排序需求,则需要对相关字段建立索引,这样利用索引的有序特性不需要排序,直接按着索引顺序扫描即可。
(7)select …where .. like ‘%xx’ ; 这种 % 放在头部的,是无法走索引的。
(8)select * 不建议使用,因为会读取大量数据,也不利于使用索引覆盖技术。索引字段能够完全在索引中获取, 就不要使用select *(因为会导致回表),无法完整在索引中获取,也是建议select具体字段。
7、查看是否使用了索引:explain命令查看
(1)explain是确定一个查询如何走索引的最简便有效的方法;
(2)关注的字段值:
——id字段:表示查询中执行select子句或操作表的顺序。
id如果相同,可以认为是一组,从上往下顺序执行;在所有组中, id值越大,优先级越高,越先执行。
——type字段:查询access的方式;
type=all表示全表扫描数据,不走索引;
type=index表示full index scan,和all的区别是index类型只遍历索引树。
——key字段:本次查询最终选择使用哪个索引,NULL表示未使用索引;
——key_len字段:选择的索引使用的前缀长度或者整个长度(判断联合索引的使用情况);
——rows字段:可以理解为查询逻辑读,需要扫描过的记录行数;
——extra字段:额外信息,主要指的fetch data的具体方式;
extra=using tmporary表示mysql需要使用临时表来存储结果集,常见于排序和分组查询。
extra=using filesort表示文件排序,需要对其优化。mysql中无法利用索引完成的排序操作称为“文件排序”。
using tmporary可能是内存临时表也可能是磁盘临时表,如果临时表大小超过tmp_table_size大小才会产生基于磁盘的临时表,也就是说,只是通过explain执行计划是无法查看是否用来磁盘临时表的,如果show processlist查看的线程有“Created_tmp_disk_tables”关键字才能代表是用使用了磁盘临时表
(3)explain的一些使用建议:
(3.1)对不确定执行计划的关键语句上线前务必explain;
(3.2)type为all的要格外注意,避免全表扫描;
(3.3)key_len只能用很少一部分前缀的,要注意索引字段顺序等;
(3.4)extra里看到using filesort和using tmporary都要尽量优化,这两种fetch方式不应该出现在任何执行频繁的关键语句中。
(4)强制使用索引 hint:
select * from table_1 force index(xxx)…
select * from table_1 ignore index(yyy)….
默认情况下,建议使用mysql优化器,不要强制所用或忽略索引
转载地址:http://tizws.baihongyu.com/