mysql 高级sql_mysql高级部分

mysql逻辑架构

连接层

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

服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存查询，SQL的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程，函数等，在该层，服务层会解析查询并创建相应的内部解析树，并对其完成响应的优化确认查询表的顺序，是否利用索引等，最后生成相应的执行操作，如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样就解决大量读操作的环境中能够很好的提供系统性能。

存储引擎层

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

数据存储层

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

MyISAM和InnoDB对比

功能点

MyISAM

InnoDB

主外键

不支持

支持

事务

不支持

支持

行表锁

表锁，操作一条记录也会锁住整个表，不支持高并发

行锁，操作一条记录只会锁住一行，适合高并发

缓存

只缓存索引，不缓存数据

缓存索引也缓存数据，对内存要求高，内存大小直接影响性能

表空间

小

大

关注点

性能

事务

默认安装

是

是

sql慢的原因

查询语句效率低

索引失效或者没建索引

join太多

服务器参数设置

索引

什么是索引

索引是一种数据结构，提高查询的效率

索引会将数据排序，从而能够快速查找

数据库除了有数据以外，还维护着一个满足特定查找算法的数据结构，这种数据结构以某种方式指向数据，这样就可以利用这种数据结构高效地查找数据，这种数据结构就是索引。

索引的优点

提高查询效率，降低IO成本

对列进行排序，降低排序成本

索引的缺点

索引也是一张表，保存了键值对，并指向实际数据，索引列也需要占用空间。

虽然索引能够提升查询的效率，但是会降低更新的效率，比如insert，update，delete，因为更新表时，mysql不仅要保存新的数据，还要更新索引列的信息。

索引分类

单值索引

唯一索引

复合索引

哪些情况可以建索引

主键自动建立唯一索引

频繁作为查询条件的字段要建立索引

与其他表关联的字段，外键字段建立索引

查询中的排序字段

查询中统计或分组的字段

哪些情况不要建索引

表数据很少(300万一下的)

经常增删改的字段

数据重复且平均的字段，这种字段建索引没有意义

explain

explain可以模拟优化器执行sql语句，从而可以了解sql语句执行的内部原理，以此来分析性能瓶颈。

使用方法

mysql> explain sql_sentence;

作用

表的读取顺序

数据库读取的操作类型

哪些索引可以被使用

哪些索引实际被使用

表之间的引用

每张表有多少行被优化器查询

输出信息

id

表的读取顺序

id相同：自上而下执行语句

id不同：id越大，优先级越高，越先执行

id有相同的也有不同的：先执行id最大的第一个，然后平级的顺序执行，再执行小一级id的语句

select_type

查询的类型

simple：简单的select查询，查询中不含子查询或union

primary：查询中包含任何复杂的子查询，则最外层的查询是此类型

subquery：查询中包含子查询

derived：临时衍生表

union：第二个select出现在union之后

union result：union之后的结果

table

数据对应的表

type

显示查询使用了哪种类型

system：表中只有一行数据，是const类型的特例

const：通过索引一次就找到了，通过主键或唯一索引与常量值比较只匹配一行数据，用于单表查询

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，用于多表联查

ref：非唯一索引扫描，返回所有匹配的行

range：检索给定范围的行，比如where中的in、between、>、<

index：只遍历索引树，用于获取表的所有索引字段的数据，select id from table;

all：全表扫描，从磁盘中读取数据，百万级数据查询出现all则需要优化

possible_key

显示可能用到的索引值，但不一定会被实际用到

key

实际使用到的索引，如果为空则没有使用或者根本没有索引

key_len

索引中使用的字节数，长度越短越好

它表示索引字段的最大可能长度，而不是实际长度

ref

显示那一列索引被用到了，可能是const或者某一列

rows

大致估算找到所需信息需要读取的行数

extra

额外信息

using filesort：mysql对数据进行重新的排序，无法利用索引进行的排序叫文件排序

using temporary：排序时创建了临时表

using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据，如果出现了using where表示索引列被用来查找特定行，如果没出现则说明索引列只用来提取数据

索引优化

防止索引失效

最佳左前缀法则：如果索引了多个列，查询需要从索引的最前列开始，且不能跳过索引中的列

不要在索引列上做任何操作，比如各种函数操作，不然会导致索引失效并转向全表扫描

在where中使用了范围，则范围右边的列失效

尽量使用覆盖索引(索引列和查询列顺序一致)，少用select *

在使用不等于(!=或者<>)时，无法使用索引，使用的是全表扫描

is null和is not null无法使用索引

like以通配符开头的(like '%xyz')，索引会失效，变成全表扫描

隐式类型转换会导致索引失效

少用or

慢查询

在/var/lib/mysql有log文件

使用mysqldumpslow查看日志