mysql优化之索引优化
1、索引是什么?
官网给出的答案是:索引(Index)是帮助MySQL高效获取数据的数据结构。其本质就是数据结构,排好序的快速查找的数据结构。
2、索引的数据结构
- 二叉树(二叉树在极端的情况下会变成一个链表(比方说添加的数据都是递增的)在这种情况下二叉树的查询速度是特别低的)
- 红黑树(红黑树是一种平衡二叉树,每一个节点的子节点都是2,当插入的节点个数都是递增的话,红黑树会通过自旋将自己维护成一个平衡的二叉树,但是会导致树的层级很高,需进行多次 I/O,而且IO的次数多少,取决于增加的数据)
- Hash 表(哈希表存储,其实是将存入的值,进行hash计算,得到一个散列值,将这个散列值,与数据的内存地址进行绑定,精确查询的速度很快,但是但不适合范围查找)
- B-Tree(B树是一个树状的数据结构,但是B树的每个节点都存储了key和data,key是索引的值,data存的是索引所在行的内存地址,但是不适合范围查找)
- B+Tree(这是索引使用的数据结构)
2.1、B树
B树是一颗多路平衡查找树,它类似普通的平衡二叉树,不同的一点是B-树允许每个节点有更多的子节点。
特点
1.所有键值分布在整颗树中(索引值和具体data都在每个节点里)
2. 每一个关键字有且只出现一次
3. 所有关键字按照从小到大的顺序进行排列
4. 每个节点除了存储关键字,还存储数据
5. 若经常访问的元素离根节点较近,则访问更加迅速
6. 叶子节点存储在同一层
7. 黑色阴影处存储的是下一个节点在磁盘中的地址
2.2、B+树
B+树是B-树的变体,也是一种多路搜索树
B+ 树特点?
- B+ 树将 data 信息挪到了叶子节点,非叶子节点不存放 data 信息,只存储索引(冗余),这就意味着非叶子节点可以存放更多的索引
- 叶子节点包含所有索引字段,叶子节点没有子节点,不用存储下一个字节的磁盘地址,所以没有那个白色的框框,但是叶子节点包含索引的 data 域
- 叶子节点用指针连接,提高区间访问的性能,这也恰好是 B 树索引和 Hash 索引劣势所在,比如执行如下 SQL :select * from t where col1 > 6 ,对于 B 树索引和 Hash 索引,他们根本不知道大于 6 的数据有哪些
- 假设索引为主键索引,我们使用 BigInt 类型,占用 8 个字节(对应着色并且带着数字的框框),MySQL 中的磁盘地址指针为 6个字节(对应空白框框),那么我们存储一个索引则需要 14 个字节,那么一个 MySQL 磁盘页可以存储 16KB/14B = 1170个索引字段
- 不同数据库对 data 域的实现方式不同,有些数据库 data 域存放的是索引行所在的磁盘地址指针,有些数据库 data 域存储的是索引行所有其他字段的信息,我们保守估计,假设每个 data 域占用 1KB,那么一个 MySQL 磁盘页可以存储个16KB/1KB = 16 个索引字段和 data 域
- 假设三层 B+ 树被撑满了,能存放的数据量为 1170 * 1170 * 16 = 21902400 条,能存放两千多万条数据
B+树与 B- 树的不同之处在于:
- 所有关键字存储在叶子节点内部,非叶子节点并不存储真正的 data
- 为所有叶子结点增加了一个链指针
- 因为内节点并不存储 data,所以一般B+树的叶节点和内节点大小不同,而B-树的每个节点大小一般是相同的
mysql使用B+树的原因?
- B+ 树将 data 信息挪到了叶子节点,非叶子节点不存放 data 信息,只存储索引(冗余),这就意味着非叶子节点可以存放更多的索引
- B+树的查找路径是由根到叶子节点,每次查找路径长度比较稳定
- 范围遍历,B+树的叶子节点构成一条链表,访问更加方便
- B+树天然具备排序功能:B+树所有的叶子节点数据构成了一个有序链表,在查询大小区间的数据时候更方便,数据紧密性很高,缓存的命中率也会比B树高;
- B+树全节点遍历更快:B+树遍历整棵树只需要遍历所有的叶子节点即可,而不需要像B树一样需要对每一层进行遍历,这有利于数据库做全表扫描;
查看 MySQL 磁盘页的大小,默认 16KB
SHOW GLOBAL STATUS like 'Innodb_page_size';
2.3、MyISAM
MyISAM :
- 存储引擎是基于表的
- mysql 5.5之前的默认存储引擎是MyISAM
- MyISAM 索引文件和数据文件是分离的(非聚集索引)
- MyISAM 使用 B+ 树作为索引,图中的 data 域存储的是下一个磁盘的地址
举例说明 select * from t where col1=‘49’ 查找的过程:
- 加载第一层(根节点)的磁盘文件至内存,发现 15<49<56,拿到到第二层的磁盘地址
- 根据第一步拿到的磁盘地址,到第二层加载磁盘文件至内存,发现 49>=49 ,拿到到第三层的磁盘地址
- 根据第二步拿到的磁盘地址,到根节点找到 col1=49 的索引列,拿到索引所在行在磁盘中的地址
- 根据第三步拿到的磁盘地址,区磁盘中加载索引所在行的数据,返回给客户端
总共发生了 4 次 I/O
基于MyISAM 数据表各种文件的含义
- .frm:表结构的定义
- .MYD:数据文件
- .MYI:索引文件
2.4、InnoDB
InnoDB:
- 表数据文件(.ibd 文件)本身就是按B+Tree组织的一个索引结构文件
- InnoDB 中叶节点包含了完整的数据记录( data 与存放了索引所在行其他所有字段的信息)
- 聚集(簇)索引:索引文件和数据文件是存储在一个文件里面,InnoDB 的主键索引就是聚集索引
- 聚集索引的优势:比非聚集索引的查找效率高,因为非聚集索引需要在 .MYD 文件和 .MYI 文件中查找,而聚集索引只需要在一个 .ibd 文件中查找
- 查询数据发生的io次数要比MyISAM的要少
举例说明 select * from t where col1=‘49’ 查找的过程:
- 加载第一层(根节点)的磁盘文件至内存,发现 15<49<56,拿到到第二层的磁盘地址
- 根据第一步拿到的磁盘地址,到第二层加载磁盘文件至内存,发现 49>=49 ,拿到到第三层的磁盘地址
- 根据第二步拿到的磁盘地址,到根节点找到 col1=49 的索引列,然后直接拿出索引所在行的数据,返回给客户端。
总共发生了 3 次 I/O
为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?
InnoDB 中索引her数据存储是在同一个文件中,该文件就是按照 B+ 树去组织构建的,如果没有主键,InnoDB 没办法组织这个数据文件。在 MySQL 如果我们没有建立主键,那么MySQL 会自动选择一个具有唯一索引的列作为主键,如果找不到,就帮我们自动地新建一列,由mysql自己去维护。
为什么推荐主键为整形?
自增主键数据类型为整形,比较效率高,不要用 UUID 作为主键,字符串之间的比较效率低,并且整形数据占用的空间也比 UUID 小很多
为什么推荐主键自增?
首先,操作系统进行数据读取的时候,是按页的大小进行读取的(页大小通常为 4 kb),MySQL 的页,默认值为 16 KB,也就是说对于 B+ 树的节点,最好设置成页的大小(16 KB),这样一个 B+ 树上的节点就只会有一次 IO 读。推荐主键自增的原因,就牵扯到了页分裂和也合并了。
页分裂和也合并?
B+ 树为了维护索引的有序性,每插入或更新一条记录的时候,会对索引进行更新。
假设原来基于身份证作索引的 B+ 树如下(假设为二叉树 ,图中只列出了身份证的前四位)
现在有一个开头是 3604 的身份证对应的记录插入 db ,此时要更新索引,按排序来更新的话,显然这个 3604 的身份证号应该插到左边节点 3504 后面(如下图示,假设为二叉树,B+树 可以考虑容量大小)
如果把 3604 这个身份证号插入到 3504 后面的话,这个节点的元素个数就有 3 个了,显然不符合二叉树的条件,此时就会造成页分裂,就需要调整这个节点以让它符合二叉树的条件
这种由于页分裂造成的调整必然导致性能的下降,尤其是以身份证作为主键的话,由于身份证的随机性,必然造成大量的随机结点中的插入,进而造成大量的页分裂,进而造成性能的急剧下降。
那如果是以自增 id 作为主键?
由于新插入的表中生成的 id 比索引中所有的值都大,所以它要么合到已存在的节点(元素个数未满)中,要么放入新建的节点中(如下图示)所以如果是以自增 id 作为主键,就不存在页分裂的问题了。
有页分裂就必然有页合并,什么时候会发生页合并呢,当删除表记录的时候,索引也要删除,此时就有可能发生页合并,如图示
当我们删除 id 为 7,9 对应行的时候,上图中的索引就要更新,把 7,9 删掉,此时 8,10 就应该合到一个节点,不然 8,10 分散在两个节点上,可能造成两次 IO 读,势必会影响查找效率!
这个页大小是不是越大越好呢?
设置大一点,节点可容纳的数据就越多,树高越小,IO 不就越小了吗,这里要注意,页大小并不是越大越好,InnoDB 是通过内存中的缓存池(pool buffer)来管理从磁盘中读取的页数据的。页太大的话,很快就把这个缓存池撑满了,可能会造成页在内存与磁盘间频繁换入换出,影响性能。
关于推荐 InnoDB 主键自增?还有一个参考
InnoDB 使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)
自增的情况
- 如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:
- 这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
非自增的情况
- 如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:
- 此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此,只要可以,请尽量在InnoDB上采用自增字段做主键。
基于InnoDB数据表各种文件的含义
- .frm:表结构的定义
- .ibd :索引和数据文件
3、联合索引的结构?
我们在三个列上建立了一个联合索引:整形 id 、字符串 name、日期 birthDate
排序规则:建立索引时,哪个列写在前面,就先按照他排序
大致查找流程:
先按照 id 查找,如果 id 能比出大小,就按照 id 字段排序,如果 id 字段都相同,就按照第二个 name 字段进行比较,以此类推 …
比如 select* from employee where name=‘Staff’ 就不会走索引,
因为 name 是依靠着 id 进行排序的,再找不到id的情况下就会进行全盘扫描
4、什么情况下需要建立索引?
- 主键自动建立唯一索引
- 频繁作为查询的条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引,加重IO负担
- Where条件里用不到的字段不创建索引
- 在高并发下倾向创建组合索引
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
- 查询中统计或者分组字段
- 经常增删改的表不需要建立索引
- 表记录太少不需要建立索引
- 数据重复且分布平均的表字段不需要建立索引
5、单表、双表、三表的如何建立索引?
单表:
单表根据where后面的查询条件建立索引。
双表:
左表连接右表,则需要拿着左表的数据去右表里面查,索引需要在右表中建立索引
右表连接左表,则需要拿着右表的数据去左表里面查,索引需要在左表中建立索引
三表:
使用小表驱动大表,在大表中建立了索引
6、MySQL 索引分类
- 普通索引:是最基本的索引,它没有任何限制,即一个索引只包含单个列,一个表可以有多个单列索引;建议一张表索引不要超过5个,优先考虑复合索引
- 唯一索引:与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
- 主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:
- 复合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
- 全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配
- 覆盖索引:slelect要查询的列在定义的索引中就能够查询到。不必在读取数据行,可以理解为要查询的列要被所建的索引覆盖。
7、MySQL 常见瓶颈
- CPU 瓶颈:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
- IO 瓶颈:磁盘I/O瓶颈发生在装入数据远大于内存容量时
- 服务器硬件的性能瓶颈:top、free、iostat和vmstat来查看系统的性能状态
8、Explain是使用
Explain 是查看执行计划,使用EXPLAIN关键字可以模拟优化器执行SQL语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
作用?
- 表的读取顺序(id 字段)
- 数据读取操作的操作类型(select_type 字段)
- 哪些索引可以使用(possible_keys 字段)
- 哪些索引被实际使用(keys 字段)
- 表之间的引用(ref 字段)
- 每张表有多少行被优化器查询(rows 字段)
9、Explain 参数详解
id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
id 取值的三种情况:
-
id相同,执行顺序由上至下
-
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
-
id相同不同,同时存在:id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行;衍生=DERIVED
select_type:查询的类型,主要用于区别普通查询、联合查询、子查询等复杂查询
- SIMPLE:简单的select查询,查询中不包含子查询或者UNION
- PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
- SUBQUERY:在SELECT或者WHERE列表中包含了子查询
- DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表里
- UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
- UNION RESULT:从UNION表获取结果的SELECT
table:显示这一行的数据是关于哪张表的
type:访问类型排列,显示查询使用了何种类型
type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是
system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
挑重要的来说:
system>const>eq_ref>ref>range>index>ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL
- system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计
- const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
- eq_ref:唯一性索引,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
- ref:非唯一索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
- range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好,因为他只需要开始索引的某一点,而结束于另一点,不用扫描全部索引
- index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘数据库文件中读的)
- all:FullTable Scan,将遍历全表以找到匹配的行(全表扫描)
possible_keys 可能会用到的索引
- 显示可能应用在这张表中的索引,一个或多个
- 若查询涉及的字段上存在索引,则该索引将被列出,但不一定被查询实际使用
key 实际上用到的索引
key_len 表示索引中使用的字节数
ref 显示索引被哪一列使用了 如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值
例如:由key_len可知t1表的索引idx_col1_col2被充分使用,t1表的col1匹配t2表的col1,t1表的col2匹配了一个常量,即’ac’
rows 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra:包含不适合在其他列中显示但十分重要的额外信息
- Using filesort(文件排序):mysql无法利用自己建立的索引排序规则完成排序操作,达到最后的结果,从而导致mysql自己内部重新建立排序规则来达到自己想要的结果,mysql自己内排序会消耗大量的时间
- Using temporary(创建临时表):使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
- Using index(覆盖索引): 指的是要查询的列,在索引表中直接返回,没有去查询数据表,避免访问了表的数据行,效率比较高。
- Using where:表明使用了where过滤
- Using join buffer:表明使用了连接缓存
10、索引失效(应该避免)
- 全值匹配我最爱
- 最佳左前缀法则:如果索引了多例,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
- mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
- is null,is not null也无法使用索引(早期版本不能走索引,后续版本应该优化过,可以走索引)
- like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作
- 字符串不加单引号索引失效
- 少用or,用它连接时会索引失效
解决【like ‘%str%’ 】索引失效的问题:覆盖索引
口诀:
- 全值匹配我最爱, 最左前缀要遵守;
- 带头大哥不能死, 中间兄弟不能断;
- 索引列上少计算, 范围之后全失效;
- LIKE 百分写最右,覆盖索引不写 *;
- 不等空值还有 OR, 索引失效要少用;
- 字符引号不可丢, SQL 优化有诀窍。