捕捉mysql中不可忽视的知识点

一、mysql命令的分类

mysql命令可分为客户端命令和服务器端命令两类：

1）客户端命令

c:提前终止结束语句

g:无论语句结束符是什么，直接将此语句送至服务器端执行；

G:无论语句结束符是什么，直接将此句送到服务器端执行，而且结果以竖排方式显示；

! COMMAND:执行shell命令

W:语句执行结束后显示警告信息；

#:对新建的对象，支持补全功能；

r：重新连接到服务器上来

补充：mysql客户端命令里提到的还有 mysqldump(备份工具), mysqlimport(导入工具),mysqlcheck

2）服务器端命令：

mysqld

mysql_multi： 支持多实例的

mysql_safe： 支持安全线程的

mysqlbinlog： 查看mysql的二进制工具

mysqlhotcopy：mysql的备份工具

服务器语句有语句结束符，默认为分号

d: 定义语句结束符（delimiter定义语句结束符）

//：语句结束符

eg：show database // 若想换回来使用d

所有服务器语句只有执行客户端才能进行，而客户端不需要语句结束符；

服务器端命令如何获取帮助？

help COMMAND :获取帮助信息

help command index：获取索引帮助信息 （要想获得进一步的帮助信息，可以把执行help command index后显示的URL后的链接复制下来贴到浏览器内还能获取到官方文档）

二、mysqladmin命令的使用：

1）简介：mysqladmin用来执行管理命令的专用命令，也是一个客户端命令，通过客户端连接到服务器端去管理命令，mysqladmin很独特，它是一个具有许多子命令的命令

2）用法: mysqladmin [option] command [arg] [command [arg]]…

mysqladmin -uroot -p password ‘NEW_PASS’ 为用户设定密码的

3）常用命令：

processlist 进程列表，可以列出服务器上正在执行所有进程列表

status 查看mysql的状态

–sleep N: 显示频率

–count N：显示多个状态

eg:mysqladmin status –sleep 2 mysql两秒钟显示一次

eg:mysqladmin status –sleep 2 –count 2 mysql两秒钟显示1次显示2次

extended-status ：显示状态变量

varitables: 显示服务器变量

flush-privileges: 让mysqld重读授权表，等同于reload；

flush-status：重置大多数的服务器的状态变量

flush-tables；关闭打开的所有的表

flush-threads: 重置线程池的，或线程缓存

flush-logs: 二进制和中继日志滚动

flush-hosts: 刷新主机，清除主机的内部信息

kill ：杀死一个线程的

refresh: 相当于同时执行flush-hosts和flush-logs同时执行

shutdown: 关闭mysql服务器进程，可停止mysql服务器

version: 服务器版本及当前状态信息

start-slave:启动复制，启动从服务器复制线程(复制的线程包括两个SQL thread和IO thread)

stop-slave:关闭复制，停止复制线程；

三、存储引擎

1）存储引擎的概念：存储引擎从某种意义上讲叫表类型，每一个表都可以自由的独立的选择一种存储引擎，存储引擎不是数据库之间的概念而是表之间的的概念，同一个数据库中的多张表完全可以使用不同的存储引擎，也就意味着底层的存储机制是各不相同的。

2）存储引擎主要包括MyISAM和InnoDB

MyISAM:(存储引擎，也被称为表类型)无事务，表锁；每个表三个文件分别如下：

.form:表结构定义文件

.MYD:表数据定义文件

.MYI:表索引定义文件

InnoDB：事务，行锁；所有表共享一个表空间文件(建议：每表一个独立的表空间文件；)

.form: 表结构

.ibd: 表空间（里面存储了表数据和表索引）

3）show engines:可以显示当前服务器所支持的所有引擎，

show table status [like …] 显示一张表的状态信息

eg：show table status like ‘use’; 显示use表的状态信息

eg：show table status like ‘use’G 查看use表的属性信息，并竖排显示

四、mysql的数据类型(MySqL data types )相关的知识点

1、mysql的数据类型：

1）数值型

精确数值

int

decimal 十进制

近似数值

float

double

real 实数

2）字符型

