Mysql增强

字数统计: 4.6k   |   阅读时长≈ 20 分钟

在传统企业中由DBA数据库管理员，写SQL，查询性能调优。

但是让大家只是了解？随着硬件和互联网，大数据的发展。例如：nosql 不仅仅是sql

关系型数据库：Oracle，SqlServer，MySQL

软肋，表的数据的关联关系非常的密切，多表联查，随着数据的增长，查询越来越慢。

数据量有一定限制！

  • Oracle单表可以支持到多少条数据？上亿

  • MySQL 1000万

  • nosql，最低亿级数据以上，mongodb,hbase

MySQL旧的方式在新的形势下被淘汰：
1）视图
2）存储过程，函数
3）触发器

旧的传统的企业，旧的系统还在使用这些旧的技术
我们面试时，会被问。

练习下面两个视图：============================================

1
2
3
4
5
6
7
CREATE VIEW v_goods238 AS
SELECT * FROM t_goods WHERE category_id=238

CREATE VIEW v_cat_goods AS 
SELECT c.name,g.item_type,g.title FROM t_goods_category c
LEFT JOIN t_goods g
ON c.id=g.category_id

mysql增强，大型系统

1、【废除】视图，本质就是一个查询语句，作用把复杂SQL封装起来，调用中直接访问视图名称即可。这样调用者就非常方便。很难SQL就是DBA，系统分析师。

2、【废除】存储过程，本质脚本语言（弱）。性能高（在数据库上直接执行，它的服务器性能是所有服务器中最强）数据库迁移oracle,mysql

3、【废除】函数，本质自定义函数，丰富业务算法（个性）。

数据库迁移oracle,mysql

4、【废除】触发器，本质类似拦截器。也很死板。
数据库迁移oracle,mysql

5、索引，复合索引，最左前缀特性本质，就是提高查询效率

什么时候创建索引？用户抱怨时。
什么字段上创建索引？where,order
主键有索引吗？主键天生就有索引！！！！

1. 数据库编程(了解)

数据库中可以编写并保存一段程序，做数据运算

  • 视图 View
  • 存储过程 Procudure
  • 函数 Function
  • 触发器 Trigger

mysql 数据库编程能力较弱，很多功能无法实现

2. 变量

2.1 用户会话变量

一个客户端与服务器连接期间的变量

1
2
set @v1 = 123;
select @v1;

2.2 局部变量

​ begin … end; 是一对大括号，局部变量只能在 begin 到 end 之间使用，end结束后，变量消失

​ 使用 declare 关键字来定义局部变量

​ delimiter ; 结束符改成;

​ delimiter // 结束符改成//

​ 创建复杂的存储过程中间会用到;结束符，如果几个;放在一起，MySQL只会识别第一个，以后的不会识别，这样会及其不完整

1
2
3
4
5
6
begin
   declare v2 int default 123;
   select v2;
   select @v1;
end//
 （上面无法执行，只是参考）

3. 视图

3.1 简单视图

​ 视图的本质就是一个查询语句

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
32
33
34
35
36
37
38
select * from t_goods where category_id=238

create view v_goods238 as 
select * from t_goods 
where category_id=238;

select * from v_goods238;

DELIMITER $$  /*设定一个标识符，标识结束$$*/

USE `tedu_store`$$	/*打开指定数据库tedu_store*/

DROP VIEW IF EXISTS `v_goods238`$$	/*如果此视图已经存在，就删除*/

create algorithm = undefined definer = `root`@`localhost` sql security
definer VIEW `v_goods238` as
select
  `t_goods`.`id`            AS `id`,
  `t_goods`.`category_id`   AS `category_id`,
  `t_goods`.`item_type`     AS `item_type`,
  `t_goods`.`title`         AS `title`,
  `t_goods`.`sell_point`    AS `sell_point`,
  `t_goods`.`price`         AS `price`,
  `t_goods`.`num`           AS `num`,
  `t_goods`.`barcode`       AS `barcode`,
  `t_goods`.`image`         AS `image`,
  `t_goods`.`status`        AS `status`,
  `t_goods`.`priority`      AS `priority`,
  `t_goods`.`created_time`  AS `created_time`,
  `t_goods`.`modified_time` AS `modified_time`,
  `t_goods`.`created_user`  AS `created_user`,
  `t_goods`.`modified_user` AS `modified_user`
FROM `t_goods`
WHERE (`t_goods`.`category_id` = 238)$$

DELIMITER ;

select * from v_goods238;

3.2 多级关联视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create view v_cat_goods as 
select 
	c.name,
    g.item_type,
    g.title
from 
	t_goods_category c
left join 
	t_goods g
