mysql测试经验共享1.0.0.2

1、Mysql测试 FAQ.Mysql测试 FAQ11.测试时候操作11.1innodb的表空间11.2max_allowed_packet选项11.3无法获取更新sql影响的记录条数21.4触发器坏的时候21.5自增列的问题21.6打开数据库的log文件进行测试21.7mysql导出转义字符32.建立同步相关32.1f需要主库的同步信息32.2主从之间必须用不同的server-id32.3必须配置log-bin32.4配置项log-slave-updates32.5四升五必须考var包42.6主从同步中忽略掉指定的表42.7只同步指定的表5Mysql性能的一般结论53.更新sql的性能53.1更

2、新类型sql的最大速度53.2影响更新sql执行时间的主要因素53.3比较短的更新sql63.4比较大的更新sq64.关于mysql检索性能测试74.1索引不止一倍提高检索速度74.2使用in比单条更快74.3比较符号比limit的效率高75.各种数据类型的处理95.1数据类型的转换规则95.2在mysql中慎用引号105.3BIGINT不是INT115.4text列125.5汉字处理131. 测试时候操作1.1 innodb的表空间Q：在测试执行的时候，经常需要环境迁移，拷贝数据库var文件夹，该文件夹下面的ibdata1文件总是很大，可以压缩删除ibdata1文件吗？A：ibdata1文件

3、不能够压缩和删除。线上的mysql5数据库多使用innodb的存储引擎，在innodb的存储引起中，数据库中数据信息的存储在名字叫做ibdata1的文件中，文件名称和属性由f中配置项innodb_data_file_path指定。测试环境中tc183上面原有的sfrd镜像线上的数据库，由于存在着触发器和触发表，而触发表中的数据有上亿条，导致ibdata1已经增大到了319G，即使drop掉增量数据库，表空间文件ibdata1的大小没有任何变化，仍然是319G。经验总结：因为ibdata文件是只增大不缩小的，所以要减小表硬盘空间的大小，必须使用如下步骤： (1)使用mysqldump转储所有In

4、noDB表;(2)停止服务器;(3)删除所有已存在的表空间文件;(4)配置新表空间;(5)重启服务器; (6)导入转储文件。这是MySQL官方的步骤,因为占用的空间已经分配给MySQL 了,跟Oracle类似的，在修改和删除的时候mysql为了下次能够继续复用这些空间避免重新申请空间代理的效率上的损失，仍然占用着这些硬盘空间，而不进行释放，下次有需要。线上的ibdata1不会出现这么大的情况，因为线上会定期删除sfrd增量数据库里面的数据，这样留出磁盘空间可以给其他的数据用，所以线上的磁盘空间也会随着时间而有少许的增长。1.2 max_allowed_packet选项Q：为什么sql里面执行一

5、个很大的sql，会出现ror导致执行失败的情况？A：client mysqld 中有一个选项max_allowed_packet，max_allowed_packet参数的作用是用来控制其通信缓冲区的最大长度，表示能够接受到的包含sql语句的event包的最大大小，当sql语句过大的时候而这个配置项的数值过小，则可能导致出现下面错误：bigger than max_allowed_packet1.3 无法获取更新sql影响的记录条数Q：对于更新sql，使用mysql e”$sql”的方法，有没有办法知道这条sql作用到的具体的记录的条目数，比如delete from table_23_allt