定长：char(#)、binary

变长：varchar(#)、varbinary

text,blob（大字符，text不区分大小写，blob区分大小写）

enum，set （内置类型）

3）日期时间型

data，time，datatime，timestamp

2、数据类型的意义：

1）存入的值类型

2）占据的存储空间

3）定长和变长

4）如何比较及排序

5）是否能够创建索引

2、数值类型：

int 整型

tinyint （微整型） 有符号时是0-255，无符号时是-128-127

smallint （小整型）

mediumint（中整型）

int （整型）

bigint （大整型）

decimal：定点数值

float：单精度浮点值 4bytes

double：双精度浮点值 8bytes

bit：按位存储

3、字符串数据类型：

char 字符型最多只能存储255个字符 定长

varchar 最大只能存储65535个字符 变长（不区分字符大小写）

binary 定长

varbinary 变长 (区分字符大小写)

tinyblob 微型blob 255字节（二进制的大对象，它们是字节字符串区分字符大小写）

blob 标准blob 64kb

mediumblob 中级blob 16Mb

longblob 长blob 4Gb

tinytext 微型text（不区分字符大小写）最多只能存储255个字符

text 标准text 最多只能存储65535个字符

mediumtext 中级text 最多只能存储16777215个字符

longtext 长text 最多只能存储4294967235个字符

enum 枚举型 最多只能存储65535

set 集合 1-64

4、日期时间型：

data     3 bytes

time     3 bytes

datatime     8 bytes

timestamp    4 bytes

year       1 bytes

5、字符串类型的修饰手段：

not null 不允许为空

null 可以为空

default 指定默认值

binary 不区分字符大小

show characher sets; 显示当前字符型数据库字符集

show collation; 显示当前字符型数据库所支持的排序规则

show global varables like ‘%char%’;

select database(); 表示执行一个函数(一个内置的函数)

select last_insert_id();

auto_increment 自动增长(整型 非空 无符号)

eg: create table test(id int unsigned auto_increment not null primary key,Name char(20))

unsigned 无符号

cerate table test(id int unsigned

补充：

1、mysql还有一个特点就是支持名称补全

名称补全：打开数据库时必须将每一个数据库的名称和每一个表的名称每一个字段的名称都要载入内存当中来，这可能会导致我们在连接到mysql上时有大量的延迟，因为它必需要遍历每一个数据库的每一个表的定义，这是非常慢的。

–html ：可以让数据库显示为html格式

2、键和索引

1）键也被称为是约束，可用作索引，属于特殊索引（有特殊限定，比如数值不相同）：它们存储下来都是B+Tree的结构。

2）索引是关系数据库的内部实现技术，属于内模式的范畴 ，create index语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引

3）索引有两种类型：

b+tree：具有动态平衡的优点

hash：具有查找速度快的特点

几乎所有的字段都支持b+tree索引，但并非所有的字段都支持hash索引。

4）创建索引的原则：

（1）对较大的表才建立索引（约5000条记录以上），并检索的数据少于总行数的2%到4%

（2）一个表可建立任意多个索引，但不能太多，会增加系统维护的开销，索引建立后由系统维护。

（3）经常出现在where子句或联接条件中的列上作为索引关键字

（4）索引关键字可以是一个列，也可以是多个列组合成的复合索引。如果是复合索引，则查询条件中含有主关键字时，系统使用索引，加快查询速度。如果查询条件中只含有次关键字，则系统不使用索引。

（5）索引信息会存储到数据字典中。

一、SQL （Structured Query Language）相关的知识

1、SQL的概念：结构化查询语言，是关系数据库的标准语言，是一个通用的、功能极强的关系数据库语言。

2、几种常见的SQL模型：

ansi quotes 双引号相当于反引号只能用来引用字段名称、表名，单引号只能用来标识字符串

ignore_space 在内键函数中忽略多余的空白字符

strict_all_tables 只要违反了数据规则的都不允许填入并会返回一个错误的

strict_trans_tables 向一个支持事务的表当中插入非法数据的时候是不允许的

traditional

3、MySQL服务器变量的分类

1）按作用域，分为两类：

全局变量（对每一个局部都生效）

show global variables

会话变量

show [session] variables

2）按生效时间，分为两类：

动态：可即时修改,能及时生效

静态：写在配置文件中或通过参数传递给mysqld

4、动态调整参数的生效方式：

全局：对当前会话无效，只对新建立会话有效；

会话：即时生效，但只对当前会话有效；

服务器变量：@@变量名

显示：select

设定：set global|session 变量名=‘value’

eg：select @@global.sql_mode;

eg: select @@session.sql_mode;

eg: set global sql_mode=’strict_all_tables’;

5. 程序语言连接数据的方式：

动态SQL：通过函数或方法与数据库服务器建立连接，将查询语句直接发往服务器端

嵌入式SQL：通过API直接连到服务器上，连接之前还要先编译

二、数据库查询相关的知识

1、创建和删除数据库

创建数据库用法：create database|schema [if not exists] db_name [character set=] [collate=]

删除数据库用法：drop {database | schema} [if exista] db_name

例如：连接到数据库执行以下命令

eg:mysql

>select @@global.sql_mode; 选择数据库模块

> help create database; 创建数据库

>show character set; 查看中文字符集

>show collation; 查看排序规则

>create schema if not exists students character set ‘gbk’ collate ‘gbk_chinese_ci’; 创建一个数据库指定字符集的策略

2、表的创建有三种方式:

1)直接定义一张空表

格式：create table [if not exists] tb_name (col_name col_defination,constraint)

(col_defination最核心的是数据类型,constraint定义约束)

eg：create table tb1 (id int unsigned not null auto_increment,Name char(20) not null,Age tinyint not null)

eg:create table tb2 (id int unsignet not auto_increment,Name char(20) not null,Age tinyint not null,primary key(id),uniqce key(name),index(age)) unique把name定义成惟一键,把年龄定义为索引

2)从其它表中查询出数据，并以之创建新表

格式：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

3)以其它表为模板创建一个新表；

