日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告：魔扣目錄網(wǎng)為廣大站長(zhǎng)提供免費(fèi)收錄網(wǎng)站服務(wù)，提交前請(qǐng)做好本站友鏈：【 網(wǎng)站目錄：http://www.ylptlb.cn 】， 免友鏈快審服務(wù)（50元/站），

點(diǎn)擊這里在線咨詢客服
新站提交
  • 網(wǎng)站：51998
  • 待審：31
  • 小程序：12
  • 文章：1030137
  • 會(huì)員：747

SQL 優(yōu)化

發(fā)布時(shí)間：2023-07-02 22:50:45 作者：網(wǎng)友整理

場(chǎng)景

應(yīng)用開發(fā)過程中，初期數(shù)據(jù)量少，開發(fā)人員更重視功能上的實(shí)現(xiàn)。應(yīng)用上線以后，隨著數(shù)據(jù)量急劇增長(zhǎng)，一些SQL語(yǔ)句暴露出性能問題，這時(shí)系統(tǒng)性能的瓶頸就是這些有問題的SQL語(yǔ)句。

  • 加載了案例庫(kù)sakila（電影出租廳管理系統(tǒng)）

優(yōu)化的步驟

通過show status查看語(yǔ)句的使用頻率

show status like 'Com_%';

所有存儲(chǔ)引擎

主要關(guān)注的幾個(gè)參數(shù)：

Com_select

Com_update

Com_insert

Com_delete

innodb存儲(chǔ)引擎

Innodb_rows_read			    select查詢返回的行數(shù)
Innodb_rows_inserted			執(zhí)行insert插入的行數(shù)
Innodb_rows_updated				執(zhí)行update操作更新的行數(shù)
Innodb_rows_deleteed			執(zhí)行delete操作刪除的行數(shù)

 

目的

通過以上參數(shù)，可以了解到當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插入更新為主還是查詢?yōu)橹鳎约案鞣NSQL大致的執(zhí)行比例。對(duì)于更新操作的計(jì)數(shù)，是對(duì)執(zhí)行次數(shù)的計(jì)數(shù)，不論提交還是回滾都會(huì)進(jìn)行累加。

對(duì)于事務(wù)型的應(yīng)用，通過Com_commit和Com_rollback可以了解事務(wù)提交和回滾的情況，對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫(kù)，可能意味著應(yīng)用編寫存在問題。

基本情況

Connections:試圖連接MySQL的次數(shù)
Uptime:服務(wù)器工作時(shí)間
Slow_queries:慢查詢次數(shù)

定位執(zhí)行效率低的SQL語(yǔ)句

兩種方式

  • 慢查詢?nèi)罩?/li>
  • show processlist
    慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才記錄，所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題時(shí)查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}，可以使用show processlist查看當(dāng)前MySQL在進(jìn)行的線程，包括線程的狀態(tài)、是否鎖表等，可以實(shí)時(shí)地查看SQL的執(zhí)行情況，同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。

通過EXPLAIN分析低效SQL的執(zhí)行計(jì)劃

通過以上步驟查詢到低效的SQL語(yǔ)句后，可以用EXPLAIN或者DESC命令獲取MySQL如何執(zhí)行select語(yǔ)句的信息，包括在select語(yǔ)句執(zhí)行過程中表如何連接和連接的順序。例如要查詢某個(gè)email為租賃電影拷貝所支付的總金額，需要關(guān)聯(lián)顧客表customer和付款表payment，并且對(duì)金額amount求和。

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='MARY.SMITH@sakilacustomer.org'G


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 13
        Extra: NULL
2 rows in set (0.00 sec)

參數(shù)

值

概述

select_type

simple primary union subquery

select的類型，有simple（簡(jiǎn)單表（不用表連接或者子查詢））,primary（主查詢（外層的查詢）），union（UNION中的第二個(gè)或者后面的查詢語(yǔ)句），subquery（子查詢中的第一個(gè)select）

table

 

