分类： 数据库

优化具体方法

　　1、SQL后面添加limit

　　2、禁用select *

　　3、优化like语句

　　4、避免在索引列上使用内置函数和表达式操作

　　5、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描

　　6、在适当的时候，使用only indexscan

　　7、避免排序

　　　　7.1、灵活使用集合运算符的 ALL 可选项
　　　　7.2、使用 EXISTS 代替 DISTINCT
　　　　7.3、在极值函数中使用索引（MAX/MIN）
　　　　7.4、在 GROUP BY 子句和 ORDER BY 子句中使用索引

　　8、删除冗余和重复索引

　　9、关于大量DELETE/UPDATE操作

　　10、where 子句中考虑使用默认值代替 null

　　11、合理使用exists&in

　　12、能写在 WHERE 子句里的条件不要写在 HAVING 子句里

　　13、用varchar代替char，合理设置varchar可变字段长度

　　14、where后字段值注意引号使用，易导致索引失效

　　15、当在 SQL 语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰

　　16、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段

　　17、表关联不要太多

　　18、Inner join 、left join、right join，优先使用 Inner join，如果是 left join，左边表结果尽量小

　　　　18.1、分解关联查询
　　　　18.2、改写关联查询

　　19、减少中间表

　　　　19.1、灵活使用 HAVING 子句
　　　　19.2、需要对多个字段使用 IN 谓词时，将它们汇总到一处

　　20、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

　　21、使用多列索引时，注意索引列的顺序，一般遵循最左匹配原则

　　22、字段类型能用数值尽量用数值类型

　　23、长度很长的多字段联合主键用hash

　　24、禁用UUID作为主键

　　25、善用set、explain查看&调试执行计划

# 建父表
create table sales_detail (
	product_id int not null,
	price numeric(12,2),
	amount int not null,
	sale_date date not null,
	buyer varchar(40),
	buyer_contarct text
);
# 建子表
create table sales_detail_y2014m01(check (sale_date >= date '2014-01-01' and sale_date < date '2014-02-01')) inherits (sales_detail);
create table sales_detail_y2014m02(check (sale_date >= date '2014-02-01' and sale_date < date '2014-03-01')) inherits (sales_detail);
create table sales_detail_y2014m03(check (sale_date >= date '2014-03-01' and sale_date < date '2014-04-01')) inherits (sales_detail);
create table sales_detail_y2014m04(check (sale_date >= date '2014-04-01' and sale_date < date '2014-05-01')) inherits (sales_detail);
create table sales_detail_y2014m05(check (sale_date >= date '2014-05-01' and sale_date < date '2014-06-01')) inherits (sales_detail);
create table sales_detail_y2014m06(check (sale_date >= date '2014-06-01' and sale_date < date '2014-07-01')) inherits (sales_detail);
create table sales_detail_y2014m07(check (sale_date >= date '2014-07-01' and sale_date < date '2014-08-01')) inherits (sales_detail);
create table sales_detail_y2014m08(check (sale_date >= date '2014-08-01' and sale_date < date '2014-09-01')) inherits (sales_detail);
create table sales_detail_y2014m09(check (sale_date >= date '2014-09-01' and sale_date < date '2014-10-01')) inherits (sales_detail);
create table sales_detail_y2014m10(check (sale_date >= date '2014-10-01' and sale_date < date '2014-11-01')) inherits (sales_detail);
create table sales_detail_y2014m11(check (sale_date >= date '2014-11-01' and sale_date < date '2014-12-01')) inherits (sales_detail);
create table sales_detail_y2014m12(check (sale_date >= date '2014-12-01' and sale_date < date '2015-01-01')) inherits (sales_detail);
# 字表索引
create index sale_detail_y2014m04_sale_date on sales_detail_y2014m01 (sale_date);
create index sale_detail_y2014m02_sale_date on sales_detail_y2014m02 (sale_date);
create index sale_detail_y2014m03_sale_date on sales_detail_y2014m03 (sale_date);
create index sale_detail_y2014m04_sale_date on sales_detail_y2014m04 (sale_date);
create index sale_detail_y2014m05_sale_date on sales_detail_y2014m05 (sale_date);
create index sale_detail_y2014m06_sale_date on sales_detail_y2014m06 (sale_date);
create index sale_detail_y2014m07_sale_date on sales_detail_y2014m07 (sale_date);
create index sale_detail_y2014m08_sale_date on sales_detail_y2014m08 (sale_date);
create index sale_detail_y2014m09_sale_date on sales_detail_y2014m09 (sale_date);
create index sale_detail_y2014m10_sale_date on sales_detail_y2014m10 (sale_date);
create index sale_detail_y2014m12_sale_date on sales_detail_y2014m12 (sale_date);
# 建触发器
CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.sale_date >= DATE '2014-01-01' AND
NEW. sale_date < DATE '2014-02-01' ) THEN
INSERT INTO sales_detail_y2014m01 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-02-01' AND
NEW.sale_date < DATE '2014-03-01' ) THEN
INSERT INTO sales_detail_y2014m02 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-03-01' AND
NEW.sale_date < DATE '2014-04-01' ) THEN
INSERT INTO sales_detail_y2014m03 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-04-01' AND
NEW.sale_date < DATE '2014-05-01' ) THEN
INSERT INTO sales_detail_y2014m04 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-05-01' AND
NEW.sale_date < DATE '2014-06-01' ) THEN
INSERT INTO sales_detail_y2014m05 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-06-01' AND
NEW.sale_date < DATE '2014-07-01' ) THEN
INSERT INTO sales_detail_y2014m06 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-07-01' AND
NEW.sale_date < DATE '2014-08-01' ) THEN
INSERT INTO sales_detail_y2014m07 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-08-01' AND
NEW.sale_date < DATE '2014-09-01' ) THEN
INSERT INTO sales_detail_y2014m08 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-09-01' AND
NEW.sale_date < DATE '2014-10-01' ) THEN
INSERT INTO sales_detail_y2014m09 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-10-01' AND
NEW.sale_date < DATE '2014-11-01' ) THEN
INSERT INTO sales_detail_y2014m10 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-11-01' AND
NEW.sale_date < DATE '2014-12-01' ) THEN
INSERT INTO sales_detail_y2014m11 VALUES (NEW.*);

