化雨u
探索云世界
化雨u
智者乐水，仁者乐山
暂时未有相关通用技术能力~
阿里云技能认证
详细说明问题原因： 抽取数据job，通过proxy连接mysql，数据库侧报错：ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'头一次见这种报错，网上查看是由max_connect_errors参数控制。顾名思义，就是限制连接报错的次数。官方文档对这个参数的解释： After max_connect_errors successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. If a connection from a host is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. To flush the host cache, execute a FLUSH HOSTS statement, a TRUNCATE TABLE statement that truncates the Performance Schema host_cache table, or a mysqladmin flush-hosts command. 大概意思就是：超过max_connect_errors个连接MySQL的请求没有成功被中断后，server会阻止再次连接。如果在max_connect_errors内连接成功，那错误次数的计次器被清0。看解释很简单，那我把这个参数设置为2，密码错误2次后不就可以了？测试一下。登录数据库，设置max_connect_errors=2； 连续两次输入错误的密码：最后发现还是能登录：这里测试就明白了，确实跟想的不一样。官网说的请求中断跟密码错误的连接是两码事。有些文章说的这个参数“防止暴力破解密码”也就是错误的。官网这个报错也有说明： B.6.2.5 Host 'host_name' is blockedIf the following error occurs, it means that mysqld has received many connection requests from the given host that were interrupted in the middle:Host 'host_name' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts' 参数是如何实现的？ 那这个参数到底是怎么实现的，官网上提到了host_cache，先看下host_cache是做什么的。 The MySQL server maintains a host cache in memory that contains information about clients: IP address, host name, and error information. The Performance Schema host_cache table exposes the contents of the host cache so that it can be examined using SELECT statements. MySQL在内存中维护了一个host cache来缓存客户端的连接信息，如：IP、hostname、报错信息等。在MySQL的Performance Schema库的 host_cache表里记录了这些信息。这样同一用户登录的时候，MySQL不用每次都去解析DNS，而是直接用缓存的信息。官方文档介绍的很详细，这里挑重点说明这个缓存跟max_connect_errors参数的联系： The cache contains information about errors that occur during the connection process. Some errors are considered “blocking.” If too many of these occur successively from a given host without a successful connection, the server blocks further connections from that host. The max_connect_errors system variable determines the permitted number of successive errors before blocking occurs (see Section B.6.2.5, “Host 'host_name' is blocked”). 这里就说明了，host cache会记录连接MySQL时的错误信息，如果错误连接次数过多，那么MySQL就会阻止这个host再次连接，怎么阻止？就是通过max_connect_errors参数来定义错误连接次数的上限。 重新测试： 明白了max_connect_errors参数的作用在重新测试之前，先来了解下记录了block连接的host_cache表：主要关注两个列：SUM_CONNECT_ERRORS：被block的连接的数量。关于这个列的说明：The number of connection errors that are deemed “blocking” (assessed against the max_connect_errors system variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES).这里就标明了，只计算协议握手错误，以及通过验证的主机。通过这个列的值判断是否超过max_connect_errors 的值。COUNT_AUTHENTICATION_ERRORS：身份认证失败的错误数 下面开始测试MySQL连接中断的情况，通过telnet的方式：1、设置max_connect_errors =2 2、telnet一次3、telnet二次4、登录MySQL测试问题复现。无法登录。 解决方法： MySQL已经提示，可以执行flush-hosts来清空host_cache。 1、mysqladmin flush-hosts 2、mysql> flush hosts; 3、truncate table performance_schema.host_cache; 可以考虑调整max_connect_errors的大小 设置skip-name-resolve=on（RDS默认是打开的），测试设置参数后，max_connect_errors参数会失效。 注意：这些都不是根除的办法，首先应该排除网络连接的问题。为什么会出现连接断开 测试skip-name-resolve参数 1、开启skip-name-resolve=on不能动态修改，在my.cnf文件中添加skip_name_resolve=on，然后重启MySQL服务。 2、telnet 1次3、telnet 2次4、登录可以登录，这时候，max_connect_errors参数没有生效。
1 Sequence简介 DRDS sequence全称：全局唯一数字序列，顾名思义，其作用是生成全局唯一和有序递增的数字序列。常用于主键列、唯一索引列等值的生成。Sequence分类，总共分为两大类：显式sequence：通过sequence语法进行创建与维护，可以单独使用。隐式sequence：DRDS表主键定义 AUTO_INCREMENT后，用于自动填充主键，由 DRDS 自动维护每类sequence针对不同的使用场景还分为四种类型，分别为Group Sequence，Group Sequence（单元化），Time-based Sequence，Simple Sequence。详情参考阿里云官网本文重点介绍group隐式sequence。为什么？1、sequence原理性的东西，在官网上基本没有体现，我们只知其然而不知其所以然2、当然是因为做DRDS数据迁移踩的关于隐式sequence的坑太多了，经常跟阿里研发取经，加上环境测试了解到一些东西。 2 sequence的原理理解 1、sequence值是如何分配的？ 如图所示：DRDS在分配sequence的时候，会根据自身的节点数来划分多个sequence段，默认每个段的长度为10万。注意这里是根据DRDS的规格节点数来划分的sequence端，而不是根据RDS的数量，与RDS无关。通过show sequences;命令，可以查看当前sequence的value。这里的value值通常是表示已经分配的最大sequence段的起始值，即200000 2、sequence的起始值 有没有发现上图中，sequence段的起始值我是从100001开始取值的。默认情况下，sequence的起始值并不是按照1来开始递增的，而是按照100001开始的。这就涉及到内部的一些算法：(value - value % (innerStep unit_count)) + innerStep unit_count + nodeIndex * innerStep 实验证明如下：新建一张表，并且插入一条测试数据：可以看到主键的id是从100001开始取值的。 3、sequence的缓存 这里在节点上分配的sequence段，我们可以称之为sequence的缓存，正常情况下，节点上的缓存用完才会重新分配sequence段。缓存实验测试如下：truncate表清理了数据之后，重新插入数据还是会用到之前的缓存，从100002开始。由于这种缓存的存在，尤其是DRDS在数据迁移的某些场景时，会产生很大的影响。 某客户案例：DRDS进行平台的数据迁移，由于没有直接迁移DRDS to DRDS的工具，经过多次测试与衡量决定采用底层RDS to RDS方式的数据迁移。在第二次进行应用测试大批量报错主键冲突。原因就是第一次测试完，truncate清理数据后，缓存还是存在的。第二次导入数据后则还是会用缓存的值，这时候测试插入数据就会出现主键冲突。这里出现sequence冲突的根本原因是，sequence本来是DRDS层面维护的，但是采用非标的方式迁移底层的RDS数据，虽然数据没有问题，但sequence就没有人来维护了。这样就很容易出现很多问题。所以不到迫不得已尽量不要走非标方案。解决方法：清理sequence缓存。常用的清理缓存的方法：1、alter sequence start with AUTO__XXXX;使用命令抬高sequence，命令是表级别的。2、重启drds，目的是重启节点。3、表删除重新建表 4、缓存的值是如何计算出来 我们已经知道，初始化的时候，sequence的值会按照算法定义起始值。那alter sequence start with AUTO__XXXX命令是如何更新sequence的value 以及 缓存的呢？同理，alter sequence调整后，也会重新初始化sequence，先用算法更新起始值(value - value % (innerStep unit_count)) + innerStep unit_count + unit_index * innerStep然后再用下面的公式计算缓存区间：所有节点先后用公式计算value + (inner_step * unit_count) 简单的说：一般而言，执行命令alter后，首先会根据算法，将调整的sequence“合法化”，就是取整数，然后加一段区间更新到缓存，所以缓存会更新到一个更大的值。所以你会发现，在执行alter命令后，之后的主键值并不是按照修改的值递增的，主键值肯定比调整的value要大。测试验证如下：上图可以看到，show sequence显示的值为200000，与缓存中已分配的sequence段的最大值匹配。上图中，随意调大了sequence的value，show sequence显示为了432432，（这个只是暂时显示，后面还是会更新为最大sequence段的起始值）上图中，又插入了一条数据，发现show sequence的值已经改了。用命令查询一下缓存，果然缓存已经更新了，并且是比alter设置的value值要大。这里还会发现，图中第一次show sequence时，不是代表最大的sequence段的起始值。这是因为sequence值用到的时候才会更新这个value。那为什么执行查看缓存的命令inspect后就恢复了呢？猜测这个命令会强制刷新value与缓存保持一致。 那么此时再插入数据主键会是从432432开始吗？ 当然不是了，如果你还觉得是，那就是之前我啰嗦的不够透彻。这里就是按照缓存中的值来取sequence了。 3、END
一个SQL无法kill掉的案例 问题描述 因为业务需要，客户有一个ddl需求扩展某一drds单表的列的长度，但是在drds上执行ddl的时候被一个select阻塞mdl（metadata lock）锁，详情见官方文档8.11.4 Metadata Locking。报错以及SQL如下：这样的SQL是手工执行，客户想要通过kill掉SQL来解决，但是出现问题： 执行kill命令成功，但是一直显示SQL为killed状态，还是在执行中。由于这个SQL一直不能释放资源，导致ddl无法执行。 解决过程 Killed状态是正在释放SQL所占用的资源，官方解释如下： Killed Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock. 通过查看SQL的事务，发现这个查询所在的事务已经执行了一周以上的时间，如果是等待回滚的话，需要等待很长一段时间。（估计是hang死在这了） 看SQL的执行计划，笛卡尔积总共扫描200亿行数据。 为了快速恢复客户业务，首先想到的就是通过RDS主备切换，因为是drds单表，访问都是在0号库上，所以只针对RDS 0号库主备切换，然后重启备库。（还是需要重启解决啊==）通过DRDS层面看这个SQL已经因为执行超时被kill了，但是下发到了RDS上还是在执行。 检查好主从状态，确定没有延迟，通过杜康进行主备切换，切换成功后验证使用ddl语句，发现报错： 根据报错发现是drds分库上表不存在。 DRDS单表与小表广播的区别： 单表与小表广播都是会在所有drds的分库上创建一张表，只不过小表广播的表所有的分表都有全表数据；而单表的数据只会存储在0号库，其他库只有一个表结构，这么做是为了方便将来可能使用小表广播。 小表广播：在各分库就完成了数据join汇总，避免跨库操作 检查分库分表情况，发现确实只有0号库有这张表，其他分库没有 SQL没有开启审计，客户反馈没有执行过删除其他分库的表命令，无法定位为什么导致表结构只有在0号库存在的问题，先解决当前问题。通过在DRDS上创建表，create table if not exists table_name，将分库的表结构补齐后，客户反馈执行ddl成功。该处理备库了，备库不出意外的也出现了MDL锁，由于同步过来的ddl语句导致，开始重启备库了。 重启备库出现了小插曲，在杜康上重启备库失败，重跑也失败，最后是黑屏客户端登录到备库上，手动拉起的mysql进程，重新跑成功，有惊无险，问题解决！ 总结： Killed这种状态，一般是大事务、大查询、ddl等执行过程中被kill，回滚需要很长的一段时间，只能通过等待SQL事务回滚完成，这里是select阻塞直接重启mysql，可以通过切换主备后重启备库的方式解决。另外在执行ddl命令前可以先在备库执行，set sql_log_bin=OFF停止写binlog，然后主备切换，这样旧主上就不会因为这个ddl同步过来再次产生mdl锁，然后在旧主上执行ddl相同命令，从而降低对业务的影响。
DRDS连接池drds是一款基于阿里云rds的数据库中间件，总结一下关于drds连接池的理解 从DRDS来看，有两个连接池：*1、 应用到DRDS的连接池2、 DRDS到后端RDS的连接池* 应用到DRDS的连接池目的：提高应用性能，减轻数据库负载作用：1、资源复用：连接可以重复利用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，同时增进了系统的平稳性。 2、提高系统响应效率：连接的初始化工作完成后，所有请求可以直接利用现有连接，避免了连接初始化和释放的开销，提高了系统的响应效率。 3、避免连接泄漏：连接池可根据预设的回收策略，强制回收连接，从而避免了连接资源泄漏 某专有云采用的是德鲁伊连接池，这也是官网推荐的连接池类型。配置信息如下图： drds之前出现过节点连接数不均衡问题，某几个节点的压力过大，导致docker连接异常。解决过程：1、修改应用到drds的探活语句，之前探活语句查看执行计划发现是打到了drds下面rds的 0 号库上，导致drds的0号库qps压力高。现在修改语句只探活到drds层面，这个主要是针对0号库压力高的问题。2、升级德鲁伊版本，并且设置固定时间关闭连接的参数phyTimeoutMillis升级后观察连接数还是比较平均的。当然drds也有自己的slb负载均衡，通过相应的算法来将应用连接数分配到各个docker节点上。 DRDS到后端RDS的连接池DRDS到后端的连接池是指：从每个DRDS节点到后端RDS每个分库的连接池。这个连接池是在drds的docker节点上的。DRDS后端连接池的大小设置是与后端RDS的最大连接数相关的,参考最大连接处设置公式如下： 最大连接数=RDS实例最大连接数/DRDS节点数/RDS实例分库数（每个RDS上的物理分库数） 改值只是一个参考值 如何修改该值：1、 通过drds manager中实用功能可以自定义该值，注意appname是show datasources中的schema字段，也可以通过drdsmanager查询该值。2、 通过drds console进行调整实例连接池信息，会根据后端RDS实例进行调整，并且会reload从drd到rds的连接，这是自动调整的，无法自定义设置。 相关问题：客户在进行压测的过程导致drds的0号库连接池被打满，具体报错理解为connection $RDS_0号库 full。想要咨询并且修改drds到后端rds的连接数。 针对该问题，首先建议客户需要优化对应的慢SQL，并且0号库rds的实例连接数相比其他的数据库连接数配置要低，连接池更容易因为慢sql而被打满，或者说有没有一些大事务、长事务等占用连接不释放等。 应用到drds和drds到rds的两段连接数是异步的，没有绑定关系的。前端到drds的连接数，drds首先接住这些连接，然后通过自己的转发机制，异步转发到节点上去执行，每个节点都会有一个连接池到rds上分库的连接，这样也不能保证是哪个节点的连接池被打满。这也跟业务相关，假如压测中很多场景是多访问0号库的业务，也会导致0号库出现这个问题，这些都是要去考虑的。 实例连接数是可以增加的，但是不能一味的增加连接数，首先应该从问题根源上确认是否有导致出现连接数打满的问题，需要去优化的要做好优化，最后考虑去升级规格。避免出现一直升级连接数还是被打满的问题。
版本： 阿里云专有云v2版本 问题现象： 同步任务反馈drds无法连接数据库，经排查drds manager 发现有一个节点异常 排查过程：向现场运维组反馈该异常后，为了确保生产环境的使用，确定通过drds manager进行白屏重启。点击重启后，无法返回成功界面，又强制重启，还是没有响应。 然后尝试通过命令行连接到该docker，显示连接失败，无法ping通该docker。 通过天目发现该docker所在的物理机报机器故障。通过天目登录到远程机房主机进行重启，依然显示连接异常。(期间是对这个docker重启了好几次) 最后针对物理机故障的问题向客户反馈维修，物理机正常之后发现该docker还是连接异常，通过天目发现报错为检测失败。 登录到该docker节点上发现，java进程一直被重新拉起。时有时无。 并且在该drds上的tddl目录下发现大量的gc 日志。 从而说明了该docker的java进程一直被不断的重拉，每起来一次就会产生一个gc日志。并且日志里面也只是存储了一些简单的信息 向drds研发同学咨询，发现drds的java进程pid内容和/home/admin/drds-server/bin/tddl_3306.pid内容比较发现进程的pid是不一致。 执行健康检查的脚本，可以判断服务是否正常，执行发现service is down 排查是否有进行手动kill，结果是没有。 经研发同学经验排查，发现该docker的nameserver有多个127.0.0.1 而正常的docker节点下，是只有一个的，如下： 解决方法： 经开发确认为，这个drds 的 docker节点在 专有云v2版本的bug。在频繁重启容器超过3次后，之后再每次重启容器都会在resolv.conf这个文件里面重复写入127.0.0.1。就会导致进程一直不断的被拉起。 根据研发的建议： 1、 先把多余的127.0.0.1删除 保留一个； 2、 把bin目录下的pid文件删除掉；3、 停止drds-server进程；4、 按照sudo -u admin sh /home/admin/drds-server/bin/startup.sh -p 3306 -D启动drds 的java进程； 重启之后，问题解决
RDS实例CPU一直被打满 1、问题描述 版本：阿里云专有云V2版本客户反馈，通过drds执行SQL比较慢，之前很快就能执行完的SQL，需要三四秒才可以执行成功。 2、问题排查 首先排查drds的状况，通过drds manager发现该drds下面有两个docker节点，并且都是正常的。检查drds下面挂的rds的实例状态，一共有四个rds，其中一个rds的CPU一直被打满，并且卡顿时间跟客户描述的一致。问题已经基本定位到时其中一个RDS实例的CPU被打满，导致查询缓慢，拖慢了整个drds实例。* 实例的cpu被打满，一般是由两种情况：1、慢SQL，全表扫描等。此时qps不高，但是查询占用了大量的逻辑IO，需要扫描大量的数据行，导致CPU资源被大量消耗2、qps高，业务量高峰。此时慢SQL不是问题所在。那就需要考虑从实际的规格和架构方面入手，可以提高rds的规格配置，增加只读实例等。*回到问题，登录到该rds。show processlit发现有很多慢查询，如图：可以明显看到这种SQL执行时间特别长，达到了21秒，并且占用的逻辑读也特别高。问题还是在慢SQL方面。那它的备库现在什么情况呢，通过show slave status发现。主备已经停止同步。此时已经不能登录到备库，查看mysql进程，已经挂掉。手动拉起mysql进程。后可以登录了。可以看到刚拉起主备延迟很高 3、问题解决 慢SQL已经反馈给客户进行处理，并且通知研发注意这种SQL，应用设计和开发过程中，要考虑查询的优化，遵守 MySQL 优化的一般优化原则，降低查询的逻辑 IO，提高应用可扩展性。客户将SQL kill处理后，该rds的cpu恢复正常，并且从drds查询速度也恢复正常，问题解决。
MySQL在很多情况下都会用到临时表，总结一下什么时候会用到临时表： 什么是临时表：MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。为什么会产生临时表：一般是由于复杂的SQL导致临时表被大量创建 临时表分为两种，一种是内存临时表，一种是磁盘临时表。内存临时表采用的是memory存储引擎，磁盘临时表采用的是myisam存储引擎（磁盘临时表也可以使用innodb存储引擎，通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎，从mysql5.7.6之后默认为innodb存储引擎，之前版本默认为myisam存储引擎）。分别通过Created_tmp_disk_tables 和 Created_tmp_tables 两个参数来查看产生了多少磁盘临时表和所有产生的临时表（内存和磁盘）。 内存临时表空间的大小由两个参数控制：tmp_table_size 和 max_heap_table_size 。一般来说是通过两个参数中较小的数来控制内存临时表空间的最大值，而对于开始在内存中创建的临时表，后来由于数据太大转移到磁盘上的临时表，只由max_heap_table_size参数控制。针对直接在磁盘上产生的临时表，没有大小控制。 下列操作会使用到临时表： 1ã union查询 2ã 对于视图的操作，比如使用一些TEMPTABLE算法、union或aggregation 3ã 子查询 4ã semi-join 包括not in、exist等 5ã 查询产生的派生表 6ã 复杂的group by 和 order by 7ã Insert select 同一个表，mysql会产生一个临时表缓存select的行 8ã 多个表更新 9ã GROUP_CONCAT() 或者 COUNT(DISTINCT) 语句 。。。 Mysql还会阻止内存表空间的使用，直接使用磁盘临时表： 1ã 表中含有BLOB或者TEXT列 2ã 使用union或者union all时，select子句有大于512字节的列 3ã Show columns或者 desc 表的时候，有LOB或者TEXT 4ã GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列
MySQL查询优化器的执行计划是根据统计信息中键值的分布选择合适的索引，这是基于索引的选择性的。innodb通过抽样的方式来计算统计信息，首先随机的读取少量的索引页面，然后以此为样本计算索引的统计信息。老的innodb默认样本页面数为8，新版本可以通过innodb_stats_transient_sample_pages（5.6.3之前是innodb_stats_sample_pages）来设置样本页的数量。样本页的数量设置的更大，理论上来说是可以得到更准确的统计信息，特别是对于超大的表。但是具体设置多大合适还是需要根据实际情况 innodb索引的统计信息存储方式有两种，一种是非持久性存储，既存储在内存中，如果服务器重启就会丢失；一种是持久性存储，即存储到磁盘上，可以永久保存。通过参数innodb_stats_persistent来控制。在MySQL5.6.6之后，默认是持久性存储。 两种存储方式： 1ã 非持久性存储，通过设置innodb_stats_persistent=OFF或者使用STATS_PERSISTENT=0创建，通过以下操作可以触发计算统计信息： a) 执行analyze table b) 在使用show table status、show index等命令的时候，或者在查询系统表INFORMATION_SCHEMA.TABLES 和 INFORMATION_SCHEMA.STATISTICS的时候。需要一个参数控制是否会触发更新统计信息，innodb_stats_on_metadata=on时。 这里需要注意的是，数据库中有大量的表或者索引的时候，会给数据库的IO带来更大的压力；并且如果频繁的更新统计信息，MySQL的执行计划的稳定性也会受到影响。 c) 在启动mysql客户端的时候采用--auto-rehash参数。 d) 一个表首次被打开的时候。 e) 表发生非常大的变化的时候（大小变化超过1/16或者新插入20亿行数据）。 2ã 持久性存储，设置innodb_stats_persistent=ON，或者STATS_PERSISTENT=1创建。 持久化的信息存储在MySQL的系统表mysql.innodb_table_stats 和mysql.innodb_index_stats 中。 因为是持久性存储到磁盘上，所以在表一段时间之后或者是进行大的改动的时候需要手动执行analyze table来更新统计信息。 总结：建议设置持久性存储到磁盘上，可以得到更稳定的执行计划，并且在系统重启之后可以更快速的生成统计信息。但是需要周期性的执行analyze table来手动更新统计信息，否则统计信息永远不变。
1、 sysbench简介sysbench是一款多线程系统压测工具。它可以根据影响数据库服务性能的各种因素来评估系统的性能。例如，可以用来测试软件I/O、操作系统调度器、内存分配和传输速度、POSIX线程，以及数据库服务器等。Sysbench支持Lua脚本语言，Lua对于各种测试场景的设置可以非常灵活。Sysbench是一种全能测试工具，支持MySQL、操作系统和硬件的硬件测试虽然mysql默认的有mysqlslap这个性能测试工具，跟sysbench比还是sysbench的功能齐全，测试准确，是最有用的MySQL工具之一。2、 下载安装https://dev.mysql.com/downloads/benchmarks.html或者wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip" [root@localhost software]# lsos_zabbix-agent sysbench-0.4.12.14.tar.gz[root@localhost software]# tar xvf sysbench-0.4.12.14.tar.gz 安装必要的依赖库[root@localhost sysbench-0.4.12.14]# yum -y install make automake libtool pkgconfig libaio-devel vim-common 开始安装：[root@localhost software]# cd sysbench-0.4.12.14[root@localhost sysbench-0.4.12.14]# lsacinclude.m4 autom4te.cache config.log configure.ac INSTALL m4 Makefile.in README TODOaclocal.m4 ChangeLog config.status COPYING install-sh Makefile missing README-WIN.txtautogen.sh config configure doc libtool Makefile.am mkinstalldirs sysbench 遇到报错：[root@localhost sysbench-0.4.12.14]# ./autogen.sh ./autogen.sh: running `libtoolize --copy --force' libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.libtoolize: copying file `config/ltmain.sh'libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.libtoolize: copying file `m4/libtool.m4'libtoolize: copying file `m4/ltoptions.m4'libtoolize: copying file `m4/ltsugar.m4'libtoolize: copying file `m4/ltversion.m4'libtoolize: copying file `m4/lt~obsolete.m4'./autogen.sh: running `aclocal -I m4' ./autogen.sh: running `autoheader' ./autogen.sh: running `automake -c --foreign --add-missing' ./autogen.sh: running `autoconf' configure.ac:49: error: possibly undefined macro: AC_LIB_PREFIX If this token and others are legitimate, please use m4_pattern_allow. See the Autoconf documentation. Can't execute autoconf查阅资料，解决方法;[root@localhost sysbench-0.4.12.14]# cat configure.ac | grep AC_LIB_PREFIX AC_LIB_PREFIX() 注释掉这个函数就可以了 [root@localhost sysbench-0.4.12.14]# ./autogen.sh ./autogen.sh: running `libtoolize --copy --force' libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.libtoolize: copying file `config/ltmain.sh'libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.libtoolize: copying file `m4/libtool.m4'libtoolize: copying file `m4/ltoptions.m4'libtoolize: copying file `m4/ltsugar.m4'libtoolize: copying file `m4/ltversion.m4'libtoolize: copying file `m4/lt~obsolete.m4'./autogen.sh: running `aclocal -I m4' ./autogen.sh: running `autoheader' ./autogen.sh: running `automake -c --foreign --add-missing' ./autogen.sh: running `autoconf' Libtoolized with: libtoolize (GNU libtool) 2.4.2Automade with: automake (GNU automake) 1.13.4Configured with: autoconf (GNU Autoconf) 2.69 [root@localhost sysbench-0.4.12.14]# ./configure && make && make install[root@localhost software]# sysbench --versionsysbench 0.4.12.10安装完成！3、 测试MySQL的OLTP基准测试[root@localhost software]# sysbench --helpUsage: sysbench [general-options]... --test= [test-options]... command General options: --num-threads=N number of threads to use [1] --max-requests=N limit for total number of requests [10000] --max-time=N limit for total execution time in seconds [0] --forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off] --thread-stack-size=SIZE size of stack per thread [32K] --init-rng=[on|off] initialize random number generator [off] --seed-rng=N seed for random number generator, ignored when 0 [0] --tx-rate=N target transaction rate (tps) [0] --tx-jitter=N target transaction variation, in microseconds [0] --report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0] --report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. [] --test=STRING test to run --debug=[on|off] print more debugging info [off] --validate=[on|off] perform validation checks where possible [off] --help=[on|off] print help and exit --version=[on|off] print version and exit Log options: --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [4] --percentile=N percentile rank of query response times to count [95] Compiled-in tests: fileio - File I/O test cpu - CPU performance test memory - Memory functions speed test threads - Threads subsystem performance test mutex - Mutex performance test oltp - OLTP test Commands: prepare run cleanup help version See 'sysbench --test= help' for a list of options for each test.这里可以看到sysbench支持文件I/O，CPU，内存，线程，mutex互斥锁，OLTP等基准测试3.1简单生成基准测试表[root@localhost sysbench-0.4.12.14]# sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-password='123456' --mysql-socket='/tmp/mysql.sock' preparesysbench 0.4.12.10: multi-threaded system evaluation benchmark No DB drivers specified, using mysqlCreating table 'sbtest'...Creating 100000 records in table 'sbtest'... 各种命令格式都在帮助里面可以找到，默认是MySQL数据库，这里已经创建了一个10万行的测试表 3.2测试结果[root@localhost sysbench-0.4.12.14]# sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-password='123456' --mysql-socket='/tmp/mysql.sock' --max-time=60 --oltp-read-only=on --max-requests=100 --num-threads=8 runsysbench 0.4.12.10: multi-threaded system evaluation benchmark No DB drivers specified, using mysqlRunning the test with following options:Number of threads: 8Random number generator seed is 0 and will be ignored Doing OLTP test.Running mixed OLTP testDoing read-only testUsing Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)Using "BEGIN" for starting transactionsUsing auto_inc on the id columnMaximum number of requests for OLTP test is limited to 100Using 1 test tablesThreads started!Done. OLTP test statistics: queries performed: read: 1400 --读总数 write: 0 --写总数 other: 200 --其他操作 total: 1600 --全部 transactions: 100 (610.21 per sec.) --事务总数 deadlocks: 0 (0.00 per sec.) --死锁 read/write requests: 1400 (8542.96 per sec.) --读写请求 other operations: 200 (1220.42 per sec.) --其他操作 General statistics: --常规统计 total time: 0.1639s --所有时间 total number of events: 100 --所有的事务数 total time taken by event execution: 1.2855 --所有事务耗时相加 response time: --响应时间 min: 5.48ms --最小耗时 avg: 12.85ms --平均耗时 max: 28.09ms --最大耗时 approx. 95 percentile: 19.89ms --超95%平均耗时 Threads fairness: events (avg/stddev): 12.5000/0.50 --平均处理事务数/标准偏差 execution time (avg/stddev): 0.1607/0.00 --平均执行时间/标准偏差
一、cdp同步基本原理数据集成（Data Integration）是阿里巴巴集团提供的数据同步平台。该平台具备可跨异构数据存储系统、可靠、安全、低成本、可弹性扩展等特点，可为 20 多种数据源提供不同网络环境下的离线（全量/增量）数据进出通道。数据源类型的详情请参见 支持数据源类型。数据集成的原理：数据集成在阿里云上提供一套分布式离线数据同步平台，同时提供一套抽象化的数据抽取插件（称之为Reader）、数据写入插件（称之为Writer），并基于此框架设计一套简化版的中间数据传输格式，从而达到任意结构化、半结构化数据源之间数据传输之目的。从用户角度来看，一个数据集成同步任务运行Job示意图如下所示： 上述中，红色虚箭头是代表通过collector状态收集器监控数据返回到脏数据管理服务器进行数据分析，灰色方向箭头代表数据流向。DI Service主要是包含资源管理器、Job管理器、脏数据管理器、分布式服务、鉴权服务等。Job Container主要是将数据集成运行任务分成若干个task，然后通过scheduler调度管理。TaskGroup Container主要是数据抽取通过数据通道（channel）将数据写入。• 使用数据集成Job启动API，向数据集成服务端发起调用，提交一个离线数据同步Job。• 数据集成收到Job API请求后，将负责做必要的安全和权限校验，待校验通过后，数据集成会下发相应的Job到执行集群节点启动离线数据同步任务。• Job启动后，根据您提供的源端（Reader）、目的端（Writer）的配置信息，加载并初始化相关插件，连接两端数据源，开始数据同步工作。• Job运行过程中，将随心跳向数据集成汇报当前进度、速度、数据量等关键运行指标，可根据Job的状态API实时获取该Job运行状态，直至Job运行结束（成功或者失败）。 流程概述 步骤1：数据源端新建表。步骤2：新增数据源。步骤3：向导模式或脚本模式配置同步任务。步骤4：运行同步任务，检查目标端的数据质量。 因为DRDS不支持存储过程，造一张实际生产的表有些复杂，所以采用通过RDS创建表数据，再导入到DRDS的方式二、测试表准备：通过RDS新建一张实际生产的表，数据量为一亿行左右（主键为自增）通过存储结构插入数据（业务只需要天数是随机的）：delimiter $$ drop procedure huayu2; CREATE PROCEDURE huayu2() begin declare var int; set var=0; while var<100000000 do insert into t_pcs_bill_recieve_huayutest select null,1234567,210025002110010114117029000016,0,null,0,null,null,null,0,0,null,null,0,null,null,null,21100101,null,null,null,null,null,null,null,21002500, null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,CONCAT(2018,'-','03','-',LPAD(FLOOR(1 + (RAND() * 31)),2,0),' ','14:54:04'),null, null,'2017-11-10 14:54:04',null,null,null,2,4,null,null,null,null,null,0,null,null,null,null,null; set var=var+1; end while; end $$ delimiter 三、测试要求通过mysqldump在DRDS新建导入的表，以天为分表键，分表键的值是随机分布在3月1日-3月31日的。将源表按分表键做拆分，1天拆成1个任务，并发向目标表里进行同步，同时目标表按天做分区，找到最佳并发量。注意：分库键的选择为自增主键，不能是固定，否则hash的时候只落在一个库里面,分表键也是随机的日期，这样数据就能均衡的分布在各个库里面四、具体实施步骤1、将RDS的表导入到测试环境DRDSmysqldump –urds_xncs_qps8 –p –P3306 –h 10.1.1.1 database test1 –t > /home/hy.sql 这里加上了-t参数，目的是不备份表的结构，这样做的好处是，导入到DRDS的时候，在DRDS端建一个相同名字的数据库，并且可以自定义分库分表键，这样就省略了再次去修改备份的.sql文件而达到分库分表的目的。当然，如果只是需要一个单表，那就复制表结构跟数据就好了 2、 将复制的表数据导入到DRDS2.1、 通过mysql -udrds_uat_pcs –P3306 –h10.3.3.3 drds_uat_pcs -p < /home/hy.slq2.2、 进入到目标表所在的数据库，执行source /home/hy.sql这两种方式大同小异，第一种稍微快些，第二种会有过程在屏幕上展示。习惯用第二种 在刚开始导入的时候会一些报错，但是对实际的导入不会影响 3、具体测试数据量： mysql> select count(*) from t_pcs_bill_recieve_huayutest ; count(*) 78322075 1、 一个工作流，分十个同步任务执行，每个同步任务where条件对应3天，每一个同步任务对应一个分区，共10个分区最大速率上线10M/s，结构图： 各个任务耗时：1、========================================================================任务启动时刻 : 2018-03-28 11:47:15 任务结束时刻 : 2018-03-28 11:57:28 任务总计耗时 : 612s 任务平均流量 : 1.69MB/s 记录写入速度 : 20753rec/s 读出记录总数 : 12638734 读写失败总数 : 02、========================================================================任务启动时刻 : 2018-03-28 11:47:15 任务结束时刻 : 2018-03-28 11:56:18 任务总计耗时 : 543s 任务平均流量 : 1.14MB/s 记录写入速度 : 14038rec/s 读出记录总数 : 7580943 读写失败总数 : 03、========================================================================任务启动时刻 : 2018-03-28 11:47:17 任务结束时刻 : 2018-03-28 11:56:23 任务总计耗时 : 545s 任务平均流量 : 1.14MB/s 记录写入速度 : 14004rec/s 读出记录总数 : 7576202 读写失败总数 : 04、========================================================================任务启动时刻 : 2018-03-28 11:47:17 任务结束时刻 : 2018-03-28 11:56:26 任务总计耗时 : 548s 任务平均流量 : 1.13MB/s 记录写入速度 : 13933rec/s 读出记录总数 : 7579645 读写失败总数 : 05、========================================================================任务启动时刻 : 2018-03-28 11:47:10 任务结束时刻 : 2018-03-28 11:56:17 任务总计耗时 : 546s 任务平均流量 : 1.14MB/s 记录写入速度 : 13979rec/s 读出记录总数 : 7576799 读写失败总数 : 06、========================================================================任务启动时刻 : 2018-03-28 11:47:15 任务结束时刻 : 2018-03-28 11:56:28 任务总计耗时 : 553s 任务平均流量 : 1.12MB/s 记录写入速度 : 13774rec/s 读出记录总数 : 7575981 读写失败总数 : 07、========================================================================任务启动时刻 : 2018-03-28 11:47:17 任务结束时刻 : 2018-03-28 11:56:18 任务总计耗时 : 541s 任务平均流量 : 1.15MB/s 记录写入速度 : 14115rec/s 读出记录总数 : 7579924 读写失败总数 : 08、========================================================================任务启动时刻 : 2018-03-28 11:47:17 任务结束时刻 : 2018-03-28 11:56:18 任务总计耗时 : 541s 任务平均流量 : 1.15MB/s 记录写入速度 : 14115rec/s 读出记录总数 : 7579924 读写失败总数 : 0 9、========================================================================任务启动时刻 : 2018-03-28 11:47:15 任务结束时刻 : 2018-03-28 11:56:27 任务总计耗时 : 551s 任务平均流量 : 1.13MB/s 记录写入速度 : 13856rec/s 读出记录总数 : 7579604 读写失败总数 : 010、========================================================================任务启动时刻 : 2018-03-28 11:47:17 任务结束时刻 : 2018-03-28 11:55:20 任务总计耗时 : 482s 任务平均流量 : 877.38KB/s 记录写入速度 : 10522rec/s 读出记录总数 : 5050713 读写失败总数 : 0 整体耗时：630秒  2、 一个工作流，分五个同步任务执行，每个同步任务where条件对应6天，每一个同步任务对应一个分区，共5个分区最大速率上线10M/s，结构图： 任务同步耗时：1、========================================================================任务启动时刻 : 2018-03-28 14:27:49 任务结束时刻 : 2018-03-28 14:37:42 任务总计耗时 : 593s 任务平均流量 : 2.09MB/s 记录写入速度 : 25605rec/s 读出记录总数 : 15158472 读写失败总数 : 02、========================================================================任务启动时刻 : 2018-03-28 14:27:53 任务结束时刻 : 2018-03-28 14:37:36 任务总计耗时 : 583s 任务平均流量 : 2.12MB/s 记录写入速度 : 26093rec/s 读出记录总数 : 15160230 读写失败总数 : 03、========================================================================任务启动时刻 : 2018-03-28 14:27:43 任务结束时刻 : 2018-03-28 14:37:33 任务总计耗时 : 589s 任务平均流量 : 2.10MB/s 记录写入速度 : 25775rec/s 读出记录总数 : 15156082 读写失败总数 : 04、========================================================================任务启动时刻 : 2018-03-28 14:27:45 任务结束时刻 : 2018-03-28 14:37:36 任务总计耗时 : 591s 任务平均流量 : 2.10MB/s 记录写入速度 : 25729rec/s 读出记录总数 : 15154873 读写失败总数 : 05、========================================================================任务启动时刻 : 2018-03-28 14:27:43 任务结束时刻 : 2018-03-28 14:38:01 任务总计耗时 : 617s 任务平均流量 : 2.34MB/s 记录写入速度 : 28768rec/s 读出记录总数 : 17692418 读写失败总数 : 0 整体耗时：629秒 3、 一个工作流，分十五个同步任务执行，每个同步任务where条件对应2天，每一个同步任务对应一个分区，共15个分区最大速率上线10M/s，结构图：  总体耗时：623秒 4、 一个工作流，分一个同步任务执行最大速率上线10M/s，结构图： 同步任务耗时：任务启动时刻 : 2018-03-28 15:42:05 任务结束时刻 : 2018-03-28 15:57:16 任务总计耗时 : 910s 任务平均流量 : 7.02MB/s 记录写入速度 : 86257rec/s 读出记录总数 : 78322075 读写失败总数 : 0 总耗时：928秒 5、 一个工作流，分三个同步任务执行最大速率上线10M/s，结构图： 总耗时：699秒 6、 一个工作流，分四个同步任务执行最大速率上线10M/s，结构图： 总耗时：672秒 测试结果： 并发数 10 5 15 1 3 4 总耗时 630秒 629秒 623秒 928秒 699秒 672秒 并发数 1 3 4 5 10 15 总耗时 928秒 699秒 672秒 629秒 630秒 623秒 初步结论：在并发为5、10、15的情况下，同步数据的速率基本没有变化630秒左右，在并发为1、2、3、4的情况下，速率由930秒提升到630秒左右，从而可以得出结论，在并发为5的情况下，同步速率已经达到最大，再增加并发对于速率的增加效果不明显
阿里云ECS安装Oracle11.2.0.4单实例数据库软件一、 环境准备阿里云ECS： 2C 8G 系统类型：centos7.4 Oracle包: 11.2.0.4 二、 云盘挂载这里使用了文件系统：先把云磁盘挂载到 ECS上使用，在 ECS上执行 fdisk -l 检查确认挂载磁盘使用 fdisk 对没有使用的磁盘进行分区，fdisk /dev/vdb ，（图为磁盘为整个分区）按照提示：选择 n，创建一个新的分区，选择作为主分区，分区号 1，开始位置 1，结束位置为最大值，即用整个磁盘作为分区。然后 w 保存退出。格式化成 ext4 分区，执行 mkfs -t ext4 /dev/vdb1修改/etc/fstab，在文件最后加上新加两个分区的定义，保存退出。挂载磁盘，执行mount /dev/vdb1 /oradata三、 系统准备1、检查内存，内存不小于 4GB2、SWAP 分区设置ECS 没有配置 SWAP，不符合 Oracle 官方的系统要求，可申请新的云盘划分 SWAP。（这里避免没必要的坑，没有配置swap分区）3、系统架构确认系统是 64 位还是 32 位，例子里是 64 位。4、/tmp 文件系统检查临时文件系统空间，ECS 默认临时文件系统共享根盘的空间，大小足够，没问题。5、OS版本6、OS内核版本7、系统RPM包系统 rpm 包：以下列出的是 Oracle 需要的系统包，其中红色是 ECS 默认没有安装的，需要单独安装。binutils-2.17.50.0.6compat-libstdc++-33-3.2.3compat-libstdc++-33-3.2.3 (32 bit)elfutils-libelf-0.125elfutils-libelf-devel-0.125gcc-4.1.2gcc-c++-4.1.2glibc-2.5-24glibc-2.5-24 (32 bit)glibc-common-2.5glibc-devel-2.5glibc-devel-2.5 (32 bit)glibc-headers-2.5ksh-20060214libaio-0.3.106libaio-0.3.106 (32 bit)libaio-devel-0.3.106libaio-devel-0.3.106 (32 bit)libgcc-4.1.2libgcc-4.1.2 (32 bit)libstdc++-4.1.2libstdc++-4.1.2 (32 bit)libstdc++-devel 4.1.2make-3.81numactl-devel-0.9.8.x86_64sysstat-7.0.2 使用如下安装命令进行安装， yum install -y 'compat-libstdc++-33' yum install -y 'compat-libstdc++-33.i686' yum install -y elfutils-libelf-devel yum install -y glibc-devel.i686 yum install -y ksh yum install -y libaio.i686 yum install -y libaio-devel yum install -y libaio-devel.i686 注意：安装图形界面时提示，有三个包是ECS不具备的，ignore就可以 8、/etc/sysctl.conf/etc/sysctl.conf，以下参数是推荐最小值，如果现有值大于推荐值，可以不修改。将以下内容添加到该文件的末尾。fs.aio-max-nr = 1048576fs.file-max = 6815744kernel.shmmni = 4096kernel.sem = 250 32000 100 128kernel.shmall = 2097152kernel.shmmax = 6442450944net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048586然后执行：/sbin/sysctl -p 生效，/sbin/sysctl -a 确认是否生效 9、/etc/pam.d/login修改/etc/pam.d/login，增加一行：session required pam_limits.so10、修改selinuxvim /etc/sysocnfig/selinux ，确认一下参数生效SELINUX=disabled 四、 Oracle软件安装配置1、创建用户和组，执行下列命令：groupadd oinstallgroupadd dbauseradd -g oinstall -G dba -d /home/oracle -m oraclepasswd oracle 2、用户 Shell limits 编辑/etc/security/limits.conf，加入如下内容：oracle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536 3、用户环境变量配置 在 oracle 用户的环境变量中增加如下内容： if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65535 fi fiORACLE_BASE=/opt/oracleORACLE_SID=orclORACLE_HOME=$ORACLE_BASE/product/11.2.0PATH=$ORACLE_HOME/bin:$ORACLE_HOME/oracm/bin:$ORACLE_HOME/OPatch:$PATHLD_LIBRARY_PATH=${LD_LIBRARY_PATH:+$LD_LIBRARY_PATH:}$ORACLE_HOME/lib:$ORACLE_HOME/lib32CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlibexport ORACLE_BASE ORACLE_HOME ORA_NLS33 ORACLE_SID PATH LD_LIBRARY_PATH CLASSPATH TNS_ADMIN 4、创建目录 执行以下命令： mkdir -p /opt/oracle/product/11.2.0mkdir -p /opt/oracle/oraInventorymkdir -p /opt/oracle/product/11.2.0/oradatamkdir -p /oradata/archchown -R oracle:oinstall /opt/oraclechown -R oracle:oinstall /oradata/archchmod -R 755 /opt/oraclechmod -R 777 /oradata 5、配置vnc server连接图形界面 参考阿里云文档: https://help.aliyun.com/knowledge_detail/41181.html?spm=5176.11065259.1996646101.searchclickresult.59466dcddVmdJI 6、解压对应的Oracle压缩包 7、通过图形界面的命令行模式，执行命令 su – oracle export DISPLAY=localhost:1 [root@localhost ~]# xhost + access control disabled, clients can connect from any host 8、切换到解压后的目录，执行： ./runInstaller 接下来就是Oracle软件安装图形化界面了 五、 Oracle软件图形化界面安装图有些没有保存，借用的网上的图片进行说明在检查的时候会warning有三个包没有安装，这是ECS的问题，可以ignore 安装时遇到的报错解决方法：查阅资料这是Oracle自带的bug，点击continue，继续安装。后续可以下载对应的安装包以root身份执行orainstRoot.sh和root.sh脚本（注：执行完脚本以后，在点击“ok”）点击close，至此数据库软件就部署完成六、 创建数据库在图形化界面Oracle用户，输入dbca自定义一个数据库为了方便选择使用统一的密码，可以根据自己的需求选择数据库文件的存储路径/oradata没有选择闪回和归档根据需求选择需要安装的数据库组件配置内存SGA和PGA：SGA：大小一般为内存的50%-80%PGA：大小一般为内存的20%左右安装成功后，点击EXIT！完成七、 创建数据库监听通过Oracle用户界面执行netca至此，在ECS上搭建一个简单的Oracle单实例数据库初步完成，刚接触Oracle，部署比MySQL复杂的多。八、 遇到的问题1、 切换到Oracle用户时，环境变量配置错误，然后切回到root用户再切到Oracle用户发现命令已经都不能使用了。解决方法：通过root用户编辑用户，/home/oracle的环境变量，修改正确之后就可以正常使用了。2、 无法连接到Oracle安装界面解决方法：没有设置xhosts，登录到Oracle用户执行命令[root@localhost ~]# export DISPLAY=localhost:1 [root@localhost ~]# xhost + access control disabled, clients can connect from any host 3、 在图形界面安装的时候，弹出来的界面显示不完整，只显示一个标题框解决方法：在启动安装界面的时候使用：安装前，先配置jdk，如果有jdk，然后执行安装命令时指定本地的jdk就可以了运行安装程序时使用 ./runInstaller -jreLoc /usr/lib/jvm/jre-1.8.04、解决方法：Inventory Directory指定的不正确，在linux上 找一下oraInventory文件的正确位置指定一下就可以了5、安装时遇到的报错解决方法：查阅资料这是Oracle自带的bug，点击continue，继续安装。后续可以下载对应的安装包6、选择Oracle database files存放的目录时，选择数据库盘时，提示对数据盘的访问没有权限，解决方法：修改磁盘的访问权限为777 chmod 777 /oradata7、搭建完之后数据库显示有乱码原因：在用户的环境变量中设置的字符集是NLS_LANG= "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"; export NLS_LANG 修改为American_America.zhs16gbk的默认的就可以了
一、安装Redis1、将压缩包导入虚拟机redis-3.2.9.tar2、进入解压后的文件夹执行命令make3、在当前的目录src下有一系列的可执行的文件，这是与redis相关的命令，将可执行的命令添加到PATH变量中。 find . -type f -executable 4、开启redis服务：# redis-server5、连接reids数据库：# redis-cli redis 数据库的使用1、string字符串类型 设置键值对 set key value127.0.0.1:6379> set a chinaitsoftOK 获取键值：127.0.0.1:6379> get a"chinaitsoft" 将key中存储的数字加1127.0.0.1:6379> set b 2OK127.0.0.1:6379> INCR b(integer) 3 将key中存储的数字减1127.0.0.1:6379> get b"6"127.0.0.1:6379> DECR b(integer) 5127.0.0.1:6379> get b"5" 返回key所存储的字符串的长度：127.0.0.1:6379> STRLEN a(integer) 11 链表的使用：在链表的前端添加一个值：127.0.0.1:6379> LPUSH aa shandong(integer) 1在链表的前端添加一个值：127.0.0.1:6379> LPUSH aa jinan(integer) 2在链表的前端添加一个值：127.0.0.1:6379> LPUSH aa changqing(integer) 3获取链表的值（链表的存储是从0开始的）：127.0.0.1:6379> LRANGE aa 0 31) "changqing"2) "jinan"3) "shandong"在链表的后端添加一个值：127.0.0.1:6379> RPUSH aa china(integer) 4 127.0.0.1:6379> LRANGE aa 0 41) "changqing"2) "jinan"3) "shandong"4) "china" 获取redis数据库中所有的键：127.0.0.1:6379> KEYS *1) "aa"2) "c"3) "b"4) "a" 集合的使用：向集合中添加一个值：127.0.0.1:6379> SADD dd 1(integer) 1127.0.0.1:6379> SADD dd 2(integer) 1127.0.0.1:6379> SADD dd 3(integer) 1 向集合中同时添加多个值：127.0.0.1:6379> SADD dd 4 5 6(integer) 3127.0.0.1:6379> SMEMBERS dd1) "2"2) "3"3) "4"4) "5"5) "6" 获取集合的中的所有的成员：127.0.0.1:6379> SMEMBERS dd1) "1"2) "2"3) "3" 移除并返回集合中的一个随机元素：127.0.0.1:6379> SPOP dd"1"127.0.0.1:6379> SMEMBERS dd1) "2"2) "3"获取集合的成员数：127.0.0.1:6379> SCARD dd(integer) 2 返回两个集合的交集：127.0.0.1:6379> SINTER dd cc1) "3" 有序集合（zset）的使用：添加一个数值： 127.0.0.1:6379> ZADD r 0 shandong(integer) 1127.0.0.1:6379> zadd r 0 jinan(integer) 1127.0.0.1:6379> ZADD r 0 changqing(integer) 1显示有序集合某一个区间的值127.0.0.1:6379> ZRANGE r 0 21) "changqing"2) "jinan"3) "shandong"127.0.0.1:6379> ZRANGE r 0 11) "changqing"2) "jinan 显示有序集合中的值的数量：127.0.0.1:6379> ZCARD r(integer) 3127.0.0.1:6379> ZRANGE r 0 11) "changqing"2) "jinan" hash的使用：创建：127.0.0.1:6379> HSET t database "mysql"(integer) 1127.0.0.1:6379> HSET t OS ""(integer) 1127.0.0.1:6379> HSET t OS "linux"(integer) 0获取所有hash表中的字段127.0.0.1:6379> HKEYS t1) "database"2) "OS"获取在hash表中指定key的所有字段和值127.0.0.1:6379> HGETALL t1) "database"2) "mysql"3) "OS"4) "linux" 获取hash表中到的所有值：127.0.0.1:6379> HVALS t1) "mysql"2) "linux" 二、redis集群的搭建 1、分区的概念：分区是分割数据到多个redis实例，每个实例保存的是key的一个子集通过利用多台计算机内存的和值，允许构造出更大的数据库2、分区的类型：1、范围分区，就是映射一定范围的对象到特定的redis实例。2、hash分区。3、redis集群的优势：1、容错性：解决了单台redis数据库的单点故障，2、扩展性:相比单台的redis服务器，在升级性能到的过程中，集群能实现多节点的热部署，不需要停止数据库。3、性能提升：性能的在扩展的时候体现出来。4、集群架构：在redis集群的每一个节点中，都有两个东西，一个是插槽，可以理解为存储两个数值的变量，这个变量的取值范围是0-16383，还有一个相当于一个管理的插件，当我们存取的key到达的时候，redis会根据算法得到一个值，然后用这个值对16383取余，这样就会对应一个编号在0-16383的hash槽。5、集群成员的管理：集群的节点和节点之间每一个节点都会定期到的交换集群内部的信息，并且更新，这些信息包括：IP、PORT、节点的名字、节点的状态、节点的角色。6、集群可用性的判断：每一个节点都会存储这个集群所有主节点和从节点的信息：1、节点之间互相ping进行监听，如果有一半的节点去ping一个节点没有响应，集群就会认为这个节点宕机。然后去连接他的从节点，如果该节点所有的从节点全部挂掉，该集群就会进入fail状态。2、如果该集群的一半以上的主节点宕机，那个该集群就会进入fail状态。无论是否有从节点。这就是redis投票机制，投票过程是该集群的所有master参与。 7、集群的搭建：要让集群正常工作至少3个主节点，所有本次我们使用6个redis节点，分别对应3个主节点，3的从节点：对应的redis节点的IP和端口对应关系：127.0.0.1:7001127.0.0.1:7002127.0.0.1:7003127.0.0.1:7004127.0.0.1:7005127.0.0.1:70067.1、创建该集群需要的目录 mkdir /redis-cluster cd /redis-cluster/ mkdir 7001 7002 7003 7004 7005 70067.2、将redis的配置文件分别拷贝到对应的PORT的文件夹下：[root@zabbix-agent redis-cluster]# cp /software/redis-3.2.9/redis.conf 7001[root@zabbix-agent redis-cluster]# cp 7001/redis.conf 7002[root@zabbix-agent redis-cluster]# cp 7001/redis.conf 7003[root@zabbix-agent redis-cluster]# cp 7001/redis.conf 7004[root@zabbix-agent redis-cluster]# cp 7001/redis.conf 7005[root@zabbix-agent redis-cluster]# cp 7001/redis.conf 70067.3、修改配置文件如下（每一个文件都需要修改）：port 7001 要和对应的目录相对应cluster-enabled yescluster-config-file nodes.confcluster-node-timeout 5000appendonly yes 7.4、分别启动这6个实例：[root@zabbix-agent redis-cluster]# cd 7001[root@zabbix-agent 7001]# lsredis.conf[root@zabbix-agent 7001]# redis-server redis.conf[root@zabbix-agent 7001]# ps -ef |grep redisroot 2328 2297 0 17:40 pts/0 00:00:11 redis-server *:6379root 2358 2335 0 17:43 pts/1 00:00:00 redis-cliroot 2963 1 0 19:25 ? 00:00:00 redis-server 127.0.0.1:7001 [cluster]root 2967 2365 0 19:25 pts/2 00:00:00 grep redis[root@zabbix-agent 7001]# cd ../7002[root@zabbix-agent 7002]# redis-server redis.conf[root@zabbix-agent 7002]# cd ../7003[root@zabbix-agent 7003]# redis-server redis.conf[root@zabbix-agent 7003]# cd ../7004[root@zabbix-agent 7004]# redis-server redis.conf[root@zabbix-agent 7004]# cd ../7005[root@zabbix-agent 7005]# redis-server redis.conf[root@zabbix-agent 7005]# cd ../7006[root@zabbix-agent 7006]# redis-server redis.conf[root@zabbix-agent 7006]# ps -ef|grep redisroot 2328 2297 0 17:40 pts/0 00:00:11 redis-server *:6379root 2358 2335 0 17:43 pts/1 00:00:00 redis-cliroot 2963 1 0 19:25 ? 00:00:00 redis-server 127.0.0.1:7001 [cluster]root 2973 1 0 19:28 ? 00:00:00 redis-server 127.0.0.1:7002 [cluster]root 2978 1 0 19:28 ? 00:00:00 redis-server 127.0.0.1:7003 [cluster]root 2983 1 0 19:28 ? 00:00:00 redis-server 127.0.0.1:7004 [cluster]root 2987 1 0 19:28 ? 00:00:00 redis-server 127.0.0.1:7005 [cluster]root 2991 1 0 19:29 ? 00:00:00 redis-server 127.0.0.1:7006 [cluster]root 2995 2365 0 19:29 pts/2 00:00:00 grep redis 7.5、安装ruby环境因为redis集群的管理工具redis-trib.rb依赖ruby环境，首先安装ruby环境。 yum install ruby yum install rubygems 检查是够安装：[root@zabbix-agent 7006]# rpm -qa |grep rubyruby-rdoc-1.8.7.374-4.el6_6.x86_64ruby-libs-1.8.7.374-4.el6_6.x86_64ruby-irb-1.8.7.374-4.el6_6.x86_64rubygems-1.3.7-5.el6.noarchruby-1.8.7.374-4.el6_6.x86_64 gem 安装redis ruby接口： gem install redis-3.2.1.gem Successfully installed redis-3.2.11 gem installed （表示安装成功）Installing ri documentation for redis-3.2.1...Installing RDoc documentation for redis-3.2.1...7.6、创建集群redis-trib.rb的子命令create命令用于创建集群--replicas则是指定为redis cluster中的每个master节点配置几个slave节点。先master后slave。[root@zabbix-agent src]# redis-trib.rb create --replicas 1 127.0.0.1:7001 127.0.0.1:7002 127.0.0.1:7003 127.0.0.1:7004 127.0.0.1:7005 127.0.0.1:7006 输入yes Creating clusterPerforming hash slots allocation on 6 nodes...Using 3 masters: 127.0.0.1:7001127.0.0.1:7002127.0.0.1:7003Adding replica 127.0.0.1:7004 to 127.0.0.1:7001Adding replica 127.0.0.1:7005 to 127.0.0.1:7002Adding replica 127.0.0.1:7006 to 127.0.0.1:7003M: 661f78d1110107c1b3c458de6d2442e1f84fb4d5 127.0.0.1:7001 slots:0-5460 (5461 slots) masterM: 3d9d07e1bea22e86af2ccbc94b1b01a61ea205bb 127.0.0.1:7002 slots:5461-10922 (5462 slots) masterM: 9c0af3c826817482c32b408727fe07f5bb0e9f9a 127.0.0.1:7003 slots:10923-16383 (5461 slots) masterS: c4d0e9dba23bd897658cda342333ee4d2113d37f 127.0.0.1:7004 replicates 661f78d1110107c1b3c458de6d2442e1f84fb4d5S: a5d9f6909981f35fec475aee87dea78773fd8cfe 127.0.0.1:7005 replicates 3d9d07e1bea22e86af2ccbc94b1b01a61ea205bbS: a98de46385b5124c5426a0f001386bbabdab4e6a 127.0.0.1:7006 replicates 9c0af3c826817482c32b408727fe07f5bb0e9f9aCan I set the above configuration? (type 'yes' to accept): yes Nodes configuration updatedAssign a different config epoch to each nodeSending CLUSTER MEET messages to join the cluster Waiting for the cluster to join... Performing Cluster Check (using node 127.0.0.1:7001) M: 661f78d1110107c1b3c458de6d2442e1f84fb4d5 127.0.0.1:7001 slots:0-5460 (5461 slots) master 1 additional replica(s)S: c4d0e9dba23bd897658cda342333ee4d2113d37f 127.0.0.1:7004 slots: (0 slots) slave replicates 661f78d1110107c1b3c458de6d2442e1f84fb4d5S: a5d9f6909981f35fec475aee87dea78773fd8cfe 127.0.0.1:7005 slots: (0 slots) slave replicates 3d9d07e1bea22e86af2ccbc94b1b01a61ea205bbM: 3d9d07e1bea22e86af2ccbc94b1b01a61ea205bb 127.0.0.1:7002 slots:5461-10922 (5462 slots) master 1 additional replica(s)S: a98de46385b5124c5426a0f001386bbabdab4e6a 127.0.0.1:7006 slots: (0 slots) slave replicates 9c0af3c826817482c32b408727fe07f5bb0e9f9aM: 9c0af3c826817482c32b408727fe07f5bb0e9f9a 127.0.0.1:7003 slots:10923-16383 (5461 slots) master 1 additional replica(s)[OK] All nodes agree about slots configuration. Check for open slots...Check slots coverage... [OK] All 16384 slots covered. 默认的是前三个节点7001 7002 7003为主节点，后三个节点7004 7005 7006为从节点，如果部署在不同的服务器，请根据主从的分布规则，填写IP以及PORT。至此redis集群搭建完成。
浅谈MySQL select count(*) 与 count(1) 最近看到同事在讨论MySQL关于count(1)、count(*)的执行效率等的问题，感兴趣去搜索并且自己做了一些实验，这里只探讨innnodb存储引擎 一、 首先看下MySQL5.6官方文档对于count函数的说明COUNT(expr) Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.…COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL. MySQL官方文档上对于count(1)和count()的比较没有过多的说明，只是说在第一个列为 NOT NULL时,count(1)与count()走同样的优化。 很多人说count(1)比count()快，因为count()操作会多一步转换操作，会将翻译成一个固定值，转换成count(1)类型 通过查阅相关资料发现现在随着MySQL版本的升级count(1)与count()的已经几乎相同。 二、 count(*)或count(1)的执行计划比较1、 表结构创建一个一千多万行记录的表| huayu_test1 | CREATE TABLE huayu_test1 (waybill_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '?????',waybill_no varchar(20) NOT NULL COMMENT '???',post_date datetime NOT NULL COMMENT '????????',order_id varchar(50) DEFAULT NULL COMMENT '?????????',batch_no varchar(50) DEFAULT NULL COMMENT '???',biz_product_id varchar(20) NOT NULL COMMENT '??????',biz_product_no varchar(20) NOT NULL COMMENT '??????',biz_product_name varchar(50) NOT NULL COMMENT '??????',sender_type varchar(20) DEFAULT NULL COMMENT '0 ?? 1????',sender_id varchar(50) DEFAULT NULL COMMENT '??????',sender_no varchar(50) DEFAULT NULL COMMENT '??????(??????)',sender varchar(200) DEFAULT NULL COMMENT '??????',sender_linker varchar(200) DEFAULT NULL,sender_fixtel varchar(50) DEFAULT NULL COMMENT '??????',sender_mobile varchar(50) DEFAULT NULL COMMENT '??????',sender_addr varchar(200) DEFAULT NULL COMMENT '??????',sender_country_no varchar(20) DEFAULT NULL COMMENT '??????',sender_country_name varchar(50) DEFAULT NULL COMMENT '??????',sender_province_no varchar(20) DEFAULT NULL COMMENT '??????',sender_province_name varchar(50) DEFAULT NULL COMMENT '??????',sender_city_no varchar(20) DEFAULT NULL COMMENT '??????',sender_city_name varchar(50) DEFAULT NULL COMMENT '??????',sender_county_no varchar(20) DEFAULT NULL COMMENT '??????',sender_county_name varchar(50) DEFAULT NULL COMMENT '??????',sender_notes varchar(1000) DEFAULT NULL COMMENT '????',receiver_no varchar(50) DEFAULT NULL COMMENT '??????????????',receiver varchar(200) DEFAULT NULL COMMENT '??????',receiver_linker varchar(200) DEFAULT NULL,receiver_fixtel varchar(50) DEFAULT NULL COMMENT '??????',receiver_mobile varchar(50) DEFAULT NULL COMMENT '??????',receiver_addr varchar(200) DEFAULT NULL COMMENT '??????',receiver_country_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_country_name varchar(50) DEFAULT NULL COMMENT '??????',receiver_province_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_province_name varchar(50) DEFAULT NULL COMMENT '??????',receiver_city_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_city_name varchar(50) DEFAULT NULL COMMENT '??????',receiver_county_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_county_name varchar(50) DEFAULT NULL COMMENT '??????',insurance_flag char(1) DEFAULT NULL COMMENT '????????????1:?? 2:?? 3:??',insurance_amount decimal(12,2) DEFAULT NULL COMMENT '??????',pickup_type varchar(20) DEFAULT NULL COMMENT '?????0 ???????1 ??????',payment_mode varchar(20) DEFAULT NULL COMMENT '????(???) 1:??? 2:??? 3:??? 4:??????? 5:?? 6:?/??? 7:???',real_weight decimal(8,0) DEFAULT NULL COMMENT '????',fee_weight decimal(8,0) DEFAULT NULL,volume_weight decimal(8,0) DEFAULT NULL,length decimal(8,0) DEFAULT NULL,width decimal(8,0) DEFAULT NULL,height decimal(8,0) DEFAULT NULL,quantity int(11) DEFAULT NULL,packaging varchar(20) DEFAULT NULL,package_material varchar(20) DEFAULT NULL,goods_desc varchar(200) DEFAULT NULL,contents_quantity int(11) DEFAULT NULL,cod_flag char(1) DEFAULT NULL,cod_amount decimal(12,2) DEFAULT NULL,receipt_flag char(1) DEFAULT NULL,receipt_waybill_no varchar(20) DEFAULT NULL,receipt_fee_amount decimal(12,2) DEFAULT NULL,insurance_premium_amount decimal(12,2) DEFAULT NULL,valuable_flag char(1) DEFAULT NULL,cargo_total_price decimal(12,2) DEFAULT NULL,cargo_total_purchasing_price decimal(12,2) DEFAULT NULL,allow_fee_flag char(1) DEFAULT NULL,is_feed_flag char(1) DEFAULT NULL,manual_fee_type char(1) DEFAULT NULL,fee_date datetime DEFAULT NULL,discount_rate decimal(6,2) DEFAULT NULL,settlement_mode varchar(20) DEFAULT NULL,payment_state char(1) DEFAULT NULL,payment_date datetime DEFAULT NULL,payment_id varchar(50) DEFAULT NULL,manage_org_code varchar(20) DEFAULT NULL,postage_suite_code varchar(20) DEFAULT NULL,fee_area_suite_code varchar(20) DEFAULT NULL,fee_area_code varchar(20) DEFAULT NULL,fee_area_name varchar(20) DEFAULT NULL,is_advance_flag char(1) DEFAULT NULL,deliver_type char(1) DEFAULT NULL,deliver_sign varchar(50) DEFAULT NULL,deliver_date char(1) DEFAULT NULL,deliver_notes varchar(1000) DEFAULT NULL,deliver_pre_date date DEFAULT NULL,battery_flag char(1) DEFAULT NULL,is_jinguan varchar(20) DEFAULT NULL,workbench varchar(20) DEFAULT NULL,electronic_preferential_no varchar(50) DEFAULT NULL,electronic_preferential_amount decimal(12,2) DEFAULT NULL,pickup_attribute char(1) DEFAULT NULL,adjust_type varchar(20) DEFAULT NULL,postage_revoke decimal(12,2) DEFAULT NULL,print_flag char(1) DEFAULT NULL,print_date datetime DEFAULT NULL,print_times int(11) DEFAULT NULL,declare_source varchar(20) DEFAULT NULL,declare_type varchar(20) DEFAULT NULL,declare_curr_code varchar(20) DEFAULT NULL,create_user_name varchar(50) DEFAULT NULL,modify_user_name varchar(50) DEFAULT NULL,volume decimal(8,0) DEFAULT NULL COMMENT '??',contents_type_no varchar(20) DEFAULT NULL COMMENT '??????',contents_type_name varchar(200) DEFAULT NULL COMMENT '??????',contents_weight decimal(8,0) DEFAULT NULL COMMENT '??????',transfer_type varchar(20) DEFAULT NULL COMMENT '????',postage_total decimal(12,2) NOT NULL COMMENT '???=????+????',postage_standard decimal(12,2) DEFAULT NULL COMMENT '????',postage_paid decimal(12,2) DEFAULT NULL COMMENT '????',postage_other decimal(12,2) DEFAULT NULL COMMENT '????',is_deleted char(1) DEFAULT '0' COMMENT '?????n0??n1??',create_user_id bigint(20) DEFAULT NULL COMMENT '???id',gmt_created datetime DEFAULT NULL COMMENT '????',modify_user_id bigint(20) DEFAULT NULL COMMENT '???id',gmt_modified datetime DEFAULT NULL COMMENT '????',reserved1 bigint(20) DEFAULT NULL COMMENT '????1',reserved2 bigint(20) DEFAULT NULL COMMENT '????2',reserved3 bigint(20) DEFAULT NULL COMMENT '????3',reserved4 varchar(200) DEFAULT NULL COMMENT '????4',reserved5 varchar(200) DEFAULT NULL COMMENT '????5',reserved6 varchar(200) DEFAULT NULL COMMENT '????6',reserved7 varchar(200) DEFAULT NULL COMMENT '????7',reserved8 varchar(200) DEFAULT NULL COMMENT '????8',reserved9 datetime DEFAULT NULL COMMENT '????9',reserved10 text COMMENT '????10',logistics_order_no bigint(50) DEFAULT NULL,inner_channel varchar(20) DEFAULT NULL,base_product_id bigint(20) DEFAULT NULL,base_product_no varchar(20) DEFAULT NULL,base_product_name varchar(20) DEFAULT NULL,is_special_marketing char(1) DEFAULT NULL,product_type varchar(20) DEFAULT NULL,biz_product_type varchar(20) DEFAULT NULL,product_reach_area char(1) DEFAULT NULL,contents_attribute char(1) DEFAULT NULL,contents_cargo_no varchar(1000) DEFAULT NULL,cmd_code varchar(20) DEFAULT NULL,manual_charge_reason varchar(200) DEFAULT NULL,time_limit char(1) DEFAULT NULL,io_type varchar(20) DEFAULT NULL,ecommerce_no varchar(20) DEFAULT NULL,waybill_type varchar(20) DEFAULT NULL,pre_waybill_no varchar(50) DEFAULT NULL,post_batch_id varchar(50) DEFAULT NULL,biz_occur_date datetime DEFAULT NULL,post_org_id bigint(20) DEFAULT NULL,post_org_no varchar(50) DEFAULT NULL,org_drds_code varchar(50) DEFAULT NULL,post_org_simple_name varchar(50) DEFAULT NULL,post_org_product_name varchar(20) DEFAULT NULL,post_person_id bigint(20) DEFAULT NULL,post_person_no varchar(50) DEFAULT NULL,post_person_name varchar(50) DEFAULT NULL,post_person_mobile varchar(50) DEFAULT NULL,sender_warehouse_id bigint(20) DEFAULT NULL,sender_warehouse_name varchar(200) DEFAULT NULL,sender_safety_code varchar(50) DEFAULT NULL,sender_im_type varchar(20) DEFAULT NULL,sender_im_id varchar(50) DEFAULT NULL,sender_id_type varchar(20) DEFAULT NULL,sender_id_no varchar(50) DEFAULT NULL,sender_id_encrypted_code varchar(50) DEFAULT NULL,sender_agent_id_type varchar(20) DEFAULT NULL,sender_agent_id_no varchar(50) DEFAULT NULL,sender_id_encrypted_code_agent varchar(50) DEFAULT NULL,sender_addr_additional varchar(200) DEFAULT NULL,sender_district_no varchar(20) DEFAULT NULL,sender_postcode varchar(20) DEFAULT NULL,sender_gis varchar(20) DEFAULT NULL,registered_customer_no varchar(50) DEFAULT NULL,receiver_type char(1) DEFAULT NULL,receiver_id bigint(20) DEFAULT NULL,receiver_warehouse_id bigint(20) DEFAULT NULL,receiver_warehouse_name varchar(200) DEFAULT NULL,receiver_safety_code varchar(50) DEFAULT NULL,receiver_im_type varchar(20) DEFAULT NULL,receiver_im_id varchar(50) DEFAULT NULL,receiver_addr_additional varchar(200) DEFAULT NULL,receiver_district_no varchar(20) DEFAULT NULL,receiver_postcode varchar(20) DEFAULT NULL,receiver_gis varchar(20) DEFAULT NULL,receiver_notes varchar(1000) DEFAULT NULL,customer_manager_id bigint(20) DEFAULT NULL,customer_manager_no varchar(50) DEFAULT NULL,customer_manager_name varchar(50) DEFAULT NULL,salesman_id bigint(20) DEFAULT NULL,salesman_no varchar(50) DEFAULT NULL,salesman_name varchar(50) DEFAULT NULL,order_weight decimal(8,0) DEFAULT NULL,post_org_name varchar(200) DEFAULT NULL, PRIMARY KEY (waybill_id), KEY auto_shard_key_post_date (post_date), KEY auto_shard_key_waybill_no (waybill_no)) ENGINE=InnoDB AUTO_INCREMENT=568964219 DEFAULT CHARSET=utf8 COMMENT='???????' | 注意：该表上有三个索引，（一个主键索引，两个二级索引） PRIMARY KEY (waybill_id), KEY auto_shard_key_post_date (post_date), KEY auto_shard_key_waybill_no (waybill_no) 2、查看表的数据量以及执行计划 1、查看执行计划 mysql> select count(1) from huayu_test1; count(1) 15861881 1 row in set (2.16 sec) mysql> select count(*) from huayu_test1; count(*) 15861881 1 row in set (2.16 sec) mysql> explain select count(1) from huayu_test1G 1. row ** id: 1 select_type: SIMPLE table: huayu_test1 type: index possible_keys: NULL key: auto_shard_key_post_date key_len: 5 ref: NULL rows: 15412951 Extra: Using index 1 row in set (0.00 sec) mysql> explain select count(*) from huayu_test1G 1. row ** id: 1 select_type: SIMPLE table: huayu_test1 type: index possible_keys: NULL key: auto_shard_key_post_date key_len: 5 ref: NULL rows: 15412951 Extra: Using index 1 row in set (0.00 sec) 通过执行计划发现count(1)和count(*)没有区别，后续就不再说明。该查询是走了一个二级索引 2、将该二级索引删除再次执行查询语句 mysql> explain select count(*) from huayu_test1G 1. row ** id: 1 select_type: SIMPLE table: huayu_test1 type: index possible_keys: NULL key: auto_shard_key_waybill_no key_len: 62 ref: NULL rows: 14061153 Extra: Using index 1 row in set (0.00 sec) mysql> explain select count(1) from huayu_test1G 1. row ** id: 1 select_type: SIMPLE table: huayu_test1 type: index possible_keys: NULL key: auto_shard_key_waybill_no key_len: 62 ref: NULL rows: 14061153 Extra: Using index 1 row in set (0.00 sec) mysql> select count(*) from huayu_test1; count(*) 15861881 1 row in set (12.70 sec) mysql> select count(1) from huayu_test1; count(1) 15861881 1 row in set (2.27 sec) mysql> select count(*) from huayu_test1; count(*) 15861881 1 row in set (2.26 sec) 发现执行计划走的是另外一个二级索引，并且执行时间相比第一次略有增加。 （这里还有一个问题，就是删除之前走的那条索引重新执行select count(*)后，第一次执行的时间比较长，后续就正常了） 3、将上一个执行计划中用到的二级索引页删除，执行语句 mysql> explain select count(*) from huayu_test1G 1. row ** id: 1 select_type: SIMPLE table: huayu_test1 type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 14061153 Extra: Using index 1 row in set (0.00 sec) mysql> explain select count(1) from huayu_test1G 1. row ** id: 1 select_type: SIMPLE table: huayu_test1 type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 14061153 Extra: Using index 1 row in set (0.00 sec) mysql> select count(*) from huayu_test1; count(*) 15861881 1 row in set (39.66 sec) mysql> select count(1) from huayu_test1; count(1) 15861881 1 row in set (54.20 sec) mysql> select count(*) from huayu_test1; count(*) 15861881 1 row in set (1 min 6.71 sec) mysql> select count(1) from huayu_test1; count(1) 15861881 1 row in set (1 min 6.90 sec) 这次是采用到了主键索引，花费的时间也是超过了一分多钟 3、原理分析 MySQL查询优化器目标就是为了减少SQL的执行时间，那为什么会用二级索引而不去用主键索引呢，因为在统计行数的操作中涉及到磁盘IO问题，降低磁盘IO问题就大大的减少执行时间。IO带宽是一定的，索引占用的空间越小产生的IO次数就越少。而innodb的主键索引包括key，事务id和rollpointer，而二级索引包括key和主键id，所以使用二级索引的开销会比较少。所以innodb的select count( )操作一般都是通过二级索引来进行统计操作 InnoDB的主键索引采用聚簇索引存储，使用的是B+Tree作为索引结构，但是叶子节点存储的是key和数据本身。 InnoDB的二级索引不使用聚蔟索引，叶子节点存储的是KEY和主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。 三、那count(1)中”1”是什么意思呢？ 1、下面是几个测试： mysql> select count(waybill_id) from huayu_test1; count(waybill_id) 15861881 1 row in set (2.53 sec) mysql> select count(2) from huayu_test1; count(2) 15861881 1 row in set (2.16 sec) mysql> select count(0) from huayu_test1; count(0) 15861881 1 row in set (2.16 sec) mysql> select count(111) from huayu_test1; count(111) 15861881 1 row in set (2.16 sec) mysql> select count(222) from huayu_test1; count(222) 15861881 1 row in set (2.14 sec) mysql> select count(999) from huayu_test1; count(999) 15861881 1 row in set (2.14 sec) mysql> desc huayu_test1g Field Type Null Key Default Extra waybill_id bigint(20) NO PRI NULL auto_increment waybill_no varchar(20) NO MUL NULL post_date datetime NO MUL NULL order_id varchar(50) YES NULL batch_no varchar(50) YES NULL biz_product_id varchar(20) NO NULL biz_product_no varchar(20) NO NULL biz_product_name varchar(50) NO NULL sender_type varchar(20) YES NULL sender_id varchar(50) YES NULL sender_no varchar(50) YES NULL sender varchar(200) YES NULL sender_linker varchar(200) YES NULL sender_fixtel varchar(50) YES NULL sender_mobile varchar(50) YES NULL sender_addr varchar(200) YES NULL sender_country_no varchar(20) YES NULL sender_country_name varchar(50) YES NULL sender_province_no varchar(20) YES NULL sender_province_name varchar(50) YES NULL sender_city_no varchar(20) YES NULL sender_city_name varchar(50) YES NULL sender_county_no varchar(20) YES NULL sender_county_name varchar(50) YES NULL sender_notes varchar(1000) YES NULL receiver_no varchar(50) YES NULL receiver varchar(200) YES NULL receiver_linker varchar(200) YES NULL receiver_fixtel varchar(50) YES NULL receiver_mobile varchar(50) YES NULL receiver_addr varchar(200) YES NULL receiver_country_no varchar(20) YES NULL receiver_country_name varchar(50) YES NULL receiver_province_no varchar(20) YES NULL receiver_province_name varchar(50) YES NULL receiver_city_no varchar(20) YES NULL receiver_city_name varchar(50) YES NULL receiver_county_no varchar(20) YES NULL receiver_county_name varchar(50) YES NULL insurance_flag char(1) YES NULL insurance_amount decimal(12,2) YES NULL pickup_type varchar(20) YES NULL payment_mode varchar(20) YES NULL real_weight decimal(8,0) YES NULL fee_weight decimal(8,0) YES NULL volume_weight decimal(8,0) YES NULL length decimal(8,0) YES NULL width decimal(8,0) YES NULL height decimal(8,0) YES NULL quantity int(11) YES NULL packaging varchar(20) YES NULL package_material varchar(20) YES NULL goods_desc varchar(200) YES NULL contents_quantity int(11) YES NULL cod_flag char(1) YES NULL cod_amount decimal(12,2) YES NULL receipt_flag char(1) YES NULL receipt_waybill_no varchar(20) YES NULL receipt_fee_amount decimal(12,2) YES NULL insurance_premium_amount decimal(12,2) YES NULL valuable_flag char(1) YES NULL cargo_total_price decimal(12,2) YES NULL cargo_total_purchasing_price decimal(12,2) YES NULL allow_fee_flag char(1) YES NULL is_feed_flag char(1) YES NULL manual_fee_type char(1) YES NULL fee_date datetime YES NULL discount_rate decimal(6,2) YES NULL settlement_mode varchar(20) YES NULL payment_state char(1) YES NULL payment_date datetime YES NULL payment_id varchar(50) YES NULL manage_org_code varchar(20) YES NULL postage_suite_code varchar(20) YES NULL fee_area_suite_code varchar(20) YES NULL fee_area_code varchar(20) YES NULL fee_area_name varchar(20) YES NULL is_advance_flag char(1) YES NULL deliver_type char(1) YES NULL deliver_sign varchar(50) YES NULL deliver_date char(1) YES NULL deliver_notes varchar(1000) YES NULL deliver_pre_date date YES NULL battery_flag char(1) YES NULL is_jinguan varchar(20) YES NULL workbench varchar(20) YES NULL electronic_preferential_no varchar(50) YES NULL electronic_preferential_amount decimal(12,2) YES NULL pickup_attribute char(1) YES NULL adjust_type varchar(20) YES NULL postage_revoke decimal(12,2) YES NULL print_flag char(1) YES NULL print_date datetime YES NULL print_times int(11) YES NULL declare_source varchar(20) YES NULL declare_type varchar(20) YES NULL declare_curr_code varchar(20) YES NULL create_user_name varchar(50) YES NULL modify_user_name varchar(50) YES NULL volume decimal(8,0) YES NULL contents_type_no varchar(20) YES NULL contents_type_name varchar(200) YES NULL contents_weight decimal(8,0) YES NULL transfer_type varchar(20) YES NULL postage_total decimal(12,2) NO NULL postage_standard decimal(12,2) YES NULL postage_paid decimal(12,2) YES NULL postage_other decimal(12,2) YES NULL is_deleted char(1) YES 0 create_user_id bigint(20) YES NULL gmt_created datetime YES NULL modify_user_id bigint(20) YES NULL gmt_modified datetime YES NULL reserved1 bigint(20) YES NULL reserved2 bigint(20) YES NULL reserved3 bigint(20) YES NULL reserved4 varchar(200) YES NULL reserved5 varchar(200) YES NULL reserved6 varchar(200) YES NULL reserved7 varchar(200) YES NULL reserved8 varchar(200) YES NULL reserved9 datetime YES NULL reserved10 text YES NULL logistics_order_no bigint(50) YES NULL inner_channel varchar(20) YES NULL base_product_id bigint(20) YES NULL base_product_no varchar(20) YES NULL base_product_name varchar(20) YES NULL is_special_marketing char(1) YES NULL product_type varchar(20) YES NULL biz_product_type varchar(20) YES NULL product_reach_area char(1) YES NULL contents_attribute char(1) YES NULL contents_cargo_no varchar(1000) YES NULL cmd_code varchar(20) YES NULL manual_charge_reason varchar(200) YES NULL time_limit char(1) YES NULL io_type varchar(20) YES NULL ecommerce_no varchar(20) YES NULL waybill_type varchar(20) YES NULL pre_waybill_no varchar(50) YES NULL post_batch_id varchar(50) YES NULL biz_occur_date datetime YES NULL post_org_id bigint(20) YES NULL post_org_no varchar(50) YES NULL org_drds_code varchar(50) YES NULL post_org_simple_name varchar(50) YES NULL post_org_product_name varchar(20) YES NULL post_person_id bigint(20) YES NULL post_person_no varchar(50) YES NULL post_person_name varchar(50) YES NULL post_person_mobile varchar(50) YES NULL sender_warehouse_id bigint(20) YES NULL sender_warehouse_name varchar(200) YES NULL sender_safety_code varchar(50) YES NULL sender_im_type varchar(20) YES NULL sender_im_id varchar(50) YES NULL sender_id_type varchar(20) YES NULL sender_id_no varchar(50) YES NULL sender_id_encrypted_code varchar(50) YES NULL sender_agent_id_type varchar(20) YES NULL sender_agent_id_no varchar(50) YES NULL sender_id_encrypted_code_agent varchar(50) YES NULL sender_addr_additional varchar(200) YES NULL sender_district_no varchar(20) YES NULL sender_postcode varchar(20) YES NULL sender_gis varchar(20) YES NULL registered_customer_no varchar(50) YES NULL receiver_type char(1) YES NULL receiver_id bigint(20) YES NULL receiver_warehouse_id bigint(20) YES NULL receiver_warehouse_name varchar(200) YES NULL receiver_safety_code varchar(50) YES NULL receiver_im_type varchar(20) YES NULL receiver_im_id varchar(50) YES NULL receiver_addr_additional varchar(200) YES NULL receiver_district_no varchar(20) YES NULL receiver_postcode varchar(20) YES NULL receiver_gis varchar(20) YES NULL receiver_notes varchar(1000) YES NULL customer_manager_id bigint(20) YES NULL customer_manager_no varchar(50) YES NULL customer_manager_name varchar(50) YES NULL salesman_id bigint(20) YES NULL salesman_no varchar(50) YES NULL salesman_name varchar(50) YES NULL order_weight decimal(8,0) YES NULL post_org_name varchar(200) YES NULL 188 rows in set (0.00 sec) 通过测试可以发现，count( )中的id，并没有特殊的含义，也不是按照第几列统计行数，因为通过表结构可以发现总共有188个列，但是count(999)跟count(1)的结果是一样的。 2、重新建一个有空值的表来测试： mysql> show create table test2; test2 CREATE TABLE test2 ( id int(11) NOT NULL AUTO_INCREMENT,name varchar(20) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | mysql> select * from test2; id name 1 asd 2 hahah 3 www 4 http 5 java 6 NULL 6 rows in set (0.00 sec) mysql> select count(*) from test2; count(*) 6 1 row in set (0.00 sec) mysql> select count(1) from test2; count(1) 6 1 row in set (0.00 sec) mysql> select count(2) from test2; count(2) 6 1 row in set (0.00 sec) mysql> select count(name) from test2; count(name) 5 1 row in set (0.00 sec) mysql> select count(id) from test2; count(id) 6 1 row in set (0.00 sec) mysql> select count(999) from test2; count(999) 6 1 row in set (0.00 sec)3、通过测试发现，count( 1)中，1只是一个固定值，没有什么具体的意义，更不是指第一个列，也可以看成一个虚值，count(*)和count(1)操作会统计表中列的行数，包括NULL列，count(col)操作会统计指定列的行数，不包括NULL值。
1、 新建一个表，表结构与原表是相同的。建表语句：CREATE TABLE huayu_test1 (waybill_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '?????',waybill_no varchar(20) NOT NULL COMMENT '???',post_date datetime NOT NULL COMMENT '????????',order_id varchar(50) DEFAULT NULL COMMENT '?????????',batch_no varchar(50) DEFAULT NULL COMMENT '???',biz_product_id varchar(20) NOT NULL COMMENT '??????',biz_product_no varchar(20) NOT NULL COMMENT '??????',biz_product_name varchar(50) NOT NULL COMMENT '??????',sender_type varchar(20) DEFAULT NULL COMMENT '0 ?? 1????',sender_id varchar(50) DEFAULT NULL COMMENT '??????',sender_no varchar(50) DEFAULT NULL COMMENT '??????(??????)',sender varchar(200) DEFAULT NULL COMMENT '??????',sender_linker varchar(200) DEFAULT NULL,sender_fixtel varchar(50) DEFAULT NULL COMMENT '??????',sender_mobile varchar(50) DEFAULT NULL COMMENT '??????',sender_addr varchar(200) DEFAULT NULL COMMENT '??????',sender_country_no varchar(20) DEFAULT NULL COMMENT '??????',sender_country_name varchar(50) DEFAULT NULL COMMENT '??????',sender_province_no varchar(20) DEFAULT NULL COMMENT '??????',sender_province_name varchar(50) DEFAULT NULL COMMENT '??????',sender_city_no varchar(20) DEFAULT NULL COMMENT '??????',sender_city_name varchar(50) DEFAULT NULL COMMENT '??????',sender_county_no varchar(20) DEFAULT NULL COMMENT '??????',sender_county_name varchar(50) DEFAULT NULL COMMENT '??????',sender_notes varchar(1000) DEFAULT NULL COMMENT '????',receiver_no varchar(50) DEFAULT NULL COMMENT '??????????????',receiver varchar(200) DEFAULT NULL COMMENT '??????',receiver_linker varchar(200) DEFAULT NULL,receiver_fixtel varchar(50) DEFAULT NULL COMMENT '??????',receiver_mobile varchar(50) DEFAULT NULL COMMENT '??????',receiver_addr varchar(200) DEFAULT NULL COMMENT '??????',receiver_country_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_country_name varchar(50) DEFAULT NULL COMMENT '??????',receiver_province_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_province_name varchar(50) DEFAULT NULL COMMENT '??????',receiver_city_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_city_name varchar(50) DEFAULT NULL COMMENT '??????',receiver_county_no varchar(20) DEFAULT NULL COMMENT '??????',receiver_county_name varchar(50) DEFAULT NULL COMMENT '??????',insurance_flag char(1) DEFAULT NULL COMMENT '????????????1:?? 2:?? 3:??',insurance_amount decimal(12,2) DEFAULT NULL COMMENT '??????',pickup_type varchar(20) DEFAULT NULL COMMENT '?????0 ???????1 ??????',payment_mode varchar(20) DEFAULT NULL COMMENT '????(???) 1:??? 2:??? 3:??? 4:??????? 5:?? 6:?/??? 7:???',real_weight decimal(8,0) DEFAULT NULL COMMENT '????',fee_weight decimal(8,0) DEFAULT NULL,volume_weight decimal(8,0) DEFAULT NULL,length decimal(8,0) DEFAULT NULL,width decimal(8,0) DEFAULT NULL,height decimal(8,0) DEFAULT NULL,quantity int(11) DEFAULT NULL,packaging varchar(20) DEFAULT NULL,package_material varchar(20) DEFAULT NULL,goods_desc varchar(200) DEFAULT NULL,contents_quantity int(11) DEFAULT NULL,cod_flag char(1) DEFAULT NULL,cod_amount decimal(12,2) DEFAULT NULL,receipt_flag char(1) DEFAULT NULL,receipt_waybill_no varchar(20) DEFAULT NULL,receipt_fee_amount decimal(12,2) DEFAULT NULL,insurance_premium_amount decimal(12,2) DEFAULT NULL,valuable_flag char(1) DEFAULT NULL,cargo_total_price decimal(12,2) DEFAULT NULL,cargo_total_purchasing_price decimal(12,2) DEFAULT NULL,allow_fee_flag char(1) DEFAULT NULL,is_feed_flag char(1) DEFAULT NULL,manual_fee_type char(1) DEFAULT NULL,fee_date datetime DEFAULT NULL,discount_rate decimal(6,2) DEFAULT NULL,settlement_mode varchar(20) DEFAULT NULL,payment_state char(1) DEFAULT NULL,payment_date datetime DEFAULT NULL,payment_id varchar(50) DEFAULT NULL,manage_org_code varchar(20) DEFAULT NULL,postage_suite_code varchar(20) DEFAULT NULL,fee_area_suite_code varchar(20) DEFAULT NULL,fee_area_code varchar(20) DEFAULT NULL,fee_area_name varchar(20) DEFAULT NULL,is_advance_flag char(1) DEFAULT NULL,deliver_type char(1) DEFAULT NULL,deliver_sign varchar(50) DEFAULT NULL,deliver_date char(1) DEFAULT NULL,deliver_notes varchar(1000) DEFAULT NULL,deliver_pre_date date DEFAULT NULL,battery_flag char(1) DEFAULT NULL,is_jinguan varchar(20) DEFAULT NULL,workbench varchar(20) DEFAULT NULL,electronic_preferential_no varchar(50) DEFAULT NULL,electronic_preferential_amount decimal(12,2) DEFAULT NULL,pickup_attribute char(1) DEFAULT NULL,adjust_type varchar(20) DEFAULT NULL,postage_revoke decimal(12,2) DEFAULT NULL,print_flag char(1) DEFAULT NULL,print_date datetime DEFAULT NULL,print_times int(11) DEFAULT NULL,declare_source varchar(20) DEFAULT NULL,declare_type varchar(20) DEFAULT NULL,declare_curr_code varchar(20) DEFAULT NULL,create_user_name varchar(50) DEFAULT NULL,modify_user_name varchar(50) DEFAULT NULL,volume decimal(8,0) DEFAULT NULL COMMENT '??',contents_type_no varchar(20) DEFAULT NULL COMMENT '??????',contents_type_name varchar(200) DEFAULT NULL COMMENT '??????',contents_weight decimal(8,0) DEFAULT NULL COMMENT '??????',transfer_type varchar(20) DEFAULT NULL COMMENT '????',postage_total decimal(12,2) NOT NULL COMMENT '???=????+????',postage_standard decimal(12,2) DEFAULT NULL COMMENT '????',postage_paid decimal(12,2) DEFAULT NULL COMMENT '????',postage_other decimal(12,2) DEFAULT NULL COMMENT '????',is_deleted char(1) DEFAULT '0' COMMENT '?????n0??n1??',create_user_id bigint(20) DEFAULT NULL COMMENT '???id',gmt_created datetime DEFAULT NULL COMMENT '????',modify_user_id bigint(20) DEFAULT NULL COMMENT '???id',gmt_modified datetime DEFAULT NULL COMMENT '????',reserved1 bigint(20) DEFAULT NULL COMMENT '????1',reserved2 bigint(20) DEFAULT NULL COMMENT '????2',reserved3 bigint(20) DEFAULT NULL COMMENT '????3',reserved4 varchar(200) DEFAULT NULL COMMENT '????4',reserved5 varchar(200) DEFAULT NULL COMMENT '????5',reserved6 varchar(200) DEFAULT NULL COMMENT '????6',reserved7 varchar(200) DEFAULT NULL COMMENT '????7',reserved8 varchar(200) DEFAULT NULL COMMENT '????8',reserved9 datetime DEFAULT NULL COMMENT '????9',reserved10 text COMMENT '????10',logistics_order_no bigint(50) DEFAULT NULL,inner_channel varchar(20) DEFAULT NULL,base_product_id bigint(20) DEFAULT NULL,base_product_no varchar(20) DEFAULT NULL,base_product_name varchar(20) DEFAULT NULL,is_special_marketing char(1) DEFAULT NULL,product_type varchar(20) DEFAULT NULL,biz_product_type varchar(20) DEFAULT NULL,product_reach_area char(1) DEFAULT NULL,contents_attribute char(1) DEFAULT NULL,contents_cargo_no varchar(1000) DEFAULT NULL,cmd_code varchar(20) DEFAULT NULL,manual_charge_reason varchar(200) DEFAULT NULL,time_limit char(1) DEFAULT NULL,io_type varchar(20) DEFAULT NULL,ecommerce_no varchar(20) DEFAULT NULL,waybill_type varchar(20) DEFAULT NULL,pre_waybill_no varchar(50) DEFAULT NULL,post_batch_id varchar(50) DEFAULT NULL,biz_occur_date datetime DEFAULT NULL,post_org_id bigint(20) DEFAULT NULL,post_org_no varchar(50) DEFAULT NULL,org_drds_code varchar(50) DEFAULT NULL,post_org_simple_name varchar(50) DEFAULT NULL,post_org_product_name varchar(20) DEFAULT NULL,post_person_id bigint(20) DEFAULT NULL,post_person_no varchar(50) DEFAULT NULL,post_person_name varchar(50) DEFAULT NULL,post_person_mobile varchar(50) DEFAULT NULL,sender_warehouse_id bigint(20) DEFAULT NULL,sender_warehouse_name varchar(200) DEFAULT NULL,sender_safety_code varchar(50) DEFAULT NULL,sender_im_type varchar(20) DEFAULT NULL,sender_im_id varchar(50) DEFAULT NULL,sender_id_type varchar(20) DEFAULT NULL,sender_id_no varchar(50) DEFAULT NULL,sender_id_encrypted_code varchar(50) DEFAULT NULL,sender_agent_id_type varchar(20) DEFAULT NULL,sender_agent_id_no varchar(50) DEFAULT NULL,sender_id_encrypted_code_agent varchar(50) DEFAULT NULL,sender_addr_additional varchar(200) DEFAULT NULL,sender_district_no varchar(20) DEFAULT NULL,sender_postcode varchar(20) DEFAULT NULL,sender_gis varchar(20) DEFAULT NULL,registered_customer_no varchar(50) DEFAULT NULL,receiver_type char(1) DEFAULT NULL,receiver_id bigint(20) DEFAULT NULL,receiver_warehouse_id bigint(20) DEFAULT NULL,receiver_warehouse_name varchar(200) DEFAULT NULL,receiver_safety_code varchar(50) DEFAULT NULL,receiver_im_type varchar(20) DEFAULT NULL,receiver_im_id varchar(50) DEFAULT NULL,receiver_addr_additional varchar(200) DEFAULT NULL,receiver_district_no varchar(20) DEFAULT NULL,receiver_postcode varchar(20) DEFAULT NULL,receiver_gis varchar(20) DEFAULT NULL,receiver_notes varchar(1000) DEFAULT NULL,customer_manager_id bigint(20) DEFAULT NULL,customer_manager_no varchar(50) DEFAULT NULL,customer_manager_name varchar(50) DEFAULT NULL,salesman_id bigint(20) DEFAULT NULL,salesman_no varchar(50) DEFAULT NULL,salesman_name varchar(50) DEFAULT NULL,order_weight decimal(8,0) DEFAULT NULL,post_org_name varchar(200) DEFAULT NULL, PRIMARY KEY (waybill_id), KEY auto_shard_key_post_date (post_date), KEY auto_shard_key_waybill_no (waybill_no)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='???????';2、 原表数据量 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='qps_waybill_base_02'; concat(round(sum(data_length/1024/1024/1024),3),'G') 18.636G 1 row in set (0.00 sec) 3、 执行命令insert into 复制表mysql> insert into huayu_test1 select * from qps_waybill_base_02;Query OK, 15861881 rows affected (16 min 8.37 sec)Records: 15861881 Duplicates: 0 Warnings: 0 数据量： mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1'; concat(round(sum(data_length/1024/1024/1024),3),'G') 9.174G 1 row in set (0.00 sec) 4、 执行命令create table as select 复制表mysql> create table huayu_test2 as select * from qps_waybill_base_02;Query OK, 15861881 rows affected (14 min 44.98 sec)Records: 15861881 Duplicates: 0 Warnings: 0 数据量 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test2'; concat(round(sum(data_length/1024/1024/1024),3),'G') 9.818G 1 row in set (0.00 sec) 5、数据行数 mysql> select count(*) from huayu_test2; count(*) 15861881 1 row in set (4.12 sec) mysql> select count(*) from huayu_test1; count(*) 15861881 1 row in set (7.00 sec) mysql> .ibd文件大小对比 ll -h total 44G-rw-rw---- 1 mysql mysql 61 May 3 2017 db.opt-rw-rw---- 1 mysql mysql 51K Feb 2 09:33 huayu_test1.frm-rw-rw---- 1 mysql mysql 12G Feb 2 09:49 huayu_test1.ibd-rw-rw---- 1 mysql mysql 52K Feb 2 09:59 huayu_test2.frm-rw-rw---- 1 mysql mysql 11G Feb 2 10:13 huayu_test2.ibd 6、由于源表是通过inert into select 将实际的生产上的表拼接起来的，现在以新建的huayu_test1为源表进行测试，这个表是通过inert into select 源表 新建的表，数据量如下分别为insert into select 、 create table as select 和 新的源表： mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1_2'; concat(round(sum(data_length/1024/1024/1024),3),'G') 9.472G 1 row in set (0.00 sec) mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1_1'; concat(round(sum(data_length/1024/1024/1024),3),'G') 9.364G 1 row in set (0.00 sec) mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1'; concat(round(sum(data_length/1024/1024/1024),3),'G') 9.174G 1 row in set (0.00 sec) mysql> 从这里看，数据量的大小还是差不多的。7、比较一下：mysql> show table status like 'qps_waybill_base_02'G 1. row ** Name: qps_waybill_base_02 Engine: InnoDB Version: 10 Row_format: Compact Rows: 15109587 Avg_row_length: 1324 Data_length: 20009975808 Max_data_length: 0 Index_length: 1240465408 Data_free: 7340032 Auto_increment: 568964219 Create_time: 2018-01-24 09:42:57 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: ??????? Block_format: Original1 row in set (0.00 sec) mysql> show table status like 'huayu_test1'G 1. row ** Name: huayu_test1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 15412951 Avg_row_length: 639 Data_length: 9850322944 Max_data_length: 0 Index_length: 1268776960 Data_free: 7340032 Auto_increment: 568964219 Create_time: 2018-02-02 09:33:40 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: ??????? Block_format: Original1 row in set (0.01 sec) mysql> show table status like 'huayu_test1_1'G 1. row ** Name: huayu_test1_1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 15310307 Avg_row_length: 656 Data_length: 10054795264 Max_data_length: 0 Index_length: 286179328 Data_free: 4194304 Auto_increment: 15925006 Create_time: 2018-02-02 10:27:47 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Block_format: Original1 row in set (0.00 sec) mysql> show table status like 'huayu_test1_2'G 1. row ** Name: huayu_test1_2 Engine: InnoDB Version: 10 Row_format: Compact Rows: 14912979 Avg_row_length: 681 Data_length: 10170138624 Max_data_length: 0 Index_length: 1307574272 Data_free: 7340032 Auto_increment: 568964219 Create_time: 2018-02-02 10:42:09 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: ??????? Block_format: Original1 row in set (0.00 sec)8、在源表执行了optimize table 之后，数据变成了10G左右，跟复制后的表的数据相近了 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='qps_waybill_base_02'; concat(round(sum(data_length/1024/1024/1024),3),'G') 10.041G 1 row in set (0.00 sec)9、初步结论 1、Create table as 比 insert into select 的速度更快，但是经实验创建表后的数据大小insert into select占用的数据量比较小。Create 是ddl语句，insert 是dml语句，insert的时候每条语句都会产生对应的redo和undo日志，所以相对create 语句是慢一些的。 官方文档说明：Create table as 语句不会复制原表的索引，如果想要复制索引的话，需要指定索引 mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo; 还有可能会发生数据类型的转换。例如，auto_increment列的属性不会保留，varchar列会转换成char列， 但是原表的数据量相比create 和 insert 复制后的表为什么会这么大，而重新复制一个相同的原表后，用新的原表测试后create 和insert 复制后的数据量就差不多了。怀疑是生产上的原表可能有一些多余的东西，或者表在合并的过程中有空隙只是占用表空间，而没有存储数据 。 2、Optimize table：InnoDB表上进行大量插入、更新或删除操作之后。ibd文件，因为它是通过启用innodb_file_per_table选项创建的。重新组织了表和索引，操作系统可以回收磁盘空间。 在delete 很大空间之后，这个空间不会被立即删除，而是等待新的插入的数据进行填充，后续的insert 数据会占用旧的位置，使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片，这个是针对myisam表和archive表的。 对于InnoDB表，Optimize table映射到ALTER TABLE…FORCE，它重新构建表以更新索引统计数据和聚集索引中的空闲未使用空间。在InnoDB表上运行时，optimize table 的时候会报这样的信息：Table does not support optimize, doing recreate + analyze instead。 会自动重建一个表再使用analyze命令进行优化 Analyse table是对表的索引分布进行分析，优化表的索引的性能
MySQL存储引擎性能压测 一、表结构： CREATE TABLE `huayu_tokudb` ( `waybill_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '?????', `waybill_no` varchar(20) NOT NULL COMMENT '???', `post_date` datetime NOT NULL COMMENT '????????', `order_id` varchar(50) DEFAULT NULL COMMENT '?????????', `batch_no` varchar(50) DEFAULT NULL COMMENT '???', `biz_product_id` varchar(20) NOT NULL COMMENT '??????', `biz_product_no` varchar(20) NOT NULL COMMENT '??????', `biz_product_name` varchar(50) NOT NULL COMMENT '??????', `sender_type` varchar(20) DEFAULT NULL COMMENT '0 ?? 1????', `sender_id` varchar(50) DEFAULT NULL COMMENT '??????', `sender_no` varchar(50) DEFAULT NULL COMMENT '??????(??????)', `sender` varchar(200) DEFAULT NULL COMMENT '??????', `sender_linker` varchar(200) DEFAULT NULL, `sender_fixtel` varchar(50) DEFAULT NULL COMMENT '??????', `sender_mobile` varchar(50) DEFAULT NULL COMMENT '??????', `sender_addr` varchar(200) DEFAULT NULL COMMENT '??????', `sender_country_no` varchar(20) DEFAULT NULL COMMENT '??????', `sender_country_name` varchar(50) DEFAULT NULL COMMENT '??????', `sender_province_no` varchar(20) DEFAULT NULL COMMENT '??????', `sender_province_name` varchar(50) DEFAULT NULL COMMENT '??????', `sender_city_no` varchar(20) DEFAULT NULL COMMENT '??????', `sender_city_name` varchar(50) DEFAULT NULL COMMENT '??????', `sender_county_no` varchar(20) DEFAULT NULL COMMENT '??????', `sender_county_name` varchar(50) DEFAULT NULL COMMENT '??????', `sender_notes` varchar(1000) DEFAULT NULL COMMENT '????', `receiver_no` varchar(50) DEFAULT NULL COMMENT '??????????????', `receiver` varchar(200) DEFAULT NULL COMMENT '??????', `receiver_linker` varchar(200) DEFAULT NULL, `receiver_fixtel` varchar(50) DEFAULT NULL COMMENT '??????', `receiver_mobile` varchar(50) DEFAULT NULL COMMENT '??????', `receiver_addr` varchar(200) DEFAULT NULL COMMENT '??????', `receiver_country_no` varchar(20) DEFAULT NULL COMMENT '??????', `receiver_country_name` varchar(50) DEFAULT NULL COMMENT '??????', `receiver_province_no` varchar(20) DEFAULT NULL COMMENT '??????', `receiver_province_name` varchar(50) DEFAULT NULL COMMENT '??????', `receiver_city_no` varchar(20) DEFAULT NULL COMMENT '??????', `receiver_city_name` varchar(50) DEFAULT NULL COMMENT '??????', `receiver_county_no` varchar(20) DEFAULT NULL COMMENT '??????', `receiver_county_name` varchar(50) DEFAULT NULL COMMENT '??????', `insurance_flag` char(1) DEFAULT NULL COMMENT '????????????1:?? 2:?? 3:??', `insurance_amount` decimal(12,2) DEFAULT NULL COMMENT '??????', `pickup_type` varchar(20) DEFAULT NULL COMMENT '?????0 ???????1 ??????', `payment_mode` varchar(20) DEFAULT NULL COMMENT '????(???) 1:??? 2:??? 3:??? 4:??????? 5:?? 6:?/??? 7:???', `real_weight` decimal(8,0) DEFAULT NULL COMMENT '????', `fee_weight` decimal(8,0) DEFAULT NULL, `volume_weight` decimal(8,0) DEFAULT NULL, `length` decimal(8,0) DEFAULT NULL, `width` decimal(8,0) DEFAULT NULL, `height` decimal(8,0) DEFAULT NULL, `quantity` int(11) DEFAULT NULL, `packaging` varchar(20) DEFAULT NULL, `package_material` varchar(20) DEFAULT NULL, `goods_desc` varchar(200) DEFAULT NULL, `contents_quantity` int(11) DEFAULT NULL, `cod_flag` char(1) DEFAULT NULL, `cod_amount` decimal(12,2) DEFAULT NULL, `receipt_flag` char(1) DEFAULT NULL, `receipt_waybill_no` varchar(20) DEFAULT NULL, `receipt_fee_amount` decimal(12,2) DEFAULT NULL, `insurance_premium_amount` decimal(12,2) DEFAULT NULL, `valuable_flag` char(1) DEFAULT NULL, `cargo_total_price` decimal(12,2) DEFAULT NULL, `cargo_total_purchasing_price` decimal(12,2) DEFAULT NULL, `allow_fee_flag` char(1) DEFAULT NULL, `is_feed_flag` char(1) DEFAULT NULL, `manual_fee_type` char(1) DEFAULT NULL, `fee_date` datetime DEFAULT NULL, `discount_rate` decimal(6,2) DEFAULT NULL, `settlement_mode` varchar(20) DEFAULT NULL, `payment_state` char(1) DEFAULT NULL, `payment_date` datetime DEFAULT NULL, `payment_id` varchar(50) DEFAULT NULL, `manage_org_code` varchar(20) DEFAULT NULL, `postage_suite_code` varchar(20) DEFAULT NULL, `fee_area_suite_code` varchar(20) DEFAULT NULL, `fee_area_code` varchar(20) DEFAULT NULL, `fee_area_name` varchar(20) DEFAULT NULL, `is_advance_flag` char(1) DEFAULT NULL, `deliver_type` char(1) DEFAULT NULL, `deliver_sign` varchar(50) DEFAULT NULL, `deliver_date` char(1) DEFAULT NULL, `deliver_notes` varchar(1000) DEFAULT NULL, `deliver_pre_date` date DEFAULT NULL, `battery_flag` char(1) DEFAULT NULL, `is_jinguan` varchar(20) DEFAULT NULL, `workbench` varchar(20) DEFAULT NULL, `electronic_preferential_no` varchar(50) DEFAULT NULL, `electronic_preferential_amount` decimal(12,2) DEFAULT NULL, `pickup_attribute` char(1) DEFAULT NULL, `adjust_type` varchar(20) DEFAULT NULL, `postage_revoke` decimal(12,2) DEFAULT NULL, `print_flag` char(1) DEFAULT NULL, `print_date` datetime DEFAULT NULL, `print_times` int(11) DEFAULT NULL, `declare_source` varchar(20) DEFAULT NULL, `declare_type` varchar(20) DEFAULT NULL, `declare_curr_code` varchar(20) DEFAULT NULL, `create_user_name` varchar(50) DEFAULT NULL, `modify_user_name` varchar(50) DEFAULT NULL, `volume` decimal(8,0) DEFAULT NULL COMMENT '??', `contents_type_no` varchar(20) DEFAULT NULL COMMENT '??????', `contents_type_name` varchar(200) DEFAULT NULL COMMENT '??????', `contents_weight` decimal(8,0) DEFAULT NULL COMMENT '??????', `transfer_type` varchar(20) DEFAULT NULL COMMENT '????', `postage_total` decimal(12,2) NOT NULL COMMENT '???=????+????', `postage_standard` decimal(12,2) DEFAULT NULL COMMENT '????', `postage_paid` decimal(12,2) DEFAULT NULL COMMENT '????', `postage_other` decimal(12,2) DEFAULT NULL COMMENT '????', `is_deleted` char(1) DEFAULT '0' COMMENT '?????\n0??\n1??', `create_user_id` bigint(20) DEFAULT NULL COMMENT '???id', `gmt_created` datetime DEFAULT NULL COMMENT '????', `modify_user_id` bigint(20) DEFAULT NULL COMMENT '???id', `gmt_modified` datetime DEFAULT NULL COMMENT '????', `reserved1` bigint(20) DEFAULT NULL COMMENT '????1', `reserved2` bigint(20) DEFAULT NULL COMMENT '????2', `reserved3` bigint(20) DEFAULT NULL COMMENT '????3', `reserved4` varchar(200) DEFAULT NULL COMMENT '????4', `reserved5` varchar(200) DEFAULT NULL COMMENT '????5', `reserved6` varchar(200) DEFAULT NULL COMMENT '????6', `reserved7` varchar(200) DEFAULT NULL COMMENT '????7', `reserved8` varchar(200) DEFAULT NULL COMMENT '????8', `reserved9` datetime DEFAULT NULL COMMENT '????9', `reserved10` text COMMENT '????10', `logistics_order_no` bigint(50) DEFAULT NULL, `inner_channel` varchar(20) DEFAULT NULL, `base_product_id` bigint(20) DEFAULT NULL, `base_product_no` varchar(20) DEFAULT NULL, `base_product_name` varchar(20) DEFAULT NULL, `is_special_marketing` char(1) DEFAULT NULL, `product_type` varchar(20) DEFAULT NULL, `biz_product_type` varchar(20) DEFAULT NULL, `product_reach_area` char(1) DEFAULT NULL, `contents_attribute` char(1) DEFAULT NULL, `contents_cargo_no` varchar(1000) DEFAULT NULL, `cmd_code` varchar(20) DEFAULT NULL, `manual_charge_reason` varchar(200) DEFAULT NULL, `time_limit` char(1) DEFAULT NULL, `io_type` varchar(20) DEFAULT NULL, `ecommerce_no` varchar(20) DEFAULT NULL, `waybill_type` varchar(20) DEFAULT NULL, `pre_waybill_no` varchar(50) DEFAULT NULL, `post_batch_id` varchar(50) DEFAULT NULL, `biz_occur_date` datetime DEFAULT NULL, `post_org_id` bigint(20) DEFAULT NULL, `post_org_no` varchar(50) DEFAULT NULL, `org_drds_code` varchar(50) DEFAULT NULL, `post_org_simple_name` varchar(50) DEFAULT NULL, `post_org_product_name` varchar(20) DEFAULT NULL, `post_person_id` bigint(20) DEFAULT NULL, `post_person_no` varchar(50) DEFAULT NULL, `post_person_name` varchar(50) DEFAULT NULL, `post_person_mobile` varchar(50) DEFAULT NULL, `sender_warehouse_id` bigint(20) DEFAULT NULL, `sender_warehouse_name` varchar(200) DEFAULT NULL, `sender_safety_code` varchar(50) DEFAULT NULL, `sender_im_type` varchar(20) DEFAULT NULL, `sender_im_id` varchar(50) DEFAULT NULL, `sender_id_type` varchar(20) DEFAULT NULL, `sender_id_no` varchar(50) DEFAULT NULL, `sender_id_encrypted_code` varchar(50) DEFAULT NULL, `sender_agent_id_type` varchar(20) DEFAULT NULL, `sender_agent_id_no` varchar(50) DEFAULT NULL, `sender_id_encrypted_code_agent` varchar(50) DEFAULT NULL, `sender_addr_additional` varchar(200) DEFAULT NULL, `sender_district_no` varchar(20) DEFAULT NULL, `sender_postcode` varchar(20) DEFAULT NULL, `sender_gis` varchar(20) DEFAULT NULL, `registered_customer_no` varchar(50) DEFAULT NULL, `receiver_type` char(1) DEFAULT NULL, `receiver_id` bigint(20) DEFAULT NULL, `receiver_warehouse_id` bigint(20) DEFAULT NULL, `receiver_warehouse_name` varchar(200) DEFAULT NULL, `receiver_safety_code` varchar(50) DEFAULT NULL, `receiver_im_type` varchar(20) DEFAULT NULL, `receiver_im_id` varchar(50) DEFAULT NULL, `receiver_addr_additional` varchar(200) DEFAULT NULL, `receiver_district_no` varchar(20) DEFAULT NULL, `receiver_postcode` varchar(20) DEFAULT NULL, `receiver_gis` varchar(20) DEFAULT NULL, `receiver_notes` varchar(1000) DEFAULT NULL, `customer_manager_id` bigint(20) DEFAULT NULL, `customer_manager_no` varchar(50) DEFAULT NULL, `customer_manager_name` varchar(50) DEFAULT NULL, `salesman_id` bigint(20) DEFAULT NULL, `salesman_no` varchar(50) DEFAULT NULL, `salesman_name` varchar(50) DEFAULT NULL, `order_weight` decimal(8,0) DEFAULT NULL, `post_org_name` varchar(200) DEFAULT NULL, PRIMARY KEY (`waybill_id`), KEY `auto_shard_key_post_date` (`post_date`), KEY `auto_shard_key_waybill_no` (`waybill_no`) ) ENGINE=InnoDB row_format=compressed AUTO_INCREMENT=568964219 DEFAULT CHARSET=utf8 COMMENT='???????'; 二、innodb表的数据大小： mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='qps_waybill_base_02'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 18.636G | +------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select count(*) from qps_waybill_base_02; +----------+ | count(*) | +----------+ | 15861881 | +----------+ 1 row in set (5.91 sec) mysql> 三、存储引擎压缩说明 压缩的理念： 通过提高CPU利用率和节约成本，降低数据库容量及I/O负载，从而使数据吞吐率得到显著提高 压缩表减少了磁盘上数据库的大小，使得用户不必频繁地操作写入和读取便可以访问数据。对于 InnoDB的工作量以及传统的用户表而言（特别是在某些读取密集型的应用中，内存有足够的空间存储常用数据），数据压缩不仅大大减少了数据库所需的存储空间，而且还减少了 I/O的工作量，提高了数据吞吐率，从而节约开销处理成本。节省存储成本固然重要，但是减少 I/O成本更为关键。 通常情况下表结构中包含字符型数据列如char, varchar, text或blob等时，具有较高的压缩率，而一些二进制数据，如整形或浮点型数据列，和一些已经压缩的多媒体文档，如jpeg、jpg、png等格式图片及mp4、avi等格式视频，其压缩率都不会好，再对其进行压缩就是纯粹浪费CPU资源 1、tokudb存储引擎 阿里云数据库 MySQL 5.6 版支持通过 TokuDB 存储引擎压缩数据。经过大量测试表明，数据表从 InnoDB 存储引擎转到 TokuDB 存储引擎后，数据量可以减少 80% 到 90%，除了数据压缩外，TokuDB 存储引擎还支持事务和在线 DDL 操作，可以很好兼容运行于 MyISAM 或 InnoDB 存储引擎上的应用。 Tokudb压缩模式： tokudb_fast: 使用quicklz 库的压缩模式。 tokudb_small: 使用 lzma 库的压缩模式。 tokudb_zlib: （默认）使用 zlib 库的压缩模式，提供了中等级别的压缩比和中等级别的CPU消耗。 tokudb_quicklz: 使用 quicklz 库的压缩模式， 提供了轻量级的压缩比和较低基本的CPU消耗。 tokudb_lzma: 使用lzma库压缩模式，提供了高压缩比和高CPU消耗。 tokudb_uncompressed: 不使用压缩模式。 TokuDB默认压缩算法为zlib，相比而言压缩的性价比非常高。 TokuDB 限制说明 · TokuDB 存储引擎无法支持外键 Foreign Key。 · TokuDB 存储引擎不适用于频繁大量读取的场景。 2、innodb存储引擎 innodb压缩的内部实现 压缩算法 压缩算法采用LZ77，在这个算法下，如果压缩效率好点的话，压缩后的大小和未压缩的数据大小比如在25-50%左右，在这种情况下就会有效地通过消耗一些CPU来减少IO操作，增大吞吐量，可以通过调节压缩程度（innodb_compression_level参数）来权衡压缩比和CPU使用率 innodb_compression_level：默认值为6，可选值0-9，数值越大表示压缩程度越大，消耗的CPU也越多 InnodbDB 限制说明 把innodb_file_per_table设置为1，innodb_file_format需要设置为Barracuda。然后在建新表或修改现有表的语句中加入row_format=compressed key_block_size=8就可以了。也可以仅加入row_format=compressed，这样key_block_size就取默认值8KB了。也可以仅加入key_block_size={1/2/4/8/16}，也会默认开启压缩。 3、myisam存储引擎 myisam压缩后的表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大减少磁盘空间占用，减少磁盘I/O，提升查询性能。 压缩表支持索引，但索引也是只读的,可以使用myisampack对MyISAM表进行压缩（也叫打包）。 因为环境无法创建myisam表，会自动更改为innodb表，所以对于myisam表的压缩能力就没有测试 四、测试结果 1、转换成tokudb后表数据的大小： mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 3.502G | +------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select count(*) from huayu_tokudb; +----------+ | count(*) | +----------+ | 15861881 | +----------+ 1 row in set (3.08 sec) 压缩比：(18.636-3.502)/18.636=81% 2、innodb压缩后，压缩级别为6 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_innodb_compressed'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 4.325G | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> select count(*) from huayu_innodb_compressed; +----------+ | count(*) | +----------+ | 15861881 | +----------+ 1 row in set (5.25 sec) 压缩比：(18.636-4.325)/18.636=76% 3、innodb压缩级别改为9之后，insert的时间比较长，但是数据反而比级别为6的时候更大了。 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_innodb_compressed2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 4.508G | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select count(*) from huayu_innodb_compressed2; +----------+ | count(*) | +----------+ | 15861881 | +----------+ 1 row in set (5.11 sec) 将压缩级别改为2：影响不大 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 4.479G | +------------------------------------------------------+ 1 row in set (0.01 sec) 4、修改tokudb的buffer大小 loose_tokudb_buffer_pool_ratio=70 占用buffer pool的70%，与之前未修改时数据的占用空间大小是一样的 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 3.502G | +-----------------------------------------------------+ 1 row in set (0.01 sec) 5、修改tokudb默认的压缩模式（tokudb_row_format）,由默认的tokudb_zlib,改为tokudb_fast，压缩能力不如默认的zlib库 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 4.347G | +------------------------------------------------------+ 压缩比：(18.636-4.347)/18.636=77% 6、修改tokudb压缩模式为tokudb_small,small库压缩能力超过默认的zlib库 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 2.947G | +------------------------------------------------------+ 1 row in set (0.00 sec) 压缩比：(18.636-2.947)/18.636=84% 7、修改tokudb压缩模式为tokudb_quicklz，使用quicklz库的轻量级压缩模式，确实压缩后数据量比较多，按时消耗的CPU消耗较低 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 4.347G | +------------------------------------------------------+ 1 row in set (0.00 sec) 压缩比：(18.636-4.347)/18.636=77% 8、修改tokudb压缩模式为tokudb_lzma,使用lzma库进行压缩，提供高压缩消耗高的cpu，这个模式同small模式的压缩能力相同 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 2.947G | +------------------------------------------------------+ 1 row in set (0.01 sec) 压缩比：(18.636-2.947)/18.636=84% 9、最后是tokudb_uncompressed模式，这两种结果是通过 1、修改已有数据的表的压缩模式，然后optimize table 的结果 2、新建相同的表，修改压缩模式参数后，插入数据对应的结果 mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 16.431G | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_tokudb2'; +------------------------------------------------------+ | concat(round(sum(data_length/1024/1024/1024),3),'G') | +------------------------------------------------------+ | 16.187G | +------------------------------------------------------+ 1 row in set (0.00 sec) 测试结果概览： innodb表源数据大小 innodb压缩（级别6） innodb压缩（级别9） innodb压缩（级别2） 18.636G 4.325G 4.508G 4.479G tokudb（tokudb_zlib） tokudb（tokudb_zlib）设置tokudb_buffer_poo70% tokudb（tokudb_fast） tokudb（tokudb_small） 3.502G 3.502G 4.347G 2.947G tokudb（tokudb_quicklz） tokudb（tokudb_lzma） tokudb（tokudb_uncompressed） 4.347G 2.947G 16.431G 五、个人总结 如果CPU有较高的性能空间，而且内存中有足够的空间来缓存数据，那么建议使用存储引擎压缩的功能。如果表中的数据不是大量的读取数据的，那么建议使用tokudb存储引擎进行压缩，性能提升会非常明显，但是tokudb不支持外键。否则，可以使用innodb存储引擎进行压缩。myisam存储引擎就先不考虑了，因为它不支持事务，并发低，并且数据存储也不安全。
关于MySQLslap压测工具----MySQL自带的也测工具 官方文档解释： http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html mysqlslap — Load Emulation Client mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server. Invoke mysqlslap like this: shell> mysqlslap [options] Some options such as --create or --query enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the --delimiter option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file; mysqlslap does not understand them. --concurrency 并发数量 --engines 要测试的引擎 --iterations 运行测试多少次。 --auto-generate-sql 用系统自己生成的SQL脚本来测试。 --auto-generate-sql-load-type 要测试的是读还是写还是两者混合的（read,write,update,mixed） --number-of-queries 总共要运行多少次查询 --debug-info 代表要额外输出CPU以及内存的相关信息。 --number-int-cols ： 创建测试表的 int 型字段数量 --auto-generate-sql-add-autoincrement : 代表对生成的表自动添加auto_increment列，从5.1.18版本开始 --number-char-cols 创建测试表的 char 型字段数量。 --create-schema 测试的schema，MySQL中schema也就是database。 --query 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。 --only-print 如果只想打印看看SQL语句是什么，可以用这个选项。 测试过程： ###################################################################################################### 一.准备环境 1.1 环境如下： ###################################################################################################### RDS: 48G 100G 主实例：drds_xncs_master 10.3.244.2 物理机ip：10.3.162.244 只读实例： drds_xncs_readonly_1 drds_xncs_master 10.3.224.68 物理机ip：10.3.162.156 drds_xncs_readonly_2 drds_xncs_master 10.3.225.153 物理机ip：10.3.162.206 drds_xncs_readonly_3 drds_xncs_master 10.3.224.74 物理机ip：10.3.162.216 drds_xncs_readonly_4 drds_xncs_master 10.3.224.3 物理机ip：10.3.162.200 drds_xncs_readonly_5 drds_xncs_master 10.3.231.6 物理机ip：10.3.162.239 DRDS: 64C 128G drds_xncs_bic_cs 10.3.244.137 ECS:16C 64G ecs_xncs_cs_1 10.3.244.148 192.168.130.115 ecs_xncs_cs_2 10.3.244.149 192.168.35.24 ecs_xncs_cs_3 10.3.244.150 192.168.36.52 ######################################################################################################### 1.2 创建测试表： 在DRDS建立非分库分表的测试表： CREATE TABLE `bic_base_org` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '组织机构id', `inst_id` bigint(20) DEFAULT NULL COMMENT 'ERP机构表唯一标识，根据INST_ID来识别是否新增或更新机构信息', `code` varchar(20) DEFAULT NULL COMMENT '机构唯一标识代码，需符合编码规则', `name_cn` varchar(200) DEFAULT NULL COMMENT '中文名称', `aic_register_name` varchar(200) DEFAULT NULL COMMENT '机构的工商注册名称', `postcode` varchar(20) DEFAULT NULL COMMENT '类型允许为空值，开发、测试、uat都要更改', `administrative_division` varchar(20) DEFAULT NULL COMMENT '机构的国家标准行政区域代码，6位编码', `province_code` varchar(20) DEFAULT NULL COMMENT '省行政区划编码', `city_code` varchar(50) DEFAULT NULL COMMENT '地市行政区划编码', `status` varchar(20) NOT NULL COMMENT '标示组织机构的生效/失效状态', `business_unit` varchar(20) NOT NULL COMMENT '标示该机构归属：邮政，速递', `org_level` varchar(20) NOT NULL COMMENT '标示机构所属层级：集团、板块总部、省分公司、地市（州）分公司、区县（旗）分公司、支局（所、网点）', `org_category` varchar(20) DEFAULT NULL COMMENT '标示机构所属分类：营业、大宗收寄、网运、投递、集邮、分销、报刊、代理速递', `manage_level` varchar(20) DEFAULT NULL COMMENT '标示机构机构管控级别：一体化专业、一体化现业、非一体化', `parent_org_code` varchar(20) DEFAULT NULL COMMENT '上级机构的机构代码，需符合编码规则', `erp_parent_org_code` varchar(20) DEFAULT NULL COMMENT 'erp上级行政机构代码', `distribution_org_flag` varchar(20) DEFAULT NULL COMMENT '一级中心局/二级中心局/三级中心局/市邮件处理中心/县邮件处理中心/不适用。速递无需此字段；邮务需此字段。', `legal_entity_flag` char(1) DEFAULT NULL COMMENT '机构是否为法人单位', `address` varchar(200) DEFAULT NULL COMMENT '机构地址', `approve_create_date` datetime DEFAULT NULL COMMENT '机构批准成立日期', `source_org_create_date` datetime DEFAULT NULL COMMENT '源系统机构创建日期', `major` varchar(20) DEFAULT NULL COMMENT '组织机构所处专业', `org_phase` varchar(20) DEFAULT NULL COMMENT '组织机构所处环节', `main_category` varchar(20) DEFAULT NULL COMMENT '管理机构：总部、省公司、总部直管单位、省管单位、地市公司、地市管单位；生产机构：速递物流揽投机构、邮件处理机构、客户服务机构、电商与物流生产机构、运输类机构、挂靠类机构；其他邮务无需此字段', `detail_category` varchar(20) DEFAULT NULL COMMENT 'A：管理机构： 1.总部：总部下级 2.省公司：省公司 \n3.总部直管单位：总部直管区域分公司，总部直管子公司，总部直管控股公司，总部直管海外公司、总部直管全国性邮件处理中心 \n4.地市公司：地市公司 \n5.省管单位：省直属专业分公司、省直属邮件处理中心、省直属营业部、省管区域分公司、省管地市区域一体化公司、省直管计划单列县、省管长期投资股权公司、其他省管机构 6.地市管单位：地市直属专业分公司、地市直属邮件处理中心、地市管区域分公司、地市管县营业部、地市管长期投资股权公司，其他地市直属机构，其他地市管机构，其他地市直属管理中心 B：生产机构：在生产机构大类基础上细化的分类。\n1.速递物流揽投：A、B、C、D\n2.邮件处理：1级、2级、3级\n3.客服服务：区域客户服务中心\n4.电商与物流生产：电商与物流业务项目组、供应链管理中心\n5.运输：运输公司、运输队\n6.挂靠：大客户营销中心，调度室，客户服务中心（本地性），财务共享中心，网运支撑中心\n邮务无需此字段', `business_function` varchar(50) DEFAULT NULL COMMENT '类型允许为空值，开发、测试、uat都要更改', `core` varchar(20) DEFAULT NULL COMMENT '1城市核心，2城市非核心，3辖县核心，4辖县非核心\n邮务无需此字段', `corporation_flag` char(1) DEFAULT NULL COMMENT '值为是或否', `department_flag` char(1) DEFAULT NULL COMMENT '值为是或否', `company_code` varchar(20) DEFAULT NULL COMMENT '是否部门项为是的才有所属公司', `company_name` varchar(200) DEFAULT NULL COMMENT '财务-所属公司名称', `common_service` char(1) DEFAULT NULL COMMENT '机构是否适用于普遍服务', `branch_emp_relationship` varchar(20) DEFAULT NULL COMMENT '代办/自办/不适用。邮务需此字段；速递无需此字段。', `branch_urban_type` varchar(20) DEFAULT NULL COMMENT '城市/农村/不适用。速递无需此字段；邮务需此字段。', `branch_func_type` varchar(20) DEFAULT NULL COMMENT '纯邮政/综合/纯金融/不适用。速递无需此字段；邮务需此字段', `branch_invest_type` varchar(20) DEFAULT NULL COMMENT '营业/营投合一/投递/不适用。速递无需此字段；邮务需此字段。', `erp_branch_invest_type` varchar(20) DEFAULT NULL COMMENT 'ERPERP网点营投类型, 开发、测试、UAT、生产都要添加', `create_date` datetime DEFAULT NULL COMMENT 'ERP系统记录的创建时间', `modify_date` datetime DEFAULT NULL COMMENT 'ERP系统记录的最后更新时间', `create_user_id` bigint(20) DEFAULT NULL COMMENT '创建人id', `gmt_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `modify_user_id` bigint(20) DEFAULT NULL COMMENT '修改人id', `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', `is_deleted` char(1) DEFAULT '0' COMMENT '是否删除：\n0：否\n1：是', PRIMARY KEY (`id`), KEY `idx_parent_org_code` (`parent_org_code`), KEY `idx_code` (`code`), KEY `idx_name_cn` (`name_cn`), KEY `idx_business_unit` (`business_unit`) ) ENGINE=InnoDB AUTO_INCREMENT=191001 DEFAULT CHARSET=utf8 COMMENT='组织机构基础信息(ERP)'; 1.3 导入测试数据（从生产全部导出并导入） mysql -udrds_xncs_bic_cs -p -h10.3.244.137 -P3306 drds_xncs_bic_cs < rds_jdsc_bic_bic_base_org_insert.sql 1.4 查看表大小 mysql> select count(*) from bic_base_org; +----------+ | count(*) | +----------+ | 191087 | +----------+ 1 row in set (0.03 sec) 1.5 查看数据 mysql> select id,inst_id,code,name_cn,aic_register_name,postcode,administrative_division,province_code,city_code,status,business_unit,org_level,org_category,manage_level,parent_org_code,erp_parent_org_code from bic_base_org where id=170000; +--------+---------+----------+--------------------------------------------------------------------+-------------------+----------+-------------------------+---------------+-----------+--------+---------------+-----------+--------------+--------------+-----------------+---------------------+ | id | inst_id | code | name_cn | aic_register_name | postcode | administrative_division | province_code | city_code | status | business_unit | org_level | org_category | manage_level | parent_org_code | erp_parent_org_code | +--------+---------+----------+--------------------------------------------------------------------+-------------------+----------+-------------------------+---------------+-----------+--------+---------------+-----------+--------------+--------------+-----------------+---------------------+ | 170000 | 245313 | 54310302 | 中国邮政储蓄银行股份有限公司苍梧县新地营业所 | NULL | 543103 | 450421 | 45 | 4504 | 0 | A | 06 | NULL | NULL | 54316100 | 54316100 | +--------+---------+----------+--------------------------------------------------------------------+-------------------+----------+-------------------------+---------------+-----------+--------+---------------+-----------+--------------+--------------+-----------------+---------------------+ 1 row in set (0.01 sec) 1.6 执行计划 mysql> explain select id,inst_id,code,name_cn,aic_register_name,postcode,administrative_division,province_code,city_code,status,business_unit,org_level,org_category,manage_level,parent_org_code,erp_parent_org_code from bic_base_org where id=170000; +------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+ | GROUP_NAME | SQL | PARAMS | +------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+ | DRDS_XNCS_BIC_CS_1513840820139DMOXDRDS_XNCS_BIC_CS_CJWT_0000_RDS | select id,inst_id,code,name_cn,aic_register_name,postcode,administrative_division,province_code,city_code,status,business_unit,org_level,org_category,manage_level,parent_org_code,erp_parent_org_code from bic_base_org where id=170000 | {} | +------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+ 1 row in set (0.00 sec) mysql> explain execute select id,inst_id,code,name_cn,aic_register_name,postcode,administrative_division,province_code,city_code,status,business_unit,org_level,org_category,manage_level,parent_org_code,erp_parent_org_code from bic_base_org where id=170000; +----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | bic_base_org | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL | +----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.01 sec) 可以看到 ，在0号db上执行，并且执行计划是主键查询，最快的方式 ############################################################################################################################################ 二.测试语句 2.1 测试语句： vi hebin_select.sql select id,inst_id,code,name_cn,aic_register_name,postcode,administrative_division,province_code,city_code,status,business_unit,org_level,org_category,manage_level,parent_org_code,erp_parent_org_code from bic_base_org where id=170000; 2.2 测试工具： mysqlslap ############################################################################################################################################# 三.开始测试 3.1 DRDS上测试（把其中一个slave读设100% 测drds下 一个备库 ） 100并发 总共跑 1千万条相同的主键查询： mysqlslap --query=/home/hebin/hebin_select.sql --concurrency=100 --number-of-queries=10000000 --iterations=1 --create-schema=drds_xncs_bic_cs --engine=innodb -h10.3.244.137 -udrds_xncs_bic_cs -p Benchmark Running for engine innodb Average number of seconds to run all queries: 235.556 seconds Minimum number of seconds to run all queries: 235.556 seconds Maximum number of seconds to run all queries: 235.556 seconds Number of clients running queries: 100 Average number of queries per client: 100000 3.2 RDS上主库测试 100并发 总共跑 1千万条相同的主键查询： mysqlslap --query=/home/hebin/hebin_select.sql --concurrency=100 --number-of-queries=10000000 --iterations=1 --create-schema=drds_xncs_bic_cs_trou_0000 --engine=innodb -h10.3.244.2 -udrds_xncs_master -p Benchmark Running for engine innodb Average number of seconds to run all queries: 154.738 seconds Minimum number of seconds to run all queries: 154.738 seconds Maximum number of seconds to run all queries: 154.738 seconds Number of clients running queries: 100 Average number of queries per client: 100000 qps=10000000/154.738=64625.3667489563 3.3 RDS上其中一个只读库测试 100并发 总共跑 1千万条相同的主键查询： mysqlslap --query=/home/hebin/hebin_select.sql --concurrency=100 --number-of-queries=10000000 --iterations=1 --create-schema=drds_xncs_bic_cs_trou_0000 --engine=innodb -h10.3.225.153 -udrds_xncs_master -p Benchmark Running for engine innodb Average number of seconds to run all queries: 150.370 seconds Minimum number of seconds to run all queries: 150.370 seconds Maximum number of seconds to run all queries: 150.370 seconds Number of clients running queries: 100 Average number of queries per client: 100000 qps=10000000/150.370=66502.62685376072 3.4 DRDS上测试（5个只读每个开20%） 100并发 总共跑 1千万条相同的主键查询： mysqlslap --query=/home/hebin/hebin_select.sql --concurrency=100 --number-of-queries=10000000 --iterations=1 --create-schema=drds_xncs_bic_cs --engine=innodb -h10.3.244.137 -udrds_xncs_bic_cs -p Benchmark Running for engine innodb Average number of seconds to run all queries: 216.412 seconds Minimum number of seconds to run all queries: 216.412 seconds Maximum number of seconds to run all queries: 216.412 seconds Number of clients running queries: 100 Average number of queries per client: 100000 qps=10000000/216.412=46208.15851246696 3.5 DRDS上测试（5个只读每个开20%） 1000并发 总共跑 1亿条相同的主键查询： mysqlslap --query=/home/hebin/hebin_select.sql --concurrency=1000 --number-of-queries=100000000 --iterations=1 --create-schema=drds_xncs_bic_cs --engine=innodb -h10.3.244.137 -udrds_xncs_bic_cs -p Benchmark Running for engine innodb Average number of seconds to run all queries: 1790.584 seconds Minimum number of seconds to run all queries: 1790.584 seconds Maximum number of seconds to run all queries: 1790.584 seconds Number of clients running queries: 1000 Average number of queries per client: 100000 qps 大约 100000000/1790=55865.9217877095 *****以上数据考虑到单台ECS性能并发量的局限性，于是采用多台ECS并发测试，通过DRDS和RDS控制台观察数据*************************** 3.6 DRDS上测试（5个只读每个开20%） 共10000并发，两个ECS执行分别5000 总共跑 20亿条相同的主键查询： mysqlslap --query=/tmp/huayu.sql --concurrency=5000 --number-of-queries=1000000000 --iterations=1 --create-schema=drds_xncs_bic_cs --engine=innodb -h10.3.244.137 -udrds_xncs_bic_cs -p 物理RT：1.1ms左右 DRDS：QPS：大约125000左右 RDS上SQL的执行时间：100微秒左右 3.7 DRDS上测试（5个只读每个开20%） 共15000并发，三个ECS执行分别5000 总共跑 30亿条相同的主键查询： DRDS：QPS：大约210000左右 RDS上SQL的执行时间：100微秒左右 物理RT：2.1ms左右 3.8 RDS上测试（5个只读每个开20%） 共1500并发，两个ECS执行分别1000,500 总共跑 20亿条相同的主键查询： mysqlslap --query=/tmp/huayu.sql --concurrency=1000 --number-of-queries=1000000000 --iterations=1 --create-schema=drds_xncs_bic_cs --engine=innodb -h10.3.244.137 -udrds_xncs_bic_cs -p 连接数：75%（1500），RDS最大连接数为2000. RDS:QPS:接近70000 CPU：接近100% SQL的执行时间：几百-几万微妙 ECS的网络：已经接近瓶颈 3.9 RDS上测试（5个只读每个开20%） 共1900并发，三个个ECS执行分别1000，500，400 总共跑 30亿条相同的主键查询 连接数：1900. RDS:QPS:接近70000 Cpu：接近100% SQL的执行时间：几百-几万微妙 RDS的QPS已经到达极限 ECS的网络：接近瓶颈 ################################################################################################################ 四.测试总结： 单台ECS相对并发量较低的测试： 1.RDS 只读库和主库 性能基本相同 2.单独开一个只读的 DRDS 耗时比 单rds写库或者单rds只读库 相差较大 154/235 = 65% 大约损失35%的性能 3.开满5个只读的 DRDS 耗时比 单rds写库或者单rds只读库 相差较大 154/216 = 71% 大约损失29%的性能 4.单rds的qps是6w6左右，最高性能的drds提高并发后最多达到5w5左右 所以 17% 左右的性能损耗 多台ECS，高并发量，DRDS、RDS最大吞吐量测试： 5.DRDS开满5个只读实例，三台ECS到达15000的并发，DRDS的QPS可以达到210000左右 6.RDS主实例开5个只读实例，两台ECS接近75%（1500）的并发，RDS的QPS可以接近70000。再次提高并发数QPS已经没有提升。可见主实例RDS规格48G内存128G磁盘的最大QPS接近70000。
1ã 首先登录DT，云数据库，通过bic子系统定位到生产上RDS的主实例ID，复制主实例的id到杜康上具体查看RDS的性能问题 2ã 杜康点击实例诊断，实例性能信息，筛选时间12-13 16:30-17:30 的性能信息 a) 磁盘空间、磁盘空间详情：这段时间的数据是一条直线，空间状态都很稳定，没有性能问题。 MySQL RDS磁盘占用包括日志文件（binlog文件、错误日志等），数据文件（数据、索引文件），和一些其他文件（ibdata，logfile_0,临时文件等） 造成 MySQL 实例空间使用率过高，主要有如下四种原因： Binlog 文件占用高。 数据文件占用高。 临时文件占用高。 系统文件占用高。 对应解决方法： 1、定期删除binlog，假如当前dml造成大量的binlog，可以通过RDS控制台即使清理binlog 2、通过truncate或者drop及时清除不需要的表 3、终止对应的回话 4、ibdata中undo占用高可以进行undo分离，或者进行数据转移；增加redo log file的大小和组数 b) IOPS：每秒读写的次数。现在是比较小的。在0-0.2之间。 如果IOPS比较高的话，有可能是以下原因： 1、实例内存满足不了缓存数据或排序等需要，导致产生大量的物理 IO。 2、查询执行效率低，扫描过多数据行。 解决方法： 1、查询是否有慢SQL，优化慢SQL，可以参考杜康的实例卡慢诊断的优化建议，或者登录DMS，通过诊断报告、优化来进行SQL优化 2、终止查询语句 3、通过show processlist，或者DMS控制台、杜康等来kill查询回话id c) MySQL内存使用率：基本上是一条直线，没有变化。因为MySQL有innodb_buffer_pool，大约为物理内存的50%-80%，内存使用率高一些，相对的性能也会提高 d) 物理内存：直线保持基本无变化，物理内存就是实际的内存条的内存大小 e) 连接数：当前连接数在1500左右，后来增高至6000左右。但是活跃连接数一直在个位数，说明现在的空闲连接数过多。总连接数超过参考值2000。出现严重问题。 数据库的连接一般是使用长连接，可能是应用侧的连接池初始连接数设置过高，应用启动后建立多个到RDS的空闲连接 解决方法： 1、长连接建议启用连接池的复用连接功能。 2、对于交互式连接和非交互式连接，建议修改相应的wait_timeout和interactive_timeout参数。（空闲时间超过指定的时间后，RDS的连接会主动关闭）。通过DT，RDS控制台，性能优化，参数设置中修改。 3、kill当前的空闲会话。 f) 线程状态：线程数跟连接数是对应的。此时也是连接的总线程数远大于活跃的线程数。 g) 备库延迟：目前主备延迟(slave-lag)为0. 主备延迟产生的原因： 1ã 主库产生非常大的binlog a) 主库上执行大量的dml语句 b) 主库上执行大事务 c) 主库上没有主键的全表扫描 2ã 主库上执行ddl语句，时间过长 3ã 备库上对myisam表长时间查询，阻塞主库的binlog同步语句 4ã 备库实例的规格配置低，磁盘IO比较低 查看方法: 1ã 首先查看备库的IOPS是否存在瓶颈 2ã 备库show processlist查看是否存在大事务 3ã 主库的写入压力是否过高，dml语句是否过多 4ã 只读节点执行 show slave status \G，判断是否有 Waiting for table metadata lock；同时在主库排查下是否有DDL 操作 5ã 只读节点执行 show slave status \G，判断是否有 Waiting for table level lock; 同时通过 show full processlist; 同时在主库检查下是否有长时间对 MyISAM 引擎表的查询 h) QPS/TPS：QPS比较高，在90000左右，最高到达110000 。每秒的事务数在10000以上。正常，业务量比较高 原因分析： QPS比较高，每秒SQL的语句执行次数高，业务量上来，处于业务的高峰期，用户连接数增加，访问量增加。 如果QPS比较高，逻辑读不高，慢SQL也不是系统的瓶颈，QPS和cpu使用率的变化曲线吻合，这时候优化的余地就不高了，可以从实例规格、应用架构方面进行考虑。 如果QPS不高，查询执行效率低、执行时需要扫描大量表中数据、优化余地大，并且出现慢查询问题，QPS和CPU的变化曲线不吻合 如果QPS比较高，并且逻辑读也比较高，CPU的使用率增加，这时候可以优化优化相应的慢SQL，添加主实例的只读实例来缓解压力。 I ) cpu/mem的使用率：现在cpu的使用率在30%左右，不算高。内存的使用率基本平稳在30%左右，正常 CPU的使用率高的原因： 系统执行应用提交查询（包括数据修改操作）时需要大量的逻 辑读，（逻辑 IO，执行查询所需访问的表的数据行数），需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。造成逻辑读高的原因，很可能是异常SQL，扫描的数据行数过多导致。 j) 慢SQL：慢SQL数量的变化曲线跟CPU的使用率的变化曲线吻合，在CPU使用率高的时候，慢SQL也跟着增加。可以通过杜康对产生的慢SQL进行优化。 K) 全表扫描次数：随着业务量的增加，全表扫描的次数也随之增加。Sql要尽量避免全表扫描 主实例问题与建议： QPS升高，业务量高的情况下，产生一些慢查询SQL，并且空闲连接数太多   1ã 连接数：连接数严重超过参考值，并且有过多的空闲线程。首先检查应用是否使用连接池，如果使用连接池，检查连接池的配置是否合理   2ã 优化慢SQL a) select id , inst_id , code , name_cn , aic_register_name , postcode , administrative_division , province_code , city_code , status , business_unit , org_level , org_category , manage_level , parent_org_code , distribution_org_flag , legal_entity_flag , address , approve_create_date , source_org_create_date , major , org_phase , main_category , detail_category , business_function , core , corporation_flag , department_flag , company_code , company_name , common_service , branch_emp_relationship , branch_urban_type , branch_func_type , branch_invest_type , create_date , modify_date , create_user_id , gmt_created , modify_user_id , gmt_modified , is_deleted from bic_base_org 优化建议：此类SQL没有where条件，一定要添加where条件并且有合适的索引。这样会造成全表扫描影响系统性能。如果一定要执行建议在业务低峰期执行 b) select count ( * ) as cnt from ( select id , jdpt_employee_code , td_employee_code , td_employee_name , td_org_code , td_org_name , td_phone_number , td_id_number , td_employee_status , td_employee_role , create_user_id , gmt_created , modify_user_id , gmt_modified , is_deleted from bic_td_jdpt_employee_relation where :1 = :2 and is_deleted = :3 ) 优化建议：此类SQL扫描行与发送行的比666184，并且查询使用了聚合函数，没有使用where条件。影响服务器性能，SQL锁行过多，可能影响其他更新语句。关联列上添加索引，子查询返回的行数尽量少 个人总结： 针对RDS的问题：CPU占有率持续高、QPS持续高、逻辑读一直高，用户连接线程增加，活跃线程数增加。 一般情况下：有慢SQL的情况，首先优化慢SQL，针对慢SQL主要注意查询多少数据和返回多少数据，如果查询的数据跟反回的数据都比较大，而且执行时间秒级别特别长，很有可能是慢SQL；没有慢SQL，或者慢SQL不是性能主导原因的话，可以考虑实例的规格配置和实例的架构，比如增加主实例的规格配置，增加只读实例缓解主实例的压力等。
邮政Zabbix部署方案 1、架构设计 考虑到ECS的数量多和扩展性问题，中间使用proxy代理。这样既能减轻server端的性能负载问题，又便于扩展，需要扩展ECS监控，可以基于server端新建proxy，子系统的监控通过proxy处理之后再发送给server端。Zabbix Proxy是实现Zabbix分布式监控的重要组成部分，是连接Zabbix Agent和Zabbix Server的“中间人”、“连接器”、“路由器”等。Zabbix Proxy将来自Agent的数据推送（push）到Zabbix Server或者由Zabbix Server来拿取（fetch）。 与Zabbix Server相比Zabbix Proxy不需要使用或安装GUI（Graphical User Interface、Web Interface），也不需要本地管理（Local administration），具有轻量、易于维护的特点。与Zabbix Server还不相同的就是Zabbix Proxy不生成任何用户通知，所有的用户通知都是由Zabbix Server完成的。Zabbix Proxy默认服务端口与Zabbix Server相同（TCP：10051），也需要数据库支持。Agent端占用端口（TCP:10050） 2、邮政实际状况邮政生产环境上一共有子系统16个，大约一共2500台左右的ECS，涉及到监控数量比较多，并且每个子系统的每台ECS的环境也不能保证完全相同，所以计划采用分批监控的方式进行 3、实际方案的部署1）server端：新建一台ECS，保证能够跟所有的ESC相通,存储为200Ga)LNMP环境的配置b)Zabbix_server的软件配置与安装c)Yum公共源的搭建（可以使用邮政的yum源，后来跟运维了解到）d)Mariadb数据库的搭建数据库硬盘空间大小估计：历史记录，事件记录，趋势数据等数据保存时间为1个月，ECS数目为2500，每台的监控项为20，每60秒刷新一次，一条记录大约占50字节需要的硬盘空间大约110G左右，所以计划用200G的硬盘估计依据：http://www.ttlsa.com/zabbix/zabbix-database-space-6-ttlsa/2）Proxy代理端：新建一台ECS，同server端一样，存储为200G计划500台ECS部署一个proxy代理，观察server和proxy的负载情况，再根据实际情况增加或减少每个proxy下ECS的数量a)Mariadb数据库的搭建b)Zabbix_proxy的搭建Proxy为主动模式：主动去请求server端和agent端zabbix_server端当主机数量过多的时候，由Server端去收集数据，Zabbix可能会出现严重的性能问题：1、当被监控端到达一个量级的时候，Web操作很卡，容易出现5022、图层断裂3、开启的进程（Pollar）太多，即使减少item数量，以后加入一定量的机器也会有问题所以下面主要往两个优化方向考虑：1、添加Proxy节点或者Node模式做分布式监控2、调整Agentd为主动模式 根据邮政的实际情况选择采用proxy代理主动模式 配置要点： 启动服务顺序：配置Proxy或Agent时要先在Web中配置Zabbix Server，后启动Proxy或Agent服务；配置顺序：先配置Server后配置Proxy，最后配置Agent3）agent端：涉及到的agent端的数量巨大，所以计划采用pssh分批量处理脚本安装的方法。脚本主要实现：agent端本地yum的编辑、zabbix客户端的搭建（yum安装）、客户端zabbix配置文件的修改并且启动zabbix客户端服务。将脚本的目录通过批量复制到分批的ECS的/tmp目录下，分批执行脚本。 批量执行的问题：1、pssh的使用首先需要配置互信，可以通过脚本将server端与ECS之间配置免密登录，也可以通过跳板机进行pssh的配置，因为跳板机与所有ECS之间是免密登录的（ssh可以直接连接到ECS）2、环境的问题，分批量处理的环境可能有不同，例如Linux系统的版本，安装包的依赖性，这些可以通过脚本找到这些环境出现问题的ECS，进行服务、yum安装的验证，将错误日志导入到一个文件中，针对环境出现问题的ECS分别解决。4） web页面: 目前主要是对ECS的ping和web进行监控 主机的添加，可以通过批量自动发现机器，发现的条件为zabbix_agent端，然后配置动作，将主机添加到群组，并且链接一个ICMP ping模板。web模板zabbix没有自带，可以通过导入web模板，再链接web模板，或者将web监控在ICMP ping模板上添加。链接ping模板之后就自动监控web。这样的话,就要把需要web监控的ECS和不需要web监控的ECS分开来执行添加ping模板,并且需要监控的ECS还要分别于子系统归类处理。 4、回退方案回退方案是针对各个模块进行的回退，哪个模块出现问题就回退哪个模块，如果是想要整体进行回退的话，推荐先回退web界面、agent端、proxy端、server端，倒着进行回退。 1）server端a）对于yum安装的包，可以yum –remove卸载相应的依赖包b）PHP服务的安装文件在/usr/local/php，直接删除这个目录，PHP服务文件/etc/init.d/php-fpm直接删除c）NGINX服务的所有文件在/usr/local/nginx，直接删除这个目录d）MySQL数据库通过yum安装，直接yum remove mariadb-server mariadbe）Zabbix_server安装在/usr/local/zabbix直接删除这个目录，删除zabbix用户和组 groupdel zabbix、userdel zabbix，在/etc/services删除最后四行zabbix的端口配置，删除zabbix_server的服务文件/etc/init.d/zabbix_*f）杀死进程 killall zabbix*,killall php ,killall nginxg）删除本地yum源配置 rm -f /etc/yum.repos.d/local_yum.repo，之前的.repo文件在/etc/yum.repos.d/backup目录里面有备份2）proxy代理端a）安装目录在/usr/local/zabbix，之间删除这个目录，删除服务/etc/init.d/zabbix*b）卸载数据库yum –y remove mariadbserver mariadbc）杀死进程 killall zabbix_proxyd）删除本地yum源配置 rm -f /etc/yum.repos.d/local_yum.repo，之前的.repo文件在/etc/yum.repos.d/backup目录里面有备份3）agent端客户端的安装过程都在脚本里面： !/bin/bash install zabbix-agent script about files in /tmp/os_zabbix-agent 当前时间戳 log_time=date +%Y-%m-%d_%H:%M:%Shost_ip=ifconfig | grep "^eth0" -A 1 |tail -1 |awk '{print $2}' 获取本机IP判断服务器的信息文件是否存在 touch /tmp/zabbix_agent_install.log if ! cat /root/monitor/info.conf >>/dev/null then echo -e "${log_time} ${host_ip} can not find /root/monitor/info.confnlogout ${host_ip}nnn" >>/tmp/zabbix_agent_install.log exit fi 获取本机的信息 ip=cat /root/monitor/info.conf |awk '{print $4}' natip=cat /root/monitor/info.conf |awk '{print $3}' ecs_name=cat /root/monitor/info.conf |awk '{print $2}' 搭建本地yum mkdir /tmp/yum.bakmv /etc/yum.repos.d/* /tmp/yum.bak/mv /tmp/os_zabbix-agent/local_yum.repo /etc/yum.repos.d/yum -y install unixODBC >>/dev/null 判断本地yum是否搭建成功 if ! yum list |grep vim >>/dev/nullthen echo -e "${log_time} ${host_ip} yum repo is error!\nlogout ${host_ip}\n\n\n" >>/tmp/zabbix_agent_install.log fi 判断系统版本 redhat_release_num=cat /etc/redhat-release |awk '{print $4}' | awk -F. '{print $1}'if (( ${redhat_release_num} != 7 ))then echo -e "${log_time} ${host_ip} system version is not 7!\nlogout ${host_ip}\n\n\n" >>/tmp/zabbix_agent_install.log exit fi 安装zabbix客户端，判断是否安装成功 rpm -ivh /tmp/os_zabbix-agent/zabbix-* >>/dev/nullif (( $? != 0 ))then echo -e "${log_time} ${host_ip} rpm -ivh is error!\nlogout ${host_ip}\n\n\n" >>/tmp/zabbix_agent_install exit fi 修改zabbix配置文件 mv /etc/zabbix/zabbix_agentd.conf /tmp/yum.bak/mv /tmp/os_zabbix-agent/zabbix_agentd.conf /etc/zabbix/sed -i "/ServerActive/a Hostname=${host_ip}" /etc/zabbix/zabbix_agentd.confsystemctl start zabbix-agent >>/dev/nullsystemctl enable zabbix-agent >>/dev/null a）zabbix-agent取消开机启动，并且删除zabbix配置文件systemctl disable zabbix-agentrm –rf /etc/zabbix/ b）卸载zabbix客户端rpm –e /tmp/os_zabbix-agent/zabbix-* c）删除本地yum，以及相应的安装文件yum -y remove unixODBCrm –f /etc/yum.repos.d/local_yum.repomv /tmp/yum.bak/*.repo /etc/yum.repos.d/ d）删除创建的错误日志和备份目录、以及传输的安装文件rm –f /tmp/zabbix_agent_install.logrm –rf /tmp/yum.bakrm –rf /tpm/os_zabbix-agent/4） web页面 直接删除创建的主机、动作、自动发现、主机群组、以及ICMP ping模板上对应的网络监控。当然，如果server端进行了回退，web页面也就无法显示了。 5、对生产的影响通过实验的zabbix客户端top命令发现，zabbix客户端对于ECS系统的cpu和内存的影响几乎为0 6、测试测试 7、总结分析由于server端已经搭建完成，之后搭建的难点在：1、agent端的部署，ECS的环境不一样，系统的版本不一样，只通过脚本很难实现将全部的ECS部署好agent端，这就需要手动进行客户端的搭建。2、数据库的性能问题，随着ECS数量的增加，数据库需要扩容和优化,可以将数据库迁移到RDS上，方便管理3、server端的容量问题，ECS数量增加，监控项的增加，需要的磁盘容量就增加4、yum源的问题，由于自己搭建的yum源是centos7的版本的，遇到其他版本的需要单独处理，经过查找发现所有ECS的非centos7的系统不超过10台。遇到centos其他版本的可以使用邮政的yum源（一开始不清楚邮政有5/6/7的yum源），Redhat版本的就需要单独处理了5、proxy端的性能问题，计划每500台ECS搭建一个proxy代理，再根据实际负载情况修改ECS的数量八、出现的问题汇总：1、在proxy端或者server端指定RDS的时候，需要输入RDS的ip，并且端口号不是默认的3306，而是对应的RDS的实际端口号。解决方法：在server和proxy配置文件中指定实际的端口号2、搭建完客户端之后发现server的日志有报错，大体意思就是server端通过端口连接客户端失败解决方法：将server端和proxy端的配置文件中默认端口对应的ip由0.0.0.0改为本机的ip地址。自己的理解是0.0.0.0默认的端口对应所有的ip，而不是邮政的内网ip。3、注意不同vpc下面的ECS是不相通的，所以proxy要跟server端在同一个proxy下面4、可以在proxy端或者server端搭建一个agent客户端用来通过模板检测proxy或者server的性能情况，通过模板发现了zabbix busy discover processes 跟zabbix busy icmp pinger processes 两个参数已经接近100%解决方法：ping和discover进程太忙，于是修改配置文件，将Start discovers =10，Start pingers=20，参数就降下来了。5、在编译安装proxy遇到了报错，mysql library not found解决方法：缺少相应的包mysql-devel，可以通过yum安装，但是在安装过程中发现与系统的版本不一致，于是直接将mariadb*卸载掉重新安装。此类问题都是缺少相应的依赖包，网上搜索找到对应的包安装就可以6、安装客户端的时候注意把客户端的serveractive指定为新的proxy，所以批量安装客户端的时候需要修改serveractive为对应的proxy的ip。7、添加完agent的ICMP ping监控之后，发现没有显示出数据，查看proxy的日志发现没有ping对应的工具解决方法：安装fping安装包8、在给proxy的RDS导数据的时候一定只要导入schema.sql文件，导入其他的文件会报错
发表了文章
2020-05-07
发表了文章
2020-03-27
发表了文章
2019-04-19
发表了文章
2018-11-10
发表了文章
2018-09-29
发表了文章
2018-09-16
发表了文章
2018-08-06
发表了文章
2018-07-23
发表了文章
2018-04-13
发表了文章
2018-04-02
发表了文章
2018-03-14
发表了文章
2018-03-12
发表了文章
2018-03-05
发表了文章
2018-02-09
发表了文章
2018-02-08
发表了文章
2017-12-25
发表了文章
2017-12-19
发表了文章
2017-12-17
