MySQL基础

一、MySQL体系结构：Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的；

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

 

2 Management Serveices & Utilities： 系统管理和控制工具

 

3 Connection Pool: 连接池。

管理缓冲用户连接，线程处理等需要缓存的需求

 

4 SQL Interface: SQL接口。

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

 

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

主要功能：

a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 

b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

 

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。

用一个例子就可以理解： select uid,name from user where gender = 1;

这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤

将这两个查询条件联接起来生成最终查询结果

 

7 Cache和Buffer： 查询缓存。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

 

8 Engine ：存储引擎。

存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）

现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB

默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。

InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 
Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

注：

基于MyISAM和InnoDB两种存储引擎的区别：(show engines  查看所支持所有存储引擎和默认引擎)

a. MyISAM文件结构：     

     .frm:  表结构

     .MYD：表数据

     .MYI：表索引

     

特点： 不支持事务

      表锁

      不支持外键

      B树索引、FULLTEXT索引、空间索引

b.InnoDB文件结构

     .opt:记录默认字符集和排序字符集信息

     .frm  :表结构

     .ibd:  表空间，表数据

特点：

事务

      B树索引，聚簇索引、自适应hash索引

      表空间，raw磁盘设备

      所有表共享一个表空间文件：

      建议：每个表使用一个独立文件 （通过设置变量innodb_file_per_table = 1可以实现）

show character set;  查看字符集

show collation;查看排序规则

二、配置文件

可以有三个配置文件存在，其读取顺序是：

/etc/my.cnf →/etc/mysql/my.cnf→$MYSQL_HOME/my.cnf→编译时指定的选项（--default-extra=/path/to/somefile)

并且可以把登录mysql的用户、密码、主机信息记录在 ~/.my.cnf 中，格式如下：

可以通过语句：

use mysql;select user,host,password from user;

查看当前所有用户和密码设置与否；（用户的形式：username@hostname）

修改用户密码的三种方式：

1.mysqladmin -u root password 'new_password' -p

2.set password for 'username'@'host'=password('new_pass');

3.update mysql.user set password=password('new_password') where user=username;flush privileges;

mysql 客户端使用：

mysql
     --user, -u
     --host, -h
     --password, -p
     --port
     --protocol    tcp | socket | pipe | memory    其中pipe|memory属于windows领域；
     --database DATABASE, -D

三、MySQL 管理的基础知识

1.锁的类型：

   读锁：共享锁，可以让多个用户读

  写锁（排他锁）：独占锁，只对进行写操作的用户使用

  可以通过语句

LOCK TABLES TB_NAME {READ|WRITE};   生成锁

UNLOCK TABLES； 解开锁

2.锁粒度：从大到小，MySQL服务器仅提供支持表级别的锁，行锁需要由存储引擎完成（MyISAM不支持行锁）；

      页锁：锁定一个数据块（data block）

      表锁     

      行锁

3.事务：

事务特性：

      ACID（原子性，事务所引起的操作，必须同时执行成功；

               一致性，数据的总和在事务前后必须相等；

               隔离性，

               持久性，）

commit   事务执行结束时，提交事务；

rollback   事务执行中，执行回滚操作；

事务执行过程：

start transaction;

SQL语句；

SQL语句；

commit 或者rollback

4.事务的隔离级别：

隔离级别：

     READ COMMITTED 读提交：当一个事务未提交时另一个事务读取的值为第一个事务操作前原先的值

     READ UNCOMMITTED 读未提交：当一个事务修改数据，并且未提交或者提交时。另一个事务读取的值为第一个事务修改的值；

     REPEATABLE READ 可重读：当一个事务对数据进行操作时，另一个事务读取到的值均为其事务启动那一刻的值；

     SERIALIZABLE 可串行：当一个事务对数据进行操作之后，另一个事务必须等待第一个事务提交之后才能读取值；

用一下语句可查看事务当前的隔离级别

select @@tx_isolation; show global variables like '%iso%';

更改级别：

set tx_isolation='read-committed'  更改为   读提交  的级别；

注：

如果没有明确启动事务：

     autocommit:能实现自动提交，每一个操作都直接提交；

     建议：明确使用事务，并且关闭自动提交

show session variables autocommit;

set autocommit=0;   关闭自动提交

savepoint  identify    保存点

rollback to identify    回滚到某个保存点  identify

四、用户管理

基于用户管理的系统表：

     user:用户帐号、全局权限

     db:库级别权限

     host:

     table_priv:表级别权限

     columns_priv:列级别权限

     procs_priv:存储过程和存储函数相关的权限

     proxies_priv:代理用户权限

用户帐号：

     用户名@主机

          用户名：16字符内

          主机：

               主机名：www.somename.com

               IP:192.168.108.123

               网络地址：192.168.108.0/255.255.255.0

               通配符：%

                    192.168.%.%

                    %.somename.com

     --skip-name-resolve   连接时 跳过域名解析

权限级别：

          全局级别

          库

          表

          列

          存储过程和存储函数

创建用户：

     create user username@host [identified by 'password'];

     %：可以用通配符%指定任意主机：create user username@'%' [identified by 'password'];

     show grants for username@host  查看用户的权限

     GRANT ALL PRIVILEGES ON [OBJECT.TYPE] （库名.表|存储过程|存储函数等）

     revoke priv_type on databasename.* from username@host;

     mysql> revoke select on catdb.* from cat@'%';

技巧：当忘记root用户密码是：

启动mysql_safe 时传递两个参数：

--skip-grant-tables     

--skip-networking

通过更新授权表方式直接修改其密码，而后移除此两个选项重启服务器。

五、日志管理

日志分类

可以通过：show global variables like '%log%'查看与日志相关的变量

错误日志

log_error

log_warning

一般查询日志

      general_log

      genral_log_file

      log  

      log_output  

慢查询日志

     mysql> show global variables like '%long%';

     | long_query_time                                   | 10.000000 |

     是否记录：

    slow_query_log                          | OFF                             |

    slow_query_log_file                     | /mysql/data/testoracle-slow.log |

二进制日志：DML,DDL,DCL 可能引起数据库改变的操作（默认路径在数据文件目录'mysql-bin*'；在启动文件my.cnf中变量指定：log-bin=/path/to/file）；

               应用场合：复制、即时点恢复；

     用mysqlbinlog  filename查看

二进制日志的格式：

     基于语句：statement

     基于行：row

     混合方式：mixed

查看当前使用的二进制文件：

     show master status

查看事件

     show binlog events in 'binlog_name' [from pos];

 查看当前系统所有二进制文件

     show binary logs;

删除二进制文件：

     purge binary logs to 'some_binary_logsfile'   删除此文件之前的二进制日志文件

show binary logs;

show master status;

show binary 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=

 

binlog_stmt_cache_size   二进制文件记录的语句（statement）的缓存大小

innodb_flush_log_at_trx_commit   0  没一秒钟同步一次；1  每事务提交 、每磁盘刷写同步一次；2  在每次调教的时候同步一次

中继日志（格式与二进制日志相同）：当前slave服务器用于复制的日志

事务日志：ACID的重要组件和必要条件；将随机IO转换为顺序IO；

记：初学，有点肤浅；

记录一下