MySQL性能优化

MySQL性能优化

影响MySQL性能的因素

要知道如何优化MySQL得先知道影响MySQL性能的因素有哪些

• 商业需求对性能的影响
• 系统架构及实现对性能的影响
  • 过度依赖sql语句。能在应用层（代码）优化就不要在MySQL才优化
  • 使用cache不合理
  • 在mysql中存放不合理的数据。该放在其他数据库就不要放在mysql中，如日志记录应放在mongodb中
• 硬件环境对系统性能的影响
• Query查询语句对系统性能的影响。包括：避免全表查询、合理使用索引、减少无效数据的查询
• 合理的需求和架构及业务。需求发起者可能不了解mysql性能会有什么影响，在确定需求时应该衡量这点，架构的选择和业务流程的代码也是很重要的

需求和架构以及业务占55%

sql优化占30%

数据库自身优化占15%

为什么要对MySQL优化？为了优化MySQL，给老板省钱的最好办法就是优化SQL

优化成本：硬件>系统配置>数据库表结构>SQL及索引。

优化效果：硬件<系统配置<数据库表结构<SQL及索引。

Query语句的执行流程

sql请求到MySQL，首先通过连接管理模块进行权限检查，接着到连接进程模块申请一个线程，然后由命令分发处理器会将命令记录在日志模块中记录命令日志、如果开启二级缓存了也会将命令分发至二级缓存中，接着分发到命令解析器解析命令，最后由其执行Query语句

MyISAM和InnoDB的区别

特点	MyISAM	InnoDB
事务	不支持	支持（ACID）
锁	表锁	表锁、行锁
磁盘文件	.frm：表结构/表定义 .MYD：表数据 .MYI：表索引	.frm：表结构/表定义 .idb：表数据、表索引
存储限制	无限制	64TB
外键	不支持	支持

索引

索引就像字典里的目录，能帮助我们更快地查询数据。用官方话来说就是：高效获取数据的数据结构。

索引独立于原数据，每个索引都将是额外的开销。

索引的数据结构

索引支持的数据结构有Hash和B+Tree，但是MySQL中不提供Hash给用户使用，即便是提供选择，都将默认选择使用B+Tree作为索引的数据结构，Hash是MySQL用于优化其他方面的性能的

Hash

Hash结构适合命中性地搜索，不适合范围式的搜索，而我们业务几乎不会碰到存在selectOne而没有selectList的情况，因此MySQL只提供B+Tree作为数据结构供选择。另一方面，Hash是无法进行排序的。

B-Tree

先来了解B+Tree的优化前身B-Tree（读作B树，不读作B减树）

从图中可以看出，B树的每个节点都存储了元素数据（data）及元素值（17、35），而非叶子节点还存储了元素导航指针（Pn）。所谓n阶树就有n个元素值，n+1个元素导航指针

元素导航指针：如P1、P2、P3分别存储的是指向<17、<35且>17、>35的元素的磁盘位置

树的搜索过程：

每个节点都是使用数组存储数据，因此每个节点内的元素值遍历都是使用二分法查找。当Query发起查询的时候，第一次IO遍历第一层即根节点的数据，节点内使用二分法查找，如果找到数据则返回，否则根据条件继续向下IO操作

那B树有什么缺点导致出现优化后的B+树呢？

每个索引树（无论B+树或B树）的IO次数都是O(log2 n)次，因此树的高度决定了IO操作的次数，所以我们要尽量保证树的高度越矮越好。而一个节点在磁盘中就是一页数据，也即一次IO操作。而一个节点默认存储量是16384bit即16kb，如果我们将每个节点最合理使用的话，也即尽可能多的存储元素导航指针是不是可以降低树的高度了？

B+Tree

B+树将所有元素数据都存储到了叶子节点上，叶子节点是双向链表结构，存储了指向邻磁盘的元素导航指针，这也是优化的一点，便于范围查询。在这里插入图片描述

