MySQL高级01-MySQL架构介绍

1、MySQL简介

1、1概述

1、2高级MySQL

• MySQL内核
• sql优化工程师
• MySQL服务器的优化
• 查询语句的优化
• 主从复制
• 软硬件升级
• 容灾备份
• sql编程

2、Mysql Linux 版的安装（mysql5.5）

2、1下载地址

https://dev.mysql.com/downloads/mysql/

2、2检查当前系统是否装过mysql

可以用如下命令

rpm -qa|grep mysql

看当前的系统进程，如果有mysql服务的就会显示的。

2、3安装mysql服务端

2、4安装mysql客户端

2、5查看mysql安装时创建的mysql用户和mysql组

2、6mysql服务的启动和停止

2、7mysql服务启动后开始连接

• 首次连接成功

• 注意这里，因为MySQL默认没有密码，所以这里我们没有输入密码就直接连上了
• 按照安装server中的提示修改登录密码

2、8自启动mysql服务

2、9修改配置文件位置

拷贝

当前5.5版本： cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

 5.6版本 cp /usr/share/mysql/my-default.cnf /etc/my.cnf

2、10修改字符集和数据存储路径

• 1、查看字符集

show variables like 'character%';

show variables like '%char%';

看看出现的结果：

默认的是客户端和服务器都用了latin1，所以会乱码。

• 2、修改

[client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set=utf8 # The MySQL server [mysqld] port = 3306 character_set_server=utf8 character_set_client=utf8 collation-server=utf8_general_ci socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 [mysql] no-auto-rehash default-character-set=utf8

• 3、重启mysql

 service mysql stop;

 service mysql start;

• 4、重新连接后重新create databse并使用新建库，然后再重新建表试试
• 5、还是乱码的话就设值init_connect='SET NAMES utf8'              ##设定连接mysql是UTF8编码

2、11MySQL的安装位置

路径	解释	备注
/var/lib/mysql/	mysql数据库文件的存放路径	/var/lib/mysql/atguigu.cloud.pid
/usr/share/mysql	配置文件目录	mysql.server命令及配置文件
/usr/bin	相关命令目录	mysqladmin mysqldump等命令
/etc/init.d/mysql	启停相关脚本

在linux下查看安装目录 ps -ef|grep mysql

3、MySQL配置文件

3、1二进制日志文件log-bin

主从复制

3、2错误日志log-error

默认是关闭的，记录严重的警告和错误消息，每次启动和关闭时的详细信息等

3、3查询日志

默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的

3、4数据文件

3、4、1两系统

• Windows

C:\Program Files\MySQL\MySQL Server 5.7\data目录下可以挑选很多库

• Linux

看看当前系统中的全部库后再进去

默认路径：/var/lib/mysql

3、4、2frm文件

存放表结构

3、4、3myd文件

存放表数据

3、4、4myi文件

存放表索引

3、5如何配置

• Windows

my.ini文件

• Linux

/etc/my.cnf文件

4、MySQL逻辑架构介绍

4、1总体概览

       和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.连接层

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

2.服务层

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

3.引擎层

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

4.存储层

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

4、2查询说明

mysql的查询流程大致是：

• mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析
• 有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用API获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。
• 然后，mysql默认使用的BTREE索引，并且一个大方向是，无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

5、MySQL存储引擎

5、1查看命令

 1 如何用命令查看

  #看你的mysql现在已提供什么存储引擎:

  mysql> show engines;

  #看你的mysql当前默认的存储引擎:

  mysql> show variables like '%storage_engine%';

 

5、2MYISAM和InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响， 适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

 5、3改进

 

• Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

• 该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,

• 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。