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
- 连接层:最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在此层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户都拿验证它所具有的操作权限。
- 服务层第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在这层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化,如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。
- 引擎层:存储引擎真正负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的 实际需要进行选取。
- 存储层
存储引擎
- 查看存储引擎: mysql>show engines;
- 查看默认存储引擎: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
- MYSQL 中有专门负责优化SELECT语句的优化器模块。主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的query提供MySQL认为最优的 执行计划(这部分比较耗时间)
- 当客户端向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的设置。
索引优化
索引失效的情况
- 全值匹配我最爱:select的项正好全是索引中的列,可以完美匹配。
- 最佳左前缀法则
- 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换),会导致索引失效
- 存储引擎不能使用索引中范围条件右边的列:譬如 where age >25 and pos = ‘maneger’;即使建了age+pos的索引,也只能用到age的部分。
- 尽量使用覆盖索引,减少select *
- MySQL在不适用不等于(!= 或<>)的时候无法使用索引会导致全表扫描
- is null,is not null无法使用索引
- like以通配符开头(’%abc…’)会导致索引失效。
- 字符串不加单引号索引失效:会隐式的类型转换,把int转换为varchar
- 少用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. 查询截取分析
查询优化
- 观察,至少跑一天,看看生产的慢SQL情况
- 开启慢查询日志,设置阈值,抓取超时的慢SQL语句
- explain慢SQL分析
- show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
- 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,此时效率并不比双路高。
- 双路排序:4.1之前使用;两次扫描磁盘,最终得到数据。
- 优化策略
- 增大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复制过程:
- master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events。
- slave将master的binary log events拷贝到它的中继日志relay log
- 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记录;从机复制
配置文件
主机配置:
- 主服务器唯一ID: server-id=1
- 启用二进制日志:log-bin=自己本地的路径/mysqlbin
- 【可选】启用错误日志:log-err=自己本地的路径/mysqlerr
- 【可选】根目录:basedir=“自己本地的路径”
- 【可选】临时目录:tmpdir=“自己本地的路径”
- 【可选】数据目录:datadir=“自己本地的路径/Data”
- read-only=0
- 【可选】设置不要复制的数据库:binlog-ignore-db=mysql
- 【可选】设置需要复制的数据库:binlog-do-db=需要复制的主数据库名字
从机配置:
- 从服务器唯一ID: server-id=1
- 启用二进制日志:log-bin=自己本地的路径/mysqlbin