ELSIF ( NEW.sale_date >= DATE '2014-12-01' AND
NEW.sale_date < DATE '2015-01-01' ) THEN
INSERT INTO sales_detail_y2014m12 VALUES (NEW.*);

ELSE
RAISE EXCEPTION 'Date out of range. Fix the
sale_detail_insert_trigger () function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_sale_detail_trigger
BEFORE INSERT ON sales_detail
FOR EACH ROW EXECUTE PROCEDURE
sale_detail_insert_trigger ();

# 数据插入：
insert into sales_detail values(1,43.12,1,date '2014-01-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-02-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-03-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-04-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-05-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-06-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-07-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-08-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-09-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-10-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-11-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-12-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-01-02', '李四', '四川yy');

# 数据测试
select * from sales_detail limit 10;

select * from sales_detail_y2014m01 limit 1;

规则实现触发器功能

CREATE RULE sales_detail_insert_y2014m01 AS

ON INSERT TO sales_detail WHERE

( sale_date >= DATE '2014-01-01' AND sale_date < DATE
'2014-02-01' )
DO INSTEAD
INSERT INTO sales_detail_y2014m01 VALUES (NEW.);
CREATE RULE sales_detail_insert_y2014m02 AS
ON INSERT TO sales_detail WHERE
( sale_date >= DATE '2014-02-01' AND sale_date < DATE
'2014-03-01' )
DO INSTEAD
INSERT INTO sales_detail_y2014m01 VALUES (NEW.);
….
CREATE RULE sales_detail_insert_y2014m12 AS
ON INSERT TO sales_detail WHERE
( sale_date >= DATE '2014-12-01' AND sale_date < DATE

'2015-01-01' )

DO INSTEAD

INSERT INTO sales_detail_y2014m12 VALUES (NEW.*);

建表插入数据

create table score(
 stu_code varchar(8) not null,
 stu_name varchar(8) not null,
 stu_sex int not null,
 stu_score int not null)
 ;
insert into score values('xm','小明',0,88);
insert into score values('xl','小磊',0,55);
insert into score values('xf','小峰',0,45);
insert into score values('xh','小红',0,66);
insert into score values('xn','晓妮',0,77);
insert into score values('xy','小伊',0,99);

