MySQL破冰之旅第二篇_vlambda技术博客

vlambda
2021-02-12

MySQL破冰之旅第二篇

有志者，事竟成，破釜沉舟，百二秦关终属楚

苦心人，天不负，卧薪尝胆，三千越甲可吞吴

—

油条说：不受煎熬，怎会成型

昨天，MySQL围城之困第一篇基础结晶篇，我们畅聊了基础架构相关知识概念，备受煎熬。

今天，MySQL破冰之旅第二篇进阶升华篇，我们谈一谈性能调优相关策略方案，百炼成型。

—

豆腐说：关键阶段，需要点化

要说MySQL性能优化，那就不得不提索引啦，让我们以问答模式来开启破冰宝典第二小节。

问：索引是什么呢？

答：索引是一种可以快速检索数据库表数据的数据结构，MySQL的索引方式主要有两种：B+Tree和Hash，一般采用InnoDb引擎，默认是B+Tree。

问：B+Tree和Hash有什么区别呢？

答：B+Tree索引底层是一种多路平衡查找树的数据结构，节点天生有序，左叶子节点小于父节点，父节点小于右叶子节点，很适合范围查询；

而Hash索引底层是键值对存储的哈希表结构，数据存储逻辑上是无序的，仅适用于精确查询，不能进行范围匹配、模糊匹配、分组或排序。当键值重复较多时，Hash索引效率大大降低，冲突比较多，维护代价也较高。

问：索引有什么优点？

答：1、大大减少了服务器需要扫描的数据量

2、帮助服务器避免排序和产生临时表

3、将随机io改为顺序io，减少磁盘io次数

—

啤酒说：别急，总有让你冒泡的时候

聊完了索引的数据结构，那我们再进一步来探讨下索引的分类结构，问答模式开启第三小节

问：索引有哪些类别呢？

答：索引分为聚簇索引（即主键索引），非聚簇索引（非主键索引，包含普通索引、唯一索引、组合索引和全文索引）

问：什么是聚簇索引呢？

答：聚簇索引的索引结构和数据保存在同一个文件中。InnoDb引擎使用的就是聚簇索引，数据存储在叶子节点中，包含文件：.frm .idb

问：聚簇索引有什么优点和缺点？

答：优点：

1、数据和索引保存在一起，查询效率较快；

2、发生覆盖索引时可以直接使用主键值。

缺点：

1、聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部在内存，那么聚簇索引就没有什么优势；

2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式；

3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置；

4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临叶（叶子节点）分裂的问题；

5、基于聚簇索引的表在删除行时，可能面临叶合并的问题；

6、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于叶分裂导致数据存储不连续的时候。

问：什么是非聚簇索引呢？

—

拉面说：想成功，得有人拉一把

探讨完索引的数据结构和分类结构，我们再来谈一谈索引的匹配方式，问答模式开启第四小节

问：索引的匹配方式有哪些呢？

答：全值匹配、匹配列前缀、匹配最左前缀、匹配范围值、精确匹配和范围匹配组合、仅访问索引的查询匹配

问：什么是全值匹配？

答：全值匹配，指的是和索引中的所有列进行匹配

select * from member where code= 888888 and name = 'itbigbird';

匹配最左前缀，只匹配前面的几列

问：什么是匹配列前缀？

答：匹配列前缀，指的是可以匹配某一列的值的开头部分

select * from member where name like 'it%'; -- type=range,Extra=Using index conditionselect * from member where name like '%bird'; --索引失效， type=all,Extra=Using where

问：什么是匹配最左前缀？

答：匹配最左前缀，指的是只匹配前面的几列匹配最左前缀，只匹配前面的几列

select * from member where name = 'itbigbird';

匹配列前缀，可以匹配某一列的值的开头部分

问：什么是匹配范围值？匹配范围值，可以查找某一个范围的数据

答：匹配范围值，指的是可以查找某一个范围的数据

select * from member where name < 'itbigbird';

问：什么是精确匹配和范围匹配组合？

答：组合匹配，指的是精确匹配某一列并范围匹配另外一列，可以查询第一列的全部和第二列的部分精确匹配，某一列并范围匹配另外一列，可以查询第一列的全部和第二列的部分

