修改wordpress自带小工具,搜索seo是什么意思,昌邑微信网站建设公司,给单位做网站需要备案吗一、mysql介绍
1、InnoDB引擎
mysql5.5.8版本开始后。InnoDB引擎就是默认存储引擎#xff0c;本文介绍知识点也都是围绕该引擎展开。
知识点1聚集存储
InnoDB引擎采用聚集存储#xff0c;即每张表的存储都是主键的顺序进行存放#xff0c;也就是每行存储的物理顺序和主键…一、mysql介绍
1、InnoDB引擎
mysql5.5.8版本开始后。InnoDB引擎就是默认存储引擎本文介绍知识点也都是围绕该引擎展开。
知识点1聚集存储
InnoDB引擎采用聚集存储即每张表的存储都是主键的顺序进行存放也就是每行存储的物理顺序和主键顺序相同如果未指定主键引擎会为每张表生成一个6字节的rowid作为主键。相对于非聚集存储聚集存储会同时存储索引和数据。 知识点2 B树索引
InnoDB引擎采用B树索引同样也是磁盘和存储工具设计的一种数据结构它是一种平衡查找树它在查找插入、修改方面的时间复杂度都稳定为 O(logn)。相对于平衡二叉树节点可以存储多个元素因此整体可以存储较多的数据并且树的高度也会矮可以减少磁盘IO提高检索效率。
注: 树结构既有数组结构检索的速度又有链表结构增删改的速度
1.1、索引数据结构
按聚集存储方式的B树索引数据结构如下 a、非叶子节点不存储data只存储索引且可以存储多个索引字段 b、叶子节点既存储data也存储索引即主键此时叶子结点覆盖所有数据 c、叶子节点用双向指针连接提高区间访问的性能
总得来说这个数据结构带来了空间和时间上成本空间上每建立一个索引都要为它建立一棵 B 树时间上每次对表中的数据进行增、删、改操作时都需要去修改各个 B 树索引。
注: 非聚集存储方式相对于聚集存储方式的最大区别这里叶子节点存储的data变更为数据地址
1.2、索引适用条件
从1.1原理可知一个索引一个B树那么索引天然是排序的、分组的。若非叶子存储多个索引字段需遵循最左匹配原则。那么索引适用条件如下:
假设t_student表主键为id联合索引为idx_name_age 适用条件 参考原理 示例 全值匹配 索引检索 select * from t_student where name zhangsan 最左匹配 多个索引时字段按顺序匹配 错误示例 select * from t_student where age 18 and age 30 范围值匹配 索引排序 select * from t_student where name zhangsan and name lisi 列前缀匹配 索引排序例如字符串已经按字母排序好此时只能匹配前缀 select * from t_student where name list zhang% 排序匹配 索引排序 select * from t_student order by age 分组配置 索引分组 select * from t_student order by name 混合匹配 索引检索、索引排序 select * from t_student where name zhangsan and age 18 and age 30 注: 任意条件仅限一个字段即不能新增nick_name精确匹配或birthday范围匹配 1.3、索引失效场景
结合1.1和1.2以及日常使用场景总结索引失效场景如下
假设t_student表主键为id联合索引为idx_name_age 失效场景 参考原理 示例 字段类型不匹配 索引检索 select * from t_student where age zhangsan 索引字段条件不匹配 索引检索(即or出现未索引字段) select * from t_student where name zhangsan or nick_name zs 通配符不匹配 索引排序 同1.2 联合索引不匹配 多个索引时字段按顺序匹配 同1.2 运算符不匹配 索引排序仅支持大于、小于、等于 ! 、 、not in、、-、*、/、is null、is not null、字段比较 排序不匹配 索引排序 禁止使用asc、desc 通过explain语句判断索引是否生效 EXPLAIN SELECT * FROM table_name WHERE column_name value; 示例1 and条件—索引命中即如图key为命中索引 示例2 or条件—索引未命中即如图key为空 2、锁
2.1、定义
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。 注
这里的共享资源不仅仅是行记录还可以是表数据、操作缓冲池数据。
这里我们关注的锁是lock。lock的对象是事务用来锁定的是数据库中的对象如表、页、行。并且一般lock的对象仅事务commit或rollback后进行释放。
2.2、锁类型
共享锁(S Lock): 允许事务读一行数据
排他锁(X Lock): 允许事务删除或更新一行数据
意向共享锁(IS Lock): 事务想要获得一张表中某几行的共享锁
意向排他锁(IX Lock): 事务想要获得一张表中某几行的排他锁
注:
为了支撑在不同粒度(表、页)上加锁并减少锁开销引入意向锁意向锁锁对某种资源的意向可通过意向锁提前判断是否有冲突减少遍历所有行锁的开销。
页是mysql的数据存储单位SHOW VARIABLES LIKE innodb_page_size可查看页大小 实践分享 考虑到select默认不加锁而delete/update/insert默认加排他锁可以通过lock in share mode主动加上S Lockfor update主动加上X Lock 监控可能存在的锁问题 select * from information_schema.INNODB_TRX; select * from information_schema.INNODB_LOCKS; select * from information_schema.INNODB_LOCKS_WAITS; 2.3、锁算法
Record Lock: 单个行记录上的锁
适用场景sql语句查询条件字段仅主键索引
Gap Lock间隙锁锁定一个范围但不包含记录本身
适用场景sql语句查询条件字段仅普通索引
注: 插入意向锁(Insert Intention Locks),提高并发插入的性能允许多事务在同一间隙内插入数据而不会相互阻塞
Next key Lock锁定一个范围但包含记录本身默认算法
适用场景sql语句查询条件字段既有主键索引又有普通索引 2.4、死锁定义
死锁上指两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象。死锁概率与事务数量、每个事务的操作数量、每个事务操作的数据量息息相关所以需要在业务上减少事务数量、每个事务的操作数量扩大每个事务操作的数据量范围。
3、事务
3.1、定义
事务同样是数据库区别于文件系统的重要特性来保障事务里面所有操作ACID特性即原子性、一致性、隔离性、持久性。
3.2、类型
默认事务类型READ REPEATABLE
4、设计规范
4.1、设计原则
a基本原则 1、第一范式 即每个列遵循原子性 举例人的多个属性不能都放在一列 2、第二范式 即每个表遵循模块化 举例订单模块和产品模块分开即同一张表只能依赖一个主键(或负荷主键) 3、第三范式 即每个列遵循冗余性 举例单价和总价不应该同时出现班级和老师不应该同时出现 总结需求性能范式为了性能/需求该冗余还是得冗余为了成本至少遵循第三范式 b进阶原则(单表数据量过大怎么解决) 4.2、阿里建表规范 1.【强制】表达是与否概念的字段必须使用is_xxx的方式命名数据类型是unsigned tinyint1表示是0表示否此规则同样适用于odps建表。 说明任何字段如果为非负数必须是unsigned。 2.【强制】表名、字段名必须使用小写字母或数字禁止出现数字开头禁止两个下划线中间只 出现数字。数据库字段名的修改代价很大因为无法进行预发布所以字段名称需要慎重考虑。 正例getter_admintask_configlevel3_name 反例GetterAdmintaskConfiglevel_3_name 3.【强制】表名不使用复数名词。 说明表名应该仅仅表示表里面的实体内容不应该表示实体数量对应于DO类名也是单数形式符合表达习惯。 4.【强制】禁用保留字如desc、range、match、delayed等参考官方保留字。 5.【强制】唯一索引名为uk_字段名普通索引名则为idx_字段名。 说明uk_即unique keyidx_即index的简称。 6.【强制】小数类型为decimal禁止使用float和double。 说明float和double在存储的时候存在精度损失的问题很可能在值的比较时得到不正确的结果。如果存储的数据范围超过decimal的范围建议将数据拆成整数和小数分开存储。 7.【强制】如果存储的字符串长度几乎相等使用CHAR定长字符串类型。 8.【强制】varchar是可变长字符串不预先分配存储空间长度不要超过5000如果存储长度大于此值定义字段类型为TEXT独立出来一张表用主键来对应避免影响其它字段索引效率。 9.【强制】表必备三字段id, gmt_create, gmt_modified。 说明其中id必为主键类型为unsigned bigint、单表时自增、步长为1gmt_create, gmt_modified的类型均为date_time类型。 10.【推荐】表的命名最好是加上“业务名称_表的作用”。 正例tiger_task / tiger_reader / mpp_config 11.【推荐】库名与应用名称尽量一致。 12.【推荐】如果修改字段含义或对字段表示的状态追加时需要及时更新字段注释。 13.【推荐】字段允许适当冗余以提高性能但是必须考虑数据同步的情况。冗余字段应遵循 1不是频繁修改的字段。 2不是varchar超长字段更不能是text字段。 正例各业务线经常冗余存储商品名称避免查询时需要调用IC服务获取。 14.【推荐】单表行数超过500万行或者单表容量超过2GB才推荐进行分库分表。 说明如果预计三年后的数据量根本达不到这个级别请不要在创建表时就分库分表。 15.【参考】合适的字符存储长度不但节约数据库表空间、节约索引存储更重要的是提升检索速度。 正例人的年龄用unsigned tinyint表示范围0-255人的寿命不会超过255岁海龟就必须是smallint但如果是太阳的年龄就必须是int如果是所有恒星的年龄都加起来那么就必须使用bigint
5、案例分享
5.1、慢查询
开启慢查询日志 [mysqld] slow_query_log 1 slow_query_log_file /path/to/mysql-slow.log #单位秒 long_query_time 0.1 模拟慢查询 第一步 批量插入100万条数据 第二步 以未加索引字段nick_name为条件进行查询 慢查询日志示例 Time Id Command Argument # Time: 2024-08-20T04:43:20.250382Z # UserHost: root[root] localhost [::1] Id: 2 # Query_time: 0.537167 Lock_time: 0.017117 Rows_sent: 0 Rows_examined: 1000000 use food_db; SET timestamp1724129000; SELECT * FROM t_student where nick_name aaa LIMIT 0, 10; 5.2、死锁
模拟死锁 会话1 begin; //排它锁 select * from t_student where id4 for update; //产生死锁 insert into t_student values(3,’test’,18) 会话2 begin; //共享锁 select * from t_student where id4 lock in share mode; --等待 提示:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 查询死锁情况
select * from information_schema.INNODB_LOCKS; lock_data是主键记录lock_page是页序号lock_rec是记录序号
注: mysql8.0版本后可使用select * from performance_schema.data_locks
6、参考书籍
《MySQL技术内幕 InnoDB存储引擎 第2版》
