Loading

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、如果查询中没有用到联合索引的第一个字段,则不会走索引

 

posted @ 2021-04-30 17:12  Sam Xiao  阅读(91)  评论(0编辑  收藏  举报