根节点存储的只有元素值，并且MySQL都将其存储在了内存中，方便查询

普通节点存储了父节点的元素值和指向子节点的元素导航指针

来用数据说明B+Tree的魅力

每个元素值占8bit、元素导航指针占6bit，那么一个节点大概能存储16384/14≈1200个元素

假设树有三层，那第一层就有1200个元素，第二层有1200*1200≈144w个元素

假设第三层每个元素存储的数据平均撑死也就1kb，那第三层就有1440000*16≈2kw条数据，也就是说，仅三层，2kw条数据最多只要3次IO操作就能得到结果

B-Tree和B+Tree的区别

• B-Tree每个元素值都对应着一条数据，B+Tree将所有数据存储在叶子结点，其他节点腾出空间主要存储元素值
• B-Tree不适合范围查找数据，而B+Tree的叶子节点存储了邻节点的指针，方便范围查找

索引的类型

主索引（一级索引、主键索引）

主索引是MySQL默认设置的。如果表中有主键则默认选其为主索引，如果没有主键则默认选择其中一个非空唯一列作为主索引，如果条件都不成立则默认创建一个独立于列的主索引

因为MyISAM和InnoDB存储数据和索引的文件类型不同，因此它们的主索引有些区别

MyISAM：其索引的叶子节点存储的是数据文件.MYD的地址指针，因此它需要比InnoDB多一步，再根据地址指针到.MYD文件中找到对应的元素数据

InnoDB：索引和数据同文件，找到元素数据即是元素数据

聚簇索引和非聚簇索引

聚簇索引就是索引与元素数据存储在一起，反之就是非聚簇索引。InnoDB的主索引使用的是聚簇索引（但不能说聚簇索引就是主索引），MyISAM的主索引使用的是非聚簇索引

严格来说聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于其实现方式。

辅助索引（二级索引）

辅助索引是我们手动设置的。其叶子节点存储的是其所在列的值和主索引的值。

辅助允许对应的列为空，辅助索引B+Tree会额外维护一个个元素为null的链表（维护需要额外开销，不建议将辅助索引用在允许为空或者存在空值的列中），该链表存在于叶子节点的头节点中，Query的时候，查找空值的数据都将额外在该空值链表中从头遍历

回表和索引覆盖

由于辅助索引存储的数据只有列值和主索引值，因此使用辅助索引的时候都会查询到主索引值再去主索引中Query找到元素数据，这个过程就称作回表（这是InnoDB的情况，MyISAM回表之后还要再根据地址指针跨文件获取元素数据）

为解决回表问题，从回表的特性就可以得出结论——使用辅助索引的时候避免非索引的查找。就比如我们只select两个列，主索引和辅助索引所在的列，但是这是不可能滴，因为业务没有这么纯粹，因此我们可以使用联合索引解决这个问题

单值索引

单值索引就是一个列，非单值索引即联合索引

联合（复合、组合）索引

联合索引即选择多列作为索引。用于当Query操作时的列大概率甚至一直会作为同时存在的条件进行查询。前面说到，使用联合索引就是实现索引覆盖，避免回表影响性能。

最左原则

这是使用联合索引必须考虑的问题。因为复合索引的排序规则由最左边即第一个索引决定的（后面其他索引无序），因此如果where后面没有第一个索引的话，这个联合索引将失效，具体看下面的例子。

以index(a,b,c)复合索引为例：

语句	索引是否生效
where a = 1	是，字段 a 索引生效
where a = 1 and b = 2	是，字段 a 和 b 索引生效
where a = 1 and b = 2 and c = 3	是，全部生效
where b = 2 or c = 3	否
where a = 1 or d = 3	否
where a = 1 and b > 2 and c = 3	字段 a生效，字段 b 和 c 失效
where a = 1 and b like ‘xxx%’ and c = 3	是，全部生效
where a = 1 and b like %xxx’ and c = 3	字段 a 生效，字段 b 和 c 失效
where a = 1 and b like %xxx%’ and c = 3	字段 a 生效，字段 b 和 c 失效
where a + 1 = 2 and b = 3	字段 a 失效，字段 b生效
where a between 1 and 100	是，字段 a 索引生效
where a in (1,100)	是，字段 a 索引生效

