場景

應用開發過程中，初期數據量少，開發人員更重視功能上的實現。應用上線以后，隨著數據量急劇增長，一些SQL語句暴露出性能問題，這時系統性能的瓶頸就是這些有問題的SQL語句。

• 加載了案例庫sakila（電影出租廳管理系統）

優化的步驟

通過show status查看語句的使用頻率

show status like 'Com_%';

所有存儲引擎

主要關注的幾個參數：

Com_select

Com_update

Com_insert

Com_delete

innodb存儲引擎

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

---

 

目的

通過以上參數，可以了解到當前數據庫的應用是以插入更新為主還是查詢為主，以及各種SQL大致的執行比例。對于更新操作的計數，是對執行次數的計數，不論提交還是回滾都會進行累加。

對于事務型的應用，通過Com_commit和Com_rollback可以了解事務提交和回滾的情況，對于回滾操作非常頻繁的數據庫，可能意味著應用編寫存在問題。

基本情況

Connections:試圖連接MySQL的次數
Uptime:服務器工作時間
Slow_queries:慢查詢次數

定位執行效率低的SQL語句

兩種方式

• 慢查詢日志
• show processlist
  慢查詢日志在查詢結束以后才記錄，所以在應用反映執行效率出現問題時查詢慢查詢日志并不能定位問題，可以使用show processlist查看當前MySQL在進行的線程，包括線程的狀態、是否鎖表等，可以實時地查看SQL的執行情況，同時對一些鎖表操作進行優化。

通過EXPLAIN分析低效SQL的執行計劃

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

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='[email protected]'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)

參數	值	概述
select_type	simple primary union subquery	select的類型，有simple（簡單表（不用表連接或者子查詢））,primary（主查詢（外層的查詢）），union（UNION中的第二個或者后面的查詢語句），subquery（子查詢中的第一個select）
table		輸出結果集的表
type	all index range ref eq_ref const system null	從左至右性能由最差到最好
possible_keys		查詢時可能用到的索引
key		實際使用的索引
key_len		使用到的索引字段的長度
rows		掃描行的數量
Extra		執行情況的說明和描述，包括不適合在其他列中顯示但是對執行計劃非常重要的額外信息

通過show profile 分析SQL

默認profiling是關閉的，可以通過set語句在Session級別開啟profiling

select @@profiling;
set profiling =1

舉例

對于MyISAM表有表的元數據緩存（例如行數count（）），但是InnoDB沒有，count（），執行很慢。

1. 這是innodb引擎上的payment表執行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;看到執行過程中線程對的每個狀態和消耗的時間。

show profile for query 2;

發現時間主要消耗在sending data上了，這個狀態是訪問數據返回結果。

1. 為了更仔細地觀察排序結果，可以查詢information_schema.profiling表，并按照時間做個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. 在獲得了最消耗時間的線程狀態后，MySQL還支持進一步選擇all、cpu、block、io、context、switch、page faults等明細類型查看MySQL在使用什么資源上耗費了過高的時間。

show profile cpu for query 2;

1. 對比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可以告訴我們時間都耗費在哪了。MySQL 5.6通過trace文件進一步向我們展示了優化器是如何選擇執行計劃的。

通過trace文件分析優化器如何選擇執行計劃

1. 打開trace并設置格式為JSON，設置trace最大能夠使用的內存。

SET OPTOMOZER_TRACE='enabled=on',END_MARKERS_IN_JSON=on;
SET OPTOMOZER_TRACE_MAX_MEN_SIZE=1000000;

1. 執行想做trace的SQL語句。
2. 檢查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何執行SQL的。

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE G

確定問題并采取相應的優化措施

場景

例如要查詢某個email為租賃電影拷貝所支付的總金額，需要關聯顧客表customer和付款表payment，并且對金額amount求和。

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='[email protected]'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)

我們可以確認對客戶表customer的全盤掃描導致效率不理想，那么對客戶表customer的email字段創建索引

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='[email protected]'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)

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

索引問題

索引存儲的分類

MySQL索引的分類、存儲、使用方法。

分類

索引	概述
B-Tree索引	最常見的索引，大部分引擎都支持B數索引
HASH索引	只有Memory引擎支持，場景簡單
R-Tree索引（空間索引）	空間索引是MyISAM的一個特殊索引類型，主要用于地理空間數據類型，使用較少
Full-text索引（全文索引）	空間索引也是MyISAM的一個特殊索引類型，主要用于全文索引，MySQL5.6版本開始支持全文索引

