MySQL笔记4——事务、函数和索引

MySQL事务

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

特点

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

限制

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

事务的ACID原则

• 原子性：意味着数据库中的事务执行是作为原子粒度，即不可再分，整个语句要么执行要么不执行。
• 一致性：即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
• 隔离性：事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。
• 持久性：意味着在事务完成之后，该事务对数据库所作的更改便持久的保存在数据库中，并不会被回滚。

MySQL实现事务的方法

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

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

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

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

MySQL实现事务的步骤

1、关闭MySQL自动提交 SET AUTOCOMMIT = 0

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

3-1、向数据库提交事务 COMMIT

3-2、将事务回滚，所有数据库操作被取消 ROLLBACK

4、开启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. 脏写（ 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机制解决可重复读中的幻读问题。

# 事务
-- 事务的四大特性
-- 原子性、一致性、隔离性、持久性
USE myschoolxxx
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='杨文琦';
select * from bank;
-- 4. 结束事务(判断)
    -- 提交
    commit;
    -- 回滚
    rollback;
-- 5. 开启自动提交
set autocommit = 1;

M y S Q L 函 数

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

• 日期时间函数：于日期和时间类型的字段进行处理
• 字符串函数：对于字符串类型的字段处理
• 数学函数：用作常规的数学运算
• 聚合函数：常用于GROUP BY从句的 SELECT查询中
• 自定义函数：根据实际需求自定义函数

常用的日期函数

函数名	返回值
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
NOW()	返回当前的日期和时间
MONTH(date)	返回date的月份值(1~12)
DATE_FORMAT(d ate,fmt)	依照指定的fmt格式格式化 日期date值
DAY(date)	返回date的日
YEAR(date)	返回日期date的年份 (1000~9999

-- 时间函数
-- 当月过生日的学生
SELECT * FROM student WHERE MONTH(birthday)=MONTH(now())
-- 日期的格式化
SELECT DATE_FORMAT(NOW(),'%a-%b-%M')
-- 时间差（日）
SELECT DATEDIFF(NOW(),'2002-01-20')
-- 时间差（自定单位）
SELECT TIMESTAMPDIFF(second,'2002-01-20',NOW())
-- 时间的加减
SELECT NOW() - INTERVAL 5 YEAR

SELECT DATE_ADD(NOW(),INTERVAL 10 year)

SELECT DATE_SUB(NOW(),INTERVAL 10 year)

常用的字符串函数

函数名	返回值
CONCAT(s1,s2...,s n)	将s1,s2...,sn连接成字符串
CONCAT_WS(sep, s1,s2...,sn)	将s1,s2...,sn连接成字符串，并用sep 字符间隔

-- 字符串拼接
SELECT CONCAT("hello ","world")

常用的数学函数

函数名	返回值
CEILING(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)	返回数字x截短为y位小数的结果

常用的聚合函数

函数名	返回值
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果

-- 数学函数
SELECT FLOOR(2.9)
SELECT ROUND(2.4)
SELECT ROUND(2.775,2)
-- 截断，在删除表时也用过
SELECT TRUNCATE(3.999,1)

SELECT Ssex,COUNT(*) FROM student GROUP BY Ssex
SELECT Ssex,GROUP_CONCAT(sname) FROM student GROUP BY Ssex

慢查询

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_text using utf8) sql_text from mysql.slow_log

-- 关闭慢查询

set global slow_query_log='OFF';

执行计划

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，这就会发生，或者是查询有问题。

-- 慢查询

-- 显示到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_text using utf8) sql_text from mysql.slow_log

-- 关闭慢查询
set global slow_query_log='OFF';

索引

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

1. 高效性：利用索引可以提高数据库的查询效率
2. 完整性：用户可以加速表和表之间的连接， 实现表与表之间的参照完整性
3. 唯一性：索引可以确保所查的数据的唯一性
4. 特殊能力：通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。

索引缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。
2. 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘 空间的索引文件。
3. 如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

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

索引的分类

• 主键索引：在数据库关系图中为表定义一个主键将自动 创建主键索引。
• 唯一索引：不允许具有索引值相同的行，从而禁止重复 的索引或键值。
• 常规索引：最基本的索引类型，没有 唯一性之类的限制。
• 全文索引：搜索引擎的关键技术，用于检索文本信息， 可以是词语或者段落。

主键索引

REATE TABLE `表名` (

        `字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,

        #或 PRIMARY KEY(`字段1`)

)

说明：

• 某一个属性组能唯一标识一条记录；
• 最常见的索引类型；
• 确保数据记录的唯一性；
• 确定特定数据记录在数据库中的位置

唯一索引

CREATE TABLE `表名` (

        `字段1` INT(11) NOT NULL UNIQUE,

        #或 UNIQUE KEY(`字段1`)

)

说明：

• 快速定位特定数据；
• index和key关键字都可设置常规索引；
• 应加在查找条件的字段；
• 不宜添加太多常规索引，影响数据的插入、删除和修改操作

常规索引

CREATE TABLE `表名` (

        `字段1` INT(11) NOT NULL,

        INDEX/KEY(`字段1`)

)

说明：

• 避免同一个表中某数据列中的值重复；
• 对比主键索引只能有一个,唯一索引可有多个。

全文索引

语法：

CREATE TABLE `表名` (

        `字段1` VARCHAR(32) NOT NULL,,

        fulltext key (字段名，字段名，字段名) with parser ngram

)ENGINE=innodb

用法：

SELECT FROM WHERE MATCH(字段) AGAINST (‘要搜索的关键词’);

注意：

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
('西安往事','这是一个古老的城市，在这个城市中有很多的人，工厂，建筑物','小杨'),
('山西往事','这是一个古老的城市，这里有很多的人，工厂，建筑','老陶'),
('地球往事','这是一个古老的星球，这里有很多的人','老刘在西安'),
('银河往事','这是一个系，打算在这个系之外造一个西安','小彭');

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;

SELECT * FROM wenzhang WHERE content LIKE '%西安%'

-- 
create table login(
    lid int primary key auto_increment,
    lname varchar(20),
    lpwd varchar(20),
    lrname varchar(10),
    lsex varchar(1),
    laddr varchar(200)
);
insert into login (lname,lpwd,lrname,lsex,laddr)
values('zhangsan','123456','张三','男','西安小寨天桥下');

-- 索引的结构 HASH b+tree