MySQL性能优化

MySQL存储引擎

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm 文件，另外还有.MYD和.MYI 文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI 文件中。

在物理存储方面，Innodb 存储引擎也和 MyISAM 不太一样，虽然也有.frm 文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。

什么是索引?

我们知道，数据库查询是数据库的最主要功能之一，例如下面的SQL语句：

SELECT * FROM my_table WHERE col2 = '77'

可以从表“my_table”中获得“col2”为“77”的数据记录。

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引类型分类

Data Structure Visualization

B+TREE : 使用平衡树实现索引，是mysql中使用最多的索引类型；在innodb中，存在两种索引类型，第一种是主键索引（primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引内容中保存的是指向主键索引的引用；所以在使用innodb的时候，要尽量的使用主键索引，速度非常快；

把索引的值做hash运算，并存放到hash表中，使用较少，一般是memory引擎使用；因为使用hash表存储，按照常理，hash的性能比B-TREE效率高很多。

hash索引的缺点：

1，hash索引只能适用于精确的值比较，=，in，或者<>, 因为只需要经过一次算法即可找到相应的键值；

2，无法使用索引排序,因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

3，组合hash索引无法使用部分索引,以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

4，如果大量索引hash值相同，性能较低；

 

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，我们主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

参考资料:mysql InnoDB引擎支持hash索引吗_Dreamer 科技-CSDN博客

MySQL 最经常使用存储引擎 InnoDB 和 MyISAM 都不支持 Hash 索引，它们默认的索引都是 B-Tree。可是假设你在创建索引的时候定义其类型为 Hash，MySQL 并不会报错，并且你通过 SHOW CREATE TABLE 查看该索引也是 Hash，仅仅只是该索引实际上还是 B-Tree。

虽然常见存储引擎并不支持 Hash 索引，但 InnoDB 有另一种实现方法：自适应哈希索引。InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

这里设表一共有三列，假设我们以Col1为主键，图解一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如图所示

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

示意图中可以看到叶子节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 

什么是聚集索引?

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。

通俗讲:

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应

索引的利弊

1，索引的好处：

1，提高表数据的检索效率；

2，如果排序的列是索引列，大大降低排序成本；

3，在分组操作中如果分组条件是索引列，也会提高效率；

2，索引的问题：索引需要额外的维护成本；

如何创建索引

1，较频繁的作为查询条件的字段应该创建索引；

2，唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

3，更新非常频繁的字段不适合创建索引；

4，不会出现在WHERE 子句中的字段不该创建索引；

主索引和辅助索引

单值索引和组合索引

1，单值索引即一列作为索引；

2，组合索引即多列创建为一个索引；

 

性能分析命令----Explain命令

1.UNION

当通过union来连接多个查询结果时,第二个之后的select及其select_type为UNION.

2.DEPENDENT UNION 与 DEPENDENT SUBQUERY

当union作为子查询时,其中第二个union的select_type就是DEPENDENT UNION.第一个子查询的select_type则是DEPENDENT SUBQUERY.

3.SUBQUERY

子查询中的第一个select其select_type为SUBQUERY.

4.DERVIED

当子查询时from子句时,其select_type为DERIVED.

type的说明

1.system,const

见上面4.DERIVED的例子.其中第一行的type就是system,第二行是const,这两种连接类型是最快的.

2.eq_ref

两张表的主键进行关联的时候,必须使用主键进行关联的时候,mcishib表的连接类型是eq_ref,这是的连接类型.

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配.常见于主键或唯一索引扫描.

 

3.ref

第二张表在查询的时候使用到了索引的情况下,连接类型是ref

使用非唯一索引或唯一索引前缀进行的查找

最左原则与相关优化

6//三个字段是联合组建 三个才保证唯一 拆开查询结果不唯一 从左往右开始匹配但是和实际书写顺序没关系 联合组建 匹配只要有相应的主键 书写顺序随意 只要都有MySQL内部可以自动匹配 有最左侧索引都可以匹配 缺了不行 可以缺少2,3索引 2索引缺失只能执行1索引进行回表再按照3条件过滤 3索引没用上只用了1 建索引要考虑使用频率和覆盖率 最左原则频率最高的优先覆盖接着尽可能覆盖多一些语句.
explain select * from employees.titles where emp_no='10001' and title='Senior Engineer' and from_date='1986-06-24'
 
//后缀查询like会导致索引失效 后缀查询%无法筛选 没法比较   
explain select * from employees.titles where emp_no='10001' and title like '%Senior'
    
//like在前缀查询的情况下不会失效 比较拿S跟元素中的第一个字母比较小的左边大的右边相同比较下一个   
explain select * from employees.titles where emp_no='10001' and title like 'Senior%'
   
//从左开始取字段前6个内容等于Senior 用了函数/做了算数表达式最终结果是否还和原来值保持原来一样的树状结构MySQL不知道 在字段上添加函数,会导致索引失效 emp_no - 1 = '10001' 算数表达式也会导致索引失效  如果有时候需求要使用值运算查询 建议添加新的字段等于表达式之后运算的结果 再对这个字段添加索引 这样就可以用上索引.
explain select * from employees.titles where emp_no='10001' and left(title,6) ='Senior'
​
//一旦在组合索引上加了范围查询后面字段索引会自动失效    
explain select * from employees.titles where emp_no<'10001' and title='Senior Engineer' and from_date between '1986-06-24' and '1986-12-31' 
​
//虽然between也属于范围查询不同于<> 不会导致索引失效 MySQL会把between转成等值查询 emp_no in(10001~10010)
explain select * from employees.titles where emp_no between '10001' and '10010' and title='Senior Engineer' and from_date between '1986-06-24' and '1986-12-31'
​
//Or 索引失效 or后面条件不能使用索引 用不上索引就走全表扫描 2走全表扫描会顺便遍历1 所以用不到索引 先走索引还是要走全表扫描 所以联合索引用or就走全表扫描了 没有意义走索引 索引就失效了
explain select * from employees.titles where emp_no='10001' and title='Senior Engineer'   

