You are here
马哥 34_01 _MySQL系列之九——MySQL事务和隔离级别 有大用
广义查询:还包括下面三个
DML:
delete:先查找到,才能删除
insert into:唯一键或主键限定,就不能一样,所以也要先查
update:先查找到,才能更新
插入
insert:
insert into tb_name (col1,col2,...) values (val1,val2,...)[(val1,val2,...),...]
# insert into 是可以批量插入的,有些场景下,批量插入可以提高性能的
字符型:单引号,
数值型:不需要引号,
日期时间型:不需要引号?
空值: NULL, ''(两个单引号,不是null,它是字符串的空串)
replace into tb_name # 没有就插入,有的话就替换 ,用法与 insert into 一样
删除
delete:
delete from tb_name where condition; (忘了加where条件,就全部删除了,它没有回收站)
(所以有个内置的选项,没有 where 子句 它不让你执行)
truncate: 完全清空表,连主键的自动增长的id也置为0(重置计数器)
truncate tb_name
更新:
update:
update tb_name set col1=..., col2=... where
用法简单,里面涉及到很多操作,rdbms隐藏底层的细节
mysql> help insert into
Nothing found
Please try to run 'help contents' for a list of all accessible topics
mysql>
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql>
mysql> help insert # help insert 才是帮助 共3种
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),... # 可插入表达式 # 花括号 里面有竖线 或者
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ... # 有点类似于 update
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] # 把查询结果插入,(当然字段得对应)
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT inserts new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on
explicitly specified values. The INSERT ... SELECT form inserts rows
selected from another table or tables. INSERT ... SELECT is discussed
further in [HELP INSERT SELECT].
URL: http://dev.mysql.com/doc/refman/5.5/en/insert.html
mysql>
mysql> use jiaowu;
Database changed
mysql> desc tutors;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(50) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> insert into tutors set tname='tom',gender='F',age=30; #只能插入一行,无法批量插入
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
+-----+--------------+--------+------+
10 rows in set (0.00 sec)
mysql>
mysql> select * from tutors order by tid desc limit 1;
+-----+-------+--------+------+
| tid | tname | gender | age |
+-----+-------+--------+------+
| 10 | tom | F | 30 |
+-----+-------+--------+------+
1 row in set (0.00 sec)
mysql>
即使把表tutors 全部删除了,下一个tid 还是从删除前的最大的tid+1开始的
mysql> select last_insert_id(); #因为它此前的计数保留在一个内置的函数当中, 除非把它这个值改为0
+------------------+
| last_insert_id() |
+------------------+
| 10 |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> desc students;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| cid1 | int(11) | YES | | NULL | |
| cid2 | int(11) | YES | | NULL | |
| tid | int(11) | YES | | NULL | |
| createTime | datetime | YES | | NULL | |
+------------+---------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> desc tutors;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(50) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> select name,gender,age from students;
+--------------+--------+------+
| name | gender | age |
+--------------+--------+------+
| GuoJing | M | 19 |
| YangGuo | M | 17 |
| DingDian | M | 25 |
| HuFei | M | 31 |
| HuangRong | F | 16 |
| YueLingshang | F | 18 |
| ZhangWuji | M | 20 |
| Xuzhu | M | 26 |
| LiHuchong | M | 22 |
| YiLin | F | 19 |
| stu1 | F | 23 |
| stu2 | F | 23 |
+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>select name,gender,age from students where age>20; # 取出年龄大于20的学生
+-----------+--------+------+
| name | gender | age |
+-----------+--------+------+
| DingDian | M | 25 |
| HuFei | M | 31 |
| Xuzhu | M | 26 |
| LiHuchong | M | 22 |
| stu1 | F | 23 |
| stu2 | F | 23 |
+-----------+--------+------+
6 rows in set (0.00 sec)
mysql>insert into totors (tname,gender,age) select name,gender,age from students where age>20;
#把年龄大于20的学生插入到教师表中
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 15 | stu1 | F | 23 |
| 16 | stu2 | F | 23 |
+-----+--------------+--------+------+
16 rows in set (0.00 sec)
mysql>
mysql> help replace
Name: 'REPLACE'
Description:
Syntax:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE works exactly like INSERT, except that if an old row in the
table has the same value as a new row for a PRIMARY KEY or a UNIQUE
index, the old row is deleted before the new row is inserted. See [HELP
INSERT].
REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL---that
either inserts or updates---see
http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html.
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT,
because there is no index to be used to determine whether a new row
duplicates another.
Values for all columns are taken from the values specified in the
REPLACE statement. Any missing columns are set to their default values,
just as happens for INSERT. You cannot refer to values from the current
row and use them in the new row. If you use an assignment such as SET
col_name = col_name + 1, the reference to the column name on the right
hand side is treated as DEFAULT(col_name), so the assignment is
equivalent to SET col_name = DEFAULT(col_name) + 1.
To use REPLACE, you must have both the INSERT and DELETE privileges for
the table.
URL: http://dev.mysql.com/doc/refman/5.5/en/replace.html
mysql>
mysql> help delete
Name: 'DELETE'
Description:
Syntax:
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition] # where 跟select 一样
[ORDER BY ...] # order by 跟select 一样
[LIMIT row_count] # limit 跟select 一样
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
For the single-table syntax, the DELETE statement deletes rows from
tbl_name and returns a count of the number of deleted rows. This count
can be obtained by calling the ROW_COUNT() function (see
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html). The
WHERE clause, if given, specifies the conditions that identify which
rows to delete. With no WHERE clause, all rows are deleted. If the
ORDER BY clause is specified, the rows are deleted in the order that is
specified. The LIMIT clause places a limit on the number of rows that
can be deleted.
For the multiple-table syntax, DELETE deletes from each tbl_name the
rows that satisfy the conditions. In this case, ORDER BY and LIMIT
cannot be used.
where_condition is an expression that evaluates to true for each row to
be deleted. It is specified as described in
http://dev.mysql.com/doc/refman/5.5/en/select.html.
Currently, you cannot delete from a table and select from the same
table in a subquery.
You need the DELETE privilege on a table to delete rows from it. You
need only the SELECT privilege for any columns that are only read, such
as those named in the WHERE clause.
As stated, a DELETE statement with no WHERE clause deletes all rows. A
faster way to do this, when you do not need to know the number of
deleted rows, is to use TRUNCATE TABLE. However, within a transaction
or if you have a lock on the table, TRUNCATE TABLE cannot be used
whereas DELETE can. See [HELP TRUNCATE TABLE], and [HELP LOCK].
URL: http://dev.mysql.com/doc/refman/5.5/en/delete.html
mysql>
mysql> help truncate; # 与 truncate 表名 不一样??忘记这是什么意思了
Name: 'TRUNCATE'
Description:
Syntax:
TRUNCATE(X,D)
Returns the number X, truncated to D decimal places. If D is 0, the
result has no decimal point or fractional part. D can be negative to
cause D digits left of the decimal point of the value X to become zero.
URL: http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html
Examples:
mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
mysql> SELECT TRUNCATE(1.999,1);
-> 1.9
mysql> SELECT TRUNCATE(1.999,0);
-> 1
mysql> SELECT TRUNCATE(-1.999,1);
-> -1.9
mysql> SELECT TRUNCATE(122,-2);
-> 100
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1028
mysql>
[root@mail ~]# mysqldump -u root -p jiaowu > jiaowu.sql # 先备份导出数据库吧
(为了后面的清空表)
mysql> delete from students;
Query OK, 12 rows affected (0.01 sec)
mysql>
mysql> select * from students;
Empty set (0.00 sec)
mysql>
mysql> insert into students(name,age,gender) values('tom',30,'F'); #再插一次
Query OK, 1 row affected (0.04 sec)
mysql> select * from students; # sid 不是1,由原来最大的增长了1
+-----+------+------+--------+------+------+------+------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+------+------+--------+------+------+------+------------+
| 13 | tom | 30 | F | NULL | NULL | NULL | NULL |
+-----+------+------+--------+------+------+------+------------+
1 row in set (0.00 sec)
mysql>
mysql> insert into students(name,age,gender) values('jerry',30,'F'); #再插一次
Query OK, 1 row affected (0.04 sec)
mysql> select * from students;
+-----+-------+------+--------+------+------+------+------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+-------+------+--------+------+------+------+------------+
| 13 | tom | 30 | F | NULL | NULL | NULL | NULL |
| 14 | jerry | 30 | F | NULL | NULL | NULL | NULL |
+-----+-------+------+--------+------+------+------+------------+
2 rows in set (0.00 sec)
mysql>
mysql> truncate students; # 清空表
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into students(name,age,gender) values('tom',30,'F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students; #此时,插入后 ,id 从 1 开始了
+-----+------+------+--------+------+------+------+------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+------+------+--------+------+------+------+------------+
| 1 | tom | 30 | F | NULL | NULL | NULL | NULL |
+-----+------+------+--------+------+------+------+------------+
1 row in set (0.00 sec)
mysql>
mysql> help update
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition] #设置条件,不设的话,全部修改
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
For the single-table syntax, the UPDATE statement updates columns of
existing rows in the named table with new values. The SET clause
indicates which columns to modify and the values they should be given.
Each value can be given as an expression, or the keyword DEFAULT to set
a column explicitly to its default value. The WHERE clause, if given,
specifies the conditions that identify which rows to update. With no
WHERE clause, all rows are updated. If the ORDER BY clause is
specified, the rows are updated in the order that is specified. The
LIMIT clause places a limit on the number of rows that can be updated.
For the multiple-table syntax, UPDATE updates rows in each table named
in table_references that satisfy the conditions. In this case, ORDER BY
and LIMIT cannot be used.
where_condition is an expression that evaluates to true for each row to
be updated. For expression syntax, see
http://dev.mysql.com/doc/refman/5.5/en/expressions.html.
table_references and where_condition are is specified as described in
http://dev.mysql.com/doc/refman/5.5/en/select.html.
You need the UPDATE privilege only for columns referenced in an UPDATE
that are actually updated. You need only the SELECT privilege for any
columns that are read but not modified.
The UPDATE statement supports the following modifiers:
o With the LOW_PRIORITY keyword, execution of the UPDATE is delayed
until no other clients are reading from the table. This affects only
storage engines that use only table-level locking (such as MyISAM,
MEMORY, and MERGE).
o With the IGNORE keyword, the update statement does not abort even if
errors occur during the update. Rows for which duplicate-key
conflicts occur are not updated. Rows for which columns are updated
to values that would cause data conversion errors are updated to the
closest valid values instead.
URL: http://dev.mysql.com/doc/refman/5.5/en/update.html
mysql>
mysql 数据是明文的
会话管理器还要负责 ssl 的加密会话
连接管理器:
接受请求 (监听在某个套接字上接受请求,有可能需要建立安全连接)
创建线程 (可以有线程池,由服务器负责缓存线程,即线程重用,连接管理器)
认证用户
建立安全连接
当我们发起查询以后,mysql 服务器会负责解析每一个查询,并且在mysql的进程内部创建一个解析树,然后在解析树的基础上进行各种优化,并计算出最优执行路径,,,,所以这就是解析器和优化器的作用
优化器的优化是自我判定的,与我们实际的场景可能有出入,因为优化器是在程序的基础上做了智能判定,但是程序本身未必像人一样,会考虑到各种变化情况,所以优化的结果未心是最佳结果.
如果我们知道哪种方式更优,可以给优化器以提示,执行语句的时候,来个简单的提示,要使用哪个索引,可能会更好,这样子就避免优化器自动挑选索引,再去执行,再去计算执行路径的开销等情况.
这一切都是优化器和解析器共同完成的
由于mysql是插件式引擎,所以考量优化的时候,不会考虑存储引擎的不同,不会考虑myisam与innodb的不同,
每一个查询结束之后,如果结果是决定性的,mysql还会缓存查询结果,这就是缓存器,缓存的目的是结果重用
缓存空间有限的,太大的数据是不能缓存的,,,如果数据太小(比如4个字节),也不要缓存
每个缓存区域里面都有槽,(缓存槽),每个查询结果都要占据槽的,
为了方便管理槽,(比如每个槽有512个字节)(数据4个字节的话,就浪费了太多),,
缓存中会产生大量碎片(因为不停的生效,失效,创建),执行效率也会差
对缓存要进行优化,各种限定
有些结果不能缓存 比如 select current_time(); 非确定性结果
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 10:59:36 |
+----------------+
1 row in set (0.00 sec)
两个语句一模一样,未必能使用缓存,一个用户可以select,另一个用户一模一样的语句,但是他没有权限
缓存很复杂,有了缓存带来的结果未必都是优势;如果没有缓存,直接交给解析树解析;如果有缓存,必须到缓存中看是否命中,如果缓存中没有,再去找解析树,,,,所以好多时候是白白地去找一次缓存
两用户同时连接数据库,第一个用户写表A,第二个用户查询表A
并发控制:
mbox邮件,两个进程同时投邮件,,MDA,它们交替着一行一行地写,会杂乱无章,所以要并发控制
一般同时写文件的话加锁
mysql服务器必须由 服务器端完成并发控制
C/A: 并发量大 比如 同时100个用户进来,,串行,一个完成,另一个才进去,必须等待前面的完成
多版本并发控制: MVCC ( Multi-Version Concurrency Control),每一个用户并不是操作源数据,而是副本或快照,最后完成快照合并,(一个用户查询别人正在修改的行的话,就算使用快照,它也受到非常复杂的控制机制)
锁:(简单的并发控制是锁,多版本并发控制好像不是锁)
读锁:共享锁,读的时候允许其它人读,但不允许写
写锁:独占锁(排他锁),写的时候,不允许其它人写,也不允许其它人读
lock tables tb_name lock_type # lock_type 就是read或write
unlock tables #解除所有表的锁,不能指定解哪个锁
锁粒度: 从大到小;;;;;;; mysql服务器仅支持表级锁,行锁需要存储引擎的支持,需要由存储引擎完成: 几乎所有的存储引擎,都有自己的锁策略,我们一般不需要在服务器上施加锁的
表锁:锁定一张表的
页锁:锁定数据块的,一个块一般情况下是多个行,这里页应该就是数据块的意思,一般用不到它吧
行锁:锁定一行的,范围最小,粒度最精细
越粗糙,越易管理,,,越精细越能够实现多个操作的并发性,
行锁,查询某表,不影响另一个人更新,又不影响再增加一个和删除一个(只要不是自己行),它们可以同时进行;
表锁的话,就锁定了整个表
粒度越精细,越能并发,内部实现机制就越复杂,数据库实现细节上越复杂,
mysql 自己自动加锁的(比如update),其他人的读写,受mysql的内在逻辑进行控制的
可以明确手动施加锁
一般我们没有必要去加锁的,因为mysql内部自己自动加锁了
在特殊情况下(比如数据库温备份的时候),才需要自己手动加锁
mysql> help lock
Name: 'LOCK'
Description:
Syntax:
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: #锁类型,读锁,写锁
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES #解除锁
MySQL enables client sessions to acquire table locks explicitly for the
purpose of cooperating with other sessions for access to tables, or to
prevent other sessions from modifying tables during periods when a
session requires exclusive access to them. A session can acquire or
release locks only for itself. One session cannot acquire locks for
another session or release locks held by another session.
Locks may be used to emulate transactions or to get more speed when
updating tables. This is explained in more detail later in this
section.
LOCK TABLES explicitly acquires table locks for the current client
session. Table locks can be acquired for base tables or views. You must
have the LOCK TABLES privilege, and the SELECT privilege for each
object to be locked.
For view locking, LOCK TABLES adds all base tables used in the view to
the set of tables to be locked and locks them automatically. If you
lock a table explicitly with LOCK TABLES, any tables used in triggers
are also locked implicitly, as described in
http://dev.mysql.com/doc/refman/5.5/en/lock-tables-and-triggers.html.
UNLOCK TABLES explicitly releases any table locks held by the current
session. LOCK TABLES implicitly releases any table locks held by the
current session before acquiring new locks.
Another use for UNLOCK TABLES is to release the global read lock
acquired with the FLUSH TABLES WITH READ LOCK statement, which enables
you to lock all tables in all databases. See [HELP FLUSH]. (This is a
very convenient way to get backups if you have a file system such as
Veritas that can take snapshots in time.)
URL: http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
mysql>
在一个putty窗口
mysql> use jiaowu;
Database changed
mysql> lock tables tutors read; #锁定表 读类型锁
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> unlock tables; # 这边一释放锁
Query OK, 0 rows affected (0.00 sec)
mysql>
另一个putty 窗口
mysql> select * from tutors; # 可以查询
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 15 | stu1 | F | 23 |
| 16 | stu2 | F | 23 |
+-----+--------------+--------+------+
16 rows in set (0.00 sec)
mysql> insert into tutors (tname,gender,age) values('jerry','M',50); #卡住了,等待别人释放锁
Query OK, 1 row affected (4 min 29.61 sec) # 前面的putty释放锁之后,这边立马插入成功(这里执行了29.61秒)
一个查询慢,并非真的由于它慢,而是由于其它操作的影响
事务:
现在的rdbms: 都支持事务,
满足四种测试: ACID 测试 (原子性(要么全执行,要么全不执行) 一致性(事务完成前后是一致的,完成前两个账户的和与完成两个账户的和是一样的) 隔离性(与其它事务隔离,不影响) 持久性(就是宕机后重启,数据还是可看到的,内存中完成计算后,立马同步到外部存储)
ACID:原子性（atomicity );一致性（consistency );隔离性（isolation );持久性（durability）
事务能力由存储引擎提供的
MyISAM:不支持事务
InnoDB:支持事务
事务的隔离性:
隔离级别: 以下级别越来越高
READ UNCOMMITTED:读未提交;;别人只要操作,我这里立马就能看到,,,隔离级别最低,干扰可能性最大
READ COMMITTED:读提交;;别人提交以后才能看到,,,(别人删除,未提交时可看到,提交后看不到了,,产生幻读)
REPEATABLE READ:可重读的;; (无论别人提不提交,我事务至始至终,看到的都是一样的,,,别人删除,别人未提交时可看到,别人提交后,也可看到30行.假如看到30行,添加2行,我自己提交后发现26行,(原因是别人的事务删了6行),这个仍会产生幻读)
SERIALIZABLE: 可串行;;一个完成,另一个才进去;让其没有并发能力
mysql 默认是 REPEATABLE READ ,很安全的,
隔离级别越高,并发能力越低,服务器性能越差;;;;
对隔离级别要求不高的话,可以级别调低点,性能会更好
事务日志:
重做日志:在写到数据库前,它就先写到重做日志了,想重做时就重做
redo log
撤销日志:在操作前,把原有的状态保存下来,将来可以撤销
undo log
在事务引擎上(比如 innodb ) mysql 根据事务日志,自动完成重做或撤销的操作
在事务引擎上(比如 innodb )
事务日志记录的是操作,而不是数据;;;日志里进行redo,写到数据文件
总感觉事务 未提交时,里面进行的增,删,改,查的操作是在一个副本中进行的,因为未提交,未写入到数据文件;;;;;而在事务里面查询插入的数据,肯定是改变了的数据,,,,这说明,应该是副本里的数据吧
日志文件不需要太大,看单个事务有多大
比如单个事务有2M,我们给它5M,当2M日志提交到数据文件(磁盘)上时,2M就腾出来了
日志组:
一般两个日志文件,轮流交替使用,效率高
如果日志文件太大(比如500M),启动mysql时,会有大量的日志写入到数据文件,会很卡
启动mysql时,会检查日志文件,进行回滚或撤销的工作
万一数据文件磁盘坏了,
所以不要把日志文件和数据文件放在同一个磁盘上
放在raid10 上都无所谓
防止磁盘坏,还要备份
串行,让其没有并发能力,让事务之间影响最小
mysql> show global variables like '%iso%'; #看下隔离级别; REPEATABLE-READ #tx_isolation 意思是transaction isolation(事务隔离)
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> set tx_isolation='READ-UNCOMMITTED'; #修改会话级别的变量,这里session省掉了,可以是 set session tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation; #查看服务器会话级别的变量的值 session省掉了,可以是 select @@session.tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql>