SELECT 
SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,  --男生数量统计
SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,  --女生数量统计
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,  --男生通过人数统计
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS  --女生数量统计
FROM score

postgres=# select (case when stu_sex =0 then 1 else 0 end) from thtf_students;

case

1
1
1
1
1
1

(6 rows)

postgres=# select case when stu_sex =0 then 1 else 0 end from thtf_students;

case

1
1
1
1
1
1

(6 rows)

关联表

CREATE TABLE class(no int primary key, class_name varchar(40));
INSERT INTO class VALUES(1,'初二(1)班');
INSERT INTO class VALUES(2,'初二(2)班');
INSERT INTO class VALUES(3,'初二(3)班');
INSERT INTO class VALUES(4,'初二(4)班');
CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int);
INSERT INTO student VALUES(1, '张三', 14, 1);
INSERT INTO student VALUES(2, '吴二', 15, 1);
INSERT INTO student VALUES(3, '李四', 13, 2);
INSERT INTO student VALUES(4, '吴三', 15, 2);
INSERT INTO student VALUES(5, '王二', 15, 3);
INSERT INTO student VALUES(6, '李三', 14, 3);
INSERT INTO student VALUES(7, '吴二', 15, 4);
INSERT INTO student VALUES(8, '张四', 14, 4);

1、标准
SELECT student_name, class_name FROM student,
class
WHERE student.class_no = class.no;

2、distinct
select distinct student_name, class_no from student, class;

3、
select student_name, class_name from student, class;

oldboy=# select * from student a, class b where a.class_no=b.no and b.class_name='初二(2)班';
 no | student_name | age | class_no | no | class_name 
----+--------------+-----+----------+----+------------
  3 | 李四         |  13 |        2 |  2 | 初二(2)班
  4 | 吴三         |  15 |        2 |  2 | 初二(2)班
(2 rows)

