MySQL 部分整理

mysql部分资料整理

1.事务

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

2.事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

3.事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	是	是
串行读（serializable）	否	否	否

mysql默认的事务隔离级别为可重复读（repeatable-read）

4.mysql乐观锁与悲观锁

4.1.1 悲观锁

悲观锁：，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。
注：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

4.1.2 Row Lock与Table Lock（行锁与表锁）

MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

4.1.3 悲观锁的缺点

悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受

4.2.1 乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

4.2.2 乐观锁的实现方法

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

1.查询出商品信息

select (status,status,version) from t_goods where id=#{id}

2.根据商品信息生成订单

3.修改商品status为2

update t_goods 

set status=2,version=version+1

where id=#{id} and version=#{version};

5 索引

5.1 索引类型

1.普通索引 : 最基本的索引，它没有任何限制

    直接创建索引
    CREATE INDEX index_name ON table(column(length))

    –修改表结构的方式添加索引
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))

    –创建表的时候同时创建索引
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,

        `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

        `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

        `time` int(10) NULL DEFAULT NULL ,

        PRIMARY KEY (`id`),

        INDEX index_name (title(length))
    )

    –删除索引
    DROP INDEX index_name ON table

2.唯一索引 : 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

     –创建唯一索引
    CREATE UNIQUE INDEX indexName ON table(column(length))

    –修改表结构
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

    –创建表的时候直接指定
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,

        `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

        `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

        `time` int(10) NULL DEFAULT NULL ,

         PRIMARY KEY (`id`),

         UNIQUE indexName (title(length))

    );

3 全文索引（FULLTEXT）:仅可用于 MyISAM 表 , (CHAR、VARCHAR或TEXT列)

–创建表的适合添加全文索引
    CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    FULLTEXT (content)

    );

    –修改表结构添加全文索引
    ALTER TABLE article ADD FULLTEXT index_content(content)

    –直接创建索引
    CREATE FULLTEXT INDEX index_content ON article(content)

4 联合索引（最左前缀原则）

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time

–title

5 主键索引 ： 不可以为空 ， 唯一

5.1.2 索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

5.2 索引优化建议

1 索引不会包含有NULL值的列 : 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2 使用短索引 ： 短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。

3. 索引列排序：MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4. like语句操作 ： like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5. 不要在列上进行运算 : 例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

5.1聚簇索引和非聚簇索引

非聚簇索引 (myisam) :主键索引与 二级索引 ,叶子页面上存储着 数据存储的地址信息,每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据

聚簇索引(innodb) : 主键索引的叶子页面上存储的所有数据 ， 二级索引叶子页面上存储着主键id，只需要通过主键id去主键索引上面取数据 ， 不需要回行取数据

这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快！

关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号） 以及回滚指针。

聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MyISAM的 索引文件.MYI 和 数据文件.MYD 是分开存储的 是相对独立的

InnoDB的数据文件只有 数据结构文件.frm 和 数据文件.idb 其中.idb中存放的是数据和索引信息 是存放在一起的

部分概念：

回行：通过叶子上的磁盘地址去磁盘内取数据

参考文章：

http://www.cnblogs.com/crazylqy/p/7615388....
https://www.cnblogs.com/huanongying/p/7021...
https://www.cnblogs.com/loveyouyou616/p/76...

本作品采用《CC 协议》，转载必须注明作者和本文链接