mysql replicatedodb,MySQL技术内幕：InnoDB存储引擎---笔记

一，mysql 进程

1.查看进程命令  命令  ps -ef  | grep mysqld

进程位 5448

2.查看mysql 配置文件  mysql --help  | grep my.cnf

3.数据存放路径    show variables like 'datadir'\G;

4.innodb 行锁设计 ，支持外健

5.MYISAM储存引擎  不支持事务，表锁设计

6. 查看所有引擎：    show engines\G;

7.查看数据库版本；select version()\G;

二，线程

1.多线程

innodb 储存引擎是基于硬盘储存的并将其中的记录方式用页的方式进行管理。

三，缓存池中的数据叶类型

1.索引页

2.数据页

3.undo页

4.插入缓存 (insert buffer)

5.自适应哈系索引 (adaptive hash index)

6.innoDB 储存的锁信息  lock info

7.数据字典信息 data dictionary

8.show variables like 'innodb_buffer_pool_instances'\G;   设置大于1得值 ，可以得到多个池实例

9.查看缓存池  show engine innodb status\G;

Buffer  pool  size  共有多少个缓存页  num * 16K   总内存

free buffers  当前free列表中页的数量

database pages  表示LRU列表中页的数量

buffer pool size = free buffers + database pages;

pages  made young  显示了LUR列表中页移动到前端的次数

buffer pool hit rate 表示缓冲池的命中率 命中率小于95% 说全表扫描引起的LUR列表被污染的问题

四，innoDB缓存池的管理

1.LRU (最少使用算法管理)

show variables like 'innodb_old_blocks_pct'\G; 表示读取到的插入到LRU列表尾段37%的位置！

midpoint old表  new表的分结点

show variables like 'innodb_old_blocks_time'\G;表示读取的到mid后多久加到new表中 的时间  也就是让热点

设置： set global innodb_old_blocks_time = 1000;

information_schema;数据库

查看缓存池运行状态表：  innodb_buffer_pool_stats

LRU列表中每个页的具体信息： innodb_buffer_page_lru

2.重做日志缓存(redo log buffer)

innodb_log_buffer_size;   默认8M；

3.脏页的设置参数  强制性checkpoint

show variables like 'innodb_max_dirty_pages_pct'\G;

五，innoDB关键特性；

1.插入缓存 (insert Buffer)

条件： 索引是辅助索引 (secondary index) 索引不是唯一的(unique)

show engine innodb status\G;   查看innoDb引擎状态

seg size 显示了insert buffer 的大小为  当前值 * 16 KB

free list len 代表了空闲表的长度

SIZE 代表已合并页的数量

inserts  代表插入的记录数

merged recs 合并插入的记录数

merges  合并的次数

2.两次写(double write)

2.1  .内存中的doublewrite   buffer   大小为2M

2.2 物理磁盘上共享表空间中连续的128个页，即2个区extent    大小为2M

2.3 查看doublewrte 运行情况

show gobal status like 'innodb_dblwr%'\G;

3.自适应哈系索引(adaptive hash index)

一种非常快的查找方法！

通过缓冲池B+树页构造而来

4.异步IO(async IO)

linux命令Iostat    查看rrqm/s wrqm/s

show variable like 'innodb_use_native_aio'\G;

innodb_use_native_aio  启动可提高恢复速度的75%

5.刷新邻接页(flush neighbor Page)

五，MYSQL文件

1。查看数据库的所有参数

information_schema数据库

select * from global_variables where variable_name like 'innodb_buffer%'\G;

show variables like 'inodb_buffer%'\G;

2,参数类型

2.1动态参数(dynamic)

2.2静态参数(static)

set  修改参数   global  session  表明的修改是基于当前会话还是实例的生命周期

SET   @@global.变量名=值       设置全局变量

SET  @@session.变量名=值      设置局部变量

2.日志文件

2.1错误日志(error log)

对mysql的启动，运行，关闭进行了记录

查看错误日志的路径：  show variables like 'log_error'\G;

2.2二进制日志(binlog)

记录了对数据库执行更改的所有操作；

2.3慢查询日志(slow query log)

show variables like 'long_query_time'/G;

慢查询阀值参数   long_query_time

慢查询开关  log_slow_queries

无索引查询语句参数开关   log_queries_not_using_indexes

DBA 管理     mysqldumpsolw  mysql-slow.log

查询最慢的10条sql语句

mysqldumpsolw  -s al -n 10 mysql-solw.log