格式：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{ LIKE old_tbl_name | (LIKE old_tbl_name) }

3、表的选项：

engine [=] engin_name 指定表的存储引擎,若不指定直接从数据库中那里直接继承

auto_increment [=] value 自动增长从哪个值开始

avg_row_length [=] value 平均行的长度

[default] character set [=] charset_name 字符集

checksum [=] {0|1} 是否启用它的校验和

[default] collate [=] collation_name 排序规则

comment [=] ‘string’ 表的注释信息

data directory [=] ‘absolute path to directory’ 数据目录

delay key_write [=] {0|1} 是否延迟键写入

index directory [=] ‘absolute path to directory’ 索引目录

insert_method 插入方法

key_block_size [=] value 键块的大小

max_rows [=] value 最多允许存储多少行

min rows [=] value 最少允许存储多少行

pack_keys [=] {0|1|default}

password [=] ‘string’ 密码

row_format [=] {default|dynamic|fixed|compressed|redundant|compact} 行格式

tablespace tabespace_name [storage {disk|memory|default}] 表空间

union [=] (tbl_name[,tbl_name]…)

4）修改表定义：

alter table

添加、删除、修改字段

添加、删除、修改索引

改表名

修改表属性

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name 修改表指定表名

DROP [COLUMN] col_name 删除字段

DROP {INDEX|KEY} index_name 删除索引

RENAME [TO|AS] new_tbl_name 重命名表名

MAX_ROWS = rows 修改表的最大行

DISCARD TABLESPACE 是否禁用所有索引

IMPORT TABLESPACE 是否启用所有索引

5、常见表的查询

1）表的查询类型分为

a) 简单查询：select select-list from tb where qualification

b) 多表查询：

多表查询的连接类型：

<1> 交叉连接：笛卡尔乘积(指定表)

<2>自然连接：where tb1.field=tab2.filed （将两张表上相同字段的两个值逐一作比较，只将那些等值关系保留下来）

<3> 外连接：

左外连接（写在前面的表）from tb1 left join tb2 on condition

右外连接 from tb1 right join tb2 on condition

自连接 （自连接时必须需要别名）

select * from tb_name; 选择显示所有表中的所有行（*是通配符，表示对应表中的所有字段）

