MySQL

官方文档

https://www.mysql.com/

官方的帮助文档非常详细，有任何问题都可以去搜索官方文档。

对一条命令不太清楚的话，还可以直接敲help命令：

help create table;
help select;

安装

下载MySQL社区版本：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

#创建数据存放目录，并赋予权限
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

# 数据文件初始化，这一步执行完/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

优先级从低到高

/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/

#初始化数据目录
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';

系统自带的数据库

库名	说明
information_schema	信息架构表，元数据表，记录了所有元数据信息：数据库名、表名、列的数据类型、访问权限等
performance_schema	用于性能分析，查看各种各样的性能数据
mysql	记录了用户登录的一些信息
sys	MYSQL5.7新加的数据库，包含一些视图，方便查询各种元数据信息

系统变量和会话变量

--系统变量:修改全局变量，只有新创建的连接才会生效
show global vairables like '%lang_query_time%';
set global lang_query_time=10;

--会话变量：只在当前会话生效
show variables like '%lang_query_time%';
set lang_query_time=10;

权限管理

权限的粒度：库权限、表权限、列权限

--创建用户
create user 'david'@'%' identified by '123456';
create user 'david'@'192.168.1.%' identified by '123456'; --在192.168.1.*这个网段内可以访问

--删除用户
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;

表名	说明
user	用户表，可以查看全局权限信息
db	库权限
tables_priv	表权限
columns_priv	列权限

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

Privilege	Grant Table Column	Context
ALL [PRIVILEGES\]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

慢查询日志

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

慢查询日志相关参数

变量名	说明
slow_query_log	是否开启慢查询日志
slow_query_log_file	慢查询日志文件名
long_query_time	指定慢查询阈值，默认10秒
min_examined_row_limit	扫描记录少于该阈值的SQL不记录到慢查询日志 ：当这条慢查询日志，执行次数超过阈值的时候才会被记录到慢查询日志，默认为0
log_queries_not_using_indexes	将没有使用所有的SQL记录到慢查询日志：默认是关闭的
log_throttle_queries_not_using_indexes	限制每分钟记录没有使用索引SQL语句次数：防止日志刷的过快
log_slow_admin_statement	记录管理操作，如ALTER/ANALYZE TABLE
log_output	慢查询日志输出位置，{FILE|TABLE|NONE} ： FILE输出到文件 TABLE输出到表mysql.slow_log 默认值：FILE。不建议记录到表： 性能开销更大 数据库备份时如果不刻意清理，则备份数据很大 好处：查询方便
log_slow_slave_statements	从服务器上开启慢查询日志
log_timestamps	写入时区 ：5.7以后新增的参数，默认值为:UTC （日志文件记录的时间差了8小时） 强烈建议改成：system

清理慢查询日志

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

2、mysql客户端执行 ：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

#更多命令参数：
/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（执行次数）   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>

#====================下面是按顺序逐条显示每条查询的详细数据==============
# 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  （离差指数：离差指数越高，对应的每条查询执行的时间差变化越大，越值得优化）
# 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 （一个直方图，执行时间分布图）
#   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

......

通用日志

记录数据库的所有操作

同样可以将日志保存到表

开启后性能下降明显（超过50%）

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

应用场景：排查问题、审计、查看程序背后的SQL语句

存储引擎介绍

#查看系统中支持的所有存储引擎
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;（修改的过程中会锁表）

数据类型

数据类型优化的原则：

更小的通常更高

简单就好

尽量避免NULL值

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

整型

类型	占用空间（字节）	最小值（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

数字类型

类型	占用空间	精度	精确性
float	4	单精度	低
double	8	双精度	低，比float高
decimal	变长	高精度	非常高

字符串类型

类型	说明	N的含义	是否有字符集	最大长度
CHAR(N)	定长字符	字符	是	255
VARCHAR(N)	变长字符	字符	是	16384
BINARY(N)	变长二进制字节	字节	否	255
VARBINARY(N)	变长二进制字节	字节	否	16384
TINYBLOB	二进制大对象	字节	否	256
BLOB	二进制大对象	字节	否	16K
MEDIUMBLOB	二进制大对象	字节	否	16M
LONGBLOB	二进制大对象	字节	否	4G
TINYTEXT	大对象	字节	是	256
TEXT	大对象	字节	是	16K
MEDIUMTEXT	大对象	字节	是	16M
LONGTEXT	大对象	字节	是	4G

讨论：

