首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

SQL优化案例1--去掉双%%后，SQL速度从5秒到0.01秒

作者：mobiledu2502892717 | 来源：互联网 | 2023-05-18 03:46
一、问题现象开发说以下SQL执行15秒，不可以接受。你把他优化到0.1秒以下吧。(跟客户交流过，SQL需要经过处理)SELECT*FROMordeleftjoin
一、问题现象

　　开发说以下SQL执行15秒，不可以接受。你把他优化到0.1秒以下吧。(跟客户交流过，SQL需要经过处理)

SELECT *
  FROM orde
  left join user
    on orde.user_id = user.id
  left join car
    on orde.car_id = car.id
  left join ocm
    on orde.charge_stand_encod = ocm.charge_stand_encod
  left join admin
    on orde.update_userid = admin.id
  LEFT JOIN scl
    on scl.id = orde.coupon_id
  LEFT JOIN sct
    on scl.coupon_type_id = sct.id
 WHERE 1 = 1
   AND user.phone like '%17885004325%'
   AND orde.operator_id in (10,
                            7,
                            1048,
                            8,
                            9,
                            998,
                            1001,
                            1002,
                            1005,
                            1006,
                            1007,
                            1009,
                            1010,
                            1011,
                            1024,
                            1012,
                            1020,
                            1013,
                            1014,
                            1015)
 ORDER BY orde.create_time DESC limit 0, 20

 

二、信息收集

查看表数据量

 

+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+
| Name       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment      |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+
| orde       | InnoDB |      10 | Dynamic    | 1551182 |            984 |  1527758848 |               0 |    787513344 |   7340032 |       10336667 | 2018-09-05 20:31:17 | 2018-10-30 17:21:57 | NULL       | utf8_general_ci |     NULL |                | 订单总表     |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+


+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------+
| Name       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment   |y
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------+
| user       | InnoDB |      10 | Dynamic    | 1537890 |            371 |   571457536 |               0 |    335577088 |   4194304 |       12112311 | 2018-10-19 23:01:12 | 2018-10-30 17:21:57 | NULL       | utf8_general_ci |     NULL |                | 会员表    |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------+


+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment      |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+
| car          | InnoDB |      10 | Dynamic    | 7057 |            807 |     5701632 |               0 |      5226496 |   4194304 |          42404 | 2018-10-01 00:14:35 | 2018-10-30 17:21:57 | NULL       | utf8_general_ci |     NULL |                | 车辆信息     |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+


+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options     | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| ocm | InnoDB    |      10 | Compact    |  153 |            321 |       49152 |               0 |        16384 |         0 |            811 | 2018-06-13 01:03:40 | 2018-10-30 15:00:42 | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+


+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+--------------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options     | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+--------------------+---------+
| admin | InnoDB  |      10 | Compact    |  631 |            207 |      131072 |               0 |        32768 |         0 |           1686 | 2018-06-13 01:03:41 | 2018-10-30 16:27:31 | NULL       | utf8_bin  |     NULL | row_format=COMPACT |         |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+--------------------+---------+


+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| Name            | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options     | Comment |
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| scl             | InnoDB |      10 | Compact    | 6498194 |            163 |  1061142528 |               0 |    533102592 |   7340032 |       44548696 | 2018-10-18 23:02:45 | 2018-10-30 17:21:57 | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+


+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options     | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| sct             | InnoDB |      10 | Compact    | 6456 |            246 |     1589248 |               0 |        98304 |   4194304 |          12356 | 2018-07-19 19:34:17 | 2018-10-30 16:58:35 | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+

 

查看表上面的索引

