关于 find_in_set 的性能问题

https://www.iteye.com/blog/jonny131-771753

 

使用一个字段来存储多对多关系，比如 表 user中有一个字段叫 category, category存储的是 "1,3,9" 这样的类型的数据，实际上是category的id 用逗号分隔开来的。

 

要查询一个用户属于id为2分类的用户可以这么写

 

 

select * from `user` where find_in_set('2',`user`.`category`)

 

具体find_in_set 的使用请参照手册

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

 

 

虽然这样很好用，但问题是如果数据量大的情况下怎么办，性能会是问题么，手册上有说对find_in_set 做的优化，但在没有索引的情况下他的性能应该是个问题。

 

于是做了个测试，user 表录入 100万的数据，同时建立 user_category 表，每个user有 3 个分类，那么category表里有300万条记录。

 

CREATE TABLE `user_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `user_id` (`tax_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT

 

 

现在比较一下在百万级的数据量上使用 join 链接外键查询和find_in_set查询的性能

 

1. 使用 find_in_set 查询，平均时间在2.2秒左右

 

 

SELECT SQL_NO_CACHE COUNT(*) FROM `user` WHERE FIND_IN_SET(65,category)

 

 

 

2. 使用left join ， 使用了右表中的索引，平均时间在0.2秒左右

 

 

SELECT SQL_NO_CACHE COUNT(DISTINCT(`user`.id)) FROM `user`
LEFT JOIN `user_category` ON `user`.`id`= `user_category`.`user_id`
WHERE `user_category`.`category_id`=75

 

 

所以在大数据量的情况下还是不适合用find_in_set, 不过有些表的数据可能永远就那么点数据，这个时候为了减少表数量，倒是可以用这样的方法做。

---

 

mysql中的find_in_set效率

https://www.jianshu.com/p/828fd8f97f26

1，工作中，同事说find_in_set效率可低了，不如把记录存成多条。比如一个user_id=3对应qrcode=‘23,24,25,26’，不如存成四条记录，qrcode改成int类型，这样效率高。我是保持怀疑态度的。实践是检验真理的唯一标准。

2，我开始在自己本地数据库中创建测试数据，数据中的数字部分都是随机生成的。textbook表是qrcode类型是varchar，user表的qrcode是int类型

 

textbook.png

 

user.png

textbook表创建了10万条测试数据，其中qrcode存了四个id的字符串。
user表创建了40万条测试数据，其中qrcode只存一个int类型的数字。

 

textbook.png

 

user.png

3，开始查询验证
user表用时0.110s

select * from user where qrcode=4
// 查询出366条，用时0.110s

 

user.png

textbook表用时：

select * from textbook where FIND_IN_SET('4',qrcode)
// 查询出370条，用时0.039s

 

image.png

4，如果把textbook表的数据增加到40万条，同样的sql查询，看看结果：
user表的查询速度变慢了。用时0.113s。
textbook表的查询用时 0.176s

 

user.png

 

textbook.png

此时user表和textbook表数据一样多的时候，find_in_set的速度是不如int类型分开存储的情况。

5，仅测试这种存储方式对查询速度的影响。find_in_set对速度影响并不大

6，再更新一下，忽略了一个问题，存数字的情况下，没有建索引。给user表的qrcode字段加一个普通索引，速度提升明显。未加索引之前，用时0.110s。加上普通索引后耗时0.040s。

 

user.png

作者：雪贝特
链接：https://www.jianshu.com/p/828fd8f97f26
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

 

---

mysql的SQL_NO_CACHE(在查询时不使用缓存)和sql_cache用法

为了测试sql语句的效率，有时候要不用缓存来查询。

使用

SELECT SQL_NO_CACHE ...

语法即可

 

SQL_NO_CACHE的真正作用是禁止缓存查询结果，但并不意味着cache不作为结果返回给query。

 

目前流传的SQL_NO_CACHE不外乎两种解释：

1.对当前query不使用数据库已有缓存来查询，则当前query花费时间会多点

2.对当前query的产生的结果集不缓存至系统query cache里，则下次相同query花费时间会多点

我做了下实验，似乎两种都对。

 

sql_cache意思是说，查询的时候使用缓存。

 

对SQL_NO_CACHE的解释及测试如下:

SQL_NO_CACHE means that the query result is not cached. It does not mean that the cache is not used to answer the query.

You may use RESET QUERY CACHE to remove all queries from the cache and then your next query should be slow again. Same effect if you change the table, because this makes all cached queries invalid.

 

mysql> select count(*) from users where email = 'hello';

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (7.22 sec)

 

mysql> select count(*) from users where email = 'hello';

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.45 sec)

 

mysql> select count(*) from users where email = 'hello';

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.45 sec)

 

mysql> select SQL_NO_CACHE count(*) from users where email = 'hello';

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.43 sec)

 

总结:可以在 SELECT 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，我们都可以指定不进行查询缓存，使用 SQL_NO_CACHE 选项。
对于那些变化不频繁的表，查询操作很固定，我们可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用SQL_CACHE 选项。

 

================MyBatis的对CACHE的应用======================

MyBatis的flushCache和useCache的使用

转自：http://blog.csdn.net/ssssny/article/details/52248960

 

（1）当为select语句时：

flushCache默认为false，表示任何时候语句被调用，都不会去清空本地缓存和二级缓存。

useCache默认为true，表示会将本条语句的结果进行二级缓存。

 

（2）当为insert、update、delete语句时：

flushCache默认为true，表示任何时候语句被调用，都会导致本地缓存和二级缓存被清空。

useCache属性在该情况下没有。

当为select语句的时候，如果没有去配置flushCache、useCache，那么默认是启用缓存的，所以，如果有必要，那么就需要人工修改配置，修改结果类似下面：

 

<select id="save" parameterType="XX" flushCache="true" useCache="false">

……

</select>

 

update 的时候如果 flushCache="false"，则当你更新后，查询的数据数据还是老的数据。