https://www.cnblogs.com/xzlive/p/9884674.html

MySQL 5.5 -- innodb_lock_wait 锁 等待

记得以前，当出现：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，
要解决是一件麻烦的事情 ；
特别是当一个SQL执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束；
DBA光从数据库无法着手找出源头是哪个SQL锁住了；
有时候看看show engine innodb status , 并结合 show full processlist; 能暂时解决问题；但一直不能精确定位；

在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）；
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

看到这个就非常激动 ； 这可是解决了一个大麻烦，先来看一下表结构

https://blog.csdn.net/mangmang2012/article/details/9207007

1、不管autocommit 是1还是0 
     START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。

2、当autocommit 为 0 时
    不管有没有START TRANSACTION。
    只有当commit数据才会生效，ROLLBACK后就会回滚。

3、如果autocommit 为1 ，并且没有START TRANSACTION 。
    调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

问题的提出

我们知道，在单机的“线程模型“中，2个线程并发修改一个变量，是需要加锁的。这个在Java并发编程–序列1已经讲过，要么是悲观锁，要么是乐观锁。 

如果把单机的线程模型，改成有客户端/服务器的进程模型。服务器可以是Mysql/Redis/Memcached任何一种，那该问题又如何解决呢？

方案1 – 单条命令的原子性

Mysql: 用类似update table set x = x + 1 where … 这样的单条语句就可解决上述问题，因为服务器内部会处理加锁的问题，不用客户端解决。

Memcached: incr/decr命令

Redis: incr/decr命令

一句话：对于这种简单的整数加减的原子操作，只要是1条命令可以搞定，就不需要客户端解决互斥问题。

方案2 – Memcached/Redis的乐观锁

上面的方案1，必须是单条命令，但该方法有很大局限性。很多时候，如果我们需要执行复杂的计算逻辑，要先把数据get出来，执行复杂逻辑，再set回去。类似下面这种：

//客户端1 x = get(key) 对x执行复杂逻辑 set(key,x)  //客户端2 x = get(key) 对x执行复杂逻辑 set(key,x)
1
2
3
4
5
6
7
8
9

此时有2条指令，没有办法保证2条语句的原子性，这个时候如何解决呢？

Mysql的乐观锁

关于Mysql解决上述问题的乐观锁方案，此处不再详述，参见Java并发编程-序列1

Memcached乐观锁

Memcached提供了2个命令 gets + cas。gets取出数据的时候，同时返回版本号；修改之后, cas回去的时候，会比较该版本号和服务器上最新的版本号。如果不等，则cas失败。

Redis乐观锁

Redis提供了watch命令，如下所示：

    watch  key1   //修改数据之前，执行watch，意思监听此key      multi     set key1  foo  //如果别的客户端在此期间修改了该key1，此处更新将失败     exec  
1
2
3
4
5
6

方案3 – Redis事务

Redis也提供了事务的概念，但它不能回滚。如果1条命令执行错误，会继续执行下面的。

      multi        get foo        ...        incr foo        exec
1
2
3
4
5
6
7
8
9

此处的multi/extc，类似Mysql中的beganTransaction/endTransaction。 
另外，由于redis是单线程的，因此事务里面的多条语句执行时，不会被打断。

Memcached的多线程 vs. Redis单线程

我们都知道, Memcached内部是多线程的，而Redis是单线程的。多线程好理解，但Redis为什么要搞成单线程呢？ 
个人认为，有以下几个原因： 
（1）redis有各种复杂的数据结构list, has, set。也就是说，对于一个(key, value)，value的类型可以是list, hash, set。在实际应用场景中，很容易出现多个客户端对同一个key的这个复杂的value数据结构进行并发操作，如果是多线程，势必要引入锁，而锁却是性能杀手。 
相比较而言，memcached只有简单的get/set/add操作，没有复杂数据结构，在互斥这个问题上，没有redis那么严重。

