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.html
https://www.cnblogs.com/huanongying/p/7021555.html
https://www.cnblogs.com/loveyouyou616/p/7697737.html

《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!