马哥 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>