个人专题目录

1. Mysql简介

1.1 概述

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

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

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

Mysql支持大型的数据库，可以处理拥有上千万条记录的大型数据库。

Mysql使用标准的SQL数据语言形式。

Mysql可以允许于多个系统上，并且支持多种语言。这种编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等。

Mysq对PHP有很好的支持，PHP是目前最流行的Web开发语言。

Mysql支持大型数据库，支持5000万条记录的仓库，32位系统表文件最大可支持4GB，64位系统最大支持的表文件为8TB.

Mysql是可以定制，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

1.2 高级MySQL

mysql内核

sql优化工程师

mysql服务器的优化

查询语句优化

主重复制

软硬件升级

容灾备份

sql编程

2. MysqlLinux版本的安装--mysql5.5

2.1 mysql5.5

下载地址

下载地址:https://dev.mysql.com/downloads/mysql/

检查当前系统是否安装过mysql

可以用 rpm -qa|grep mysql

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

查询命令：rpm -qa|grep -i mysql

已装过：

# rpm -qa|grep -i mysql
mysql-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64

删除命令：rpm -e RPM 软件名名（该名字是上一个命令查出来的名字）

安装mysql服务端(注意提示)

rpm -ivh MySQL-server-5.xx.linux.xx.rpm

安装mysql客户端

# cat /etc/passwd|grep mysql
mysql:x:498:500::/home/mysql:/bin/bash
# cat /etc/group|grep mysql
mysql:x:500:
You have mail in /var/spool/mail/root

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

mysql服务的启+停

# service mysql start
# mysql

mysql服务启动后开始连接

首次连接成功

注意这里,,因为mysql默认没有密码,所以这里我们没有输入密码就直接连上了

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

# usr/bin/mysqladmin -u root password 123456
# mysql -u root -p123456

自启动mysql服务

# 设置开机自启动服务
# chkconfig mysql on
# chkconfig --list |grep mysql
# 查看是否已经设置好
# ntsysv

修改配置文件位置

ls -l *.cnf
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

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

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

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

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

# 修改
default-character-set=utf8

character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
# 注意linux下mysql安装完后是默认：表名不区分大小写，列名不区分大小写：0：区分  1：不区分
lower_case_table_names=1
# 设置最大连接数，默认为151，mysql服务器允许的最大连接数16384
max_connections=1000

default-character-set=utf8

Mysql的安装位置

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

3. Mysql配置文件

3.1 主要配置文件

二进制日志log-bin

主重复制

错误日志log-error

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

查询日志log

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

数据文件

两系统

windows

D:\ProgramFiles\MySQL\MySQLServer5.5\data目录下可以挑选很多库

linux

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

默认路径：/var/lib/mysql

frm文件

存放表结构

myd文件

存放表数据

myi文件

存放表索引

如何配置

windows

my.ini文件

Linux

/etc/my.cnf文件

4. Mysql逻辑架构介绍

4.1 总体概览

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

clip_image028.jpg

• 连接层
  • 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似与tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
• 服务层
  • 第二层架构主要完成大多少的核心服务功能，如sql接口，并完成缓存的查询，sql的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
• 引擎层
  • 存储引擎层叠 ，存储引擎真正的负责了mysql中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
• 存储层
  • 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

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 语句的结果

他使用的是“选取-投影-联接”策略进行查询。

   用一个例子就可以理解： 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 可插拔存储引擎的特色。

 从图2还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意：存储引擎是基于表的，而不是数据库。

4.2 查询说明

5. Mysql存储引擎

ISAM(Indexed Sequential Access Method)

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

注意：使用ISAM注意点：必须经常备份所有实时数据。

MyISAM

MyISAM是MySQL的ISAM扩展格式（MySQL5.5之前版本的缺省数据库引擎）数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

MyISAM引擎使用注意：必须经常使用Optimize Table命令清理空间；必须经常备份所有实时数据。工具有用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。不支持事务。数据越多，写操作效率越低。因为要维护数据和索引信息。（索引列越多，相对效率月底。）

如果使用该数据库引擎，会生成三个文件：

.frm:表结构信息

.MYD:数据文件

.MYI:表的索引信息

InnoDB

InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外键。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。是现在的MySQL（5.5以上版本）常用版本默认引擎

MySQL 官方对InnoDB是这样解释的：InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署的性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

在MySQL5.7版本中，InnoDB存储引擎管理的数据文件为两个：分别是frm,idb文件。

InnoDB特点：

1）、支持事务

2）、数据多版本读取（InnoDB+MyISAM+ISAM）

3）、锁定机制的改进

4）、实现外键

Memory存储引擎

Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash之后，Memory的表就只剩下一个结构了。Memory表支持索引，并且同时支持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。

NDBCluster存储引擎

NDB存储引擎也叫NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。

Merge存储引擎

MERGE存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创建MERGE表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。BDB存储引擎

BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是MySQL自己开发实现的一个存储引擎，而是由SleepycatSoftware所提供，当然，也是开源存储引擎，同样支持事务安全。

FEDERATED存储引擎

FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程MySQL服务器上面的数据的访问接口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用FEDERATED存储引擎才行，因为MySQL默认是不起用该存储引擎的。

ARCHIVE存储引擎

ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操

作，仅支持插入和查询操作。锁定机制为行级锁定。

BLACKHOLE存储引擎

BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。

CSV存储引擎

CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

5.1 查看命令

# 查看mysql提供了什么存储引擎
mysql> show engines;
# 查看当前默认的存储引擎
show variables like '%storage_engine%'
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
也可以在MySQL配置文件中查看。 windows - my.ini。 Linux - my.cnf
# 查看数据库表所用的存储引擎
show create table table_name
# 创建表指定存储引擎
create table table_name (column_name column_type) engine = engine_name
# 修改表的存储引擎
alter table table_name engine=engine_name

在MySQL配置文件中修改下述内容：
default-storage-engine=INNODB
MySQL配置文件：
windows系统 - MySQL安装目录/my.ini （5.7版本my.ini文件在数据目录中。 C:/programdata/MySQL Server 5.7/mysql/）
linux系统 - /etc/my.cnf

5.2 MyISAM和InnoDB

1. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

2. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

3. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

5.3 阿里巴巴，淘宝用哪个

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