网站建设与管理复习题,营销比较成功的品牌,长沙搭建网络环境,创建网站怎么创一.MySQL架构连接层:处理客户端连接服务,认证授权相关的操作服务层:最核心的一层#xff08;核心服务功能#xff09;,处理sql,包括sql优化,函数调用....存储引擎层:存储引擎是真正负责来操作数据的#xff08;mysql中数据的存储和提取#xff09;, mysql中有不同存储引擎,…一.MySQL架构连接层: 处理客户端连接服务,认证授权相关的操作服务层: 最核心的一层核心服务功能,处理sql,包括sql优化,函数调用....存储引擎层: 存储引擎是真正负责来操作数据的mysql中数据的存储和提取, mysql中有不同存储引擎,不同的引擎处理技术不同物理文件层: 存在电脑硬盘上的表数据,日志文件负责与存储引擎的数据交互二.存储引擎 存储引擎是真正负责来操作数据的,不同的引擎处理技术不同 (支持事务,行级锁,外键约束,索引技术)
查看支持的引擎 SHOWENGINES;查看表引擎 SHOWTABLE STATUS LIKE 表名修改引擎 方式1:将mysql.ini 中default-storage-engineInnoDB重启服务. 方式2:建表时指定 CREATETABLE 表名(...)ENGINEMYISAM; 方式3:建表后修改 ALTERTABLE 表名 ENGINEINNODB;
存储引擎主要有 1.MyIsam,2.InnoDB,3.Memory,4.Blackhole,5.CSV,6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam但在实际开发中,需要根据具体的操作,来为表选择合适的引擎mysql中常用的两种引擎:用MyIsam 和InnoDB两者的区别 innodb:支持事务,有行级锁,外键约束 myisam: 不支持事务,不支持行锁,支持表锁,不支持外键约束 因此当INSERT或UPDATE数据时即写操作需要锁定整个表eg: 新增,修改,删除操作比较多的表,建议使用innodb引擎 查询多的表(例如每月话费账单表),只插入一次数据,后面都是查询操作,建议使用myIsam引擎.注由于现在表数据量比较大,动辄需要对数据库进行拆分,使用存储过程,视图,自定义函数这些内容移植性差,所以不建议使用了.三.索引1.为什么要用索引 表中存储着大量数据,如果没有索引,当我们需要查询一条数据时,就要从第一条数据开始查询,直到找到我们需要查询的数据,这么查找效率太低,太耗时间和内存,所以需要索引2.索引的优/缺点优点: ①减少了查询次数, 降低IO成本(与硬盘交互次数),提高了数据检索的效率 ②索引是已经排好序了的,通过索引给数据排序提高排序效率,减少来cpu的消耗缺点: ①索引的本质是一张表保存了主键与索引字段,并指向实体表的记录,它也是占磁盘空间 ②索引提高了查询速度,但也降低更新表的速度,每次更新段,都要调整索引信息 注数据变化之后索引结构也是需要发生改变, 所以添加索引也是需要有规则的.3.索引的原理 索引类似于书的目录,通过目录可以快速的定位到数据的物理位置.4.什么是索引 索引是帮助mysql高效获取数据的排好序的快速查找的数据结构 在向数据库中插入数据时,mysql自动会为主键创建一个索引树,通过主键查询时,先在主键索引树上查询,可以提高查询效率,找到主键后,就可以快速定位到数据.5.索引分类①主键索引 一个表中只能有一个主键, primary key
创建主键索引ALTER TABLE 表名 add PRIMARY KEY 表名(列名);//设定为主键后数据库会自动建立索引删除主键索引ALTER TABLE 表名 drop PRIMARY KEY ;
②单值索引一个索引只包含单个列一个表可以有多个单列索引
创建单值索引 CREATE INDEX 索引名 ON 表名(列名);删除索引DROP INDEX 索引名;
③组合索引(符合索引)一个索引中可以包含多个列(建议)
创建复合索引 CREATE INDEX 索引名 ON 表名(列1,列2...);删除索引DROP INDEX 索引名 ON 表名;
组合索引最左前缀原则使用组合索引时,需要在查询条件中用到最左侧列,否则索引失效.
eg 列如表中有a,b,c3列,为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’’ 索引不生效
④全文索引 在mysql中 like 模糊查询导致索引失效,这时就可以使用全文索引来解决这个问题
全局索引使用CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)WITH PARSER ngram;SELECT 结果 FROM 表名 WHEREMATCH(列名)AGAINST(‘搜索词)查看索引SHOWINDEX FROM 表名;
6.索引创建原则①哪些情况建议添加索引 主键自动建立唯一索引 作为查询条件的字段应该创建索引(where 后面的语句) 尽量使用联合索引减少单列索引 针对于数据量较大且查询比较频繁的表建立索引。 查询中排序的字段分组中的字段,若通过索引去访问将大大提高排序速②哪些情况不应该加索引 表记录太少 经常增删改的表 Where条件里用不到的字段不创建索引 数据重复且分布平均的表字段因此应该只为最经常查询和最经常排序的数 据列建立索引某个数据列包含许多重复的内容建立索引没有太大实际效果7.索引数据结构mysql中索引数据结构使用的是B树B树优点: 一个节点中可以存储多个数据, 非叶子节点中不存储表数据,只存储索引数据,这样一个节点中就可以存储更多索引数据 数据都存储在叶子节点, 通过主键查询,找到了索引,就找打到了数据 叶子节点之间也是建立了连接, 所以非常适合范围查询8.回表查询 在使用非主键索引查询数据时,例如通过学号查询数据,先在学号索引树查询,查询到之后,还需要去主键索引树查询,因为数据都挂载在主键索引树下,需要回表二次查询,实际开发中,尽量减少回表查询.四.数据库事务 数据库事务是数据对一次连接过程发送的多条sql执行进行管理,这多条sql要么都执行,要么都不执行. 当一次连接过程中,所有操作都执行没有问题时,向数据库提交事务,数据库才真正的执行sql.
eg 转账: 对用户来说转账是一个整体的操作:包含减钱和加钱, 必须要保证这多条sql要么都成功执行要么都失败sql1 从A账户减钱//异常sql2 向B账户加钱提交事务
1.事务特性 事务是必须满足4个条件原子性Atomicity或称不可 分割性、一致性Consistency、隔离性Isolation又称独立性、持久性Durability。 原子性: 不可拆分 要么都执行,要么都不执行 持久性: 事务一旦提交后,不能撤销,保证数据持久保存 隔离性: 数据库运行多个事务同时对数据库数据进行操作,操作时给我们提供了4种隔离级别进行选择, 保证操作之间相互之间的关系 一致性: 数据完整性 经过多次转账操作后, 最终的结果需要和我们预期结果是一致2.事务隔离级别
查看隔离级别
SELECT session.transaction_isolation,transaction_isolation
①读 未提交: 一个事务读到另一个事务还未提交的数据这会带来脏读幻读不可重复读问题
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
②读 已提交: 一个事务只能读到另一个事务提交后的数据避免了脏读仍然存在不可以重复读和幻读问题 不可重复读问题: 在同 一个事务中,读取相同的数据两次,结果两次结果不一样
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
③可 重复读: 在同 一个事务中,读取相同的数据两次,结果两次结果一样 可重复读也称为快照读, 底层在第一次读取的时候,进行拍照, 在同一个事物中,第二次读的时候,直接读取快照,解决不可重复读问题,部分还存在幻读问题 幻读问题: 在同同一个事物中,查询两次,两次的数量不一样 可重复读隔离级别 对于普通的查询sql解决了幻读问题对于查询语句后面添加了for update的语句,仍然存在幻读问题
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
④串行化: 一次只能允许一个事务 操作(多个事务对同一条数据) 可以解决以上问题,但是效率低
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
五.mysql中的锁机制1.全局锁锁住整个数据库, 只允许查询操作一般在备份数据库时使用.
添加全局锁FLUSH TABLES WITH READ LOCK释放全局锁UNLOCK TABLES;
2.表锁 锁着整表, myisam引擎默认只支持表锁, 一次只允许一个对该表事务进行操作.3.行级锁行锁 : 精确的锁定操作的那一行数据 例如 update test set age 20 where id 1; 锁住id1的这条记录间隙锁: 锁定一个区间 例如 update test set age 20 where id 1 and id 5 锁定了id2,3,4的记录行锁又可以分为:共享锁:一般给查询语句添加,当一条查询语句添加了共享锁后, 允许其他事同时务读, 但是不允许其他事务为该条记录加排他锁.排他锁:新增,修改,删除默认加排他锁查询语句也可以加排他锁, select ..... for update
