Mysql高级

https://www.bilibili.com/video/BV1KW411u7vy

1. mysql的架构介绍

Mysql安装（linux环境）

• cd /opt
• rpm -qa|grep -i mysql ：查询是否安装过mysql（qa:query-all）
• rpm -e RPM软件包名 删除软件包
• rpm -ivh Mysql-server-5.xxxxx.rpm(install–安装，v–verbose日志, h–hash 进度）安装服务器端
• rpm -ivh Mysql-client-5.xxxxx.rpm 安装客户端
• service mysql start 启动服务 ； stop 结束服务
• centos7 mysql数据库安装和配置
• 修改密码：
  • /usr/bin/mysqladmin -u root password 123456
• chkconfig mysql on 设置开机自启动
  • chkconfig --list | grep mysql 查看linux各运行级别下mysql是否启用自启动
  • cat /ect/initab 查看运行级别
  • ntsysv 打开窗口：“您想自动启动哪些服务？”
• MySQL的安装位置
  • ps -ef |grep mysql 显示信息里 --datadir=/var/lib/mysql 是数据库文件的存放路径
  • /usr/share/mysql 配置文件目录
  • /usr/bin 相关命令（如mysqladmin)目录
  • /etc/init.d/mysql启停相关脚本

配置文件

修改字符集配置

• 修改配置文件 /usr/share/mysql/my-huge.cnf (5.5，5.6是my-default.cnf）
  • cp my-huge.cnf /etc/mysql5.5/my.cnf（错误，一定要放在/etc下，放在/etc/mysql5.5下配置失败）。
• 修改字符集编码
  • mysql>show variables like ‘%char%’; 查看字符集（database和server默认是latin1，通常中文会乱码）
  • vim my.cof
    • 新增了三处地方，再查
  • 重启服务 systemctl restart mariadb.service ，新建database生效。

主要配置文件

• 二进制日志log-bin
• 错误日志log-error：默认是关闭的，记录严重的警告和错误信息
• 查询日志log：默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能
• 数据文件
  • Windows：D:\devSoft\MySQLServer5.5\data目录下可以挑选很多库
  • Linux：查看当前系统中的全部库后再进去（默认路径：/var/lib/mysql）
  • frm文件：存放表结构
  • myd文件：存放表数据
  • myi文件：存放表索引

逻辑架构介绍

|-- Connectors:Native C API,JDBC,ODBC,.NET,PHP…
|-- MySQL Server
  |–Management Service & Utilities
  |-- Conection Pool:Authentication,Connection Limits…
  |–SQL interface
  |–Parser
  |–Optimizer优化器
  |–Caches & Buffers
  |-- Pluggable Storage Engines 引擎 MyISAM,InnoDB,NDB,Archive…
  |–File system
  |–Files & Logs

1. 连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在此层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户都拿验证它所具有的操作权限。
2. 服务层第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在这层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
3. 引擎层：存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的 实际需要进行选取。
4. 存储层

存储引擎

1. 查看存储引擎: mysql>show engines;
2. 查看默认存储引擎：mysql>show variables like ‘%storage_engine%’;

MariaDB [db02]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys       | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)                   | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| ARCHIVE            | YES     | gzip-compresses tables for a low storage footprint                               | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| FEDERATED          | YES     | Allows to access tables on other MariaDB servers, supports transactions and more | YES          | NO   | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

2. 索引优化分析

前言

性能下降SQL慢

查询语句写的不好
索引失效
单值索引

create index idx_user_name on user(name);

多值索引

create index idx_user_nameEmail on user(name，email);

SQL解析顺序：

FROM ——笛卡尔积
ON——主表保留
JOIN——不符合ON的也添加
WHERE——非聚合——非SELECT别名
GROUP BY——改变对表引用
HAVING——只作用分组后
SELECT——DISTINCT
ORDER BY——可使用SELECT别名
LIMIT——rows——offset

常见通用的join查询

七种join

