事务、函数和索引

什么是事务？

事务（Transaction），就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内 的所有SQL都将被取消执行。

特点

一个事务中如果有一个数据库操作失败，那么整个事务的所有数据库操作都会失败，数据库数据就会回滚到该事务开始之前的状态。

限制

MySQL数据库中仅InnoDB和BDB类型的数据库表支持事务。

事务的ACID原则

1.Atomic 原子性

意味着数据库中的 事务执行是作为原 子粒度。即不可再 分，整个语句要么 执行，要么不执行 。

2.Consist 一致性

即在事务开始之前和 事务结束以后，数据库的完整性约束没有被破坏。注：过程中的一致性是不保证的

3. Isolated 隔离性

事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。

4.Durable 持久性

意味着在事务完成以后 ，该事务所对数据库所 作的更改便持久的保存 在数据库之中，并不会被回滚。

MySQL实现事务的方法

1.START TRANSACTION

开始一个事务,标记事务的起始点。

2.SET AUTOCOMMIT

 使用该语句来改变自动提交模式，等于0时关闭自动提交模式，等于1时开启自动提交模式。默认为1，使用事务时为0。

3.COMMIT

提交一个事务给数据库。

4.ROLLBACK

将事务回滚，数据回到本次事务的初始状态。

MySQL实现事务的步骤

01 关闭MySQL自动提交                                        SET AUTOCOMMIT =0；

02  开启一个事务，标记事务的起始点                   START TRANSACTION;

03-1 向数据库提交事务                                          COMMIT;

03-2 将事务回滚，所有的数据库操作被取消           ROLLBACK；

04 开启MySQL自动提交                                       SET AUTOCOMMIT =1；

事务的原子性、一致性、持久性

 事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。

• REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。

• UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

事务的隔离性

 1.什么是事务的隔离性：

为了让不同的事务之间相互不存在干扰，就需要对事务的操作进行隔离， 事务的隔离性也就是将操作同一个数据的事务相互分离，让操作之间分开有序的执行。

2.用什么方式实现事务的隔离性:

通常数据库里都是采用锁的机制，保证事务之间的隔离性。

MySql中的锁

锁分类：

基于锁的属性分类：共享锁（读锁、S锁）、排他锁（写锁，X锁）。

基于锁的粒度分类：表锁、行锁(记录锁、间隙锁、临键锁)。

基于锁的状态分类：意向共享锁、意向排它锁。

事务的隔离级别

事务并发问题：

在事务并发执行的时候，如果不进行事务隔离，那么就会产生脏写、脏读、 重复读、幻读的问题。

事务的隔离级别：

 1. READ_UNCOMMITTED         读未提交

2. READ_COMMITTED               读提交（不可重复读）

3. REPEATABLE_READ                可重复读

4. SERIALIZABLE                       串行化

每个隔离级别都针对事务并发问题中的一种或几种进行解决，事务级别越高，解决的 并发事务问题也就越多，同时也意味着加的锁就越多，所以性能也会越差。

事务并发时出现的问题

1.脏写（ Dirty Write ）

对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修 改过 的数据，那就意味着发生了 脏写

2.脏读（ Dirty Read ）

对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交的字段。 之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。 Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name 列更新 为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为' 张三'，而 Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据， 这种现象 就称之为 脏读 。 事务并发时出现的问题

3.不可重复读（ Non-Repeatable Read ）

对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后 Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。 我们在Session B中提交了几个 隐式事务 （注意是隐式事务，意味着语句结束事务就提交了），这些事务 都修改了studentno列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看 到最新的值，这种现象也被称之为 不可重复读 。

 4.幻读（ Phantom ）

对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插 入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。 Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为'张三'的记录； 之后Session B中提交了一个 隐式事务 ，该事务向表student中插入了一条新记录；之后Session A中的事务 再根据相同的条件 studentno > 0查询表student，得到的结果集中包含Session B中的事务新插入的那条记 录，这种现象也被称之为 幻读 。我们把新插入的那些记录称之为 幻影记录 。

InnoDB的MVCC

 MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个 版 本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样 在做查询的时候就不用等待另一个事务释放锁。

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

 InnoDB就是通过MVCC机制解决可重复读中的幻读问题。

什么是函数？

 数学函数 用作常规的数学运算

字符串函数 对于字符串类型的字段处理

日期时间函数 对于日期和时间类型的字段进行处理

聚合函数 常用于GROUP BY从句的 SELECT查询中

自定义函数 根据实际需求自定义函数

MySQL函数，是一种控制流程函数，属于数据库用语言。

常用的日期函数

CURDATE() 返回当前的日期HOUR(time)返回time的小时值(0~23)

CURTIME() 返回当前的时间MINUTE(time)返回time的分钟值(0~59)

NOW()返回当前的日期和时间SECOND(time)返回time的秒(0~59)

DAYOFWEEK(dat e) 返回date所代表的一星期中 的第几天(1~7)

DATE_FORMAT(d ate,fmt) 依照指定的fmt格式格式化 日期date值 DAYOFMONTH( date) 返回date是一个月的第几天 (1~31)

DAYOFYEAR(dat e) 返回date是一年的第几天 (1~366)

WEEK(date)返回日期date为一年中第 几周(0~53)

DAY(date)返回date的日 MONTH(date) 返回date的月份值(1~12)

YEAR(date)返回日期date的年份 (1000~9999)

查询两个日期的时间差

DATEDIFF(expr1,expr2)

-- select datediff(now(), '2020-9-1 15:30:30');

-- select datediff('2020-9-1 15:30:30',now()); TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

-- year / --month / -- DAY

# select TIMESTAMPDIFF(DAY,now(),'2020-9-1 15:30:30')