輸出結(jié)果集的表

type

all index range ref eq_ref const system null

從左至右性能由最差到最好

possible_keys

 

查詢時(shí)可能用到的索引

key

 

實(shí)際使用的索引

key_len

 

使用到的索引字段的長(zhǎng)度

rows

 

掃描行的數(shù)量

Extra

 

執(zhí)行情況的說明和描述，包括不適合在其他列中顯示但是對(duì)執(zhí)行計(jì)劃非常重要的額外信息

通過show profile 分析SQL

默認(rèn)profiling是關(guān)閉的，可以通過set語(yǔ)句在Session級(jí)別開啟profiling

select @@profiling;
set profiling =1

舉例

對(duì)于MyISAM表有表的元數(shù)據(jù)緩存（例如行數(shù)count（）），但是InnoDB沒有，count（），執(zhí)行很慢。

  1. 這是innodb引擎上的payment表執(zhí)行count（*）查詢。
mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.01 sec)

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00007075 | select @@profiling           |
|        2 | 0.00514425 | select count(*) from payment |
+----------+------------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
  1. 通過show profile for query 2;看到執(zhí)行過程中線程對(duì)的每個(gè)狀態(tài)和消耗的時(shí)間。
show profile for query 2;

發(fā)現(xiàn)時(shí)間主要消耗在sending data上了，這個(gè)狀態(tài)是訪問數(shù)據(jù)返回結(jié)果。

  1. 為了更仔細(xì)地觀察排序結(jié)果，可以查詢information_schema.profiling表，并按照時(shí)間做個(gè)DESC排序。
set @query_id := 4;
SELECT STATE, SUM(DURATION) AS Total_R,
	ROUND(	100*SUM(DURATION) /
			(SELECT SUM(DURATION)
			FROM INFORMATION_SCHEMA.PROFILING
			WHERE QUERY_ID = @query_id
		),2) AS Pct_R,
	COUNT(*) AS Calls,
	SUM(DURATION) / COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC;


+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.004972 | 96.62 |     1 | 0.0049720000 |
| starting             | 0.000045 |  0.87 |     1 | 0.0000450000 |
| freeing items        | 0.000022 |  0.43 |     1 | 0.0000220000 |
| Opening tables       | 0.000019 |  0.37 |     1 | 0.0000190000 |
| cleaning up          | 0.000013 |  0.25 |     1 | 0.0000130000 |
| statistics           | 0.000011 |  0.21 |     1 | 0.0000110000 |
| init                 | 0.000011 |  0.21 |     1 | 0.0000110000 |
| preparing            | 0.000010 |  0.19 |     1 | 0.0000100000 |
| closing tables       | 0.000010 |  0.19 |     1 | 0.0000100000 |
| end                  | 0.000008 |  0.16 |     1 | 0.0000080000 |
| System lock          | 0.000007 |  0.14 |     1 | 0.0000070000 |
| checking permissions | 0.000006 |  0.12 |     1 | 0.0000060000 |
| query end            | 0.000006 |  0.12 |     1 | 0.0000060000 |
| optimizing           | 0.000004 |  0.08 |     1 | 0.0000040000 |
| executing            | 0.000002 |  0.04 |     1 | 0.0000020000 |
+----------------------+----------+-------+-------+--------------+
  1. 在獲得了最消耗時(shí)間的線程狀態(tài)后，MySQL還支持進(jìn)一步選擇all、cpu、block、io、context、switch、page faults等明細(xì)類型查看MySQL在使用什么資源上耗費(fèi)了過高的時(shí)間。
show profile cpu for query 2;
  1. 對(duì)比MyISAM的count（*）
create table payment_myisam like payment;
alter table payment_myisam engine=myisam;
insert into payment_myisam select * from payment;
select count(*) from payment_myisam;
show profiles;
show profile for query N;


