一、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