MySQL

官方文檔

https://www.mysql.com/

官方的幫助文檔非常詳細，有任何問題都可以去搜索官方文檔。

對一條命令不太清楚的話，還可以直接敲help命令：

help create table;
help select;

安裝

下載MySQL社區(qū)版本：https://dev.mysql.com/downloads/mysql/

下載并安裝Generic版本，安裝文檔：

https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

安裝MySQL5.7：

#添加mysql用戶和用戶組
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql

#解壓
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql

#創(chuàng)建數(shù)據(jù)存放目錄，并賦予權限
shell> mkdir -p /opt/mydata/mysql57
shell> chown mysql:mysql /opt/mydata

#編輯配置文件
shell> vi /etc/my.cnf

[mysqld]
port = 3306
user = mysql
datadir = /opt/mydata/mydb57
log_error = error.log
socket = /tmp/mysql.sock

# 數(shù)據(jù)文件初始化，這一步執(zhí)行完/opt/mydata/mydb57目錄下就應該有內容了
shell> bin/mysqld --initialize-insecure --user=mysql

#啟動MySQL
shell> bin/mysqld_safe --user=mysql &

# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

#停止重啟MYSQL服務
service mysql.server restart

#修改默認root密碼
shell> mysql -p
mysql> set password='123456';

關于MySQL配置文件 my.cnf

優(yōu)先級從低到高

/etc/my.cnf → /etc/mysql/my.cnf → /usr/local/mysql/etc/my.cnf →～/.my.cnf

shell> mysql --help --verbose | grep my.cnf

多版本多實例安裝

多實例可以充分利用服務器資源，在已有MySQL5.7版本 3306 端口實例的基礎上，再配置另外一個MySQL5.7版本的實例，和MySQL5.6、MySQL5.8版本的實例。

#下載MySQL 5.6 和 8.0 的版本，并分解解壓和建立軟連接到mysql56、mysql80
shell > ll /usr/local | grep mysql
mysql -> mysql-5.7.32-linux-glibc2.12-x86_64
mysql56 -> mysql-5.6.51-linux-glibc2.12-x86_64
mysql-5.6.51-linux-glibc2.12-x86_64
mysql-5.7.32-linux-glibc2.12-x86_64
mysql80 -> mysql-8.0.22-linux-glibc2.12-x86_64
mysql-8.0.22-linux-glibc2.12-x86_64

#編輯配置文件
shell > vi /etc/my.cnf
#在已安裝的mysql5.7上啟動一個端口為3307新的實例
[mysqld1]
port = 3307
datadir = /opt/mydata/mydb57_2
socket = /tmp/mysql.sock1

#指定basedir，在mysql5.6上啟動端口為3308的新實例
[mysqld2]
port = 3308
datadir = /opt/mydata/mydb56
socket = /tmp/mysql.sock2
basedir = /usr/local/mysql56

#指定basedir，在mysql8.0上啟動端口為3309的新實例
[mysqld3]
port = 3309
datadir = /opt/mydata/mydb80
socket = /tmp/mysql.sock3
basedir = /usr/local/mysql80/

#初始化數(shù)據(jù)目錄
shell > mkdir /opt/mydata/mydb57_2
shell > mkdir /opt/mydata/mydb56
shell > mkdir /opt/mydata/mydb80
shell > chown mysql:mysql /opt/mydata/mydb57_2
shell > chown mysql:mysql /opt/mydata/mydb56
shell > chown mysql:mysql /opt/mydata/mydb80

shell > /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/opt/mydata/mydb57_2
shell > /usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/opt/mydata/mydb56
shell > /usr/local/mysql80/bin/mysqld --initialize-insecure --user=mysql --datadir=/opt/mydata/mydb80

#啟動所有實例
shell > /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running

shell > /usr/local/mysql/bin/mysqld_multi start 1
shell > /usr/local/mysql/bin/mysqld_multi start 2
shell > /usr/local/mysql/bin/mysqld_multi start 3

#連接多實例，修改root用戶密碼
shell > mysql -S /tmp/mysql.sock1 -p
mysql> set password='123456';

shell > mysql -S /tmp/mysql.sock2 -p
mysql> set password=password('123456');

shell > /usr/local/mysql80/bin/mysql -S /tmp/mysql.sock3 -p
mysql> set password='123456';

系統(tǒng)自帶的數(shù)據(jù)庫

系統(tǒng)變量和會話變量