oldboy=# explain analyze select * from student a, class b where a.class_no=b.no and b.class_name='初二(2)班';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=17.79..35.25 rows=3 width=212) (actual time=0.020..0.022 rows=2 loops=1)
   Hash Cond: (a.class_no = b.no)
   ->  Seq Scan on student a  (cost=0.00..15.90 rows=590 width=110) (actual time=0.007..0.008 rows=8 loops=1)
   ->  Hash  (cost=17.75..17.75 rows=3 width=102) (actual time=0.008..0.008 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on class b  (cost=0.00..17.75 rows=3 width=102) (actual time=0.005..0.006 rows=1 loops=1)
               Filter: ((class_name)::text = '初二(2)班'::text)
               Rows Removed by Filter: 3
 Planning Time: 0.080 ms
 Execution Time: 0.038 ms
(10 rows)

oldboy=# explain analyze select * from student where class_no in  (select no from class where class_name='初二(2)班');
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=17.79..35.25 rows=3 width=110) (actual time=0.019..0.021 rows=2 loops=1)
   Hash Cond: (student.class_no = class.no)
   ->  Seq Scan on student  (cost=0.00..15.90 rows=590 width=110) (actual time=0.006..0.007 rows=8 loops=1)
   ->  Hash  (cost=17.75..17.75 rows=3 width=4) (actual time=0.007..0.008 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on class  (cost=0.00..17.75 rows=3 width=4) (actual time=0.005..0.005 rows=1 loops=1)
               Filter: ((class_name)::text = '初二(2)班'::text)
               Rows Removed by Filter: 3
 Planning Time: 0.089 ms
 Execution Time: 0.038 ms
(10 rows)

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from urllib import request
import json
from datetime import datetime, timedelta
import time

def get_data():
    url = 'https://search.51job.com/list/070200,000000,0000,00,9,99,java%25E5%25BC%2580%25E5%258F%2591,2,1.html'
    headers = {
        'User-Agent' : 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36'
    }
    req = request.Request(url, headers=headers)
    response = request.urlopen(req)
    print('===============', response.getcode(), type(response))
    if response.getcode() == 200:
        # 字节码
        data = response.read()
        print(type(data))
        # 字节码转字符串
        data = data.decode('utf-8')
        print(type(data))
        # print(data)
        with open('index.html', mode='w', encoding='utf-8') as f:
            f.write(data)

if __name__ == '__main__':
    get_data()

亿图、Visio

逻辑备份

逻辑备份
mysqldump -A -B --master-data=2 --single-transaction|gzip >/opt/all.sql.gz
恢复
zcat opt/all.sql.gz|mysql
mysqlbinlog mysql-binlog.000008 mysql-bin.000009 >bin.sql
mysql <bin.sql 

物理备份

冷备份方式
cp、rsync、tar、scp等复制工具将MySQL数据文件复制成多份。
热备份方式
Xtrabackup

核心的SQL

DQL 数据查询语句。select

DML数据操作语言。insert、update、delete

DDL数据定义语言。create、drop、alter

DCL数据控制语言。grant、revoke

非核心

TPL事务处理语言。BEGIN TRASACTION COMMIT ROLLBACK

CCL 指针控制语言

新建数据结构

 CREATE TABLE `tbl_tree` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`parent_id` int(11) DEFAULT NULL, 
`name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;  

插入数据信息

INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('1', '0', '家配成品 类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('2', '0', '营销物料 类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('3', '1', '家配'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('4', '1', '寝具'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('5', '1', '衣百货'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('6', '2', '物料'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('7', '3', '凳类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('8', '3', '椅类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('9', '3', '床类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('10', '3', '餐椅类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('11', '3', '桌台类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('12', '3', '沙发类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('13', '3', '窗帘类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('14', '3', '茶几类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('15', '3', '床头柜类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('16', '3', '软床类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('17', '3', '按摩护理 类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('18', '3', '其它配套 类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('19', '4', '软床类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('20', '4', '床垫类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('21', '4', '床配类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('22', '4', '床头柜类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('23', '4', '排骨架类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('24', '4', '销售道具'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('25', '5', '礼包类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('26', '5', '日用品类'); 
INSERT INTO `tbl_tree` (`id`, `parent_id`, `name`) VALUES ('27', '5', '家居饰品 类');

#1、在mysql的安装目录中，新建data目录及my.ini#注意事项my.ini文件必须要用ansi的方式编码

#2、编辑my.ini

[mysqld] 
basedir=D:\mysql-5.7.36-winx64
datadir=D:\mysql-5.7.36-winx64\data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-server=utf8
character_set_filesystem=utf8

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#3、将D:\mysql-5.7.36-winx64\bin路径添加到path中

#4.初始化数据库D:\mysql-5.7.36-winx64\bin>mysqld –initialize –user=mysql –console

2022-03-30T07:44:01.640992Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

2022-03-30T07:44:01.641071Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-03-30T07:44:01.641079Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2022-03-30T07:44:02.890183Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-03-30T07:44:03.350137Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2022-03-30T07:44:03.496899Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2b9d5b48-affd-11ec-83b7-0250f2000002.
2022-03-30T07:44:03.537576Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-30T07:44:04.828539Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-03-30T07:44:04.828830Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-03-30T07:44:04.829738Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-30T07:44:06.375278Z 1 [Note] A temporary password is generated for root@localhost: V8uZTa!8fk.p

#【注意】记录随机生成密码 或 初始采用这条命令：mysqld initialize insecure –user=mysql –console

#5.启动服务mysql服务net start mysql

【帮助指南】

1、netstat -ano|findstr 3306
2、Windows下Mysql5.7忘记root密码的解决方法

1. 打开第一个cmd窗口执行 net stop mysql57
2. 在第一个cmd窗口执行 mysqld –defaults-file=”C:\ProgramData\MySQL\MySQL Server 5.7\my.ini” –skip-grant-tables —注意以你的路径为准
3. 打开第二个cmd窗口执行 mysql -uroot -p 提示输入密码，直接回车（不用输入密码）
4. 选择数据库：use mysql;
5. 更新root的密码：update user set authentication_string=password(‘新密码’) where user=’root’ and Host=’localhost’;
6. 刷新权限：flush privileges;
7. 退出：quit
8. 重新登录：mysql -uroot -p 提示输入密码，这时输入密码才能登录。完成！！！

【FQA】mysqld: [ERROR] Found option without preceding group in config file D:\mysql-5.7.36-winx64\my.ini at line 1!
 
没有新建库，需要建立一个库