# select TIMESTAMPDIFF(DAY,'2020-9-1 15:30:30',now())

-- HOUR

# select TIMESTAMPDIFF(HOUR,now(),'2020-9-1 15:30:30')

# select TIMESTAMPDIFF(HOUR,'2020-9-1 15:30:30',now())

-- MINUTE

# select TIMESTAMPDIFF(MINUTE,now(),'2020-9-1 15:30:30')

 # select TIMESTAMPDIFF(MINUTE,'2020-9-1 15:30:30',now())

-- SECOND

 # select TIMESTAMPDIFF(SECOND,now(),'2020-9-1 15:30:30')

# select TIMESTAMPDIFF(SECOND,'2020-9-1 15:30:30',now())

查询距离时间的间隔时间

select NOW(), NOW() + INTERVAL 1 year

select DATE_ADD(NOW(),INTERVAL 1 year)

常用的字符串函数

CONCAT(s1,s2...,s n)     将s1,s2...,sn连接成字符串

CONCAT_WS(sep, s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep 字符间隔 POSITION(subs trin str) 返回子串substr在字符串str中第一次 出现的位置，从1开始 LOWER(str)返回将字符串str中所有字符改变为小 写后的结果

UPPER(str)返回将字符串str中所有字符转变为大写后的结果

常用的数学函数

ABS(x)返回x的绝对值

LOG(x,y)返回x的以y为底的对数

BIN(x)返回x的二进制

MOD(x,y)返回x/y的模（余数）

CEILING(x)返回大于x的最小整数值PI()返回pi的值（圆周率）

 EXP(x) 返回值e（自然对数的底）的x次方

SIGN(x) 返回代表数字x的符号的值

FLOOR(x)返回小于x的最大整数值

ROUND(x,y)返回参数x的四舍五入的有y位小 数的值

GREATEST(x1,x2,. ..,xn)返回集合中最大的值

RAND()返回０到１内的随机值,可以通过 提供一个参数(种子)使RAND()随 机数生成 TRUNCATE(x,y)返回数字x截短为y位小数的结果

常用的聚合函数

AVG(col) 返回指定列的平均值

COUNT(col) 返回指定列中非NULL值的个数

MIN(col) 返回指定列的最小值

MAX(col) 返回指定列的最大值

SUM(col) 返回指定列的所有值之和

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

慢查询

 什么是慢查询？

MySQL默认10秒内没有响应SQL结果,则为慢查询 。可以去修改MySQL慢查询默认时间

 Mysql对慢查询的操作：

//显示到mysql数据库的连接数

 Show status like 'connections';

--查看慢查询的状态

Show variables like '%slow_query%';

--设置慢查询的到表mysql.slow_log

Set global log_output='TABLE';

--设置慢查询的时间

Set global long_query_time=3;

--开启慢查询

Set global slow_query_log='ON';

--慢查询的次数

 Show status like 'slow_queries';

--慢查询记录

Select * From mysql.slow_log;

--慢查询sql语句

Select convert(sql_textusingutf8) sql_textfrommysql.slow_log

--关闭慢查询

Set global slow_query_log='OFF';

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

高效性

利用索引可以提高数据库的查询效率.

完整性

用户可以加速表和表之间的连接， 实现表与表之间的参照完整性

唯一性

索引可以确保所查的数据的唯一性

特殊能力

通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。

索引有它的缺点

 1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。

2.因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘 空间的索引文件。

3. 如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引文件大小会比数据文件还要大。

 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立 最优秀的索引，或优化查询语句。

索引的分类

主键索引

在数据库关系图中为表定义一个主键将自动创建主键索引。

常规索引

最基本的索引类型，没有唯一性之类的限制。

唯一索引

不允许具有索引值相同的行，从而禁止重复的索引或键值。

全文索引

搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。

全文索引的注意：

 1.Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。

2.只有char、varchar、text类型字段能创建全文索引。

3.当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率。

4.Mysql内置ngram 解析器，可以解析中日韩三国文字。有汉字的一定要启用它。

5.英文分词用空格，逗号；中文分词用 ngram_token_size 设定.

索引的管理

查看索引

SHOW INDEX(或KEYS) FROM 表名;

删除索引

ALTER TABLE 表名 DROP PRIMARYKEY;

 DROP INDEX 索引名 ON 表名;

 ALTER TABLE 表名 DROP INDEX 索引名;

修改索引

ALERT TABLE 表名 ADD 索引类型（数据列名）;

ALTER TABLE add FULLTEXT INDEX (字段名1，字段2，，) [ WITH PARSER ngram];

优化原则—创建索引

 1.【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值 禁止被更新 。

 2.【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。

 3.【建议】主键的名称以 pk 开头，唯一键以 uni 或 uk 开头，普通索引以 idx 开头，一律 使用小写格式，以字段的名称或缩写作为后缀。

4.【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。

 5.【建议】单个表上的索引个数 不能超过6个 。

 6.【建议】在建立索引时，多考虑建立 联合索引 ，并把区分度最高的字段放在最前面。

7.【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。

8.【建议】建表或加索引时，保证表里互相不存在 冗余索引 。 比如：如果表里已经存在key(a,b)， 则key(a)为冗余索引，需要删除。

优化原则—SQL编写

 1.【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。

 2.【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。 3.【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。

 4.【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很 快，但会引起主从同步延迟。

5.【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以 内。

 6.【建议】线上环境，多表 JOIN 不要超过5个表。

 7.【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

8.【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果 集请保持在1000行以内，否则SQL会很慢。

 9.【建议】对单表的多次alter操作必须合并为一次 对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整 合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极 大影响。

10.【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。

11.【建议】事务里包含SQL不超过5个。 因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。

12.【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。