从上表中可以得出“导致索引失效的原因”有：

• 复合索引不满足最左原则
• 使用函数将失效（包括+、-、*、/，建议将函数运算后的列作为一个新列然后建立索引）
• 存在左模糊查询（%xxx[%]后缀匹配）将失效
• <、>、%模糊查询会导致其后面的索引无论什么原因都失效
• mysql会将between转成in语法，in语法是等值匹配，区别于模糊匹配

索引使用建议

由于索引需要存储在磁盘空间，DML操作都要维护索引树，虽然DQL性能提高，但是业务是不可能仅操作DQL的，因此建议

• 一张表建议使用索引个数5个（一张表最多可以建立16个索引）

• 非频繁查询条件的字段不建议建立索引

• 索引选择性不高的列不适合作为索引（索引的选择性是指，不重复的索引值和数据表的记录总数（T）的比值，范围从1/T 到 1 之间。如性别列，这种一只手都能算出各种值在表中所占比）

• 更新频繁的字段不适合建立索引

• 不会出现在where语句中的字段不适用建立索引

• 索引所在字段不应太长（每个节点存储的元素少，会导致索引树高度变高）

• 能使用联合索引就尽量不用多个单值辅助索引

• 能使用一个联合索引覆盖就不使用多个联合索引

• 使用非业务的自增列作为索引

补充：

索引选择性。由于索引选择性低，因此索引树存在很多重复的元素值，这会导致叶子节点的链表中相同元素值集中分布在某块区域，辅助索引的索引树在进行范围性搜索的时候得到的都是主索引id值，而这些id值是无序的（根据辅助索引列排序的），回表之后就不能像辅助索引那样范围性搜索了，而是变成一条条地无序搜索。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当**前缀长度合适（注意一定要兼顾好列长度）**时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

以city_demo表为例，表中存在city的列，如果全city的索引选择性太低，可以尝试使用部分前缀

#计算长度
-- 查询重复次数最多的1000条完整城市名称及其数量
SELECT COUNT(*) cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 1000;
-- 查询重复次数最多的1000条城市名称(前3个字符)及其数量,可以发现:前3个字符的相同数量过大,不适合做前缀索引
SELECT COUNT(*) cnt, LEFT(city,3) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 1000;
-- 查询重复次数最多的1000条城市名称(前7个字符)及其数量,可以发现:前7个字符的相同数量和完整城市名称很相近了,可以考虑作为做前缀索引
SELECT COUNT(*) cnt, LEFT(city,7) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 1000;

为什么强烈建议使用非业务的自增列作为索引：

业务列通常都是varchar类型的，而一般索引的大小都得控制在8bit也就是Integer及以下，太多长的话每个节点存储的元素少，会导致索引树高度变高，IO频率太高。

自增列作为索引建立索引树的时候，每次新增节点时需要在磁盘空间申请页空间作为新节点，当节点空间溢出就会再申请页空间作为新节点，这是一个从无到有的过程。而使用非自增列作为索引建立索引树时，因为插入元素不是根据递增排序的，也就是无序插入，这样出现新节点的时候，由于索引树的特性，可能此时新旧节点中的元素都没有占满，这就导致了节点空间中存在浪费的情况，浪费的多创建的节点也会更多，CPU性能会下降，由于全表扫描也会由于节点分裂太多而导致IO次数变多。而自增索引树除了最后一个节点都是满内容的，其空间利用率是最高的，因此节点数也是最少的，树的高度相比之下最低。