--系統(tǒng)變量:修改全局變量，只有新創(chuàng)建的連接才會生效
show global vairables like '%lang_query_time%';
set global lang_query_time=10;

--會話變量：只在當前會話生效
show variables like '%lang_query_time%';
set lang_query_time=10;

權限管理

權限的粒度：庫權限、表權限、列權限

--創(chuàng)建用戶
create user 'david'@'%' identified by '123456';
create user 'david'@'192.168.1.%' identified by '123456'; --在192.168.1.*這個網(wǎng)段內可以訪問

--刪除用戶
drop user 'david'@'%';

--查看用戶某用戶的權限
show grants; --查看當前用戶的權限
show grants for 'david'@'%'; --查看指定用戶的權限

--授權
grant select,update,insert,delete on employees.* to 'david'@'%';

--修改密碼
alter user 'david'@'%' identified by '45678';

--添加新的權限
grant create,index on employees.* to 'david'@'%';

--刪除權限
revoke create,index on employees.* from 'david'@'%';
revoke all on *.* from 'david'@'10.237.102.60';

--同時可以把自己的權限賦予別的用戶
grant select,update,insert,delete on employees.* to 'david'@'10.237.102.60' with grant option;

權限信息相關表：

use mysql;

權限列表：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

慢查詢日志

mysql> show variables like '%slow_query%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slow_query_log      | ON       | 
| slow_query_log_file | slow.log |
+---------------------+----------+

shell> cat /etc/my.cnf
[mysqld]
#log
slow_query_log=1
long_query_time=5
slow_query_log_file=slow.log

慢查詢日志相關參數(shù)

清理慢查詢日志

1、重命名： mv slow.log slow.log-2021-04-08

2、mysql客戶端執(zhí)行 ：flush slow logs;（關閉slow log句柄，并重新建立）

慢查詢記錄到表

(root@localhost) [(none)]> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> select sleep(12);
+-----------+
| sleep(12) |
+-----------+
|         0 |
+-----------+
1 row in set (12.02 sec)

(root@localhost) [(none)]> select * from mysql.slow_log \G
*************************** 1. row ***************************
    start_time: 2021-04-08 16:24:10.304979
     user_host: root[root] @ localhost []
    query_time: 00:00:07.000412
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: mysql
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: select sleep(7)
     thread_id: 4

myql.slow_log是一張存儲引擎為CSV的表。在可以直接在文件中查看：

cat /opt/mydata/mydata57/mysql/slow_log.CSV

慢查詢日志分析

分析工具：pt-query-digest

相關文檔： https://blog.csdn.net/xiaoweite1/article/details/80299754

下載：https://www.percona.com/downloads/percona-toolkit/LATEST/

下載版本：Linux - Generic

#安裝
#下載并拷貝到服務器 /usr/local 目錄
cd /user/local
tar -zxvf percona-toolkit-3.3.0_x86_64.tar.gz
cd percona-toolkit-3.3.0
perl Makefile.PL PREFIX=/usr/local/percona-toolkit-3.3.0
make && make install

關于percona-toolkit：用于MySQL性能分析的各種工具集合

各個工具簡介： https://www.percona.com/doc/percona-toolkit/2.2/index.html

分析慢查詢日志

pt-query-digest slow.log > slow_report.log

#更多命令參數(shù)：
/usr/local/percona-toolkit-3.3.0/bin/pt-query-digest --help

#日志分析
cat /opt/slow_report.log 

#======匯總信息======
# 11.1s user time, 840ms system time, 38.71M rss, 190.07M vsz
# Current date: Thu Apr 15 17:30:27 2021
# Hostname: sptlpcmsitapp01
# Files: /opt/mydata/mydb57/slow.log-20210415
# Overall: 706 total, 12 unique, 0.00 QPS, 0.01x concurrency _____________
# Time range: 2021-04-08T07:04:13 to 2021-04-15T14:34:18
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          4595s      5s    221s      7s     10s      8s      5s
# Lock time           53ms       0    27ms    75us   194us     1ms       0
# Rows sent          4.35M       0 432.92k   6.30k   9.33k  41.60k       0
# Rows examine     390.06M       0 202.80M 565.75k   1.46M   7.64M       0
# Query size       647.15M      15 1014.89k 938.64k 1009.33k 265.94k 1009.33k