慢查询的日志表  在MYSQL数据库  SLOW_LOG

show create table mysql.slow_log\G;

参数log_output指定了慢查询的输出格式   默认为FILE  可以修改：

SET GLOBAL LOG_OUTPUT='TABLE';

设置睡眠时间为10S    select sleep(10)\G;

修改表的引擎为MYISAM   alter table mysql.slow_log engine=MYISAM;

2.4查询日志(log)

3.PID文件

当MYSQL实例启动时，将自己的进程写进PID文件中

show variables like 'pid_file'\G;

4.表结构定义文件 .frm 后缀的文件   cat   文件名.frm;

5.INNODB 存储引擎文件

六，表

innodb储存引擎表总是B+树索引组织的

数据都被逻辑储存在一个空间中  tablespace  表空间(ibdata1);

查看初始共享表空间大小     show  variables like 'innod_file_per_table'\G;

查询表空间的大小

system ls -lh /data/mariadb/ibdata*

-rw-rw---- 1 mysql mysql 12M 10月  9 22:11 /data/mariadb/ibdata1

表空间大小为12MB

段 (segment)

区(extent)

区是由连续页组成的空间    区的大小为1M

页  (page)或块(block)   页的大小为 16K

页是INNODB磁盘管理的最小单位

show variables like 'innodb_page_size'\G;

行 INNODB储存引擎是面向列的(row-oriented)  数据是按行进行存放的

每页最多存放  16KB/2 - 200 行记录          即7992行记录

6.1 INNODB行记录格式

compact(默认)   redundant   两种格式   (compressed     dynamic)行溢出储存方式

查看行记录结构        show table status like 'table_name'\G;

row_foramt  表示当前所使用的行记录结构类型

6.2 CHAR的行结构储存

1.查看字段的长度

select  字段名, char_length(字段名),length(字段名) from  表名\G;

select  字段名,  HEX(字段名)   from  表名\G;(十六进制的储存)

6.3查看储存引擎的文件格式语句

参数 innodb_file_format  指定文件格式

select @@version\G;    查看当前数据库版本

show variables like 'innodb_version'\G;

show variables like 'innodb_file_format'\G;

6.4约束

1.1数据完整性

约束是一个逻辑概念，用来保证数据的完整性；

索引是一个数据结构，既有逻辑上的概念，也代表物理储存方式

1.2对错误数据的约束   参数 sql_mode 决定

set  sql_mode='strict_trans_tables';    修改参数

6.5分区

1.1查看当前数据库是否启用了分区

show variables like '%partition%'\G;

show plugins\G;

1.2  RANGE 分区

实例： 按照ID进行分区

create table t (

id int

) engine=innodb

partition by range (id)(

partition p0 values less than (10),

partition p1 values less than (20));

insert into t select 9;

insert into t select 10;

insert into t select 15;

查看分区

select * from information_schema.partitions where table_schema=database() and table_name = 't'\G;

insert into t select 30;

追加分区最大值

alter table t add partition (partition p2 values less than maxvalue );

删除分区：  alter table  t   drop  partition  p2;

解析SQL语句      explain partition select * from t;

1.3 LIST 分区   值是离散的

事例：

create table b (

a int,

b int

)engine = innodb

partition by list(b)(

partition b0 values in (1,3,5,7,9),

partition b1 values in (0,2,4,6,8,10)

);

insert into b select 1,1;

insert into b select 1,4;

select * from information_schema.partitions where table_name='b' and table_schema=database()\G;

select table_name, partition_name, table_rows from information_schema.partitions where table_name='b' and table_schema=database()\G;

insert插入多行遇到分区未定义的值时；innodb 储存引擎将其视为一个事务，没有任何数据插入；MYISAM会将正确的数据插入，错误的不会被插入

1.4HASH 分区  将数据均匀的分布到预先定义的分区中，保证各分区的数据数量大致一样  用户定义函数区分

create table t_hash(

a int,

b datetime

)engine = innodb

partition by hash(year(b))    #by hash(expr) expr 是一个返回一个整数的表达式

#(可以是字段类型为整形的一列的名字)

partitions 4;#表示表将被分割成分区的数量

1.5  key分区  与HASH相似  不同在于mysql系统函数分区

create table t_hash(

a int,

b datetime

)engine = innodb

partition    by   key(b)    #by hash(expr) expr 是一个返回一个整数的表达式

#(可以是字段类型为整形的一列的名字)

partitions 4;#表示表将被分割成分区的数量

