SQL巩固&数据库优化&SQL优化(待补充完整细节)

前言

对于初级程序开发工程师而言，SQL是很多人的弱项，为此我给大家来做一下总结，希望能够帮到你们。

课程说明

1、介绍项目实战开发过程中常用的MySQL函数及常用语法，并且分析三种联合查询原理及如何使用、什么时候使用；

2、针对数据库层面的优化方案做介绍说明；

3、对MySQL SQL优化方案做讲解，学习如何排查慢查询；

SQL巩固

初始化

说明：这里用大家熟知的电商业务来讲解，进一步巩固学习SQL

表结构创建

订单表

CREATE TABLE `tb_order` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
   `user_id` int(11) NOT NULL COMMENT '用户id',
   `price` int(11) NOT NULL COMMENT '订单金额',
   `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
   `create_time` datetime NOT NULL COMMENT '创建时间',
   `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
   `update_time` datetime DEFAULT NULL COMMENT '最后更新时间',
   PRIMARY KEY (`id`),
   KEY `IDX_USER_ID` (`user_id`),
   KEY `IDX_PAY_TIME` (`pay_time`),
   KEY `IDX_CREATE_TIME` (`create_time`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='订单表'

商品表

CREATE TABLE `tb_goods` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
   `price` int(11) NOT NULL DEFAULT '0' COMMENT '单价',
   `stock` int(11) NOT NULL DEFAULT '0' COMMENT '库存',
   `status` smallint(6) NOT NULL DEFAULT '0' COMMENT '状态',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='商品表'

由于一个订单可能包含多种商品，故存在

订单商品关系表

CREATE TABLE `tb_order_goods` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '关联数据id',
   `order_id` int(11) NOT NULL COMMENT '订单id',
   `goods_id` int(11) NOT NULL COMMENT '商品id',
   `goods_num` int(11) NOT NULL COMMENT '商品数量',
   `goods_price` int(11) NOT NULL COMMENT '商品单价',
   `sum_price` int(11) NOT NULL COMMENT '小计金额',
   PRIMARY KEY (`id`),
   KEY `IDX_ORDER_ID` (`order_id`),
   KEY `IDX_GOODS_ID` (`goods_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='商品订单关系表'

用户表

CREATE TABLE `tb_user` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `name` varchar(32) DEFAULT NULL COMMENT '姓名',
   `phone` varchar(15) NOT NULL DEFAULT '' COMMENT '电话',
   `sex` varchar(1) DEFAULT NULL COMMENT '性别',
   `password` varchar(64) DEFAULT NULL COMMENT '密码',
   `user_name` varchar(18) NOT NULL COMMENT '用户名',
   `status` tinyint(3) DEFAULT NULL COMMENT '状态',
   PRIMARY KEY (`id`),
   KEY `IDX_PHONE` (`phone`),
   KEY `IDX_USER_NAME` (`user_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='用户表'

有的同学想要知道，针对已存在的表结构，如何添加索引呢？简单演示如下

给tb_user表name字段添加普通索引

-- IDX_NAME 为索引名，可自行定义
ALTER TABLE `tb_user` ADD INDEX `IDX_NAME` (`name`);

添加测试数据

INSERT  INTO `tb_goods`(`id`,`name`,`price`,`stock`,`status`) VALUES (1,'华为P30',600000,20,1),(2,'美特斯邦威男士夹克衫',29900,100,1);
INSERT  INTO `tb_order`(`id`,`user_id`,`price`,`pay_time`,`create_time`,`status`,`update_time`) VALUES (1,1,629900,'2020-02-01 00:00:00','2020-02-01 00:00:00',1,'2020-02-01 00:00:00'),(2,1,1200000,NULL,'2020-02-02 00:00:00',0,'2020-02-02 00:00:00'),(3,2,600000,'2020-03-01 00:00:00','2020-03-01 00:00:00',1,'2020-03-01 00:00:00');
INSERT  INTO `tb_order_goods`(`id`,`order_id`,`goods_id`,`goods_num`,`goods_price`,`sum_price`) VALUES (1,1,1,1,600000,600000),(2,1,2,1,29900,29900),(3,2,1,2,600000,1200000),(4,3,1,1,600000,600000);
INSERT  INTO `tb_user`(`id`,`name`,`phone`,`sex`,`password`,`user_name`,`status`) VALUES (1,'高M','18810531708','男','123456','gao_yue',1),(2,'明哥','18810532526','男','123654','tang_',1),(3,'培哥','15544455665','男','545466','pei_',1);

常见操作

count累计

count(值)，这个值如果不是null则计1，值为null则计0

所以select count(1) from dual、select count(10000) from dual其结果都为1

而select count(null) from dual结果则为0

例如tb_user表中有三条数据，其中有一条数据的name字段为空，那么存在

1、select count(name) from tb_user结果则为2而非3，如果想要统计表中数据条数则用count(*)或count(id)

2、另外值得一说的是，select 1000 from tb_user表，其结果为3行1000，所以存在如下

3、select count(1000) from tb_user结果为3

count结合distinct使用可统计某个字段出现(除空值之外的)不同值的次数

比如查询订单表有多少笔金额相同的订单

select count(distinct(price)) from tb_order;

sum累加

sum(值)，会把这个值的数值进行一个累加，sum(null)则为null

所以select sum(0) from dual结果为0，select sum(1000) from dual结果为1000

而select sum(null) from dual结果为null

故针对sum的字段如果可能出现null的话，需要提前做判断转换IFNULL(字段,代替值)，避免出现统计错误

比如统计订单表订单销售总额(不区分订单状态)

select sum(ifnull(price,0)) from tb_order;

distinct去重

比如我们要查询所有下过单的用户id集合，这里结果集中针对相同id要做去重处理

SELECT DISTINCT user_id FROM tb_order;

group by、having、order by

在查询结果集之上再做查询

结果集行号、rownum

case when then else end

不用max、min如何找最大、最小

日期常见操作

now()

current_date、current_time

date_format()

unix_ timestamp

date_sub、date_add

字符串常见操作

concat

substr

left、right

if、男女性别互换

联合更新

查询结果集导入表

union、union all

联合查询

left join

right join

inner join、逗号连接

full join

这么多join方式如何选择

你应该知道的

联合索引按左匹配

数据库、表结构优化

优化的必要性

1、减轻关系型数据库访问压力，提升程序负载能力

2、加快响应速度，提升用户体验

3、慢查询引起的生产事故

4、表结构重构、数据迁移是巨大的工程

5、关系型数据库针对某些数据的存储不是那么合适

数据库表结构合理设计

数据库设计三范式

1、每一列是不可再分的属性值，同一列不能包含多个值，关系型数据库的特点之一

地址：省、市、区、具体地址

2、数据库表中每个实例或行必须可以被唯一地区分

需要有唯一能区分行数据的字段(主键)

3、一个数据表中不再包含已在其它表中包含的非主关键字信息（看情况）

订单数据，包含用户id，不再存储用户其它数据（看情况），（分布式系统中，比如下单时的用户手机号...）

设计符合业务的表结构

1、比如业务流程中，有明显的区分使用用户基础数据跟用户详情数据可以对应的建立用户基础表、用户详情表

2、多对多的关系设计中，使用中间关联表，如品牌表、分类表、品牌分类关联表

主从同步、读写分离

缓存

MySQL缓存

服务本地缓存

分布式缓存

分库分表

按业务垂直拆分

按数据水平拆分

NoSQL

NewSQL

存储引擎优化

数据归档

数据中台、数仓

SQL优化

分页查询优化

建立索引、使用索引

索引应建立在那些将用于JOIN、WHERE条件判断、ORDERBY排序的字段上

MySQL——SQL巩固&数据库优化&SQL优化