+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000048 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000020 |
| init                 | 0.000013 |
| System lock          | 0.000007 |
| optimizing           | 0.000006 |
| executing            | 0.000009 |
| end                  | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000008 |
| freeing items        | 0.000009 |
| cleaning up          | 0.000015 |
+----------------------+----------+

show profile可以告訴我們時(shí)間都耗費(fèi)在哪了。MySQL 5.6通過trace文件進(jìn)一步向我們展示了優(yōu)化器是如何選擇執(zhí)行計(jì)劃的。

通過trace文件分析優(yōu)化器如何選擇執(zhí)行計(jì)劃

  1. 打開trace并設(shè)置格式為JSON，設(shè)置trace最大能夠使用的內(nèi)存。
SET OPTOMOZER_TRACE='enabled=on',END_MARKERS_IN_JSON=on;
SET OPTOMOZER_TRACE_MAX_MEN_SIZE=1000000;
  1. 執(zhí)行想做trace的SQL語(yǔ)句。
  2. 檢查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何執(zhí)行SQL的。
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE G

確定問題并采取相應(yīng)的優(yōu)化措施

場(chǎng)景

例如要查詢某個(gè)email為租賃電影拷貝所支付的總金額，需要關(guān)聯(lián)顧客表customer和付款表payment，并且對(duì)金額amount求和。

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='MARY.SMITH@sakilacustomer.org'G


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 13
        Extra: NULL
2 rows in set (0.00 sec)

我們可以確認(rèn)對(duì)客戶表customer的全盤掃描導(dǎo)致效率不理想，那么對(duì)客戶表customer的email字段創(chuàng)建索引

create index idx_email on customer(email);
explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='MARY.SMITH@sakilacustomer.org'G


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref
possible_keys: PRIMARY,idx_email
          key: idx_email
      key_len: 153
          ref: const
         rows: 1
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 13
        Extra: NULL
2 rows in set (0.00 sec)

可以看到我們檢索的行數(shù)從599行變成了1行。

索引問題

索引存儲(chǔ)的分類

MySQL索引的分類、存儲(chǔ)、使用方法。

分類

索引

概述

B-Tree索引

最常見的索引，大部分引擎都支持B數(shù)索引

HASH索引

只有Memory引擎支持，場(chǎng)景簡(jiǎn)單

R-Tree索引（空間索引）

空間索引是MyISAM的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型，使用較少

Full-text索引（全文索引）

空間索引也是MyISAM的一個(gè)特殊索引類型，主要用于全文索引，MySQL5.6版本開始支持全文索引

MySQL暫時(shí)不支持函數(shù)索引，但是能對(duì)前面某一部分進(jìn)行索引，例如標(biāo)題title字段可以只取title的前十個(gè)字符進(jìn)行索引，這個(gè)特性可以大大縮小索引的大小。但是前綴索引有個(gè)缺點(diǎn)，在排序Order By和分組Group by時(shí)無(wú)法使用。前綴索引：

create index idx_title on film(title(10));

常用引擎支持的索引類型

索引

MyISAM

Innodb

memory

B-Tree

√

√

√

HASH

×

×

√

R-Tree

√

×

×

Full-text

√

√

×
  • Innodb自動(dòng)生成哈希索引但是不支持用戶干預(yù)。
  • 最常用的就是B樹和哈希索引。哈希索引相對(duì)簡(jiǎn)單，適用于Key-Value查詢，通過Hash索引要比通過B-Tree索引查詢更快速；哈希索引不適用范圍查詢，例如< , > , <= , >= 這類操作。
  • 如果使用memory/Heap引擎并且where條件中不使用‘’=‘’進(jìn)行索引列，那么不會(huì)用到索引。

如何使用索引（B-Tree）

B-Tree索引中的B不是代表二叉樹（binary），而是代表平衡樹（balanced）。

應(yīng)用場(chǎng)景

