Mysql 数据库系列

Mysql 中 MyISAM 和 InnoDB 的区别

1. InnoDB所有的表都保存在同一个数据文件中。 MyISAM每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件；
2. InnoDB 支持外键，而 MyISAM 不支持。
3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
4. InnoDB 支持事务，MyISAM 不支持事务。
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

全文索引
全文索引是将存储在数据库中的大段文本中的任意内容信息查找出来的技术
MySQL全文检索只支持英文，因为英文使用空格来作为分词的分隔符，对中文而言，空格分词不合适。从MySQL 5.7开始，内置了ngram全文检索插件，用来支持中文分词。

全文索引的版本、存储引擎、数据类型的支持情况

1. MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
2. MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
3. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

//创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名 (column1, column2)

自然语言的全文索引
自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

SELECT * FROM 表名 WHERE Match(列名1,列名2) Against (检索内容1 检索内容2);

注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

布尔全文索引
在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

• + 必须包含该词
• -必须不包含该词
• > 提高该词的相关性，查询的结果靠前
• <降低该词的相关性，查询的结果靠后
• (*)星号 通配符，只能接在词后面

select * test where match(content) against('检索内容*' in boolean mode);

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。想对一个词语使用全文索引搜索，则这个词语的长度必须在两个变量之间。

show variables like ‘%ft%’;

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

Mysql 锁机制

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，为了保证数据的一致性，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要机制。

表锁
表锁是MySQL各存储引擎中最大颗粒度的锁定机制，由于表锁一次性会将整个表锁定，所以可以避免死锁问题。

表锁最大的特点就是实现逻辑非常简单，带来的系统负面影响最小，所以获取锁和释放锁的速度很快。当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率会很高，致使并发度大打折扣。

行锁
行锁是MySQL各存储引擎中最小颗粒度的锁定机制，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够尽可能大的处理并发问题，从而提高系统的整体性能。

由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也就更多，带来的消耗自然也就更大。而且行锁最容易发生死锁。

页锁
页锁是MySQL中比较独特的一种锁定级别，特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也是介于二者之间。页级锁定也会发生死锁。

锁定级别-共享锁
共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。

如果事务1对数据加上读锁后，则其他事务只能对数据上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。

锁定级别-排他锁
排他锁又称写锁，如果事务1对数据加上写锁后，则其他事务不能再对数据加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。

悲观锁
悲观锁（Pessimistic Lock）：就是很悲观，每次去取数据的时候都认为别人会去修改，所以每次在取数据的时候都会给它上锁。简单来讲，悲观锁就是假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。悲观并发控制实际上是 “先取锁，再访问” 的保守策略，为数据处理的安全提供了保证。比如用在库存增减问题上，利用悲观锁可以有效的防止减库存问题。

乐观锁
乐观锁（Optimistic Lock）：就是很乐观，每次去获取数据时，都认为其他人不会修改它，因此不会锁定，但是在提交更新时会判断在此期间其他人是否有去更新此数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。也就是说，乐观锁就是假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

悲观锁与乐观锁的区别
1 优缺点
两种锁各有优缺点，比如像乐观锁，适用于写比较少的情况下，冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

2 实现方式
悲观锁的实现方式：悲观锁的实现，依靠数据库提供的锁机制。

1. 在对数据修改前，尝试增加排他锁。
2. 加锁失败，意味着数据正在被修改，进行等待或者抛出异常。
3. 加锁成功，对数据进行修改，提交事务，锁释放。
4. 如果我们加锁成功，有其他线程对该数据进行操作或者加排他锁的操作，只能等待或者抛出异常。

乐观锁的实现方式:
version方式：一般是在数据表中加上一个数据版本号version字段

update table set n=n+1, version=version+1 where id=#{id} and version=#{version};