select filed1,filed2 from tb_name;投影 （只挑选有效的字段）

select [distinct] * from tb_name where qualification;选择

(distinct表示独有的，相同的值只显示一次)

select后面跟的是from(from子句：要查询的关系)， from后面跟的是表、多个表、其它select语句

c) 子查询（嵌套查询）：

比较操作符中进行子查询：子查询只能返回一个字段的单个值；

IN():列表(某字段的多个值)

在from中使用子查询；

2)where子句逻辑关系：

and 与

or 或

no 非

xor 异或

between … and …

3)常用的一些查询命令

like ”

%:任意长度的任意字符

_:任意单个字符

regexp ,rlike 作比较的时候支持正则表达式

eg:select name from student where name rlike ‘^[XY].*$’;

in 在…内

is null 判断是否为空

is not null 判断是否不空

ORDER BY field_name,…{ASC|DESC} 排序:asc升序，desc降序

给字段取别名：select Name AS Student_Name FROM students;(字段别名使用：as)

limit子句：limit [offset,]count limit表示后的offset,表示略过多少个，count表示取多少个

聚合计算：sum(), min(),max(), avg(),平均 count()个数之和

eg: select avg(age) from students; 平均年龄

group by :分组，主要目的做聚合计算

group by filed1,…

having 只能跟group by一起用，用来将group by的结果再次进行过滤

eg：select avg(age) gender from students group by Gender;

having qualification：过滤

三、事务和日志

1、事务的特性：

1）原子性[Automaticty]: 事务所引起的数据库操作，要么都完成，要么都不执行；

2）一致性[Consistency]: 从一个状态转换为另一个状态，这个状态是平稳转换的;

3）隔离性[Isolation]: 把事务隔离开，彼此间不干扰

隔离级别：

read uncommitted：读未提交

read committed：读提交

repatable read:可重读

seriablizable:可串行

4）持久性[Durability]:一旦失误成功完成，系统必须保证任何故障都不会引起事务表示出不一致性；

a) 事务提交之前就已经写出数据至持久性存储

b) 结合事务日志完成

事务日志：顺序IO

数据文件：随机IO

2、事务的状态：

活动的：active

部分提交的：最后一条语句执行后

失败的：没执行完成的

中止的：不执行的

提交的：已完成并提交（事务一旦提交就无法撤销）

补充：

事务并发执行的好处：提高吞吐量和资源利用率、减少等待时间。

事务调度的分类：可恢复调度、无级联调度。

并发控制依赖的技术手段：锁、时间戳、多版本和快照隔离

