MySQL从5.6到8.0及性能优化

一、选择mysql8.0.16的原因

项目一直采用的是mysql5.5版本，由于是单数据库配置，无法分担数据压力，已经达到了mysql5.5的性能瓶颈，在无法使用两个数据库分担压力的实际背景下，决定采用性能更强大的mysql8.0.16版本（当时最新）。

性能得到了极大的提升

由于MySQL8.0.16的默认InnoDB引擎，所以这里采用InnoDB引擎进行测试。特别是当线程数增加时，MySQL8.0的表现会明显优于5.7版本。 特别是在高负载服务器上。MySQL 5.7和MySQL 8.0在InnoDB行操作上的区别，确实存在有很大的不同，特别是当线程数增加的时候。MySQL 8.0表明，无论工作负载如何，它都能高效地运行 。通过下图可以清晰的看到，不论是整体的I/O性能还是数据库常见的增删改查场景下，MySQL8.0.16都完爆5.*系列的MySQL。

大部分支持向下兼容

考虑MySQL高性能的同时，也要考虑高版本的MySQL具有哪些新特性，与之前的老版本有哪些不同，通过优化操作，能否兼容老数据和sql语句的语法，避免项目的大规模改动。所以需要了解MySQL与之前的相同与不同。

首先先了解一下MySQL8.0.16的新特性（截取部分有用）

降序索引

MySQL 8.0的一个很大的改进就是加入了开发者一直渴望的降序索引。表现在写操作的效率上，特别是对于高工作负载的服务器。在8.0版本中，影响MySQL读取性能的重要新增支持是：可以按降序(或正向索引扫描)创建索引的能力。以前的版本只有升序或反向索引扫描，如果需要降序，MySQL必须执行filesort（如果需要filesort，需要检查max_length_for_sort_data的值）。当最有效的扫描顺序混合某些列的升序和其他列的降序时，降序索引还使优化器可以使用多列索引。

简单来说：老版本的索引没有升序降序之分，全部默认的是升序索引，即使采用降序索引语法，虽然不报错，但是根本就不生效。所以采用该索引进行降序的时候，相当于多做了一次filesort。现在有了降序索引，避免了该次操作，最大化了性能。

UTF-8编码格式的变化

新版本中用UTF8MB4替换latin1作为默认字符编码。这意味着它需要更多的磁盘空间，因为UTF8在非US-ASCII字符上需要2个字节。并且默认排序规则从更改latin1_swedish_ci为utf8mb4_800_ci_ai。

隐藏函数：

在 MySQL 8.0 中，索引可以被"隐藏"和"显示"。当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其"恢复显示"即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。

可靠性

InnoDB 现在支持表 DDL 的原子性，也就是 InnoDB 表上的 DDL 也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性**，**元数据存储在单个事务数据字典中。

底层设计的更加依赖InnoDB引擎

新增了事务类型的数据字典，所有的元数据信息，都用InnoDB存储引擎进行存储。8.0之前的版本中，我们知道Server层和InnoDB引擎层有两套数据字典表。其中Server层部分的数据字典，存储在.frm文件里面。而InnoDB存储引擎层也有自己的数据字典表，在information_schema库下面的 tables表中进行存储。这种方式的最大问题就是：数据字典信息改动很难同步，而且两个字典信息库很难保证一致性，根结就在于DDL操作不是原子性的，对数据字典的更新不是事务性的。

由下图可知，老版本的MySQL的文件层面存储在文件系统上，系统表层面 用MyISAM存储引擎存储，而InnoDB存储引擎的数据字典（存储在information_schema库的tables表中）。这样看起来层面太多，存储结构很乱。