6、ype where caseid = 1这个sql就无法知道caseid=1的sql是否存在，除非sql本身错误，否则不知道该条sql一共删除了几条caseid=1的记录？A：使用交互模式登陆到mysql服务器时候，执行一条sql，mysql会返回影响的条数，例如update table_int set caseid=1;Query OK, 0 rows affected (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 0但是非交互模式也就是-e的方式执行，却不会直接返回sql所影响到的记录数。在进行测试的时候，有时候我们需要执行的sql是否真的和

7、底层数据match上，为了解决这个问题，可以利用mysql的c的API函数mysql_affected_rows(MYSQL *)来执行。所以我们编写exesql这个工具来获取sql影响到的记录的个数，这个工具只是调用mysql_affected_rows(MYSQL *)这个函数。1.4 触发器坏的时候Q：建立触发器既不能够添加也不能够删除的情况：曾经在多次修改删除触发器的时候，遇到触发器失灵的问题。具体表现是这样的，使用命令“use SF_Word;show triggers”显示的结果为空，但是使用建立触发器的sql，却发现这个触发器已经存在，报错“trigger already exi

8、st!”，而使用删除触发器的sql语句“droptrigger trigger_name”之后，却无法删除。A：解决的方法是直接到文件夹VAR/SF_Word下面，删除以.TRG和. TRN结尾的文件，这样就彻底删除触发器。触发器就能够顺利安装上了。1.5 自增列的问题Q：在一个已经建立自增列的执行delete全表的操作，发现id不是从当前最大的开始增加而是从1开始A：innodb表中使用auto_increment，mysql官方文档关于自增列的说明是“每个表中只能存在一个AUTO_INCREMENT的字段。同时这个字段必须被索引，而且也不能为这个字段设置一个默认值”。当进行全表删除时，AU

9、TO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：Drop table table_name;create table table_name;这个时候已经分配的id都被删除。如果即使删除数据库表中的记录，也想让id从当前最大的开始增加，可以使用delete from table_name where 1强迫不进行优化；1.6 打开数据库的log文件进行测试Q:如何了解在测试过程中对mysql进行的所有操作？A:数据库的测试的时候，有时候想要看每条sql在数据库中的执行情况，这个时候可以在f中配置log文件选项。在f中添加log=“mysql.log”，然后重启my

10、sql数据库，这个时候mysql就会把接收到的所有sql都记录到mysql.log这个文件中。不过处于效率考虑，线上的mysql都是不写这个日志的；如果功能测试，可以打开，但是如果是性能测试，建议尽量保持和线上一致，不要开启这个日志。需要的时候可以打开mysql的慢查询日志log-slow-queries=slow.log#慢查询日志名称log_query_time=1#查询时间超过1秒的被当作慢查询log-queries-not-using-indexes=idx.log#没有使用索引的查询记录日志名称1.7 mysql导出转义字符Q:从mysql中导出数据默认进行字符转义的问题,如何去掉转

11、义字符的处理？A：如果向数据库表wordlist中插入记录insert into test.t1 values(1t2);insert into test.t1 values(1n2);然后使用select from wordlist into outfile ./a.txt; 会是什么样子的数据？mysql自动对导出数据进行转义处理，将1t2进行转义处理变成1t2;将1n2转换为1n2。可以在使用select from wordlist into outfile ./a.txt;的时候加上参数fields_escape=就可以将导出时候的转移符号设置为空，也可以设置fields_escape

12、为其他的字符，这样对转义字符就会加上自己设置的字符。2. 建立同步相关2.1 f需要主库的同步信息Q：在建立数据库镜像同步的时候，拷贝mysql主库上面的var包文件的时候，把binlog文件和文件也拷贝过来，为什么会无法启动？有可能造成启动失败的情况。A：在建立数据库镜像同步的时候，拷贝mysql主库上面的var包文件的时候，把binlog文件和文件也拷贝过来，有可能造成启动失败的情况。删除var包下面和、binlog文件后，如果f中没有关于主从同步相关的配置（包括master-host、master-user

13、、master-port）而直接运行mysqld skip-slave-start，就有可能出现“master into structure init failed”的错误导致启动失败；在f中添加上这几个配置，启动就能够成功；2.2 主从之间必须用不同的server-idQ：主库和从库的server-id相同的时候，主从同步能够建立吗？A：建立mysql的镜像同步时候，安装完mysql以后，f中的server-id配置需要进行更改，否则如果和从库和主库的server-id相同，在从库发送com_bin_dump命令而主库返回信息包体时候，就会造成冲突，因为主从交互的socket包中是包括ser

14、ver-id这个字段的内容的；2.3 必须配置log-bin Q:为什么重新安装一个从库，使用change master设置了主从同步以后，从库却无法同步主库中的sql？A:主库上面没有配置相关log-bin时候没有同步；配置项log-bin所指向的binlog文件是专门用于主从同步，在主库上没有设置log-bin，这个时候从库由于没有主库上的binlog文件可以读取，就无法实现。log-bin指向的binlog文件中记录所有主库中执行成功的更新sql，查询sql、执行不成功的sql、回滚事务的sql不会记录到binlog文件中。binlog中的记录的单位是一个个的event，对于同步比较重要

15、的event有rotate和query事件。2.4 配置项log-slave-updatesQ：在fc-web的数据库和fc-sfrd数据库搭建数据库镜像同步的时候，发现主库中的binlog文件被从库接收并写入到从库本地的log文件，但是查看从库的数据表中对应的数据，却还是之前没有更改的，与主库不一致；这是为什么？A：这个问题的原因是在建立主从同步的时候，默认从库只记录主库上面同步下来的同步sql，而对于是否执行，则最后由配置项log-slave-updates指定，添加这个配置项，则数据库从库按照从主库上面同步的下来的bin-log文件进行同步。通常情况，从服务器从主服务器接收到的更新不记入

16、它的二进制日志。该选项告诉从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志。为了使该选项生效，还必须用logs-bin选项启动从服务器以启用二进制日志。2.5 四升五必须考var包Q:不管是线上还是测试环境里面，当涉及到mysql4和mysql5建立同步，主库是4从库是5的时候必须拷贝var包的方式，这是为什么？A:创建mysql4到mysql5的同步时候必须使用拷贝var包的形式的原因Shifen系统的sfrd的数据库使用的版本是5.0.19，sf-web使用的版本是4.0.16，而这两个版本对decimal类型超过定义小数点位进行取舍时候，采用的策略不一样。4.0.16是直接

17、将多余的小数点位截断，而5.0.19则是进行四舍五入。对于4.0.16执行use test;create table t(a decimal(10,2) unsigned NOT NULL default 0.00);insert into t values(1.456); select * from t;+-+| a |+-+| 1.45 |+-+而对于5.0.19执行use test;create table t(a decimal(10,2) unsigned NOT NULL default 0.00);insert into t values(1.456); select * fro

18、m t;+-+| a |+-+| 1.46 |+-+如果采用各自版本的自己的建表语句，对decimal的数据类型就会因为处理策略不同而引入数据不一致的问题。所以不能采用在主库上面建表，然后同步到从库上面的方式进行同步，必须使用停止数据库，然后拷贝var包的方式进行主从同步的建立。目前进行的数据库4升5，将对数据库版本进行统一，之后mysql5到mysql5的同步镜像是否就可以直接使用建库建表sql，然后load data from master的方式进行数据的拷贝和数据库的同步。2.6 主从同步中忽略掉指定的表Q：如何配置从库，使得同步的时候忽略主库中指定的数据表？A：在从库的f中添加配置项r

19、eplicate-ignore-table的选项，就可以忽略指定的表replicate-ignore-table = MAIN.JHF_ALIVEreplicate-ignore-table = MAIN.JHF_UNreplicate-ignore-table = mysql.%replicate-ignore-table = test.%2.7 只同步指定的表Q：如何配置从库，使得从库只同步主库中的指定的数据表？A：在f中添加配置项replicate-do-table的选项，就可以忽略指定的表replicate-do-table = MAIN.JHF_ALIVEreplicate-do-t

20、able = MAIN.JHF_UNreplicate-do-table = mysql.%replicate-do-table = test.%Mysql性能的一般结论3. 更新sql的性能3.1 更新类型sql的最大速度在没有读请求的情况下，使用线上的update的sql，mysql的执行速度非常的快，达到每秒6000个update的sql（使用dbpress开启16个线程，按照10000sql/s来发送，最高每秒能够发送6000个sql)；有必要说明的是线上update的sql都是比较规范，类似这样的格式update where col=val(其中col为数据库表的索引列)的格式，my