+------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orde       |          0 | PRIMARY                 |            1 | id            | A         |     1634857 |     NULL | NULL   |      | BTREE      |         |               |
| orde       |          1 | update_userid           |            1 | update_userid | A         |      167598 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | oper_orders_update_time |            1 | update_time   | A         |     1410960 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | ope_orders_uid          |            1 | user_id       | A         |      159562 |     NULL | NULL   |      | BTREE      |         |               |
| orde       |          1 | order_id_index          |            1 | order_id      | A         |     1634865 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | begin_time_index        |            1 | begin_time    | A         |     1277148 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | end_time                |            1 | end_time      | A         |     1531872 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | create_time_index       |            1 | create_time   | A         |     1628670 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | car_id_index            |            1 | car_id        | A         |        8313 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | idx_operator_id         |            1 | operator_id   | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | idx_status              |            1 | status        | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| orde       |          1 | idx_user_id             |            1 | user_id       | A         |      162152 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user       |          0 | PRIMARY                     |            1 | id              | A         |     1320970 |     NULL | NULL   |      | BTREE      |         |               |
| user       |          0 | web_user_phone_unique_index |            1 | phone           | A         |     1320979 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          0 | web_user_phone_unique_index |            2 | top_operator_id | A         |     1320979 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | user_name                   |            1 | user_name       | A         |         205 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | user_type                   |            1 | user_type       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | province                    |            1 | province        | A         |          29 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | is_have_order               |            1 | is_have_order   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | rfid                        |            1 | rfid            | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | idx_operator_id             |            1 | operator_id     | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | idx_create_time             |            1 | create_time     | A         |     1232045 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | invitation_code             |            1 | invitation_code | A         |      566384 |     NULL | NULL   | YES  | BTREE      |         |               |
| user       |          1 | i_imei                      |            1 | imei            | A         |      469607 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+--------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                 | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| car          |          0 | PRIMARY                  |            1 | id                   | A         |        7121 |     NULL | NULL   |      | BTREE      |         |               |
| car          |          0 | idx_plate                |            1 | plate                | A         |        7121 |     NULL | NULL   |      | BTREE      |         |               |
| car          |          1 | city_code                |            1 | city_code            | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | user_id                  |            1 | user_id              | A         |        2390 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | order_id                 |            1 | order_id             | A         |        2402 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | car_is_in_rail           |            1 | car_is_in_rail       | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | amap_id                  |            1 | amap_id              | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | ope_car_info             |            1 | device_num           | A         |        7121 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | idx_deposit_rail_draw_id |            1 | deposit_rail_draw_id | A         |        1165 |     NULL | NULL   | YES  | BTREE      |         |               |
| car          |          1 | idx_deposit_rail_draw_id |            2 | car_now_status       | A         |        1553 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------------+------------+-----------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name                    | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-----------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ocm             |          0 | PRIMARY                     |            1 | id                 | A         |         101 |     NULL | NULL   |      | BTREE      |         |               |
| ocm             |          1 | ope_charge_main_charge_code |            1 | charge_stand_encod | A         |         101 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+-----------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| admin           |          0 | PRIMARY          |            1 | id          | A         |         732 |     NULL | NULL   |      | BTREE      |         |               |
| admin           |          0 | user_name_unique |            1 | user_name   | A         |         732 |     NULL | NULL   | YES  | BTREE      |         |               |
| admin           |          1 | user_code        |            1 | user_code   | A         |         670 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| scl             |          0 | PRIMARY                 |            1 | id          | A         |     5043168 |     NULL | NULL   |      | BTREE      |         |               |
| scl             |          1 | is_delete               |            1 | is_delete   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| scl             |          1 | scl_user_id |            1 | user_id     | A         |      611029 |     NULL | NULL   | YES  | BTREE      |         |               |
| scl             |          1 | idx_create_time         |            1 | create_time | A         |      856759 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sct             |          0 | PRIMARY   |            1 | id          | A         |        5590 |     NULL | NULL   |      | BTREE      |         |               |
| sct             |          1 | is_delete |            1 | is_delete   | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 

查看SQL执行计划

+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                              | key                         | key_len | ref                                  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | orde  | NULL       | index  | ope_orders_uid,idx_operator_id,idx_user_id | create_time_index           | 6       | NULL                                 |   19 |   100.00 | Using where |
|  1 | SIMPLE      | user  | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.orde.user_id            |    1 |   100.00 | Using where |
|  1 | SIMPLE      | car   | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.orde.car_id             |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | ocm   | NULL       | ref    | ope_charge_main_charge_code                | ope_charge_main_charge_code | 8       | bq_bjev_gxqc.orde.charge_stand_encod |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | admin | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.orde.update_userid      |    1 |   100.00 | Using index |
|  1 | SIMPLE      | scl   | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 8       | bq_bjev_gxqc.orde.coupon_id          |    1 |   100.00 | Using where |
|  1 | SIMPLE      | sct   | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.scl.coupon_type_id      |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+-------------+

 