MySQL暫時不支持函數索引，但是能對前面某一部分進行索引，例如標題title字段可以只取title的前十個字符進行索引，這個特性可以大大縮小索引的大小。但是前綴索引有個缺點，在排序Order By和分組Group by時無法使用。前綴索引：

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

常用引擎支持的索引類型

索引	MyISAM	Innodb	memory
B-Tree	√	√	√
HASH	×	×	√
R-Tree	√	×	×
Full-text	√	√	×

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

如何使用索引（B-Tree）

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

應用場景

可以利用B-Tree索引進行全關鍵字、關鍵字范圍和關鍵字前綴查詢。

• 匹配全值
• 匹配值的范圍查詢
• 匹配最左前綴
• 僅僅對索引進行查詢
• 匹配列前綴
• 能夠實現索引匹配部分精確而其他部分進行范圍匹配
• 如果列名是索引，那么使用column_name is null 就會使用索引。

有索引但是不能使用的場景

• 以%開頭的LIKE查詢
• 數據類型出現隱式轉換時
  特別是當列是字符串，那么一定記得在where條件中把字符常量值用引號引起來，因為mysql默認把輸入的常量值進行轉換以后才進行檢索。
• 不滿足最左原則
• 若MySQL估計使用索引比全表掃描更慢
  trace中看優化去選擇的過程。會發現選擇的代價cost，比較選擇。
• 用or分割開的條件
  前邊條件有索引但是后邊的條件不包含索引則涉及的索引都不會用到。

查看索引使用情況

Handler_read_key如果索引正在工作，值會變很高，代表了一個行被索引值讀的次數，很低的值代表增加的索引性能改善不高，因為索引不經常使用。

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

show status like 'Handler_read%';

兩個簡單的優化方法

定期分析表和檢查表

analyze table payment;
check table payment_myisam;

定期優化表

optimize table payment_myisam;

對于Innodb表來說，設置innodb_fil_per_table參數，設置Innodb為獨立表空間模式，這樣每個庫的每個表都會生成一個獨立的ibd文件，用于存儲表的數據和索引，這樣可以一定程度上實現InnoDB表的空間回收問題。另外，在刪除大量數據后，InnoDB表可以通過alter table但是不修改引擎的方式來回收不用的空間。

alter table payment engine=innodb;

ANALYZE , CHECK , OPTIMIZE , ALTER TABLE執行期間都會對表進行鎖定，因此一定要注意在數據庫不繁忙的時候執行這些操作。

常用SQL的優化（insert、group by等）

對于InnoDB

大批量插入數據

1. 將導入的數據按照主鍵的順序排列可以提高導入數據的效率。
2. 導入數據前，執行SET UNIQUE_CHECKS=0。關閉唯一性校驗，導入結束后執行SET UNIQUE_CHECKS=1恢復唯一性校驗。
3. 如果應用采用自動提交方式，建議在導入前執行SET AUTOCOMMIT=0關閉自動提交，導入結束后執行SET AUTOCOMMIT=0恢復。

優化insert語句

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

優化order by語句

1. 排序方式

• 通過有序索引順序掃描返回有序數據，使用explain時Extra的值為Using index。
• 對返回數據排序，Filesort排序。
  盡量減少額外的排序，通過索引直接返回有序數據。order by和where使用相同的索引，并且order by 的順序和索引對的順序相同。
  不使用索引的情況
• order by字段中混合含有DESC、ASC。
• 用于查詢行的關鍵字與order by中所使用的不同。

select * from 1 where col1='a' order by col2;

• 對不同的關鍵字使用order by

1. Filesort的優化

• 兩次掃描法
• 一次掃描法

GROUP BY語句的優化

優化嵌套查詢

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

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優化OR條件

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

優化分頁查詢

一般分頁查詢時，通過創建覆蓋索引可以提高性能。

第一種優化思路

在索引上完成排序分頁的操作，最后根據主鍵關聯回源表查詢所需要的其他列的內容。

• 對標題排序后，取某一頁數據，從結果看進行了全表掃描，效率不高。

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)

• 按照索引分頁后回表方式改寫SQL后結果中已經看不出全表掃描了。

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掃描盡可能少的頁面來提高分頁效率
https://segmentfault.com/a/1190000008131735

第二種優化思路

