Mysql

事务

事务四大特征

原子性: 不可分割的最小操作单位、要么同事成功、要么同时失败
持久性: 当事务提交或回滚后, 数据都会被持久化到硬盘保存
一致性: 事务前后,数据总量不变
隔离性: 多个事务相互隔离

事务隔离出现的问题

概念: 多个事务处理同一批数据, 则会出现一些问题、设置不同的隔离级别就可以解决

赃读: 即为事务1第二次读取时，读到了事务2未提交的数据。若事务2回滚，则事务1第二次读取时，读到了脏数据。
不可重复读：事务一没结束的情况下第二次查询某条数据时，读到了事务二提交的修改的该数据, (同一个事物中多次查询数据不一致的情况)
幻读: 事务2在事务1第二次读取时，提交了数据。导致事务1前后两次读取的数据量不一致

隔离级别分类

read uncommitted: 读未提交
read committed: 读已提交 (oracle 默认级别)
repeatable read : 可重复读 (mysql 默认级别)
serializable: 串行化

注意: 隔离级别从小到大, 意味着安全性越来越高,但效率越来越低

-- 查询数据库事务级别
SELECT @@tx_isolation;
-- 修改数据库事务级别
set global transaction isolation level 级别字符串
-- 设置了隔离级别后要重新启动数据库工具(不用重启动数据库服务)  就可以生效了

mysql优化

查询优化

前面的查询流程分析，我们大概了解了MySQL是如何执行的，其中涉及到的部分我们在后面会一一道来。现在我们先从查询优化部分开始。

sql是我们和数据库交流最重要的部分，所以我们在调优的时候，需要花费的大量时间就在sql调优上面。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

EXPLAIN

EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮助开发人员写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了：

EXPLAIN SELECT * FROM products

结果的列的说明如下：

1) id

SELECT识别符。这是SELECT查询序列号。这个不重要

2) select_type

表示SELECT语句的类型。

simple:简单select（不使用union或子查询）。
primary:最外面的select。
union:union中的第二个或后面的select语句。
dependent union:union中的第二个或后面的select语句，取决于外面的查询。
union result:union的结果。
subquery:子查询中的第一个select。
dependent subquery:子查询中的第一个select，取决于外面的查询。
derived:导出表的select（from子句的子查询）。

3) table

显示这查询的数据是关于哪张表的。

4) type

区间索引，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别,最好能达到ref。

system：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：数据表最多只有一个匹配行，因为只匹配一行数据，所以很快
eq_ref：mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。
ref：查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range：只检索给定范围的行,使用一个索引来选择行。
index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）

5) possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

6) key

实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。

7) key_len

最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。

8) ref

显示使用哪个列或常数与key一起从表中选择行。

9) rows

显示MySQL认为它执行查询时必须检查的行数。

10) Extra

执行状态说明，该列包含MySQL解决查询的详细信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

索引

索引类型

主键索引 PRIMARY KEY
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
```
PRIMARY KEY (`id`)
```

唯一索引 UNIQUE
唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。
```
UNIQUE KEY `num` (`number`) USING BTREE
```

普通索引 INDEX
这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构
```
KEY `num` (`number`) USING BTREE
```

组合索引 INDEX
索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
```
KEY `num` (`number`,`name`) USING BTREE
```
注意，组合索引前面索引必须要先使用，后面的索引才能使用。

全文索引 FULLTEXT
全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

索引的存储结构

BTree索引

在前面的例子中我们看见有USING BTREE，这个是什么呢？这个就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。

特点：

BTREE索引以B+树的结构存储数据
BTREE索引能够加快数据的查询速度
BTREE索引更适合进行行范围查找

使用的场景：

全值匹配的查询，例如根据订单号查询 order_sn='98764322119900'
联合索引时会遵循最左前缀匹配的原则,即最左优先
匹配列前缀查询，例如：order_sn like '9876%'
匹配范围值的查找，例如：order_sn > '98764322119900'
只访问索引的查询

哈希索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

特点：

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；
Hash索引在任何时候都不能避免表扫描；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

Full-text全文索引

Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

注意：

对于较大的数据集，把数据添加到一个没有Full-text索引的表，然后添加Full-text索引的速度比把数据添加到一个已经有Full-text索引的表快。
针对较大的数据，生成全文索引非常的消耗时间和空间。
5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本和之后InnoDB存储引擎开始支持全文索引。
在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

索引的使用

虽然索引能够为查找带来速度上的提升，但是也会对性能有一些损失。

索引会增加写操作的成本
太多的索引会增加查询优化器的选择时间

当创建索引带来的好处多过于消耗的时候，才是最优的选择~

使用索引的场景

主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向建立组合索引；
用于聚合函数的列可以建立索引，例如使用count(number)时，number列就要建立索引

不使用索引的场景

有大量重复的列不单独建立索引
表记录太少不要建立索引，因为没有太大作用。
不会作为查询的列不要建立索引

资料：MySQL 5.7 中文全文检索使用教程

文章目录

Mysql
- 事务
- mysql优化
  - 查询优化
    - EXPLAIN
  - 索引
    - 索引类型
    - 索引的使用

MySQL事务、索引