MYSQL的索引 ( 主键索引、唯一索引、普通索引、全文索引)
MYSQL的索引主要分为主键索引(PRIMARY KEY),唯一索引(UNIQUE) ,普通索引(INDEX)和全文索引(FULLTEXT) 。
主键索引:它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候指定了主键,就会创建主键索引, CREATE INDEX不能用来创建主键索引,使用 ALTER TABLE来代替。
唯一索引:与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须一。
普通索引:这是最基本的索引,它没有任何限制。
全文索引:FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR, VARCHAR和 TEXT列。
个人理解 : 普通索引, 唯一索引等都是 二级索引 ( 辅助索引 ) 不是直接通过主键索引来直接查询
二级索引的问题 :
二级索引:叶子节点中存储主键值,每次查找数据时,根据索引找到叶子节点中的主键值,根据主键值再到聚簇索引中得到完整的 一行记录。
1.相比于叶子节点中存储行指针,二级索引存储主键值会占用更多的空间,那为什么要这样设计呢?InnoDB在移动行时,无需维护二级索引,因为叶子节点中存储的是主键值,而不是指针。
2.那么InnoDB有了聚簇索引,为什么还要有二级索引呢?聚簇索引的叶子节点存储了一行完整的数据,而二级索引只存储了主键值,相比于聚簇索引,占用的空间要少。当我们需要为表建立多个索引时,如果都是聚簇索引,那将占用大量内存空间,所以InnoDB中主键所建立的是聚簇索引,而唯一索引、普通索引、前缀索引等都是二级索引。
3.为什么一般情况下,我们建表的时候都会使用一个自增的id来作为我们的主键?InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的,每插入一条记录,其实都是增加一个叶子节点,如果主键是顺序的,只需要把新增的一条记录存储在上一条记录的后面,当页达到最大填充因子的时候,下一跳记录就会写入新的页中,这种情况下,主键页就会近似于被顺序的记录填满。 若表的主键不是顺序的id,而是无规律数据,比如字符串,InnoDB无法加单的把一行记录插入到索引的最后,而是需要找一个合适的位置(已有数据的中间位置),甚至产生大量的页分裂并且移动大量数据,在寻找合适位置进行插入时,目标页可能不在内存中,这就导致了大量的随机IO操作,影响插入效率。除此之外,大量的页分裂会导致大量的内存碎片。因为主键聚集索引是需要根据 这个主键来构造 B+ 树的 如果自增的话, 数据的插入与查询都会快很多,物理存储顺序与主键顺序一致, 只需要在刚才的最后一条记录后面, 如果不是主键,那么B+ 树就会出现大量的分裂平衡移动, 影响效率.
InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:
(1)如果表定义了PK,则PK就是聚集索引;
(2)如果表没有定义PK,则第一个not NULL unique列是聚集索引;
(3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
事务是逻辑上的一组操作,要么都执行,要么都不执行。
1. 原子性(Atomicity):** 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
2. 一致性(Consistency):** 执行事务后,数据库从一个正确的状态变化到另一个正确的状态;
3. 隔离性(Isolation):** 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
4. 持久性(Durability):** 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者.这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的结果就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.
ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者.这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的结果就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.
一致性是数据库需要靠应用层来实现的一个达到我们预期结果的一个功能, 转账 100 ,对方收到100 ,这个功能数据库并不能帮我们控制, 需要在应用层开发人员来进行控制 . 只要达到我们的预期结果其实也就是实现了一致性, 毕竟程序也只是为了服务人, 服务开发者来满足用户的一个工具 . 那么 现在转账 100, 生活中肯定是要给到对方100, 但是我现在 就给对方转 50 , 剩下的我吃回扣 ( 突然想到, 其实微信的提现扣我手续费不就是如此吗, 难道说它没有保证一致性吗) ,只要实现了我开发者的预期, 其实就是满足了一致性
READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)
oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。
默认系统事务隔离级别是READ COMMITTED,也就是读已提交
SQL Server 默认系统事务隔离级别是read committed,也就是读已提交
A 原子性 由undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的sql
C 一致性 一般由代码层面来保证
I 隔离性 由MVCC来保证
D 持久性 由内存+redo log来保证,mysql修改数据同时在内存和redo log记录这次操作,事务提交的时候通过redo log刷盘,宕 机的时候可以从redo log恢复
存储引擎就是指:表的类型以及表在计算机上的存储方式
MyISAM是MySQL的默认数据库引擎(5.5版之前)。虽然性能极佳,而且提供了大量的特性,包括全文索引、压缩、空间函数等,但MyISAM不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。不过,5.5版本之后,MySQL引入了InnoDB(事务性数据库引擎),MySQL 5.5版本后默认的存储引擎为InnoDB。
两者的对比:
如何选择:
是否要支持事务,如果要请选择innodb,如果不需要可以考虑MyISAM;
如果表中绝大多数都只是读查询,可以考虑MyISAM,如果既有读也有写,请使用InnoDB。
系统奔溃后,MyISAM恢复起来更困难,能否接受;
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM),说明其优势是有目共睹的,如果你不知道用什么,那就用InnoDB,至少不会差。
Innodb 与 myisam Innodb 中的主键索引 用的是聚簇索引 , 以这个主键来建立 B+ 树, 数据的物理存放顺序与索引顺序是一致的, 即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。 所以插入与查询更快(顺序读写磁盘的速度很快) , 非叶子节点存储的是 索引, 叶子节点存储的是 具体主键索引对应的数据, 可以直接获取到所需要的数据 . Innodb 的非聚簇索引, 也就是普通索引, 唯一索引等 , 虽然也是用 B+ 树进行存储的, 但是叶子节点数据并不是这条整个数据, 而是这个普通索引对应的主键, 然后通获取到的主键去进行聚簇索引来查询数据. Myisam 的主键索引与辅助索引, 虽然使用的 B+ 树, B+ 树的非叶子节点也都是索引, 但是叶子节点 data域存储的是数据指针, 而不是innodb 的直接存储的数据信息 .
首先红黑树跟 AVL 树当数据较多的时候, 树的高度比B树高 , 磁盘 IO 次数多, 效率自然相对就较低 .
B 树 因为每个索引节点上 data 域都存储有 数据, 占用更多的空间, 这样索引页所存储的节点就比 B+ 树少, 磁盘 IO次数也就比B+ 多, 效率比 B+ 树低, 还有是B+ 树叶子节点都有指向下一个叶子节点的指针, 链表, 这样在遍历的时候,只需要遍历叶子节点的即可,更快更方便
1. 最左前缀法则 : 如果索引了多列, 遵守最左前缀法则, 指的是查询从索引的最左列开始并且不跳过索引中的列.
2. 不在索引列上做任何操作( 计算 函数 类型转换 ),会导致索引失效而转向全表扫描.
3. 存储引擎不能使用索引中范围条件右边的列(范围之后的索引会失效)
4. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)), 减少select *
5. mysql在使用 != 的时候无法使用索引会导致全表扫描
6. is null , is not null 也无法使用索引
7. like 以通配符 %开头的索引失效会变成全表扫描
8. 字符串不加单引号导致索引失效(隐式的类型转换)
9. 少用 or , 用它也会索引失效
设定步长,比如1-1024张表我们分别设定1-1024的基础步长,这样主键落到不同的表就不会冲突了。
分布式ID,自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种
分表后不使用主键作为查询依据,而是每张表单独新增一个字段作为唯一主键使用,比如订单表订单号是唯一的,不管最终落在哪张表都基于订单号作为查询依据,更新也一样。
1 SELECT [ALL | DISTINCT]
2 {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
3 FROM table_name [as table_alias]
4 [left | right | inner join table_name2] -- 联合查询
5 [WHERE ...] -- 指定结果需满足的条件
6 [GROUP BY ...] -- 指定结果按照哪几个字段来分组
7 [HAVING] -- 过滤分组的记录必须满足的次要条件
8 [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
9 [LIMIT {[offset,]row_count | row_countOFFSET offset}];
10--
指定查询的记录从哪条至哪条
作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条
1 -- # 查看哪些同学参加了考试(学号)
2 SELECT * FROM result; -- 查看考试成绩 去除重复项
3 SELECT studentno FROM result; --
4 SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)
like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
-- IN
28 -- =============================================
29 -- 查询学号为1000,1001,1002的学生姓名
30 SELECT studentno,studentname FROM student
31 WHERE studentno IN (1000,1001,1002);
33 -- 查询地址在北京,南京,河南洛阳的学生
34 SELECT studentno,studentname,address FROM student
35 WHERE address IN ('北京','南京','河南洛阳');
inner join 与 left join 之间的区别
连接查询如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join 查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
MySQL的默认端口号是3306
一:用法和区别
drop:drop table 表名删除内容和定义,并释放空间。执行drop语句,将使此表的结构一起删除。
truncate (清空表中的数据):truncate table 表名删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。truncate不能删除行数据,虽然只删除数据,但是比delete彻底,它只删除表数据。
delete:delete from 表名 (where 列名 = 值)与truncate类似,delete也只删除内容、释放空间但不删除定义;但是delete即可以对行数据进行删除,也可以对整表数据进行删除。
二:注意1.delete语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存,以便进行进行回滚操作。2.执行速度一般来说:drop>truncate>delete3.delete语句是数据库操作语言(dml),这个操作会放到 rollback segement 中,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发。4.truncate、drop 是数据库定义语言(ddl),操作立即生效,原数据不放到 rollback segment 中,不能回滚,操作不触发trigger。5.truncate语句执行以后,id标识列还是按顺序排列,保持连续;而delete语句执行后,ID标识列不连续
其实这个话题是老生常谈,很多人在工作中确实也不会使用外键。包括在阿里的JAVA规范中也有下面这一条
区别一,定长和变长
char 表示定长,长度固定,varchar表示变长,即长度可变。char如果插入的长度小于定义长度时,则用空格填充;varchar小于定义长度时,还是按实际长度存储,插入多长就存多长。
因为其长度固定,char的存取速度还是要比varchar要快得多,方便程序的存储与查找;但是char也为此付出的是空间的代价,因为其长度固定,所以会占据多余的空间,可谓是以空间换取时间效率。varchar则刚好相反,以时间换空间。
区别二,存储的容量不同
对 char 来说,最多能存放的字符个数 255,和编码无关。
而 varchar 呢,最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。
本文发布于:2024-01-31 04:18:21,感谢您对本站的认可!
本文链接:https://www.4u4v.net/it/170664590825401.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
留言与评论(共有 0 条评论) |