12、说说mysql

旧城等待， 2022-05-28 11:00 134阅读 0赞

![Center][]  
注意：存储引擎是基于表的，而不是数据库。  
innoDB VS MyiSAM  
innoDB 支持行锁、事务、事务隔离、聚集数据  
MyiSAM支持表锁，非聚集数据

show engines ;
    use mysql ;
    show tables ;
    select * from user; 
    show variables like 'innodb_version' ;

连接方式  
TCP/IP

Linux安装mysql

$ wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
    $ rpm -ivh mysql-community-release-el6-5.noarch.rpm
    $ yum install mysql-server
    $ mysql -u root
    登录时有可能报这样的错：ERROR 2002 (HY000): Can‘t connect to local
     MySQL server through socket ‘/var/lib/mysql/mysql.sock‘
    (2)，原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户：
    $ sudo chown -R mysql:mysql /var/lib/mysql #这里的mysql是用户名（需要设置用户组合用户）
    $ service mysqld restart
    $ mysql -u root

一、启动方式
    　　1、使用 service 启动：service mysqld start
    　　2、使用 mysqld 脚本启动：/etc/init.d/mysqld start
    　　3、使用 safe_mysqld 启动：safe_mysqld&
    二、停止
    　　1、使用 service 启动：service mysqld stop
    　　2、使用 mysqld 脚本启动：/etc/init.d/mysqld stop
    　　3、mysqladmin shutdown
    三、重启
    　　1、使用 service 启动：service mysqld restart
    　　2、使用 mysqld 脚本启动：/etc/init.d/mysqld restart
    四、查看运行状态
       1、使用 service 启动：service mysql status
       2、使用 mysqld 脚本启动：/init.d/mysqld status

## 锁 ##

锁是数据库系统区别文件系统的一个关键特性，数据库使用锁是为了支持对共享资源的并发访问，提供数据的完整性和一致性。InnoDB提供了非锁定读，行锁的支持。

### 锁的类型 ###

#### InnoDB提供了2种标准的行级锁： ####

1、共享锁\[读锁\]  
2、派他锁\[写锁，更新或删除\]

#### 粗粒度锁 ####

1、意向共享锁\[ IS \] 事务想要获得一张表上几行的共享锁。  
2、意向排他锁 \[ IX\] 事务想要获得一张表上几行的排他锁。  
意向锁之间不会不兼容，即IX 和IS可以同时出现在一张表上，IX和S是不兼容的。  
意向锁虽然是在表/库/页上添加的，但作用还是在行，受影响的还是行。

#### 一致性非锁定读 ####

InnoDB通过MVCC(multi-version concurrent controll)实现commit-read 和repeatable-read下的非锁定读。  
mvcc和\[X/S\]是两个完全不同的概念，X/S是对行上面的数据，而MVCC是行上的快照，并且不只一个。  
如果在mvcc中执行了更新操作，会对行上添加X锁。

在commit-read中，每次查询都是最新的数据。读取快照下的最后一个数据。  
在repeatable-read中，只读事务开始时的数据。读取本事务最先的数据。

#### 一致性锁定读 ####

**数据不一致的产生？**  
特别在repeatable-read的情况下，是很容易出问题的，因为当前事务读到的都是历史数据，而行上的数据已经改变了，而你的逻辑还在使用老数据在操作，最后更新到表里去，明显数据会出现问题。  
在commit-read的情况下，也会出现类似的问题，因为你不能保证你查询到的数据和行上的数据是实时一致的，如果不加以控制是不对的。  
这里有两种解决方案：  
1、使用乐观锁，必须带版本号的，更新操作最好自增减的；版本号必须自增。  
2、使用悲观锁或共享锁

select * from table for update ;  //加X锁，同事务为重入锁
    select * from table in share mode ;  //加S锁

**使用悲观锁和共享锁都会阻塞 写操作，不会阻塞普通读操作； 在事务提交或连接丢失时，锁自动释放。**

### 锁的算法 ###

racord lock 行锁  
gap lock 间隙锁 不含本身  
next-key lock 范围锁 含本身

