wordpress 网站加载过慢6,有什么网站做热图,asp网站空间申请,wordpress本地图标引用MySQL 文章目录 MySQLchar和varchar的区别视图视图的特点 存储过程存储过程的特点 触发器触发器的特点 MySQL引擎索引为什么要有索引呢#xff1f;什么是索引索引的优势索引的劣势索引类型索引种类组合索引最左前缀原则索引创建原则B树和B树的区别数据库为什么使用B树而不是B树…MySQL 文章目录 MySQLchar和varchar的区别视图视图的特点 存储过程存储过程的特点 触发器触发器的特点 MySQL引擎索引为什么要有索引呢什么是索引索引的优势索引的劣势索引类型索引种类组合索引最左前缀原则索引创建原则B树和B树的区别数据库为什么使用B树而不是B树聚簇索引和非聚簇索引聚簇索引非聚簇索引 数据库事务MySQL事务处理主要有两种方法事务隔离级别为什么要有隔离级别?设置隔离级别为*未提交读*read uncommitted设置*提交读隔离级别*read committed设置*可重复读隔离级别*repeatable read设置串行化(serializable) 事务实现的原理原子性实现原理持久性实现原理 隔离级别实现原理MVCCMVCC是什么ReadView 是什么 锁机制行锁、间隙锁、表锁行锁间隙锁表锁 共享锁(s)排他锁(X) SQL优化 char和varchar的区别
1.长度可变性
varchar类型用于存储可变长度的字符串比固定长度类型更加节省空间。有一种情况除外如果MySQL表使用ROW_FORMATFIXED创建的话每一行都会使用定长存储。
char类型用于存储定长字符串。
2.存储方式
varchar需要1或者2个额外字节记录字符串的长度如果列的最大长度小于或者等于255字节则只用使用1个字节表示否则需要两个字节表示。VARCHAR节省了存储空间所以对性能也有帮助。但是由于行是变长的在UPDATE时可能使行变得比原来更长这就导致需要做额外的工作。如果一个行占用的空间增长并且在页内没有更多的空间可以存储在这种情况下不同的存储引擎的处理方式是不一样的。例如MylSAM会将行拆成不同的片段存储InnoDB则需要分裂页来使行可以放进页内。
char适合存储很短或者长度近似的字符串。例如密码的MD5值因为这是一个定长值。对于经常改变的值char要比varchar好因为定长的char类型不容易产生碎片。对于非常短的列char在空间存储上比varcahr更有效率例如用char1来存储只有Y和N的值如果采用单字节字符集只需要一个字节但是varchar1却需要两个字节因为还有一个记录长度的额外字节。
视图
视图是虚拟的表。与包含数据的表不一样视图只包含使用时动态索引数据的查询。
创建视图视图用create view语句来创建。
/*CREATE VIEW 视图名称 AS SQL查询语句*/
CREATE VIEW view1 AS
SELECTaccount,admin.name aname,role.name rname
FROMadmin,admin_role,ROLE
WHERE admin.id admin_role.adminidAND admin_role.roleid role.id执行语句:
SELECT * FROM view1修改:
#方式一
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
#方式二
ALTER VIEW 视图名
AS
查询语句;删除语句
DROP VIEW 视图1,视图2,...;视图的特点
简化sql语句提高SQL的重用性保护基本表数据,提高了安全性
小结
视图为虚拟的表。它包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次。
存储过程
存储过程简单来说存储过程就是为以后使用而保存的一条或者多条SQL语句。可以将其视为批文件虽然他们的作用不仅限于批处理。
在存储过程的定义中可以定义参数参数分为IN、OUT、ONOUT三种类型。
IN类型表示接受调用者传入的数据OUT类型表示向调用者返回数INOUT类型既可以接受调用者传入的参数也可以向调用者返回数据。
存储过程的特点
通过处理封装在容易使用的单元中简化了复杂的操作。简化对变动的管理。如表名、列名、或者业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用修改自己的代码。有助于提高应用程序的性能减少应用程序与数据库服务器之间的流量。应为应用程序不必发送多个冗长的SQL语句只用发送存储过程中的名称和参数即可。
缺点
大量使用存储过程这些存储过程的每个链接的内存使用量将大大增加。如果在存储过程中大量使用逻辑操作CPU的使用率也会增加。MySQL数据库最初的设计就侧重于高效的查询而不是逻辑运算。
CREATEPROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2...])BEGIN[DECLARE 变量名 类型 [DEFAULT 值];]存储过程的语句块;END;
定义一个存储过程
CREATEPROCEDURE demo2(IN s_sex CHAR(1),OUT s_count INT)-- 存储过程体BEGIN-- 把SQL中查询的结果通过INTO赋给变量SELECT COUNT(*) INTO s_count FROM student WHERE sex s_sex;SELECT s_count;END$$调用这个存储过程
-- s_count表示测试出输出的参数
CALL demo2 (男,s_count);
/* 结果
s_count6
*/触发器
触发器trigger是一种特殊的存储过程,其特殊性在于它并不需要用户直接调用而是对表添加、修改、删除之前或者之后自动执行的存储过程。
触发器的特点 与表相关联 触发器定义在特定的表上这个表称为触发器表。 自动激活触发器 当表中的数据执行INSERT、UPDATE或者DELETE操作时如果表上的这个操作定义了触发器该触发器自动执行这是不可以撤销的。 不能直接调用 与存储过程不同触发器不能被直接调用也不能传递或者接受参数。 作为事务的一部分 触发器和激活触发器的语句一起作为一个单一的事务来对待可以从触发器中的任何位置回滚。
定义触发器
CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称FOR EACH ROW -- 行级触发
BEGIN语句
END;语法解析
发器名称用来标识触发器的由用户自定义。触发时机其值为before或者after。触发事件insert、update和delete表名称表示在哪张表建立触发器语句触发器程序体触发程序可以使用begin和end作为开始和结束中间包含多条语句
删除用户时自动删除用户菜单关系
DELIMITER $$
CREATE TRIGGER delete_user_menu BEFORE DELETE
ON t_user
FOR EACH ROW
BEGINDELETE FROM t_user_menu WHERE user_id old.id;
END$$;新增用户时 自动向其他表中插入数据
DELIMITER $$
CREATE TRIGGER save_user_log AFTER INSERT
ON user
FOR EACH ROW
BEGININSERT INTO test(id,NAME)VALUES(new.id,new.account);
END$$;在行级触发器代码中,可以使用old和new访问到该行的旧数据和新数据,old和new是对应表的行记录类型变量。
MySQL引擎
数据库引擎用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并且快速处理事务从而满足企业内大多数需要大量数据的应用程序要求。
存储引擎主要有
MyIsam、InnoDB、Memory、Blackhole等。
mysql数据库默认引擎是InnoDB
InnoDB是一个事务型的存储引擎有行级锁和外键约束支持全文检索索引它的设计目标是处理大容量数据库系统MySQL运行时InnoDB会在内存中建立缓冲池用于缓冲数据和索引支持主键自增不存储表的总行数。
索引
为什么要有索引呢
假设某张表中有10万条数据这100万条数据在硬盘上是存储在数据页上的一页数据大小为16K100万条数据需要很多数据页现在如果要查询id 8900条数据信息。MySQL需要全表扫描来找到id 8900 的数据也就是从“数据页1”来查询对于大量数据查询起来是非常慢的。
什么是索引
索引就是一个排好序的快速查找的数据结构。
数据库除存储数据本身之外还维护着一个满足特定查找算法的数据结构这些数据结构以某种方式指向数据这要就可以在这些数据结构的基础上实现高级查找算法这种数据结构就是索引。 左边是数据表一共两列七行记录最左边表示数据记录的地址为了加快Col2的查找速度可以维护一个右边所示的二叉查找树每个节点分别包含索引键值和一个执行对应数据记录物理地址的指针这样就可以运用二叉查找在一定的复杂度内获取到响应数据而快速的检索出符合条件的记录。
索引的原理类似于查字典查询书籍等。本质都是不断地缩小数据范围筛选出想要的结果。
索引的优势
提高数据检索的效率降低数据库的IO成本
通过索引列对数据进行排序降低数据排序的成本降低了CPU的消耗
索引的劣势
索引也是一张表该表保存了主键与索引字段并指向实体表的记录所以索引列也是要占用磁盘内存的。
索引提高查询速度的同时也会降低更新表的速度例如进行INSERT、UPDATE和DELETE因为更新时MySQL不仅要保证数据还要保证一下索引文件每次更新添加了索引列的字段都会调整因为更新带来的键值变化后的索引信息。
索引类型
FULLTEXT全文索引一般用于查找文本中的关键字而不是直接比较是否相等多在char、varchar、text 等数据类型中创建全文索引。全文索引主要是用来解决like模糊匹配效率低的问题
HASH哈希索引多用于等值查询时间复杂度为o1效率非常高但是不支持排序、范围查询和模糊查询等
BTREEB数索引InnoDB存储引擎默认的索引支持排序分组范围查询模糊查询等并且性能稳定。
RTREE空间数据索引多用于地理数据的存储优势在于支持范围查找。
索引种类
主键索引设定为主键后数据库会自动创建索引
ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
单列索引一个索引值包含一个列一个表可以有多个单列索引
CREATE INDEX 索引名 ON 表名(列名);
唯一索引索引列的值必须唯一允许为null
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
组合索引一个索引包含多个列在数据库操作期间符合索引比单值左印所需要的的开销更小对于相同的多个列建立索引
CREATE INDEX 索引名 ON 表名(列 1,列 2…);
组合索引最左前缀原则
例如某个表中有a、b、c三列a、b为组合索引那么使用时需要满足最左侧索引原则。在使用组合索引的列作为条件时必须出现最左侧列作为条件否则组合索引不生效。
-- 列如
select * from table where a’’and b’’-- 索引生效
select * from table where b’’and a’’-- 索引生效
select * from table where a’’and c’’-- 索引生效
select * from table where b’’and c’’-- 索引不生效索引创建原则
需要创建索引
主键自动创建唯一索引频繁作为查询条件的字段应该设置为索引就是where后面的语句查询中与其他表关联的字段外键关系建立索引查询中排序的字段排序字段若通过索引去访问将大大提高排序速度分组中的字段需要建立索引
不要创建索引 表记录太少 经常增删改的表索引提高了查询的速度同时会降低更新表的速度如对表进行INSERT、UPDATE、DELETE因为更新表时MySQL不仅要存储数据还要更新索引文件 where条件里用不到的字段不创建索引 数据重复且分布平均的表字段
B树和B树的区别
数据库为什么使用B树而不是B树
聚簇索引和非聚簇索引
聚簇索引
找到了索引就找到了需要的数据那么这个索引就是聚簇索引所以主键就是聚簇索引。
非聚簇索引
索引的存储和数据是分离的也就是说找到了索引但是没有找到数据需要根据索引上的值再次回表查询非聚簇索引也叫辅助索引。
一个例子
CREATE TABLE student (id BIGINT,NO VARCHAR (20), NAME VARCHAR (20), PRIMARY KEY (id), UNIQUE KEY idx_no (no)
)创建一个学生表做三种查询
SELECT * FROM student WHERE id 1直接根据主键查询获取所有字段数据此时主键是聚簇索引因为主键对应的索引叶子节点存储了id1的所有字段的值。
SELECT NO,NAME FROM student WHERE NO 123根据编号查询编号本身是一个唯一索引但查询的列包含学生编号和学生姓名当命中编号索引时该索引的节点的数据存储的是主键ID需要根据主键ID重新查询一次所以这种查询下编号no不是聚簇索引。
SELECT NO FROM student WHERE NO 123根据编号查询编号这有啥好差的要的。可能是验证数据库中是否存在该编号这种查询命中编号索引时直接返回编号因为需要的数据就是该索引不需要回查这种场景下no就是聚簇索引。
数据库事务
MySQL事务处理主要有两种方法 用BEGIN、ROLLBACK、COMMIT来实现 BEGIN开启一个事务 ROLLBACK事务回滚 COMMIT事务确认 直接用set来改变MySQL的自动提交模式 set global autocommit 0;-- 禁止自动提交set global autocommit 1;-- 开启自动提交查看autocommit模式:
show global variables like autocommit;事务隔离级别
为什么要有隔离级别?
MySQL是一个服务器/客户端软件架构也就是说多个客户端连接服务器后可以同时在不同的会话一个客户端与服务器连接成功后就叫一个会话中对服务器进行操作输入各种语句这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求也就是说服务器可能同时在处理多个事务这样可能会导致不同的事务同时访问相同的数据。因为一个事务具有隔离性当一个事务提交后其他事务才能执行继续访问这个数据。这样对性能影响很大所以设计数据库时就提出来各种隔离级别来最大限度的提升系统并发处理事务的能力。
查看隔离级别
SELECT global.transaction_isolation,transaction_isolation;mysql数据库提供了四种隔离级别实际开发根据不同场景选择不容的隔离级别除了串行化其他级别都存在某种问题。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED原数据users表:
idnameage1jim20
设置隔离级别为未提交读read uncommitted
同时开启两事务A、BB事务在A事务还没有提交的时候读取A中修改的数据。
事务A
BEGIN;UPDATE users SET age 18 WHERE id 1 -- 1ROLLBACK -- 3COMMIT; -- 4事务B:
BEGIN;SELECT age FROM users WHERE id 1 -- 2COMMIT;-- 5
/*
结果: 18
数据库表 age 20
*/B读取可A未提交的数据A事务中发生了回滚数据库中age并没有被修改所以B事务读取到的数据是错的所以称之为脏读。
设置提交读隔离级别read committed
同时开启两个事务A、BB事务在A事务提交后才能读取到A中修改的结果。
A事务未提交时B事务获取到的仍然是事务开启前的结果。
A事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN;UPDATE users SET age 18 WHERE id 1-- 1COMMIT;-- 3B 事务:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN;SELECT age FROM users WHERE id 1 -- 2 结果 20SELECT age FROM users WHERE id 1 -- 4 结果 18COMMIT;-- 5提交读隔离级别可以解决脏读问题但是B事务中两次查询的结果不一致称为不可重复读。
设置可重复读隔离级别repeatable read
MySQL默认的隔离级别。同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题但是除InnoDB 外幻读依然存在。
事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN;UPDATE users SET age 18 WHERE id 1 -- 2COMMIT; -- 3 事务B:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN;SELECT age FROM users WHERE id 1 -- 1 结果18SELECT age FROM users WHERE id 1 -- 4 结果18COMMIT; -- 4可重复读可能会产生幻读问题幻读就是B事务查询了users 表发现只有id1的用户想要添加一个id 2的用户但是A事务快一步添加B事务在添加的时候就会显示重复的主键可是B事务查询明明没有id 2的数据。
设置串行化(serializable)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE解决幻读问题
读写加锁B事务如果未提交A事务是无法insert的必须等待B事务提交
事务A
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN;INSERT INTO users(id,NAME,age) VALUE (3 ,ldl,18)-- 2 执行后一直在等待状态,直到B事务提交.COMMIT;事务B:
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN;SELECT * FROM users -- 1COMMIT;事务隔离级别脏读不可重复读幻读read uncommitted可能可能可能read committed不可能可能可能repeatable read不可能不可能可能serializable不可能不可能不可能
事务实现的原理
MySQL中日志有很多种如二进制日志、错误日志、查询日志、慢查询日志等此外InnoDB存储引擎还提供了两种日志redolog重做日志和undolog回滚日志。其中redolog用于保存事务的持久性undolog则是事务原子性和隔离性实现的基础。 原子性实现原理
原子性实现的关键是当事务发生回滚的时候能撤销所有已经成功执行的SQL语句。InnoDB实现回滚靠的是undolog当事务对数据库中的数据进行修改时InnoDB会生成对应的undolog如果事务执行失败或者是执行ROLLBACK导致事务需要回滚时就可以利用undolog中的数据来回滚到修改之前的样子。
undolog属于逻辑日志它记录的是SQL执行的相关信息。当发生回滚时InnoDB会根据undolog的内容做与之前相反的工作对于每个insert回滚时执行delete每个delete执行insert每个update执行一个相反的update把数据改回去。
持久性实现原理
redolog叫做重做日志是保证实物持久性的重要机制。当mysql服务器意外崩溃或者宕机后保证已经提交的事务确定持久化到磁盘中的一种措施。
InnoDB是以页为单位来管理存储空间的任何的增删改查操作最终都会操作完整的一个页会将整页的数据加载到buffer pool中然后对需要修改的记录进行修改修改完毕后不会立即刷新到磁盘中而且仅仅修改了一条记录刷新一个完整的数据页的话过于浪费了。但是如果不刷新的话数据此时还在内存中如果此时系统崩溃最终数据会丢失。因此引入了redolog也就是说修改完成之后不立即刷新而是记录一条日志日志内容就是记录那个页面多少偏移量什么数据发生了什么改变。这样即使系统崩溃在恢复后也可以根据日志进行数据恢复。另外redolog是循环写入固定的文件是顺序写入磁盘的。
总结redolog就是记录在操作之后记录哪个页面多少偏移量什么数据发生了怎样的变化。
隔离级别实现原理MVCC
MVCC是什么
多版本并发控制MVCCMulti-Version Concurrent Control是MySQL提高性能的一种方式配合undolog和版本链让不同事务的读写、写读操作可以并发执行减少锁的使用从而提升系统的性能。
MVCC的目的是让读写,写读操作并发执行.
MVCC使得数据库读不会对数据加锁普通的select请求不会加锁提高了数据库的并发处理能力。借助MVCC数据库可以实现READ COMMITTEDREPEATABLE READ等隔离级别。 事务D的两次查询在不同的隔离级别结果是不同的。
读未提交name 张二三 name 张三三
读已提交name 张三 name 张二三
可重复读 name 张三 name 张三
MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一个保存行的事务IDTRX_ID,一个保存了行的回滚指ROLL_PT).
trx_id每次对某行记录执行改动时都会把对应事务id赋值给trx——id隐藏列。
roll_pt每次对记录进行改动时都会把旧版本写到undolog日志中然后这个隐藏列就相当于一个指针可以通过它来找到修改前的信息。
对该记录每次更新后都会将旧值放到一条 undolog 中就算是该记录的一个旧版本随着更新次数的增多所有的版本都会被 roll_pt 属性连接成一个链表我们把这个链表称之为版本链版本链的头节点就是当前记录最新的值。另外每个版本中还包含生成该版本时对应的事务 id这个信息很重要。
ReadView 是什么
ReadView是“快照读”SQL执行时MVCC提取数据的依据是一个数据结构包含4个字段
m_ids当前活跃的事务编号集合
min_trx_id最小活跃事务编号
max_trx_id预分配事务编号当前最大事务编号1
creator_trx_idReadView创建者的事务编号
读已提交(RC) 可重复读(RR):
读已提交级别: 称为当前读,当每个事物每次读取时,会生成一个 readVew,读取
的是最新数据.
可重复读级别: 称为快照读,当一个事务第一次查询时,会生成一个 readView,第二次查询时仍会从当前 readView 中读数据.
锁机制
行锁、间隙锁、表锁
按照粒度锁可以分为行锁、间隙锁和表锁。间隙锁位于行锁和表锁之间。表锁在操作数据时会锁定整张表并发性能差行锁只锁定需要操作的数据并发性能好但是加锁本身就需要消耗资源获取锁、检查锁、释放锁等都需要耗费资源因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同存储引擎支持的锁是不一样的MyIsam支持表锁InnoDB同时支持表锁和行锁。
行锁
MySQL中粒度最小的一种锁表示只针对当前操作的行进行加锁。行锁能大大减少数据库操作的冲突。其锁的粒度小但是加锁的开销大
特点开销大加锁慢会出现死锁锁定力度小发生冲突概率低并发度也高。
间隙锁
锁的是一个区间,当我们用范围条件而不是相等条件检索数据InnoDB 会给符合条件的已有数据记录的索引项加锁对于键值在条件范围内但并不存在的记录叫做“间隙,InnoDB 也会对这个“间隙”加锁这种锁机制就是所谓的间隙锁Next-Key 锁。 例如
select * from student where id2 and id5
/*
数据库中只有id为1和2的数据
即使数据库中没哟id 3 的数据,其他线程想要添加id为3的数据也是不可以的,因为2到5的数据已经被锁定.
*/表锁
表级锁是 MySQL 中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分 MySQL 引擎支持。最常使用的MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享锁与表排他锁。
特点
开销小加锁快不会出现死锁锁定粒度大发出锁冲突的概率最高并发度最低.
共享锁(s)
共享锁又称为读锁。允许一个事务去读一行组织其他事务获取相同数据的排他锁。若事务T对数据A加上S锁则事务T可以读取A但是不能修改A其他事务只能对A加S锁不能加X锁直到T释放A上的S锁。
排他锁(X)
排他锁又称为写锁。允许获取排他锁的事务更新数据阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务 T 对数据对象 A 加上 X 锁事务 T可以读 A 也可以修改 A其他事务不能再对 A 加任何锁直到 T 释放 A 上的锁。update,delete,insert 都会自动给涉及到的数据加上排他锁select 语句默认不会加任何锁类型。
SQL优化 尽量不要使用select * 而是使用具体字段。可能用到覆盖索引减少回表提高查询效率。 避免在where字句中使用or来链接条件使用union all吧两个SQL结果合并。使用or可能会使索引失效从而全表扫描。 尽量用数值代替字符串类型。如主键用int类型性别用0/1.在查询和链接时会逐个比较字符串的每个字符数值类型只需要比较一次就可以了。 对于查询的优化尽量避免全表扫描首先应该考虑在where以及order by涉及到的列上建立索引。 尽量避免索引失效 5.1 在where字句中对字段进行null值判断否则将会导致引擎放弃使用索引进而全表扫描可以在num上设置默认值0 5.2 避免在where字句中使用or来链接条件将会导致引擎无法使用索引进而全表扫描使用union all吧两个SQL结果合并。 5.3 in和not in 也要谨慎使用例如in1,2,3这种连续的数值能用between就不要用inbetween 1 and 3 5.4 模糊查询也将导致全表扫描。 5.5尽量避免在where字句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)abc inner join、left join、right join优先使用inner join 三种连接如果结果相同优先使用 inner join inner join 内连接只保留两张表中完全匹配的结果集 left join 会返回左表所有的行即使在右表中没有匹配的记录 right join 会返回右表所有的行即使在左表中没有匹配的记录 使用group by 时建议先过滤在分组。 清空表时优先使用truncate truncate table 比 delete 速度快且使用的系统和事务日志资源少. delete 语句每次删除一行并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据. 表链接不宜太多。 不建议在索引上使用内置函数。