# 1.内连接
SELECT <select_list> FROM TableA A INNER JOIN TableB B 
ON A.key=B.key; #A和B的共有部分
# 2.左连接
SELECT <select_list> FROM TableA A LEFT JOIN TableB B 
ON A.key=B.key; #A有的部分（B可以有，可以没有）
# 3.右连接
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B 
ON A.key=B.key; #B有的部分（A可以有，可以没有）
# 4.
SELECT <select_list> FROM TableA A LEFT JOIN TableB B 
ON A.key=B.key WHERE B.key IS NULL; #A有的部分（B没有的那部分）
# 5.
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B 
ON A.key=B.key WHERE A.key IS NULL; #B有的部分（A没有的那部分）
# 6.全连接
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B 
ON A.key=B.key; # A和B的合集
# 7.
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B 
ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL; # A和B的合集减去A和B的交集。

1+4 = 2
1+5 = 3
1+7 = 6
7 = 4+5
2 + 3 = 1+4+1+5 = 2+7 = 1+6 # A+B = A并B+A交B
6 = 2 union 3 # union自动去重

索引简介

什么是索引

索引是帮助MySQL高校获取数据的数据结构。
一般来说，索引本身也很大，往往以索引文件的形式存储在磁盘上。

优势：

• 提高数据检索的效率，降低数据库的IO成本。
• 降低数据排序的成本，降低了CPU的消耗。

劣势：

• 实际上索引也是一张表，也是要占空间的
• 虽然索引大大提高了查询速度，但会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE。因为更新表时，不仅要保存数据，还要更新索引信息。
• 索引只是提高效率的一个因素，如果MySQL中有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一
• 复合索引：一个索引包含多个列

基本语法

# 创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD[UNIQUE] INDEX [indexName] ON columnname(length);

# 删除
DROP INDEX [indexName] ON mytable
# 查看
SHOW INDEX FROM table_name\G #\表示竖版显示

MySQL索引结构：

• BTree索引
• Hash索引
• full-text全文索引
• R-Tree索引

哪些情况适合建立索引：

• 主键
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键建立索引
• 查询中排序的字段
• 查询中统计或者分组字段

哪些情况不适合建立索引：

• 频繁更新的字段
• where条件里用不到的字段
• 表记录太少
• 数据重复且分布平均，应该只为最经常查询和最经常排序的数据列建立索引

性能分析

MySQL Query Optimizer

1. MYSQL 中有专门负责优化SELECT语句的优化器模块。主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的query提供MySQL认为最优的 执行计划（这部分比较耗时间）
2. 当客户端向MySQL请求一条query，命令解析器模块完成请求分类，区别是SELECT并转发给MySQL Query Optimizer，MySQL Query Optimizer首先会对整条query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析query中的Hint信息（如果有），看显示Hint信息是否可以确定该query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据query进行写相应的计算分析，最后再得出执行计划。

Mysql的常见瓶颈：

• CPU：CPU饱和一般发生在数据装入内存或从磁盘上读取数据时
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free，iostat和vmstat查看系统的性能状态

Explain

• 使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理该SQL语句的。分析查询语句或表结构的性能瓶颈。
• 能干嘛：
  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询
• 执行计划包含的信息
  • id：select查询的序列号，包含一组数字目标是查询中执行select子句或操作表的顺序
  • 三种情况：
    • id相同，执行顺序由上而下。
    • id不同，id值越大优先级越高，越先被执行
    • 混合型：id有的相同，有的不同，先执行id大的，id相同的自上而下。
  • select_type：主要是区别普通查询、联合查询、子查询等的复杂查询
    • SIMPLE：简单的select查询，不包含子查询或UNION
    • PRIMARY：包含子查询的最外层查询
    • SUBQUERY：在select或where列表中包含； 子查询
    • DERIVED：派生，在from列表中包含的子查询被标记为派生，mysql会递归的执行这些子查询，把结果放在临时表里
    • UNION：若第二个select出现在union之后，则会被标记为union；若union包含在from子句的子查询中，外层select会被标记为derived。
    • UNION RESULT
  • table：显示这一行的数据是关于哪张表的
  • type：访问类型排列；
    • system>const>eq-ref>ref>range>index>ALL（逐渐变差)；一般查询达到range级别，最好能达到ref
    • ALL：全表扫描
    • index
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在where语句中出现了between、<、>、in等的查询。这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
    • eq-ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
    • const：表锁通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
    • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，也可以忽略不计。
    • null
  • possible_keys ：显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
  • key ：查询中实际使用的索引。如果为NULL，则没有使用索引。若使用了覆盖索引，则该索引仅出现在key列表中
  • key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索得出的。
  • ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
  • rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
  • Extra ：包含不适合在其它列中显示，但非常重要的信息。
    • Using filesort：说明MySQL会对数据使用一个外的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作成为”文件排序“
    • Using temporary ：使用了临时表保存中间结果，MySQL对查询结果排序时使用临时表。常见于 order by和group by。
    • Using index：表示相应的select操作使用 了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表示索引被用来执行索引键值的查找；如果没有出现using where，表示索引用来读取数据而非执行查找动作。
      • 覆盖索引：select的数据列只用从索引列中就能获得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。（不用回表）
    • using where：使用了where过滤
    • using join buffer：使用了连接缓存
    • impossible where：where的子句总是false，不能用来获取任何元组
    • select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    • distinct：优化distinct操作，在找到第一匹配的元组后停止查找同样值的动作。

