1.测试MySQL的Like查询效率

By youfang at: 2022-08-13

Mysql的查询

一. 准备数据

本文参考了

二. Count查询

  1. 100w数据
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT COUNT(1) from product
    > OK
    > 时间: 2.344s


    SELECT COUNT(*) from product
    > OK
    > 时间: 2.305s


    SELECT COUNT(id) from product
    > OK
    > 时间: 2.338s

    InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

    COUNT(*)与COUNT(1) 性能没有差距。

    Mysql官方文档说明

总结：以后还是用count(*)

三、Like查询

  1. 100w数据
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    SELECT * FROM product where product_name like '%8%'
    > OK
    > 时间: 5.257s


    SELECT * FROM product where INSTR(product_name,'8') > 0
    > OK
    > 时间: 5.468s


    SELECT * FROM product where LOCATE('8',product_name) > 0
    > OK
    > 时间: 5.423s


    SELECT * FROM product where POSITION('8' IN product_name) > 0
    > OK
    > 时间: 5.688s

总结：以后还是正常用like