（2）对于纯内存操作来说，cpu并不是瓶颈，瓶颈在网络IO上。所以即使单线程，也很快。另外，如果要利用多核的优势，可以在一个机器上开多个redis实例。

       如上图所示，如果更新操作顺序执行，则数据的版本（version）依次递增，不会产生冲突。但是如果发生有不同的业务操作对同一版本的数据进行修改，那么，先提交的操作（图中B）会把数据version更新为2，当A在B之后提交更新时发现数据的version已经被修改了，那么A的更新操作会失败。
       2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。
       以mysql InnoDB存储引擎为例，还是拿之前的例子商品表items表中有一个字段status，status=1表示该商品未被下单，status=2表示该商品已经被下单，那么我们对每个商品下单前必须确保此商品的status=1。假设有一件商品，其id为10000；
       下单操作包括3步骤：
       //查询出商品信息
       select (status,version) from items where id=#{id}
       //根据商品信息生成订单
       //修改商品status为2
       update items set status=2,version=version+1 where id=#{id} and version=#{version};
       为了使用乐观锁，我们需要首先修改items表，增加一个version字段，数据默认version可设为1；
       其实我们周围的很多产品都有乐观锁的使用，比如我们经常使用的分布式存储引擎XXX，XXX中存储的每个数据都有版本号，版本号在每次更新后都会递增，相应的，在XXX put接口中也有此version参数，这个参数是为了解决并发更新同一个数据而设置的，这其实就是乐观锁；
       很多情况下，更新数据是先get，修改get回来的数据，然后put回系统。如果有多个客户端get到同一份数据，都对其修改并保存，那么先保存的修改就会被后到达的修改覆盖，从而导致数据一致性问题,在大部分情况下应用能够接受，但在少量特殊情况下，这个是我们不希望发生的。
       比如系统中有一个值”1”, 现在A和B客户端同时都取到了这个值。之后A和B客户端都想改动这个值，假设A要改成12，B要改成13，如果不加控制的话，无论A和B谁先更新成功，它的更新都会被后到的更新覆盖。XXX引入的乐观锁机制避免了这样的问题。刚刚的例子中，假设A和B同时取到数据，当时版本号是10，A先更新，更新成功后，值为12，版本为11。当B更新的时候，由于其基于的版本号是10，此时服务器会拒绝更新，返回version error，从而避免A的更新被覆盖。B可以选择get新版本的value，然后在其基础上修改，也可以选择强行更新。

先来就库存超卖的问题作描述：一般电子商务网站都会遇到如团购、秒杀、特价之类的活动，而这样的活动有一个共同的特点就是访问量激增、上千甚至上万人抢购一个商品。然而，作为活动商品，库存肯定是很有限的，如何控制库存不让出现超买，以防止造成不必要的损失是众多电子商务网站程序员头疼的问题，这同时也是最基本的问题。

从技术方面剖析，很多人肯定会想到事务，但是事务是控制库存超卖的必要条件，但不是充分必要条件。

举例：

总库存：4个商品

请求人：a、1个商品 b、2个商品 c、3个商品

程序如下：

beginTranse(开启事务)