# Profile（最耗時的查詢語句列表，默認顯示TOP 10，可在生成報告時加入 --limit 20 來分析更多的查詢）
# Rank Query ID                            Response time   Calls R/Call   
# ==== =================================== =============== ===== ======== 
#    1 0x4A3DEA18106D921BB903EE0B41520F95  3699.4559 80.5%   653（執(zhí)行次數(shù)）   5.6653  0.02 INSERT lineitem
#    2 0xD4F86AA814C1813180C7B8C06D458D0B   388.3153  8.5%    27  14.3820  5.55 SELECT lineitem
#    3 0x63DC23F8BE55E50357191611FBCE2299   220.9463  4.8%     1 220.9463  0.00 SELECT orders
#    4 0x18C441EFD50890CAC27B0453AB318ABA    86.1554  1.9%     6  14.3592  0.00 SELECT orders
# MISC 0xMISC                               199.7724  4.3%    19  10.5143   0.0 <8 ITEMS>

#====================下面是按順序逐條顯示每條查詢的詳細數(shù)據(jù)==============
# Query 1: 0.15 QPS（頻率）, 0.87x concurrency, ID 0x4A3DEA18106D921BB903EE0B41520F95 at byte 146551092
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02  （離差指數(shù)：離差指數(shù)越高，對應的每條查詢執(zhí)行的時間差變化越大，越值得優(yōu)化）
# Time range: 2021-04-09T09:26:42 to 2021-04-09T10:37:44
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         92     653
# Exec time     80   3699s      5s      7s      6s      6s   362ms      5s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    99 647.14M 1014.69k 1014.89k 1014.81k 1009.33k    0.02 1009.33k
# String:
# Databases    dbt3
# Hosts        localhost
# Users        root
# Query_time distribution （一個直方圖，執(zhí)行時間分布圖）
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  #############
#    1s  ###################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `dbt3` LIKE 'lineitem'\G
#    SHOW CREATE TABLE `dbt3`.`lineitem`\G
INSERT INTO `lineitem` VALUES (2235107,104319,6830,2,15,19849.65,0.1,0.08,'N','O','1998-04-26','1998-05-06','1998-05-23','DELIVER IN PERSON','AIR','blithely final theodolites ha') /*... omitted ...*/\G

......

通用日志

記錄數(shù)據(jù)庫的所有操作

同樣可以將日志保存到表

開啟后性能下降明顯（超過50%）

mysql> show variables like '%general_log%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log      | OFF         |
| general_log_file | general.log |
+------------------+-------------+

應用場景：排查問題、審計、查看程序背后的SQL語句

存儲引擎介紹

#查看系統(tǒng)中支持的所有存儲引擎
show engines;

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

Notes:

\1. Implemented in the server, rather than in the storage engine.

\2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

\3. Implemented in the server via encryption functions.

\4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest encryption is supported.

\5. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

\6. Support for FULLTEXT indexes is available in MySQL 5.6 and later.

\7. Support for geospatial indexing is available in MySQL 5.7 and later.

\8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

\9. See the discussion later in this section.

修改表的存儲引擎：alter table tb_a engin=innodb;（修改的過程中會鎖表）

數(shù)據(jù)類型

數(shù)據(jù)類型優(yōu)化的原則：

更小的通常更高

簡單就好

盡量避免NULL值

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/data-types.html

整型

數(shù)字類型

字符串類型

討論：

VARCHAR最大長度設置成多少合適？

BLOB和TEXT

枚舉類型

create table enum_test(e enum('fish','apple','dog'));
insert into enum_test(e) values('fish'),('dog'),('apple'),('fish');

日期類型

相關函數(shù)：

select from_unixtime(start_time) from program;
select date_add(now(), interval 1 day);

JSON

官方文檔：

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

(root@localhost) [test]> create table jtest(uid int auto_increment primary key, data json);

(root@localhost) [test]> insert into jtest(data) values('{"name":"david","address":"shanghai"}');

(root@localhost) [test]> insert into jtest(data) values('{"name":"toby","address":"beijing"}');

(root@localhost) [test]> insert into jtest(data) values('{"name":"jim","address":"shenzhen","passport":"E06546198"}');

(root@localhost) [test]> select uid,json_extract(data,"$.name") from jtest;
+-----+-----------------------------+
| uid | json_extract(data,"$.name") |
+-----+-----------------------------+
|   1 | "david"                     |
|   2 | "toby"                      |
|   3 | "jim"                       |
+-----+-----------------------------+
3 rows in set (0.26 sec)