可以利用B-Tree索引進(jìn)行全關(guān)鍵字、關(guān)鍵字范圍和關(guān)鍵字前綴查詢。

  • 匹配全值
  • 匹配值的范圍查詢
  • 匹配最左前綴
  • 僅僅對(duì)索引進(jìn)行查詢
  • 匹配列前綴
  • 能夠?qū)崿F(xiàn)索引匹配部分精確而其他部分進(jìn)行范圍匹配
  • 如果列名是索引，那么使用column_name is null 就會(huì)使用索引。

有索引但是不能使用的場(chǎng)景

  • 以%開頭的LIKE查詢
  • 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換時(shí)
    特別是當(dāng)列是字符串，那么一定記得在where條件中把字符常量值用引號(hào)引起來(lái)，因?yàn)閙ysql默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索。
  • 不滿足最左原則
  • 若MySQL估計(jì)使用索引比全表掃描更慢
    trace中看優(yōu)化去選擇的過程。會(huì)發(fā)現(xiàn)選擇的代價(jià)cost，比較選擇。
  • 用or分割開的條件
    前邊條件有索引但是后邊的條件不包含索引則涉及的索引都不會(huì)用到。

查看索引使用情況

Handler_read_key如果索引正在工作，值會(huì)變很高，代表了一個(gè)行被索引值讀的次數(shù)，很低的值代表增加的索引性能改善不高，因?yàn)樗饕唤?jīng)常使用。

Handler_read_rnd_next數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。值高意味著查詢運(yùn)行低效，應(yīng)建立索引補(bǔ)救。如果有大量的表掃描，Handler_read_rnd_next值高通常意味著索引不正確或者寫入的查詢沒有利用索引。

show status like 'Handler_read%';

兩個(gè)簡(jiǎn)單的優(yōu)化方法

定期分析表和檢查表

analyze table payment;
check table payment_myisam;

定期優(yōu)化表

optimize table payment_myisam;

對(duì)于Innodb表來(lái)說，設(shè)置innodb_fil_per_table參數(shù)，設(shè)置Innodb為獨(dú)立表空間模式，這樣每個(gè)庫(kù)的每個(gè)表都會(huì)生成一個(gè)獨(dú)立的ibd文件，用于存儲(chǔ)表的數(shù)據(jù)和索引，這樣可以一定程度上實(shí)現(xiàn)InnoDB表的空間回收問題。另外，在刪除大量數(shù)據(jù)后，InnoDB表可以通過alter table但是不修改引擎的方式來(lái)回收不用的空間。

alter table payment engine=innodb;

ANALYZE , CHECK , OPTIMIZE , ALTER TABLE執(zhí)行期間都會(huì)對(duì)表進(jìn)行鎖定，因此一定要注意在數(shù)據(jù)庫(kù)不繁忙的時(shí)候執(zhí)行這些操作。

常用SQL的優(yōu)化（insert、group by等）

對(duì)于InnoDB

大批量插入數(shù)據(jù)

  1. 將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排列可以提高導(dǎo)入數(shù)據(jù)的效率。
  2. 導(dǎo)入數(shù)據(jù)前，執(zhí)行SET UNIQUE_CHECKS=0。關(guān)閉唯一性校驗(yàn)，導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1恢復(fù)唯一性校驗(yàn)。
  3. 如果應(yīng)用采用自動(dòng)提交方式，建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0關(guān)閉自動(dòng)提交，導(dǎo)入結(jié)束后執(zhí)行SET AUTOCOMMIT=0恢復(fù)。

優(yōu)化insert語(yǔ)句

  1. 對(duì)于同意客戶，盡量使用多個(gè)值的insert語(yǔ)句
  2. 對(duì)于從不同客戶插入很多行，可以使用INSERT DELAYED語(yǔ)句得到更高的速度。DELAYED語(yǔ)句的含義是讓insert語(yǔ)句立即執(zhí)行，其實(shí)數(shù)據(jù)都被放在內(nèi)存隊(duì)列中，并沒有寫入磁盤，這比每條語(yǔ)句分別插入要快得多。LOW_PRIORITY正好相反，對(duì)所有用戶對(duì)表的讀寫完成之后才進(jìn)行插入。
  3. 將索引文件和數(shù)據(jù)文件在不同的磁盤上存放。（利于建表中的選項(xiàng)）
  4. MyISAM適用：批量插入時(shí)，增加bulk_insert_buffer_size變量值的方法來(lái)提高速度。
  5. 當(dāng)一個(gè)文本文件裝載一個(gè)表時(shí)，使用LOAD DATA INFILE,比使用insert速度會(huì)提高20倍。