新的数据字典,不仅简化了层面结构，而且Server层面和InnoDB层面的数据字典进行了合并。数据字典信息全都存储在InnoDB存储引擎里面，实现了原子性的DDL。InnoDB也可以保证对于数据字典表的更新是事务性的。并可以通过视图（views）的方式来查看数据字典，查询性能提升近百倍。 其次目前只有唯一一个数据字典源，所以我们对它的访问可以通过唯一 一套加锁机制来实现。主要是通过server层面的元数据锁,简称MDL.通过这个来实现对数据字典表的并发访问。好处就在于InnoDB内部数据字典相关的锁就会被优化掉，并发性能得到提升。最后一点就是Server层面比起之前版本，会接受更多的任务，InnoDB存储引擎逐渐减少了工作量。让InnoDB可以挥发其最大作用。MySQL8.0版本，所有数据字典信息和系统表都存储在mysql.ibd（不允许用户自己创建）。 MySQL 8.0将其元数据存储到InnoDB中，这是一种久经考验的事务性存储引擎。系统表（如Users和Privileges以及Data Dictionary表）现在驻留在InnoDB中。MySQL 8.0消除了潜在不一致的一个来源。在5.7和更早版本中，基本上有两个数据字典，一个用于服务器层，另一个用于InnoDB层，在某些崩溃的情况下这些数据字典可能不同步。在8.0中只有一个数据字典。MySQL 8.0确保原子的，崩溃安全的DDL。有了这个，用户可以保证任何DDL语句将被完全执行或根本不执行。这在复制环境中尤为重要，否则可能会出现主节点和从节点（节点）不同步的情况，从而导致数据漂移。

简而言之：相对于Mysql5.6来说，mysql8.0默认使用了INNODB引擎，并且对Mysql底层的存储结构进行了变更，使得原本存储在Myism引擎上的系统表转移到INNODB引擎上，确保了INNODB引擎性能提升，并且更加安全稳定。基本解决了MySQL的历史遗留导致的性能问题。可以说MySQL8.0系列是一个全新的MySQL数据库。

配置持久化

MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。 MySQL 8.0新增了SET PERSIST 命令。命令如下： set persist sync_binlog=1; MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

SELECT ORDER BY与GROUP BY语法变更

8.0.12,8.0.13（未发布版本，但文档中已经更新内容）开始，MySQL 的 Order by 支持 GROUPING函数 以及 WITH ROLLUP语法，然后，在8.0.13开始，废弃掉group gy 中的desc，asc关键字，对于 WITH ROLLUP 得到的结果集合的排序，需要使用order by 语法。 对于搞数据聚合比较多的人来说，WITH ROLLUP 与 GROUPING 应该不算陌生，这个语法变更，相当于是把 order by 的语法补全完整，更兼容 SQL 标准语法了，如果迁移程序到 8.0，需要注意这种不兼容的变更。

安全性

对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权。导致无法使用SQLYOG和NAVICAT等工具连接MySQL8.0.16。当然后面会说如何解决这个问题。（目前感觉比较坑爹）

二、数据库升级

下载最新mysql数据库

点击官网下载链接https://dev.mysql.com/downloads/mysql/

注意：本次采用的是MySQL8.0绿色安装包版

下载流程如图所示 :

[外链图片转存失败(img-qeXUrz7J-1568081492889)(C:\Users\zhaoyi0815\Desktop\滴水集图片\性能文章08.png)]

[外链图片转存失败(img-d4Mxe860-1568081492890)(C:\Users\zhaoyi0815\Desktop\滴水集图片\性能文章09png.png)]

自定义my.ini文件

由于绿色安装包版没有my.ini文件，所以需要开发者自定义my.ini文件。

