Database Learn Note

置顶黃建榮

已于 2023-09-12 06:16:23 修改

阅读量404

点赞数 1

分类专栏： SQL 文章标签：学习

于 2022-04-16 23:05:07 首次发布

本文链接：https://blog.csdn.net/ispriatiom_h/article/details/124222643

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

數據庫學習部分

基礎概念

關係型數據庫 – PostgreSQL

關係型數據庫 – MySQL

技巧部分

常用`SQL`技巧和常見問題

正則表達式的使用

select 'abcdefg' REGEXP '.h', 'abcdefg' REGEXP '.f';

通過正則表達式刪選相應郵箱

select name, email from t where email REGEXP "@163[.,]com$";
-- 篩選除了163的郵箱，如果不使用正則表達式，使用 like 則對應如下
select name, email from t where email like "@163%.com" or email like "@163%.com"

SQL （MySQL）優化

SQL優化一般步驟

通過慢查日誌等定位那些執行效率較低的SQL語句
explain 分析SQL的執行計畫

需要重點關注type、rows、filtered、extra。

type由上至下，效率越來越高

Extra

Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型流覽所有行並為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然後關鍵字被排序，並按排序順序檢索行。
Using temporary：使用了臨時表保存中間結果，性能特別差，需要重點優化
Using index：表示相應的 select 操作中使用了覆蓋索引（Coveing Index）,避免訪問了表的數據行，效率不錯！如果同時出現 using where，意味著無法直接通過索引查找來查詢到符合條件的數據。
Using index condition：MySQL5.6之後新增的ICP，using index condtion就是使用了ICP（索引下推），在存儲引擎層進行數據過濾，而不是在服務層過濾，利用索引現有的數據減少回表的數據。
ALL 全表掃描
index 索引全掃描
range 索引範圍掃描，常用語<,<=,>=,between,in等操作
ref 使用非唯一索引掃描或唯一索引首碼掃描，返回單條記錄，常出現在關聯查詢中
eq_ref 類似ref，區別在於使用的是唯一索引，使用主鍵的關聯查詢
const/system 單條記錄，系統會把匹配行中的其他列作為常數處理，如主鍵或唯一索引查詢
null MySQL不訪問任何表或索引，直接返回結果雖然上至下，效率越來越高，但是根據cost模型，假設有兩個索引idx1(a, b, c),idx2(a, c)，SQL為select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那麼是type為range，如果走idx2，那麼type是ref；當需要掃描的行數，使用idx2大約是idx1的5倍以上時，會用idx1，否則會用idx2

show profile 分析

瞭解SQL執行的線程的狀態及消耗的時間。默認是關閉的，開啟語句set profiling = 1;

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

trace

trace分析優化器如何選擇執行計畫，通過trace檔能夠進一步瞭解為什麼優惠券選擇A執行計畫而不選擇B執行計畫。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

確定問題並採用相應的措施

優化索引
優化SQL語句：修改SQL、IN 查詢分段、時間查詢分段、基於上一次數據過濾
改用其他實現方式：ES、數倉等
數據碎片處理

場景分析

案例1、最左匹配

索引


KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL語句

select * from _t where orderno=''

查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引(shop_id,order_no)調換前後順序

案例2、隱式轉換

索引

KEY `idx_mobile` (`mobile`)

SQL語句

select * from _user where mobile=12345678901

隱式轉換相當於在索引上做運算，會讓索引失效。mobile是字元類型，使用了數字，應該使用字串匹配，否則MySQL會用到隱式替換，導致索引失效。加粗样式

案例3、大分頁

索引

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL語句

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

對於大分頁的場景，可以優先讓產品優化需求，如果沒有優化的，有如下兩種優化方式，一種是把上一次的最後一條數據，也即上面的c傳過來，然後做c < xxx處理，但是這種一般需要改介面協議，並不一定可行。另一種是採用延遲關聯的方式進行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動如下

select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;

案例4、`in + order by`

索引

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL語句

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

in查詢在MySQL底層是通過n*m的方式去搜索，類似union，但是效率比union高。in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_dive_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。因此會導致執行計畫選擇不准確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不准確。

處理方式，可以(order_status, created_at)互換前後順序，並且調整SQL為延遲關聯。

案例5、範圍查詢阻斷，後續字段不能走索引

索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL語句

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

範圍查詢還有IN、between

案例6、不等於、不包含不能用到索引的快速搜索。（可以用到ICP）

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

