编程爱好者之家

MySQL优化索引及优化汉字模糊查询语句

2018-02-28 19:23:15 624

利用mysql这种关系型数据库来做索引，的确有些勉强了，也只能看情况来说了，有些简单的功能还是可以考虑的。

对于模糊查询语句，最不利的情况是要like '%key%'这样的查询，但是如果是like 'key%'这种情况，那么mysql的索引在些查询方式上还是可以优化的。

网上常见的是ASCII的英文字符优化，如下：

[sql] view plaincopy

  1. select corp_code, corp_corp from tb_Z_Corp where corp_code like '0008%';  

举个例子来看看问题的来源吧：

先看一下要试验表的创建语句和结构，这是一个只包含股票代码和股票名称的数据表，主键为股票代码：

[sql] view plaincopy

  1. mysql> show create table tb_Z_Corp;  

  2. +-----------+-----------------------------------+  

  3. | Table     | Create Table                      |  

  4. +-----------+-----------------------------------+  

  5. | tb_Z_Corp | CREATE TABLE `tb_Z_Corp` (  

  6.   `corp_code` char(10) NOT NULL,  

  7.   `corp_corp` varchar(60) NOT NULL,  

  8.   PRIMARY KEY  (`corp_code`),  

  9.   KEY `idx_Z_Corp_corp_corp` (`corp_corp`)  

  10. ) ENGINE=MyISAM DEFAULT CHARSET=utf8            |   

  11. +-----------+-----------------------------------+  

  12.   

  13. mysql> desc tb_Z_Corp;  

  14. +---------------+-------------+------+-----+---------+-------+  

  15. | Field         | Type        | Null | Key | Default | Extra |  

  16. +---------------+-------------+------+-----+---------+-------+  

  17. | corp_code     | char(10)    | NO   | PRI |         |       |   

  18. | corp_corp     | varchar(60) | NO   | MUL |         |       |   

  19. +---------------+-------------+------+-----+---------+-------+  

表里面的数据举例如下（股票代码和股票名称）：

[sql] view plaincopy

  1. +-----------+--------------+  

  2. | corp_code | corp_corp    |  

  3. +-----------+--------------+  

  4. | 000800    | 一汽轿车     |   

  5. | 000801    | 四川九洲     |   

  6. | 000802    | 北京旅游     |   

  7. | 000803    | 金宇车城     |   

  8. | 000805    | *ST炎黄      |   

  9. | 000806    | 银河科技     |   

  10. | 000807    | 云铝股份     |   

  11. | 000809    | 中汇医药     |   

看一下要待优化的语句（一个英文、一个中文的）：

[sql] view plaincopy

  1. select corp_code, corp_corp from tb_Z_Corp where corp_code like '0008%';  

  2. select corp_code, corp_corp from tb_Z_Corp where corp_corp like '江%';  

对于第一个待优化的SQL语句来说，比较简单，很多地方都介绍过怎样优化：

[sql] view plaincopy

  1. select corp_code, corp_corp from tb_Z_Corp where corp_code >= '0008' and corp_code < '0009';  

而对于第二个是中文字符，马上想到是加一个最大编码的汉字，这里数据库存储的是UTF-8格式存储，而汉字的编码为3字节，所以按其最大编码的规则应该是：
Unicode编码是从U+0800到U+FFFF先标记一下这16位：“zzzzyyyy yyxxxxxx”；
然后把这16位对应到UTF-8的编码：“1110zzzz 10yyyyyy 10xxxxxx”。
所以最大的3个字节的编码为“11101111 10111111 10111111”，也就是十六进制的“EFBFBF”。
在MySQL中，用x'EFBFBF'表明这里面是用16进制编码的字符串，所以我们优化后的语句应该是这样的：

[sql] view plaincopy

  1. select corp_code, corp_corp from tb_Z_Corp where corp_corp >= '江' and corp_corp < CONCAT('江', x'EFBFBF');  

这样就达到了不用like语句比较表中的每一条记录，而直接使用索引快速检索。看，结果出来了：

[sql] view plaincopy

  1. +-----------+--------------+  

  2. | corp_code | corp_corp    |  

  3. +-----------+--------------+  

  4. | 600750    | 江中药业     |   

  5. | 002226    | 江南化工     |   

  6. | 601199    | 江南水务     |   

  7. | 000519    | 江南红箭     |   

  8. | 600527    | 江南高纤     |   

  9. | 002061    | 江山化工     |   

  10. | 600389    | 江山股份     |   

  11. | 600212    | 江泉实业     |   

  12. | 002484    | 江海股份     |   

  13. | 000816    | 江淮动力     |   

  14. | 600418    | 江淮汽车     |   

  15. | 002176    | 江特电机     |   


     


同类文章