MySQL之索引优化

一、Table Demo

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

这里边加入了联合索引

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

全值匹配

mysql> explain select * from employees where name = 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

算算这个key_len

key_len ： 显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

【字符串】

• char(n)：n字节长度
• varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

【数值类型】

• tinyint：1字节
• smallint：2字节
• int：4字节
• bigint：8字节

【时间类型】

• date：3字节
• timestamp：4字节
• datetime：8字节

如果字段为null，需要1字节记录是否为null。
索引最大长度768字节，字符过长时，mysql默认调用类似左前缀索引的处理，将前半部分的字符提取出来做索引

name varchar(24)—> 3 * 24 + 2 = 74 , 用了联合索引中的name

---

mysql> explain select * from employees where name = 'LiLei' and age= 22;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ken_len 长度78 ？

第二个是int , int 占 4个字节 ， 74 + 4 = 78 ，这个SQL用了联合索引中的 name + age

---

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

key_len = 74 + 4 + 72 = 140

---

mysql> explain select * from employees where name = 'LiLei'  and position = 'manager';

只用了联合索引中的name

最左前缀

如果索引了多列，要遵守最左前缀法则 , 指的是查询从索引的最左前列开始并且不跳过索引中的列。

mysql> explain select * from employees where name = 'LiLei' and age= 22;

符合最左前缀。

---

mysql> explain select * from employees where  age= 22 and position='manager';

不符合最左前缀。

user where : 使用 where 语句来处理结果，并且查询的列未被索引覆盖

1、禁止索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

2、存储引擎不能使用索引中范围条件右边的列，尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

3、mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

4、is null,is not null 一般情况下也无法使用索引

5、like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作。

%在前，还是要回想那个索引B+Tree ,

意味着前面可能还有其他的字符串， 那在树中的有序性没法保证啊！继续回想那个索引B+Tree , % 不在前面 意味着%前面的字符串固定， 那在树中的就是有序的，当然可以走索引

6、like 的优化

【问题：解决like’%字符串%'索引不被使用的方法？】

A： 使用覆盖索引，查询字段必须是建立覆盖索引字段

不敢说好太多， index 总比 all 好吧 。

B： 如果不能使用覆盖索引则可能需要借助搜索引擎 ，Es等

字符串不加单引号索引失效

7、少用or或in

用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

8、范围查询优化

增加索引

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法： 可以将大的范围拆分成多个小范围

9、like KK%相当于=常量，%KK和%KK% 相当于范围

转载自:https://blog.csdn.net/weixin_45395031/article/details/108760807