一: 事物
1. 什么是事物
事务是逻辑上的一组操作，要么都执行，要么都不执行
2. 事物的四大特性(ACID)
A原子性: 事物的最小单位, 不可分割. 事物的原子性确保动作要么全部完成, 要么全部起作用.
C一致性: 执行事物前后, 数据保持一致
I隔离性: 多个事物同时访问数据库时, 其中的一个事物不会被其它事物影响, 各个事物之间的数据库是独立的
D持久性: 一个事物被提交之后, 它对数据库中的数据改变是持久的, 既使数据库发生故障也不会对其有任何影响
3. 如何使用事物
# 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。 # 因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
(1) 开启事物
start transaction
(2) 事物回滚
rollback
(3) 永久性更改
commit
4. 脏读,幻读,不可重复读(并发带来的问题)
(0) 隔离级别
(1) 脏读(读取为提交的数据)
当事务的隔离级别为read uncommitted, 则可以读到其它事务未提交的数据
开启事务, A事务读取B事务没有提交的数据. 此时如果B事务发生错误并执行回滚操作,
那么A事务读取到的数据就是脏数据.
(2)不可重复读(前后多次读取, 数据内容不一致)
当事务隔离级别为read committed，则当前事务只能读到其他事务已提交的数据，不能读到其他事务未提交的数据
事务A在执行读取操作, 由整个事务A比较大, 前后读取同一条数据需要经历很长的时间.
而在事务A第一次读取数据, 小明的年龄为18, 事务B执行更改操作, 将小明的年龄改为20,
此时事务A第二次读取到小明的年龄, 发现年龄是20, 和之前的数据不一样了, 数据不重复了.
系统不可以读取到重复的数据, 成为不可重复读.
(3)幻读(前后多次读取, 数据总量不一致)
REPEATABLE READ
事务A在执行读取操作, 需要两次统计数据的总量, 前一次查询的数据总量后, 此时事务B
执行了新增数据的操作并提交后, 这个时候事务A读取的数据总量和之前统计的不一样,
就像产生了幻觉一样, 平白无故的多了几条数据, 成为幻读
(4) 不可重复读和幻读到底有什么区别?
1. 不可重复读是提取了其它事务更改的数据(针对insert和update操作)
解决: 使用行级锁, 锁定该行, 事务A多次读取操作完成后才释放该锁, 这个时候才允许其它事务更改数据
2. 幻读是读取了其它事务新增的数据(针对insert和delete操作)
通过多版本并发控制解决了幻读问题, mysql默认的事物隔离级别REEATABLE READ
隔离级别由高到低: SERIALIZABLE， REPEATABLE READ， READ COMMITTED和 READ UNCOMMITTED。
(5) 事物隔离级别操作
# 1.查看当前会话隔离级别
select @@tx_isolation;
# 2.查看系统当前隔离级别
select @@global.tx_isolation;
# 3.设置当前会话隔离级别
set session transaction isolatin level repeatable read;
# 4.设置系统当前隔离级别
set global transaction isolation level repeatable read;
二: 索引
0. 表中默认主键为索引, 索引方法使用btree
1. 什么是索引?
索引在mysql中也叫做键, 是存储引擎用于快速找到记录的一种数据结构
键: primary key 主键, unique key非空且唯一, index key索引(加速查找)
注意: foreign key不是用来加速查询的, 不用研究. 上面三个键不且有加速查询的效果还有约束条件(index没有约束条件)
索引是一种数据结构, 类似于书的目录.意味着以后在查询数据应该先找书的目录再找数据, 而不是翻页的方式查询数据.
本质: 通过不断的缩小你想要获取数据的范围来筛选出最终的结果, 同时把随机的事件变成顺序的事件,也就是说, 有了这种索引机制, 我们可以
总是用同一种查找方式来锁定数据
2. 索引能够干什么?
索引能够提高查询速度
3. 索引为什么能够提高查询的速度?
把无序的数据变的有序(相对)
4. b+树
叶子节点: 只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据,叶子结点放的一条条完整的记录
查询次数由树的层级决定，层级越低次数越少
5. 如何设置索引和删除
#方法一：创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); #方法二：CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; #方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; #删除索引：DROP INDEX 索引名 ON 表名字;
#方式一 create table t1( id int, name char, age int, sex enum(‘male‘,‘female‘), unique key uni_id(id), index ix_name(name) #index没有key ); #方式二 create index ix_age on t1(age); #方式三 alter table t1 add index ix_sex(sex); #查看 mysql> show create table t1; | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` enum(‘male‘,‘female‘) DEFAULT NULL, UNIQUE KEY `uni_id` (`id`), KEY `ix_name` (`name`), KEY `ix_age` (`age`), KEY `ix_sex` (`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
6. 查询索引
show index from table_name（表名）
Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。 Key_name 索引的名称。 Seq_in_index 索引中的列序列号，从1开始。 Column_name 列名称 Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。 Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。 Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。 Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。 Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
聚合索引: 由主键建立的索引, 唯一, 叶子节点有真实数据
辅助索引: 非主键建立的索引, 如: name, 通过name找到该记录的id(聚集索引), 然后找到数据
联合索引: 联合索引时指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列
覆盖索引: 即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
7. 索引的影响
- 在表中有大量数据的前提下，创建索引速度会很慢
- 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
三: 慢查询优化
1. 查看mysql慢查询的时间
show VARIABLES LIKE ‘long%‘ # 默认时间为10s, 超过该时间就会被记录
2. 查看慢查询日志开启情况
SHOW VARIABLES LIKE ‘slow%‘
注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，
相关命令为：show variables like ‘%log_output%’；去查看输出的格式
3. 开启慢查询
1种方法): 在配置文件中my.ini, 慢查询日志
[mysqld]
long_query_time = 2 # 慢查询的时间设置为2秒
# 5.0, 5.1版本
log-show-queries="mysql_show_query.log"
# 5.5版本及以上配置
show-query-log = On
show_query_log_file="mysql_show_query.log"
2种方法): 通过mysql数据库命令行开启
4. 分析慢查询日志
# 比如某条数据扫描的次数过多 # 解决方法: 设置索引
5. 常见的慢查询优化
1) 索引没有起作用
(1): 使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的查询语句中, 如果匹配字符串的第一个字符为‘%‘,
索引不会起作用. 只有%不在第一个位置索引才会起作用.
(2) 使用多列索引的查询语句
mysql可以为多个字段创建索引. 一个索引最多包括16个字段. 对于多列索引,
只有查询条件使用了这些字段中的一个字段, 索引才会被使用.
2) 优化数据库结构
(1) 将字段 很多的表分解成多个表
对于字段比较多的表, 如果有些字段的使用频率很低, 可以将这些字段分离出来形成一个新的列表.
因为一个表中的数据量很大的时, 会由于使用频率低的字段存在而变慢.
(2) 增加中间表
对于需要经常联合查询的表, 可以建立中间表以提高查询效率. 通过建立中间表, 把需要经常联合
查询的数据插入到中间表中, 然后将原来的联合查询改为对中间表的查询, 以此来提高查询效率
3) 分解关联查询
将一个大的查询分解为多个小查询是很必要的.
很多高性能的应用都会对关联查询进行分解, 就是可以对每一个表进行查询, 然后将查询的结果子
应用程序中进行关联, 很多场景下这样效率会高.
(6) 查看慢查询日志命令和字义解析
tail -f slow_query.log # Time: 110107 16:22:11 # User@Host: root[root] @ localhost [] # Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774 SET timestamp=1294388531; select count(*) from ep_friends;
字义解析:
第一行,SQL查询执行的时间 第二行,执行SQL查询的连接信息，用户和连接IP 第三行,记录了一些我们比较有用的信息，如下解析: - Query_time,这条SQL执行的时间,越长则越慢 - Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间 - Rows_sent,查询返回的行数 - Rows_examined,查询检查的行数，越长就当然越费时间 第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。 第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。
(7)mysql慢查询日志的分析方法
方法一: 使用mysql程序自带的mysqldumpslow命令分析
mysqldumpslow -s c -t 10 /tmp/slow-log # 这会输出记录次数最多的10条SQL语句，得出的结果和上面一般慢查询记录的格式没什么太大差别，这里就不展开来详细解析了
参数解析:
参数解析：
-s：是表示按照何种方式排序，子参数如下：
c、t、l、r：分别是按照记录次数、时间、查询时间、返回的记录数来排序，
ac、at、al、ar：表示相应的倒叙；
-t：返回前面多少条的数据，这里意思就是返回10条数据了（也可以说是前十）
-g：后边可以写一个正则匹配模式，大小写不敏感的，比如：
/path/mysqldumpslow -s r -t 10 /tmp/slow-log，得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log，得到按照时间排序的前10条里面含有左连接的查询语句。
五: 主从
乐观锁与悲观锁
大表优化
读写分离
主库负责写, 从库负责读
限定数据范围
垂直分区
水平分区
集群,分布式,微服务
https://www.jianshu.com/p/1f9455139a31
mysql的存储引擎