on 
	c.id = g.category_id;
    
select * from v_cat_goods;

视图在大型项目中被废除！！！！
视图过程是把查询所有的记录都查询回来，然后再过滤数据，过滤掉非238.
如果这张表中有一千万数据。数据量大时无法优化。
Java程序可以吗？mybatis它直接处理，返回结果数据。结果就比视图快。

4. 存储过程

存储过程是存储在数据库服务器中的一段过程代码

优点：

Jsp-java-database(proc)

存储过程离数据库最近，所以它执行代码是效率最高的。

Client(brower)-WebServer（tomcat）-DatabaseServer（mysql）

WebServer和DatabaseServer，在企业中，WebServer一般服务器即可，但是DatabaseServer是所有服务器中最好的。甚至大型项目，小型机。Unix+Oracle。

​ 废除：

​ 1） 写数据库代码（存储过程-单独学习语法，无法断点，system.out没有输出，几乎无法调试。DBA），写java代码（人员众多，好调试）。后期维护。不好维护。Java好维护。

​ 2） 存储过程不好写，质量不好控制。

4.1 定义存储过程

1
2
3
4
5
6
7
8
9
10
--  修改结束符
delimiter //

--  创建存储过程p1
create procedure p1()
begin  --  大括号开始
   -- 定义局部变量v2
   declare v2 int default 123;
   select v2; --  显示v2变量的值
end//  --  大括号结束

4.2 调用存储过程

1
call p1()//

4.3 查看存储过程

1
2
3
show procedure status where db='db1'
	查看指定库中的存过程
show create procedure p1

4.4 删除存储过程

1
drop procedure if exists p1//

4.5 存储过程的参数

三种参数：

  • in 输入参数
  • out 输出参数
  • inout 既能输入又能输出

存储过程参数测试

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
--  向学生表和联系方式表同时插入数据
--  1) 插入学生数据
--  2) 得到新插入的自增主键值
--  3) 插入联系方式表
传统方式：
1．	页面填写两张表的数据，学生名称，学生的电话，存在两个表中
2．	学生表自增主键
3．	联系表中和学生表的关联（一对一）

