MySQL 长字符串字段的查询优化

比如有一张表 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

因为本机表的数据量少，所以性能的提升还不算大。如果数据量大的话，性能的提升会更大。