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ù)存放目錄推捐，并賦予權(quán)限
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目錄下就應該有內(nèi)容了
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';

關(guān)于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;

權(quán)限管理

權(quán)限的粒度：庫權(quán)限嘹狞、表權(quán)限、列權(quán)限

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

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

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

--授權(quán)
grant select,update,insert,delete on employees.* to 'david'@'%';

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

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

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

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

權(quán)限信息相關(guān)表：

use mysql;

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

慢查詢?nèi)罩?/h2>

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

慢查詢?nèi)罩鞠嚓P(guān)參數(shù)

變量名	說明
slow_query_log	是否開啟慢查詢?nèi)罩?/td>
slow_query_log_file	慢查詢?nèi)罩疚募?/td>
long_query_time	指定慢查詢閾值磅网，默認10秒
min_examined_row_limit	掃描記錄少于該閾值的SQL不記錄到慢查詢?nèi)罩?：當這條慢查詢?nèi)罩荆瑘?zhí)行次數(shù)超過閾值的時候才會被記錄到慢查詢?nèi)罩究曷牛J為0
log_queries_not_using_indexes	將沒有使用所有的SQL記錄到慢查詢?nèi)罩荆耗J是關(guān)閉的
log_throttle_queries_not_using_indexes	限制每分鐘記錄沒有使用索引SQL語句次數(shù)：防止日志刷的過快
log_slow_admin_statement	記錄管理操作涧偷，如ALTER/ANALYZE TABLE
log_output	慢查詢?nèi)罩据敵鑫恢敏の梗瑊FILE|TABLE|NONE} ： FILE輸出到文件 TABLE輸出到表mysql.slow_log 默認值：FILE。不建議記錄到表： 性能開銷更大 數(shù)據(jù)庫備份時如果不刻意清理燎潮，則備份數(shù)據(jù)很大 好處：查詢方便
log_slow_slave_statements	從服務器上開啟慢查詢?nèi)罩?/td>
log_timestamps	寫入時區(qū) ：5.7以后新增的參數(shù)喻鳄，默認值為:UTC （日志文件記錄的時間差了8小時） 強烈建議改成：system

清理慢查詢?nèi)罩?/strong>

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

2确封、mysql客戶端執(zhí)行 ：flush slow logs;（關(guān)閉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

慢查詢?nèi)罩痉治?/strong>

分析工具：pt-query-digest

相關(guān)文檔： 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

關(guān)于percona-toolkit：用于MySQL性能分析的各種工具集合

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

分析慢查詢?nèi)罩?/strong>

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

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery (note 1)	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	Yes (note 2)	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes (note 3)	Yes (note 3)	Yes (note 4)	Yes (note 3)	Yes (note 3)
Foreign key support	No	No	Yes	No	Yes (note 5)
Full-text search indexes	Yes	No	Yes (note 6)	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes (note 7)	No	No
Hash indexes	No	Yes	No (note 8)	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support (note 1)	Yes	Limited (note 9)	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

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

整型

類型	占用空間（字節(jié)）	最小值（Signed / Unsigned）	最大值（Signed / Unsigned）
tinyint	1	-128 / 0	127 / 255
smallint	2	-32768 / 0	32767 / 65535
mediumint	3	-8388608 / 0	8388607 / 16777215
int	4	-2147483648 / 0	2147483647 / 4294967295
bigint	8	-9223372036854775808 / 0	9223372036854775807 / 18446744073709551615

數(shù)字類型

類型	占用空間	精度	精確性
float	4	單精度	低
double	8	雙精度	低诡曙，比float高
decimal	變長	高精度	非常高

字符串類型

類型	說明	N的含義	是否有字符集	最大長度
CHAR(N)	定長字符	字符	是	255
VARCHAR(N)	變長字符	字符	是	16384
BINARY(N)	變長二進制字節(jié)	字節(jié)	否	255
VARBINARY(N)	變長二進制字節(jié)	字節(jié)	否	16384
TINYBLOB	二進制大對象	字節(jié)	否	256
BLOB	二進制大對象	字節(jié)	否	16K
MEDIUMBLOB	二進制大對象	字節(jié)	否	16M
LONGBLOB	二進制大對象	字節(jié)	否	4G
TINYTEXT	大對象	字節(jié)	是	256
TEXT	大對象	字節(jié)	是	16K
MEDIUMTEXT	大對象	字節(jié)	是	16M
LONGTEXT	大對象	字節(jié)	是	4G

討論：

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

BLOB和TEXT

枚舉類型

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

日期類型

類型	占用字節(jié)	表示范圍
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	1000-01-01 ~ 9999-12-31
TIMESTAMP	4	1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC
YEAR	1
TIME

相關(guān)函數(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ū)表的技術(shù)已經(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   --返回結(jié)果的行數(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)生的原因套腹？

如何解決死鎖殖蚕？

鎖類型

查詢性能剖析

性能的度量

響應時間、吞吐量

剖析服務器負載

捕獲并分析慢查詢?nèi)罩?/p>

剖析單條查詢

--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ù)結(jié)構(gòu)模擬：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以及以后版本中咕娄，查詢能夠同時使用兩個索引進行掃描，并將二者的結(jié)果合并珊擂。這種算法有三個變種：OR條件的聯(lián)合（union取合集）圣勒、AND條件的相交（intersection取交集）、組合前兩種摧扇。

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

選擇合適的索引順序

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

索引匹配的原則：

全值匹配

最左前綴匹配

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

匹配某列的前綴字符串

范圍查詢

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

聚簇索引

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

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

缺點

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

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

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

頁分裂（page split）問題

覆蓋索引

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

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

使用索引掃描來排序

關(guān)于filesort：MySQL有兩種filesort算法：two-pass和single-pass

冗余和重復索引

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

索引和鎖

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

查詢性能優(yōu)化

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

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

MySQL是否在掃描額外的記錄

重構(gòu)查詢

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

切分查詢

分解關(guān)聯(lián)查詢

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

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

優(yōu)化子查詢

優(yōu)化GROUP BY和DISTINCT

LIMIT 優(yōu)化