三、分析思路

id都是1，表连接顺序从上往下

orde 表数据量 1551182，用到索引 create_time_index，type 是 index，表示是全索引扫描。extra是using where ，表示使用了 where 过滤。实际统计结果如下

select count(*) from orde
where orde.operator_id in (10,7,1048,8,9,998,1001,1002,1005,1006,1007,1009,1010,1011,1024,1012,1020,1013,1014,1015)
+----------+
| count(*) |
+----------+
|  1717046 |
+----------+

结果是 170万行，比原来的150万还要大，很明显统计信息不准确。MySQL实际肯定会拿着170万的数据去跟下一个表做关联的，这将会很恐怖。

 

接下来是user表，用到索引 primary，type 是 eq_ref，表示是唯一索引扫描。多表连接中使用主键或者唯一索引作为关联条件。extra也是using where，实际统计结果如下

select count(*) from user
where user.phone like '%17885004325%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+

结果只有一条。而且 user 表，phone字段上面是有索引的，基数也很高。为什么没有用到索引，细心的朋友应该发现了。用了两个%%，这是走不了索引的。

把两个%%去掉之后，执行速度在0.01秒。接下来看一下执行计划

+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                              | key                         | key_len | ref                                  | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | range  | PRIMARY,web_user_phone_unique_index        | web_user_phone_unique_index | 42      | NULL                                 |    1 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | orde  | NULL       | ref    | ope_orders_uid,idx_operator_id,idx_user_id | ope_orders_uid              | 4       | bq_bjev_gxqc.user.id                 |    8 |   100.00 | Using where                                            |
|  1 | SIMPLE      | car   | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.orde.car_id             |    1 |   100.00 | NULL                                                   |
|  1 | SIMPLE      | ocm   | NULL       | ref    | ope_charge_main_charge_code                | ope_charge_main_charge_code | 8       | bq_bjev_gxqc.orde.charge_stand_encod |    1 |   100.00 | NULL                                                   |
|  1 | SIMPLE      | admin | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.orde.update_userid      |    1 |   100.00 | Using index                                            |
|  1 | SIMPLE      | scl   | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 8       | bq_bjev_gxqc.orde.coupon_id          |    1 |   100.00 | Using where                                            |
|  1 | SIMPLE      | sct   | NULL       | eq_ref | PRIMARY                                    | PRIMARY                     | 4       | bq_bjev_gxqc.scl.coupon_type_id      |    1 |   100.00 | NULL                                                   |
+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+--------------------------------------------------------+

执行计划改变了，由 user 表作为最开始的驱动表，由上面可知，过滤条件后只剩下 1 条记录。1 条记录去关联ope_orders，这个速度还是很快的。但是 user 表为什么会排序暂时还没研究明白，order by 是在 orde 表上。有大神看到的话，可以互相讨论下。SQL已经优化到0.01秒了，也没有继续再优化下去的必要。

 

四、总结

如果列上面有索引的话，不要在列两端加上% ，类似这种 %aaa%，大部分场景会引起索引失效。aaa% 这种情况也是可以用到索引的，话。

 