索引选择性与前缀索引

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

select count(distinct(title))/count(*) as Selectivity from employees.titles;

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

总结:

1.数据条数<2000

2.索引选择性太低,既重复值很多 越接近1越好重复性越低

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

 如果[魔法门按名字搜索员工,这样显然效率很低,因此我们可以考虑建索引.有种选择,建<first_name>或<first_name,last_name>,看下两个索引的选择性:

 

<first_name>显然选择性太低,<fir_name,last_name>选择性好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑first_name和last_name的前几个字符建立索引,例如<first_name,left(last_name,3)>,看看其选择性:  

 

性能的提升是显著的,查询速度提高了285倍之多.

前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于order by 和 group by 操作,也不能用于 covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身).

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

页分裂和页合并问题

主键自增情况 : 先申请一块页空间从左自右增大 自增每次右边插入最右侧即可 当最后一个元素空间满了之后再加元素申请一块新的页空间用双向链表连接起来 用完一页申请一页双向链表连接.

如果主键随机情况 : 不是一致往最右侧插入,而是随机插入会导致页分裂,分裂的页没有存满元素会导致空间利用率很低.当主键不是连续递增的值每次随机插入都有可能导致页分裂情况,频繁的页分裂都会导致性能变低,空间利用率也低,还会影响全表扫描性能,本来扫描两个页就行,但是分裂导致页没存满可能导致扫描更多页,磁盘io变多.

JOIN底层算法

 

关联字段有索引情况:

扫描t1表关联t2表的字段是有索引的,r1值从树上寻找到后再回表找到对应记录.前提是table2表对应关联字段有索引.

索引嵌套循环连接是基于索引进行连接的算法,索引是基于内层表的,通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表记录进行比较,从而利用索引的查询减少了对内层表的匹配次数,优势极大的提升了join的性能.

原来的匹配次数 = 外层表行数 * 内层表行数

优化后的匹配次数 = 外层表的行数 * 内层表索引树的高度

使用场景 : 只有内层表join的列有索引时,才能用到Index Nested - LoopJion进行连接.

由于用到索引,如果索引是辅助索引而且返回数据还包括内层表的其他数据,则会回内层表查询数据,多了一些IO操作.

这种算法是将外层循环的行/结果集存入join buffer,内层循环的每一行数据与整个buffer记录做比较,可以减少内层循环的扫描次数.

举个简单的例子 : 外层循环结果集有1000行数据,使用NLJ算法需要扫描内层表1000次,但是如果使用BNL算法,则先取出外层表结果集的100行放到join buffer,然后用内层表的每一行数据去和这100行结果集比较,可以一次性与100行数据进行比较,这样内层表其实只需要循环1000/100=10次,减少了9/10.

小结果集驱动大结果集

性能优化方案:

1.增大join buffer 减少对被驱动表扫描次数

2.单条数据越小 join buffer 存储越多 扫描被驱动表次数越少 要什么字段查什么字段 不要select *

join inner join where 都是小结果集驱动大结果集 左右两边条件都满足才能出现在结果集之中

left join 已经声明了左边是驱动表 右边是被驱动表 如果条件不满足 依然展示左边表数据 右侧为null 业务需求左边表数据是完整的

straight join 左右两边条件都满足才会出现在结果集中 可以指定驱动表和被驱动表

Order By性能优化

name字段没索引

顺序读 : 连续访问数据 按顺序读取 速度比较快

乱序读 ; 随机读 磁盘硬件中磁头要转到另一个扇区再读 磁头来回转动读不同区数据 性能会差一些.

双路排序当查询的字段太多了(select *) sort buffer会优化 只存排序列和对应主键,以便存储更多.读完在内存中进行排序,要所有字段 还要根据id进行回表 因为顺序乱了执行乱序读.

如果读取数据表 放到sort buffer中满了 快速排出一次 存到临时表中 会产生很多临时表 单个临时文件中是排完序的 再对文件进行读取归并排序 之后返回列即可.

具体使用哪种算法 根据内部配置max_length_for_sort_date 如果返回字段长度超过max 字段太多了 进行双路排序 太多字段没法一次性加载进sort buffer中 每次加的数据太少了会产生非常多临时文件,所以直接走双路 排好序之后再经过乱序读查出具体内容出来. max默认值1024

所以尽量不要写select * 会走双路执行两次磁盘IO 性能变差.

name有索引 在索引树上本身是有序的 所以不需要进行单双路排序 order by在树上拿到的结果本来就是有序的 从左到右递增

优化方案：

1，加大max_length_for_sort_data .

2，去掉不必要的返回字段.

3，增大sort_buffer_size 参数,减少临时文件产生.

美团优化案例

MySQL索引原理及慢查询优化 - 美团技术团队