MariaDB [db01]> explain select * from user join student;
+------+-------------+---------+------+---------------+------+---------+------+------+------------------------------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+------+-------------+---------+------+---------------+------+---------+------+------+------------------------------------+
|    1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                    |
|    1 | SIMPLE      | user    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using join buffer (flat, BNL join) |
+------+-------------+---------+------+---------------+------+---------+------+------+------------------------------------+

案例分析

案例1：单表

新建表:

MariaDB [db01]> CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views  INT(10) UNSIGNED NOT NULL, comments  INT(10) UNSIGNED NOT NULL, title VARBINARY (255) NOT NULL, content TEXT NOT NULL ); 
Query OK, 0 rows affected (0.05 sec)

MariaDB [db01]> INSERT INTO article (author_id,category_id,views,comments,title,content) values (1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(3,3,3,3,'3','3');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [db01]> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         3 |           3 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+

# 查询category_id为1且comments大于1的情况下，views最多的article_id
MariaDB [db01]> explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+------+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+------+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

# 查看当前索引（除了主键，没有建索引
MariaDB [db01]> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

新建（三个字段）索引

MariaDB [db01]> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [db01]> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
MariaDB [db01]> explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+------+-------------+---------+------+-----------------+-----------------+---------+-------+------+------------------------------------------+
| id   | select_type | table   | type | possible_keys   | key             | key_len | ref   | rows | Extra                                    |
+------+-------------+---------+------+-----------------+-----------------+---------+-------+------+------------------------------------------+
|    1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 4       | const |    1 | Using where; Using index; Using filesort |
+------+-------------+---------+------+-----------------+-----------------+---------+-------+------+------------------------------------------+
1 row in set (0.00 sec)

虽然用到了索引，但还有 Using filesort（影响效率）
于是改变其中一个条件：>1 变成=1，则没有 Using filesort了。

MariaDB [db01]> explain select id from article where category_id=1 and comments=1 order by views desc limit 1;
+------+-------------+---------+------+-----------------+-----------------+---------+-------------+------+--------------------------+
| id   | select_type | table   | type | possible_keys   | key             | key_len | ref         | rows | Extra                    |
+------+-------------+---------+------+-----------------+-----------------+---------+-------------+------+--------------------------+
|    1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 | Using where; Using index |
+------+-------------+---------+------+-----------------+-----------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

删除上一个索引，新建索引（调整索引列顺序）

MariaDB [db01]> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db01]> create index idx_article_vcc on article(views,comments,category_id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db01]> explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+------+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id   | select_type | table   | type  | possible_keys | key             | key_len | ref  | rows | Extra                    |
+------+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | article | index | NULL          | idx_article_vcc | 12      | NULL |    1 | Using where; Using index |
+------+-------------+---------+-------+---------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

若只用两个字段建索引：（ref比上面index更优）

MariaDB [db01]> create index idx_article_cv on article(category_id,views);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db01]> explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+------+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id   | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+------+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
|    1 | SIMPLE      | article | ref  | idx_article_cv | idx_article_cv | 4       | const |    1 | Using where |
+------+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

案例2：两表

新建表：

