Mysql 存储引擎的区别以及索引查询失效的情况

存储引擎：就是指表在计算机上的存储方式。可以通过 SHOW ENGINES; 命令查询支持的存储引擎。

alter table test engine= innodb/memory/myisam/archive;

InnoDB 只有InnoDB支持事务存储
InnoDB 支持自动增长列的主键，以及外键
InnoDb 支持频繁的更新与删除
InnoDB 读写效率差，占用的空间大

MyISAM 曾经是Mysql的默认存储引擎，不支持事务，不支持外键
MyISAM 锁级别为表锁，INSERT和UPDATE操作需要锁定整个表
MyISAM 因为它保存了表的行数，当使用COUNT统计时不会扫描全表；

MEMORY 存储的表结构以frm的形式保存在硬盘上，数据全部在内存中
MEMORY 默认是以Hash作为索引，速度比使用B型树索引快。（也可以改为B型树索引）
MEMORY 生命周期短

Archive 以zlib的格式对数据进行压缩， 数据存储在arz的后缀文件中。 Archive表比MyISAM表要小大约75%，比InnoDB表小大约83%
Archive 不允许有索引与主键
Archive 只允许插入与查询
Archive 表比其他任何类型的表执行的物理I/O操作都要少。性能最高

mysql 引起索引失效的条件：
表一：
CREATE TABLE `user` (
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
KEY `index_name` (`name`),
KEY `index_age` (`age`),
KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表二：
CREATE TABLE `job` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) DEFAULT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

1，单表查询时，使用了 OR 条件索引失效
explain SELECT name,age,address FROM user where name = '光头强' or age = 20

2，like查询是以'%'开头（以%结尾是可以使用索引的）
explain SELECT name,age,address FROM user where name like '%头强'

3，对查询的列上有运算或者函数的
explain SELECT name,age,address FROM user where substr(name,-2)='头强'
explain SELECT name,age,address FROM user where age+1=12

4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
explain SELECT name,age,address FROM user where name = 10

5，左连接查询或者右连接查询查询关联的字段编码格式不一样（并且字符串长度必须一样）
EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引
EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

7、连接查询中，按照优化器顺序的第一张表不会走索引
EXPLAIN select a.name,a.age,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

8、如果查询中没有用到联合索引的第一个字段，则不会走索引