mysql 进阶（一）

1启动问题，配置文件，编码问题

 

1配置文件

查看字符编码

设置字符编码：

1.

 

 总体要设置的

注意事项：修改编码，只对之后创建的数据库生效，因此，建议在刚安装完成mysql后 第一时间设置字符编码

ctrl+l 清屏

mysql 逻辑分层：

 

查询优化器： 将用户写的sql语句进行优化，存在一个问题（待补充）

存储引擎

 

sql优化

 sql查询慢的原因：

 

查询语句执行顺序

 索引的优点缺点 和适用情况等

 btree（非 b+ tree）

sql性能问题：

　　explain

 表结构

要求：查询相应数据

使用执行计划来分析多表连表查询时的笛卡尔乘积的区别（连表顺序），和执行顺序区别

 

 

 explian中的id

 执行计划来查询数据（多表查询）

 当将教师表数据增加到6条的时候（多表查询）

 

执行顺序发生变化的原因分析

当id值不同时执行顺序，从大到小的顺序执行（id值越大越优先）

（子查询，从里到外）

select_type的解释

主查询和子查询(多表查询和子查询混合使用)

 

简单查询 simple（不包含子查询，union）

衍生查询

　　a.在from子查询中只有一张表

 

　　b子查询中使用union进行链表查询的时候 左表就是衍生查询（mysql5.N 特性，在mysql8.0有修改）

 

计划任务中的 select_type字段的解释

索引类型 type

 

1。system(很少能达到这个级别) ：只有一条数据的表，或衍生表只有一条数据的主键查询 基本也达不到，很难达到这个type

2.const 必须是用到primarykey 或unique key 基本也达不到，很难达到这个type

 

3.eq_ref:唯一索引，对于每个索引建的查询，返回匹配为一行的数据（有且只有一个，切不能多，不能为0）基本也达不到，很难达到这个type

4ref:非唯一索引

　　对于每个索引建的查询，返回匹配的所有行（0个或多个）

range

　　索引指定范围的行，wherer后边是一个范围查询（betwenn,  > , < , >= ,<=  ，特殊：in 有时候会失效，all， 查询所有行）

 

 

index:

　　将索引树全部查一遍

all:

　　将表中的全部数据查询一遍

 比如 select * from course where tid=1    如果tid 不是索引需要扫面全部表中的数据

possible_keys:可能用到的索引，是一种预测，有时候不准

key:用到的索引

如果possible_keys 和key 是 null 则说明没有用到索引

key_len:索引长度

　　主要作用：1.判断复合索引是否全部被使用

　　　　单值索引也可以用key_len 但是一般都是一个固定的值

　　如果索引字段可以为空，这会多使用一个字节，用于标识。 则这个字段key_len 是 字段长度+1

ref: 注意与 type中的ref区分

　　作用：指明当前表所参照的字段

 

 

rows   

　　被索引优化查询的   数据个数 

　　

 

 

extra 

　　 a) using filesort: 性能消耗大，sql语句需要额外一个排序（查询） 常见于order by 语句中

小结：对于但索引：如果排序和查找是同一字段，不会出现 using filesort;如果排序和查找不是同一字段，会出现 using filesort;

避免方法：where哪些字段就 order by哪些字段

 复合索引：不能跨列  （最左前缀）

 

这种不会出现

如何避免：使用复合索引的时候 where 和order by 按照复合索引的顺序使用  不要跨列或者无序使用

b) using temporary :性能损耗较大，用到了临时表，已经有表了，但不适用，需要额外再使用一张临时表，一般出现在group by 语句中

 

如何避免：查询哪些列就按照哪些列进行分组

 c) using index:性能提升了，索引覆盖（覆盖索引）

　　原因：不读取源文件（原来的数据库表），直接能从索引中把数据取出

　　只要使用到的索引列 全部都在索引中，就是索引覆盖 using index

 

 索引覆盖会对possible_keys 和key造成影响

　　a:如果没有where则索引只出现在key中；

　　b:如果有where，则索引出现在key和possible_keys中

例子如下

d)using where:  需要回表查询

　　假设age是索引列

　　　explian   select name,age form user where age=11

　　则这个就会在extar中打印 using where

e)impossible where ：where 字句永远为false

 优化示例：

 

不好的使用顺序

 using filesort 文件内排序，

　　 where 和order by 一起用的时候，不要跨列使用，否则回出现这个 查询效率低

 

 

 总结：1如果复合索引（a,b,c,d） 和使用顺序一致（且不跨列使用（where 和order by 一起用的时候不要跨列）），则复合索引全部使用，否则使用部分索引