MariaDB [db01]> CREATE TABLE IF NOT EXISTS class(
    -> id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> card INT(10) UNSIGNED NOT NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [db01]> CREATE TABLE IF NOT EXISTS book(
    -> bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> card INT(10)UNSIGNED NOT NULL ,
    -> PRIMARY KEY(bookid)
    -> );
Query OK, 0 rows affected (0.05 sec)

插入数据（随机函数RAND）*20：

MariaDB [db01]> INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.11 sec)

MariaDB [db01]> INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.00 sec)

尝试查找：

MariaDB [db01]> explain select * from book inner join class on book.card = class.card;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 |                                                 |
|    1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

结论：
左连接 右表加索引更好。
右连接 左表建索引

案例3：三表

在案例2的基础上，再建一张表：

MariaDB [db01]> CREATE TABLE IF NOT EXISTS phone( 
    -> phone_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> card INT(10) UNSIGNED NOT NULL,
    -> PRIMARY KEY(phone_id)
    -> )ENGINE = INNODB;
Query OK, 0 rows affected (0.08 sec)

MariaDB [db01]> INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.00 sec)

三表连接查询（不建索引）：

MariaDB [db01]> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card=phone.card;
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|    1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 |                                                        |
|    1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (incremental, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [db01]> explain SELECT * FROM class INNER JOIN book ON class.card = book.card INNER JOIN phone ON book.card=phone.card;
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|    1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                        |
|    1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (incremental, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
3 rows in set (0.00 sec)

对后两表建立索引：

MariaDB [db01]> ALTER TABLE phone  ADD INDEX idx_p_c (card);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db01]> ALTER TABLE book  ADD INDEX idx_b_c (card);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db01]> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card=phone.card;
+------+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref             | rows | Extra                    |
+------+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
|    1 | SIMPLE      | class | ALL  | NULL          | NULL    | NULL    | NULL            |   20 |                          |
|    1 | SIMPLE      | book  | ref  | idx_b_c       | idx_b_c | 4       | db01.class.card |    1 | Using index              |
|    1 | SIMPLE      | phone | ref  | idx_p_c       | idx_p_c | 4       | db01.book.card  |    1 | Using where; Using index |
+------+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
3 rows in set (0.11 sec)

结论：

• 后2行的type都是ref且总rows优化很好，效果不错。
• 尽可能减少join语句中NestedLoop的循环总次数，用小结果集驱动大结果集。
• 当无法保证被驱动表的join条件字段被索引且内存资源充足的情况下，不要太吝惜join buffer的设置。

索引优化

索引失效的情况

1. 全值匹配我最爱：select的项正好全是索引中的列，可以完美匹配。
2. 最佳左前缀法则
3. 不在索引列上做任何操作（计算、函数、（自动/手动）类型转换），会导致索引失效
4. 存储引擎不能使用索引中范围条件右边的列：譬如 where age >25 and pos = ‘maneger’;即使建了age+pos的索引，也只能用到age的部分。
5. 尽量使用覆盖索引，减少select *
6. MySQL在不适用不等于（！= 或<>）的时候无法使用索引会导致全表扫描
7. is null，is not null无法使用索引
8. like以通配符开头（’%abc…’）会导致索引失效。
9. 字符串不加单引号索引失效：会隐式的类型转换，把int转换为varchar
10. 少用or，用它来连接时会导致索引失效

如何解决通配符（’%abc%’）：使用覆盖索引
index (name,age)
select name … where name like ‘%aa%’;
select age… where name like ‘%aa%’;
select name,age … where name like ‘%aa%’;

question：
假设index(a,b,c)，索引是否被使用

• where a = 3 : 是。
• where a = 3 and b=5 : 是
• where a = 3 and b=5 and c=4：是
• where b = 3 或者 where b=3 and c = 4或者 where c = 4 ：否
• where a = 3 and c = 5 ：是，只用到了a那段
• where a = 3 and b> 4 and c=5 ：是，用到了a、b（range)那段
• where a = 3 and b like ‘kk%’ and c = 4 ：是，用到了a、b那段

面试题

index(a,b,c,d)
select *

