Mysql数据库四大特性、事物的四个隔离、基本MySQL语句、独立表空间

 

本人学习mysql的时候感觉笔记有点散所以自己做了一个整合，而且有些概念介绍的太官方了，所以自己根据理解总结了一下。（有不对的请指点！）

 

mysql：

sql：关系型数据库：（复杂的关系形数据库）。

nosql：非关系型数据库：（储存的格式很简单）

key，value（memcached），user1：1，user2：2（存在内存里）

 

 

事务：一组原子性的SQL查询，或者是一个或多个sql语句组成的独立工作单元；操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

 

事务的特性：

（1）原子性（Atomicity）：强调事务是一个整体，要么都执行，要不都不执行。

（2）一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态，强调数据的一致性。

（3） 隔离性（Isolation）：指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被企图进行修改的事务看到。

（4）持久性（Durability）：意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。

 

不考虑事务隔离性会引发的四个问题：

（1）脏读：一个事务读到另一个事务未提交的数据（就是数据不统一）。

说明：有两个用户a和b在使用同一个数据库A，a用户修改数据库数据但还未提交，这时候用户b还是能读取a用户没修改前的数据这就是脏读。

  （2）不可重复读：一个事务读到另一个事务已提交的数据，强调Update（更新）。

说明：有一个学生信息表，里面有一条数据[小明 男 25岁]；事务1现在读取这个条数据，将“小明”修改为“张三”，但是还没有提交到数据库中；这个时候事务2来进行对这条数据的操作，事务2需要两次读取这条数据，事务2第一次读取数据是在事务1还没有提交的时候，读出的数据依然是[小明 男 25岁]，但是事务2在第二次读取的时候，小明已经提交了，这个时候同样的读取操作结果确实[张三 男 25岁]，事务2两次读取的结果不一致，这就是不可重复读问题。

（3）虚度/幻读:一个事务读到另一个事务已提交的数据，强调Insert。

（4）丢失更新: 两个事务同时修改数据，后提交事务覆盖了先提交事务的结果。

说明：从字面上理解就行了，就是前一个修改的数据丢失了。

 

事物的隔离级别：四个

ISOLATION_DEFAULT：使用数据库默认的隔离级别。 
ISOLATION_SERIALIZABLE：完全服从ACID的原则，确保不发生脏读、不可重复读和幻读。 

ISOLATION_REPEATABLE_READ：对相同字段的多次读取结果一致（不可以重复度），不发生脏读，可导致幻读。 

ISOLATION_READ COMMITTED：允许并发事务提交之后读取，可以避免脏读，可能导致重复读和幻读。

ISOLATION_READ_UNCOMMITTED：允许读取改变了的还未提交的数据，可能导致脏读、不可重复读和幻读（等级最低）。 

##个人感觉了解两款主流的引擎是很有必要的，对数据库操作和理解就很有帮助。

主流引擎区别：

INNODB：支持事务处理与外键和行级锁，不支持全文索引，可以做回滚以及系统崩溃修复能力。（生产环境肯定使用这款，因为安全性比较高。）InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。主键范围更大，最大是MyISAM的2倍

MYISAM：读取的性能高，但是不支持事物，出错了，就不能回滚回来了。不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

 

日志管理：

二进制日志：mysqldump备份还原。

错误日志：查看mysql服务的错误日志。

慢查询日志：查看慢查询的日志。

通用查询日志：线上不提倡开启，io会过高。

注意：不能添加太多索引因为这样会使得写入的时候会变慢，所以一般使用的是联合索引。

 

Mysql语言例子：

插入单个数据：

Mysql>  INSERT INTO cml(I d,`name`) VALUE(1,'cml');

插入多个数据：

Mysql>  INSERT INTO cml(id,`name`)VALUE(2,'zhangfei'),(3,'zhaoyun');

 

查看数据：

Mysql>  SELECT * FROM cml;
+----+----------+
| id | name     |
+----+----------+
|  1 | cml |
|  2 | zhangfei |
|  3 | zhaoyun |
+----+----------+

 

建议程序员写查询的时候：

Mysql>  SELECT id,name FROM cml;
+----+----------+
| id | name     |
+----+----------+
|  1 | cml |
|  2 | zhangfei |
|  3 | zhaoyun |
+----+----------+

 

加条件的查看：

Mysql>  SELECT id,name FROM cml WHERE id=3;
+----+---------+
| id | name    |
+----+---------+
|  3 | zhaoyun |
+----+---------+

 

 

