随缘记录一些MySQL问题

记录一些mysql的问题

一、MySQL自增ID用完了会怎样？

• 解答
  情况分两种:得看有主键和没有主键两种情况
  1）表有主键，且吧主键设置为自增，int类型（MySQL中int占用四个字节(32位)，范围是【-2147483648，2147483647】），当插入一个ID为最大值2147483647的值后，再次插入数据，会造成主键冲突
  2）没有主键:InnoDB会自动创建一个不可见的，长度为6字节的row_id，默认是无符号的，所以最大长度是2^48-1。（实际上InnoDB维护了一个全局的dictsys.row_id，所以未定义主键的表都共享该row_id，并不是单表共享，每次插入一条数据，都把全局row_id当作主键id，然后全局row_id加1）

• 实际方法
  1）可以把主键类型改为bigint，也就是8个字节。这样能存储的最大数据量就是2^64 -1，对于所有场景几乎都是够用的
  2）当row_id达到最大值后会从0重新开始算，前面插入的数据就会被后插入的数据覆盖，且不会报错（当达到最大值后，新数据覆盖旧数据），尽量给表设置主键

二、自增主键的好处和自增主键存在哪里？自增主键一定是连续的吗？

1）自增主键好处：自增主键可以让主键索引尽量的保持递增顺序插入，避免了页分裂，是因此索引更加紧凑，查询的时候效率更高
2）不同引擎对于子增值的保存策略不同，M有ISAM引擎的自增值保存在数据文件中。
①在MySQL8.0前，InnoDB引擎的自增值是存在内存中，但是每次重启后内存这个值就丢失了，所以每次重启后第一次打开表，就会找自增值的最大值max(id)，然后将最大值加1作为这个表的自增值
②在MySQL8.0版本会将子增值的变更记录在redo log中，重启时依靠redo log恢复
3）自增主键一定是连续的吗？
不一定，有几种情况会导致自增主键不连续。
①唯一键冲突导致自增主键不连续。当我们想一个和自增主键的InnoDB表中插入数据的时候，如果违反表中定义的唯一索引的唯一约束，会导致插入数据失败。此时表的自增主键的键值是会想后加1滚动的
②事务回滚导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果显示开启了事务，此时表的自增值也会发生滚动，而接下来新插的数据也不能使用滚动过的自增值，而是重新申请一个新的自增值
③批量插入导致自增值不连续。MySQL有个批量申请自增id的策略：语句执行过程中，第一次申请自增id，分配1个自增id；1个用完后，第二次申请会分配2个自增id；两个用完后，第三次盛情，会分配4个自增id；依次类推。如果下一次事务再次插入数据的时候，则会基于上一个事务申请后自增值基础上再申请。此时就会出现自增值不连续的情况出现。
④自增步长不是1，也会导致自增主键不连续

三、mysql有那些锁？

• 按锁粒度分类，有行、表和页级锁
  1）行级锁粒度最细，只针对当前操作的某一行进行加锁，大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大
  2）表级锁是MySQL中锁定粒度最大的，表示对当前操作整张表枷锁，实现简单，资源消耗少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定
  3）页级锁：锁粒度介于行和表锁之间的锁。表级锁速度快，但冲突多；行级冲突少，但速度慢。因此，采取折衷的页级锁，一次锁定相邻的一组记录

• 按锁级别分类，有共享锁、排他锁和意向锁
  1）共享锁（读锁）：读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到以释放所有共享锁
  2）排他锁（写锁、独占锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加上排他锁后，则其他事务不能再对A加所任类型的读锁。获准排他锁的事务技能读数据，又能修改数据.
  3）意向锁（表级锁）：主要是为了在一个事务中揭示下一行要被请求锁的类型。InnoDB中的两个表锁：（意向锁是InnoDB加的，不需要用户干预）
  ①意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先获得该表的IS锁
  ②意向排他锁（IX）：表示事务准备数据行加入排他锁，说明事务在一个数据加排他锁前必须先取得该表的IX锁

①对于INSERT、UPDATE和DELETE，InnoDB会自动给涉及的数据加排他锁
②对于一般的SELECT语句，InnoDB不会加任何锁
③事务可以通过以下语句显示加共享锁或排他锁
共享锁:
SELECT … LOCK IN SHARE MODE;
排他锁：
SELECT … FOR UPDATE;

四、mysql的游戏邮件数据不跟据人物登录捞取引起的内存爆满

• 前提
  玩家的单个邮件数据在MySQL大概有210字节，当每个人的模板邮件有100封时，每个人的邮件数据就达到了21KB,不包括大型的邮件64K一封。但这是在mysql内部磁盘存储占用的空间，但是当mysql的数据比如说varchar(512)字节捞到内存时，字符串占用的内存就是实打实的512字节。由此以来，我司在真正生产环境下用到的机器内存是4到8G，那么不能起服的时候就把玩家邮件全部都捞出来，内存会被挤爆

• 系统背景
  1）32位程序的寻址能力是2^32，也就是4G。对于32位程序只能申请到4G的内存。而且这4G内存中，在windows下有2G，linux下有1G是保留给内核态使用，用户态无法访问。故只能分配2G、3G的内存使用。
  2）但是，如果系统是64位的话，且有8G内存，那么用户申请的内存就能达到32位机器的顶峰，能实打实的用到4G，毕竟系统有64位的寻址能力没必要再去和你的小底盘上抢资源

• 导致的问题
  当玩家邮件不跟玩家一起捞出来时，全部邮件数据一起捞到内存，占用内存1.8G，当时机器就宕机了，所以邮件数据

五、为什么数据库字段建议设置为NOT NULL？

