mysql高级---语句优化,执行优化[重要]

前提:
这里介绍mysql 的一些高级知识,从mysql的用戶权限，mysql的逻辑架构,存储引擎,索引优化,查询截取优化这些方面来介绍

一. mysql的用戶权限
mysql的用户,用户权限都在mysql.user表中

select host,user,password from user;

这里host不包含远程登陆,远程登陆host为%
通过以下命令修改远程登陆的权限

#授予通过网络方式登录的的root用户 ，对所有库所有表的全部权限，密码设为123
grant all privileges on *.* to root@'%'  identified by 'root'; 
flush privileges

修改完再查看user表，host多了%，新增用戶，表权限全是修改user表内容

这样root用户就可以远程登录了

二. mysql 的sql_mode 配置
sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

在sql-mode是空的情况下,我们执行下面操作
表mytbl2 如下

#每个部门年龄最大的人

SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;

结果

明显这个结果是错误的,这时候 需要修改
set sql_mode=‘ONLY_FULL_GROUP_BY’;

ONLY_FULL_GROUP_BY 介绍:
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

修改sql_mode之后重新计算

结果就是:ERROR 1055 (42000): ‘mysql.mytbl2.NAME’ isn’t in GROUP BY
这就是正确的

对于groupBy 语句 select 后只能有group by 之后字段,聚合函数

三. mysql 的逻辑架构,sql查询语句的底层执行流程
和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。

这种架构可以根据业务的需求和实际需要选择合适的存储引擎

mysql 架构分开介绍
1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。连接者

2.服务层

2.1 Management Serveices & Utilities： 系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解： select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。

2.5 Cache和Buffer： 查询缓存。
cache 查
buffer 写
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

mysql 查询语句流程如上图,总结如下
(1)mysql客户端通过协议与mysql服务器建连接，发送查询语句

(2) 检查查询缓存，如果缓存中有，直接返回结果

(3) 缓存中没有结果,使用sql 接口分析sql 语句
(4)解析复杂sql ,mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”,mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。

(5)当解析树被认为是合法的了，并且由优化器将其转化成执行计划。这里执行优化包括建立索引,语句优先执行等等

(6)存储引擎按照计划分类执行–个人认为这里存储引擎可以理解为执行引擎

(7) 返回结果到客户端,并且保存结果到cache
innodb 把索引和内容都保存在在cache中了
myisam 只保存了索引

这里可以使用profile查看底层执行顺序,profile默认不开启

四. mysql 语句执行顺序
手写顺序

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
下面是经常出现的查询顺序：
五. mysql 存储引擎
看mysql现在已提供什么存储引擎:
show engines;

看你的mysql当前默认的存储引擎:
show variables like ‘%storage_engine%’;

这里逐个介绍引擎,从默认的InnoDB开始
1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

2、MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
总结二者区别如下:

mysql 数据库默认安装的表比如user表,默认是myisam 存储引擎,因为不存在高并发访问

还有其他引擎,比如Archive引擎,Blackhole引擎d等这里不介绍了
淘宝就是在innodb之上更新的搜索引擎xtraDB

六.索引优化分析
当数据库遇到如下问题:
性能下降SQL慢
执行时间长
等待时间长
我们会想到
我们会想到如下解决方法

索引
索引概述：
是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。可以理解为“排好序的快速查找数据结构”

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

索引目的：
提高查询效率，可以理解为字典，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的