推荐阅读
  • select

    CSS3选择器的使用方法详解，提高Web开发效率和精准度

    CSS3选择器的使用方法详解，提高Web开发效率和精准度
    本文详细介绍了CSS3新增的选择器方法，包括属性选择器的使用。通过CSS3选择器，可以提高Web开发的效率和精准度，使得查找元素更加方便和快捷。同时，本文还对属性选择器的各种用法进行了详细解释，并给出了相应的代码示例。通过学习本文，读者可以更好地掌握CSS3选择器的使用方法，提升自己的Web开发能力。 ... [详细]
  • select

    MyBatis多表查询与动态SQL使用

    MyBatis多表查询与动态SQL使用
    本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]
  • perl

    欢乐的票圈重构之旅——RecyclerView的头尾布局增加

    欢乐的票圈重构之旅——RecyclerView的头尾布局增加
    项目重构的Git地址：https:github.comrazerdpFriendCircletreemain-dev项目同步更新的文集：http:www.jianshu.comno ... [详细]
  • int

    HashMap的相关问题及其底层数据结构和操作流程

    HashMap的相关问题及其底层数据结构和操作流程
    本文介绍了关于HashMap的相关问题，包括其底层数据结构、JDK1.7和JDK1.8的差异、红黑树的使用、扩容和树化的条件、退化为链表的情况、索引的计算方法、hashcode和hash()方法的作用、数组容量的选择、Put方法的流程以及并发问题下的操作。文章还提到了扩容死链和数据错乱的问题，并探讨了key的设计要求。对于对Java面试中的HashMap问题感兴趣的读者，本文将为您提供一些有用的技术和经验。 ... [详细]
  • select

    如何在MySQL中将零值替换为先前的非零值？

    本文介绍了如何在MySQL中将零值替换为先前的非零值的方法，包括使用内联查询和更新查询。同时还提供了选择正确值的方法。 ... [详细]
  • select

    如何限制php数据库链接数和连接超时时间？

    本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]
  • char

    二叉树层序创建问题的解决方法

    本文介绍了解决二叉树层序创建问题的方法。通过使用队列结构体和二叉树结构体，实现了入队和出队操作，并提供了判断队列是否为空的函数。详细介绍了解决该问题的步骤和流程。 ... [详细]
  • fetch

    游标的使用笔记

    本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]
  • select

    MySQL外键1对多问题的解决方法及实例

    MySQL外键1对多问题的解决方法及实例
    本文介绍了解决MySQL外键1对多问题的方法，通过准备数据、创建表和设置外键关联等步骤，实现了用户分组和插入数据的功能。详细介绍了数据准备的过程和外键关联的设置，以及插入数据的示例。 ... [详细]
  • select

    MySQL8.0中的性能查看及SQL执行顺序解析

    本文介绍了在MySQL8.0中如何查看性能并解析SQL执行顺序。首先介绍了查询性能工具的开启方法，然后详细解析了SQL执行顺序中的每个步骤，包括from、on、join、where、group by、having、select distinct、union、order by和limit。同时还介绍了虚拟表的概念和生成过程。通过本文的解析，读者可以更好地理解MySQL8.0中的性能查看和SQL执行顺序。 ... [详细]
  • replace

    MooTools和JQuery并排 - MooTools and JQuery Side by Side

    IjustinheritedsomewebpageswhichusesMooTools.IneverusedMooTools.NowIneedtoaddsomef ... [详细]
  • python

    EzPP 0.2发布，新增YAML布局渲染功能

    EzPP 0.2发布，新增YAML布局渲染功能
    EzPP发布了0.2.1版本，新增了YAML布局渲染功能，可以将YAML文件渲染为图片，并且可以复用YAML作为模版，通过传递不同参数生成不同的图片。这个功能可以用于绘制Logo、封面或其他图片，让用户不需要安装或卸载Photoshop。文章还提供了一个入门例子，介绍了使用ezpp的基本渲染方法，以及如何使用canvas、text类元素、自定义字体等。 ... [详细]
  • select

    Explain如何助力SQL语句的优化及其分析方法

    Explain如何助力SQL语句的优化及其分析方法
    本文介绍了Explain如何助力SQL语句的优化以及分析方法。Explain是一个数据库SQL语句的模拟器，通过对SQL语句的模拟返回一个性能分析表，从而帮助工程师了解程序运行缓慢的原因。文章还介绍了Explain运行方法以及如何分析Explain表格中各个字段的含义。MySQL 5.5开始支持Explain功能，但仅限于select语句，而MySQL 5.7逐渐支持对update、delete和insert语句的模拟和分析。 ... [详细]
  • char

    EPPlus绘制刻度线的方法及示例代码

    EPPlus绘制刻度线的方法及示例代码
    本文介绍了使用EPPlus绘制刻度线的方法，并提供了示例代码。通过ExcelPackage类和List对象，可以实现在Excel中绘制刻度线的功能。具体的方法和示例代码在文章中进行了详细的介绍和演示。 ... [详细]
  • select

    MySQL子查询实例及用法详解

    本文主要介绍了MySQL中子查询的基本用法和三种用法，包括生成参考值、内层查询与外层查询的比较操作以及使用事件号在成绩表中找到学生的分数记录。通过详细解析子查询的实例，帮助读者更好地理解和应用子查询。 ... [详细]
author-avatar
mobiledu2502892717
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有