(锁：读锁（共享锁）、写锁（独占锁、排查锁）

锁粒度：从大到小，Mysql服务器只支持表级锁，行锁需要有存储引擎完成；）

3、日志类型有六类：(日志中记录的是操作过程，不是数据)

1）错误日志:主要用于记录服务运行当中的错误信息，服务器启动或关闭过程中显示的信息还可记录警告信息，从服务器上启动中继服务进程管理到的信息，还有事件调度器产生的信息，还可记录警告信息

2）一般查询日志：

（任何一个查询语句所产生的日志信息都要记录下来，一般查询日志通常是不开启的）

general_log

general_log_file

log

log_output

3）慢查询日志：查询日志的查询评估是根据它从开始启动这个查询一直到查询执行结束这个时间，而不是其真正在CPU上执行的时间，无论是一般查询日志还是慢查询日志它们都可以记录到文件中

log_output {table|file|none} 定义日志信息输出到什么位置(table和file可同时指定一般用逗号隔开)

long_query_time

log_slow_queries={YES|NO}

slow_query_log

slow_query_log_file

4）二进制日志：任何引起或可能引起数据库变化的操作；

复制、即时点恢复；

mysqlbinlog

重放

二进制日志的格式（事件）

基于语句：statement

基于行：row

混合方式：mixed

（二进制日志自己是会做滚动的，默认情况下存放至数据目录中，一般建议一定不要跟数据放在同一个存储设备上，因为每一次存储设备的IO操作[数据的写操作]都会引起二进制日志的写操作，两者都会产生IO，也就意味着在同一块磁盘上两者会产生竞争的，所以本身就会带来性能低下的状况，而且为了避免由于数据文件损坏也导致二进制日志损坏，所以就尤其的建议应该分开存储，基于性能和数据可靠性的原因）

二进制日志时间：

产生的时间

相对位置

二进制日志文件

索引文件

二进制日志文件

查看当前正在使用的二进制日志文件

show master status; 查看当前这段时间的日志

show binary logs; 查看当前系统上正在使用的二进制日志

show binlog events in ‘二进制文件名’ [from position]; 查看当前日志中的某个文件

purge binary logs to’日志文件’; 实现某个文件备份之后清除某个日志之前的日志

flush logs; 实现二进制日志的滚动

mysqlbinlog 显示详细查看输出二进制文件内容的日志

–start-position 指定起始位置

–stop-position 指定结束位置

–start-datetime ‘yyyy-mm-dd hh:mm:ss’ 以时间为上下限指定起始位置

–stop-datetime ”

可以将输出的信息一并的保存至文本文件中，导入到数据库里面可以实现恢复数据，因为它里面记录的都是具体的操作

5）中继日志：

从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件

6）事务日志：ACID，将随机IO转换为顺序IO；

事务性存储引擎用于保证原子性、一致性、隔离性和持久性；

（>show global variables like ‘%log%’;执行此命令可显示以下内容）

log_flush_log_at_trx_commit;

0：每秒同步，并执行磁盘flush操作；

1：每事务同步，并执行磁盘flush操作；

2：每事务同步，但不执行磁盘flush操作；

innodb_log_buffer_size：内存缓存大小

binlog_stmt_cache_size 二进制语句缓存的大小

sql_log_bin={on|off} 用于控制二进制日志信息是否记录进日志文件

innodb_log_group_home_dir 日志组放在当前目录

innodb_mirrored_log_groups 是否对文件组做镜像，若做的话要指定位置

expire_logs_days 日志的过期天数，若后面的数字是30也就意味着30天后里面的二进制日志将被清除

补充：mysqld产生的错误日志

1）服务器启动和关闭过程中的信息

2）服务器运行过程中的错误信息

3）时间调度器运行一个事件产生的错误信息

4）在从服务器上启动从服务器进程时产生的信息

四、MySQL的备份和还原

1、备份：将数据存放一个副本，提供一个副本在出现任何可能性故障的时候能够将数据还原回来的这么一个副本。（若把数据保存在RAID1、RAID10上也需要备份，因为RAID1、RAID10主要作用：保证硬件损坏而不会业务中止，一定程度上也保证数据的稳定性；）

备份用法：

select * from outfile ‘/path/to/somefile.txt’ from tb_name [where clause];

还原用法：

load data infile ‘/path/to/somefile.tct’ into table tb_name;

2、备份类型：

1） 热备份、温备份和冷备份（hot,warm,cold）

热备份：读、写不受影响；在线备份

温备份：仅可以执行读操作；

冷备份：离线备份；读、写操作均中止

2）物理备份和逻辑备份

物理备份：复制数据文件

逻辑备份：将数据导出至文本文件中；

3） 完全备份、增量备份和差异备份：

完全备份：备份全部数据 （full）

增量备份：仅备份上次完全备份或增量备份以后变化的数据；（incremental）

差异备份：仅备份上次完全备份以来变化的数据 （differential）

（在线：物理完全备份，它们之间完全可以自由的进行组合）

3、备份什么：数据、配置文件、二进制日志(做即时点还原)、事务日志(物理备份要备份事务日志)

4、MySQL备份工具：

1）mysqldump:逻辑备份工具、MyISAM(温备份)、InnoDB（热备份）

mysqlhotcopy：物理备份工具、温备份

MyISAM引擎：可以使用几乎热备，但要借助于逻辑卷进行备份，使用快照进行备份；最好的方式是温备份，以共享方式锁定MyISAM中的所有表。

InnoDB引擎：xtrabackup(可以实现热备，而且是物理备份),mysqldump(可以实现对InnoDB进行热备份)

MySQL –>从服务器：（把mysql在线服务做一个从服务器，需要备份的时候把从服务器停下来，备份好后再重新启动起来，它会自动从上一次提供服务器的那里停用的那一刻再同步）

2）备份的特点：

a)物理备份：速度快（可直接复制数据文件）