CREATE TABLE `t_student` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t_tel` (
  `stu_id` int(11) NOT NULL,
  `tel` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

DELIMITER //
create procedure p_student_tel
(in name varchar(20),
in tel varchar(20), out id int)
begin
   declare v int;-- 定义临时变量用来存自增主键值
   --  插入学生数据
   insert into t_student(name) values(name);
   --  获得新生成的自增主键值存到v
   set v = last_insert_id();
   --  插入联系方式数据
   insert into t_tel(stu_id,tel) values(v, tel);
   --  自增主键值存到输出参数id
   set id=v;
end//

-- 调用
CALL p_student_tel('刘强东',13010100808,@id);

--  调用
CALL p_student_tel('刘强东',13010100808,@id);
 
select * from t_student;
select * from t_tel;
select @stu_id;

5. 流程控制

5.1 if

1
2
3
4
5
6
7
8
9
10
11
if 条件 then

   代码

end if

if 条件 then
   代码1
else
   代码2
end if

5.2 case

1
2
3
4
5
case
   when 条件1 then ...
   when 条件2 then ...
   else ...
end case
1
2
3
4
5
case 变量或表达式
   when 值1 then ...
   when 值2 then ...
   else
end case

分支判断测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
delimiter //
 
drop procedure if exists p1//
 
create procedure p1(in v int)
begin
   if v=1 then
      select 'v的值是1';
   end if;
   case v
      when 2 then select 'v的值是2';
      when 3 then select 'v的值是3';
      else select 'v的值不是2,3';
   end case;
end//
 
call p1(1) //
call p1(2) //
call p1(3) //
call p1(4) //

5.3 while

1
2
3
while 条件 do
   代码
end while

5.4 loop

1
2
3
4
5
6
7
8
9
--  lp: 循环命名，可以起任意的名字

lp: loop
   ...
   if 条件 then
      leave lp; --  指定离开哪个循环
   end if;
   ...
end loop;

5.5 repeat

1
2
3
repeat
   代码
until 条件 end repeat;

循环测试

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
32
33
34
35
36
37
38
39
40
41
--  新建 tb1 表
use db1 //

drop table if exists tb1 //

create table tb1(
   id int primary key auto_increment,
   num int
)engine=innodb charset=utf8 //

delimiter //
drop procedure if exists p2//
create procedure p2(in n int)
begin
   declare i int default 0;
   --  while
   while i<n do -- i的值从0递增到n-1
      insert into tb1(num) values(i+1);
      set i=i+1;
   end while; 
   --  loop
   set i=0;
   lp: loop
		insert into tb1(num) values(i+1);
		set i=i+1;
		
		if i=n then -- i等于n时退出loop循环
			leave lp;
		end if;
	end loop;
	-- repeat
	set i=0;
	repeat
      insert into tb1(num) values(i+1);
      set i=i+1;
   until i=n end repeat; -- i等于n时退出repeat循环
end//

call p2(10) //

select * from tb1 //

6. 函数

  • 函数有返回值
  • 调用存储过程用call，调用函数直接调

函数为什么不让用？

分布式？又放在Java端

Zookeeper集群管理者，当一台服务的配置更新，zk会自动把配置改变信息更新到其它所有的机器上。

6.1 创建函数

1
2
3
4
5
6
create function fn()
returns varchar(100)
begin
   执行代码运算产生计算结果
   return 计算结果;
end//

6.1.1 求平方的函数 fn_pow

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER //
drop function if exists fn_pow //

create function fn_pow(n int)
returns int -- 函数返回什么类型的数据
begin
   
   set r = n*n;
   return r; -- 向调用位置返回计算结果
end //

select fn_pow(5) //
select *, fn_pow(num) from tb1 //

6.2 查看函数

1
2
3
4
5
show function status;

show function status where db='tedu_store';

show create function fn_pow;

6.3 删除函数

1
drop function if exists fn_pow //

7. 触发器

对表中的数据操作时，可以触发一段代码执行

三个数据操作：

  • insert
  • update
  • delete

两个触发时间：

  • before
  • after

一张表中最多可以有6个触发器

  • before insert
  • after insert
  • before update
  • after update
  • before delete
  • after delete

两个隐含对象

  • new

    ​ 新的数据行
    ​ insert的new：要插入的新行
    ​ update的new：修改后的新行
    ​ delete的new：没有

  • old

    ​ 旧的数据行

    ​ insert的old：没有

    ​ update的old：修改前的旧数据

    ​ delete的old：被删除的旧数据

  • 访问新行或旧行数据：

​ new.id
​ new.name
​ old.age
​ old.tel

7.1 创建触发器

操作商品表insert,before,new

1
2
3
4
5
create trigger 名 触发时间 on 表
for each row
begin
   代码
end;

用触发器自动更新updated字段

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
--  学生表添加 updated字段
alter table t_student
add created datetime //
--  添加触发器，插入数据时自动填入时间
--  before insert
DELIMITER //
CREATE TRIGGER tr_b_i
BEFORE INSERT ON t_student FOR EACH ROW
BEGIN
 SET new.created=NOW();
END //

insert into t_student(name) values('tony') //
 
select * from t_student //

--  添加触发器，修改数据时自动修改时间

alter table t_student
add updated datetime //

DELIMITER //
CREATE TRIGGER tr_b_u
BEFORE UPDATE ON t_student FOR EACH ROW
BEGIN
  SET new.updated = NOW();
END //

UPDATE t_student SET NAME='tina' WHERE stu_id=2
 
select * from t_student //


-- 级联删除。删除主表信息时利用触发器删除子表信息

-- before delete

delimiter //
create trigger tr_b_d
before delete on t_student for each row
begin
	delete from t_tel where stu_id=old.stu_id;
END //

select * from t_student;

select * from t_tel;

delete from t_student where name='a';

7.2 查看触发器

进入系统库 information_schema

use information_schema //

查询 triggers 表中触发器信息

select * from triggers

7.3 删除触发器

drop trigger 触发器名

级联删除

删除主表的数据时。要把子表的数据删除。

8. 数据库的备份、恢复

8.1 备份方式：

1）冷备份（按月）

2）热备份

​ a. 定时任务 Job （按周，日（凌晨2点），小时）、

​ b. 实时备份（主从复制，实时同步）

查看数据安装路径

select @@basedir AS basePath FROM DUAL;

C:\Program Files\MySQL Server 5.5\bin>

使用 mysqldump 命令备份数据库

使用 mysql 命令恢复数据库

8.2 备份

退出 mysql，在系统命令行执行：

下面命令是一行代码，不能折行

1
2
3
mysqldump -uroot -p
--default-character-set=utf8	//表中存的是什么编码
hr>d:\hr.sql	//库>文件

数据库名

8.3 恢复

再次恢复：

1）在数据库中新建一个库

​ create database jtdb2 charset utf8;

  1. 在系统命令行执行恢复命令

​ 下面命令是一行代码，不能折行

1
2
3
mysql -uroot -p
--default-character-set=utf8
jtdb2:\jtds.sql

9. 索引

9.1 存储过程产生大量数据

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
32
33
34
35
36
37
38
39
1) 创建一个内存表 tm, engine=memory
2) 创建一个磁盘表 td
3) 在存储过程中，循环 20000 次向内存表插入随机字符串
4) 把 20000 条数据，一批存磁盘表
5）清空内存表

-- 内存表

drop table if exists tm;
create table tm(
	c varchar(20)
) engine=memory chaset=utf8;

-- 磁盘表

drop table if exists td;
create table td(
    id int primary key auto_increment,
    c varchar(20)
) engine=innodb charset=utf8;

-- 存储过程

drop procedure if exists gen_data;
delimiter //
create procedure gen_data(in n int)
begin
	declare i int default 0;
    while i<n do
		insert into tm values (uuid());
        set i=i+1;
	end while;
    insert into td(c) select c from tm;
    delete from tm;
end //

call gen_data(1000)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP PROCEDURE IF EXISTS test_insert;
DELIMITER //
CREATE PROCEDURE test_insert(cnt INT)
  BEGIN 
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    WHILE i<cnt
      DO 
          INSERT INTO td(c) VALUES(UUID());
      SET i=i+1;
      END WHILE;
    COMMIT;
END //


call test_insert(1000);

9.2 索引优化

索引其本质是创建了一张索引表，现有表的拷贝

（索引需要代价，插入数据要重新排序）

只拷贝现有表的指定索引字段。重要是做了一件事情：

排序

为什么它要排序呢？ 为什么排序后就快了呢？

不创建索引，查询时，全表遍历。

折半算法

用索引提高数据查找效率

1
2
3
4
5
6
7
8
-- 没有索引，花3.5秒查询
select * from td where c='ab' //

-- 对c字段创建索引，花1分40秒左右
create index td_c_index on td(c) //

-- 有索引查询花费  0.00 秒
select * from td where c='ab' //

9.3 MySQL 验证索引的使用-最左前缀特性

复合索引

通过 EXPLAIN 关键字可以判断查询 SQL 语句是否使用索引

1
EXPLAIN select * from td where c='ab'

Possible_keys如果有值就代表使用了哪个索引，如果null就代表查询没有使用索引，全表遍历。

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
SELECT * FROM tb1 
WHERE user_id=100;

SELECT * FROM tb1 
WHERE order_id=300;

SELECT * FROM tb1 
WHERE user_id=100 AND order_id=300;

SELECT * FROM tb1 
WHERE order_id=300 AND user_id=100 ;

没使用索引
EXPLAIN 
SELECT * FROM t_goods 
WHERE title LIKE '%皮面%'

使用索引
EXPLAIN
SELECT * FROM t_goods
WHERE title LIKE '皮面%'

没有使用索引
EXPLAIN
SELECT * FROM t_goods
WHERE title LIKE '%皮面'

EXPLAIN select * from tb_cart where user_id=1 and item_id=1

1
EXPLAIN select * from tb_cart where user_id=1

1
EXPLAIN SELECT * FROM tb_cart WHERE item_id=1

查看SQL的执行计划，可以看出第一句、第二句SQL使用了索引，第三句 SQL未使用索引。很好的证明了索引左侧前缀特性。

注意：

1）下面的语句违反了左侧前缀的特性，为何仍然可以使用索引呢？因为MYSQL对SQL语句有优化，它会重新组合where条件。

1
EXPLAIN SELECT * FROM tb_cart WHERE item_id=1 AND user_id=1

2）没有where条件的查询是不会使用索引的。

10. 作业

商品表 tb_item

1
select * from tb_item;

有什么品牌

1
select distinct brand from tb_item;

最贵商品的品牌、名称、价格

1
2
3
4
select brand,title,price
from tb_item
order by price desc
limit 1;

分页查看商品

1
2
3
4
5
6
7
select id,brand,title,price
from tb_item
order by price desc
-- limit 0,10;
-- limit 10,10;
-- limit 20,10;
limit 30,10;

商品名和商品描述

1
2
3
4
5
6
7
8
9
select
	title,item_desc
from 
	tb_item i
join
	tb_item_desc 
on 
	i.id = d.item_id
limit 1;

商品分类表 tb_item_cat

1
2
3
4
5
6
7
8
9
10
11
12
select * from tb_item_cat;

select * 
from 
	tb_item_cat
where 
	name like '%电脑%' 
or
	name like '%笔记本%'
or 
	name like '%手机%';

查询所有的顶层分类

1
2
3
4
5
select * from tb_item_cat
where 
	is_parent=1 
and 
	parent_id is null;

查询 161 下的二层分类

1
2
3
4
5
select * from 
	tb_item_cat
where 
	parent_id=161
order by sort_order;

查询 162 下的三层分类

1
2
3
4
5
select * from tb_item_cat
where 
	parent_id=162
order by 
	sort_order;

用户表

1
select * from tb_user;

订单表

1
select * from tb_order;

用户 id 是14的订单

1
select * from tb_order where user_id=14;

订单编号是 20161001490698615071

查询这个订单的所有商品

1
2
select * from tb_order_item
where order_id=20161001490698615071;

订单编号是 20161001490698615071

​ 查询每种类别的商品数量

1
select cid,count(*) from tb_item group by cid

查询 类别 163 的商品

1
select id,title,price from tb_item where cid=163;

​ 查询商品价格不大于100的商品名称列表

1
select id,title,price from tb_item where price<100;

​ 查询品牌是联想,且价格在40000以上的商品名称和价格

1
select id,title,price from tb_item where brand='联想' and price>40000;

​ 查询品牌是三木,或价格在10以上的商品名称和价格

1
select id,brand,title,price from tb_item where brand='三木' or price<50;

​ 查询品牌是三木、广博、齐心的商品名称和价格

1
select id,brand,title,price from tb_item where brand in('三木','广博','齐心');

​ 查询品牌不是联想、戴尔的商品名称和价格

1
select id,brand,title,price from tb_item where brand not in('联想','戴尔');

​ 查找品牌是联想且价格大于10000的电脑名称

1
select id,brand,title,price from tb_item where brand='联想' and price>10000;

​ 查询联想或戴尔的电脑名称列表

1
select id,brand,title,price from tb_item where brand='联想' or brand='戴尔';

​ 查询联想、戴尔、三木的商品名称列表

1
select id.brand,title,price from tb_item where brand in('联想','戴尔','三木');

​ 查询不是戴尔的电脑名称列表

1
select id,brand,title,price from tb_item where brand not in('戴尔');

​ 查询所有是记事本的商品品牌、名称和价格

1
select id,brand,title,price from tb_item where title like '%记事本%';

​ 查询品牌是末尾字符是’力’的商品的品牌、名称和价格

1
select id,brand,title,price from tb_item where brand like '%力';

​ 名称中有联想字样的商品名称

1
select id,brand,title,price from tb_item where title like '%联想%';

​ 查询卖点含有’爆款’电脑名称

1
select id,brand,title,price from tb_item where cell_point like '%爆款%';

​ 查询开头字母是A的电脑名称

1
select id,brand,price from tb_item where title like 'A%';

​ 将地址表中的城市、地区、详细地址组合到一起,

1
select concat(receiver_city, receiver_district,receiver_address) addr from tb_address;

​ 获取订单编号为 20161001490698615071 中的每种商品的标题、单价、数量、总价

1
select title,price,num,total_fee from tb_order_item where order_id='20161001490698615071';

​ 统计商品表中各个品牌的商品数量

1
select brand,count(*) from tb_item group by brand

​ 统计商品表中各个品牌的商品数量,并且按照数量从少到多排序

1
select brand,count(*) c from tb_item group by brand order by c;

​ 统计商品表中各个品牌的商品数量,去除品牌为NULL的行,并且按照数量从少到多排序

1
select brand,count(*) c from tb_item where brand is not null group by brand order by c;

​ 查询不同品牌中最贵的商品价格

1
select id,title,brand,price from tb_item where(brand,price) in(select brand,max(price) m from tb_item where brand is not null group by brand);

​ 将不同品牌中最贵的商品按照价格降序排序

1
select id,title,brand,price from tb_item where (brand,price)in(select brand,max(price) m from tb_item where brand is not null group by brand) order by price desc;

​ 找出不同品牌中最贵的商品的前三名

1
2
select id,title,brand,price from tb_item where (brand,price)in(select brand,max(price) m from tb_item where brand is not null group by brand) order by price desc 
limit 3;

​ 查询订购了10000028商品的客户姓名和联系方式(三层子查询)

​ 首先查询订单表,根据10000028这个商品编号找到有该商品的订单编号

​ 然后查询订单表,根据上面得到的订单编号找到订购此订单的用户编号

​ 最后查询用户表,根据第二部中查到的用户编号,找到对应的用户名和联系方式

1
select id,username,phone,email from tb_user where id in (select user_id from tb_order where (select order_id from td_order_item where item_id=10000028))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select 
	distinct 
from
	td_user u 
join 
	tb_order o 
on 
	u.id=o.user_id
join 
	tb_order_item i
on
	o.order_id=i.order_id
where 
	item_id=10000028
打赏
  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！
  • Copyrights © 2015-2023 高行行
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信