[mysqld] 
default_authentication_plugin=mysql_native_password //修改密码加密方式 
log-bin-trust-function-creators=1 //防止function函数无法创建 
binlog_expire_logs_seconds=604800 //7天日志会被清除，保证日志文件不会过大 
innodb_file_per_table //每个表一个文件存储 
innodb_flush_log_at_trx_commit = 2 //bin日志写入方式，尽量保证性能优先 
lower_case_table_names=1 //表名等信息大小写不敏感 
group_concat_max_len=1M //group_concat可以拼接的最大长度 
log_output=table,file //将慢查询的SQL写入文件和表(mysql.slow.log) 
long_query_time = 2 //指定慢查询的阀值 
port=3306 //端口号 
basedir=D:/zhaoyi/abiz_aeps/etc/mysql //mysql安装目录（需根据实际情况修改） 
datadir=D:/zhaoyi/abiz_aeps/etc/mysql/data //data目录的路径(需根据实际情况修改) 
character_set_server=utf8mb4 //指定编码格式 
default-storage-engine=INNODB //设置默认引擎 
explicit_defaults_for_timestamp=true //解决时间戳不能为00-00 00:00:00的问题 
connect_timeout=600 
wait_timeout=600 
max_connections=3000 //最大连接数 
sql_mode= //sql的语法模式 
ngram_token_size=1//ngram的最小分词 
ft_min_word_len=1//mysql的最小索引长度 
[mysql] 
default-character-set=utf8mb4 
[client] 
default-character-set=utf8mb4

将定义好的my.ini文件放到mysql的目录下

修改密码

注意mysql8.0.16的window环境要求vc2015版本，所以使用之前最好更新一下环境。

1. 输入cmd进入命令行模式

2.输入mysqld --initialize --console ,即可自动生成data文件夹，注意：随后出现的命令行中会出现mysql随机生成的密码，本次是：prrAUsCal3)A。后面登录数据库时可以使用。注意路径不能有中文！！！

3.初始化mysql服务 mysqld install 服务别名（必须不一致）

4.注意：如果不是以管理员身份进入cmd模式，输入上面指令则会报：Install/Remove of the Service Denied!的错误。 解决办法：

5.双击cmd.reg注册表文件即可。然后鼠标右击即可看到进入管理员模式的入口。

6.启动mysql服务

net start 服务名(必须与上面一致)

7.登录mysql

mysql -u root -p，输入临时密码，注意 -p后面要紧跟#passwd, 不能加空格。

8.修改密码

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY ‘Abc_123’; 记住一定要加";"。 因为mysql8.0的密码加密方式不一样了，所以必须设置成 mysql_native_password格式的密码，密码必须是Abc_123，因为本项目代码中硬核写入了密码，除非我有闲工夫去修改。

9.此时，安装包密码就修改成功了。

MySQL8.0.16升级和使用过程中可能存在的问题

host为localhost：只支持本地访问，无法远程访问

解决办法：输入sql语句，将其值修改为%即可。修改权限表一定要刷新。

UPDATE mysql.user SET HOST ='%' WHERE USER='root';
FLUSH PRIVILEGES;

mysql8.0的驱动无法使用5.6的驱动

理论上应该选用最新的，考虑到最新的驱动开发是基于JDK1.8,而我们项目的JDK只能使用1.7。所以需要同时兼容mysql8.0.16和JDK1.7。我选择的驱动5.1.47版本。注意驱动8.0的驱动名改动。

MySQL8.0的url 与老版本写法不一致

需要加上时区和SSL等配置 ，但是在实际使用时，新的url可以兼容之前老版本的url。

jdbc:mysql://127.0.0.1:3306/abiz_aeps?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true

MySQL分组不在支持排序

注意mysql8.0严格了sql语法。具体影响是GROUP BY函数不在具有排序功能，只能分组。5.6版本可以在GROUP BY函数后面加ASC或者DESC进行排序，同时默认为ASC排序。

解决办法： 采用ORDER BY函数对原有的GROUP BY字段进行排序，在此基础上可以采用MySQL新特性降序索引，对order by函数进行优化。 应用场景：如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，可以采用降序索引 注意点：建立联合索引时，降序索引必须有DESC，这样才生效，即sqlyog工具生成的索引没有降序索引，只能sql指定。

MySQL的密码加密方式与老版本不同

mysql的默认密码加密方式与之前的不兼容，是新的算法，sqlyog等常用辅助工具无法连接。

解决办法：

my.ini的[mysqld]下加入default_authentication_plugin=mysql_native_password ，重启即可使用。

导入老sql文件时会报错，函数无法创建。

