MySQL数据库(七)：底层概念扩展

1、MySQL的性能了解

1.1影响服务器性能的几个方面

1.服务器硬件(处理器、内存等)
2.服务器的操作系统(linux（centos、ubantu）、windows的选择)
3.数据库存储引擎的选择(innodb、myisam)
4.数据库参数配置
5.数据库结构设计和SQL语句

1.2SQL性能下降原因

关于sql语句的性能，都是指查询操作，其他操作一般不会影响性能
1.查询语句写的不好
2.索引失效
3.关联查询太多join
4.服务器调优及各个参数设置

1.3SQL加载的顺序

①想要查询的sql语句

select distinct 
    <select _list>
from 
    <left_table>
join  <right_table> on <join_codition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

②所写sql语句查询的顺序，面试常问，先是执行from从哪张表里面读，在执行连接的是哪种表，详情看下面的顺序，只需要了解手写和机读的顺序不一致即可

1.4MySQL常见瓶颈

①CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘读取数据的时候
②IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
③服务器硬件的性能瓶颈
性能优化简单理解指的就是是优化查询语句，硬件性能只能通过更换设备优化，mysql性能可以通过优化sql语句来优化

2、explain分析SQL语句

/G可以使得结果不以列表形式展现，而是以文本形式展现

explain select t2.* from t1,t2,t3 where t1.id=t2.id and t1.id=t3.id and t1.other_column='wchao'/G;

①使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的，查询优化器属于服务层
②语法：explain + SQL语句
③作用：表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询

3、explain字段解释

sql优化面试时候聊select_type和type以及key、extra必须会

3.1id

表的读取顺序也是select查询的顺序号,包含一组数字,表示查询中执行select子句或操作表的顺序包含两种情况:
①id相同,执行顺序由上至下
②id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

3.2select_type

3.3table、partitions

显示这一行的数据是关于哪张表的以及查询访问的分区

3.4type

最好到最差的顺序如下

system > const > eq_ref > ref > range > index > ALL

system：表只有一行记录(等于系统表),这是const类型的特例
const：表示通过索引一次就找到了,const用于比较primary key
eq_ref：唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配
ref：非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
range：只检索给定范围的行,使用一个索引来选择行。
index：Full Index Scan,index与ALL区别为index类型只遍历索引树。
ALL：将遍历全表找到匹配的行

3.5possible_keys、key

①显示可能应用在这张表中的索引,一个或多个。实际使用的索引。如果为null,则没有使用索引
②查询中若使用了覆盖索引,则该索引仅出现在key列表中(类似于应到多少人实到多少人)

3.6key_len

①表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
②key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
③长度的计算规则

varchr(24)变长字段且允许NULL
24*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL  
10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL      
10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL    
10*(Character Set：utf8=3,gbk=2,latin1=1)

3.7rows、extra

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
包含不适合在其他列中显示但十分重要的额外信息
①Using filesort,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为"文件排序"
②Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
③Using index,使用了索引,避免了全表扫描
④Using where,使用了where过滤
⑤Using join buffer,使用了连接缓存
⑥impossible where,不可能的条件,where子句的值总是false

4、索引的分类

4.1回表

①select * from person where ID = 6,因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。
②如果执行的sql语句是非主键索引select * from person where age = 18
上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。
③从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。

4.2覆盖索引

我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做"覆盖索引"，通过为想要查询的字段创建索引来避免回表，这就叫做覆盖索引

4.3索引下推

select * from xx where name=？ and age=？;

1. 没有索引下推：先根据name从引擎中获取符合规则的数据，在到server层对age进行过滤
2. 有索引下推：根据name，age两个条件在引擎中获得对应的数据