優(yōu)化order by語(yǔ)句

  1. 排序方式
  • 通過有序索引順序掃描返回有序數(shù)據(jù)，使用explain時(shí)Extra的值為Using index。
  • 對(duì)返回?cái)?shù)據(jù)排序，F(xiàn)ilesort排序。
    盡量減少額外的排序，通過索引直接返回有序數(shù)據(jù)。order by和where使用相同的索引，并且order by 的順序和索引對(duì)的順序相同。
    不使用索引的情況
  • order by字段中混合含有DESC、ASC。
  • 用于查詢行的關(guān)鍵字與order by中所使用的不同。
select * from 1 where col1='a' order by col2;
  • 對(duì)不同的關(guān)鍵字使用order by
  1. Filesort的優(yōu)化
  • 兩次掃描法
  • 一次掃描法

GROUP BY語(yǔ)句的優(yōu)化

優(yōu)化嵌套查詢

將嵌套查詢改為更有效率的連接

select * from customer where customer_id not in (select cutomer_id from payment);
select * from customer a left join payment on customer.customer_ip=payment.customer_id where payment.customer_id is null;

MySQL優(yōu)化OR條件

對(duì)于含有OR條件的查詢語(yǔ)句，要想利用索引，則每個(gè)條件列都必須用到索引；如果沒有，可以考慮增加索引。

優(yōu)化分頁(yè)查詢

一般分頁(yè)查詢時(shí)，通過創(chuàng)建覆蓋索引可以提高性能。

第一種優(yōu)化思路

在索引上完成排序分頁(yè)的操作，最后根據(jù)主鍵關(guān)聯(lián)回源表查詢所需要的其他列的內(nèi)容。

  • 對(duì)標(biāo)題排序后，取某一頁(yè)數(shù)據(jù)，從結(jié)果看進(jìn)行了全表掃描，效率不高。
mysql> explain select film_id, description from film order by title limit 50, 5G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using filesort
1 row in set (0.00 sec)
  • 按照索引分頁(yè)后回表方式改寫SQL后結(jié)果中已經(jīng)看不出全表掃描了。
mysql> explain select a.film_id, a.description from film a  inner join (select film_id from film order by title limit 50, 5)b on a.film_idG
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
        Extra: Using index

這種方式讓mysql掃描盡可能少的頁(yè)面來(lái)提高分頁(yè)效率
https://segmentfault.com/a/1190000008131735

第二種優(yōu)化思路

把limit查詢轉(zhuǎn)換成某個(gè)位置的查詢
提前確定位置，將limit m,n 的查詢編程limit n的查詢。這種情況只適合在排序字段不會(huì)出現(xiàn)重復(fù)值的特定環(huán)境，能夠減少分頁(yè)帶來(lái)的壓力；如果排序字段出現(xiàn)大量的重復(fù)，仍進(jìn)行這種優(yōu)化那么分頁(yè)結(jié)果可能會(huì)丟失部分記錄。

使用SQL提示

SQL提示（SQL HINT）。簡(jiǎn)單的說就是在SQL語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的。

SELCT SQL_BUFFER_RESULT * FROM ...

這個(gè)語(yǔ)句將強(qiáng)制MySQL生成一個(gè)臨時(shí)的結(jié)果集。只要臨時(shí)結(jié)果集生成后，所有表上的鎖定均被釋放。這能在遇到表鎖定問題時(shí)或要花很長(zhǎng)時(shí)間將結(jié)果發(fā)給客戶端時(shí)有用，因?yàn)榭梢员M快釋放鎖資源。

  • USE INDEX
    提供MySQL期望的索引，不再考慮其他可用索引。
