mysql相关学习1

1、安装之后的一点配置（centos7+mysql5.6）

        修改字符集（默认情况长这样子，里面有latin字符集中文会乱码，需要更换）：

show variables like 'collation_%';    //这是查看语句
show variables like 'character\_set\_%';   //也是查看语句
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

在etc下找到my.cnf，找不到的话去/usr/share/mysql下面把类似my-default.cnf的文件拷贝一份到etc目录下，进入my.cnf文件，在[client]和[mysqld]下分别增加default-character-set=utf8和character-set-server=utf8的配置，如果没有[client]那就自己加一个。

2、Mysql中日志文件和配置文件以及数据文件的位置

show variables like 'log_%';

+----------------------------------------+---------------------+
| Variable_name                          | Value               |
+----------------------------------------+---------------------+
| log_bin                                | OFF                 |
| log_bin_basename                       |                     |
| log_bin_index                          |                     |
| log_bin_trust_function_creators        | OFF                 |
| log_bin_use_v1_row_events              | OFF                 |
| log_error                              | /var/log/mysqld.log |
| log_output                             | FILE                |
| log_queries_not_using_indexes          | OFF                 |
| log_slave_updates                      | OFF                 |
| log_slow_admin_statements              | OFF                 |
| log_slow_slave_statements              | OFF                 |
| log_throttle_queries_not_using_indexes | 0                   |
| log_warnings                           | 1                   |
+----------------------------------------+---------------------+

可以看是否启用日志

log_bin                    # 二进制日志用于主从复制 
log_bin_basename           # 二进制日志文件名，不指定log_bin_basename,则binlog使用主机名命名binlog日志
log_bin_index              # 二进制的索引文件名
log_bin_trust_function_creators  #
log_bin_use_v1_row_events        #
log_error                        # 错误日志
log_output                       # FILE 表示将日志存入文件  TABLE表示将日志存入数据库
log_queries_not_using_indexes          | OFF                 |
log_slave_updates                     
log_slow_admin_statements              
log_slow_slave_statements              
log_throttle_queries_not_using_indexes 
log_warnings                          

        2.1、binlog

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。如果update操作没有造成数据变化，也是会记入binlog

binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

这个二进制日志包括两类文件：

• 索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
• 日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

举个例子：

在my.cnf的 [mysqld]下面加入以下配置
server-id = 11
log-bin = /var/mysqllog/123log   #这是日志文件的位置，需要mysql用户有访问权限，一般情况下是没有的
chown mysql -R /var/mysqllog/123log  #改变文件夹的属主这样就可以使得mysql有访问权限

这个地方，我尝试的时候经常启动不起来mysqld服务，查看日志，一般是/var/log/mysqld.log下可以看到(Errcode: 13 - Permission denied)这样的错误，所以需要改变你要使用的文件夹的权限，因为mysqld启动使用的是系统的mysql用户，所以你需要给mysql用户权限。

 可以看到这个地方已经有两个文件生成了，其中拓展名为index的文件，就是当前正在使用的二进制日志文件的记录文件，

 查看其内容，正是binlog日志文件的名字（带拓展名序号）。

+----------------------------------------+----------------------------+
| Variable_name                          | Value                      |
+----------------------------------------+----------------------------+
| log_bin                                | ON                         |
| log_bin_basename                       | /var/mysqllog/123log       |
| log_bin_index                          | /var/mysqllog/123log.index |

再此查询日志信息，可以看出前三个变量已经变化。

当然你也可以在my.cnf中加入以下配置

log-bin-index = /var/mysqllog/456log   #自定义那个index文件的位置和名字
#log-bin-basename  没有这个变量名啊

        在innodb里其实又可以分为两部分，一部分在缓存中，一部分在磁盘上。这里业内有一个词叫做刷盘，就是指将缓存中的日志刷到磁盘上。跟刷盘有关的参数有两个个:sync_binlog和binlog_cache_size。这两个参数作用如下：binlog_cache_size: 二进制日志缓存部分的大小，默认值32k  sync_binlog=[N]: 表示写缓冲多少次，刷一次盘,默认值为0，binlog_cache_size设过大，会造成内存浪费。binlog_cache_size设置过小，会频繁将缓冲日志写入临时文件。sync_binlog=0:表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘，这个性能最好。sync_binlog=1，代表每次事务提交时就会刷新binlog到磁盘，其他的N表示N次事务提交刷一次盘。

        这里存在一个一致性问题，sync_binlog=N，数据库在操作系统宕机的时候，可能数据并没有同步到磁盘，于是再次重启数据库，会带来数据丢失问题。

        当sync_binlog=1，事务在Commit的时候，数据写入binlog，但是还没写入事务日志(redo log和undo log，有关这俩日志，后面会讲)。此时宕机，重启数据库，数据被回滚。但是binlog里已经记录，这里存在不一致问题。这个事务日志和binlog一致性的问题，大家可以查询mysql的内部XA协议，该协议就是解决这个一致性问题的。

