初步了解mysql
mysql架构:
connectors -----> 连接器
Management Services & Utilities -----> 系统管理和控制工具
connection Pool ------> 连接池
Sql Layer Mysql业务层:
Sql Interface -----> sql 接口 用于接收Sql DML DDL
Parser ----> 解析器,用于语法解析,形成语法树
Optimizer ----> 查询优化器,用于找到最佳索引,也就是使用最优的explain
Cache和Buffer ----> 缓存,8.0以后废了,redis不香吗?
Pluggable Storage Engines ----> 可插拔存储引擎,在建表的时候指定,也是以表为单位
例:create table tableName engine = InnoDB/Memory/MyISM
mysql简化版执行流程
存储引擎:
MyISAM: 高速,插入,查询速度++, 不支持事物,支持表锁但不支持行锁,不支持外键,所以只用来读,就还行
存储:.frm--->表定义文件
.myd--->数据文件
.myi--->索引文件
索引结构: B+Tree
InnoDB:5.5以后默认使用,事物、行锁、外键它都支持,比MyISAM处理速度稍慢
存储:.frm--->表定义文件
.ibd--->数据文件+索引文件
索引结构: B+Tree
Memory:内存引擎,快,非常快,没有IO 你琢磨一下子是不是得快,但是在内存上保存数据慎用,
重启 == 数据丢失,而且对表的大小有要求,不能建立太大的表
注:同一个数据库可以使用多种存储引擎的表,根据业务具体选择
B+Tree:
我觉得在这里最重要的点是,B+上面的所有节点,都会出现在叶子节点,而且叶子节点自形成一个链表,这样排序起来就相对简单
索引树区别:
MyISAM:
分为主键索引+辅助索引(不是主键索引都叫这个),它的所有索引树的叶子节点下都会挂载,
对应行数据的地址值,先查到索引位置,然后根据挂载的地址值再去.myi文件中找到该行数据
InnoDB:
也是有主键索引+辅助索引,不过在InnoDB的主键索引树的叶子节点,会直接挂载对应的行数据,
而在辅助索引的叶子节点,挂载的是对应的主键索引,所以会出现一个新的词:回表
回表的意思就是说,查询优化器使用的是辅助索引,会先在辅助索引树定位,
然后根据辅助索引树叶子节点挂载的主键索引位置,再去主键索引树找到对应叶子节点下挂载的对应行数据
注:说到这里大家可能会有个疑问,如果我建表的时候没有建主键咋办????
-----> 没有的话 mysql 会找你的唯一列作为主键索引,如果你连唯一列都没有,那么mysql做主,
跟你生成一个虚拟的主键
mysql的explain:
id:
id是一样的话–>执行顺序由上至下 id不一样的时候,id值越大,越先执行 一个sql中,id越少越好
select_type(查询类型):
分为普通查询、联合查询、子查询、复杂查询
simple:单边查询喽
primary:查询中如果包含了任何复杂的子部分,最外层查询泽会被标记为primary,就是主查询
derived:出现了临时表
subquery:在select或者where中包含了子查询--子查询
dependent subquery:这个subquery的查询要受到外部表查询的影响
dependent subquery:在select或者where中包含了子查询,而且该子查询是基于外层的
uncacheable subquery:当使用了@@来引用系统变量的时候,不会使用缓存
union:如果第二个select出现了union之后,就会被标记为union,但是如果union包含了from子句的子查询中,外层select将被标记为derived
dependent union:这个union要受到外部表查询的影响
union result:从union表中获取结果的select
table:
这个数据是基于哪张表,表别名 显示别名
type:
查询的访问类型
system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > > index_merge> index > ALL
注:优化器会选择一个最优索引,而且除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引
system:
表中只有一行数据或者空表(这玩意一般是见不到)
const:
使用唯一索引(肯定包括主键),where等值条件时,通常就是const
eq_ref:
这个一般针对唯一索引---->是多表join的时候,对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较操作都是‘=’,查询效率较高
ref:
这个一般针对非唯一索引----->使用等值查询非主键,非唯一索引的等值连接,或者最左前缀规则索引查询
fulltext:
全文索引,在es之后 实在无用,但是mysql中全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
ref_or_null:
与ref方法类似,只是增加了null值的比较。实际用的不多。
unique_subquery:
用于where中的in形式子查询,子查询返回不重复值唯一值。
index_subquery:
用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
range:
索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge:
merge,很明显,使用了两个以上的索引,最后取交集或者并集,比如and,or的条件使用了不同索引的时候就会出现
注:很多人喜欢将他放在ref_or_null后面,但是实际上由于要读取多个索引,性能可能大部分时间都不如range
index:
条件出现在了索引树中的节点的。但是可能没有完全匹配索引。
all:
就很废了,就是全表扫,流程是将数据从存储引擎层全部拿到sql layer(你可以理解为三层架构中的service层)层处理过滤,这样IO 消耗太大了
possible_keys
显示–可能–应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
key
实际使用的索引。如果为NULL,则没有使用索引。
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长,说明索引使用的越充分。
ref:
常数等值查询,显示const
链接查询,显示关联字段
使用了表达式或者函数、内部隐式转换,可能是func
rows:
执行计划的估计---->注意是估计扫描行数
extra:(这个里面可能有多个using)
no tables used:
不带from,就是没有表呗
using filesort:
排序无法使用索引时,就出现这个,常见order by和group by语句中
using index:
查询不回表,就是说覆盖索引了
using temporary:
出现了临时表存中间结果
distinct:
在用到了索引的索引字段上使用了distinct关键字
using where:
引擎不能满足所有查询条件,需要在sql layer层过滤
sql使用索引:
全值匹配最好;
联合要遵循最左前缀;
不要在索引列做计算
范围条件右边的列就失效了(对索引顺序来说,而不是值where后面的条件顺序)
覆盖索引也不错,可以不回表
不要在索引字段上使用不等
主键索引字段不要判断null
like时 ‘常量%’ 尽量不要'%常量'
字符串索引字段记得加‘’ mysql里面的 转型 char转int可以继续用索引 int转char就用不到索引了
索引字段不要使用or 可以用union优化