mysql> explain select count(*) from rental use index (rental_date)G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: index
possible_keys: NULL
          key: rental_date
      key_len: 10
          ref: NULL
         rows: 16005
        Extra: Using index
******************************************************
mysql> explain select count(*) from rentalG
           id: 1
  select_type: SIMPLE
        table: rental
         type: index
possible_keys: NULL
          key: idx_fk_staff_id
      key_len: 1
          ref: NULL
         rows: 16005
        Extra: Using index
  • IGNORE INDEX
    忽略一個(gè)或者多個(gè)索引。
  • FORCE INDEX
    強(qiáng)制使用一個(gè)特定的索引。
mysql> explain select * from rental where inventory_id>1G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: ALL
possible_keys: idx_fk_inventory_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16005
        Extra: Using where
1 row in set (0.01 sec)

因?yàn)榇蟛糠值膇d都大于1，所以MySQL會(huì)默認(rèn)使用全表掃描。

mysql> explain select * from rental FORCE INDEX(idx_fk_inventory_id)  where inventory_id>1G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: range
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: NULL
         rows: 8002
        Extra: Using index condition
1 row in set (0.00 sec)

這種情況使用use index 是不能指定索引的。

常用的SQL技巧

正則表達(dá)式

模式

描述

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

模式

描述

^

匹配輸入字符串的開始位置。如果設(shè)置了 RegExp 對(duì)象的 Multiline 屬性，^ 也匹配 'n' 或 'r' 之后的位置。

$

匹配輸入字符串的結(jié)束位置。如果設(shè)置了RegExp 對(duì)象的 Multiline 屬性，$ 也匹配 'n' 或 'r' 之前的位置。

.

匹配除 "n" 之外的任何單個(gè)字符。要匹配包括 'n' 在內(nèi)的任何字符，請(qǐng)使用象 '[.n]' 的模式。

[...]

字符集合。匹配所包含的任意一個(gè)字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。

[^...]

負(fù)值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。

p1|p2|p3

匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 則匹配 "zood" 或 "food"。

*

匹配前面的子表達(dá)式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等價(jià)于{0,}。

+

匹配前面的子表達(dá)式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等價(jià)于 {1,}。

{n}

n 是一個(gè)非負(fù)整數(shù)。匹配確定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的兩個(gè) o。

{n,m}

m 和 n 均為非負(fù)整數(shù)，其中n <= m。最少匹配 n 次且最多匹配 m 次。

使用RAND()隨機(jī)提取行

抽樣分析統(tǒng)計(jì)時(shí)很有用。

select * from category by rand() limit 5;

利用GROUP BY的WITH ROLLUP子句

使用這個(gè)子句可以檢索出更多的分組聚合信息，它不僅僅像一般的GROUP BY 語(yǔ)句那樣檢索出各組的聚合信息，還能檢索出本組類的整體聚合信息。

  • 在payment表中，按照支付時(shí)間的年月、經(jīng)手員工的編號(hào)列分組對(duì)支付金額amount列進(jìn)行聚合計(jì)算如下：
mysql> select date_format(payment_date, '%Y-%m'),staff_id, sum(amount) from payment group by date_format(payment_date, '%Y-%m'), staff_id;
+------------------------------------+----------+-------------+
| date_format(payment_date, '%Y-%m') | staff_id | sum(amount) |
+------------------------------------+----------+-------------+
| 2005-05                            |        1 |     2621.83 |
| 2005-05                            |        2 |     2202.60 |
| 2005-06                            |        1 |     4776.36 |
| 2005-06                            |        2 |     4855.52 |
| 2005-07                            |        1 |    14003.54 |
| 2005-07                            |        2 |    14370.35 |
| 2005-08                            |        1 |    11853.65 |
| 2005-08                            |        2 |    12218.48 |
| 2006-02                            |        1 |      234.09 |
| 2006-02                            |        2 |      280.09 |
+------------------------------------+----------+-------------+
10 rows in set (0.04 sec)