VARCHAR最大长度设置成多少合适？

BLOB和TEXT

枚举类型

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

日期类型

类型	占用字节	表示范围
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

相关函数：

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)

分区表

• 将一个表或者索引分解为多个更小、更可管理的部分
• 目前只支持水平分区
• 局部分区索引：每个分区保存自己的数据和索引
• 分区列必须是唯一索引的一个组成部分

随着版本的提升，大部分BUG已经被修复，分区表的技术已经趋于成熟

--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（序列化）

并发操作时可能出现的问题

脏读（Dirty Read）

不可重复读

幻读（Phantom Read）

多版本并发控制（MVCC）

MySQL大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，她们一般都同时实现了多版本并发控制（MVCC）。

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

EXPLAIN

查看优化后的语句

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        --执行的顺序
  select_type: SIMPLE   --查询的类型：SIMPLE/PRIMARY/SUBQUERY/DERIVED
        table: program  --表名，<derivedN> ，<union1,2>
   partitions: NULL     --分区信息
         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     --优化后实际使用哪个索引
      key_len: 8        --在索引里使用的字节数
          ref: NULL     --key列记录的索引中，表查找值所用到的列或常量：const（常量），func，NULL，字段名
         rows: 1        --预估要读取并检索的行数
     filtered: 100.00   --返回结果的行数占需读取行数的百分比
        Extra: Using index condition  --额外信息：Using index/Using where/Using temporary/Using filesort/Impossible where

锁

锁的基本模式

共享锁：其它事务可以继续加共享锁，但是不能继续加排他锁

排他锁：一旦加了排他锁，其它事务就不能加锁了

读写锁

读锁：共享锁，SELECT时会自动加读锁，不会阻塞其它进程对同一数据的读请求，但会阻塞对同一数据的写请求。

写锁：排他锁，更新的时候会自动加写锁，会阻塞其它进程对同一数据的读和写操作。

锁粒度

表锁（table lock）

行级锁（row lock）

InnoDB锁实现方式

记录锁（Record Lock）

间隙所（Gap Lock）

下一键锁（Next Key Lock）

插入意向锁（Insert Intention Lock）

死锁

死锁产生的原因？

如何解决死锁？

锁类型

查询性能剖析

性能的度量

响应时间、吞吐量

剖析服务器负载

捕获并分析慢查询日志

剖析单条查询

--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%';

索引的类型

索引大大减少了服务器需要扫描的数据量

所以可以帮助服务器避免排序和临时表

所以可以将随机I/O变为顺序I/O

数据结构模拟：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;

索引选择性

索引的基数cardinality

索引选择性selectivity

前缀索引

索引并不总是生效

select * from program where ccid>194020005;

select * from program where ccid<194020005;

联合索引

很多人对多列索引的理解都不够。常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。在多个列上建立独立的单列索引，大部分情况下并不能优化MySQL的查询性能

索引合并

在MySQL 5.0以及以后版本中，查询能够同时使用两个索引进行扫描，并将二者的结果合并。这种算法有三个变种：OR条件的联合（union取合集）、AND条件的相交（intersection取交集）、组合前两种。

索引合并策略是一种优化的结果，但实际上更说明了表上的索引建的很糟糕

选择合适的索引顺序

创建索引时，要充分的考虑列的顺序，以更好的满足排序和分组的需要。

索引匹配的原则：

全值匹配

最左前缀匹配

查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

匹配某列的前缀字符串

范围查询

查询条件中含有函数或表达式

聚簇索引

数据访问更快，直接拿到数据，减少一次磁盘IO

使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

二级索引（非聚簇索引）访问数据需要两次索引查找

插入速度严重依赖于插入顺序。

按照主键顺序插入是最快的方式，无序插入代价很高，经常无需插入最好定期用 optimize table 命令重新组织一下表。

页分裂（page split）问题

覆盖索引

索引的叶子节点中已经包含要查询的数据，无需再回表查询

覆盖索引可以极大的提高性能

使用索引扫描来排序

关于filesort：MySQL有两种filesort算法：two-pass和single-pass

冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

索引和锁

索引可以让查询锁定更少的行

查询性能优化

优化数据访问

是否向服务器请求了不需要的数据

MySQL是否在扫描额外的记录

重构查询

一个复杂的查询还是多个简单的查询？

切分查询

分解关联查询

优化 COUNT() 查询

优化关联查询

优化子查询

优化GROUP BY和DISTINCT

LIMIT 优化