• where d=d1 and c=c1 and b=b1 and a=a1; 实际可以用到索引，因为mysql进行了优化。
• where d>d1 and c=c1 and b=b1 and a=a1; 用到了4个，最后一个是range
• where d=d1 and b=b1 and a=a1 order by c3; 用到了4个（错，只用到了2个，第3个排序，需要全部数据，相当于断了）
• where b=b1 and a=a1 order by c; 用到了2个
• where b=b1 and a=a1 order by d; 用到了2个，还会Using filesort
• where a=a1 and e=e1 order by b,c; 用到了3个应该是1个，后面两个用于排序，记住，排序需要全部数据，查找=是const数据，查找范围是range
• where a=a1 and e=e1 order by c,b; 用到了一个，还有filesort。
• where a=a1 and b=b1 order by b,c; 用到了两个
• where a=a1 and b=b1 and e =e1 order by b,c ：用到了两个
• where a=a1 and b=b1 and e =e1 order by c,b ：用到了两个
• where a=a1 and d=d1 order by b,c ：用到了两个错，只用到了1个
• where a=a1 and d=d1 order by c,b ：用到了1个，除了Using filesort，还有where和temporary
• where a = 3 and b like’kk%’ and c = 4 ; 用到了两个错，用到了3个
• where a = 3 and b like’%kk’ and c = 4; 用到了一个
• where a = 3 and b like’%kk%’ and c = 4；用到了一个
• where a = 3 and b like’k%kk%’ and c = 4；用到了三个

总结

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like 百分写最右，覆盖索引不写星
不等空值还有or，索引失效要少用

3. 查询截取分析

查询优化

1. 观察，至少跑一天，看看生产的慢SQL情况
2. 开启慢查询日志，设置阈值，抓取超时的慢SQL语句
3. explain慢SQL分析
4. show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
5. SQL数据库服务器的参数调优

小表驱动大表

select * from A where id in (select id from B)
# 等价于
for select id from B
for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in优于exists

select * from A where exists (select id from B where A.id = B.id )
# 等价于
for select * from A
for select * from B where A.id = B.id

当A表的数据集必须小于B表的数据集时，用exists优于in

SELECT … FROM table WHERE EXISTS (subquery)

• 将主查询的数据，放到子查询中做条件验证，根据验证结果（TURE / FALSE）来决定主查询的数据结构是否得以保留。
• EXISTS (subquery)只返回TURE / FALSE，因此子查询中的SELECT *也可以是SELECT 1或SELECT X，官方说法是实际执行时会忽略SELECT清单，因此没有区别。

ORDER BY 优化

• 尽量使用Index方式排序，避免使用filesort
• 尽可能在索引上完成排序工作，遵照索引的最左前缀
• 如果不在索引列上，filesort有两种算法：mysql启动双路排序和单路排序
  • 双路排序：4.1之前使用；两次扫描磁盘，最终得到数据。
    • 读取行指针和orderby列，对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出
    • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其它字段。
  • 取一批数据，要对磁盘进行了两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，使用单路排序。
  • 单路排序：从磁盘中读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出。
  • 单路排序的效率更高，因为避免了第二次读取数据，而且把随机IO变成了顺序IO，但是会使用更多的空间。
  • 但是单路若是buffer空间有限，无法一次读取所有数据，从而要多次IO，此时效率并不比双路高。
• 优化策略
  • 增大sort_buffer_size参数（服务器数据库配置文件里）
  • 增大max_length_for_sort_data参数的设置（服务器数据库配置文件里）
  • 减少使用select *；
• 总结
  • MySQL的两种排序方式，文件排序或扫描有序索引排序
  • MySQL能为排序和查询使用相同的索引（也即是索引既能用来排序，也能用来查询）

index(age,birth)
select * from tblA

• where age>20 ORDER BY age：不会产生filesort ； using where，index
• where age>20 ORDER BY age,birth：不会产生filesort ； using where，index
• where age>20 ORDER BY birth：会产生filesort ； using where，index, filesort
• where age>20 ORDER BY birth,age：会产生filesort ； using where，index, filesort , template
• ORDER BY birth：会产生filesort ； usingindex, filesort
• where birth > ‘2016-01-28 00:00:00’ ORDER BY birth：会产生filesort ； using where，index, filesort
• where birth > ‘2016-01-28 00:00:00’ ORDER BY age：不会产生filesort ； using where，index
• ORDER BY age ASC , birth DESC：会产生filesort ； using index, filesort 要么同升A，要么同降D

