大连做公司网站,建立网站需要多少钱费用,wordpress搭建付费网站,黑糖 wordpress什么是事务#xff1f; 事务#xff08;Transaction#xff09;#xff0c;就是将一组SQL语句放在同一批次内去执行#xff0c;如果一个SQL语句出错#xff0c;则该批次内 的所有SQL都将被取消执行。 特点 一个事务中如果有一个数据库操作失败#xff0c;那么整个事务… 什么是事务 事务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 BSession 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 BSession 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_outputTABLE;
--设置慢查询的时间
Set global long_query_time3;
--开启慢查询
Set global slow_query_logON;
--慢查询的次数 Show status like slow_queries;
--慢查询记录
Select * From mysql.slow_log;
--慢查询sql语句
Select convert(sql_textusingutf8) sql_textfrommysql.slow_log
--关闭慢查询
Set global slow_query_logOFF; 什么是索引 索引是对数据库表中一列或多列的值进行排序的一种结构使用索引可快速访问数据库表中的特定信息。
高效性
利用索引可以提高数据库的查询效率.
完整性
用户可以加速表和表之间的连接 实现表与表之间的参照完整性
唯一性
索引可以确保所查的数据的唯一性
特殊能力
通过使用索引可以在查询过程中使用优化隐藏器提高系统性能。
索引有它的缺点 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 idXX; 否则会产生间隙锁内部扩大锁定范围导致系统性能下降产生死锁。