案例7、優化器選擇不使用索引的情況

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時（一般是**20%**左右），優化器會選擇通過聚集索引來查找數據。

select * from _order where  order_status = 1

查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒法使用索引。

案例8、複雜查詢

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

如果是統計某些數據，可能改用數倉進行解決；如果是業務上就有那麼複雜的查詢，可能就不建議繼續走SQL了，而是採用其他的方式進行解決，比如使用ES等進行解決。

案例9、`asc`和`desc`混用

select * from _t where a=1 order by b desc, c asc

desc 和asc混用時會導致索引失效

案例10、大數據

對於推送業務的數據存儲，可能數據量會很大，如果在方案的選擇上，最終選擇存儲在MySQL上，並且做7天等有效期的保存。那麼需要注意，頻繁的清理數據，會照成數據碎片，需要聯繫DBA進行數據碎片處理。

杂项主题

关于 MySQL 的自增主键

自增`id`

自增id，第一反应一定是在设计表结构的时候自定义一个自增id字段，那么就有一个问题啦，在插入数据时有可能唯一主键冲突、sql事务回滚、批量插入的时候，批量申请自增值等原因导致自增id是不连续的。

表定义的自增值达到上线后的逻辑是：再申请下一个id的时候，获取的是同一个值（最大值）。大家可以插入sql设置id是最大值，再insert一条不主动设置id的语句就可以验证这一结论啦。这个时候如果再插入就是报主键冲突。

这里提醒一下： $2^{32}-1(4294967295)$ 不是一个特别大的数，对于一个频繁插入删除数据的表来说，是可能会被用完的。因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 bigint unsigned。

`InnoDB`系统自增`row_id`

如果创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值，所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。

实际上，在代码实现时 row_id 是一个长度为8字节的无符号长整型 (bigint unsigned)。但是，InnoDB 在设计时，给 row_id 留的只是 6 个字节的长度，这样写到数据表中时只放了最后 6 个字节，所以 row_id 能写到数据表中的值，就有两个特征：

row_id 写入表中的值范围，是从 0 到 $2^{48}-1$ ；
当 dict_sys.row_id= $2^{48}$ 时，如果再有插入数据的行为要来申请 row_id，拿到以后再取最后 6 个字节的话就是 0。

虽然， $2^{48}$ 这个数字已经很大了，但是大家要知道一个系统是可以跑很久的，那么还是可能达到上限的，这时候再申请就会覆盖原来的记录了。因此，尽量不要选择这种方式！

`Xid`

MySQL中redo log 和 binlog 相配合的时候，它们有一个共同的字段叫作 Xid。它在 MySQL 中是用来对应事务的。

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。而 global_query_id 是一个纯内存变量，重启之后就清零了。所以在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。

`Innodb` `trx_id`

InnoDB 内部维护了一个 max_trx_id 全局变量，每次需要申请一个新的 trx_id 时，就获得 max_trx_id 的当前值，然后并将 max_trx_id 加 1。

InnoDB 数据可见性的核心思想是：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。但是这个过程有脏读存在，那么这个id就不会是原子性的，存在重复的可能性。

`thread_id`

其实，线程 id 才是 MySQL 中最常见的一种自增 id。平时我们在查各种现场的时候，show processlist 里面的第一列，就是 thread_id。

thread_id 的逻辑很好理解：系统保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量。

thread_id_counter 定义的大小是 4 个字节，因此达到 $2^{32}-1$ 后，它就会重置为 0，然后继续增加。结果跟row_id一样，就会覆盖原有记录了。

上面介绍了几种MySQL自身的一些自增id，其实，实际运用中，我们也可能会选择外部的自增主键，然后持久化到数据库，以此来代替数据库自身的自增id。下面来说说吧。

Redis自身的自增主键

Redis自身是原子性的，因此高并发也是线程安全的。假设主键字段长度20，我们以时间+自增数来构成主键，例如：8位日期+12位自增数。那么，根据业务性质可以决定时间取年月日或者到毫秒级，那么在毫秒之间自增数的重复概率是极小极小的，基本的业务都能适用。

非關係型數據庫 – Redis

嘗試理解内存

Redis所有的數據都存在内存中，當前内存雖然越來越便宜，但跟廉價的硬碟相比成本還是比較昂貴，因此如何高效利用Redis内存變得非常重要。高效利用Redis内存首先需要理解Redis内存消耗在哪里，如何管理内存，最後才能考慮如何優化内存。掌握這些知識後能夠實現用更少的内存存儲更多的數據，從而降低成本。