b)逻辑备份：速度慢、丢失浮点数精度；方便使用文本工具直接对其处理、可移植能力强；

1)浮点数据丢失精度

2)备份出的数据更占用存储空间；压缩后可大大节省空间

3)不适合对大数据库做完全备份

使用逻辑备份的前提：

1）数据文件要在逻辑卷上；

2）此逻辑卷所在卷组必须有足够空间使用快照卷

3）数据文件和事务日志要在同一个逻辑卷上

步骤：

1>打开会话，施加读锁，锁定所有表

mysql> flush tables with read lock;

mysql> flush logs;

2>通过另一个终端，保存二进制日志文件及相关位置信息

mysql -uroot -p -e ‘show master statusG’ > /path/to/master.info

3>创建快照卷

lvcreate -L # -s -p -r -n LV_NAME /path/to/source_lv

4>释放锁

mysql > unlock tables;

5>挂载快照卷，备份

mount

cp

6>删除快照卷

7>增量备份二进制日志

补充：

备份策略：完全+增量；完全+差异（完全备份是物理方式还是逻辑方式是取决你的需要，增量方式是以物理方式还是逻辑方式进行也取决你的需要）

完全备份：mysqldump

增量备份：备份二进制日志文件

5、文件系统工具（第二组工具）：

1)cp:冷备

2)lv:逻辑卷的快照功能，可以实现几乎热备；

mysql> flush tables;刷新表

mysql> lock tables;锁表

然后创建快照：释放锁，而后复制数据

（对MyISAM引擎来讲刷新表并锁表过程很简单，也没有什么难题；但对InnoDB引擎来说，刷新表锁表后，这并不意味着它背后没有写操作，因为有些事务可能仍然在日志当中，它从日志文件中正在同步到数据文件中，此外已经提交的数据可能仍在内存当中，从内存中往日志文件中提交，所以刷新表锁表后不能立即创建快照，还要监控着InnoDB的存储引擎确保它的缓存驱动中的内容都已经同步到磁盘上才行，这可能需要大量的表）

3)mysqldump：逻辑备份

mysqldump（完全备份）+ 二进制日志

完全+增量

mysqldump支持所有引擎，温备份，备份速度中等，还原速度最慢，还原时的控制能力是最具有弹性的。mysqldump对InnoDB引擎来讲，热备，备份速度中等，还原速度最慢，还原时的控制能力是最具有弹性的select intc outfile:只能够对应每张表中的某些个对应级,保存的时候对应某些文件级，但是它并不某些表结构。

4)mk-parallel-backup:这是一个perl脚本，依赖于很多perl模块进行备份，速度非常快，但服务器本身必须是多CPU或多核才有意义。

5)ibbackup:商业工具，因为它是物理备份，所以速度非常快，对MyISAM引擎温备，对InnoDB温备

6)mysqlhotcopy：MyISAM引擎，几乎是冷备，速度较快，还原也比较快

6、备份单个数据库，或库中的特定表

mysqldump DB_NAME [tb1] [tb2] 备份指定数据库

flush tables with read lock; 刷新所有表并执行读锁

lock tables;

show binary lock;

–master-data={0|1|2}

0: 不记录二进制日志文件及路位置；

1：以change master to 的方式记录位置，可用于恢复后直接启动从服务器

2: 以change master to 的方式记录位置，但默认为被注释；

–lock-all-tables:锁定所有表（备份之前锁定所有表）

–flush-logs:执行日志flush；（锁完表执行日志滚动）

如果指定库中的表类型均为InnoDB，可使用–single-transaction启动热备；

备份多个库：

–all-databases:备份所有库

–databases DB_NAME,DB_NAME,…:备份指定库

–events 备份事件调度器

–routines 备份存储过程和存储函数

–triggers 备份触发器

本文出自 “show_only” 博客，请务必保留此出处http://10240214.blog.51cto.com/6634068/1199002

转载请注明：爱开源 » 捕捉mysql中不可忽视的知识点