mysql优化之搜索引擎_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% 相当于范围