GROUP BY优化

• group by实质上是先排序后进行分组，遵照索引建的最佳左前缀
• 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
• where高于having，能写在where限定的条件就不要去having限定了。

慢查询日志

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值（不含等于）的SQL，则会被记录到慢查询日志中。
• long_query_time默认值为10，指10秒。
• 收集慢SQL语句，结合explain进行全面分析。
• 默认没有开启慢查询日志。如果不是调优需要，也不建议开启，因为会对MySQL性能产生影响。
• 命令：
  • 查看是否开启：show variables like ‘%slow_query_log%’;
  • 开启：set global slow_query_log=1; （重启后失效）
  • 修改时间：set global long_query_time = [time] ; （设置好需要重新连接或新开一个会话才能看到修改值，或者show global variables like ‘%long_query_time%’; ）
  • 查看慢SQL语句数目：show global status like ‘%slow_queries%’;
• 日志分析工具 mysqldumpslow
  • mysqldumpslow - s r -t 10 /var/lib/mysql/localhost-slow.log得到返回记录集最多的10个SQL
  • mysqldumpslow - s c -t 10 /var/lib/mysql/localhost-slow.log得到访问次数最多的10个SQL
  • mysqldumpslow - s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log得到按照时间排序的含有左连接的前10个SQL
  • 另外建议结合 | 和more使用，以免出现爆屏

批量插入数据脚本

1. 建表

新建库

CREATE DATABASE bigData;
USE bigData;

新建表

CREATE TABLE dept(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	dname VARCHAR(20) NOT NULL DEFAULT "",
	loc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=GBK;

CREATE TABLE emp(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
	ename VARCHAR(20) NOT NULL DEFAULT "",/*名字*/
	job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
	mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
	hiredate DATE NOT NULL , /*入职时间*/
	sal DECIMAL(7,2) NOT NULL ,/*薪水*/
	comm DECIMAL(7,2) NOT NULL, /*红利*/
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=GBK;

2. 设置参数

log_bin_trust_function_creators : set global log_bin_trust_function_creators = 1;
永久方法：配置文件中更改

3. 创建函数，保证每条数据都不同

# 产生随机字符串——名字
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i<n DO
	# CONCAT 连接
	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i=i+1;
	END WHILE;
	RETURN return_str;
END $$

# 产生随机字符串——编号
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RAND()*10);
	RETURN i;
END $$

4. 创建存储过程

# 插入数据到emp
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
		SET i=i+1;
		INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
	UNTIL i=max_num
	END REPEAT;
	COMMIT;
END $$

# 插入数据到dept
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
		SET i=i+1;
		INSERT INTO dept(deptno,dname,loc) VALUES ((START+i),rand_string(10),rand_string(8));
	UNTIL i=max_num
	END REPEAT;
	COMMIT;
END $$

5. 调用存储过程

DELIMITER ;
# 10条测试一下
CALL insert_dept(100,10);

# 实测 5万条 12.49秒
MariaDB [bigData]> CALL insert_emp(100001,50000);
Query OK, 0 rows affected, 1 warning (12.49 sec)

MariaDB [bigData]> select * from emp limit 20;
+----+--------+--------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+--------+----------+-----+------------+---------+--------+--------+
|  1 | 100002 | iMkNkK | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    109 |
|  2 | 100003 | EfNBpX | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    108 |
|  3 | 100004 | lKRcMC | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    104 |
|  4 | 100005 | SQClAR | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    106 |
|  5 | 100006 | UurzvH | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    109 |
|  6 | 100007 | aewRXk | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    101 |
|  7 | 100008 | jxJdrv | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    101 |
|  8 | 100009 | pjazvD | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    106 |
|  9 | 100010 | EWTEpO | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    109 |
| 10 | 100011 | wrvaQb | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    107 |
| 11 | 100012 | ynRtwa | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    107 |
| 12 | 100013 | QOvMwa | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    106 |
| 13 | 100014 | vaQbLC | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    105 |
| 14 | 100015 | knJJrF | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    100 |
| 15 | 100016 | zoWPkH | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    106 |
| 16 | 100017 | dDFsXK | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    106 |
| 17 | 100018 | ZfDASO | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    103 |
| 18 | 100019 | moGqIw | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    101 |
| 19 | 100020 | qljmHz | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    105 |
| 20 | 100021 | qWLMEJ | SALESMAN |   1 | 2020-09-20 | 2000.00 | 400.00 |    106 |
+----+--------+--------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)

