MySQL-索引优化(七)

两边都有百分号的,有全表扫描

mysql> EXPLAIN SELECT * FROM staffs where name LIKE '%july%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

百分号在左边,有全表扫描 

mysql> EXPLAIN SELECT * FROM staffs where name LIKE '%july';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

百分号在右边的,使用到了索引

mysql> EXPLAIN SELECT * FROM staffs where name LIKE 'july%';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

口诀: 百分Like加右边

 

解决 like '%字符串%'时索引不被使用的方法

准备数据

CREATE TABLE `tbl_user`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL ,
  `age` INT(11) DEFAULT NULL ,
  `email` VARCHAR(20) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('1aa1', 21, 'b@163.com');
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('2aa2', 222, 'a@163.com');
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('3aa3', 265, 'c@163.com');
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('4aa4', 21, 'd@163.com');

CREATE INDEX idx_user_nameAge ON tbl_user(`name`,`age`);

以下查询的字段只要全部是索引中的字段,就能使用索引

mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> EXPLAIN SELECT age FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id, name FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id, name, age FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果两边非要用%模糊查询,就要用覆盖索引

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
索引优化是数据库性能优化的重要部分,可以大幅提高查询效率。索引是一种数据结构,用于加速对表中数据的查找和排序。在查询中使用索引可以避免全表扫描,提高查询速度。以下是索引优化的一些技巧: 1. 确定索引类型 MySQL 支持多种索引类型,包括 B-Tree 索引、Hash 索引、Full-Text 索引等。不同的索引类型适用于不同的场景。例如,B-Tree 索引适用于范围查询,而 Hash 索引适用于等值查询。因此,在创建索引时,需要根据实际情况选择合适的索引类型。 2. 确定索引字段 索引字段是指在哪些字段上创建索引。一般来说,需要在经常用于查询的字段上创建索引。但是,创建太多的索引也会影响性能,因为每个索引都需要占用存储空间,而且在插入、更新和删除数据时也会增加额外的开销。因此,需要权衡索引的数量和存储空间的使用。 3. 索引覆盖查询 索引覆盖查询是指查询结果可以从索引中直接获取,而不需要再访问数据表。这样可以避免访问数据表的开销,提高查询效率。要实现索引覆盖查询,需要在查询语句中包含索引字段,并且查询语句只查询索引字段。 4. 索引列的顺序 在创建索引时,需要注意索引列的顺序。一般来说,应该把区分度高的列放在前面。区分度是指该列中不同值的数量与总行数之比。区分度越高,表示该列的值越能区分不同的行,因此放在前面可以提高索引效率。 5. 索引的长度 索引的长度是指索引列中的字符数或字节数。索引的长度对查询效率有影响,因为索引长度越长,索引树的高度越高,查询时需要访问的磁盘块数也就越多。因此,需要根据实际情况选择合适的索引长度。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值