Mysql>  SELECT * FROM cml LIMIT 1;
+----+---------+
| id | name    |
+----+---------+
|  1 | cml |

 

Mysql>  SELECT * FROM cml ORDER BY id DESC LIMIT 1;
+----+---------+
| id | name    |
+----+---------+
|  3 | zhaoyun |
+----+---------+

 

4、更新字段的值：

Mysql>  UPDATE cml set name='guanyu' where id=1;
Query OK, 1 rowaffected (0.00 sec)
Rows matched:1  Changed: 1  Warnings: 0

 

5、删除id=1的值

Mysql>  DELETE FROM cml WHERE id=2;
Query OK, 1 rowaffected (0.00 sec)

6、DELETE 删除的时候计数器没有被清空：

Mysql>  DELETE FROM cml;           
Query OK, 2 rowsaffected (0.00 sec)
 
Mysql>  select * FROM cml;
Empty set (0.00sec)
 
Mysql>  INSERT INTO cml(`name`) VALUE('machao'); 
Query OK, 1 rowaffected (0.00 sec)
 
Mysql>  select * FROM cml;                      
+----+--------+
| id | name   |
+----+--------+
|  4 | machao |
+----+--------+

 

 

7、把表结构ID修改id自动增长，然后插入数据：

Mysql>  CREATE TABLE `cml` ( `id` int(11) NOT NULLAUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) );
 
Mysql>  INSERT INTO cml(`name`) VALUE('zhangfei');
Query OK, 1 rowaffected (0.00 sec)
 
8、清空计数器：
Mysql>  TRUNCATE cml;
Query OK, 0 rowsaffected (0.00 sec)
 
Mysql>  INSERT INTO cml(`name`) VALUE('machao');
Query OK, 1 rowaffected (0.00 sec)
 
Mysql>  select * FROM cml;                          
+----+--------+
| id | name   |
+----+--------+
|  1 | machao |
+----+--------+
1 row in set(0.00 sec)

 

9、插入测试数据：

Mysql>  CREATE TABLE `cml` ( `id` int(11)AUTO_INCREMENT, `name` varchar(20) , age int, PRIMARY KEY (`id`) );
Query OK, 0 rowsaffected (0.00 sec)
 
Mysql>  INSERT INTO cmlVALUE(1,'zhangfei',20),(2,'zhaoyun',25),(3,'machao',30);
Query OK, 3 rowsaffected (0.00 sec)
Records: 3  Duplicates: 0 Warnings: 0
 
Mysql>  select * from cml;
+----+----------+------+
| id | name     | age |
+----+----------+------+
|  1 | zhangfei |   20 |
|  2 | zhaoyun |   25 |
|  3 | machao  |   30 |
 
Mysql>  SELECT name FROM cml WHERE age>25;
+--------+
| name   |
+--------+
| machao |
+--------+
 
Mysql>  SELECT name FROM cml WHERE age>25 ANDage<=30;
+--------+
| name   |
+--------+
| machao |

 

按照年龄排序：

Mysql>  SELECT * FROM cml ORDER BY age;
+----+----------+------+
| id | name     | age |
+----+----------+------+
|  4 | wanggai |   18 |
|  1 | zhangfei |   20 |
|  2 | zhaoyun |   25 |
|  3 | machao  |   30 |
+----+----------+------+

 

独立表空间：

 

优点：

每个表都有自已独立的表空间。

每个表的数据和索引都会存在自已的表空间中。

可以实现单表在不同的数据库中移动。

空间可以回收（除drop table操作处，表空不能自已回收）

       1、Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以          通过:alter table TableName engine=innodb;回缩不用的空间。

       2、对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

       3、对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且            还有机会处理。

缺点：

 

单表增加过大，如超过100个G。

 

结论

 

共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整一下：innodb_open_files 。InnoDB Hot Backup（冷备）的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。

 

innodb_file_per_table设置.

 

开启方法：

在my.cnf中[mysqld]下设置

innodb_file_per_table=1


查看是否开启：

mysql> show variables like'%per_table%';
+-----------------------+-------+
| Variable_name        | Value |
+-----------------------+-------+
| innodb_file_per_table | ON   |
+-----------------------+-------+
1 row in set (0.02 sec)


 

关闭独享表空间：

innodb_file_per_table=0

关闭独立的表空间

mysql> show variables like'%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.01 sec)