目錄
-
- mysql中文亂碼問題
- 查詢每個機構年齡最大的人
- mysql的底層邏輯架構
- mysql的存儲引擎
-
- myISAM與InnoDB的差別
- 兩台不同的伺服器上的表進行聯合查詢
- 性能下降SQL慢，執行時間長，等待時間長，優化
- 關聯查詢
- 索引簡介
-
- 是什麼
- 優勢
- 劣勢
- 索引的結構
-
- BTree索引
- B+Tree索引
- 聚簇索引與非聚簇索引
- 索引的分類
-
- 單值索引
- 唯一索引
- 主鍵索引
- 複合索引
- 哪些情況下需要建立索引
- 哪些情況下不需要建立索引
- 性能分析Explain
-
- 是什麼（檢視執行計劃）
- 能幹什麼
- 怎麼用
- 如何在一張表中插入一百萬條資料，速度更快一些？
-
- 方法一
- 方法二
- 方法三
- 方法四
mysql中文亂碼問題
- 修改配置檔案
- 修改已經存在的資料庫的配置檔案
- 修改已經存在表的配置檔案
查詢每個機構年齡最大的人
CREATE TABlE mytbl2 (id INT,NAME VARCHAR(200), age INT,dept INT);
INSERT iNTO mytbl2 VALUES(1,'zhang3 ' ,33,101);
iNSERT INTO mytbl2 VALUES(2,'li4',34,101);
iNSERT INTo mytbl2 VALUES(3,'wangs',34,102);
INSERT INTO mytbl2 VALUES(4, ' zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);
-- 每個機構年齡最大 的人
-- 錯誤的 :5.7之後會報錯
SELECT NAME dept, MAX(age) FROM mytbl2 GROUP BY dept ;
-- 正确的
SELECT * FROM mytbl2 m iNNER JOIN(
SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.dept AND m.age=ab.maxage
mysql的底層邏輯架構
和其它資料庫相比，MySQL有點與衆不同，它的架構可以在多種不同場景中應用并發揮良好作用。主要展現在存儲引擎的架構上，
插件式的存儲引擎架構将查詢處理和其它的系統任務以及資料的存儲提取相分離。這種架構可以根據業務的需求和實際需要選擇合适的存儲引擎。
mysql的存儲引擎
myISAM與InnoDB的差別
建立一張表預設的存儲引擎是InnoDB
系統表用的myISAM
兩台不同的伺服器上的表進行聯合查詢
在AB伺服器之間再次增加一個資料中心伺服器進行資料處理
性能下降SQL慢，執行時間長，等待時間長，優化
-
資料過多
分庫分表
-
關聯了太多的表，太多join
SQL優化
-
沒有充分利用到索引
索引建立
-
伺服器調優及各個參數設定
調整my.cnf
關聯查詢
| 名稱 | 語句圖解 |
|---|---|
| 内連接配接 | |
| 左外連接配接 | |
| 右外連接配接（實際工作中很少用到，經常用左外連接配接，因為工作中左右連接配接隻能選擇一種，不能在一個查詢語句中都用，是以我麼實際生活中經常使用左外連接配接） | |
| 取A表獨有的資料 | |
| 全連接配接（mysql沒有全連接配接，隻有Oracle有） | |
| 去同 | |
索引簡介
是什麼
MySQL官方對索引的定義為：索引(Index）是幫助MySQL高效擷取資料的資料結構。
可以得到索引的本質：索引是資料結構。
你可以簡單了解為"排好序的快速查找資料結構”。
一般來說索引本身也很大，不可能全部存儲在記憶體中，是以索引往往以索引檔案的形式存儲的磁盤上
優勢
類似大學圖書館建書目索引，提高資料檢索的效率，降低資料庫的IO成本
通過索引列對資料進行排序，降低資料排序的成本，降低了CPU的消耗
劣勢
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。
因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案每次更新添加了索引列的字段，
都會調整因為更新所帶來的鍵值變化後的索引資訊
實際上索引也是一張表，該表儲存了主鍵與索引字段，并指向實體表的記錄，是以索引列也是要占用空間的。
索引的結構
BTree索引
| 原理圖 | |
|---|---|
| 非葉子節點包含資料 | 1、資料 2.、向下的指針 3、指向資料的指針 |
| 時間複雜度 | |
B+Tree索引
| 原理圖 | |
|---|---|
| 非葉子節點包含資料 | 1、資料 2.、向下的指針 |
聚簇索引與非聚簇索引
聚簇索引并不是一種單獨的索引類型，而是一種資料存儲方式。
術語‘聚簇’表示資料行和相鄰的鍵值聚簇的存儲在一起。
如下圖，左側的索引就是聚簇索引，因為資料行在磁盤的排列和索引排序保持一緻。
索引的分類
# 檢視t_emp表的索引字段
SHOW INDEX FROM t_emp;
單值索引
即一個索引隻包含單個列，一個表可以有多個單列索引
# 随表一起建索引customer_name
CREATE TABLE customer (
id INT(10)UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY(customer_name)
);
# 給t_emp表的NAME字段建立idx_name 單值索引
CREATE INDEX idx_name ON t_emp(NAME);
# 删除索引:
DROP INDEX idx_name on t_emp;
唯一索引
索引列的值必須唯一，但允許有空值
CREATE UNIQUE INDEX idx_empno ON t_emp(empno);
# 檢視t_emp表的索引字段
SHOW INDEX FROM t_emp;
主鍵索引
設定為主鍵後資料庫會自動建立索引，innodb為聚簇索引
複合索引
即一個索引包含多個列
CREATE INDEX idx_age_deptid_name ON t_emp(age,deptid,NAME);
# 檢視t_emp表的索引字段
SHOW INDEX FROM t_emp;
哪些情況下需要建立索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的字段應該建立索引
- 查詢中與其它表關聯的字段，外鍵關系建立索引
- 單鍵/組合索引的選擇問題，組合索引成本效益更高
- 查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度
- 查詢中統計或者分組字段
哪些情況下不需要建立索引
- 表記錄太少
- 經常增删改的表或者字段
- Where條件裡用不到的字段不建立索引
- 過濾性不好的不适合建索引
性能分析Explain
是什麼（檢視執行計劃）
使用EXPLAIN關鍵字可以模拟優化器執行SQL查詢語句，進而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸
能幹什麼
- 表的讀取順序
- 哪些索引可以使用
- 資料讀取操作的操作類型
- 哪些索引被實際使用
- 表之間的引用
- 每張表有多少行被實體查詢
怎麼用
Explain + SQL語句
如何在一張表中插入一百萬條資料，速度更快一些？
方法一
- 使用java語言拼接字元，使多條INSERT INTO 語句變成一條INSERT INTO 語句
INSERT INTO `category` (`id`, `name`, `parent_id`, `sorted`, `level`, `create_time`, `deleted`) VALUES
(1, '網際網路營銷', 0, 1, 1, '2020-08-21 18:06:03', 0),
(2, 'UI/UE互動設計', 0, 2, 1, '2020-08-21 18:08:28', 0),
(3, 'Web前端', 0, 3, 1, '2020-08-21 18:08:45', 0),
(4, '程式開發', 0, 4, 1, '2020-08-21 18:08:58', 0),
(5, '三維可視化設計', 0, 5, 1, '2020-08-21 18:09:13', 0),
(6, 'office', 0, 6, 1, '2020-08-21 18:09:23', 0)
.......
.......;
方法二
索引的優勢是查詢，排序快，但是插入，寫操作會降低速度。是以我們在執行插入100萬條資料之前，可以先把這張表的除主鍵索引外，其他索引都暫時删除掉。
方法三
關閉自動送出，當一百萬條資料都執行完，改為手動送出，隻送出一次。
方法四
java多線程插入資料。