Java理论-MySQL
悲观锁和乐观锁的区别，乐观锁适用于什么情况
- 悲观锁：每次访问数据的时候都觉得会有别人修改它，所以每次拿数据时都会上把锁，确保在自己使用的过程中不会被他人访问。
- 乐观锁：每次拿数据的时候都不会上锁，只是在更新数据的时候去判断该数据是否被别人修改过。
大多数的关系型数据库写入操作都是基于悲观锁，缺点在于如果持有锁的客户端运行的很慢，那么等待解锁的客户端被阻塞的时间就越长。 - 乐观锁适用于读多写少的情况，因为在写操作比较频繁的时候，会不断地重试，从而降低性能。
数据库优化
- 建立索引
- 分库分表
- 负载均衡
SQL优化
-
注意通配符中Like的使用 like后面不要写 ‘%name%’，以下写法会造成全表扫描
- select id,name from userinfo where name like '%name%'
- select id,name from userinfo where name like 'name%'
-
避免在where子句中对字段进行函数操作
- select id from userinfo where substring(name,1,6) = 'xiaomi'
- select id from userinfo where datediff(day,datefield,'2017-05-17') >= 0
-
在子查询当中，尽量用exists代替in
select name from userinfo a where id in(select id from userinfo b)
可以改为
select name from userinfo a where exists(select 1 from userinfo b where id = a.id)
下面的查询速度比in查询的要快很多。
-
where子句中尽量不要使用is null 或 is not null对字段进行判断
例如：
select id from userinfo where name is null
尽量在数据库字段中不出现null，如果查询的时候条件为 is null ，索引将不会被使用，造成查询效率低，
因此数据库在设计的时候，尽可能将某个字段可能为空的时候设置默认值，那么查询的时候可以
根据默认值进行查询，比如name字段设置为0，查询语句可以修改为
select id from userinfo where name=0
-
避免在where子句使用or作为链接条件
例如：
select id from userinfo where name='xiaoming' or name='xiaowang'
可以改写为：
select id from userinfo where name = 'xiaoming' union all
select id from userinfo where name = 'xiaowang'
-
避免在 where 子句中使用 != 或 <> 操作符。
例如：
select name from userinfo where id <> 0
说明：数据库在查询时，对 != 或 <> 操作符不会使用索引，
而对于 < 、 <= 、 = 、 > 、 >= 、 BETWEEN AND，数据库才会使用索引。
因此对于上面的查询，正确写法可以改为：
select name from userinfo where id < 0 union all
select name from userinfo where id > 0
-
少用in 或 not in
对于连续的数值范围查询尽量使用BETWEEN AND，例如：
select name from userinfo where id BETWEEN 10 AND 70
-
不要select * from table
mysql查询常见面试题
#创建表
CREATE TABLE if NOT EXISTS users
(
id INT PRIMARY KEY COMMENT 'id',
userid VARCHAR(100) NOT NULL COMMENT '用户编号',
username VARCHAR(100) NOT NULL COMMENT '用户名',
age INT NOT NULL COMMENT '年龄',
sex INT NOT NULL COMMENT '性别',
depid VARCHAR(50) NOT NULL COMMENT '部门id'
)ENGINE=INNODB COMMENT = '用户表';
ALTER TABLE users
MODIFY id int AUTO_INCREMENT COMMENT 'id';
#创建部门表
CREATE TABLE if NOT EXISTS department
(
id INT PRIMARY KEY AUTO_INCREMENT,
depid VARCHAR(100) NOT NULL COMMENT '部门id',
depname VARCHAR(100) NOT NULL COMMENT '部门名字'
)ENGINE = INNODB COMMENT = "部门类型表";
####################插入数据
INSERT INTO users
(
userid,username,age,sex,depid
)
VALUES
('001','管理员',40,1,'101'),
('002','小玲',22,0,'102'),
('003','张三',27,1,'103'),
('004','李四',34,1,'103'),
('004','李四',34,1,'103'),
('005','小王',22,0,'102'),
('006',NULL,33,1,'103');
####################插入数据
INSERT INTO department
(depid,depname)VALUES
('101','系统管理部'),
('102','工商局'),
('103','质监局'),
('104','发改委');
#############练习sql语句##################
#查询年龄最小的人的名字及他的年龄
SELECT username,age FROM users WHERE age in (SELECT MIN(age) FROM users);
#################################################
#查询所有人员的名字，性别以及他们所在部门的名称。
#性别为1时显示男，性别为0时显示女。
#并且按照年龄从大到小排序
SELECT u.username,case when u.sex=1 then '男' when u.sex=0 then '女' END AS `sex`,dp.depname,u.age
FROM users u,department dp
WHERE u.depid = dp.depid
ORDER BY u.age DESC
##############################################
#查询姓名为NULL的人员名字及特们所在部门的名称。
#注意：NULL不是字符串，表示空字符
SELECT u.username,dp.depname
FROM users u,department dp
WHERE u.username is NULL AND u.depid = dp.depid;
#############################################
#查询所有的部门的名称及部门内人员的名字，
#且没有人员的部门也能查出来
SELECT dp.depname,u.username
FROM department dp LEFT JOIN users u
on u.depid = dp.depid
##############################################
#写一条SQL向人员表中插入一条数据，使得这个人的姓名为
#“柳荫”,性别为“女”，userid为“007”，部门为“发改委”
INSERT INTO users
(userid,username,age,sex,depid)VALUES
("007","柳荫",19,"0","104")
################################################
#假设上面表中的人员表有100条数据，请按照sql server
#的分页方式写出查询第70条至第90条数据，按照id排序
SELECT * FROM users
ORDER BY id
LIMIT 69,20
