做网站的公司北京有哪些,电子商务的功能有哪些,便利的合肥网站建设,搜索推广的流程DDL 建表 DML增删改 DQL查询 DCL控制用户权限 存储引擎
MYSQL体系结构 *连接层 *服务层#xff08;DML DDL #xff09; *引擎层#xff08;可插拔#xff09;#xff08;索引在这里#xff0c;不通的引擎 索引结构不同#xff09; *存储层#xff0c;
外键#xff…DDL 建表 DML增删改 DQL查询 DCL控制用户权限 存储引擎
MYSQL体系结构 *连接层 *服务层DML DDL *引擎层可插拔索引在这里不通的引擎 索引结构不同 *存储层
外键外键关系Foreign Key Relationship从表的外键与主表的主键或唯一键相对应。级联更新Cascade Update当主表中的主键或唯一键更新时从表中对应的外键也会自动更新。级联删除Cascade Delete当主表中的主键或唯一键删除时从表中对应的外键也会自动删除或置为NULL。 存储引擎 基于表的创建表的时候 Innodb默认--5.5版本后 myISAM早期默认 Memory
Innodb
*特点 *支持DML 事务 行锁 高并发外键约束保证数据完整和正确*磁盘每个表都有一个表空间文件表结构 数据索引
逻辑存储结构表--段--区1M--页(16K)--行
MYISAMmongodb替代
*不支持事务外键行锁 *支持表锁 *访问速度快 *磁盘有三个文件分别存表结构 数据索引 Memoryredis替代
*内存存放 *hash索引默认 磁盘一个文件表结构 选择引擎 事务-外键-完整性等 Innodb 插入-读取多更新和删除少的 MYISAM 临时Memory 索引
*概述是一种高速获取数据的-有序的数据结构 二叉树先比较 --小的往左 大的往右*优点 高速获取数据 提高排序效率 降低cpu消耗缺点 占用磁盘 影响增删改
索引结构
Btree索引 支持所有引擎 hash索引 支持memoryBtree:中间元素向上分裂 4ket 5指针Btree: *所有的元素都会出现在叶子节点 *所有叶子都会形成一个单向链表
mysql优化后的Btree *增加一个指向相邻叶子节点的链表指针双向形成有序的指针提高访问Hash等值配匹-不能范围-不能排序通常一次查询 很快 *将键值换算成hash值 映射到对应的槽位上然后存在hash表中 *hash冲突碰撞通过链表来解决
Innodb自适应hash功能在指定环境下可以自动构建为什么Innodb选择使用Btree*相对二叉树层级更少查询效率更高 *对于Btree,无论是叶子还是非叶子节点都存储数据这样导致一页中存储的键值和指针减少要保存同样的数据就加树高度导致性能降低 *相对Hash 不能范围-不能排序
索引分类主键索引唯一索引常规索引全文索引查找文中的关键字
innodb索引的存储形式1:聚集索引必须有 且一个主键 结构叶子节点挂的就是row数据2:二级索引 (多个) 结构叶子节点挂的就是idselect * from aaa where name 666( 先二级查询找到id再根据id聚集查询找到数据)回表查询聚集索引选取规则 *优先主键 *第一个唯一索引 *自动生成一个隐藏的Innodb主键索引的Btree高度为多高 *每个节点(包含key,指针6字节)落在磁盘的页16K当中*假设 一行数据大小为1k ,一页可以存16行指针占用6byte, bigint主键key值占用8byte高度为2 第一层 n*8 (n1)*6 16*1024 计算n1170 n就是ket的数量 第二层 1171*16 18730 一万八 如果是三层 1171*1171*16 2千1百万
SQL分析 *查询SQL执行各种操作的频率 show global status like Com____ *查询慢sql日志 默认关闭的 打开并 设置时间 *查看sql执行各个阶段耗时 profile 先查看是否支持*执行计划explain
explain*id 越大越优先执行相同就从上到下执行 *type 连接类型null ,system ,const(主键查询),ref一般查询 ,all * possible_key 可能用到的索引 *key 实际用到的索引 *key_len索引长度 越短越好 *rows查询行数 *filterd 结果和读取比例 越大100%越好 *extra 额外信息
索引使用
避免回表- 避 避免回表免回表 避免回表 避免回表 避免回表 避免回表 避免回表 失效的情况
*最左前缀法则 联合索引 最左的字段一定要存在 不然就失效跳过的也失效 *范围查询失效联合索引 范围查询失效 用 解决 *不要再索引列进行sub等运算 会失效在hwhere 里面 *字符串不加引号 索引失效 *模块查询头不配匹就会失效 *使用or 两边都要有索引 才会生效 *MYSQL评估 走全文比索引快 就不会走索引
SQL提示选择走哪个索引 背景同一个联合索引和一般索引(自动选择 不固定)可能会先走联合索引*use index 建议使用这个索引 *ignore index 不走 *force index 必须走 例子 select * from table use index(id) where id 11 覆盖索引
返回的数据 尽量包含索引 少用select* 很容易回表 *查询返回减少无用字段 多余的字段会导致回表查询索引信息没有包含全部数据 ps-尽量在索引就能回去所有数据
前缀索引
*对大字段部分长度做索引 减少索引体积空间 *选择长度越长越好 最好唯一 单列索引多个只会有快的那一个 联合索引存在多个查询条件 建议使用 考虑建立索引的顺序
设计原则
*数据量大 *查询频繁 *针对 在 where ,order by, group by 的字段 *选择区分度高的 例如id 唯一的 *如果是大字符串的 建议前缀索引 *尽量联合索引 减少空间减少回表尽量覆盖索引 *控制索引的数量会影响增删改 *索引不能为null建表的时候 约束not null, SQL优化
insert优化 *批量插入最大1千 *手动提交事务 *主键顺序插入乱序会产生页分裂*load指令大大批量插入数据百万级别 主键优化 表数据是根据主键顺序组织存放的 称为索引组织表 *因为innodb数据是有序的 乱序插入会 页分裂*数据删除只是标记当删除默认页50%就会页合并 1尽量减少主键长度影响空间和查询二级索引也存有主键 2顺序插入 现在自动增长避免页分裂 3尽量不要用uuid 身份证 做主键 因为他们是无序的且长 4业务操作 避免对主键修改order by 优化 排序有两种 1Using filesort 通过索引或者全表扫描得到数据 放到缓冲区完成排序的 2Using index 通过索引顺序扫描直接得到有序数据速度快优化的目标覆盖索引 *创建索引默认升序 *创建联合索引 优化 1建立合适索引 遵循最左前缀法则 2尽量使用 覆盖索引 3多索引 一个升序 一个降就注意索引创建对应的升降 4缓冲区默认256K大数据可以 调高一点不然会在磁盘操作 慢 group by 优化 *通过索引操作 *最左前缀法则 Limit优化 越往后分页越慢 *思路通过覆盖索引再加子查询 count()优化 *自己记录 *count(*)最快直接统计行数 count(0)填充数字统计行数 快 *count(字段) 统计非nullupdate优化innodb行数锁是针对索引加的不是子记录加如果索引失效 会升级表锁*一定要加索引
视图 *虚拟表只保存sql逻辑 要关联基础表 创建 create view aa1 AS select id from bb 加检查选项 检查选项casaded(默认)向上找local递归找 *视图也可以作为基表检查选项继承 *可以当作一个表来使用 *视图可以更新的条件是 和基础表数据一对一作用 简单简化其他操作 安全数据库授权 部分字段 数据独立屏蔽基础班结构变化带来的影响
存储过程
定义存储在数据库的sql集合 作用简化开发人员工作减少应用和客户段的交互 特点 封装和重用接收参数和返回参数减少网络传输创建 create procedure name {参数列表} begin -----sql end调用 call name {参数列表} 查看 1可以查看某个表有哪些存储过程 2查看存储过程的 语句 3工具routine下面系统变量如提交事务两个 *全局变量gloabl 会话变量:session *查看show 和设置set(默认重启还原)自定义变量一个 在当前会话生效 可以直接赋值 可以查询赋值 没有赋值就是null局部变量 *在begin 和 end 之间生效 声明declare参数 in 默认 out inout 存储函数例如 sum1到N的累加结果 *封装方法可以用存储过程代替
触发器 tirgger
*是和表有关的对象 在表增删改的时候触发指定的sql集合 *只支持行级 old new 操作前后的数据 锁 锁 锁 锁
锁了以后 只读
*全局锁 经典使用全库备份 加锁---备份--解锁*表级锁 *myIsam innodb 都支持 1表锁读锁,写锁 lock tble name read/write 读锁阻塞两个客户端写可读 写锁阻塞其他客户端的读写 2元数据锁系统自动控制 在表有 DML DDL操作的时候 自动加索避免数据冲突 3意向锁-自动 表锁前 先查看有没有通过意向锁检查是否存在行锁在
共享锁 IS读 可以兼容共享 其他不行 排他锁 IX读写都不兼容*行级锁(加载索引)
innodb支持
RU 读未提交 RC读提交 RR 重复读(l临界锁) SE 序列化分类行锁 间隙锁 临界锁*行锁 RC RR级别支持防止幻读 例子锁5或10有索引的时候就自动优化为行锁不然对所有记录加锁-会升级为表锁 *间隙锁gapRR,例子5-10之间6789 防止其他数据加入间隙 产生幻读*临界锁行锁间隙锁 RR例子锁5678910增删改加的是排他锁自动查不加锁可以手动加 Innodb结构 1表空间 2段数据段索引段回滚段 3区1M 包含64页 4页,innodb磁盘管理最小单位16k 5行内存架构先在内存buffer操作 一段时间再更新到磁盘
事务原理 *原子性 undo log *一致性 都由日志来保证redo log undo log *持久性 redo log *隔离性由锁MVCC保证的
MVCC 多版本并发控制!!!!!!!!!!!!!!! 概念 当前读读取最新数据加锁 不能被其他修改 快照读简单select 不加锁 非阻塞。RR第一次查询是 MVCC实现依赖 *快照读*3个隐藏式字段*undo log回滚日志readView#3个字段 1最近修改事务的ID 2回滚指针配合undo log 可以找回上一个版本 3隐藏主键只有再没有主键的情况才会有#undo log回滚日志 #readView *是快照读获取数据的依据 MYcat 概念逻辑库----逻辑表-----分片规则---分片节点
