索引优化
索引优化
索引失效(避免)：
建表：
create table `staffs`( id int primary key auto_increment, name varchar(24) not null default '' comment '姓名', age int not null default 0 comment 'age', pos varchar(20) not null default '' comment '职位', add_time TIMESTAMP not null default CURRENT_TIMESTAMP COMMENT 'time' )CHARSET utf8 COMMENT '员工登记表'; drop table staffs; desc staffs; delimiter $ create procedure insert_staffs(in count int) begin declare i int default 0; a:while i<count do insert into staffs(name, age, pos, add_time) VALUES (concat('王',i),i,concat('i',i),now()); set i = i+1; end while a; end $ drop procedure insert_staffs; call insert_staffs(30000); select * from staffs; alter table staffs add index idx_staffs_nameAgePos(name,age,pos); show index from staffs;
1.全值匹配我最爱
2.最佳左前缀法则：
3.不在索引列上做任何操作 比如在索引列上加进行函数或者类型的转换
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））减少select *
6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
7.is null，is not null 也无法使用索引
8.like以通配符开头（‘%abc...’）mysql索引失效会变成全表扫描的操作
9.字符串不加单引号索引失效
10.少用or，用它来连接时会索引失效
建立了一个三个字段的两个索引，搜索一个字段
mysql> explain select * from staffs where name = '王6000'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 7 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
建立了一个三个字段的两个索引，搜索两个字段
mysql> explain select * from staffs where name = '王6000' and age = 32; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
建立了一个三个字段的两个索引，搜索三个字段
mysql> explain select * from staffs where name = '王6000' and age = 32 and pos = 'i33'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
搜索后两个字段发现type变为了all全查找key索引失效，rows变大extra变为了using where ref栏变为null
mysql> explain select * from staffs where age = 32 and pos = 'i33'; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 29593 | 1.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where pos = 'i33'; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 29593 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
如果没有name，那么索引失效，如果索引了多列，要遵守最佳左前缀发则，指的时查询从索引的最左前列开始并且不跳过索引中的列
个人理解：就相当于上楼梯，我搭了三层梯子，没有第一层梯子我只能停步不前，上不去，虽然我有第二层和第三层，但是没有第一层来通向第二三层，
或者没有第二层，第一层也到不了第三层
那么时没有办法用上这个梯子（索引）的
mysql> explain select * from staffs where name='王6000'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 7 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
age 没有查询，跳过了age ，那么只是一部分运用到了索引，造成后半部分索引失效
mysql> explain select * from staffs where name='王6000' and pos = 'i33'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 7 | 10.00 | Using index condition | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
3.不在索引列上做任何操作 比如在索引列上加进行函数或者类型的转换
type变为了全扫描
mysql> explain select * from staffs where left(name,3) = '王60'; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 29593 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
4.存储引擎不能使用索引中范围条件右边的列 索引用到了，type 变为了range范围在age层面上用于排序，导致pos索引字段失效
mysql> explain select * from staffs where name ='王6000' and age >25 and pos = 'i33'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 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 | 78 | NULL | 3 | 10.00 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））减少select *
type为ref select 索引列，直接Extra 为Using index ref为3个常数，性能最优
mysql> explain select name,age,pos from staffs where name ='王6000' and age = 25 and pos = 'i33'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 2 | 100.00 | Using index | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select name,age,pos from staffs where name ='王6000' and age > 25 and pos = 'i33'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+ | 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 | 78 | NULL | 2 | 10.00 | Using where; Using index | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
mysql> explain select * from staffs where name!='王6000'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 29593 | 65.00 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where name<>'王6000'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 29593 | 65.00 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
7.is null，is not null 也无法使用索引 全null
mysql> explain select * from staffs where name is null; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where name is not null; +----+-------------+-------+------------+------+--------------- +------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL |idx_staffs_nameAgePos | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec)
8.like以通配符开头（‘%abc...’）mysql索引失效会变成全表扫描的操作 写like 只能在右面写百分号
mysql> explain select * from staffs where name like '%60%'; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 29593 | 11.11 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where name like '%60'; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 29593 | 11.11 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where name like '60%'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 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.01 sec)
解决like%字符串%时索引不会被使用的方法？？？
双%% like type为all 全表扫描
mysql> explain select name ,age from tbl_user where name like '%na%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 2000 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
没有索引的情况下，查询任何索引，type都为all全表扫描
mysql> show index from tbl_user; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | tbl_user | 0 | PRIMARY | 1 | id | A | 2000 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.02 sec) mysql> explain select name from tbl_user where name like '%na%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 2000 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select age from tbl_user where name like '%na%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 2000 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
建立索引 name和age的联合索引
mysql> alter table tbl_user add index idx_user_nameAge(name,age); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from tbl_user; +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | tbl_user | 0 | PRIMARY | 1 | id | A | 2000 | NULL | NULL | | BTREE | | | YES | NULL | | tbl_user | 1 | idx_user_nameAge | 1 | name | A | 2000 | NULL | NULL | YES | BTREE | | | YES | NULL | | tbl_user | 1 | idx_user_nameAge | 2 | age | A | 2000 | NULL | NULL | YES | BTREE | | | YES | NULL | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.02 sec)
再次explain查看，发现帮type变为index extra using index 情况好了点 索引也用到了
mysql> explain select name, age from tbl_user where name like '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
id 主键索引
id，name 主键索引+index（name，age）
id，name，age 主键索引+index（name，age）
name,age index（name，age）
他们like ‘%%’，索引被用到，没有失效 用覆盖索引，意思就是建立的索引跟查的字段最好个数个顺序上最好完全一致。
mysql> explain select id from tbl_user where name like '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select name from tbl_user where name like '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select age from tbl_user where name like '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | 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 '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id,name from tbl_user where name like '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select name,age from tbl_user where name like '%na%'; +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 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 | 2000 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tbl_user where name like '%na%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 2000 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select id,name, age,email from tbl_user where name like '%na%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 2000 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
9.字符串不加单引号索引失效 因为当name字段为varchar时你给name一个整数，他会在6000这个整数上运行函数进行把6000转换为‘6000’，
固索引失效
mysql> explain select * from staffs where name = 6000; +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 29593 | 10.00 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+-------+----------+-------------+ 1 row in set, 3 warnings (0.01 sec)
实际用到key，type为ref
mysql> explain select * from staffs where name ='6000'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 3 | 100.00 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
10.少用or，用它来连接时会索引失效（mysql 5.5） 本机选用 mysql 8.0type变为range
mysql> explain select * from staffs where name ='6000' or name = '王6000'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 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 | 7 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
浙公网安备 33010602011771号