用自定义Integer类型的自增id类最好。前面虽然没有说非得顺序紧凑递增，但是从上面也可以分析出，同等范围，id散列越好越能保证Integer的可用性

explain执行计划

使用explain语法可以查看Query的执行情况从而有根据性地进行sql调优

#用法
explain select * from table where id = 3

其结果生成一个表，其中的列表示的意思如下表所示

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 两种情况 id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（即系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行 注意：一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	表示MySQL实际决定使用的字段(索引)。如果没有选择索引则是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	表示索引命中的列或者常量Const，比如有些联表查询的时候驱动表会使用on中的列，被驱动表就会使用Const，通俗地讲就是where后的条件筛选或on中，对应的实际决定的key中的取值，为NULL可能表示对索引key使用了between或on
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

举个栗子

补充：还可以使用show profiles语法查看本次查询消耗的时间、IO次数等详情。

#用法
explain select * from table where id = 3
show profiles

举个栗子

Query语句优化

Join算法

Simple Nested-Loop Join算法，也叫SNL算法

普通join语法就是使用SNL算法，联表查询需要经过count(table1)*count(table2)次行联匹配，也就是在table1全表查询的每一条数据再到table2全表查询匹配

Index Nested-Loop Join算法，也叫INL算法

其实就是往被匹配的内层表/驱动表的关联的（join on后面的消除笛卡尔积的条件）列添加索引，这样主动匹配的外层表/被驱动表查询之后，每一个条件到table2都将使用索引进行log2 n 次遍历。因此联表查询只需要经过count(table1)*(log2 count(table2))次行联匹在这里插入图片描述
配

Block Nested-Loop Join算法，也叫BNL算法

大致思想就是建立一个缓存区join buffer，一次从驱动表中取多条记录，然后扫描被驱动表，被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配，如果匹配则连接并加入结果集。缓冲区越大，驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。

通过join buffer缓存，只需要经过count(table1) + (count(table1) / size(join buffer)) * count(table2) 次行联匹配，并且缓存的匹配操作比IO匹配操作更快

注意：join的所有算法要保证小结果集驱动大结果，其效率比大结果集驱动小结果集更快，所谓大结果集和小结果集就是count(table)的大小。

做个比较看一下就明白了：假设table1有50条数据，table2有100条数据，join buffer固定大小为20。按照BNL算法，小结果集驱动大结果集的匹配次数=50+50÷20x100=300，大结果集驱动小结果集的匹配次数=100+100÷20x50=350，当join buffer的容量越大时差距越明显。

另外，MySQL中不会因为join的先后顺序决定哪个是驱动表哪个是被驱动表，它会根据结果数而决定，少的作为驱动表，多的作为被驱动表。

另外，不仅是join，where和inner join（全内连接查询）都是跟join一样的特点。

Order by的单双路排序

当对sql进行order by排序的时候，需要尽可能的使用索引排序，如果无法使用索引排序的话，mysql就会使用文件排序。

文件排序出现的几种情况：

• order by 字段不是索引字段

• order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from employee order by age asc;

• order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select weight, height from employee order by weight desc, height asc;

• order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如建立索引index(weight, height )，sql：select weight, height from employee order by height asc, weight asc;

文件排序（FileSort）分为两种：

• 双路排序（又叫回表排序模式）：先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段，即一次顺序读和一次回表乱序读；
• 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序随即返回结果集，仅一次顺序读；

显然单路排序比双路排序高效，但是什么原因会造成双路排序呢？

MySQL根据max_length_for_sort_data变量来确定使用哪种排序，其默认大小1024bit（可以在MySQL的my.ini文件中的[mysqlId]节点下更改max_length_for_sort_data的默认值），如果返回的列的总长度大于max_length_for_sort_data默认值，就会使用双路排序，这也印证了为什么要避免使用select *（甚至有些公司明令禁止使用select *）


Group by优化理念跟Order by是相同的，不再赘述