事务、函数和索引

M y S Q L 事 务

什么是事务？

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

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

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

事务的ACID原则

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

一致性（Consist ）： 即在事务开始之前和 事务结束以后，数据 库的完整性约束没有 被破坏。

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

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

MySQL实现事务的方法

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

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

COMMIT         提交一个事务给数据库。

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

MySQL实现事务的步骤

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

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

向数据库提交事务                                              COMMIT;

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

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

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

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

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

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

事务的隔离性

什么是事务的隔离性：

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

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

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

MySql中的锁

锁分类：

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

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

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

事务的隔离级别

事务并发问题：

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

事务的隔离级别：

        1. READ_UNCOMMITTED  读未提交

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

        3. REPEATABLE_READ     可重复读

        4. SERIALIZABLE               串行化

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

事务的隔离级别解决的问题

1.READ UNCOMMITTED
事务读取：不加锁
事务写入：加写锁
解决问题：脏写
存在问题：脏读，不可重复读、幻读。
2.READ COMMITTED
事务读取：加读锁（每次select完成都会释放读锁）
事务写入：加写锁
解决问题：脏写、脏读
存在问题：不可重复读、幻读。
3.REPEATABLE READ
事务读取：加读锁（每次select完不会释放锁，而是事务结束后才释放）（如果是MysqI
的innodb还会加间隙锁)。
事务写入：加写锁
解决问题：脏写、脏读、不可重复读，幻读（如果是Mysqli的innodb则已解决）
存在问题：幻读（如果是Mysql的innodb则不存在）
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机制解决可重复读中的幻读问题。

# 事务
-- 事务的四大特性


create table bank(
	bid int primary key auto_increment,
	bname varchar(10),
	bmoney decimal(20,2)
);
insert into bank(bname,bmoney) values('杨文琦',5);
insert into bank(bname,bmoney) values('周永康',100000000000000);

select * from bank;

update bank set bmoney = 100000000000000 where bname='周永康';
update bank set bmoney = 5 where bname='杨文琦';


-- 事务的操作
-- 1. 关闭自动提交
set autocommit = 0;
-- 2. 开始事务
start transaction;
-- 3. 一组sql语句
update bank set bmoney = bmoney-1000 where bname='周永康';
update bank set bmoney = bmoney+1000 where bname='杨文齐';

-- 4. 结束事务(判断)
	-- 提交
	commit;
	-- 回滚
	rollback;
-- 5. 开启自动提交
set autocommit = 1;

M y S Q L 函 数

什么是函数？

常用的日期函数

查询两个日期的时间差

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())

查询距离时间的间隔时间

A.T_DATE = B.T_DATE+ interval 1 hour——即 A表时间 = B表时间 + 1小时，也可以为分钟、秒。 或者

DATE_ADD(date,INTERVAL expr unit)

DATE_SUB(date,INTERVAL expr unit)

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

select DATE_ADD(NOW(),INTERVAL 1 year)

常用的字符串函数

常用的数学函数

常用的聚合函数

 

# 函数
-- 日期
select curdate()
select curtime()

select now()

select month(now())

select * from student where month(birthday) = month(now())

select date_format(now(),"%a - %b - %M")

-- 天的差
select datediff(now(), '2024-6-24')
select datediff('2024-6-24',now())


select timestampdiff(year,'1976-1-1',now())


select now() - interval 5 year

select DATE_ADD(now(),INTERVAL 10 year)

select DATE_SUB(now(),INTERVAL 10 year)


select concat('hello','world')

select concat_ws('#','hello','world')

select floor(3.9999)
select ROUND(3.555,2)

select truncate(3.999,2)

select ssex,group_concat(sname) from student group by ssex

慢 查 询

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

 Mysql对慢查询的操作：

执行计划

问题：

当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢？

答案是能的，mysql提供了相应的功能和语法来实现该功能。

分析：

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。

比如我们要分析如下SQL语句：

explain select * from table where table.id = 1

运行上面的sql语句后你会看到，下面的表头信息：

table | type | possible_keys | key | key_len | ref | rows | Extra

EXPLAIN列的解释：

table

显示这一行的数据是关于哪张表的

type

这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL

说明：不同连接类型的解释（按照效率高低的顺序排序）

system：表只有一行：system表。这是const连接类型的特殊情况。

const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为 MYSQL先读这个值然后把它当做常数来对待。

eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键 或惟一键的全部时使用。

ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的 表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。

range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。

index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。

ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

 possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择 一个合适的语句

key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。 这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用 IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

rows

MYSQL认为必须检查的用来返回请求数据的行数

Extra

关于MYSQL如何解析查询的额外信息。是Using temporary和Using filesort，意思MYSQL根本不能使用索 引，结果是检索会很慢

说明：extra列返回的描述的意义

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。 Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一 个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的 行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。 Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候。

Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果， 这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表 中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。 

# 索引
-- 慢查询
show status like  'connections'; 

Show variables like '%slow_query%';

set global log_output='TABLE';

set global slow_query_log='ON';


show status like 'slow_queries';

select * From mysql.slow_log ;

select convert(sql_text using utf8) sql_text from mysql.slow_log


select count(*) from emp;
时间: 1.918ms
select count(1111) from emp;
时间: 1.898ms
select count(eid) from emp;
时间: 8.799ms


show create table emp;

CREATE TABLE `emp` (
  `eid` bigint(20) DEFAULT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `esex` varchar(5) DEFAULT NULL,
  `ebirthday` datetime DEFAULT NULL,
  `ehisday` datetime DEFAULT NULL,
  `job` varchar(15) DEFAULT NULL,
  `emoney` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

select * from emp where eid = 200000;
时间: 11.306ms

select eid,ename,esex from emp where eid = 200000;
时间: 10.072ms

explain select * from emp where eid = 200000;


alter table emp add primary key(eid);
时间: 83.687ms

select * from emp where eid = 200000;
时间: 0.003ms

select * from emp where eid = 3000000;
时间: 0.002ms

explain select * from emp where ename = '骆乐慕';
时间: 11.105ms
时间: 0.004ms

alter table emp add index(ename);
时间: 84.291ms

 

索 引

 什么是索引？

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

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

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

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

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

索引有它的缺点 

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

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

3. 如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立 最优秀的索引，或优化查询语句。

索引的分类

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

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

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

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

主键索引

语法

说明：

• 某一个属性组能唯一标识一条记录；

• 最常见的索引类型； • 确保数据记录的唯一性；

• 确定特定数据记录在数据库中的位置。

唯一索引

语法

说明：

• 快速定位特定数据；

• index和key关键字都可设置常规索引；

• 应加在查找条件的字段；

• 不宜添加太多常规索引，影响数据的插入、删除和修改操作。

常规索引

语法

说明：

• 避免同一个表中某数据列中的值重复；

• 对比主键索引只能有一个,唯一索引可有多个。

全文索引

语法

用法

全文索引的注意：

1.Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。 2.只有char、varchar、text类型字段能创建全文索引。

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

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

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

注意需要在配置文件中加入：

[mysqld]

ngram_token_size=2

create table wenzhang(
	wid int PRIMARY KEY auto_increment,
	title varchar(20),  
	content text, 
	zuozhe varchar(20), 
	FULLTEXT(title,content,zuozhe) with parser ngram -- 中日韩
);

insert into wenzhang(title,content,zuozhe) 
values
('西安往事','这是一个古老的城市，在这个城市中有很多的人，工厂，建筑物','小杨'),
('山西往事','这是一个古老的城市，这里有很多的人，工厂，建筑','老陶'),
('地球往事','这是一个古老的星球，这里有很多的人','老刘在西安'),
('银河往事','这是一个系，打算在这个系之外造一个西安','小彭');

explain select * from wenzhang 
where match(title,content,zuozhe) AGAINST('西安');

select * from wenzhang where match(title,zuozhe) AGAINST('西安');
ALTER table wenzhang add FULLTEXT(title,zuozhe)with parser ngram;


explain select * from wenzhang where content like '%西安%'

-- 索引的结构 BTREE   B+tree

 

索引的管理

查看索引

SHOW INDEX(或KEYS) FROM 表名;

删除索引

ALTER TABLE 表名 DROP PRIMARY KEY;

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; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

优化原则—索引失效

1.最佳左前缀法则

2.主键插入顺序

3.计算、函数导致索引失效

4.类型转换导致索引失效

5.范围条件右边的列索引失效

6.不等于(!= 或者<>)索引失效

7.is null可以使用索引，is not null无法使用索引

8.like以通配符%开头索引失效

9.OR 前后存在非索引的列，索引失效

10.数据库和表的字符集统一使用utf8mb4