目录
1、简单的示例展示在MySQL中，LIKE查询可以通过一些方法来使得LIKE查询可以或许使用索引。以下是一些可以使用的方法：
下面是一个简单的例子，演示如何使用LIKE查询并且使索引有效 [code]-- 假设我们有一个表 users，有一个索引在 name 字段上 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255) ); -- 创建索引 CREATE INDEX idx_name ON users(name); -- 使用 LIKE 查询，并且使用索引举行查询的例子 -- 使用前导通配符，确保它紧跟着一个固定的字符 SELECT * FROM users WHERE name LIKE 'A%'; -- 使用索引 -- 制止使用后置通配符 SELECT * FROM users WHERE name LIKE '%A'; -- 不使用索引 -- 使用 COLLATE 来确保比力符合特定的语言或字符集规则 SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%A%'; -- 使用索引 [/code]在实际应用中，你须要根据你的数据库表布局、查询模式和数据分布来决定是否可以使用LIKE查询并且使索引有效。如果LIKE查询不能使用索引，可以考虑使用全文搜索功能或者其他查询优化技巧。 2、实验演示是否能正确使用索引2.1、表及数据预备预备两张表 t_departments 和 t_deptlist [code](root@192.168.80.85)[superdb]> desc t_departments; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | DEPARTMENT_ID | int | NO | PRI | NULL | | | DEPARTMENT_NAME | varchar(30) | YES | | NULL | | | MANAGER_ID | int | YES | | NULL | | | LOCATION_ID | int | YES | MUL | NULL | | +-----------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) (root@192.168.80.85)[superdb]> create table t_deptlist as select DEPARTMENT_ID,DEPARTMENT_NAME from t_departments; Query OK, 29 rows affected (0.09 sec) Records: 29 Duplicates: 0 Warnings: 0 (root@192.168.80.85)[superdb]> desc t_deptlist; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | DEPARTMENT_ID | int | NO | | NULL | | | DEPARTMENT_NAME | varchar(30) | YES | | NULL | | +-----------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) (root@192.168.80.85)[superdb]> alter table t_deptlist add constraint pk_t_deptlist_id primary key(DEPARTMENT_ID); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@192.168.80.85)[superdb]> create index idx_t_deptlist_department_name on t_deptlist(department_name); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@192.168.80.85)[superdb]> show index from t_departments; +---------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +---------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t_departments | 0 | PRIMARY | 1 | DEPARTMENT_ID | A | 29 | NULL | NULL | | BTREE | | | YES | NULL | | t_departments | 1 | idx_t_department_name | 1 | DEPARTMENT_NAME | A | 29 | NULL | NULL | YES | BTREE | | | YES | NULL | +---------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.00 sec) (root@192.168.80.85)[superdb]> show index from t_deptlist; +------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t_deptlist | 0 | PRIMARY | 1 | DEPARTMENT_ID | A | 29 | NULL | NULL | | BTREE | | | YES | NULL | | t_deptlist | 1 | idx_t_deptlist_department_name | 1 | DEPARTMENT_NAME | A | 29 | NULL | NULL | YES | BTREE | | | YES | NULL | +------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.00 sec) [/code]表t_departments有多个字段列，此中DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段，别的是非索引字段列 表t_deptlist有两个字段，此中DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段 2.2、 执行 where DEPARTMENT_NAME LIKE ‘Sales’[code](root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE 'Sales'; +----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t_departments | NULL | range | idx_t_department_name | idx_t_department_name | 123 | NULL | 1 | 100.00 | Using index condition | +----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) (root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE 'Sales'; +----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_deptlist | NULL | range | idx_t_deptlist_department_name | idx_t_deptlist_department_name | 123 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.01 sec)[/code]执行筹划检察，发现选择扫描二级索引index_name,表t_departments有多个字段列的行筹划中的 Extra=Using index condition 使用了索引下推功能。MySQL5.6 之后，增长一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，在存储引擎层直接过滤掉不满足条件的记载后再返回给 MySQL Server 层，减少回表次数，从而提拔了性能。 2.3、 执行 where DEPARTMENT_NAME LIKE ‘Sa%’[code](root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE 'Sa%'; +----+-------------+---------------+------------+-------+-----------------------+-----------------------+-------------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+-----------------------+-----------------------+-------------+------+------+----------+-----------------------+ | 1 | SIMPLE | t_departments | NULL | range | idx_t_department_name | idx_t_department_name | 123 | NULL | 1 | 100.00 | Using index condition | +----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) (root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE 'Sa%'; +----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_deptlist | NULL | range | idx_t_deptlist_department_name | idx_t_deptlist_department_name | 123 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)[/code]执行筹划检察，发现选择扫描二级索引index_name 2.4、 执行 where DEPARTMENT_NAME LIKE ‘%ale%’[code](root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE '%ale%'; +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_departments | NULL | ALL | NULL | NULL | NULL | NULL | 29 | 11.11 | Using where | +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) (root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE '%ale%'; +----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_deptlist | NULL | index | NULL | idx_t_deptlist_department_name | 123 | NULL | 29 | 11.11 | Using where; Using index | +----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)[/code]表t_departments有多个字段列的执行筹划的效果 type= ALL，代表了全表扫描。 2.5、 执行 where DEPARTMENT_NAME LIKE ‘%ale’[code](root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE '%ale'; +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_departments | NULL | ALL | NULL | NULL | NULL | NULL | 29 | 11.11 | Using where | +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) (root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE '%ale'; +----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_deptlist | NULL | index | NULL | idx_t_deptlist_department_name | 123 | NULL | 29 | 11.11 | Using where; Using index | +----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)[/code]表t_departments有多个字段列的执行筹划的效果 type= ALL，代表了全表扫描。 3、为什么表t_deptlist where department_name LIKE ‘%ale’ 和 LIKE '%ale%'用上了二级索引首先，这张表的字段没有「非索引」字段，所以 SELECT * 相称于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME，这个查询的数据都在二级索引的 B+ 树，由于二级索引idx_t_deptlist_department_name 的 B+ 树的叶子节点包含「索引值+主键值」，所以查二级索引的 B+ 树就能查到全部效果了，这个就是覆盖索引。 从执行筹划里的 type 是 index，这代表着是通过全扫描二级索引的 B+ 树的方式查询到数据的，也就是遍历了整颗索引树。 而 LIKE 'Sales’和LIKE 'Sa%'查询语句的执行筹划中 type 是 range，表现对索引列DEPARTMENT_NAME举行范围查询，也就是使用了索引树的有序性的特点，通过查询比力的方式，快速定位到了数据行。 所以，type=range 的查询服从会比 type=index 的高一些。 4、为什么选择全扫描二级索引树，而不扫描聚簇索引树呢？由于表t_deptlist 二级索引idx_t_deptlist_department_name 的记载是「索引列+主键值」，而聚簇索引记载的东西会更多，比如聚簇索引中的叶子节点则记载了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的非索引列。 再加上表t_deptlist 只有两个字段列，DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段，因此 SELECT * 相称于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME 不消执行回表利用。 所以， MySQL 优化器以为直接遍历二级索引树要比遍历聚簇索引树的本钱要小的多，因此 MySQL 优化器选择了「全扫描二级索引树」的方式查询数据。 5、数据表t_departments 多了非索引字段，执行同样的查询语句，为什么是全表扫描呢？多了其他非索引字段后，select * from t_departments where DEPARTMENT_NAME LIKE ‘%ale’ OR DEPARTMENT_NAME LIKE ‘%ale%’ ; 要查询的数据就不能只在二级索引树里找了，得须要回表利用找到主键值才气完成查询的工作，再加上是左模糊匹配，无法使用索引树的有序性来快速定位数据，所以得在二级索引树逐一遍历，获取主键值后，再到聚簇索引树检索到对应的数据行，这样执行本钱就会高了。 所以，优化器以为上面这样的查询过程的本钱着实太高了，所以直接选择全表扫描的方式来查询数据。 如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配或左右模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。 到此这篇关于MySQL使用LIKE索引是否失效的验证的示例的文章就先容到这了,更多相干MySQL LIKE索引内容请搜索脚本之家以前的文章或继承欣赏下面的相干文章希望大家以后多多支持脚本之家！ 来源：https://www.jb51.net/database/32601751i.htm 免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
/6 
|手机版|小黑屋|梦想之都-俊月星空
( 粤ICP备18056059号 )|网站地图
GMT+8, 2025-7-2 03:07 , Processed in 0.032500 second(s), 18 queries .
Powered by Mxzdjyxk! X3.5
© 2001-2025 Discuz! Team.