binlog不是InnoDb独有的，大家应该知道Innodb和mysiam最显著的区别就是一个支持事务，一个不支持事务你可以说，binlog是基于事务来记录二进制日志，比如sync_binlog=1,每提交一次事务，就写入binlog。你却不能说binlog是事务日志，binlog不仅记录Innodb日志，在Myisam中，也一样存在binlog。

这里先简单提一下主从复制：主库（Master）有一个log dump线程，将binlog传给从库，从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log，SQL线程从relay log里面读取内容，写入从库的数据库。

使用reset master,该命令将会删除所有日志，并让日志文件重新从000001开始。

binlog的常见格式：

        目前推荐使用的是row模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的

常见参数：

参考原文：什么是 binlog？_日志

        2.2、Mysql查询日志--general_log

show variables like '%general_log%';

+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+

使用上面的查询sql，可以查到 查询日志 相关的变量信息，可以看到general_log默认是关闭的，

MySQL的查询日志支持写入文件或写入数据表两种形式，这个由参数log_output控制，log_output这个参数在上面提过，默认是写入文件。如果设置log_output=table的话，则日志结果会记录到名为gengera_log的表中，这表的默认引擎是CSV。MySQL的查询日志记录了所有MySQL数据库请求的信息。无论这些请求是否得到了正确的执行。这个就是即使我查询一个不存在的表的SQL，查询日志依然会记录。

参考：MySQL查询日志总结 - Ray(Mr.huang) - 博客园

        2.3、Undo Log

        2.4、Redo Log

        2.5、Mysql的数据文件

在windows系统中，找到安装目录的data下即可查看。

在Linux下默认是  /var/lib/mysql目录下

在存储引擎为Myisam的情况下会有以下文件类型

frm类型文件	存放表结构
myd文件文件	存放表数据
myi类型文件	存放表索引

在存储引擎为InnoDB的情况下会有以下文件类型

frm类型	存放表结构
ibd	存放表数据和索引

有关mysql索引的知识，后面聊

        2.6、Mysql的配置文件，win下是my.ini文件，Linux下是/etc/my.cnf文件

3、Mysql的逻辑架构

由图可以发现，mysql主要由以下部分组成：

  1. 连接池组件
  2. 管理服务 和 工具组件
  3. SQL接口组件
  4. 查询分析器组件
  5. 优化组件
  6. 缓冲组件
  7. 插件式存储引擎
  8. 物理文件

  Connectors

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

  Connection Pool

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

  Management Serveices & Utilities （备份、安全、复制、集群）

        系统管理和控制工具。

  SQL Interface（存储过程、视图、触发器）

        接受用户的SQL命令，并且返回用户需要查询的结果。

  Parser

        SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给Parser，Parser专门负责将各种 Query 进行分类，然后转发给各个对应的处理模块。

        a 、 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的； 
　　b、 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

 Optimizer

查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。 
　　使用的是“选取-投影-联接”策略进行查询
 

Cache和Buffer

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

存储引擎接口

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

        3.1、一个完整的执行流程

1.连接（建立TCP连接）----->  2.授权认证：（在真正的操作之前，还需要调用用户模块进行授权检查，来验证用户是否有权限）------> 3. 接收SQL语句 ------> 4.将语句交给SQL语句解析模块进行语法分析和语义分析------>5.0如果是一个查询语句，则可以先看查询缓存中是否有结果------>6.如果查询缓存中没有结果，就需要真的查询数据库引擎层了，于是发给SQL优化器 ------>

5.1 如果是表变更，则分别交给insert、update、delete、create、alter处理模块进行处理---->...

7.存储引擎打开表，如果需要的话获取相应的锁 ---->  8.先查询缓存页中有没有相应的数据，如果有则可以直接返回，如果没有就要从磁盘上去读取。----->9.当在磁盘中找到相应的数据之后，则会加载到缓存中来，从而使得后面的查询更加高效，由于内存有限，多采用变通的LRU（最近最少使用算法)表来管理缓存页，保证缓存的都是经常访问的数据。 ------> 10.最后，获取数据后返回给客户端，关闭连接，释放连接线程

一条SQL的执行流程：

4、Mysql的存储引擎

        MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎，存储引擎各有特点，如内存存储引擎处理数据快、Innodb存储引擎支持事务。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

使用show engines可以查看所有存储引擎，support是当前mysql可以支持的。可以看出mysql5.6中默认的存储引擎是Innodb。
mysql 默认引擎innodb 初探（一）_走一步再走一步-CSDN博客

可以使用

show variables like '%storage_engine%'

 查看正在使用的存储引擎

主流的存储引擎Myisam和InnoDB的对比

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