try{

    $result = $dbca->query('select amount from s_store where postID = 12345');

    if(result->amount > 0){

        //quantity为请求减掉的库存数量

        $dbca->query('update s_store set amount = amount - quantity where postID = 12345');

    }

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

以上代码就是我们平时控制库存写的代码了，大多数人都会这么写，看似问题不大，其实隐藏着巨大的漏洞。数据库的访问其实就是对磁盘文件的访问，数据库中的表其实就是保存在磁盘上的一个个文件，甚至一个文件包含了多张表。例如由于高并发，当前有三个用户a、b、c三个用户进入到了这个事务中，这个时候会产生一个共享锁，所以在select的时候，这三个用户查到的库存数量都是4个，同时还要注意，mysql innodb查到的结果是有版本控制的，再其他用户更新没有commit之前(也就是没有产生新版本之前)，当前用户查到的结果依然是就版本；

然后是update，假如这三个用户同时到达update这里，这个时候update更新语句会把并发串行化，也就是给同时到达这里的是三个用户排个序，一个一个执行，并生成排他锁，在当前这个update语句commit之前，其他用户等待执行，commit后，生成新的版本；这样执行完后，库存肯定为负数了。但是根据以上描述，我们修改一下代码就不会出现超买现象了，代码如下：

beginTranse(开启事务)

try{

    //quantity为请求减掉的库存数量
    $dbca->query('update s_store set amount = amount - quantity where postID = 12345');

    $result = $dbca->query('select amount from s_store where postID = 12345');

    if(result->amount < 0){

       throw new Exception('库存不足');

    }

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

另外，更简洁的方法：

beginTranse(开启事务)

try{

    //quantity为请求减掉的库存数量
    $dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

=====================================================================================

1、在秒杀的情况下，肯定不能如此高频率的去读写数据库，会严重造成性能问题的
必须使用缓存，将需要秒杀的商品放入缓存中，并使用锁来处理其并发情况。当接到用户秒杀提交订单的情况下，先将商品数量递减（加锁/解锁）后再进行其他方面的处理，处理失败在将数据递增1（加锁/解锁），否则表示交易成功。
当商品数量递减到0时，表示商品秒杀完毕，拒绝其他用户的请求。

2、这个肯定不能直接操作数据库的，会挂的。直接读库写库对数据库压力太大，要用缓存。
把你要卖出的商品比如10个商品放到缓存中；然后在memcache里设置一个计数器来记录请求数，这个请求书你可以以你要秒杀卖出的商品数为基数，比如你想卖出10个商品，只允许100个请求进来。那当计数器达到100的时候，后面进来的就显示秒杀结束，这样可以减轻你的服务器的压力。然后根据这100个请求，先付款的先得后付款的提示商品以秒杀完。

3、首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。

这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。
乐观锁，就是在数据库设计一个版本号的字段，每次修改都使其+1，这样在提交时比对提交前的版本号就知道是不是并发提交了，但是有个缺点就是只能是应用中控制，如果有跨应用修改同一条数据乐观锁就没办法了，这个时候可以考虑悲观锁。
悲观锁，就是直接在数据库层面将数据锁死，类似于oralce中使用select xxxxx from xxxx where xx=xx for update，这样其他线程将无法提交数据。
除了加锁的方式也可以使用接收锁定的方式，思路是在数据库中设计一个状态标识位，用户在对数据进行修改前，将状态标识位标识为正在编辑的状态，这样其他用户要编辑此条记录时系统将发现有其他用户正在编辑，则拒绝其编辑的请求，类似于你在操作系统中某文件正在执行，然后你要修改该文件时，系统会提醒你该文件不可编辑或删除。

4、不建议在数据库层面加锁，建议通过服务端的内存锁（锁主键）。当某个用户要修改某个id的数据时，把要修改的id存入memcache，若其他用户触发修改此id的数据时，读到memcache有这个id的值时，就阻止那个用户修改。

5、实际应用中，并不是让mysql去直面大并发读写，会借助“外力”，比如缓存、利用主从库实现读写分离、分表、使用队列写入等方法来降低并发读写。

3.txt为header,firefox header,修改 header

首先，我们需要查看mysql server版本号，以及是否配置启用了slow query log。

#打开服务
log_slow_queries = ON

当log_slow_queries是ON时，才表示已经启用了记录slow query功能。默认是不记录slow query的。
启用slow query日志
#//将下列配置放到my.cnf中
[mysqld]
log-slow-queries = /var/lib/mysql/slow-queries.log

log-slow-queries = /data/mysqldata/slowquery.log    #日志目录 long_query_time = 1                          #记录下查询时间查过1秒 log-queries-not-using-indexes     #表示记录下没有使用索引的查询

分析日志 – mysqldumpslow
分析日志，可用mysql提供的mysqldumpslow，使用很简单，参数可–help查看

# -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序； #                             ac , at , al , ar 表示相应的倒叙； # -t：返回前面多少条的数据； # -g：包含什么，大小写不敏感的； mysqldumpslow -s r -t 10  /slowquery.log     #slow记录最多的10个语句 mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log     #按照时间排序前10中含有"left join"的

推荐用分析日志工具 – mysqlsla

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz tar zvxf mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl Makefile.PL make make install mysqlsla /data/mysqldata/slow.log # mysqlsla会自动判断日志类型，为了方便可以建立一个配置文件“~/.mysqlsla” # 在文件里写上：top=100，这样会打印出前100条结果。

【说明】 queries total: 总查询次数 unique:去重后的sql数量 sorted by : 输出报表的内容排序 最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数. Count, sql的执行次数及占总的slow log数量的百分比. Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比. 95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间. Lock Time, 等待锁的时间. 95% of Lock , 95%的慢sql等待锁时间. Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量. Rows examined, 扫描的行数量. Database, 属于哪个数据库 Users, 哪个用户,IP, 占到所有用户执行的sql百分比 Query abstract, 抽象后的sql语句 Query sample, sql语句

innodb_additional_pool_size 
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。

innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。

innodb_log_buffer_size 
默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit  （这个很管用） 
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

myisamchk 修复表后报的错误
2，命令行下repair修复表
查看复制打印?
mysql> repair table dxad;  //dxad是表名 
如下图

命令行下repair修复表成功
到这儿，myisam表损坏就修复好了。

　　innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
    加上 default-storage-engine=innodb　
加上这段之后，以后新增的数据表型态都即是 InnoDB，不然每次新增一次数据表，SQL 后面得加上 ENGINE=InnoDB;

6 .将刚刚备份出来的sql，将ENGINE=MyISAM改成ENGINE=InnoDB。
7. /usr/local/mysql/bin/safe_mysqld --user=mysql &  ，或service mysql start 启动数据库
8. 建立一个新的数据库(数据库名称跟备份出来的数据库名称一样)。
9. mysql -u[user] -p[password] [database_name] < [dbfile] # 将改好的数据汇入数据库中！

说明：
* 设定文件的选择是参照内存大小来选择。
my-huge.cnf - 1G~2G 、my-large.cnf - 512M 、 my-medium.cnf - 32M - 64M 、my-small.cnf <= 64M 。
InnoDB：my-innodb-heavy-4G.cnf
* 假如不会将备份出来的数据库改型态，那么您可以用下面这个指令，直接改变数据表的型态。

　ALTER TABLE [tablename] ENGINE=InnoDB 如有存放全文索引功能的话，转换会失败的。

* 如你有一批数据表要改，可以用下面的指令：
 mysql_convert_table_format [opt] -- ENGINE=InnoDB dbname [tablename]
但千万注意不要改变 mysql 数据库的数据型态，因为 mysql数据库存放的是 MySQL 内部的管理信息，所以必须保持 MyISAM 的格式。

* 加大 tablespace 空间：
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend:max2G

　上面的意思是，tablespace 包含 ibdata1 & ibdata2 两个文件，若文件不存在，则建立容量各为1G的文件。一旦未来 InnoDB 需要，更多的空间，则 ibdata2 将每次自动增加 8MB，直到2G为止。

　* MySQL 3.23.n，innodb_data_home & innodb_data_file_path设定是必须要有的，MySQL 4.0.0 之后的版本则是非必须的。

一，用alter table来实现。
mysql> alter table merchant type=innodb;

ALTER TABLE  `block_view` ENGINE = INNODB;

二，导出数据，重新建表
1，导出表结构以及数据，删除老表，重新建表
查看复制打印?
//方法1，我们可以把把创建这个表的sql拷贝出来，把ENGINE=MyISAM换成，ENGINE=INNODB 
mysql> show create table merchant\G; 
*************************** 1. row *************************** 
 Table: merchant 
Create Table: CREATE TABLE `merchant` ( 
 `btid` int(5) unsigned zerofill NOT NULL auto_increment, 
 `btname` varchar(100) NOT NULL, 
 `btnote` text, 
 PRIMARY KEY  (`btid`), 
 UNIQUE KEY `btname` (`btname`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 
 
//方法2，直接导出表结构，在文件里面替换一个myisam 
mysqldump -udba -p -d test merchant >/tmp/test/mytest/merchant.sql; 
2，导出数据
mysqldump -udba -p --no-create-info test merchant >/tmp/test/mytest/merchant_data.sql; 
3，删除老表，重新建表，导入数据，这个就不说了，linux 简单的mysql备份和导入，以及文件的备份和导入

--------

要把ib_log*的文件删除,再启动就正常了

Solution? TMPFS!

tmpfs is a filesystem, that resides in RAM/Swap, so if your server has enough available RAM, files written there will bypass disk I/O completely, and will perform significantly faster.

Now, “High Performance MySQL, Second Edition” claims that this solution is still not as good as a MEMORY table, since it requires MySQL to use some expensive OS calls to write & read the temporary table, but it is still faster than the disk based temporary table.

To set it up, just mount a tmpfs system on an empty directory (you should also add this to fstab):
mount tmpfs /tmpfs -t tmpfs
and edit my.cnf to make MySQL use that directory as a temporary directory:
tmpdir = /tmpfs
Be careful though, there is a bug in some versions that prevents this from working properly.

For more information, see this blog.

建立索引时，数据库计算key的长度是累加所有Index用到的字段的char长度后再按下面比例乘起来不能超过限定的key长度1000：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
举例能看得更明白些，以GBK为例：
CREATE UNIQUE INDEX `unique_record` ON reports (`report_name`, `report_client`, `report_city`);
其中report_name varchar(200), report_client varchar(200), report_city varchar(200)
(200 + 200 +200) * 2 = 1200 > 1000，所有就会报1071错误，只要将report_city改为varchar(100)那么索引就能成功建立。
如果表是UTF8字符集，那索引还是建立不了。