21、sql在解析这样的sql的时候运行速度很快；另外值得一提的是，对于用or连接起来的含有多个where条件的sql，如果每一列都是索引列的时候，执行速度也是很快的，例如sql：DELETE FROM SF_User.usermaps WHERE fatuid = '1139751' OR sonuid = '1139751' 在数据库中的执行速度也是很快的，因为where条件中的两列在表中都定义成了索引列，下面是线上的表SF_User.usermaps的定义CREATE TABLE usermaps ( maptype smallint(5) unsigned N

22、OT NULL default '0', sonuid int(10) unsigned NOT NULL default '0', fatuid int(10) unsigned NOT NULL default '0', addtime datetime NOT NULL default '0000-00-00 00:00:00', UNIQUE KEY sonuid (sonuid), KEY fatuid (fatuid) ENGINE=InnoDB 3.2 影响更新sql执行时间的主要因素从性能测试的结果上看，当没有其他

23、的操作和select的sql的时候，下面两个因素会影响更新sql的处理时间:第一是sql语句本身的大小，第二是这条sql语句所影响到的数据库里面记录的个数;当sql语句本身的长度比较短的时候，无论是delete语句还是update语句，满足where条件的记录个数对于sql语句本身的执行时间基本上影响不是很大。3.3 比较短的更新sql下面两个图能够说明一些问题，当Update sql很短时（update SF_Other.tradecore set guidedata='z）很短的时候，在mysql中update的sql语句执行时间保持在一定范围内基本不变：下图中1B的update语

24、句是指update SF_Other.tradecore set guidedata='z中记录选取的引号里面的字符'z是1B长对于delete语句，也有类似的效果，Delete语句为简单删除语句，如delete from SF_Other.tradecore where tradeid < 10;执行时间随着影响记录条数的变化增长,但是增加并不明显3.4 比较大的更新sql但是，当sql的长度比较大的时候，mysql中的执行时间就会随着影响的记录个数出现显著的增长例如下图，当sql长度为60KB的时候，就会出现上述的执行时间显著增加的情况4. 关于mysql检索性能测试

25、有关mysql的性能测试中，sql语句是整个测试的基础工作，好的sql语句能够比mysql配置的调优效果明显很多，这也是整个和mysql有关的性能测试的基础工作。4.1 索引不止一倍提高检索速度where语句中表达式有无索引对于查找效率很关键;在测试sfrd3.0的fcbm模块的时候，发现导出基准时间很长，3000w记录一天都没有完全导出，查看的原因是创建数据表的时候建表sql中没有对planinfo表的planid字段设置索引；设置完索引以后导出速度明显加快，一个小时就把基准文件生成完成；4.2 使用in比单条更快在select的条件语句中使用in的效率比直接使用单条sql要快。在sfrd3

26、.0的测试过程中，fcout导出增量文件的速度过慢。之前的方式是每个id都使用select * from table where id=，现在的方案是每次先把所有的id都找到，然后将sql聚类以后，采用selectin的方式速度提升一倍。4.3 比较符号比limit的效率高测试sfrd3.0时候发现，同样在where条件中按照索引进行查找，使用limit的时候，会进行全表遍历；而在使用两个比较的时候只是跟进索引进行条件查找，由于mysql的索引(大部分情况下是b-树)支持这种><的查找类型，所以需要查找的记录数量要小于直接limit查找的记录数目。效率低的sql：select *

27、from SF_Output.sfevent where eventid > %llu and eventid < %llu order by eventid查看在mysql中的执行explain select * from SF_Output.sfevent where eventid > 1000 and eventid < 2000 order by eventid; +-+-+-+-+-+-+-+-+-+-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows

28、 | Extra |+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE | sfevent | range | PRIMARY | PRIMARY | 8 | | 1976 | Using where |+-+-+-+-+-+-+-+-+-+-+改造后的sql：select * from SF_Output.sfevent where eventid > %llu order by eventid limit 1000;查看在mysql中的执行explain select * from SF_Output.sfevent where eventid > 1000 o

29、rder by eventid limit 1000; +-+-+-+-+-+-+-+-+-+-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE | sfevent | range | PRIMARY | PRIMARY | 8 | | 131499744 | Using where |+-+-+-+-+-+-+-+-+-+-+去掉order by以后explain select * from SF_

30、Output.sfevent where eventid > 1000 limit 1000; +-+-+-+-+-+-+-+-+-+-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE | sfevent | range | PRIMARY | PRIMARY | 8 | | 132196410 | Using where |+-+-+-+-+-+-+-+-+-+-+在sfevent表中有258

31、777832条记录，满足limit中的where条件的也有258831238，而使用limit查找的rows是131499744，很显然limit也不是全表查找，是采用自己的方法进行优化过，具体limit优化策略如何呢？还需要进一步跟进。select count(*) from SF_Output.sfevent; +-+| count(*) |+-+| 258777832 |+-+ select count(*) from SF_Output.sfevent where eventid > 1000;+-+| count(*) |+-+| 258831238 |+-+5. 各种数据类型

32、的处理在mysql中存在着纷繁的数据类型，并且各种数据类型都是能够彼此运算的，不过要想在mysql中得到正确运算的结果却要遵循mysql的规则，甚至是由于mysql自身设计上面的bug会导致数据运算和比较的时候得到一个出乎我们意料的结果，尤其是5.0.45版本的mysql对于数据类型也并非我们想象的那样正确和准确。对于数据类型的处理，无外乎比较运算和算术运算两种类型。5.1 数据类型的转换规则在Mysql中，各种的运算遵循一定得规则，mysql内部的参加数据类型有下面几类String:例如abcdDouble：例如1.11Int：123Decimal：1.11算数运算（+-*/ %）的类型转换

33、规则如下：a）如果操作数中有一个是字符串型，结果为双精度b）如果操作数中有一个是decimal型，结果为38位decimalc）其它情况结果为longMySQL中的伪代码如下（r0和r1是两个操作数）：（2）当做比较操作（=, >=, >, <=, <, !=）时，转换规则规则如下：a）如果两个操作数都是字符串型，则使用字符串比较的方法b）如果两个操作数都是long，则使用数字比较的方法c）如果decimal和long比较，则使用decimal比较的方法d）其它情况采用real比较的方法e）在每种比较方法中，都是先将两个操作数转换为相应的类型在进行比较。如，long比较

34、方法中，将两个操作数（a, b）转换为long类型，再用整数比较方法进行比较；MySQL中的伪代码如下（a和b是两个操作数）需要说明的是，在mysql中的有关double和real类型的计算是不够准确，例如所以一旦出现使用double或者real逻辑的地方就有可能会出现准确性的问题。5.2 在mysql中慎用引号双引号或者是单引号引起来的数字和其他数字之间的运算，mysql在内部在计算结果的时候使用的类似于c中double运算，所以在赋值的时候存在不够精确而在查找比较运算的时候结果不准确的问题。赋值的时候不精确的例子，对一个int列赋值,可以看到加引号和不加引号采取的截断方法是完全不一样的，不

35、加引号的时候，一个四舍五入的方法，另外一个则是直接截断。insert into table_23_alltype set xint='1.0'/'2'select xint from table_23_alltype where caseid=1; +-+| xint |+-+| 0 | +-+insert into table_23_alltype set xint=1.0/2,caseid=2; select xint from table_23_alltype where caseid=2; +-+| xint |+-+| 1 | +-+查找的时候比较运算

36、不准确，写入得数值使用原有的计算未必可以检索出来对于decimal类型的列mysql> insert into table_23_alltype set xdecimal=18 + 4.5,xtext="",xmtext="",caseid=200;Query OK, 1 row affected (0.00 sec)不加引号可以检索处理mysql> select caseid,xdecimal from table_23_alltype where xdecimal=18 + 4.5; +-+-+| caseid | xdecimal |+

37、-+-+| 200 | 22.5000000000000000000 | +-+-+1 row in set (0.00 sec)添加引号以后就无法检索出来mysql> select caseid,xdecimal from table_23_alltype where xdecimal=18 + '4.5'Empty set (0.00 sec)插入的时候添加引号，检索的时候也添加引号还是无法检索出来mysql> insert into table_23_alltype set xdecimal='18' + '4.5',xtext

38、="",xmtext="",caseid=222;Query OK, 1 row affected (0.00 sec)mysql> select caseid,xdecimal from table_23_alltype where xdecimal='18' + '4.5'Empty set (0.00 sec)去掉引号的时候可以mysql> select caseid,xdecimal from table_23_alltype where xdecimal=18 + 4.5; +-+-+| caseid

39、 | xdecimal |+-+-+| 222 | 22.5000000000000000000 | +-+-+1 rows in set (0.00 sec)在mysql中，如果decimal的列和string列进行比较，会当做string类型统一比较。这样的情况下，不仅记录的结果和比较值要相等，位数也要一样，才会得到比较的结果为真。从中可以看到mysql处理添加引号的数字的时候规则是比较晦涩的，建议在使用sql的时候不要给数值加上引号，这会导致一些无法预料的情况出现，目前web端的sf-lib中对sql中的数值都是加引号的，存在一定风险，建议去掉；5.3 BIGINT不是INTBIGINT

40、在比较的时候采取一种和int截然不的逻辑，对于int列，a = 5.1在a为5时是不成立的，对于bint列则不然。这是因为在int列比较时，记录中的数值转成decimal类型处理，而在BIGINT列比较时，转成BIGINT类型处理。例如对于int列，a = 5.1在a为5时是不成立的mysql> insert into table_23_alltype set xint=5,caseid=15;Query OK, 1 row affected (0.00 sec)mysql> select caseid,xint from table_23_alltype where xint=5

41、.1; Empty set (0.00 sec)对于bint列则不然，a=5.1是能够成立mysql> insert into table_23_alltype set xbint=5,caseid=5;Query OK, 1 row affected (0.00 sec)mysql> select caseid,xbint from table_23_alltype where xbint=5.1; +-+-+| caseid | xbint |+-+-+| 5 | 5 | +-+-+1 row in set (0.00 sec)5.4 text列对于text的列在创建主键的时候

42、，必须指定为主键的text的列的字段长度，例如下面的建表sql在mysql中执行失败，报错BLOB/TEXT column 'c4_xtext' used in key specification without a key length对于text的列在创建主键的时候，必须指定为主键的text的列的字段长度，例如CREATE TABLE table_5col_maxlen ( c1_caseid bigint(20) NOT NULL default '0', c2_xchar char(255) NOT NULL default 'a', c

43、3_xvarchar varchar(255) NOT NULL default 'a', c4_xtext text NOT NULL, c5_xmtext mediumtext NOT NULL, PRIMARY KEY (c1_caseid,c4_xtext) ENGINE=InnoDB;而增加长度限制就可以使得这个成功CREATE TABLE table_5col_maxlen ( c1_caseid bigint(20) NOT NULL default '0', c2_xchar char(255) NOT NULL default 'a&#

44、39;, c3_xvarchar varchar(255) NOT NULL default 'a', c4_xtext text NOT NULL, c5_xmtext mediumtext NOT NULL, PRIMARY KEY (c1_caseid,c4_xtext(10) ENGINE=InnoDB;另外，对于text类型的不能够有默认值，除非默认值是单引号，这个时候mysql会给出warning，例如：CREATE TABLE table_5col_maxlen ( c1_caseid bigint(20) NOT NULL default '0', c2_xc