解决办法：my.ini中[mysqld]下添加配置log-bin-trust-function-creators=1 即可关闭

Datetime数据类型不支持00-00这种格式

解决办法：my.ini中[mysqld]下添加配置explicit_defaults_for_timestamp=true即可解决

使用group by函数进行分组操作时，报错EEOR1055

原因：mysql8.0默认sql_mode=only_full_group_by select 选取分组中的列+聚合函数 from 表名称 group by 分组的列 ，从语法格式来看，是先有分组，再确定检索的列，检索的列只能在参加分组的列中选。 解决办法：把sql_mode中的only_full_group_by删除重新，重启即可。

mysql insert 有一个时间字段，'0000-00-00 00:00:00’格式为这样。插入数据时报错

原因：sql_mode=no_zero_date 则mysql不支持伪日期。 解决办法：删除该配置，重启数据库即可。 如果以上操作都不想重启数据库，也可以通过SET GLOBAL关键词来设置全局变量。缺点：重启mysql数据库则配置不在生效。

删除服务操作

进入cmd命令行。运行 sc delete “服务名” 即可删除该服务了。

如果出现下面的错误,则是因为进入的cmd没有以管理员身份进入。

解决办法： Win10：搜索到cmd的启动程序，右击以管理员身份进入即可。

卸载MYSQL绿色安装包版

首先是关闭mysql数据库服务器：net stop 服务名
然后是关闭myssql服务：以管理员身份打开cmd，输入：sc delete 服务名

最后：删掉本地的绿色安装包即可。

如果以上操作不行，补救措施：win+R,输入regedit，打开注册表，搜索mysqld，删除所有有关mysql的目录。
以上操作都不行，重启吧。挺管用的。

三、基于MySQL8.0.16的性能优化

全文索引替换模糊查询

一般情况下，系统中的搜索功能都是基于ES等框架的搜索服务实现的，但是在没有搜索服务的情况下，我们也可以采用SQL语句的模糊查询方式实现搜索。也就是采关键字LIKE。

其中LIKE实现的模糊查询中有一中全模糊查询方式:like%字段%。这种查询方式是无法走索引的，所以效率比较低。

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。 从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。 MySQL5.6.24上InnoDB引擎也加入了全文索引 所以基于InnoDB引擎的Mysql8.0可以很好的支持中文的全文索引 。

使用全文索引需要注意的坑

1. 建立全文索引时一定要明确语法表示使用mysql的自带ngram解析器，支持中文，日文和韩文（CJK）

   具体写法： 在原有的基础上加上WITH PARSER ngram。

2. 需要修改的配置 （my.ini）

   SHOW GLOBAL VARIABLES LIKE ‘%ft_%’;查询整体的全文索引配置 SHOW VARIABLES LIKE ‘ngram_token_size’;查询ngram解析器的分词值。

3. 查询默认相关配置（my.ini）

   在my.ini中修改相关配置; 具体是在[mysqld]下面添加ngram_token_size=1(ngram的最小分词) ft_min_word_len=1（mysql的最小索引长度）

4. 字符集陷阱

   目前字符集引起了数据不精准问题，无法查询英文的问题。最好采用utf8mb4_bin。

5. 多列模糊查询

   单列分别建立全文索引时，多列模糊查询时不生效。

6. 使用联合多列索引的坑

   联合索引存在两个 String值时， string值必须加’**’。个人建议：为了语法统一，不论是单字段查询，还是联合多列字段查询，都加上 。遵循最左原则。

7. 不同表的全文索引不能放在一起查询

   可以两个语句中加上OR。

   select * from a,b where match(a.name,b.name) against(('*zy*') in boolean mode)
   

   select * from a,b where match(a.name) against(('*zy*') in boolean mode) or select * from a,b where match(b.name) against(('*zy*') in boolean mode)
   

上OR。

select * from a,b where match(a.name,b.name) against(('*zy*') in boolean mode)

select * from a,b where match(a.name) against(('*zy*') in boolean mode) or select * from a,b where match(b.name) against(('*zy*') in boolean mode)