• 原因
  1）如果不设置NOT NULL，那么后插入数据的时候默认值就是NULL。（NULL和NOT NULL使用空值表示的意义是不一样的，NULL可以认为这一列的值是未知的，空值则可以认为我们知道这个值，只不过他是空的而已）举例：比如成绩字段，0表示0分，空表示未知、未设置。因为设置空值会导致程序空指针的问题
  2）导致聚合函数不准确：对于NULL值的列，使用聚合函数的时候会忽略NULL值，会造成统计结果不准确
  3）导致等于号"="失效，对于NULL值的列，是不能使用=表达式进行判断的，会造成统计结果不准确
  4）NULL和其他任何值进行运算都是NULL，包括表达式的值都是NULL。举例比如对值为NULL的字段+1，结果还是NULL
  5）使用NULL值相比于空字符值会占用更多的空间(一个bit的标志位)

六、一些sql语句题

1）查询01课程比02课程成绩高的学生信息及课程分数

#学生表
create table Student(
Sid varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10));

#教师表
create table Teacher(
Tid    varchar(10),
Tname  varchar(10));

#科目表
create table Course(
Cid    varchar(10),
Cname  varchar(10),
Tid    varchar(10));

#成绩表
create table SC(
Sid     varchar(10),
Cid     varchar(10),
Score   decimal(18,1));  //总长度为18，小数位数占一位的数值
-------------------------------------
insert into Student values('01','赵雷','1990-01-01','男');
insert into Student values('02','钱电','1990-12-21','男');
insert into Student values('03','孙凤','1990-05-20','男');
insert into SC values('02','03',80);
insert into SC values('03','01',76);
insert into SC values('07','02',87);
.........
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

①通过主键Sid连接学生表和成绩表(sc是主表，)

select * 
from sc a
left join student d
	on a.sid = d.sid;
1
2
3
4

②要对同一学生，那就Sid相同，Cid不同进行关联

select *
from sc a
left join student d
	on a.sid = d.sid
inner join sc b
	on a.sid = b.sid and a.Cid != b.Cid;
1
2
3
4
5
6

要求是01课程和02课程

select *
from sc a
left join student d
	on a.sid = d.sid
inner join sc b
	on a.sid = b.sid and a.Cid = 01 and b.Cid = 02;
1
2
3
4
5
6

③01成绩比02成绩高

select *
from sc a
left join student d
	on a.sid = d.sid
inner join sc b
	on a.sid = d.sid and a.Cid = 01 and b.Cid = 02
where a.score > b.score;
1
2
3
4
5
6
7

或这么写

select * 
from Student a
inner join sc b
	a.Sid = b.Sid
inner join sc c
	a.Sid = c.Sid and b.id = 01 and c.Sid = 02
where b.score > c.score;
1
2
3
4
5
6
7

2）查询同时存在01课程和02课程的情况

select * 
from (select * from sc where Cid = '01') a
inner join (select * from sc where Cid = '02') b
where a.Sid = b.Sid;
1
2
3
4

或

select * 
from  sc  a
inner join  sc  b
on a.Sid = b.Sid
where a.Cid = '01' and b.Cid = '02';
1
2
3
4
5

3）查询存在’01’课程但可能不存在’02’课程的情况

先左连接，因为01课程肯定存在

select * 
from (select *  from sc a where a.Cid='01' )
left join sc b
on a.Sid = b.Sid and b.Cid = '02';
1
2
3
4

4）查询不存在’01’课程但存在’02’课程的情况

select * 
from (select * from sc where Sid not in (select Sid from sc where Cid = '01'))
where Cid = '02';
1
2
3

或

select * 
from sc a 
where Sid not in (select * from sc where Cid = '01')
and Cid = '02';
1
2
3
4

5）查询平均成绩大于等于60分的同学学生编号和学生姓名和平均成绩

①先找出大于60的学生信息表（Sid）
②再用这个表去对比Sid匹配

select * 
    a.Sid,
    a.Sname,
    b.avg_score
from student a
inner join
(select 
    Sid
    avg(score) as avg_score
from sc 
group by Sid having avg(score) >= 60 ) b
where a.Sid = b.Sid;
1
2
3
4
5
6
7
8
9
10
11
12

6）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为null）

①先出总成绩和选课总数
②再根据学号Sid去匹配名字和成绩

select 
    a.Sid,
    a.Sname,
    b.cons,
    b.sum_score
from sc a
left join
(select 
    Sid,
    count(Cid) as cons,
    sum(score) as sum_score
from sc 
group by Sid)b
on a.Sid = b.Sid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7）查询“李”姓老师的数量

select count(1) as cons from Teacher  where tname like '李%';
1

8）查询接受过张三老师授课同学的信息

tid: 教师ID
sid:学生ID

①得到教师和课程的关系

select * from course a inner join teacher b on a.Tid = b.Tid;
1

②得到老师和成绩的关系

select * from sc a inner join (select a.*,b.tname from course a inner join
teacher b on a.tid=b.tid) b  on a.cid = b.cid; //课程划分
1
2

③得到学生和教师的关系

select * from student a inner join
(②
select * from sc a.*,b.tname,b.tid,b.cname. inner join (select a.*,b.tname from course a inner join
teacher b on a.tid=b.tid) b  on a.cid = b.cid;

) b
on a.sid=b.sid;
1
2
3
4
5
6
7

④得到张三老师的学生信息

select * from student a
inner join
(③)
where tname = '张三';
1
2
3
4

9）查询没有学完所有课程的同学信息

①学生表和成绩表交叉

select * from student a inner join sc b on a.sid=b.sid  
1

②以课程表分类

group by a.sid
1

③统计下总课程数

having count(b.sid) <(select count(cid) from course);
1

合起来

select * from student a inner join sc b on a.sid=b.sid  
group by a.sid
having count(b.sid) <(select count(cid) from course);
1
2
3

连接传送门