MySQL
MySQL学习笔记
筝子果
野生Java
展开
-
使用java通过固定的excel模板自动生成数据库表的ddl建表语句
有时候要建很多表,一个个复制字段弄太麻烦了,为了提高点工作效率,写了个小工具通过固定的excel模板自动生成ddl建表语句,不是很完美,有需要再完善maven依赖 <!--核心jar包--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId>原创 2022-03-27 21:18:03 · 2935 阅读 · 2 评论 -
MySQL索引是什么,什么时候需要创建索引,创建索引需要注意些什么
索引是什么索引相当于一本字典的目录,当你需要查找一个字的释义时,需要先通过拼音或者偏旁部首等,查到该字所在的页码,再通过页码找到释义所在的书页,索引的功能就类似于目录的功能。索引的作用1、大大减少服务器需要扫描的行数。2、帮助服务器避免排序和临时表。3、可以将随机IO变为顺序IO。注意对于小表,大部分情况下简单的全表扫描更高效。对于中到大型的表,索引非常有效。对于特大型的表,维护索引的代价比较大,建议分区。创建索引时的注意事项索引的创建尽量避免选择频繁修改的字段,那些在where,grou原创 2022-03-24 16:42:40 · 1411 阅读 · 0 评论 -
Docker部署MySQL7,配置基于日志复制的主备集群
主库docker命令docker run -d -p 3308:3306 --restart=always --privileged=true -v /docker/mysql7_master/data:/var/lib/mysql -v /docker/mysql7_master/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 --name mysql7_master mysql:5.7 --character-set-server=utf原创 2022-02-21 23:48:54 · 910 阅读 · 0 评论 -
MySQL数据库优化
表设计使用小而简单的字段类型字符型设置满足需要的最小字段,过长会浪费内存作为关联条件的字段尽量设置为相同的数据类型字段最好设置非null,因为这可能会导致=判断,函数等结果不符合预期索引的创建尽量避免选择频繁修改的字段,那些在where,group by,order by中出现的字段应该优先考虑建立索引不能建立过多索引,会影响数据增删改效率。建立索引时,应该尽量扩展已有索引而不是一来就新建索引创建复合索引时,应该考虑字段的顺序,根据经验值把选择性高的字段放在前面,但是实际过程中我们还应该考虑到原创 2021-12-20 19:32:35 · 588 阅读 · 0 评论 -
MySQL中如何使null=null成立
在多表联查的on条件中可能会出现null=null的情况,如果用=判断则null=null返回的是false,用<=>判断则可以避免这种问题两种关键字进行对比select case when null = null then 'true' else 'false' end 等于关键字;select case when null <=> null then 'true' else 'false' end 改变关键字;...原创 2021-12-20 14:42:50 · 1747 阅读 · 0 评论 -
MySQL中游标的使用
游标可用于接收查询语句的结果集,并遍历结果集获取每条记录进行处理drop procedure if exists student_pro; CREATE PROCEDURE student_pro()BEGIN-- 定义接收变量DECLARE nam varchar(20);DECLARE ag int;DECLARE sid int;DECLARE done int; -- 创建游标接收结果集DECLARE all_data CURSOR FORSELECT id,`name`,原创 2021-11-24 20:16:50 · 917 阅读 · 0 评论 -
MySQL对表进行KEY分区,新增,减少,删除和取消等操作
引用MySQL表RANGE分区,新增,删除,重组,效率对比这篇文章的表作为示例。文章目录KEY分区管理创建表时进行分区为现有数据的表进行分区常规分区线性分区新增分区减少分区查看分区的数据量删除分区取消分区KEY分区管理key分区和hash分区比较相似。创建表时进行分区CREATE TABLE `student_1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char(2) NOT原创 2021-11-22 20:30:13 · 1180 阅读 · 0 评论 -
MySQL对表进行HASH分区,新增,减少,删除和取消等操作
引用MySQL表RANGE分区,新增,删除,重组,效率对比这篇文章的表作为示例。文章目录HASH分区管理创建表时进行分区为现有数据的表进行分区常规分区线性分区新增分区减少分区查看分区的数据量删除分区取消分区HASH分区管理创建表时进行分区CREATE TABLE `student_1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char(2) NOT NULL, `AGE` i原创 2021-11-22 20:12:29 · 1838 阅读 · 0 评论 -
MySQL表LIST分区,新增,删除,重组等
引用MySQL表RANGE分区,新增,删除,重组,效率对比这篇文章的表作为示例。LIST分区和RANGE分区很相似。文章目录LIST分区管理创建表时进行分区为现有数据的表进行分区新增分区重组分区合并分区拆分分区查看分区的数据量删除分区取消分区LIST分区管理创建表时进行分区CREATE TABLE `student_1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char(2)原创 2021-11-21 22:02:17 · 1107 阅读 · 0 评论 -
MySQL表RANGE分区,新增,删除,重组,效率对比
可以使用分区的场景分区的限制为什么要使用分区,比如表数据量远大于内存时,索引在空间和维护上的消耗很大,即使用到索引,数据也不是按照想要的方式聚集,而且会有大量的碎片产生,最终导致一个查询产生成千上万的随机IO。...原创 2021-11-21 15:40:13 · 1583 阅读 · 2 评论 -
MySQL分区概念,使用场景以及限制
文章目录分区的概念分区使用场景分区的限制为什么要使用分区分区的概念分区表是一个独立的逻辑表,但是底层是由多个物理子表组成。分区的主要目的是将数据按照一个较粗的粒度分布在不同的表中,能够快速过滤掉大量数据。分区使用场景分区的限制为什么要使用分区比如表数据量远大于内存时,索引在空间和维护上的消耗很大,即使用到索引,数据可能也不是按照想要的方式聚集,而且会有大量的碎片产生,最终导致一个查询产生成千上万的随机IO,效率低下,使用分区我们就能快速过滤掉大量不需要的数据,从而提高效率。...原创 2021-11-21 15:13:48 · 614 阅读 · 0 评论 -
MySQL的MVCC原理,可见性算法流程图
以下针对InnoDB 存储引擎文章目录几个重要组件和属性行隐藏列Read View读视图undo log可见性算法可见性算法流程图几个重要组件和属性行隐藏列DB_TRX_ID:mysql会为每个事务分配一个事务id,该字段记录最后一次插入或更新该行的事务 id。DB_ROLL_PTR:回滚指针,指向该行的 undo log。DB_ROW_ID:当表没有主键且没有唯一非空索引时生成的隐藏主键。Read View读视图三个主要属性为1.m_up_limit_id 最小活跃事务id2.m_l原创 2021-11-17 18:31:07 · 2111 阅读 · 0 评论 -
MySQL的JOIN连接查询原理
假设我们有两张表A和表B,表A有100条数据,表B有10000条数据,现在进行两表的内连接关联查询,MySQL优化器会默认选择数据量小的表A作为驱动表。简单嵌套循环连接 Nested Loop Join 算法(BLJ)如下图所示,该算法下,以A表作为驱动表,每扫描一条A表的记录,需要全量扫描B表进行匹配,扫描行数为100(表A)+ 100 * 10000(100次表B)= 1000100 条基于索引的嵌套循环链接Index Nested-Loop Join(INLJ)如下图所示,该算法下,以A表原创 2021-11-17 14:06:01 · 699 阅读 · 0 评论 -
MySQL最小值函数MIN()优化
基于MySQL中使用延迟关联优化order by加limit超大分页这篇的student来练习如下SQL,grade上面没有建立索引,查询最小值需要0.965sselect min(id) from student where grade='10年级'通过如下方式优化后执行只需0.549s,这种方式可以让mysql扫描尽可能少的记录数select id from student use index(PRIMARY) where grade='10年级' limit 1...原创 2021-11-14 14:24:30 · 1186 阅读 · 0 评论 -
MySQL中使用延迟关联优化order by加limit超大分页
创建一个student表插入200万条数据CREATE TABLE `student` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char(2) NOT NULL, `AGE` int(11) NOT NULL, `CLASS` varchar(10) NOT NULL, `GRADE` varchar(20) NOT NULL, `HOBBY` varchar(100)原创 2021-11-12 22:03:45 · 831 阅读 · 0 评论 -
MySQL通过存储过程循环插入数据
创建一个student表CREATE TABLE `student` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char(2) NOT NULL, `AGE` int(11) NOT NULL, `CLASS` varchar(10) NOT NULL, `GRADE` varchar(20) NOT NULL, `HOBBY` varchar(100) DEFAULT原创 2021-11-12 20:15:00 · 649 阅读 · 0 评论 -
MySQL的ORDER BY排序优化
索引设计时,尽量满足既能满足排序,又能查找行。ORDER BY优化时尽量使用索引排序。只有当索引列的顺序和ORDER BY的字段顺序一致,排序方向也一致,并且索引包含要查找的行,满足最左前缀原则,才能使用索引来对结果排序。例如给student表建立联合索引NAME, AGE, CLASSCREATE TABLE `student` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char原创 2021-11-12 16:21:00 · 1324 阅读 · 0 评论 -
MySQL的GROUP BY怎么优化,GROUP BY进行了隐式排序,怎么禁止隐式排序
EXPLAIN证明GROUP BY进行了隐式排序EXPLAIN SELECT NAME FROM student GROUP BY NAME可以看到Extra一列有Using temporary; Using filesort说明用到了临时表和排序接下来我们通过添加ORDER BY NULL禁止排序EXPLAIN SELECT NAME FROM student GROUP BY NAME ORDER BY NULL可以看到现在只用到了临时表,没有进行排序了所以在需要分组并不需要对结果进原创 2021-11-11 15:53:31 · 952 阅读 · 0 评论 -
MySQL的where子句中or导致索引失效,以及包含or时能使索引生效的情况
结论:包含or时,需要有一个索引包含了所有where用到的字段,这个索引才会生效。否则索引不生效。建表语句CREATE TABLE `course` ( `cno` varchar(20) NOT NULL, `cname` varchar(10) NOT NULL, `tno` varchar(20) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`cno`), KEY `cname` (`cname`) USING BT原创 2021-07-04 21:49:29 · 2236 阅读 · 0 评论 -
mysql查询时给数据结果加一列自增值
select @rownum:=@rownum+1 AS rownum,name from student,(SELECT @rownum:=0) r;原创 2021-11-08 19:54:45 · 1959 阅读 · 1 评论 -
MySQL查看单个表,所有表数据大小
查询表所属schemaSELECT * FROM information_schema.COLUMNS WHERE table_name = 'student';查询表大小select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size from information_schema.tables where table_schema='study' AND table_name='student';...原创 2021-11-08 20:37:49 · 378 阅读 · 0 评论