select * from member where name = 'itbigbird' and code > 666666;

问：何谓只访问索引的查询？

答：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

select code,name from member where code=888888 and name = 'itbigbird' ;


注：member包含主键索引id，以及组合索引（code，name）

—

历史说：标点全是问号，幕后全是惊叹号

聊完了索引的匹配方式，我们再进一步看看具体查询语句的SQL执行计划，问答模式开启第五节

问：怎么查看执行计划呢？

答：一图解千愁，计划握在手

—

钟表说：可以回到起点，却已不是昨天

阐述完索引执行计划的具体解析，我们来唠叨下几个关键的核心概念，问答模式开启第六小节

问：何谓索引覆盖？

答：查询结果只包含主键列以及查询条件中所使用的其他索引列，再经过一次的索引查询后就可以返回结果，避免了回表查询

不是所有的引擎都支持覆盖索引，memory不支持覆盖索引；因为innodb是基于聚簇索引的，所以覆盖索引的效果极好

select name,code from member where name = 'XX'; --（name列已添加索引）覆盖索引，查询语句前添加explain进行查看extra列中显示为Using index

问：怎解索引下推？

答：在数据返回服务端前，执行引擎就完成了对所有索引列数据的匹配过滤，将过滤后的数据再返回服务端，减少了返回的数据条数，且减少了IO次数

问：怎看回表？

答：针对于普通索引，查询结果包含除索引外的其他列，需要经过普通索引查找到主键值，然后再根据主键二次查找才能返回结果

—

指南针说：思想稳定，不受外面诱惑

畅谈了几个核心概念，我们再聊聊日常的集中SQL查询类型的对应优化方式，废话不多说，直接上干货

问：谈一谈count统计查询如何优化？

答：MyISAM的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的（MyISAM引擎在文件中存储有表记录的行数）；

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统；

count(1)，count(*)，count(id)查询效果一样；但是count(1)、count(*)会查询所有列，count(id)会忽略id为空的列。

问：说一说关联查询如何优化？

答：确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序；

当表A和表B使用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，可以在A表的c列建立索引；

没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表（非驱动表）的相应列上创建索引；

确保任何的group by和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化。

问：讲一讲limit分页如何优化？

答：优化limit查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列

select film_id,description from film order by title limit 50,5explain select film_id,film.description from film inner join
(select film_id from film order by title limit 50,5) as lim using(film_id);

问：聊一聊union查询如何优化？

答：MySQL总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化；

除非确实需要服务器消除重复的行，否则一定要使用union all；因为没有all关键字，mysql会在查询的时候给临时表加上distinct关键字去重，这个操作的代价很高。

—

小龙虾说：大红之日，即是大悲之时

最后，索引设计的再好，打开方式不对，也会事倍功半。我们最后补充下索引失效实际场景和组合索引的正确打开方式

索引失效实际场景

不在索引列上做任何操作（计算，函数，（自动或者手动）类型转换），会导致索引失效而导致全表扫描。
隐式类型转换，字符串范围值也走索引 name>'itbigbird'走索引，name>123不走索引；相当于加了 CAST( id AS signed int) 函数
组合索引用or不会走索引；单列索引用or才会走索引。如果一个表只有组合索引字段没有其他字段，用or会走索引；
在MYSQL使用不等于（<,>,!=）的时候无法使用索引，会导致索引失效。
更新十分频繁，区分度不高的字段不宜建立索引。区分度在80%之上就可以建立索引
如果MySQL估计使用全表扫描要比使用索引快,则不使用索引
创建索引的列不允许为null，可能会得到不符合预期的结果。
单表索引建议控制在5个以内。太多的话占空间大，io多，速度慢。组合索引字段数不允许超过5个。

组合索引的正确打开方式

select * from member where a=1 and b=2 and c=3;使用了索引abcselect * from member where b=2 a=1 and and c=3;使用了索引abc，MySQL内部优化器会对sql语句进行优化重排序select * from member where a=1 and b>2 and c=3;使用了索引ab，范围匹配后的索引列失效select * from member where a=1 and c=3;使用了索引aselect * from member where b=2 and c=3;没有使用索引，不符合最左匹配select * from member where a=1 and b like '%2%' and c=3;使用了索引a，模糊匹配列不生效