show profile

• mysql提供的可以用来分析当前会话中语句执行的资源消耗情况。
• 默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
• 命令
  • 查看：show variables like ‘profiling’;
  • 开启 ：set profiling=on;
  • 运行SQL：
  • 查看结果： show profiles;
  • 诊断：show profile cpu,block io for query 3;
    • all 显示所有开销
    • block io 显示块IO相关开销
    • context switches 上下文切换相关开销
    • CPU
    • IPC 发送和接收相关开销
    • MEMORY 内存
    • PAGE FAULTS 页面错误相关开销
    • SOURCE 和source_function,source_file,source_line相关开销
    • SWAPS 显示交换次数相关开销
  • 严重问题：
    • converting HEAP to MyISAM 查询结果太大，内存不够用了往磁盘上搬了
    • Creating tmp table创建临时表
    • Copying to tmp table on disk把内存中临时表复制到磁盘
    • locked

MariaDB [bigData]> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00081741 | show variables like 'profiling'            |
|        2 | 0.00009953 | select * from emp limit 20                 |
|        3 | 0.06913266 | select  count(*) from emp where deptno=106 |
|        4 | 0.01880381 | select  count(*) from emp group by deptno  |
+----------+------------+--------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [bigData]> show profile cpu, block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000015 | 0.000008 |   0.000004 |            0 |             0 |
| Waiting for query cache lock   | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000140 | 0.000098 |   0.000045 |            0 |             0 |
| checking permissions           | 0.000008 | 0.000004 |   0.000002 |            0 |             0 |
| Opening tables                 | 0.000010 | 0.000007 |   0.000003 |            0 |             0 |
| After opening tables           | 0.000004 | 0.000002 |   0.000001 |            0 |             0 |
| System lock                    | 0.000003 | 0.000002 |   0.000001 |            0 |             0 |
| Table lock                     | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| After table lock               | 0.000005 | 0.000003 |   0.000002 |            0 |             0 |
| Waiting for query cache lock   | 0.000002 | 0.000001 |   0.000000 |            0 |             0 |
| After table lock               | 0.000013 | 0.000009 |   0.000005 |            0 |             0 |
| init                           | 0.053006 | 0.039487 |   0.013285 |            0 |             0 |
| optimizing                     | 0.000075 | 0.000048 |   0.000022 |            0 |             0 |
| statistics                     | 0.000020 | 0.000014 |   0.000006 |            0 |             0 |
| preparing                      | 0.000055 | 0.000038 |   0.000018 |            0 |             0 |
| executing                      | 0.000008 | 0.000005 |   0.000002 |            0 |             0 |
| Sending data                   | 0.015687 | 0.010304 |   0.000000 |            0 |             0 |
| end                            | 0.000023 | 0.000019 |   0.000000 |            0 |             0 |
| query end                      | 0.000013 | 0.000013 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| updating status                | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| updating status                | 0.000016 | 0.000017 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000002 | 0.000001 |   0.000000 |            0 |             0 |
| updating status                | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| storing result in query cache  | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
28 rows in set (0.00 sec)

全局查询日志

不要在生产环境开启！

在my.cnf中，设置

# 开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

4. Mysql锁机制

锁分类

• 对数据操作的类型：读/写锁
• 对数据操作的粒度：表锁/行锁

表锁

偏向MyISAM存储引擎，开销小。加锁快；无死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。

CREATE TABLE mylock(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
)engine myisam;

INSERT INTO mylock(name) values('a');
INSERT INTO mylock(name) values('b');
INSERT INTO mylock(name) values('c');
INSERT INTO mylock(name) values('d');
INSERT INTO mylock(name) values('e');

SELECT * FROM mylock;

# 查看锁
show open tables;
# 手动增加表锁
LOCK TABLE table1 read(write),table2 read(write);
# 手动解锁
uplock tables;
# 分析表锁定

