简介

本文介绍了设置MySQL事务的三种方式：全局、当前session和下一个事务，并提供了示例代码展示如何开始事务和设置隔离级别。

同时，提到了不同数据库的SQL占位符语法差异。然后对比了InnoDB引擎的特点，如支持事务和行级锁，并给出查看行锁争用的查询。

然后简述了引擎设置和数据源DSN格式。最后，讨论了SQL优化策略，包括选择合适的存储引擎、优化字段数据类型、建立索引、避免全表扫描等。

1 设置事务的几种方式

1全局

    直接改配置文件
        set global transaction isolation level repeatable read;

2当前session

        set tx_isolation = 'repeatable-read';
        set session transaction isolation level repeatable read

3下一个事务

        set transaction isolation level repeatable read;

• 例子

go 启动 mysql 事务
DSN = "admin:admin20@tcp(127.0.0.1:3306)/mystate?multiStatements=true&allowNativePasswords=false&checkConnLiveness=false&maxAllowedPacket=0"

创建连接池，默认无限制

    db, err = sql.Open("mysql", DSN) 
    if err != nil {
        log.Fatal(err)
    }
    db.SetConnMaxLifetime(time.Minute * 3)
    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)

    Logg.Println("conn mysql success with", DSN)
    defer db.Close()

    ptx, err := db.Begin()
    ptx, err := db1.Begin()
    if err != nil {
        msg := fmt.Sprintf("start translation failure wuth db1 connection.: %+v\n", err)
        panic(msg)
    }
    _,err = ptx.Exec("ROLLBACK;")
    rst, isoerr := ptx.Exec("SET TRANSACTION ISOLATION LEVEL " +isolationLevel)
    if isoerr != nil {
        msg := fmt.Sprintf("set fault to :%v, %v\n", isolationLevel, isoerr)
        panic(msg)
    }
    _,err = ptx.Exec("BEGIN;")
    name, err := ptx.Exec("SELECT name FROM users where id = 1;")

补充：不同的数据库中，SQL语句使用的占位符语法不尽相同。

数据库 占位符语法

    MySQL    ?
    PostgreSQL    $1, $2等
    SQLite    ? 和$1
    Oracle    :name

2 引擎innoDB 的对比

mysql支持事务的默认引擎为innoDB.

InnoDB的行锁
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION),并且事务是默认自动进行提交的(可修改autocommit变量)；二是采用了行级锁。

行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

这里注意一个问题，和锁没什么关系，可以跳过：InnoDB没设置主键使用隐式ROW_ID.

• 1. 查看行锁的争用情况

     show status like "innodb_row_lock%";
         +-------------------------------+-------+
        | Variable_name                 | Value |
        +-------------------------------+-------+
        | Innodb_row_lock_current_waits | 0     |
        | Innodb_row_lock_time          | 0     |
        | Innodb_row_lock_time_avg      | 0     |
        | Innodb_row_lock_time_max      | 0     |
        | Innodb_row_lock_waits         | 0     |
        +-------------------------------+-------+
        5 rows in set (0.09 sec)
     

     如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高.
     还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

     并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 2 锁的粒度
  SQL Server可以锁定表、分页、行等级别的数据资源。它同样可以锁定索引键及一定范围内的索引键。
  谨记如果表上存在聚集索引，数据行就在聚集索引的叶级，并且是由键锁而不是行锁来锁定它们的。

  SQL中的锁(行锁、页锁、表锁、共享锁、排它锁、乐观锁、悲观锁)

  SQL中的锁按照锁颗粒对锁进行划分行锁页锁表锁从数据库管理的角度对锁进行划分共享锁排它锁从程序员的角度对锁进行划分
  乐观锁 (Optimistic Locking）悲观锁（Pessimistic Locking）适用场景避免死锁的发生 锁用来对数据进行锁定，我们可以从锁定对象的粒度大小来对锁进行划分，分别为行锁、页锁和表锁。
  按照锁颗粒对锁进行划分 行锁 就是按照行的粒度对数据进行锁定。
  锁定力度小，发生锁冲突概率低，可以实现的并发度高，但是对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

3 引擎设置

数据源设置 ,完整形式的 DSN：

username:password@protocol(address)/dbname?param=value

除数据库名称外，所有值都是可选的。所以最小的DSN是：

/dbname    

不希望预选数据库，请dbname 为空

/

这与空DSN字符串具有相同效果,创建表时可选引擎

    CREATE TABLE  IF NOT EXISTS  `users` ( 
    `name` VARCHAR(40) NOT NULL, 
    PRIMARY KEY (name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

4 如何进行sql优化

优化数据库的方法,选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM.

 使用连接(JOIN)来代替子查询
 适用联合(UNION)来代替手动创建的临时表
 事务处理
 锁定表、优化事务处理
 适用外键，优化锁定表
 建立索引
 优化查询语句

如何进行SQL优化？答：

（1）选择正确的存储引擎

以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。但是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

（2）优化字段的数据类型

记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。

（3）为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

(4)避免使用Select * 从数据库里读出越多的数据，那么查询就会变得越慢。

并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利。

(5)使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

(6)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。

NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。

当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

(7)固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。

例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。

而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。