MySQL数据库知识点

1、事务和锁

事务级别：

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行：我的事务尚未提交，别人就别想改数据。
这4种隔离级别，并行性能依次降低，安全性依次提高。

 

在MySQL的InnoDB中，预设的Tansaction isolation level 为REPEATABLE READ（可重读）

在SELECT 的读取锁定主要分为两种方式：

　　SELECT ... LOCK IN SHARE MODE　

　　SELECT ... FOR UPDATE

　　这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时，都必须等待其它事务数据被提交(Commit)后才会执行。

　　而主要的不同在于LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁。

　　简单的说，如果SELECT 后面若要UPDATE 同一个表单，最好使用SELECT ... UPDATE

MySQL SELECT ... FOR UPDATE 的Row Lock 与Table Lock

上面介绍过SELECT ... FOR UPDATE 的用法，不过锁定(Lock)的数据是判别就得要注意一下了。由于InnoDB 预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

（1）服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

（2）排它锁和共享锁不能作用在同一个记录上。

（3）mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

 

innodb行级锁是通过锁索引记录实现的。如果update的列没建索引，即使只update一条记录也会锁定整张表

2、mysql分页查询

 

一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

• 第一个参数指定第一个返回记录行的偏移量
• 第二个参数指定返回记录行的最大数目
• 如果只给定一个参数：它表示返回最大的记录行数目
• 第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
• 初始记录行的偏移量是 0(而不是 1)

下面是一个应用实例，查询第1001-1010条数据：

select * from orders_history where type=8 limit 1000,10;

这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;

偏移量=行数*（页数-1）

3、读写分离

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

4、binlog，server层日志

Binlog（Binary Log）日志用于记录所有更新了数据或者以及潜在更新了数据（例如，没有匹配任何行的一个DELETE）。它记录了数据库的更改，所以我们可以利用binlog来对误操作的数据进行恢复，也可以用来进行主从数据库的同步，当然也可以用来监听和分发数据变更。

5 redo log ,Innodb引擎特有日志

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板） 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个 操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

6、优化器选错索引

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决，重新计算区分度。 而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过 修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

7、给字符串加索引的几种方式

1. 直接创建完整索引，这样可能比较占用空间；

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

    使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

    使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需 要考虑的一个因素。

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不 支持范围扫描。

8 insert … on duplicate 语句，确保了在事务内部，执行了这个 SQL 语句后，就强行占住了这个行锁

9、对索引使用函数会使查询语句放弃使用索引：如

mysql> select count(*) from tradelog where month(t_modified)=7;

在mysql中，字符串和数字作比较的话，是将字符串转换为数字。如果查询语句中用数字去匹配varchar列，会引发类型转换这种函数操作，导致不命中索引。

如果两个数据表使用的字符集不同，在连表查询时可能会触发字符转换函数操作，导致索引未命中。

10/如果真的要去掉gap lock，可以考虑改用RC隔离级别+binlog_format=row

11、InnoDB的RR下不会产生幻读

老师好，有个问题请教下。文章中说：“可重复读（Repeatable Read）可以避免脏读、不可重复读，但依然存在幻读的问题。”
我之前了解的是，SQL标准不要求RR解决幻读，但InnoDB的 RR下 是不会产生幻读的。
而且自己实验也是InnoDB在RR下没有幻读的现象。操作如下：
sessionA: begin;
sessionA: select * from t where id>1;(2条记录)
sessionB: insert into t(id,a) values(5,5);
sessionA: select * from t where id>1;(还是2条看不到5,5。没有产生幻读)

作者回复: 这是RR下使用的间隙锁，所以不会出现幻读