[TOC]
Q:什么是存储引擎? A:比如一个视频文件可以转成mp4,avi,wmv,而存储进磁盘中也会存在不同类型的文件系统中如linux中的ext3,ext4,xfs等.(直观区别就是占用系统的空间大小与清晰程度可能不一样);
数据库表的数据存储在数据库里以及磁盘上和上述的视频格式存储文件系统格式特征类似,也有很多存储方式;但是对于用户/程序来说无论用什么引擎来存储取到表的数据都是一致的不会产生变化。
MySQL常见的存储引擎是MyISAM和Innodb引擎,不同的引擎存取数据/引擎性能,占用的空间大小读取性能还是有所差别的。
存储引擎体系结构: MySQL各种存储引擎在MySQL里是通过插件的方式使用的,所有MySQL可以同时支持多种存储引擎。 主要由下面几部分构成: 客户端连接语言,连接池组件,管理服务和工具组件,SQL接口组件,查询分析器组件,优化器组件,缓冲(Cache)组件,插件式存储引擎,物理文件;
(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 引擎由什么特点?
MyISAM引擎适用的生产业务场景:
MyISAM引擎调优精要:
(2)InnoDB引擎介绍 InnoDB 引擎是MySQL数据库的另一个重要的存储引擎,是新版本的默认的数据库引擎,被包含在所有二进制安装包里; 存储引擎比如InnoDB,也支持不使用文件系统直接管理裸设备,开始逐步取代MyISAM引擎;
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引擎特点:
InnoDB引擎生产业务场景:
InnoDB引擎调优:
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三个存储引擎是目前互联网公司应用比较多的存储引擎;
WeiyiGeek.引擎一览对比图
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):
事务的开启实例:
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 | 小可爱 |
+------+----------------+
MYSQL数据库中的日志文件分类: (1) 错误日志(Error Log):记录MySQL服务进程mysqld在启动/关闭或者运行过程中遇到的错误信息; (2) 查询日志(Query Log):[支持全局动态修改]
(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模式。
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
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}'
WeiyiGeek.character
Q:有那些常见的字符集? utf-8兼容比较好,对于mysql来说UTF-8(每个汉字占三个字节);
WeiyiGeek.
描述:在MySQL有六处使用了字符集,分别为:client 、connection、database、results、server 、system
。
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了。
WeiyiGeek.set案例
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 服务器字符集
WeiyiGeek.配置文件
注意: 在修改了系统的/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; //注意建库/表时候的字符集 一致最好
WeiyiGeek.鉴别
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";
WeiyiGeek.导入数据库
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;
WeiyiGeek.Alter
注意:这个两个命令都没更新已有记录得字符集,而是只是对于新建立得表或者记录生效;
注意事项:
描述:对于已经记录得字符得调整,必须先将数据导出,经过修过字符集后重新导入后才可完成,修改数据库默认编码:
alter database [databaseName] charset [character setting]
> alter database user charset utf8;
> show create database user;
#之后建立得表也是采用utf8字符集
WeiyiGeek.数据库与表编码
下面模拟线上业务,数据库编码转换,将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
注意:选择目标字符集时,要注意最好大于等于源字符集(字库更大),否则可能会丢失不被支持的数据;最后成功的替换:
WeiyiGeek.导入数据
WeiyiGeek.完成修改
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更改|响应的字符集
WeiyiGeek.终端工具字符集