(root@localhost) [test]> select uid , json_unquote(json_extract(data,"$.name")) from jtest;
+-----+-------------------------------------------+
| uid | json_unquote(json_extract(data,"$.name")) |
+-----+-------------------------------------------+
|   1 | david                                     |
|   2 | toby                                      |
|   3 | jim                                       |
+-----+-------------------------------------------+
3 rows in set (0.00 sec)

(root@localhost) [test]> select uid,data->>"$.passport" from jtest;
+-----+---------------------+
| uid | data->>"$.passport" |
+-----+---------------------+
|   1 | NULL                |
|   2 | NULL                |
|   3 | E06546198           |
+-----+---------------------+
3 rows in set (0.00 sec)

分區(qū)表

• 將一個表或者索引分解為多個更小、更可管理的部分
• 目前只支持水平分區(qū)
• 局部分區(qū)索引：每個分區(qū)保存自己的數(shù)據(jù)和索引
• 分區(qū)列必須是唯一索引的一個組成部分

隨著版本的提升，大部分BUG已經(jīng)被修復，分區(qū)表的技術已經(jīng)趨于成熟

--RANGE
CREATE TABLE tb1(id int primary key) engine=INNODB
PARTITION BY RANGE(id)(
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20)
);

--HASH
CREATE TABLE tb2(a int,b datetime) engine=INNODB
PARTITION BY HASH(YEAR(b))
PARTITIONS 4;

--LIST
CREATE TABLE tb3(a int,b int) engine=INNODB
PARTITION BY LIST(b)(
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (0,2,4,6,8)
);

--KEY
CREATE TABLE tb4(a int, b datetime) engine=innodb
PARTITION BY KEY(b)
PARTITIONS 4;

事務

事務的四個要素：ACID

原子性（Atomicity）

一致性（Consistency）

隔離性（Isolation）

持久性（Durability）

隔離級別

Read Uncommitted（未提交讀）

Read Committed（提交讀）

Repeated Read（可重復讀）

Serialization（序列化）

并發(fā)操作時可能出現(xiàn)的問題

臟讀（Dirty Read）

不可重復讀

幻讀（Phantom Read）

多版本并發(fā)控制（MVCC）

MySQL大多數(shù)事務型存儲引擎實現(xiàn)的都不是簡單的行級鎖。基于提升并發(fā)性能的考慮，她們一般都同時實現(xiàn)了多版本并發(fā)控制（MVCC）。

可以認為MVCC是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現(xiàn)機制有所不同，但大都實現(xiàn)了非阻塞的讀操作，寫操作也只鎖定必要的行。

EXPLAIN

查看優(yōu)化后的語句

explain extended select * from film where id = 1;

show warnings;

(root@10.243.94.17) [ppcloud_live]> explain select * from program where ccid>100000000000\G
*************************** 1. row ***************************
           id: 1        --執(zhí)行的順序
  select_type: SIMPLE   --查詢的類型：SIMPLE/PRIMARY/SUBQUERY/DERIVED
        table: program  --表名，<derivedN> ，<union1,2>
   partitions: NULL     --分區(qū)信息
         type: range    --查詢方式：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys: ccid     --顯示查詢可能使用哪些索引來查找
          key: ccid     --優(yōu)化后實際使用哪個索引
      key_len: 8        --在索引里使用的字節(jié)數(shù)
          ref: NULL     --key列記錄的索引中，表查找值所用到的列或常量：const（常量），func，NULL，字段名
         rows: 1        --預估要讀取并檢索的行數(shù)
     filtered: 100.00   --返回結果的行數(shù)占需讀取行數(shù)的百分比
        Extra: Using index condition  --額外信息：Using index/Using where/Using temporary/Using filesort/Impossible where

鎖

鎖的基本模式

共享鎖：其它事務可以繼續(xù)加共享鎖，但是不能繼續(xù)加排他鎖

排他鎖：一旦加了排他鎖，其它事務就不能加鎖了

讀寫鎖

讀鎖：共享鎖，SELECT時會自動加讀鎖，不會阻塞其它進程對同一數(shù)據(jù)的讀請求，但會阻塞對同一數(shù)據(jù)的寫請求。

寫鎖：排他鎖，更新的時候會自動加寫鎖，會阻塞其它進程對同一數(shù)據(jù)的讀和寫操作。

鎖粒度

表鎖（table lock）

行級鎖（row lock）