WITH ROLLUP

mysql> select date_format(payment_date, '%Y-%m'),IFNULL(staff_id, ''), sum(amount) from payment group by date_format(payment_date, '%Y-%m'), staff_id with rollup;
+------------------------------------+----------------------+-------------+
| date_format(payment_date, '%Y-%m') | IFNULL(staff_id, '') | sum(amount) |
+------------------------------------+----------------------+-------------+
| 2005-05                            | 1                    |     2621.83 |
| 2005-05                            | 2                    |     2202.60 |
| 2005-05                            |                      |     4824.43 |
| 2005-06                            | 1                    |     4776.36 |
| 2005-06                            | 2                    |     4855.52 |
| 2005-06                            |                      |     9631.88 |
| 2005-07                            | 1                    |    14003.54 |
| 2005-07                            | 2                    |    14370.35 |
| 2005-07                            |                      |    28373.89 |
| 2005-08                            | 1                    |    11853.65 |
| 2005-08                            | 2                    |    12218.48 |
| 2005-08                            |                      |    24072.13 |
| 2006-02                            | 1                    |      234.09 |
| 2006-02                            | 2                    |      280.09 |
| 2006-02                            |                      |      514.18 |
| NULL                               |                      |    67416.51 |
+------------------------------------+----------------------+-------------+
16 rows in set (0.02 sec)

使用BIT GROUP FUNCTIONS做統(tǒng)計(jì)

如何使用GROUP BY語(yǔ)句和BIT_AND、BIT_OR函數(shù)完成統(tǒng)計(jì)工作。這兩個(gè)函數(shù)的用途就是做數(shù)值之間的邏輯位運(yùn)算，但是當(dāng)它們與GROUP BY 聯(lián)合使用就可以完成一些其他操作。

  • 場(chǎng)景
    超市要記錄每個(gè)顧客來(lái)超市都購(gòu)買了哪些商品。（面包、牛奶、餅干、啤酒）
  1. 通常的處理方法是，建立購(gòu)物單表，記錄時(shí)間、顧客；再建立一個(gè)購(gòu)物單明細(xì)，記錄購(gòu)買的商品。這樣設(shè)計(jì)的優(yōu)點(diǎn)是可以記錄商品的詳細(xì)信息（數(shù)量?jī)r(jià)格種類）。但是如果我們只需要知道顧客購(gòu)買的商品的種類和總價(jià)格，那么這個(gè)數(shù)據(jù)結(jié)構(gòu)就復(fù)雜。
  2. 一個(gè)表實(shí)現(xiàn)這個(gè)功能。并且用一個(gè)字段用字符串 的方式記錄顧客購(gòu)買的所有商品的商品號(hào)。但是如果顧客一次購(gòu)買的商品很多，需要很大的存儲(chǔ)空間，則做統(tǒng)計(jì)的時(shí)候也會(huì)捉襟見肘。
  3. 最好的解決辦法是：類似與第二種方法，仍用一個(gè)字段表示顧客購(gòu)買的商品信息，但是是數(shù)值類型而不是字符串類型，這個(gè)字段存儲(chǔ)一個(gè)十進(jìn)制的數(shù)字，當(dāng)她轉(zhuǎn)換成二進(jìn)制時(shí)，每一位二進(jìn)制數(shù)字代表一個(gè)商品該位為1，則顧客購(gòu)買了該商品；否則，沒有購(gòu)買。例如面包牛奶面包啤酒代表4位二進(jìn)制。在數(shù)據(jù)庫(kù)中用BIT_OR()操作就可以知道這個(gè)用戶購(gòu)買過什么商品（或操作）;BIT_AND()可以知道這個(gè)用戶每次來(lái)超市都買的東西（與操作）。
