mysql text字段的优化

mysql text字段比较特殊，text用来存储较多字符的字符串，因为text一般比较大，普通索引效率很低， 不可以直接在text字段上创建普通索引。

在text字段上可以创建2种索引：全文索引（fulltext index）和前缀索引（column prefix index）

(实际上全文索引可以在char，varchar，text上创建）

 

 

mysql全文索引

mysql的全文索引跟Oracle的全文索引是类似的，全文索引将字段中的词汇进行索引，获取方式跟parser相关，所以也存在text中存在的词索引不到的情况

全文索引创建语法跟一般索引不太一样，在index前有fulltext的关键字

create fulltext index idx_ft on t2(b);

如果没有fulltext关键字会报错

ERROR 1170 (42000): BLOB/TEXT column 'object_key' used in key specification without a key length

 

全文索引跟一般索引有所区别

mysql> show index from  t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | idx_t2   |            1 | b           | A         |          47 |       10 | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | idx_ft   |            1 | b           | NULL      |          47 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

其中index_type为fulltext

 

mysql使用全文索引不能使用like，必须用match  .. against

比如

SELECT * FROM lzltab WHERE MATCH (col1) AGAINST ('abcdefghijklmn');

 

 

mysql前缀索引

前缀索引会截取前N给字符并创建B+树索引。超过N个字符的索引是索引不到的

前缀索引跟普通索引创建方式类似

create index idx_t2 on t2(b(10));

mysql> show index from  t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | idx_t2   |            1 | b           | A         |          47 |       10 | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | idx_ft   |            1 | b           | NULL      |          47 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 subpart字段可以看出该索引是只取了前10个字符的前缀索引

 

 

 

text字段sql的优化案例

某库cpu高，存在慢sql且执行次数和时间均偏高

执行计划如下

mysql> explain SELECT * FROM `tablzl` WHERE (ididid=73 and object_key='lzlzlzlzllz.pdf');
+----+-------------+------------------+------+--------------------+--------+---------+-------+--------+------------------------------------+
| id | select_type | table            | type | possible_keys      | key    | key_len | ref   | rows   | Extra                              |
+----+-------------+------------------+------+--------------------+--------+---------+-------+--------+------------------------------------+
|  1 | SIMPLE      | tablzl | ref  | uk_obj,idx_version | uk_obj | 8       | const | 400290 | Using index condition; Using where |
+----+-------------+------------------+------+--------------------+--------+---------+-------+--------+------------------------------------+

表数据分布和定义如下

mysql> select ididid,count(1) from tablzl group by ididid;
+-------------+----------+
| ididid | count(1) |
+-------------+----------+
|          23 |       64 |
|          73 |   633280 |
|         100 |     3627 |
|         106 |      481 |
|         107 |      914 |
|         121 |   178694 |
|         124 |      320 |
|         130 |      204 |
|         132 |      118 |
|         134 |      991 |
|         135 |      569 |
|         144 |      615 |
|         150 |      990 |
+-------------+----------+
13 rows in set (0.29 sec)

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_obj` (`ididid`,`file_id`),
  KEY `idx_region` (`uid`,`region_id`),
  KEY `idx_version` (`ididid`,`rule_version`)

sql走的是uk_obj索引，过滤的字段是ididid，而ididid的值为73时，过滤性很差，从而导致该sql执行效率较低。（当ididid不为73时，sql执行效率较高，所有慢sql只有ididid=73的）
 关键问题还是要让sql走object_key='lzlzlzlzllz.pdf'这样的条件过滤，所以要在object_key上创建索引

 

尝试创建普通索引

mysql> create index idx_obj_id on tablzl(object_key);
mysql> ERROR 1170 (42000): BLOB/TEXT column 'object_key' used in key specification without a key length

普通索引在text字段无法创建

 

尝试创建fulltext全文索引

mysql>  create fulltext index  idx_obj_id on tablzl(object_key);
Query OK, 0 rows affected, 1 warning (50.42 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> explain SELECT * FROM `tablzl` WHERE (ididid=73 and object_key='lzlzlzlzllz.pdf');
+----+-------------+------------------+------+------------------------+-------------+---------+-------+--------+------------------------------------+
| id | select_type | table            | type | possible_keys          | key         | key_len | ref   | rows   | Extra                              |
+----+-------------+------------------+------+------------------------+-------------+---------+-------+--------+------------------------------------+
|  1 | SIMPLE      | tablzl | ref  | uk_obj ,idx_version | uk_obj | 8       | const | 408092 | Using index condition; Using where |
+----+-------------+------------------+------+------------------------+-------------+---------+-------+--------+------------------------------------+

使用全文索引必须用match agaist语句，不该sql是走不了的

因为sql的object_key='lzlzlzlzllz.pdf'前面没有%，所以可以走前缀索引

创建前缀索引

mysql> create index idx_obj_key on tablzl(object_key(50));
Query OK, 0 rows affected (9.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT * FROM `tablzl` WHERE (ididid=73 and object_key='lzlzlzlzllz.pdf');
+----+-------------+------------------+------+-------------------------+-------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys           | key         | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+-------------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tablzl | ref  | idx_inst_id,idx_obj_key | idx_obj_key | 153     | const |    1 | Using where |
+----+-------------+------------------+------+-------------------------+-------------+---------+-------+------+-------------+

包含text字段的sql成功走到前缀索引，cpu使用率从40%下降到4%