InnoDB鎖實現(xiàn)方式

記錄鎖（Record Lock）

間隙所（Gap Lock）

下一鍵鎖（Next Key Lock）

插入意向鎖（Insert Intention Lock）

死鎖

死鎖產(chǎn)生的原因？

如何解決死鎖？

鎖類型

查詢性能剖析

性能的度量

響應時間、吞吐量

剖析服務器負載

捕獲并分析慢查詢日志

剖析單條查詢

--1、使用SHOW PROFILE
set profiling=1;
select * from employees;
show tables;
select * from titles;
show profiles;
show profile for query 3;

--從表里查詢
set @query_id=1;
select state, sum(duration) as Total_R, 
              round(100*sum(duration)/(select sum(duration) from profiling where query_id=@query_id ),2) as Pct_R, 
              count(*) as Calls, 
              sum(duration)/count(*) as 'R/Call' 
from profiling  
where query_id=@query_id 
group by state
 order by Total_R desc;

--2、 使用SHOW STATUS
flush status;
query....
show status where variable_name like 'Handler%' OR variable_name like 'Created%';

索引的類型

索引大大減少了服務器需要掃描的數(shù)據(jù)量

所以可以幫助服務器避免排序和臨時表

所以可以將隨機I/O變?yōu)轫樞騃/O

數(shù)據(jù)結構模擬：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B-樹索引：B+樹、聚簇索引、非聚簇索引

哈希索引

全文索引

InnoDB/MyISAM 不支持哈希索引：自定義哈希索引

InnoDB的自適應哈希索引

索引是最好的解決方案嗎？

高性能索引策略

索引不能是表達式的一部分

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

索引選擇性

索引的基數(shù)cardinality

索引選擇性selectivity

前綴索引

索引并不總是生效

select * from program where ccid>194020005;

select * from program where ccid<194020005;

聯(lián)合索引

很多人對多列索引的理解都不夠。常見的錯誤就是，為每個列創(chuàng)建獨立的索引，或者按照錯誤的順序創(chuàng)建多列索引。在多個列上建立獨立的單列索引，大部分情況下并不能優(yōu)化MySQL的查詢性能

索引合并

在MySQL 5.0以及以后版本中，查詢能夠同時使用兩個索引進行掃描，并將二者的結果合并。這種算法有三個變種：OR條件的聯(lián)合（union取合集）、AND條件的相交（intersection取交集）、組合前兩種。

索引合并策略是一種優(yōu)化的結果，但實際上更說明了表上的索引建的很糟糕

選擇合適的索引順序

創(chuàng)建索引時，要充分的考慮列的順序，以更好的滿足排序和分組的需要。

索引匹配的原則：

全值匹配

最左前綴匹配

查詢條件用到了索引中列的精確匹配，但是中間某個條件未提供

匹配某列的前綴字符串

范圍查詢

查詢條件中含有函數(shù)或表達式

聚簇索引

數(shù)據(jù)訪問更快，直接拿到數(shù)據(jù)，減少一次磁盤IO

使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點中的主鍵值

缺點

二級索引（非聚簇索引）訪問數(shù)據(jù)需要兩次索引查找

插入速度嚴重依賴于插入順序。

按照主鍵順序插入是最快的方式，無序插入代價很高，經(jīng)常無需插入最好定期用 optimize table 命令重新組織一下表。

頁分裂（page split）問題

覆蓋索引

索引的葉子節(jié)點中已經(jīng)包含要查詢的數(shù)據(jù)，無需再回表查詢

覆蓋索引可以極大的提高性能

使用索引掃描來排序

關于filesort：MySQL有兩種filesort算法：two-pass和single-pass

冗余和重復索引

MySQL允許在相同列上創(chuàng)建多個索引，無論是有意的還是無意的。MySQL需要單獨維護重復的索引，并且優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進行考慮，這會影響性能。

索引和鎖

索引可以讓查詢鎖定更少的行

查詢性能優(yōu)化

優(yōu)化數(shù)據(jù)訪問

是否向服務器請求了不需要的數(shù)據(jù)

MySQL是否在掃描額外的記錄

重構查詢

一個復雜的查詢還是多個簡單的查詢？

切分查詢

分解關聯(lián)查詢

優(yōu)化 COUNT() 查詢

優(yōu)化關聯(lián)查詢

優(yōu)化子查詢

優(yōu)化GROUP BY和DISTINCT

LIMIT 優(yōu)化