1.6  COLUMNS(列)分区   直接使用非整形的数据进行分区，分区根据类型比较而得，不需要转化为整形。

分区支持以下的数据类型：

1.6.1  所有的整形类型。 float  decimal 则不支持

1.6.2 日期类型。如date  datetime；   其余日期类型不支持

1.6.3  字符串类型 。 blob  text  类型不支持

事例：

create table t_columns_range(

a int,

b datetime

)engine=innodb

partition by range | list columns(b)(

partition p0 values less than ('2016-01-01'),

partition p1 values less than ('2017-01-01')

);

1.7分区中的NULl值

MYSQL 数据库的分区总是把NULL值视为小于任何一个非NULL值

1.7.1  对于range分区   插入NULL值，放入最左边的分区

1.7.2 对于LIST 分区 需要显示的指出那个分区放入NULL值，否则会报错

1.7.3 对于HASH KEY 任何分区函数都会将含有NULL值得记录返回为0

总结：数据库的运用分俩种：

1是OLTP(在线事物处理) 博客 电子商务 网络游戏

2是OLAP( 在线分析处理)数据仓库， 数据集市     这个适合分区  提高查询速度

七，索引与算法

1，B+树索引   最常用和最有效的索引

1.1数据结构和算法

二分查找法：(binary  search)

二叉查找树：

左子树的键值总是小于根的键值，右子树的键值总是大于根的键值；通过中序遍历得到键值的排序输出。

平衡二叉树：

符合二叉查找树的定义 &&  必须满足任何结点的两个子树的高度最大差为1

聚集索引  按照每张表的主键构造一颗B+树

辅助索引

查看表的索引    show index from 表名\G；

参数：cardinality   表示索引中不重复记录数量的预估值；

2，全文索引

3，哈系索引

2.索引提示  INDEX  HINT

八,锁

解释：锁是数据库系统区别于文件系统的的一个关键特性，管理对共享资源的并发访问。提供数据的完整性和一致性！

LOCK的对象是事务！用来锁定的是数据库中的对象，如表， 页， 行。

8.1  INNODB储存引擎的行级锁：

共享锁：(S  LOCK)  允许事务读一行数据    只与S锁兼容

排他锁：(X LOCK)  允许事务删除或更新一行数据   与任何锁都不兼容

意向共享锁(IS LOCK) 事务想要获得一张表中某几行的共享锁；

意向排他锁(IX LOCK) 事务想要获得一张表中某几行的排他锁；

INFORMATION_SCHEMA  数据中的3张锁表

INNODB_TRX   INNODB_LOCKS  INNODB_LOCK_WIATS

8.2 innodb 储存引擎对于SELECT语句支持两种一致性的锁定读(LOCKING READ )操作；

SELECT ........FOR UPDATE 对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁！

SELECT ........LOCK IN SHARE MODE 对读取的行记录加一个S锁，其他事务也可以加S锁，但是加X锁  就会阻塞！

show variables like 'innodb_autoinc_lock_mode'\G;   控制主键的自曾模式;

show variables like 'innodb_lock_wait_timeout'\G;    用来设定是否在等待超时对进行中的事务进行回滚操作(默认OFF，代表不回滚)；

九，事务   ACID特性

原子性

一致性

隔离性

持久性

9.2事务类型

扁平事务  begin  commit  rollback

带有保存点的扁平事务    保存点(SAVEPOINT)

链事务

嵌套事务

分布式事务

参数：innodb_flush_log_at_trx_commit; 控制重做日志刷新到磁盘的策略。

默认为1  表示事物提交必须调用一次函数fsync  操作；

0  表示事物提交时不进行写入重做日志操作

2表示事物提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync 操作：

9.3事务控制语句

开启事务：start   transaction | begin:

事务提交 ： commit;

事务回滚 ：rollback;

事务节点 ： savepoint;

删除节点：release savepoint identifier;

返回事务节点： rollback to identifier;

事务隔离级别：set transaction   四个级别;   read committed ;    read uncommitted  ; repeatable read; serializable

9.4查看当前事务隔离级别：  select  @@global.tx_isolation\G;

9.5事务的不良习惯：避免循环中提交事务， 自动提交和自动回滚！

十，数据库的备份

10.1  类别：

Hot backup   热备   在线备份

Clod backup  冷备   离线备份

Warm backup  温备   加锁备份

windows 下备份数据库命令：mysqldump -uroot -proot  数据库名称  >  路径