mysql> create table order_rab(id int, customer_id int, kind int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into order_rab values(1,1,5),(4,2,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into order_rab values(3,2,3),(2,1,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from order_rab;
+------+-------------+------+
| id   | customer_id | kind |
+------+-------------+------+
|    1 |           1 |    5 |
|    4 |           2 |    4 |
|    3 |           2 |    3 |
|    2 |           1 |    4 |
+------+-------------+------+
4 rows in set (0.00 sec)

mysql> select customer_id,bit_or(kind) from order_rab group by customer_id;
+-------------+--------------+
| customer_id | bit_or(kind) |
+-------------+--------------+
|           1 |            5 |
|           2 |            7 |
+-------------+--------------+
2 rows in set (0.00 sec)

mysql> select customer_id,bit_and(kind) from order_rab group by customer_id;
+-------------+---------------+
| customer_id | bit_and(kind) |
+-------------+---------------+
|           1 |             4 |
|           2 |             0 |
+-------------+---------------+
2 rows in set (0.00 sec)

數(shù)據(jù)庫(kù)名、表名大小寫問題

UNIX對(duì)大小寫敏感，windows對(duì)大小寫不敏感。所以在兩種系統(tǒng)中，會(huì)因?yàn)槊值膯栴}產(chǎn)生沖突。最好采用一致的轉(zhuǎn)換，例如總是用小寫創(chuàng)建并引用數(shù)據(jù)庫(kù)名和表名。
使用lower_case_tables_name來(lái)選擇如何在硬盤上保存和使用表名、數(shù)據(jù)庫(kù)名。

值

含義

0

對(duì)大小寫敏感，怎么創(chuàng)建怎么保存。（UNIX默認(rèn)值）

1

表名在硬盤上用小寫保存，名稱對(duì)大小寫敏感。（Windows默認(rèn)值）

2

怎么創(chuàng)建怎么保存在硬盤上，但是MySQL將其轉(zhuǎn)換成小寫以便查詢使用。在對(duì)大小寫不敏感的系統(tǒng)上使用。

只在一個(gè)平臺(tái)上使用MySQL是不用設(shè)置這個(gè)變量的。

使用外鍵需要注意的問題

mysql> create table user2(id int, bookname varchar(20),userid int, primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table book2(id int, bookname varchar(10),userid int, primary key(id),constraint fk_user_id foreign key(userid) references user2(id))engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> inser into book2 values(1,"book",1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sakila`.`book2`, CONSTRAINT `fk_user_id` FOREIGN KEY (`userid`) REFERENCES `user2` (`id`))

mysql> show create table book2;
| Table | Create Table
| book2 | CREATE TABLE `book2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `bookname` varchar(10) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_id` (`userid`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`userid`) REFERENCES `user2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

分享到：
標(biāo)簽：SQL
用戶無(wú)頭像

網(wǎng)友整理

注冊(cè)時(shí)間：

網(wǎng)站：5 個(gè)   小程序：0 個(gè)  文章：12 篇

  • 51998

    網(wǎng)站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會(huì)員

趕快注冊(cè)賬號(hào)，推廣您的網(wǎng)站吧！
最新入駐小程序

數(shù)獨(dú)大挑戰(zhàn)2018-06-03

數(shù)獨(dú)一種數(shù)學(xué)游戲，玩家需要根據(jù)9

答題星2018-06-03

您可以通過答題星輕松地創(chuàng)建試卷

全階人生考試2018-06-03

各種考試題，題庫(kù)，初中，高中，大學(xué)四六

運(yùn)動(dòng)步數(shù)有氧達(dá)人2018-06-03

記錄運(yùn)動(dòng)步數(shù)，積累氧氣值。還可偷

每日養(yǎng)生app2018-06-03

每日養(yǎng)生,天天健康

體育訓(xùn)練成績(jī)?cè)u(píng)定2018-06-03

通用課目體育訓(xùn)練成績(jī)?cè)u(píng)定