MYSQL数据库详解（5）
事务
事务（Transaction），就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。
特点：一个事务中如果有一个数据库操作失败，那么整个事务的所有数据库操作都会失败，数据库数据就会回滚到该事务开始之前的状态。
限制：MySQL数据库中仅InnoDB和BDB类型的数据库表支持事务
事务的四大原则（特性）
MySQL实现事务的步骤
1.关闭自动提交
set autocommit= 0;
2.开启事务
start transaction;
3.执行一组SQL语句
update bank set bmoney = bmoney - 5 where bname = '小牛';
update bank set bmoney = bmoney + 5 where bname = '小铁';
4.结束事务（只能执行一个）
– 提交
commit;
– 回滚
rollback;
5.开启自动提交
set autocommit = 1;
事务的隔离性：为了让不同的事务之间相互不存在干扰，就需要对事务的操作进行隔离，事务的隔离性也就是将操作同一个数据的事务相互分离，让操作之间分开有序的执行
实现方式:通常数据库里都是采用锁的机制，保证事务之间的隔离性。
锁分类：
**基于锁的属性分类：**共享锁（读锁、S锁）、排他锁（写锁，X锁）。
**基于锁的粒度分类：**表锁、行锁(记录锁、间隙锁、临键锁)。
**基于锁的状态分类：**意向共享锁、意向排它锁
事务并发问题：
在事务并发执行的时候，如果不进行事务隔离，那么就会产生脏写、脏读、重复读、幻读的问题。
事务的隔离级别：
1.READ_UNCOMMITTED 读未提交
2.READ_COMMITTED 读提交（不可重复读）
3.REPEATABLE_READ 可重复读
4.SERIALIZABLE 串行化
每个隔离级别都针对事务并发问题中的一种或几种进行解决，事务级别越高，解决的并发事务问题也就越多，同时也意味着加的锁就越多，所以性能也会越差。
事务的隔离级别解决的问题
1.READ_UNCOMMITTED 读未提交
事务读取:不加锁
事务写入:加写锁
解决问题:脏写
存在问题:脏读，不可重复读、幻读
2.READ_COMMITTED 读提交（不可重复读）
事务读取:加读锁(每次select完成都会释放读锁)
事务写入:加写锁
解决问题:脏写、脏读
存在问题:不可重复读、幻读。
3.REPEATABLE_READ 可重复读
事务读取:加读锁(每次select完不会释放锁，而是事务结束后才释放) (如果是Mysql的innodb还会加间隙锁)。
事务写入:加写锁
解决问题:脏写、脏读、不可重复读，幻读(如果是Mysql的innodb则已解决)
存在问题:幻读(如果是Mysql的innodb则不存在)。
4.SERIALIZABLE 串行化
不管读取还是修改所有的事务串行化执行个事务的执行必须等其他事务结束。
函数
MySQL函数，是一种控制流程函数，属于数据库用语言。
函数和方法的区别
相同点：实现某个功能
不同点：
函数：系统调用的
方法：某个对象调用的
日期时间
– 当月生日的学生
select * from student where month(birthday) = month(now());
时间差
1.datediff() 只能算天 大的日期在前数值为正，反之数值为负
select datediff(now(),'1998-7-21');
select datediff('1998-7-21',now());
2.timestampdiff()
select timestampdiff(second,'1998-7-21',now());
时间间隔
时间间隔字面量
方式1
select now() + interval 5 day
select now() - interval 5 month
方式2
select date_add(now(), interval 10 minute)
select date_sub(now(), interval 10 hour);
字符串
拼接
select concat('JX','你好','万岁');
select concat_ws('-','JX','你好','万岁','!')
数学函数
向上取整
select ceiling(3.14)
向下取整
select floor(3.99)
四舍五入
select round(3.49,1)
截断
select truncate(3.49,1);
聚合函数
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
MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。
表头数据 ：table | type | possible_keys | key | key_len | ref | rows | Extra
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，这就会发生，或者是查询有问题。
索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
高效性：利用索引可以提高数据库的查询效率
完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性
**唯一性 **：索引可以确保所查的数据的唯一性
特殊能力：通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。
缺点：
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
2.因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
3.如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
分类：
主键索引
CREATE TABLE `表名` (
`字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,
或 PRIMARY KEY(`字段1`)
)
唯一索引
CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL UNIQUE,
#或 UNIQUE KEY(`字段1`)
)
常规索引
CREATE TABLE `表名` (
`字段1 `INT(11) NOT NULL,
INDEX/KEY(`字段1`)
)
全文索引
语法
CREATE TABLE 表名 (
字段1 VARCHAR(32) NOT NULL,
fulltext key (字段名，字段名，字段名) with parser ngram
)
create table wenzhang(
wid int PRIMARY KEY auto_increment,
title varchar(20),
content text,
zuozhe varchar(20),
FULLTEXT(title,content,zuozhe) with parser ngram
);
用法
SELECT <字段表> FROM <表名> WHERE MATCH(字段) AGAINST (‘要搜索的关键词’);
select * from wenzhang where match(title,zuozhe) 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
索引的管理
索引九大使用原则
1.最左前缀匹配原则，非常重要的原则：对于联合索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。
2.尽量选择区分度高的列作为索引 ：区分度的公式是count(distinct col)/count(*)
3.=和in可以乱序
4.索引列不能参与计算，保持列“干净”
5.尽量的扩展索引，不要新建索引
6.索引不会包含有NULL值的列
7.使用短索引 ：短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作
8.索引列排序： 因此数据库默认排序可以符合要求的情况下不要使用排序操作
9.like语句操作：一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，过多的创建索引反而不能提升速度。
使用会索引的场景
1、匹配全值：对索引中所有列都指定具体值，即对索引中的所有列都有等值匹配的条件。
2、匹配值的范围查询：对索引的值能够进行范围查找
3、匹配最左前缀：仅仅使用索引中的最左边列进行查询。
比如组合索引（col1,col2,col3）能够被col1，col1+col2，col1+col2+col3的等值查询利用到的。
4、仅对索引进行查询：当查询列都在索引字段中。即select中的列都在索引中。
5、匹配列前缀：仅仅使用索引的第一列，并且只包含索引第1列的开头部分进行查找。
例如：WHERE title LIKE ‘xxx%’
6、索引部分等值匹配，部分范围匹配
7、若列名是索引，则使用column_name is null就会使用索引
索引存在但不能使用索引的场景
1、以%开头的like查询
2、数据类型出现隐式转化，不会使用索引
3、组合索引，不满足最左原则，不使用符合索引
4、估计使用索引比全表扫描还慢，则不要使用索引
5、用or分割条件，若or前后只要有一个列没有索引，就都不会用索引
6、使用 != 或 <> 操作符时 ：尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。
7、对字段进行null值判断：应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
8、避免select *： 在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
SQL优化原则
- 建议使⽤预编译语句进⾏数据库操作
- 避免数据类型的隐式转换
- 充分利⽤表上已经存在的索引
- 禁⽌使⽤ SELECT * 必须使⽤ SELECT 查询
- 禁⽌使⽤不含字段列表的 INSERT 语句
- 避免使⽤⼦查询，可以把⼦查询优化为 join 操作
- 避免使⽤ JOIN 关联太多的表
- 对应同⼀列进⾏ or 判断时，使⽤ in 代替 or
- WHERE 从句中禁⽌对列进⾏函数转换和计算
- 在明显不会有重复值时使⽤ UNION ALL ⽽不是 UNION
ll值判断：应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
8、避免select *： 在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
SQL优化原则
- 建议使⽤预编译语句进⾏数据库操作
- 避免数据类型的隐式转换
- 充分利⽤表上已经存在的索引
- 禁⽌使⽤ SELECT * 必须使⽤ SELECT 查询
- 禁⽌使⽤不含字段列表的 INSERT 语句
- 避免使⽤⼦查询，可以把⼦查询优化为 join 操作
- 避免使⽤ JOIN 关联太多的表
- 对应同⼀列进⾏ or 判断时，使⽤ in 代替 or
- WHERE 从句中禁⽌对列进⾏函数转换和计算
- 在明显不会有重复值时使⽤ UNION ALL ⽽不是 UNION
- 拆分复杂的⼤ SQL 为多个⼩ SQL
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
