A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.
It applies a hash function to the username and password before sending them over the network. In contrast, basic access authentication uses the easily reversible Base64 encoding instead of hashing, making it non-secure unless used in conjunction with TLS.
In cryptography, a message authentication code (MAC), sometimes known as a tag, is a short piece of information used for authenticating a message. In other words, to confirm that the message came from the stated sender (its authenticity) and has not been changed. The MAC value protects a message’s data integrity, as well as its authenticity, by allowing verifiers (who also possess the secret key) to detect any changes to the message content.
If any change is made to the data being sent, the resulting HMAC will be completely different from the original. Additionally, since the key is known only to the sender and the receiver, no valid HMAC can be regenerated by anyone else.
All you need to do is take the HTTP request body and apply the SHA-256 hash function to it, using the secret key as the hash key. You then compare the resulting HMAC to the one contained in the Signature header:
If the HMACs are identical, then the data corresponds to what sender sent.
If they are different, this indicates that the data has been intercepted and altered in some way.
explain select id from t_order where create_time between'2019-10-17'and'2019-10-25' limit 1000000, 10;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type |table| type | possible_keys | key | key_len |ref|rows| Extra | +----+-------------+---------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ |1| SIMPLE | t_order |range| idx_create_time | idx_create_time |5|NULL|3775048|Usingwhere; Using index | +----+-------------+---------+-------+-----------------+-----------------+---------+------+---------+--------------------------+
上述查询字段改为 id 后，执行计划中新增： Extra=Using index，表示走覆盖索引，无需回表，查询速度快了 N 倍。
延迟关联优化
延迟关联优化涉及到了 SQL 优化的两个重要概念：覆盖索引和回表。
通过覆盖索引在辅助索引上完成所有扫描、过滤、排序（利用索引有序）和分页；
最后通过主键回表查询，最大限度减少回表查询的 I/O 次数。
SQL 改造如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
explain select*from t_order t innerjoin ( select id from t_order where create_time between'2019-10-17'and'2019-10-25' limit 1000000, 10 ) e on t.id = e.id;
+----+-------------+---------------+--------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type |table| type | possible_keys | key | key_len |ref|rows| Extra | +----+-------------+---------------+--------+-----------------+-----------------+---------+------+---------+--------------------------+ |1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|1000010|NULL| |1|PRIMARY| t | eq_ref |PRIMARY|PRIMARY|8| e.id |1|NULL| |2| DERIVED | t_order |range| idx_create_time | idx_create_time |5|NULL|3775048|Usingwhere; Using index | +----+-------------+---------------+--------+-----------------+-----------------+---------+------+---------+--------------------------+
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers INNERJOIN Orders ON Customers.cust_id = Orders.cust_id GROUPBY Customers.cust_id;
但如果使用左外连接 LEFT OUTER JOIN 和聚集函数 COUNT() 进行相同统计，将会包括那些还未下单的顾客，例如顾客 1000000002：
1 2 3 4 5 6 7 8 9 10 11 12 13
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers LEFTOUTERJOIN Orders ON Customers.cust_id = Orders.cust_id GROUPBY Customers.cust_id;
A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined.
例如，使用以下 join type 执行 t1、t2 和 t3 三个表之间的表连接：
1 2 3 4
Table Join Type t1 range t2 ref t3 ALL
使用 NLJ 算法，则按以下方式处理连接：
1 2 3 4 5 6 7
for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } }
for each row in t1 matching range { for each row in t2 matching reference key { store used columns from t1, t2 in join buffer if buffer is full { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } empty join buffer } } }
if buffer is not empty { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } }
设 S 为每行 used columns from t1, t2 的大小， C 为其行数，则 t3 全表扫描的次数为：
1 2 3 4
t3_scanned_count = (S * C) % join_buffer_size == 0 ? (S * C) / join_buffer_size : (S * C) / join_buffer_size + 1
因此，随着 join_buffer_size 增加，t3 全表扫描的次数反比减少，直到 join_buffer_size=(S * C) 时则无法再优化。
索引是存储引擎用于快速找到记录的一种数据结构。在 MySQL 中，索引是在存储引擎层而不是服务器层实现的（如下图）。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
常见索引类型
MySQL 支持的常见索引类型：
B+Tree 索引
Hash index（哈希索引）
R-Tree index（空间数据索引）
Full-text index（全文索引）
……
本文只探讨最常用的 B-Tree 索引。
B+Tree 数据结构
B+Tree 索引特性
当我们谈论索引的时候，如果没有特别指明类型，那多半说的是 B+Tree 索引。InnoDB 存储引擎默认使用的也是 B+Tree 数据结构来存储数据。 索引可以包含一个或多个列的值。如果包含多个列（称为“联合索引”），那么列的顺序至关重要，因为 MySQL 只能高效地使用索引的 最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的，下面看个例子：
1 2 3 4 5 6 7
CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f') not null, key(last_name, first_name, dob) );
可以使用该 B-Tree 索引的查询类型：
全值匹配
全值匹配（Match the full value）：
1 2 3
SELECT * FROM people WHERE last_name = 'Wu' AND first_name = 'Qida' AND dob = '2018-01-01';
匹配最左前缀
匹配最左前缀（Match a leftmost prefix）：
1 2 3 4 5 6 7
SELECT * FROM people WHERE last_name = 'Wu';
SELECT * FROM people WHERE last_name = 'Wu' AND first_name = 'Qida';
匹配列前缀
匹配列前缀（Match a column prefix）：
1 2 3
SELECT * FROM people WHERE last_name LIKE 'W%';
注意 MySQL LIKE 的限制：
MySQL can’t perform the LIKE operation in the index. This is a limitation of the low-level storage engine API, which in MySQL 5.5 and earlier allows only simple comparisons (such as equality, inequality, and greater-than) in index operations.
MySQL can perform prefix-match LIKE patterns in the index because it can convert them to simple comparisons, but the leading wildcard in the query makes it impossible for the storage engine to evaluate the match. Thus, the MySQL server itself will have to fetch and match on the row’s values, not the index’s values.
匹配范围值
匹配范围值（Match a range of values）：
1 2 3
SELECT * FROM people WHERE last_name BETWEEN 'Wu' AND 'Li';
精确匹配某一列，并范围匹配另外一列
精确匹配某一列，并范围匹配另外一列（Match one part exactly and match a range on another part）：
1 2 3
SELECT * FROM people WHERE last_name = 'Wu' And first_name LIKE 'Q%';
覆盖索引
只访问索引列的查询（Index-only queries）：
1 2 3
SELECT last_name, first_name, dob FROM people WHERE last_name = 'Wu' AND first_name = 'Qida' AND dob = '2018-01-01';
-- 错误示范 SELECT* FROM t_order WHERE merchant_no =2016;
-- 正确示范 SELECT* FROM t_order WHERE merchant_no ='2016';
字符串索引优化
常规方式
直接创建完整索引
优点：可以使用覆盖索引
缺点：比较占用空间
创建前缀索引
优点：节省空间
缺点：
需要计算好区分度，以定义合适的索引长度，否则会增加回表次数
无法使用覆盖索引
区分度计算方法：
1 2 3 4 5 6
select count(distinctleft(email,4)) /count(distinct email) as L4, count(distinctleft(email,5)) /count(distinct email) as L5, count(distinctleft(email,6)) /count(distinct email) as L6, count(distinctleft(email,7)) /count(distinct email) as L7, from t1;
SELECT*FROM tbl_name WHERE primary_key_part1 =1AND primary_key_part2 =2;
SELECT*FROM tbl_name WHERE unique_key ='001';
SELECT*FROM tbl_name WHERE unique_key_part1 ='001'AND unique_key_part2 ='002';
SELECT*FROM ref_table,other_table WHERE ref_table.unique_key_column = other_table.unique_key_column AND other_table.unique_key_column ='001';
eq_ref
对于 other_table 中的每行，仅从 ref_table 中读取唯一一行。eq_ref 类型用于主键索引（PRIMARY KEY ）或 NOT NULL 的唯一索引（UNIQUE KEY），且索引被表连接所使用时。除了 system 和 const 类型之外，这是最好的连接类型。select_type=SIMPLE 简单查询类型不会出现这种类型。
例子：
1 2 3 4 5 6
SELECT*FROM ref_table,other_table WHERE ref_table.unique_key_column = other_table.column; SELECT*FROM ref_table,other_table WHERE ref_table.unique_key_column_part1 = other_table.column AND ref_table.unique_key_column_part2 =1;
ref
对于 other_table 中的每行，从 ref_table 中读取所有匹配行。ref 类型用于普通索引或联合索引的最左前缀列（leftmost prefix of the key），即无法根据键值查询到唯一一行。如果使用的索引仅匹配几行结果，则也是一种很好的连接类型。
例子：
1 2 3 4 5 6 7 8 9 10
SELECT*FROM ref_table WHERE key_column = expr;
SELECT*FROM ref_table WHERE key_column_part1 = 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;
Prefixes, defined by the length attribute, can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.
EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.
ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.
For InnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
…
ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