MariaDB [bigData]> show status like 'table%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Table_locks_immediate | 100186 |
| Table_locks_waited    | 0      |
+-----------------------+--------+
2 rows in set (0.08 sec)

• Table_locks_immediate ：产生表级锁定的次数，表示可以立即获取锁的查询次数，没立即获取锁值+1；
• Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高说明存在着较严重的表级锁争用情况。

加完读锁 v后：

• 别人可以读，不能写加锁的表（阻塞），可以写其它没有加锁的表
• 自己不能写（插入/更新），不能读其它没有加锁的表

加完写锁后：

• 别人不能读（阻塞），不能写加锁的表（阻塞），可以写其它没有加锁的表
• 自己不能写（插入/更新），不能读其它没有加锁的表

总结：

• 对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
• 对myisam表的写操作（加写锁），会阻塞其他进程对同一表的读和写请求。只有当读锁释放后，才会执行其它进程的读写操作。

行锁

• 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率低，并发度高。
• InnoDB与myisam的最大不同有两点，一是支持事务，而是采用了行级锁。

事务：

• 更新丢失：当两个及以上事务选择同一行，最后的更新覆盖了其它事务的更新 （当一个事务完成之前，其它事务不能访问同一数据，则可避免）
• 脏读：事务A读到事务B已修改没有提交的数据，在这个数据基础上进行了操作，如果B事务回滚，A读取的数据无效。不符合一致性
• 不可重复读：事务A读到了事务B已提交的修改数据（与前一次读取数据不同），不符合隔离性
• 幻读：事务A读到事务B提交的新增数据，不符合隔离性。

事务隔离级别：

• 未提交读
• 已提交读
• 可重复读
• 可序列化

查看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;
MySQL默认RR可重复读

行锁案例

无索引行锁升级为表锁

分析行锁：

MariaDB [bigData]> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |#等待总时长
| Innodb_row_lock_time_avg      | 0     |#等待平均时长
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |#等待总次数
+-------------------------------+-------+

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP）
InnoDB也会对 这个间隙加锁，这种锁机制也就是所谓的间隙锁（Next-Key锁）。

当某个范围被锁定时，无法插入锁定键值范围内的任何数据，可能会影响性能 。

页锁介于行锁和表锁之间；会出现死锁；

5. 主从复制

MySQL复制是异步且串行化的。
MySQL复制过程：

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events。
2. slave将master的binary log events拷贝到它的中继日志relay log
3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。

复制的基本原则：
4. 每个slave只有一个master
5. 每个slave只能一个有唯一的服务器ID
6. 每个master可以有多个slave

一主一从常见配置

• mysql版本一致且后台以服务运行
• 主从都配置在[mysqld]节点下，都是小写
• 主机、从机修改配置文件
• 主机、从机重启
• 都关闭防火墙 service iptables stop
• 在主机上建立账户并授权slave
  • mysql>GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'182.168.1.131' IDENTIFIED BY '123456' ;
  • mysql>show master status;查看master的状态，记录下file和position的值（pos值后续会改变）
• 在从机上配置需要复制的主机
  • mysql>change master to master_host=‘192.1681.14’,
  • master_user=‘zhangsan’,
  • master_password=‘123456’,
  • master_log_file=‘mysqlbin.具体数字’,master_log_pos=具体值;
  • start slave;
  • show slave status\G; 当Slave_IO_Running:Yes 和Slave_SQL_Running:Yes时，配置成功。
• 主机新建库，新建表，insert记录；从机复制

配置文件

主机配置：

1. 主服务器唯一ID: server-id=1
2. 启用二进制日志：log-bin=自己本地的路径/mysqlbin
3. 【可选】启用错误日志：log-err=自己本地的路径/mysqlerr
4. 【可选】根目录：basedir=“自己本地的路径”
5. 【可选】临时目录：tmpdir=“自己本地的路径”
6. 【可选】数据目录：datadir=“自己本地的路径/Data”
7. read-only=0
8. 【可选】设置不要复制的数据库：binlog-ignore-db=mysql
9. 【可选】设置需要复制的数据库：binlog-do-db=需要复制的主数据库名字

从机配置：

1. 从服务器唯一ID: server-id=1
2. 启用二进制日志：log-bin=自己本地的路径/mysqlbin