《MySql高级技术篇》--学习笔记

MySQL架构

mysql架构图：

 mysql查询语句限制性from语句

mysql分层以及可插拔的存储引擎

1. 连接层
2. 服务层
3. 存储引擎层
4. 数据存储层

 

sql语句：

show engines; 查看当前支持的存储引擎

show variables like '%storage_engine%';  查看当前默认存储引擎

 MyISAM和InnoDB的区别：

MySQL优化分析：

创建索引：create index ind_user_name【索引名称】 on user【表名】(`name`【字段名称】);

SQL执行顺序：

我们手写SQL的顺序：

mysql解析SQL顺序：

MySQL JOIN连接

索引：

基本概念：

优劣势：

索引分类：

实际应用过程中建复合索引>单值索引

B-TREE：

性能分析：

mysql查询优化器：

Explain：

explain各字段解释：

type：

extra：

索引优化：

索引失效原因：

 

 根据上图，使用like查询时，如果左边有%，则会发生全表扫描问题，如果like的时候必须使用左右%且要求用上索引，那么需要使用覆盖索引来解决，也就是只查添加索引的字段

 尽量使用组合索引，遵循最左前缀原则；like左边带%无法使用索引，是因为匹配字段的开头是不确定的字段数量和内容，无法使用索引进行检索，左侧无%能使用索引是因为左侧开头能够确定字母，例如是k%，那么mysql最起码可以直接根据索引的排序功能先从k开头的索引进行查找

索引的作用有两个：1.检索；2.排序；检索意味着条件查询，排序意味着大于、小于、排序等操作有效

group by基本上都需要进行排序，会有临时表生成

查询截取分析：

大纲： 

mysql优化步骤：

查询优化：

order by：

group by： 

慢查询：

 

 使用set global slow_query_log=1默认本次生效，重启mysql后失效。

set global long_query_time=3;

使用select sleep(4);执行长时间查询来测试是否开启慢查询成功。

查询当前系统中记录的慢查询sql条数：show global status like '%Slow_queries%';

 

 

showProfile：

 

开启showProfile

 例如：show profile cpu,block io for query 1;

上方相关查询参数：

 日常开发过程中当使用showProfil工具分析时，如果出现以下四点那么需要注意：

 

 全局查询日志：

mysql锁机制：

 按照对锁的操作类型分为读锁和写锁：

 按照锁的粒度分为表锁和行锁。

表锁：

 

show open tables;查看当前数据库的表的状态，包含锁信息。

给表解锁：unlock tables;

读锁：

 写锁：

总结： 

 

 

 

行锁：

特点：

 

 并发事务带来的问题：

 

事务的隔离级别：

 行锁：

 

 

 更新数据时索引失效会导致行锁升级为表锁：

例如b字段是varchar类型，从前面的章节我们知道查询时，条件中如果varchar类型没有加单引号，会导致索引失效；同样的如果在更新的时候，如update table set a=xxx where b=xxx，此处b没有加单引号会导致索引失效，最终导致行锁变成表锁。

间隙锁：

总结：

查看当前数据库的连接：

show processlist命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别出有问题的查询语句等。

主从复制：

一主一从常见配置：

本例中master节点是windows，slave节点是linux。

主机配置（windows）：

从机配置（linux）：

 

主机授权给从机访问数据库：

从机配置需要复制的主机：