1)连接器:主要负责跟客户端建立连接、获取权限、维持和管理连接。
2)查询缓存:优先在缓存中进行查询,如果查到了则直接返回,如果缓存中查询不到,在去数据库中查询。
MySQL缓存是默认关闭的,也就是说不推荐使用缓存,并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的:
3)解析器/分析器:分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析,最终得到抽象语法树,然后再使用预处理器对抽象语法树进行语义校验,判断抽象语法树中的表是否存在,如果存在的话,在接着判断select投影列字段是否在表中存在等。
4)优化器:主要将SQL经过词法解析、语法解析后得到的语法树,通过数据字典和统计信息的内容,再经过一系列运算 ,最终得出一个执行计划,包括选择使用哪个索引。
在分析是否走索引查询时,是通过进行动态数据采样统计分析出来;只要是统计分析出来的,那就可能会存在分析错误的情况,所以在SQL执行不走索引时,也要考虑到这方面的因素。
5)执行器:根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据,完成SQL的执行。
其子查询在Mysql5.5版本里,内部执行计划是这样:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会非常慢。
在MariaDB10/Mysql5.6版本里,采用join关联方式对其进行了优化,这条SQL语句会自动转换为:
但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE子查询无效,固生产环境应避免使用子查询
由于MySQL的优化器对于子查询的处理能力比较弱,所以不建议使用子查询,可以改写成Inner Join,之所以 join 连接效率更高,是因为 MySQL不需要在内存中创建临时表
另外,MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:
对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。
3、读取适当的记录LIMIT M,N,而不要读多余的记录
使用上述sql语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
对于 limit m, n 的分页查询,越往后面翻页(即m越大的情况下)SQL的耗时会越来越长,对于这种应该先取出主键id,然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后放弃前 offset 行,返回 N 行,那当 offset
特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。
优化的方法如下:可以取前一页的最大行数的id(将上次遍历到的最末尾的数据ID传给数据库,然后直接定位到该ID处,再往后面遍历数据),然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。sql可以采用如下的写法:
如果我们对结果没有排序的要求,就尽量少用排序;
如果排序字段没有用到索引,也尽量少用排序;
另外,分组统计查询时可以禁止其默认排序
默认情况下,Mysql会对所有的GROUP BT col1,col2…的字段进行排序,也就是说上述会对 goods_id进行排序,如果想要避免排序结果的消耗,可以指定ORDER BY NULL禁止排序:
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。
当然,union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。
以上两个语句都无法用到索引
8、只返回必要的列,用具体的字段列表代替 select * 语句
SELECT * 会增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前者也需要经常更新。所以要求直接在select后面接上字段名。
MySQL数据库是按照行的方式存储,而数据存取操作都是以一个页大小进行IO操作的,每个IO单元中存储了多行,每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。
但是如果查询的字段都在索引中,也就是覆盖索引,那么可以直接从索引中获取对应的内容直接返回,不需要进行回表,减少IO操作。除此之外,当存在 order by 操作的时候,select 子句中的字段多少会在很大程度上影响到我们的排序效率。
区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
另外,in查询在某些情况下有可能会查询返回错误的结果,因此,通常是建议在确定且有限的集合时,可以使用in。如 IN (0,1,2)。
如果对group by语句的结果没有排序要求,要在语句后面加 order by null(group 默认会排序);
如果group by需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大tmp_table_size参数,来避免用到磁盘临时表;
使用where子句替换Having子句:避免使用having子句,having只会在检索出所有记录之后才会对结果集进行过滤,这个处理需要排序分组,如果能通过where子句提前过滤查询的数目,就可以减少这方面的开销。
若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
当我们执行两个表的Join的时候,就会有一个比较的过程,逐条比较两个表的语句是比较慢的,因此可以把两个表中数据依次读进一个内存块中,在Mysql中执行:
可以看到join在内存中的缓存池大小,其大小将会影响join语句的性能。在执行join的时候,数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer。
什么是驱动表,什么是被驱动表,这两个概念在查询中有时容易让人搞混,有下面几种情况,大家需要了解。
1)当连接查询没有where条件时
-
left join 前面的表是驱动表,后面的表是被驱动表
-
right join 后面的表是驱动表,前面的表是被驱动表
-
straight_join(≈join) 直接选择左边的表作为驱动表(语义上与join类似,但去除了join自动选择小表作为驱动表的特性)
2)当连接查询有where条件时,带where条件的表是驱动表,否则是被驱动表
假设有表如右边:t1与t2表完全一样,a字段有索引,b无索引,t1有100条数据,t2有1000条数据
若被驱动表无索引,那么其执行算法为:Block Nested-Loop Join(BLJ)(Block 块,每次都会取一块数据到内存以减少I/O的开销),示例如下:
③另外还有一种算法为Simple Nested-Loop Join(SLJ),其逻辑为:顺序取出驱动表中的每一行数据,到被驱动表去做全表扫描匹配,匹配成功则作为结果集的一部分返回。
另外,Innodb会为每个数据表分配一个存储在磁盘的 表名.ibd 文件,若关联的表过多,将会导致查询的时候磁盘的磁头移动次数过多,从而影响性能
所以实践中,尽可能减少Join语句中的NestedLoop的循环次数:“永远用小结果集驱动大的结果集”
性能优化,left join 是由左边决定的,左边一定都有,所以右边是我们的关键点,建立索引要建在右边。当然如果索引是在左边的,我们可以考虑使用右连接,如下:
Tips:Join左连接在右边建立索引;组合索引则尽量将数据量大的放在左边,在左边建立索引。
如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进,判断查询的字段以哪种形式组合能使得查询更快,所有比如创建的是(a,b)索引,查询的是(b,a),查询优化器会修改成(a,b)后使用索引查询。
1)计算:对索引进行表达式计算会导致索引失效,如 where id + 1 = 10,可以转换成 where id = 10 -1,这样就可以走索引
从 MySQL 8.0 开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,所以就可以通过扫描索引来查询数据。
(自动/手动)类型转换
MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。以上这条语句相当于:select * from user where id = CAST(“1” AS signed int),索引字段并没有用任何函数,CAST 函数是用在了输入参数,因此是可以走索引扫描的。
3)存储引擎不能使用索引中范围条件右边的列。
4)尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))
你可以想象一下,对于一棵B+树,根节点是40,如果你的条件是等于20,就去左面查,你的条件等于50,就去右面查,但是你的条件是不等于66,索引应该咋办?还不是遍历一遍才知道。
6)is null, is not null 也无法使用索引,在实际中尽量不要使用null(避免在 where 子句中对字段进行 null 值判断) 不过在mysql的高版本已经做了优化,允许使用索引
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
7)like 以通配符开头(%abc..)时,mysql索引失效会变成全表扫描的操作。
对于一棵B+树索引来讲,如果根节点是字符def,假如查询条件的通配符在后面,例如abc%,则其知道应该搜索左子树,假如传入为efg%,则应该搜索右子树,如果通配符在前面%abc,则数据库不知道应该走哪一面,就都扫描一遍了。
8)少用or,在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。
因为 OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描。
必须要or前后的字段都有索引,查询才能使用上索引(分别使用,最后合并结果type = index_merge)
9)在组合/联合索引中,将有区分度的索引放在前面
如果没有区分度,例如用性别,相当于把整个大表分成两部分,查找数据还是需要遍历半个表才能找到,使得索引失去了意义。
短索引不仅可以提高查询性能而且可以节省磁盘空间和I/O操作,减少索引文件的维护开销,但缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引。
比如有一个varchar(255)的列,如果该列在前10个或20个字符内,可以做到既使前缀索引的区分度接近全列索引,那么就不要对整个列进行索引。为了减少key_len,可以考虑创建前缀索引,即指定一个前缀长度,可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。
当 type=index 时,索引物理文件全扫,速度非常慢。