Mysql性能优化

最常见的手段是使用索引：

1.索引优势劣势

优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间

的。

2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为

更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所

带来的键值变化后的索引信息。

2.索引的实现（B+树）

2.1 BTREE 结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

树中每个节点最多包含m个孩子。

除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。

若根节点不是叶子节点，则至少有两个孩子。

所有的叶子节点都在同一层。

每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

2.2 B+TREE结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

3). 所有的非叶子节点都可以看作是key的索引部分。

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

2.3 MySQL中的 B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指

针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图:

3.mysql两种常用引擎的介绍

存储引擎基于表 不同的表可以按需求使用存储引擎

3.1 InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。

但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点 ：

事务控制

一个连接中新增操作 另一个连接查询不到证明支持事务

外键约束

MySQL支持外键的存储引擎只有InnoDB ， 在创建外键的时候， 要求父表必须有对应的索引 ， 子表在创建外键的

时候， 也会自动的创建对应的索引。

3.2 MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT

为主的应用基本上都可以使用这个引擎来创建表

3.3存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选

择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高

的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，

那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还

可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，

InnoDB是最合适的选择。

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发

性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。

MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数

据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结

果。

MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优

点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善

MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

4.表设计

4.1 不创建外键

1.数据库需要维护外键的内部管理；

2.外键等于把数据的一致性事务实现，全部交给数据库服务器完成；

3.有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；

4.外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；

4.2关联数据表合理建表原则

这里的外键不是说要你真的加外键,只是说创建与主键相同的数据列

4.3尽量用占内存少的数据类型

mysql一页是 16K 这16k里你的数据越多,查询时IO操作耗时越少

4.4各数据类型介绍

 

 

 

 

 

 

 

5.避免索引失效

1). 全值匹配 ，对索引中所有列都指定具体值。

改情况下，索引生效，执行效率高

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。

3). 范围查询右边的列，不能使用索引 。

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引

5). 字符串不加单引号，造成索引失效。4). 不要在索引列上进行运算操作， 索引将失效

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

8). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

9). 如果MySQL进行路径分析评估使用索引比全表更慢，则不使用索引。

10). is NULL ， is NOT NULL 有时索引失效。是因为上一点

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

6.SQL优化

6.1优化 select 语句

明知只有一条查询结果，那请使用 “LIMIT 1” 

这样就会在检索出第一条符合条件的数据后不继续向下检索，效率会快很多

6.2优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户

端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

6.3优化排序

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件

和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是

降序。否则肯定需要额外的操作，这样就会出现FileSort。

6.4优化group by*语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分

组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在

GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把

这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL

操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接

（JOIN）替代

6.5建议使用 union 替换 or

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

6.6优化分页查询

第一步：利用覆盖索引优化

select * from user limit 10000,100;

select id from user limit 10000,100;

第二步：利用子查询优化

select * from user limit 10000,100;

select * from user where id>= (select id from user limit 10000,1) limit 100;

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

6.7使用SQL提示

1.USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他

可用的索引。

2.IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

3.FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

7.读写分离

7.1Mysql复制概述

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行

（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

7.2 复制原理

从上层来看，复制分成三步：

Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。

主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log 。

slave重做中继日志中的事件，将改变反映它自己的数据。

7.3 复制优势

MySQL 复制的有点主要包含以下三个方面：

主库出现问题，可以快速切换到从库提供服务。

可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。

可以在从库中执行备份，以避免备份期间影响主库的服务。

8.应用优化

8.1缓存

可以在业务系统中使用redis来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率。

8.2 非关系数据库

也可以考虑将非核心（重要）数据，存在 MongoDB 中，这样可以提高插入以及查询的效率。

9.分库分表

9.1垂直拆分

 

9.2 水平拆分

10.重启数据库或者服务器

如果你的数据库 为你不停息战斗了 几年 那么 可怜可怜它 趁夜深人静的时候 重启就完事了 怕有人看到 挂个系统维护的牌子

服务器注意是热启动，不是按开关，而且数据备份啥的要记得。怕启动时机器直接gg
对服务器的保养有必定的作用，开释内存，缓解CPU压力。服务器运转时刻长，
会形成许多冗余的DLL程序，致使体系运转速度较慢。体系重启会使电脑康复到默许加载状况，
也即是说在还未重启时的许多应用程序进程都驻留在内存中，会使电脑变慢，重启后它们就没了

11.机器的性能
示例问题描述

环境 MySQL5.7 CPU:4核 内存:16384 M 最大连接数:8000 最大IOPS:16800 空间：已使用 118.46G ( 共 500G )

table_open_cache:3000 table_open_cache_instances:16

Open_tables:2992 Opened_tables:27622

innodb_buffer_pool_size 3/4 调整到5/8， 重启后 内存从80%下降到70%，一两天后又继续飙升会了80%以上

参考答案

如果你关注的是为什么系统启动一段时间后，增加了10%（1.6G）的内存消耗，这个似乎并不是关键。
如果是的话，那就分析一下其他哪些进程占用了内存 top 。
​
否则的话就是关注MySQL。buffer pool已经用掉了62.5%（10G）的内存，剩下的内存本来也不多了。
如果对实际性能影响不大，这个值可以调低。

在专用数据库服务器上，可以将缓冲池大小innodb_buffer_pool_size设置为计算机物理内存大小的80%

# 计算是否应该添加内存
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size
当结果 < 95% 则减少 innodb_buffer_pool_size， 可适当较少，当然独享业务机器多了也没啥问题

关注一下这些跟内存有关的参数：
show variables like ‘innodb_buffer_pool_size’;
show variables like ‘table_open_cache’;
show variables like ‘key_buffer_size’;
show variables like ‘query_cache_size’;
show variables like ‘tmp_table_size’;
show variables like ‘innodb_buffer_pool_size’;
show variables like ‘innodb_log_buffer_size’;
show variables like ‘max_connections’;
​
还有服务状态：
show full processlist;
show status;
show engine innodb status;
​
你的max_connections是否设置过大？这个本来指的是数据库的并发连接数，你的数据库是否有连接池？是否需要这么大的并发连接？
​
如果你去问阿里云客服，很有可能会让你升级内存……