|
比如有一张表 osx_yue_order，包含字段 yo_order_id、yo_pay_status，值形如 tywx2021073015544530593
原本的查询语句为 select yo_pay_status ?from osx_yue_order oyo where yo_order_id ='tywx2021081209433714083';
优化的方法是：
1、新建字段?yo_order_id_crc32，int (10) unsigned NOT NULL DEFAULT '0' COMMENT 'yo_order_id 的 crc32 值 ',
2、对此字段建立索引??KEY `osx_yue_order_yo_order_id_crc32_IDX` (`yo_order_id_crc32`) USING BTREE
3、修改查询语句?select yo_pay_status ?from osx_yue_order oyo where? yo_order_id_crc32=crc32 ('tywx2021081209433714083')? and yo_order_id ='tywx2021081209433714083';
使用如下命令压测：
mysqlslap -h127.0.0.1 -uroot --concurrency=8 --iterations=10 --create-schema=osx1 --query=d:\bench.sql --engine=innodb --number-of-queries=100000 --debug-info
优化前的压测结果：
Benchmark
? ? ? ? Running for engine innodb
? ? ? ? Average number of seconds to run all queries: 9.735 seconds
? ? ? ? Minimum number of seconds to run all queries: 8.906 seconds
? ? ? ? Maximum number of seconds to run all queries: 10.172 seconds
? ? ? ? Number of clients running queries: 8
? ? ? ? Average number of queries per client: 12500
优化后的压测结果：
Benchmark
? ? ? ? Running for engine innodb
? ? ? ? Average number of seconds to run all queries: 7.026 seconds
? ? ? ? Minimum number of seconds to run all queries: 6.219 seconds
? ? ? ? Maximum number of seconds to run all queries: 7.875 seconds
? ? ? ? Number of clients running queries: 8
? ? ? ? Average number of queries per client: 12500
因为本机表的数据量少，所以性能的提升还不算大。如果数据量大的话，性能的提升会更大。
|