把limit查詢轉換成某個位置的查詢
提前確定位置，將limit m,n 的查詢編程limit n的查詢。這種情況只適合在排序字段不會出現重復值的特定環境，能夠減少分頁帶來的壓力；如果排序字段出現大量的重復，仍進行這種優化那么分頁結果可能會丟失部分記錄。

使用SQL提示

SQL提示（SQL HINT）。簡單的說就是在SQL語句中加入一些人為的提示來達到優化操作的目的。

SELCT SQL_BUFFER_RESULT * FROM ... 

這個語句將強制MySQL生成一個臨時的結果集。只要臨時結果集生成后，所有表上的鎖定均被釋放。這能在遇到表鎖定問題時或要花很長時間將結果發給客戶端時有用，因為可以盡快釋放鎖資源。

• 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
  忽略一個或者多個索引。
• FORCE INDEX
  強制使用一個特定的索引。

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)

因為大部分的id都大于1，所以MySQL會默認使用全表掃描。

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技巧

正則表達式

模式	描述

模式	描述
^	匹配輸入字符串的開始位置。如果設置了 RegExp 對象的 Multiline 屬性，^ 也匹配 'n' 或 'r' 之后的位置。
$	匹配輸入字符串的結束位置。如果設置了RegExp 對象的 Multiline 屬性，$ 也匹配 'n' 或 'r' 之前的位置。
.	匹配除 "n" 之外的任何單個字符。要匹配包括 'n' 在內的任何字符，請使用象 '[.n]' 的模式。
[...]	字符集合。匹配所包含的任意一個字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	負值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 則匹配 "zood" 或 "food"。
*	匹配前面的子表達式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等價于{0,}。
+	匹配前面的子表達式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等價于 {1,}。
{n}	n 是一個非負整數。匹配確定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的兩個 o。
{n,m}	m 和 n 均為非負整數，其中n <= m。最少匹配 n 次且最多匹配 m 次。

使用RAND()隨機提取行

抽樣分析統計時很有用。

select * from category by rand() limit 5;

利用GROUP BY的WITH ROLLUP子句

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

• 在payment表中，按照支付時間的年月、經手員工的編號列分組對支付金額amount列進行聚合計算如下：

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做統計

如何使用GROUP BY語句和BIT_AND、BIT_OR函數完成統計工作。這兩個函數的用途就是做數值之間的邏輯位運算，但是當它們與GROUP BY 聯合使用就可以完成一些其他操作。

• 場景
  超市要記錄每個顧客來超市都購買了哪些商品。（面包、牛奶、餅干、啤酒）

1. 通常的處理方法是，建立購物單表，記錄時間、顧客；再建立一個購物單明細，記錄購買的商品。這樣設計的優點是可以記錄商品的詳細信息（數量價格種類）。但是如果我們只需要知道顧客購買的商品的種類和總價格，那么這個數據結構就復雜。
2. 一個表實現這個功能。并且用一個字段用字符串 的方式記錄顧客購買的所有商品的商品號。但是如果顧客一次購買的商品很多，需要很大的存儲空間，則做統計的時候也會捉襟見肘。
3. 最好的解決辦法是：類似與第二種方法，仍用一個字段表示顧客購買的商品信息，但是是數值類型而不是字符串類型，這個字段存儲一個十進制的數字，當她轉換成二進制時，每一位二進制數字代表一個商品該位為1，則顧客購買了該商品；否則，沒有購買。例如面包牛奶面包啤酒代表4位二進制。在數據庫中用BIT_OR()操作就可以知道這個用戶購買過什么商品（或操作）;BIT_AND()可以知道這個用戶每次來超市都買的東西（與操作）。

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)

數據庫名、表名大小寫問題

UNIX對大小寫敏感，windows對大小寫不敏感。所以在兩種系統中，會因為名字的問題產生沖突。最好采用一致的轉換，例如總是用小寫創建并引用數據庫名和表名。
使用lower_case_tables_name來選擇如何在硬盤上保存和使用表名、數據庫名。

值	含義
0	對大小寫敏感，怎么創建怎么保存。（UNIX默認值）
1	表名在硬盤上用小寫保存，名稱對大小寫敏感。（Windows默認值）
2	怎么創建怎么保存在硬盤上，但是MySQL將其轉換成小寫以便查詢使用。在對大小寫不敏感的系統上使用。

只在一個平臺上使用MySQL是不用設置這個變量的。

使用外鍵需要注意的問題

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 |