假如已有数据：  
| id | name | age |  
|1 | sunxk |21 |  
|2| nick | 46|  
如果age上添加了索引，则范围锁会被采用，因为，因插入的数据，会涉及到一区间范围，如  
insert into user(name,age) values (nn,25) ;  
此时，事务会锁定（21，46\],如果此时正好也有一条数据在插入：  
insert into user(name,age) values (nn,26) ;  
后面这条会被阻塞。  
另外，如果更新条件正好为一个索引值，如  
update user set name = kkk where age = 21 ;  
这时也会采用范围锁，锁住 （0，21\] 和（21，46\]。  
如果一行数据既有主键也由其他索引，对这行数据进行更新的时候，会采用范围锁。

同理，如果时插入一条带主键的sql呢?没有其他索引。  
这时采用行锁，X锁。  
如果含有其他索引，会采用 间隙锁。

如果悲观锁或共享锁带有主键条件，则采用 行锁。

举个例子：  
表task\_queue  
Id taskId  
1 2  
3 9  
10 20  
40 41

开启一个会话： session 1  
sql> set autocommit=0;  
\#\#  
取消自动提交

sql> delete from task\_queue where taskId = 20;  
sql> insert into task\_queue values(20, 20);

在开启一个会话： session 2  
sql> set autocommit=0;  
\#\#  
取消自动提交

sql> delete from task\_queue where taskId = 25;  
sql> insert into task\_queue values(30, 25);

在没有并发，或是极少并发的情况下， 这样会可能会正常执行，在Mysql中， 事务最终都是串行执行， 但是在高并发的情况下， 执行的顺序就极有可能发生改变， 变成下面这个样子：  
sql> delete from task\_queue where taskId = 20;  
sql> delete from task\_queue where taskId = 25;  
sql> insert into task\_queue values(20, 20);  
sql> insert into task\_queue values(30, 25);

这个时候最后一条语句：insert into task\_queue values(30, 25); 执行时就会爆出死锁错误。因为删除taskId = 25这条记录的时候，9 – 41 都被锁住了，sql3 等待释放锁，被阻塞，而sql4被自己阻塞了，以为也不能获取X锁。

这种问题的解决办法：前面说了， 通过修改innodb\_locaks\_unsafe\_for\_binlog参数来取消间隙锁从而达到避免这种情况的死锁的方式尚待商量， 那就只有修改代码逻辑， 存在才删除，尽量不去删除不存在的记录。

## 底层文件 ##

### 配置文件（linux）： ###

mysql --help |grep my.cnf
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

### 日志文件 ###

binlog ，记录更改日志，不含有select /show ;开启后性能损失1%，建议是开启。 show variables like ‘%log\_bin%’  
error log ,记录了mysql的启动，关闭，运行错误等；show variables like ‘log\_error’ ;  
slow log,慢查询日志，  
—show variables like ‘long\_query\_time’ ; 默认为超过10s为慢查询。

### 开启MySQL的binlog日志 ###

如何开启mysql的binlog日志呢？  
在my.cnf主配置文件中直接添加三行

log_bin=ON  
    log_bin_basename=/var/lib/mysql/mysql-bin  
    log_bin_index=/var/lib/mysql/mysql-bin.index  
    server-id=123454

三个参数来指定，  
第一个参数是打开binlog日志  
第二个参数是binlog日志的基本文件名，后面会追加标识来表示每一个文件  
第三个参数指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录  
随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定了

有了上述的配置之后，重启  
service mysqld restart

启动成功之后，我们可以登陆查看我们的配置是否起作用  
show variables like ‘%log\_bin%’  
![Center 1][]

### 表结构文件 ###

.frm

### 表空间文件 tablespace ###

什么是表空间?表文件的逻辑单元。

### 重做日志 redo log ###

重做日志组，至少包含2个重做日志文件。如ib\_log\_0 ,ib\_log\_1 ;可以设置多个重做日志组，一个组下可设置多个重做文件。  
先写ib\_log\_0，写满后 再写ib\_log\_1 ;

redo\_buffer -> redo\_log 。  
重做日志不需要双写。由master线程控制完成。

### bin\_log和 redo\_log日志的区别： ###

bin\_log记录的是操作逻辑，用于数据恢复和复制。在更改提交时记录，属于mysql的范畴。  
redo\_log记录是物理快照，在mysql宕机后恢复。在任何sql执行都记录，属于innoDB范畴。

当使用事务的存储引擎InnoDB时，所有未提交的事务会记录到一个缓存中，事务提交时，直接将缓冲中的二进制日志写入二进制日志文件，可以设置同步写入。  
重做日志有个capacity变量，该值代表了最后的检查点不能超过这个阀值。  
重做日志用来实现事物的持久性，即事务ACID中的D。