0%

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
CREATE TABLE user
(
id INT PRIMARY KEY,
name NVARCHAR(20),
hobby NVARCHAR(20),
INDEX (hobby)
) ENGINE = innodb;

INSERT INTO user
VALUES (1, 'tom', 'football');
INSERT INTO user
VALUES (2, 'dick', 'basketball');
INSERT INTO user
VALUES (3, 'lovin', 'volleyball');
INSERT INTO user
VALUES (4, 'wanda', 'badminton');
INSERT INTO user
VALUES (5, 'stuart', 'tennis');
CREATE TABLE user_money
(
uid INT PRIMARY KEY,
money INT
) ENGINE = innodb;

INSERT INTO user_money
VALUES (1, 268);
INSERT INTO user_money
VALUES (2, 12846);
INSERT INTO user_money
VALUES (3, 4856);
INSERT INTO user_money
VALUES (4, 3489);
INSERT INTO user_money
VALUES (5, 489123);

所有英文来源于MYSQL官网

1
SET SESSION optimizer_switch ='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,prefer_ordering_index=off';

const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values
from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast
because they are read only once.

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries,
tbl_name can be used as a const table:

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;

1
EXPLAIN SELECT * FROM user u WHERE u.id = 1;

eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const
types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is
a PRIMARY KEY or UNIQUE NOT NULL index.

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or
an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an
eq_ref join to process ref_table:

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

1
2
EXPLAIN SELECT * FROM user u
LEFT JOIN user_money um ON u.id = um.uid WHERE u.id = um.uid;

ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref
is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other
words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows,
this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can
use a ref join to process ref_table:

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

1
2
3
EXPLAIN SELECT * FROM user u
LEFT JOIN user_money um ON u.id = um.uid
WHERE u.hobby = '1';

range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row
indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this
type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>,
BETWEEN, LIKE, or IN() operators:

SELECT * FROM tbl_name
WHERE key_column = 10;

SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

1
EXPLAIN SELECT * FROM user u WHERE u.id > 1;

index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the
index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL
because the size of the index usually is smaller than the table data.

A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not
appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

1
EXPLAIN SELECT u.hobby FROM user u;

ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table
is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding
indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

1
EXPLAIN SELECT u.* FROM user u;

注意先把优化器关了

1
SET SESSION optimizer_switch ='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,prefer_ordering_index=off';

子查询执行过程

1.从外层查询中取出一个元组(即一行),将元组相关列的值传给内层查询。

2.执行内层查询,得到子查询操作的值。

3.外查询根据子查询返回的结果或结果集得到满足条件的行。

4.然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。

SIMPLE

1
2
3
EXPLAIN
SELECT *
FROM contents;

PRIMARY

UNION

UNION RESULT

1
2
3
4
EXPLAIN
SELECT * FROM contents
UNION
SELECT * FROM contents;

PRIMARY

DEPENDENT SUBQUERY

DEPENDENT UNION

UNION RESULT

1
2
3
4
5
6
7
8
EXPLAIN
SELECT *
FROM contents c
WHERE c.post_id in (
SELECT c1.post_id FROM contents c1
UNION
SELECT c2.post_id FROM contents c2
);

PRIMARY

SUBQUERY

1
2
3
EXPLAIN
SELECT c.*, (SELECT post_id FROM contents WHERE post_id = 2)
FROM contents c;

PRIMARY

DEPENDENT SUBQUERY

1
2
3
4
EXPLAIN
SELECT c.*
FROM contents c
WHERE c.post_id IN (SELECT p.id FROM posts p);

DERIVED 出现在 FROM 子句中的子查询

1
2
3
EXPLAIN
SELECT c.*
FROM (SELECT * FROM contents) c;

下载地址

https://github.com/ytdl-org/youtube-dl

常用命令

1
2
3
4
5
6
7
8
9
10
网络选项
--proxy URL 使用HTTP/HTTPS/SOCKS协议的代理.如:socks5://127.0.0.1:1080/.
视频格式选项
-f, --format FORMAT 视频格式代码,查看"FORMAT SELECTION"获取所有信息
--all-formats 获取所有视频格式
-F, --list-formats 列出请求视频的所有可用格式
视频选择
--playlist-start NUMBER 指定列表中开始下载的视频(默认为1)
--playlist-end NUMBER 指定列表中结束的视频(默认为last)
--playlist-items ITEM_SPEC 指定列表中要下载的视频项目编号.如:"--playlist-items 1,2,5,8"或"--playlist-items 1-3,7,10-13"

实例

1
youtube-dl.exe --proxy socks5://127.0.0.1:1086 -f 139 https://www.youtube.com/playlist?list=PL-XOEdg1pwkpaeBB6aI1BIW8Lc9N-hX-d

1.什么是消息中间件

消息中间件利用高效可靠的消息传递机制进行平台无关的数据交流,并基于数据通信来进行分布式系统的集成。通过提供消息传递和消息排队模型,它可以在分布式环境下扩展进程间的通信。

2.为什么使用消息中间件

解耦、异步、削峰
虚拟场景:用户注册后,需调用其它系统,发相关的通知短信或邮件。

阅读全文 »

1.背景

线程安全大多数时候很重要。

2.Thread

线程(英语:thread)是操作系统能够进行运算调度的最小单位。它被包含在进程之中,是进程中的实际运作单位。一条线程指的是进程中一个单一顺序的控制流,一个进程中可以并发多个线程,每条线程并行执行不同的任务。

阅读全文 »

概念

GDP=国内生产总值

一个国家(或地区)所有常驻单位,在一定时期内,生产的全部最终产品和服务价值的总和,常被认为是衡量国家(或地区)经济状况的指标。

CPI=居民消费价格指数(consumer price index)

居民消费价格指数,是一个反映居民家庭一般所购买的消费品和服务项目价格水平变动情况的宏观经济指标。它是在特定时段内度量一组代表性消费商品及服务项目的价格水平随时间而变动的相对数,是用来反映居民家庭购买消费商品及服务的价格水平的变动情况。

阅读全文 »

概念

天使投资(AI):指具有一定净财富的人士,对具有巨大发展潜力的高风险的初创企业进行早期的直接投资。

风险投资(VC):由一群具有科技及财务相关知识与经验的人所组合而成的,经由直接投资获取投资公司股权的方式,提供资金给需要资金者(被投资公司)。

私募基金(PE):是指以非公开方式向特定投资者募集资金并以证券为投资对象的证券投资基金。私募基金是以大众传播以外的手段招募,发起人集合非公众性多元主体的资金设立投资基金,进行证券投资。

首次公开募股(IPO):是指一家企业或公司 (股份有限公司)第一次将它的股份向公众出售。

阅读全文 »

概念

1.基金:

广义:基金是指为了某种目的而设立的具有一定数量的资金。例如,信托投资基金、公积金、保险基金、退休基金,各种基金会的基金。

狭义:人们平常所说的基金主要是指证券投资基金。

2.债券:债券是政府、企业、银行等债务人为筹集资金,按照法定程序发行并向债权人承诺于指定日期还本付息的有价证券。

3.股票:股票(stock)是股份公司发行的所有权凭证,是股份公司为筹集资金而发行给各个股东作为持股凭证并借以取得股息和红利的一种有价证券。

阅读全文 »

数据

1)A,B两支球队

2)博彩公司赔率

公司 A胜 B胜
C1 1.40 4.57 6.5
C2 1.31 5.75 9.55
C3 1.32 5.65 11.5
阅读全文 »