- 0x01 常用引擎及优缺点
- 0x02 服务日志介绍
- 0x03 数据库字符集介绍及乱码
- (1) 数据库字符集介绍
- (2) 数据库字符集问题
- (3) 数据库线上业务字符集转换实战
- (n) 总结
0x01 常用引擎及优缺点
Q:什么是存储引擎？
A：比如一个视频文件可以转成mp4,avi,wmv,而存储进磁盘中也会存在不同类型的文件系统中如linux中的ext3,ext4,xfs等.(直观区别就是占用系统的空间大小与清晰程度可能不一样);
数据库表的数据存储在数据库里以及磁盘上和上述的视频格式存储文件系统格式特征类似,也有很多存储方式;但是对于用户/程序来说无论用什么引擎来存储取到表的数据都是一致的不会产生变化。
MySQL常见的存储引擎是MyISAM和Innodb引擎,不同的引擎存取数据/引擎性能,占用的空间大小读取性能还是有所差别的。
存储引擎体系结构：
MySQL各种存储引擎在MySQL里是通过插件的方式使用的,所有MySQL可以同时支持多种存储引擎。
主要由下面几部分构成：
客户端连接语言,连接池组件,管理服务和工具组件,SQL接口组件,查询分析器组件,优化器组件,缓冲（Cache）组件,插件式存储引擎,物理文件;
- Connectors : 通过SDK来访问MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。
- Connection Management : 每一个基于TCP的网络服务都需要管理客户端链接,为每一个连接绑定一个线程所有查询都在这个线程中执行;
- Connection Pool : 避免频繁创建和销毁线程带来开销,通常会缓存线程或者使用线程池，从而避免频繁的创建和销毁线程;客户端连接到MySQL需要进行认证,用了SSL或者TLS的方式进行连接，还会进行证书认证。
- SQL Interface : MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口。
- Parser : 解析SQL查询，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限.
- Optimizer : 语法解析和查询重写之后，MySQL会根据语法树和数据的统计信息对SQL进行优化，包括决定表的读取顺序、选择合适的索引等，最终生成SQL的具体执行步骤.
- Caches & Buffers : 比如Query Cache用来缓存一条Select语句的执行结果，如果能够在缓存中找到结果，就不必再进行查询解析、优化和执行的整个过程了。
- Pluggable Storage Engine ：存储引擎都实现了MySQl定义好的存储引擎API的部分或者全部。MySQL可以动态安装或移除存储引擎，可以有多种存储引擎同时存在，可以为每个Table设置不同的存储引擎。存储引擎负责在文件系统之上，管理表的数据、索引的实际内容，同时也会管理运行时的Cache、Buffer、事务、Log等数据和功能。
- File System ： 数据库、表的定义/表的每一行的内容/索引，都是存在文件系统上，以文件的方式存在的;
（1）MyISAM引擎介绍
Q：什么是MyISAM引擎？
答：是mySQL关系数据库管理系统的默认存储引擎（MYSQL 5.5.5 以前）, MySQL表存储结构从旧的ISAM代码扩展出许多有用的功能（解决内存空间小/存储进文件系统中）;
MySQL 5.7.24 默认支持的(innodb)存储引擎如下：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| 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 (用户SEESION表
) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> show create table mysql.user\G; --#系统表默认都是采用的MyISAM引擎
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
每一个MyISAM表都在硬盘上对应三个文件：
- frm 文件保存表的定义（不是MyISAM一部分,而是服务器的一部分）。
- MYD 保存表的数据。
- MYI 表压缩数据的索引。
[root@WeiyiGeek mysql]$ pwd && ls user.*
/data/3306/data/mysql
user.frm user.MYD user.MYI
[root@WeiyiGeek mysql]$ file user.frm
user.frm: MySQL table definition file Version 9
[root@WeiyiGeek mysql]$ file user.MYD
user.MYD: DBase 3 data file (167515135 records)
[root@WeiyiGeek mysql]$ file user.MYI
user.MYI: MySQL MISAM compressed data file Version 1
Q:MyISAM 引擎由什么特点？
- 不支持事务
- 表级锁定:数据库更新时锁整个表,锁定机制是表级索引,让锁定的实现成本很小但是也同时大大降低了并发性能;
- 读写相互阻塞
- 只会缓存索引:通过key_buffer_size 缓存索引,以提高风闻性能减少磁盘IO;但是只会缓存索引而不会缓存数据。
- 读取速度较快,占用资源相对少
- 不支持外键(两个表中有相同的字段)约束,单支持全文索引
MyISAM引擎适用的生产业务场景：
- 不需要事务支持的业务,对数据一致性要求不是非常高的业务（如电商转账/充值等）
- 读数据比较多的应用,或者单一操作(即：尽量纯读或者纯写等),不适合读写都频繁的场景
- 读写并发访问较低的业务（锁定机制问题）,数据修改相对较少的业务（阻塞问题）
- 硬件资比较差的机器可以用MyISAM;
MyISAM引擎调优精要：
- 设置合适的索引以及缓存buffer大小。
- 调整读写优先级,根据实际需求确保重要操作更优先执行。
- 启用延迟插入改善大批量写入性能(降低写入频率,尽可能多条数据一次性写入)
- 尽量顺序操作让insert数据都写入到尾部(减少阻塞)
- 分解大的时间长的操作,降低单个操作的阻塞时间。
- 降低并发数(减少对MySQL访问),某些高并发场景通过应用进行排队队列机制Q队列.
- 对于相对静态(更改不频繁)的数据库数据,充分利用Query Cache 或memecached 缓存服务可以极大的提高访问效率;
- MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count而进行实际的数据访问。
- 把主从同步的主库使用innodb,从库使用MyISAM引擎（不推荐）。
（2）InnoDB引擎介绍
InnoDB 引擎是MySQL数据库的另一个重要的存储引擎,是新版本的默认的数据库引擎,被包含在所有二进制安装包里;
存储引擎比如InnoDB,也支持不使用文件系统直接管理裸设备,开始逐步取代MyISAM引擎;
InnoDB 引擎的优点:
- 支持兼容ACID的事务(类似于PostgreSQL)
- 参数完整性(即对外键的支持),数据的一致性更好,对事务参照完整性
- 采用innobase采用双授权,它使用GNU发行,也允许其他想将InnoDB集合到商业团体获得授权;
- 以及更高的并发性
[root@WeiyiGeek data]$ pwd
/data/3306/data
-rw-r----- 1 mysql mysql 12582912 Apr 3 13:52 ibdata1 #InnoDB 存储的物理数据文件
[root@WeiyiGeek study]$ ls
db.opt student.frm student.ibd #独立表空间对于的物理数据文件
/data/3306/data/study
InnoDB引擎特点：
- 支持事务(Support Transcations),支持4个事务隔离级别,支持多版本读;
- 行级锁定(更新时一般是锁定当前行),通过索引实现,全表扫描任然会是表锁,注意间隙锁的影响;
- 读写阻塞与事务隔离级别相关;
- 具有非常高效的缓存特性,能缓存索引/数据;
- 整个表和主键以Cluster方式存储,组成一颗平衡数;
- 支持分区表空间,类似于Oracle数据库;
- 所有的Secondary Index都会保存主键信息;
- 支持外键(foreign Keys)约束,5.5以前不支持全文索引,后面的版本支持了;
- 对于服务器硬件资源要求比较高,相对于MyISAM来说
- 相比MyISAM引擎InnoDB更消耗资源,速度没有MyISAM引擎快;
InnoDB引擎生产业务场景：
- 需要事务支持的业务,数据一致性要求比较高的业务;如(充值转账,银行卡转账)
- 行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成的.
- 数据读写以及更新都较为频繁的场景,如(BBS,SNS,微博,微信);
- 硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘IO;
InnoDB引擎调优：
- 主键尽可能小,避免给Secondary Index带来过大的空间负担;
- 避免全表扫描,因为会使用表锁
- 尽可能缓存所有的索引和数据,提高响应速度减少磁盘IO消耗;
- 在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交;(后端代码处理)
- 当事务提交,合理设置 innodb_flush_log_at_trx_commit = 0/1/2 参照值不要过度最求安全性;
- 避免主键更新,因为这会带来大量的数据移动;
MySQL在做事务的时候使用的日志先行的方式保证事务的快速和持久：
innodb_flush_log_at_trx_commit = 0; #性能最好,安全性也最差;系统岩机丢失1s数据;(每一个事务提交时候,每隔一秒把事务日志缓存区的数据写到日志文件,以及把日志文件的数据刷新到磁盘中)
innodb_flush_log_at_trx_commit = 1; #(每个事务提交),把事务日志从缓冲区写到日志文件中,并且刷新日志文件的数据到磁盘上;
innodb_flush_log_at_trx_commit = 2; #(把事务日志数据从缓存区写到日志文件中,每隔一秒刷新一次日志文件,但不会刷新到磁盘中)
（3）引擎种类介绍
在MyISAM,InnoDB和NDBCluster三个存储引擎是目前互联网公司应用比较多的存储引擎;
MYSQL引擎修改：
#1）创建后表的引擎的更改
ALTER TABLE oldboy ENGINE = INNODB
ALTER TABLE oldboy ENGINE = MyISAM
#2）使用sed对备份内容进行引擎转换
nohup sed -e 's/MyISAM/InnoDB/g' old2boy.sql > oldboy.sql &
#3）mysql_convert_table_format 命令修改 在安装的mysql中目录之中;
`which mysql_convert_table_format` --help
mysql_convert_table_format --user=root --password=oldboy123 --socket=/data/3306/mysql.sock --engine=MyISAM oldboy table
（附录）：事务介绍
简单地说,事务就是指逻辑上的一组SQL语句操作,组成一个操作的各个SQL语句执行时要么全成功要么全失败,参照PHP代码事务处理,主要解决是转账一致性问题;MySQL5.5支持事务的引擎（innodb/ndb-集群）
事务的四大特性（ACID）:
- 原子性(Atomicity):事务是一个不可分割的单位,所有的SQL等操作要么全发生/要么都不发生;
- 一致性(Consistency):事务发生前后数据的完整性必须保持一致。
- 隔离性(Isolation)：当并发访问数据库时候，一个正在执行事务在执行完毕前,对于其他的会话是不可见的,多个并发事务之间的数据库是相互隔离的比如：innodb引擎在导出数据时候参数 --single-transaction
- 持久性(Durability)：一旦事务提交,它对数据库中的数据改变是永久性的,如果出现错误，事务也不允许撤消只能通过”补偿性事务“;
事务的开启实例：
begin # 或者 autocommit = 0 开启事务 / 禁止事务提交
rollback #回滚事务
commit #提交事务
mysql> begin; #只是在会话中生效
Query OK, 0 rows affected (0.00 sec)
#或者采用
mysql> set autocommit = OFF;
mysql> insert into student values(44,'小可爱');
mysql> select * from study.student; #退出查看发现无该条记录
mysql> insert into study.student values(444,'小可爱');
mysql> rollback; #插入错误的数据进行事务回滚
mysql> select * from study.student; #查询无该条记录
mysql> insert into study.student values(1024,'小可爱');
mysql> commit; #事务提交
mysql> select * from study.student;
+------+----------------+
| id | name |
+------+----------------+
| 1024 | 小可爱 |
+------+----------------+
0x02 服务日志介绍
MYSQL数据库中的日志文件分类：
(1) 错误日志(Error Log):记录MySQL服务进程mysqld在启动/关闭或者运行过程中遇到的错误信息;
(2) 查询日志(Query Log):[支持全局动态修改]
- General Query Log：记录客户端连接信息和执行的SQL语句信息;
- Slow Query Log：慢查询日志记录执行时间超出指定(Long_query_time)的SQL语句;
(3) 二进制日志(Binary Log)：binlog记录数据被修改的相关信息;
三种日志配置：
#(1) 错误日志配置
[mysqld]
log-error = /data/mysql-error.log #错误日志设置
#(2) 查询日志
mysql> set global general_log = ON; #通用查询日志记录
###慢查询###
long_query_time = 1
log-slow-queries = /data/3306/slow.log
log_queries_not_using_indexes = OFF #日志查询不使用索引 OFF/ON
####配置成功查询####
mysql> show variables like 'log_error%';
+---------------------+----------------------------+
| Variable_name | Value |
+---------------------+----------------------------+
| log_error | /data/3306/mysql-error.log |
| log_error_verbosity | 3 |
+---------------------+----------------------------+
mysql> show variables like 'general_log%';
+------------------+-------------------------------+
| Variable_name | Value |
+------------------+-------------------------------+
| general_log | ON |
| general_log_file | /data/3306/data/WeiyiGeek.log |
+------------------+-------------------------------+
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------+
| log_bin | ON | #开启bin-log日志
| log_bin_basename | /data/3306/binlog |
| log_bin_index | /data/3306/binlog.index |
| sql_log_bin | ON | #临时不记录binlog
+---------------------------------+-------------------------+
BIN-log日志的三种日志格式
Mysql至5.1版本以后出现了三种日志格式:分别是Statement Level模型,Row Level模型,Mixed模式。
- Statememt Level 模式：
顾名思义，STATEMENT 格式的 Binlog 记录的是数据库上执行的原生SQL语句;每修改一条的sql语句会被记录到master的binlog中,slave在复制的时候sql进程解析成和原来master端执行过相同的sql再次执行。
优点：解决row level的缺点,不需要记录每一行数据的变化,减少bin-log日志量,节约IO提高性能(只需要记录master执行的语句的细节,以及执行语句时候的上下文的信息)。
缺点：不能全部适应mysql新函数/功能的加入,可能导致Mysql的复制出现问题,记录的每条执行语句还有上下文信息。 - Row Level 模式：
日志中会记录成每一行数据被修改的形式,然后再slave端再对进行相同的数据进行修改。
优点：再该模型下,bin-log中可以不记录执行的sql语句,仅仅只需要记录那一条记录被修改了,记录下每一行数据修改的细节。
缺点：会产生大量的日志内容,再执行alter table语句的时候是直接重建整个表。 - Mixed 模式：
实际上是前两种模式的结合,在Mixed模型下MySQL会根据执行每一条具体的sql语句来区别对待日志形式，也就是在statement和Row之间选择一种;Statement level模型处理的方式还是和以前一样仅仅记录执行的语句;在新版本中对Row Level 进行了优化并不是所有的修改都会以row Level来记录,像遇到表结构变更的时候就会以statement模型来记录。
BINlog日志格式切换：
#配置文件参数如下
log-bin = mysql-bin
binlog_format = "STATEMENT" # STATEMENT / ROW / MIXED
set SEESION binlog_format = 'ROW' # 运行时在线修改
set GLOBAL binlog_format = 'ROW' # STATEMENT 全局生效 (退出重新登陆生效)
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
#行级模式binlog日志输出
mysqlbinlog --base64-output="decode-rows" -v xxxx.00001
0x03 数据库字符集介绍及乱码
(1) 数据库字符集介绍
Q:什么是字符集？
A:是一套文字符号及其编码，比较规则得集合，第一个计算机字符集是ASC2；
Q:MySQL数据库字符集的包括？
A:字符集（Character）和校对规则（Collation）两个概念；
- Character：定义Mysql数据字符串的存储方式；
- Collation：定义比较字符串的方式；
比如在建表的语句中：
数据库字符集：CHARACTER SET latin1
数据库校对规则：COLLATION latin1_swedish_ci
mysql> show create table user\G;
查看校对规则：
# mysql -uroot -p123456 -e "show character set" | egrep "gbk|utf8|latin1|utf8mb4|binary" | awk '{print $0}'
Q:有那些常见的字符集？
utf-8兼容比较好，对于mysql来说UTF-8（每个汉字占三个字节）；
(2) 数据库字符集问题
描述:在MySQL有六处使用了字符集，分别为：client 、connection、database、results、server 、system。
- client:是客户端使用的字符集。
- connection:是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
- database:是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
- results:是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
- server:是服务器安装时指定的默认字符集设定。
- system:是数据库系统使用的字符集设定。(MySQL 8.0 默认是utf8mb4,而MySQL 5.7 默认是utf8,而5.6以下是latins)
0）在mysq服务搭建编译时或者创建数据库时候指定字符集
#/** 数据库要支持创建的字符集 , 在编译的时候必须指定 **/
-DEXTRA_CHARSETS=gbk,gb2312,utf-8,ascii;
#或者
-DDEFAULT_CHARSET=utf8\
-DDEAFULT-COLLATION=utf8_general_ci\
#客户端连接MySQL时候指定
mysql -h 127.0.0.1 -u root -p --default-character-set=gbk
#demmo , 之后创建的库表默认都是utf8字符集的
create database user;
create table user(`id` int(30), `name` varchar(30) NOT NULL);
show create table user; //注意建库/表时候的字符集 一致最好
1）采用 set names 关键字指定字符集：（临时生效）
set names latins; --设置字符集为插入数据的字符集，然后再插入中文 （临时得）
insert into student value (1, "长三"),(2,"李伟");
-- #到MySQL命令行输入“SET NAMES utf8;”，然后执行“show variebles like“character_set_%”;”，发现原来为latin1的那些变量“character_set_client”、“character_set_connection”、“character_set_results”的值全部变为utf8了。
2）修改my.cnf(永久生效)或者采用set character_set_%进行设置字符集
#Windows下可通过修改my.ini内的，Linux则修改my.cnf
---------------------
[mysql] 客户端配置
default-character-set=utf8 //客户端的默认字符集
[mysqld] 服务器端配置 //服务端 - 改动的是character_set_database 和 server 两处
default-character-set=utf8 //服务器端默认的字符集 适合5.1及以前版本
default-set-server=latin1 //适合5.5
character-set-server = gbk // 5.7 以上
collation-server = gbk_bin
#注意版本不同my.cnf 可能是mysql或者client
[client] //最大的好处是不用重启数据库服务
default-character-set=utf8 //客户端的默认字符集
-------------------------------------------------------------------
信息输入路径：client→connection→server
信息输出路径：server→connection→results
#查看当前字符设置情况-临时生效
show variables like "character_set_%"
set character_set_client=utf8;
--------------------------------------------------------------------
character_set_client 客户端字符集
character_set_connection 连接字符集
character_set_database 数据库字符集在建库建表的时候设置的
character_set_result 返回结果字符集
character_set_server 服务器字符集
注意: 在修改了系统的/etc/sysconfig/i18n的字符集变量时候，会影响到client，connection，result的值；
3）建库-建表时候指定支持中文的字符集
#建立数据库地时候
> show character set; #查看校对得字符集
mysql> create database test DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #设置了GBK字符集
mysql> create database test DEFAULT CHARACTER SET gbk COLLATE utf8_general_ci; #设置了UTF-8字符集
#建立表得时候 (自动递增数量10)
use test;
create table tb (`id` INT(4) NOT NULL, `name` VARCHAR(255) NOT NULL default 'empty') ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=UTF8;
show create table td; //注意建库/表时候的字符集 一致最好
**4）在登录导入数据库时候设置字符集；（临时生效）**
/** tb.sql insert into tb value (7,"爱您"),(8,"一万年"),(9,"不变"); **/
mysql -usystem -p123456 --default-character-set=utf8 test < tb.sql
//#不登录mysql数据库执行SQL命令 -e
mysql -usystem -p123456 -e "select * from test.tb";
5 将set names写入到sql文件之中
利用source进行导入，也能不乱码；
>tb.sql
#set names latin1; //在里面查询得时候utf8的字符集将变码 | 当当前字符集为utf8时候latin1将乱码 （所以建议库表字符集一致）
>source tb.sql
6）对于新建立库/表更改数据库与表得字符集
alter database character set [字符集]
alter table tablename character set [字符集]
> alter table user1 character set utf8;
> show create table user1;
注意：这个两个命令都没更新已有记录得字符集，而是只是对于新建立得表或者记录生效；
注意事项:
(3) 数据库线上业务字符集转换实战
描述：对于已经记录得字符得调整，必须先将数据导出，经过修过字符集后重新导入后才可完成，修改数据库默认编码：
alter database [databaseName] charset [character setting]
> alter database user charset utf8;
> show create database user;
#之后建立得表也是采用utf8字符集
下面模拟线上业务，数据库编码转换，将latin1-->>>utf8字符集
Step1.导出表结构
mysqldump -uroot -p --default-character-set=latin1 -d [DatabaseName] >alltable.sql
#--default-character-set=gbk 表示以GBK字符集进行连接
#-d 只导表结构
Step2.确保数据库不再更新，导出所有数据
mysqldump -usystem -p --quick --no-create-info --extended-insert #--default-character-set=latin1 test>alldata.sql
#--quick : 用于转储大的表,强制mysql从服务器一次一行的检索而不是检索所有行，并输出前CACHE到内存中；
#--no-create-info : 不创建CREATE TABLE 语句;
#--extended-insert : 使用包括几个VALUES列表的多行INSERT语法，这样文件更小,IO也小导入数据时会非常快
#--default_character-set = latin1 : 按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码；
Step3.修改导出的.sql文件
编辑 alltable.sql 将 latin1 改成 utf8；
打开 alldata.sql 将 set names latin1 修改成 set names utf8；
Step4.建库/建表
create database [dbname] default charset utf8;
Step5.导入数据
mysql -uroot -p dbnames < alltable.sql
mysql -uroot -p dbnames < alltable.sql
注意：选择目标字符集时，要注意最好大于等于源字符集（字库更大），否则可能会丢失不被支持的数据；最后成功的替换：
(n) 总结
1.执行DQL、DML语句之前设置 set names 系统及库表的字符集，建议中英文使用utf8；
2.通一字符集将不会出现乱码，Linux系统字符集 - 客户端 - 服务端 - 库 - 表 -程序；（非常注意建库建表的字符集）
3.在查询的时候如果想看不是uft8字符集插入的数据而是latin1的话就必须set names latin1才能查看正常
4.连接服务器得终端工具得字符集有时也需要进行设置;
LINUX > system cat /etc/sysconfig/i18n
客户端 > 设置UTF8 - set names utf8 （建议中英文选择utf8）
服务端 > my.cnf中mysqld更改|响应的字符集
