3天怒肝5万字！阿里P7大佬手写MySQL超全笔记，还担心学不会吗？

本文链接：https://blog.csdn.net/vip0809/article/details/117784315

下面对于MySQL进行相关介绍，文档的内容较为基础，针对刚开始学习的同学，仅仅涉及操作，少量原理，大佬请绕道哦。因字数太多，会分为两篇发放。

一、MySQL架构介绍

1-MySQL简介

概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL是开源的，所以你不需要支付额外的费用。

MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。

MySQL可以运用于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。

MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。

2-RPM安装

①下载地址

MySQL Linux版（CentOS）官网下载地址

②检查当前系统是否安装过MySQL

查询命令：rpm -qa|grep -i mysql
删除命令：rpm -e RPM软件包名(该名字是上一个命令查出来的名字)`

没装过：

已装过

③安装MySQL服务端（注意提示）

安装命令：rpm -ivh RPM软件包名

如果报错，可以说缺包：

rpm -ivh
MySQL-server-5.5.48-1.linux2.6.i386.rpm --nodeps --force 解决了依赖性关系，强制安装

yum -y install glibc.i686
yum -y install libncurses.so.5
yum -y install libaio.so.1
yum -y install libz.so.1

④安装MySQL客户端

安装命令：rpm -ivh RPM软件包名

rpm -ivh
MySQL-server-5.5.48-1.linux2.6.i386.rpm --nodeps --force 解决了依赖性关系，强制安装

查看MySQL安装时创建的MySQL用户和MySQL组

cat /etc/passwd |grep mysql
cat /etc/group |grep mysql

或者可以执行 mysqladmin --version 命令。

或者ps -ef | grep mysql

⑤MySQL服务启动与暂停

service mysql stop/restart/start
ps -ef | grep mysql查看

启动 service mysql start 可以报错

因为这个文件的问题：
mv /etc/my.cnf /etc/my.cnf.bak

⑥ROOT密码设置和开机自启动

mysql服务启动后，开始连接。

首次连接成功

输入命令mysql 因为初始的root用户没有密码的，所以直接输入mysql就可以登录了
退出命令exit

按照安装Server中的提示修改登录密码

/usr/bin/mysqladmin -u root password coderxz

密码其实对应着mysql中的mysql数据库中的user表：

也可以具体到IP 192.168.1.%

自启动mysql服务

⑦修改配置文件位置

cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

为什么mysql会自己读取/etc/my.cnf？

打开mysql.server你就会发现conf=/etc/my.cnf居然是写死的。

link

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

⑧修改字符集

查看字符集

show variables like 'character%";
show variables like ‘%char%";

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

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

vim /etc/my.cnf

1234567891011121314151617[client]
#password = your_password
port   = 3306
socket    = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysqld]
port   = 3306
character_set_server=utf8
character_set_client=utf8
socket  = /var/lib/mysql/mysql.sock
collation-server=utf8_general_ci
#(注意linux下mysql安装完毕是默认：表名区分大小写；0：区分大小写；1：不分区大小写)
lower_case_table_names=1
#(设置最大连接数，默认为151，MySQL服务器允许的最大的连接数为16384)
max_connections=1000
[mysql]
default-character-set = utf8

重启数据库

service mysql start
service mysql stop

重新连接后重新create databse并使用新建库，然后再重新建表试试

3-MySQL配置文件

主要配置文件

二进制日志log-bin

用于主从复制

错误日志log-error

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

查询日志log

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

数据文件

两系统windows输入mysql后select @@database;linux默认路径：/var/lib/mysql
frm文件（form）存放表结构
myd文件（my data）存放表数据
myi文件（my index）存放表索引

如何配置

Windows - my.ini文件
Linux - /etc/my.cnf文件

4-MySQL逻辑架构简介

总体概览

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

1.连接层

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

2.服务层

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

3.引擎层

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

4.存储层

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

1.Connectors

指的是不同语言中与SQL的交互

2 Management Serveices & Utilities：

系统管理和控制工具

3 Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句），根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果(SQL查询顺序优先级从高到低：
from-where-groupby-having-select-orderby-limit)
他使用的是“选取-投影-连接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果

7 Cache和Buffer：查询缓存。

它的主要功能是将客户端提交给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

8 、存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。
从图中还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
注意：存储引擎是基于表的，而不是数据库。

5-存储引擎简介

查看命令：

查看mysql以提供什么存储引擎
- show engines;

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

12345678910mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.01 sec)

MyISAM和InnoDB

阿里巴巴、淘宝用哪个？

Percona为MySQL数据库服务器进行了改进，在功能和性能上较MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能、为DBA提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。
该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好，
阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
AliSql+AliRedis

二、索引优化分析

1-SQL性能下降原因

1.查询语句写得烂没建索引

2.索引失效

单值

12select * from user where name='';
create index idx_user_name on user(name);

复合

12select * from user where name='' and email='';
create index idx_user_name on user(name, email);

3.关联查询太多join（设计缺陷或不得已的需求）

4.服务器调优及各个参数设置（缓冲、线程数等）

2-SQL执行加载顺序

123456789101112131415161718SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN 
    <right_table> 
ON
    <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_number>

机读 先从from开始读

123456789101 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>

总结