mysql高级部分

mysql逻辑架构

连接层

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

服务层

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

存储引擎层

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

数据存储层

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

MyISAM和InnoDB对比

功能点	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，操作一条记录也会锁住整个表，不支持高并发	行锁，操作一条记录只会锁住一行，适合高并发
缓存	只缓存索引，不缓存数据	缓存索引也缓存数据，对内存要求高，内存大小直接影响性能
表空间	小	大
关注点	性能	事务
默认安装	是	是

sql慢的原因

1. 查询语句效率低
2. 索引失效或者没建索引
3. join太多
4. 服务器参数设置

索引

什么是索引

索引是一种数据结构，提高查询的效率
索引会将数据排序，从而能够快速查找
数据库除了有数据以外，还维护着一个满足特定查找算法的数据结构，这种数据结构以某种方式指向数据，这样就可以利用这种数据结构高效地查找数据，这种数据结构就是索引。

索引的优点

1. 提高查询效率，降低IO成本
2. 对列进行排序，降低排序成本

索引的缺点

1. 索引也是一张表，保存了键值对，并指向实际数据，索引列也需要占用空间。
2. 虽然索引能够提升查询的效率，但是会降低更新的效率，比如insert，update，delete，因为更新表时，mysql不仅要保存新的数据，还要更新索引列的信息。

索引分类

1. 单值索引
2. 唯一索引
3. 复合索引

哪些情况可以建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段要建立索引
3. 与其他表关联的字段，外键字段建立索引
4. 查询中的排序字段
5. 查询中统计或分组的字段

哪些情况不要建索引

1. 表数据很少（300万一下的）
2. 经常增删改的字段
3. 数据重复且平均的字段，这种字段建索引没有意义

explain

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

使用方法

mysql> explain sql_sentence;

作用

1. 表的读取顺序
2. 数据库读取的操作类型
3. 哪些索引可以被使用
4. 哪些索引实际被使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

输出信息

id

表的读取顺序

1. id相同：自上而下执行语句
2. id不同：id越大，优先级越高，越先执行
3. id有相同的也有不同的：先执行id最大的第一个，然后平级的顺序执行，再执行小一级id的语句

select_type

查询的类型

1. simple：简单的select查询，查询中不含子查询或union
2. primary：查询中包含任何复杂的子查询，则最外层的查询是此类型
3. subquery：查询中包含子查询
4. derived：临时衍生表
5. union：第二个select出现在union之后
6. union result：union之后的结果

table

数据对应的表

type

显示查询使用了哪种类型

1. system：表中只有一行数据，是const类型的特例
2. const：通过索引一次就找到了，通过主键或唯一索引与常量值比较只匹配一行数据，用于单表查询
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，用于多表联查
4. ref：非唯一索引扫描，返回所有匹配的行
5. range：检索给定范围的行，比如where中的in、between、>、<
6. index：只遍历索引树，用于获取表的所有索引字段的数据，select id from table;
7. all：全表扫描，从磁盘中读取数据，百万级数据查询出现all则需要优化

possible_key

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

key

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

key_len

索引中使用的字节数，长度越短越好
它表示索引字段的最大可能长度，而不是实际长度

ref

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

rows

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

extra

额外信息

1. using filesort：mysql对数据进行重新的排序，无法利用索引进行的排序叫文件排序
2. using temporary：排序时创建了临时表
3. using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据，如果出现了using where表示索引列被用来查找特定行，如果没出现则说明索引列只用来提取数据

索引优化

防止索引失效

1. 最佳左前缀法则：如果索引了多个列，查询需要从索引的最前列开始，且不能跳过索引中的列
2. 不要在索引列上做任何操作，比如各种函数操作，不然会导致索引失效并转向全表扫描
3. 在where中使用了范围，则范围右边的列失效
4. 尽量使用覆盖索引（索引列和查询列顺序一致），少用select *
5. 在使用不等于（!=或者<>）时，无法使用索引，使用的是全表扫描
6. is null和is not null无法使用索引
7. like以通配符开头的（like '%xyz'），索引会失效，变成全表扫描
8. 隐式类型转换会导致索引失效
9. 少用or

慢查询

在/var/lib/mysql有log文件
使用mysqldumpslow查看日志