MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）_mysql sql优化
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
目录
0 存储引擎介绍
myisam存储如果表对事务要求不高同时是以查询和添加为主的我们考虑使用myisam存储引擎比如bbs 中的发帖表回复表
- 需要定时进行碎片整理因为删除的数据还是存在
optimize table table_name;
InnoDB存储对事务要求高保存的数据都是重要数据我们建议使用INN0DB,比如订单表账号表.
面试问MyISAM和INNODB的区别
- 1.事务安全
- 2.查询和添加速度
- 3.支持全文索引
- 4.锁机制
- 5.外键MyISAM不支持外键INNODB 支持外键.
Mermory存储比如我们数据变化频繁不需要入库同时又频繁的查询和修改我们考虑使用memory
查看mysql以提供什么存储引擎show engines;
查看mysql当前默认的存储引擎show variables like '%storage_engine%';
1 SQL性能分析
SQL性能下降原因
- 1、查询语句写的烂
- 2、索引失效数据变更
- 3、关联查询太多join设计缺陷或不得已的需求
- 4、服务器调优及各个参数设置缓冲、线程数等
通常SQL调优过程
- 观察至少跑1天看看生产的慢SQL情况。
- 开启慢查询日志设置阙值比如超过5秒钟的就是慢SQL并将它抓取出来。
- explain + 慢SQL分析。
- show profile。
- 运维经理 or DBA进行SQL数据库服务器的参数调优。
总结
- 1、慢查询的开启并捕获
- 2、explain + 慢SQL分析
- 3、show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
- 4、SQL数据库服务器的参数调优
2 常见通用的JOIN查询
SQL执行加载顺序
手写顺序
SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type>
JOIN <right_table> on <join_codition> //join_codition比如员工的部门ID和部门表的主键id相同
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT
<limit_number>
MySQL机读顺序
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
总结
- 运行顺序一上一下
七种JOIN写法
创建表插入数据左右主外键相连
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
//设置存储引擎主键自动增长和默认文本字符集
CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
#查询执行后结果
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
1、inner join只有 deptId 和 id 的共有部分
2、left join全A前七条共有数据第八条a表独有数据b表补null
3、right join全B前七条共有数据第八条b表独有数据a表补null
4、左join独A表A独有部分
5、右join独B表B独有部分
6、full joinMySQL不支持full join用全a+全bunion去重中间部分
union关键字可以合并去重
7、A、B各自独有集合
3 索引介绍
3.1 索引是什么
MySQL官方对索引的定义为索引Index是帮助MySQL高效获取数据的数据结构索引的本质是数据结构排序+查询两种功能。
索引的目的在于提高查询效率可以类比字典。
如果要查“mysql”这个单词我们肯定需要定位到m字母然后从下往下找到y字母再找到剩下的sql。
如果没有索引那么你可能需要逐个逐个寻找如果我想找到Java开头的单词呢或者Oracle开头的单词呢
是不是觉得如果没有索引这个事情根本无法完成
索引可以理解为排好序的快速查找数据结构
下图就是一种可能的索引方式示例
假如找4号这本书扫码得到对应的编号为9191比34大往右边找91比89大往右边找然后找到比较三次后就可以找到然后检索出对应的物理地址
为了加快Col2的查找可以维护一个右边所示的二叉查找树每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针这样就可以运用二叉查找在一定的复杂度内获取到相应数据从而快速的检索出符合条件的记录
结论在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用(指向数据这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引
一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。
我们平常所说的索引如果没有特别指明都是指B树多路搜索树并不一定是二叉的结构组织的索引。其中聚集索引次要索引覆盖索引复合索引前缀索引唯一索引默认都是使用B+树索引统称索引。当然除了B+树这种类型的索引之外还有哈稀索引(hash index)等
3.2 索引优劣势
优势
- 类似大学图书馆建书目索引提高数据检索的效率降低数据库的IO成本。
- 通过索引列对数据进行排序降低数据排序的成本降低了CPU的消耗。
劣势
- 实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录所以索引列也是要占用空间的占空间
- 虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件每次更新添加了索引列的字段都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素如果你的MysQL有大数据量的表就需要花时间研究建立最优秀的索引或优化查询
3.3 索引分类和建索引命令语句
主键索引索引值必须是唯一的且不能为NULL
- 第一种
CREATE TABLE table_name(id int PRIMARY KEY aoto_increment,name varchar(10)); - 第二种
ALTER TABLE table_name ADD PRIMARY KEY (columnName);
普通索引索引值可出现多次
- 第一种
CREATE INDEX index_name on table_name(columnName); - 第二种
ALTER TABLE table_name ADD INDEX index_name (columnName);
全文索引主要是针对文本的检索如文章全文索引只针对MyISAM引擎有效并且只针对英文内容生效
-
建表时创建
#建表 CREATE TABLE articles( id INT UNSIGNED ATUO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT(title,body) )engine=myisam charset utf8; #指定引擎 #使用 select * from articles where match(title,body) against('英文内容'); #只针对英语内容生效 #说明 #1、在mysql中fultext索引只针对 myisam 生效 #2、mysq1自己提供的flltext只针对英文生效->sphinx (coreseek)技术处理中文工 #3、使用方法是match(字段名...) against(‘关键字') #4、全文索引一个叫停止词因为在一个文本中创建索引是一个无穷大的数因此对一些常用词和字符就不会创建这些词称为停止词 -
ALTER TABLE table_name ADD FULLTEXT index_name (columnName);
唯一索引索引列的值必须唯一但允许有空值NULL并可以有多个。
- 第一种
CREATE UNIQUE INDEX index_name ON table_name(columnName); - 第二种
ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (columnName);
单值索引即一个索引只包含单个列一个表可以有多个单列索引。
- 第一种
CREATE INDEX index_name ON table_name(columnName); - 第二种
ALTER TABLE table_name ADD INDEX index_name ON (columnName);
select * from user where name='';
//经常查name字段为其建索引
create index idx_user_name on user(name);
复合索引即一个索引包含多个列
- 第一种
CREATE INDEX index_name ON table_name(columnName1columnName2...); - 第二种
ALTER TABLE table_name ADD INDEX index_name ON (columnName1columnName2...);
select * from user where name='' and email='';
//经常查name和email字段为其建索引
create index idx_user_name on user(name, email);
查询索引
- 第一种
SHOW INDEX FROM table_name; - 第二种
SHOW KEYS FROM table_name;
删除索引
- 第一种
DROP INDEX index_name ON table_name; - 第二种
ALTER TABLE table_name DROP INDEX index_name; - 删除主键索引
ALTER TBALE table_name DROP PRIMARY KEY;
3.4 索引结构与检索原理
MySQL索引结构
- BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
初始化介绍
一颗b+树浅蓝色的块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项深蓝色所示和指针黄色所示),如磁盘块1包含数据项17和35包含指针P1、P2、P3,
P1表示小于17的磁盘块P2表示在17和35之间的磁盘块P3表示大于35的磁盘块。
真实的数据存在于叶子节点3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据只存储指引搜索方向的数据项如17、35并不真实存在于数据表中。
查找过程
如果要查找数据项29那么首先会把磁盘块1由磁盘加载到内存此时发生一次IO。在内存中用二分查找确定 29 在 17 和 35 之间锁定磁盘块1的P2指针内存时间因为非常短相比磁盘的IO可以忽略不计通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存发生第二次IO29 在 26 和 30 之间锁定磁盘块3的P2指针通过指针加载磁盘块8到内存发生第三次IO同时内存中做二分查找找到29结束查询总计三次IO
真实的情况是3层的b+树可以表示上百万的数据如果上百万的数据查找只需要三次IO性能提高将是巨大的如果没有索引每个数据项都要发生一次IO那么总共需要百万次的IO显然成本非常非常高
3.5 哪些情况适合建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段外键关系建立索引
- 单键/组合索引的选择问题who?(在高并发下倾向创建组合索引)
- 查询中排序的字段排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
3.6 哪些情况不适合建索引
- Where条件里用不到的字段不创建索引
- 表记录太少300w以上建
- 经常增删改的表提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件
- 数据重复且分布平均的表字段因此应该只为最经常查询和最经常排序的数据列建立索引。注意如果某个数据列包含许多重复的内容为它建立索引就没有太大的实际效果。比如国籍、性别
假如一个表有10万行记录有一个字段A只有T和F两种值且每个值的分布概率天约为50%那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录表索引列有1980个不同的值那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1这个索引的效率就越高
4 性能分析
4.1 性能分析前提知识
MySQL Query Optimizer查询优化器[ˈkwɪəri] [ˈɒptɪmaɪzə]
Mysql中专门负责优化SELECT语句的优化器模块主要功能通过计算分析系统中收集到的统计信息为客户端请求的Query提供他认为最优的执行计划他认为最优的数据检索方式但不见得是DBA认为是最优的,这部分最耗费时间
当客户端向MySQL请求一条Query命令解析器模块完成请求分类区别出是SELECT并转发给MySQL Query Optimizer时MySQL Query Optimizer首先会对整条Query进行优化处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划则会读取所涉及对象的统计信息根据Query进行写相应的计算分析然后再得出最后的执行计划
MySQL常见瓶颈
- CPUCPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈topfreeiostat和vmstat来查看系统的性能状态
4.2 Explain使用简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
Explain的作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
使用Explain
- explain + sql语句
- 执行计划包含的信息
重点| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
4.3 执行计划包含的信息字段解释重中之重
执行计划包含的信息重点 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
面试重点id、type、key、rows、Extra
id表的读取顺序
select查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序
三种情况
-
1、id相同执行顺序由上至下t1、t3、t2
-
2、id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行t3、t1、t2
-
3、id相同不同同时存在。先走数字大的数字相同的由上至下t3、s1、t2
select_type 数据读取操作的操作类型
查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。
- SIMPLE [ˈsɪnpl] 简单的select查询,查询中不包含子查询或者UNION
- PRIMARY查询中若包含任何复杂的子部分最外层查询则被标记为最后加载的那个
- SUBQUERY [ˈkwɪəri] 在SELECT或WHERE列表中包含了子查询
- DERIVED [dɪˈraɪvd]在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表里
- UNION [ˈjuːniən]若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVED
- UNION RESULT [rɪˈzʌlt] 从UNION表获取结果的SELECT两个select语句用UNION合并
table显示执行的表名
显示这一行的数据是关于哪张表的
type访问类型排列
显示查询使用了何种类型
访问类型排列system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL
type常用八种类型
结果值从最好到最坏依次是重点system > const > eq_ref > ref > range > index > ALL
一般来说得保证查询至少达到range级别最好能达到ref
详细说明
-
system表只有一行记录等于系统表这是const类型的特列平时不会出现这个也可以忽略不计。
-
const表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据所以很快如将主键置于where列表中MySQL就能将该查询转换为一个常量。
-
eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
-
ref非唯一性索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体
-
range只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好因为它只需要开始于索引的某一点而结束语另一点不用扫描全部索引
-
indexFull Index Scanindex与ALL区别为index类型只遍历索引列。这通常比ALL快因为索引文件通常比数据文件小也就是说虽然all和Index都是读全表但index是从索引中读取的而all是从硬盘中读的
-
allFull Table Scan将遍历全表以找到匹配的行
工作案例经理这条SQL我跑了一下Explain分析在系统上可能会有ALL全表扫描的情况建议尝试一下优化。我把这条SQL改了改我优化后是这么写这个效果已经从ALL变成了…
possible_keys哪些索引可以使用
显示可能应用在这张表中的索引一个或多个。查询涉及到的字段火若存在索引则该索引将被列出但不一定被查询实际使用系统认为理论上会使用某些索引
key哪些索引被实际使用
实际使用的索引。如果为NULL则没有使用索引要么没建要么建了失效
查询中若使用了覆盖索引则该索引仅出现在key列表中
覆盖索引建的索引字段和查询的字段一致如下图
key_len消耗的字节数
表示索引中使用的字节数可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下长度越短越好
key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的
ref表之间的引用
显示索引的哪一列被使用了如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值。
rows每张表有多少行被优化器查询
根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数越小越好
未建索引时
建索引后扫描行数减少
Extra [ˈekstrə]
包含不适合在其他列中显示但十分重要的额外信息
信息种类Using filesort 、Using temporary 、Using index 、Using where 、Using join buffer 、impossible where 、select tables optimized away 、distinct
Using filesort需要优化
说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"
Using temporary需要优化
使了用临时表保存中间结果MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
Using indexgood
表示相应的select操作中使用了覆盖索引Covering Index避免访问了表的数据行效率不错
-
情况一
-
情况二
覆盖索引 / 索引覆盖Covering Index。
- 理解方式一就是select的数据列只用从索引中就能够取得不必读取数据行MySQL可以利用索引返回select列表中的字段而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
- 理解方式二索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了或覆盖了满足查询结果的数据就叫做覆盖索引。
注意
- 如果要使用覆盖索引一定要注意select列表中只取出需要的列不可select*
- 因为如果将所有字段一起做索引会导致索引文件过大查询性能下降
Using where表明使用了where过滤。
Using join buffer使用了连接缓存
impossible wherewhere子句的值总是false不能用来获取任何元组
select tables optimized away
在没有GROUPBY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作在找到第一匹配的元组后即停止找同样值的动作。
练习
写出下图的表的执行顺序
第一行执行顺序4id列为1表示是union里的第一个selectselect_type列的primary表示该查询为外层查询table列被标记为表示查询结果来自一个衍生表其中derived3中3代表该查询衍生自第三个select查询即id为3的select。【select d1.name… 】
第二行执行顺序2id为3是整个查询中第三个select的一部分。因查询包含在from中所以为derived。【select id,namefrom t1 where other_column=’’】
第三行执行顺序3select列表中的子查询select_type为subquery为整个查询中的第二个select。【select id from t3】
第四行执行顺序1select_type为union说明第四个select是union里的第二个select最先执行【select name,id from t2】
第五行执行顺序5代表从union的临时表中读取行的阶段table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
5 索引优化
5.1 索引单表优化案例
建表
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 VARCHAR(255) NOT NULL,
content TEXT NOT NULL
);
INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
//查询
mysql> 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 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
案例
要求查询 category_id 为 1 且 comments 大于1 的情况下views 最多的 article_id
//功能实现
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.00 sec)
//explain分析
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
结论很显然type是ALL即最坏的情况。Extra里还出现了Using filesort也是最坏的情况。优化是必须的
开始优化
新建索引给WHERE语句后使用的字段添加索引
创建方式
create index idx_article_ccv on article(category_id,comments,views);ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
索引用处不大删除DROP INDEX idx_article_ccv ON article;
结论
-
type变成了range这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
-
但是我们已经建立了索引为啥没用呢
-
这是因为按照BTree索引的工作原理先排序category_id如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。
-
当comments字段在联合索引里处于中间位置时因comments > 1条件是一个范围值(所谓range)MySQL无法利用索引再对后面的views部分进行检索即
range类型查询字段后面的索引无效。
改进
上次创建索引相比这次不为comments字段创建索引
结论type变为了refref 中是 constExtra 中的 Using filesort也消失了结果非常理想
5.2 索引两表优化案例
建表
CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS book(
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
);
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
//查询
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 17 |
| 2 | 2 |
| 3 | 18 |
| 4 | 4 |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
| 8 | 1 |
| 9 | 18 |
| 10 | 6 |
| 11 | 15 |
| 12 | 15 |
| 13 | 12 |
| 14 | 15 |
| 15 | 18 |
| 16 | 2 |
| 17 | 18 |
| 18 | 5 |
| 19 | 7 |
| 20 | 1 |
| 21 | 2 |
+----+------+
21 rows in set (0.00 sec)
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 8 |
| 2 | 14 |
| 3 | 3 |
| 4 | 16 |
| 5 | 8 |
| 6 | 12 |
| 7 | 17 |
| 8 | 8 |
| 9 | 10 |
| 10 | 3 |
| 11 | 4 |
| 12 | 12 |
| 13 | 9 |
| 14 | 7 |
| 15 | 6 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 5 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
开始Explain分析type都是all需要优化总有一个表来添加索引驱动
- 左连接为左表加索引
删除索引drop index y on class;
- 左连接为右表添加索引
删除索引drop index Y on book;
- 案例如果别人建的索引位置不对只需要自己查询时调整左右表的顺序即可
结论
- 第二行的type变为了refrows也变少了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行左边一定都有
所以右边是我们的关键点一定需要在右表建立索引小表驱动大表。 左连接右表加索引同理右连接左表加索引
5.3 索引三表优化案例
建表
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
//查询
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
| 1 | 10 |
| 2 | 13 |
| 3 | 17 |
| 4 | 5 |
| 5 | 12 |
| 6 | 7 |
| 7 | 15 |
| 8 | 17 |
| 9 | 17 |
| 10 | 14 |
| 11 | 19 |
| 12 | 13 |
| 13 | 5 |
| 14 | 8 |
| 15 | 2 |
| 16 | 8 |
| 17 | 11 |
| 18 | 14 |
| 19 | 13 |
| 20 | 5 |
+---------+------+
20 rows in set (0.00 sec)
用上一节两个表删除他们的索引
三表查询语句应为SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
创建索引
-
应该为第一个LFET JOIN 的右表 book 建索引
alter table `book` add index Y(`card`); -
应该为第二个LFET JOIN 的右表 phone 建索引
alter table `phone` add index z(`card`);
Explain分析
后2行的 type 都是ref且总 rows优化很好效果不错。因此索引最好设置在需要经常查询的字段中
结论
- Join语句的优化
- 尽可能减少Join语句中的NestedLoop的循环总次数“
永远用小结果集驱动大的结果集比如书的类型表驱动书的名称表”。 - 优先优化NestedLoop的内层循环保证Join语句中被驱动表上Join条件字段已经被索引。
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下不要太吝惜JoinBuffer的设置
5.4 索引失效
建表
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
索引失效案例
-
1、全值匹配我最爱
-
2、
最佳左前缀法则重要如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。
中间列不能断
-
3、不在索引列上做任何操作计算、函数、自动or手动类型转换会导致索引失效而转向全表扫描。
-
4、存储引擎不能使用索引中范围条件右边的列范围之后全失效范围列并不是做的查询而是排序。
-
5、尽量使用覆盖索引只访问索引的查询索引列和查询列一致减少select *。
-
6、mysql在使用不等于!=或者<>的时候无法使用索引会导致全表扫描。
-
7、is null, is not null 也无法使用索引。
-
8、like以通配符开头’%abc…’mysql索引失效会变成全表扫描的操作%写在最右边索引不会失效或覆盖索引。
问题解决like '%字符串%'时索引不被使用的方法 采用覆盖索引的方法
建表CREATE TABLE `tbl_user`( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age`INT(11) DEFAULT NULL, `email` VARCHAR(20) DEFAULT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com'); INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com'); INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com'); INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com'); //查询 mysql> select * from tbl_user; +----+------+------+-----------+ | id | name | age | email | +----+------+------+-----------+ | 1 | 1aa1 | 21 | a@163.com | | 2 | 2bb2 | 23 | b@163.com | | 3 | 3cc3 | 24 | c@163.com | | 4 | 4dd4 | 26 | d@163.com | +----+------+------+-----------+ 4 rows in set (0.00 sec)创建索引
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);索引成功使用
索引失效
总结%写在最右边如果非要写在最左边就使用覆盖索引 -
9、字符串不加单引号索引失效。
Explain分析
-
10、少用or用它来连接时会索引失效
5.5 索引面试题分析
建表
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
//查看表结构
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
建索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
//查看索引
mysql> show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
1逐一增加列
2交换条件顺序不影响索引但最好按照建索引顺序来写SQL
3) 限定范围
4order by
5group by
定值、范围还是排序一般order by是给个范围
group by基本上都需要进行排序会有临时表产生
建议
- 对于单值索引尽量选择针对当前query过滤性更好的索引。
- 在选择组合索引的时候当前Query中过滤性最好的字段在索引字段顺序中位置越靠左越好。
- 在选择组合索引的时候尽量选择可以能够包含当前query中的where字句中更多字段的索引。
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
5.6 总结
优化总结口诀
全值匹配我最爱 最左前缀要遵守
带头大哥不能死 中间兄弟不能断
索引列上少计算 范围之后全失效
LIKE 百分写最右 覆盖索引不写 *
不等空值还有OR 索引影响要注意
VAR 引号不可丢 SQL 优化有诀窍。
6 查询截取分析
6.1 小表驱动大表
EXISTS [ɪɡˈzɪsts]语法SELECT ...FROM table WHERE EXISTS (subquery)
该语法可以理解为将主查询的数据放到子查询中做条件验证根据验证结果TRUE或FALSE来决定主查询的数据结果是否得以保留
提示
- EXSTS(subquey) 只返回TRUE或FALSE因此子查询中的SELECT * 也可以是 SELECT 1 或select ‘X’官方说法是实际执行时会忽略SELECT清单因此没有区别。
- EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比如果担忧效率问题可进行实际检验以确定是否有效率问题。
- EXISTS子查询往往也可以用条件表达式其他子查询或者JOIN来替代何种最优需要具体问题具体分析
in和exists用法
6.2 Order by 关键字排序优化
1、ORDER BY之后子句尽量使用Index方式排序避免使用FileSort方式排序
建表
create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
create index idx_A_ageBirth on tblA(age, birth);
//查询
mysql> select * from tblA;
+------+---------------------+
| age | birth |
+------+---------------------+
| 22 | 2021-04-04 19:31:45 |
| 23 | 2021-04-04 19:31:45 |
| 24 | 2021-04-04 19:31:45 |
+------+---------------------+
3 rows in set (0.00 sec)
mysql> show index from tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbla | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | |
| tbla | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
关注点是order by之后会不会产生Using filesort
MySQL支持二种方式的排序FileSort和lIndexIndex效率高它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况会使用Index方式排序
- ORDER BY语句使用索引最左前列。
- 使用where子句与Order BY子句条件列组合满足索引最左前列。
2、尽可能在索引上完成排序操作遵照建索引的最佳左前缀
3、如果不在索引列上mysql的filesort有两种算法自动启动
-
双路排序
MySQL4.1之前是使用双路排序字面意思就是两次扫描磁盘最终得到数据读取行指针和OrderBy列对他们进行排序然后扫描已经排序好的列表按照列表中的值重新从列表中读对应的数据输出。
从磁盘取排序字段在buffer进行排序再从磁盘取其他字段。
取一批数据要对磁盘进行了两次扫描众所周知I\O是很耗时的所以在mysql4.1之后出现了第二种改进的算法就是单路排序
-
单路排序
从磁盘读取查询需要的所有列按照order by列在buffer对它们进行排序然后扫描排序压的列表进行输出它的效率更快一些避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间因为它把每一行都保存在内存中了
-
结论及引申出的问题
由于单路是后出的总体而言好过双路
但是用单路有问题在sort_buffer中方法B比方法A要多占用很多空间因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量导致每次只能取sort_buffer容量大小的数据进行排序创建tmp文件多路合并)排完再取取
sort_buffer容量大小再排……从而多次I/O。本来想省一次I/O操作反而导致了大量的I/O操作反而得不偿失
4、优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
- Why
5、小总结
6.3 Group by 优化
group by实质是先排序后进行分组遵照索引建的最佳左前缀。
当无法使用索引列增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置。
where高于having能写在where限定的条件就不要去having限定了
6.4 慢查询日志重点
介绍
- MySQL的慢查询日志是MySQL提供的一种日志记录它用来记录在MySQL中响应时间超过阀值的语句具体指运行时间超过long_query_time值的SQL则会被记录到慢查询日志中。
- 具体指运行时间超过long_query_time值的SQL则会被记录到慢查询日志中。long_query_time的默认值为10意思是运行10秒以上的语句。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值比如一条sql执行超过5秒钟我们就算慢SQL希望能收集超过5秒的sql结合之前explain进行全面分析
操作说明
默认情况下MySQL数据库没有开启慢查询日速需要我们手动来设置这个参数。
当然如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看是否开启及如何开启
- 默认
SHOW VARIABLES LIKE '%slow_query_log%';[ˈveəriəbls] - 开启
set global slow_query_log=1;只对当前数据库生效如果MySQL重启后则会失效
如果要永久生效就必须修改配置文件my.cnf(其它系统变量也是如此)
修改my.cnf文件[mysqld] 下增加或修改参数slow_query_log和slow_query_log_file后然后重启MySQL服务器。也即将如下两行配置进my.cnf文件
slow_query_log =1
slow_query_log_file=/var/lib/mysqatguigu-slow.log
关于慢查询的参数slow_query_log_file它指定慢查询日志文件的存放路径系统默认会给一个缺省的文件host_name-slow.log如果没有指定参数slow_query_log_file的话
开启了慢查询日志后什么样的SQL才会记录到慢查询日志里面呢
这个是由参数long_query_time控制默认情况下long_query_time的值为10秒命令SHOW VARIABLES LIKE 'long_query_time%';
可以使用命令修改也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况并不会被记录下来。也就是说在mysql源码里是判断大于long_query_time而非大于等于。
命名修改慢SQL阈值时间set global long_query_time=3; [ˈɡləʊbl]
看不到修改情况的话重开连接或者换一个语句show global variables like 'long_query_time';
记录慢SQL并后续分析:
假设我们成功设置慢SQL阈值时间为3秒set global long_query_time=3;。
模拟超时SQLselect sleep(4);
查询当前系统中有多少条慢查询记录show global status like '%Slow_queries%'; [ˈsteɪtəs]
在配置文件中设置慢SQL阈值时间永久生效
#[mysqld]下配置:
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE;
日志分析工具mysqldumpslow
在生产环境中如果要手工分析日志查找、分析SQL显然是个体力活MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的帮助信息mysqldumpslow --help。
常用mysqldumpslow帮助信息
- s是表示按照何种方式排序
- c访问次数
- l锁定时间
- r返回记录
- t查询时间
- al平均锁定时间
- ar平均返回记录数
- at平均查询时间
- t即为返回前面多少条的数据
- g后边搭配一个正则匹配模式大小写不敏感的
工作常用参考
- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log - 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log - 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log - 另外建议在使用这些命令时结合│和more 使用否则有可能出现爆屏情况`mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more
6.5 批量插入数据脚本
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=utf8;
//员工表
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=utf8;
2、设置参数log_bin_trust_function_creators
创建函数假如报错This function has none of DETERMINISTIC…
由于开启过慢查询日志因为我们开启了bin-log我们就必须为我们的function指定一个参数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
这样添加了参数以后如果mysqld重启上述参数又会消失永久方法
- windows下my.ini[mysqld] 加上
log_bin_trust_function_creators=1 - linux下/etc/my.cnf 下my.cnf[mysqld] 加上
log_bin_trust_function_creators=1
3、创建函数保证每条数据都不同
- 随机产生字符串
delimiter $$ #为了存储过程能正常运行修改命令结束符两个 $$ 表示结束 create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i=i+1; end while; return return_str; end $$ - 随机产生部门编号
delimiter $$ create function rand_num() returns int(5) begin declare i int default 0; set i=floor(100+rand()*10); return i; end $$
4、创建存储过程
-
创建往emp表中插入数据的存储过程
delimiter $$ create procedure insert_emp(in start int(10),in max_num int(10)) #max_num表示插入多少条数据 begin declare i int default 0; set autocommit = 0; #关闭自动提交避免写一个insert提交一次50w条一次性提交 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表中插入数据的存储过程
delimiter $$ 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、调用存储过程
-
往dept表中插入数据
mysql> DELIMITER ; # 修改默认结束符号为;之前改成了## mysql> CALL insert_dept(100, 10); Query OK, 0 rows affected (0.01 sec) -
往emp表中插入50万数据
mysql> DELIMITER ; mysql> CALL insert_emp(100001, 500000); Query OK, 0 rows affected (27.00 sec) -
查看运行结果
mysql> select * from dept; +----+--------+---------+--------+ | id | deptno | dname | loc | +----+--------+---------+--------+ | 1 | 101 | mqgfy | ck | | 2 | 102 | wgighsr | kbq | | 3 | 103 | gjgdyj | brb | | 4 | 104 | gzfug | p | | 5 | 105 | keitu | cib | | 6 | 106 | nndvuv | csue | | 7 | 107 | cdudl | tw | | 8 | 108 | aafyea | aqq | | 9 | 109 | zuqezjx | dpqoyo | | 10 | 110 | pam | cses | +----+--------+---------+--------+ 10 rows in set (0.00 sec) mysql> select * from emp limit 10; #查看前10条数据50W太多了 +----+--------+-------+----------+-----+------------+---------+--------+--------+ | id | empno | ename | job | mgr | hiredate | sal | comm | deptno | +----+--------+-------+----------+-----+------------+---------+--------+--------+ | 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 | | 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 | | 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 | | 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 | | 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 | | 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 | | 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 | | 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 | | 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 | | 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 | +----+--------+-------+----------+-----+------------+---------+--------+--------+ 10 rows in set (0.00 sec)
6.6 Show Profile进行sql分析重中之重
Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下参数处于关闭状态并保存最近15次的运行结果
分析步骤
-
1、是否支持看看当前的mysql版本是否支持
show variables like 'profiling';
默认是关闭使用前需要开启
-
2、开启功能默认是关闭使用前需要开启
set profiling=on;
-
3、运行SQL随便运行用来测试
mysql> select * from emp group by id%10 limit 150000; mysql> select * from emp group by id%20 order by 5; -
4、查看结果
show profiles;mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 1 | 0.00204000 | show variables like 'profiling' | | 2 | 0.55134250 | select * from emp group by id%10 limit 150000 | | 3 | 0.56902000 | select * from emp group by id%20 order by 5 | +----------+------------+-----------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) -
5、诊断SQL
show profile cpu,block io for query ID号;ID号为第4步Query_ID列中数字mysql> show profile cpu,block io for query 3; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000049 | 0.000000 | 0.000000 | NULL | NULL | | checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | Opening tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL | | init | 0.000021 | 0.000000 | 0.000000 | NULL | NULL | | System lock | 0.000009 | 0.000000 | 0.000000 | NULL | NULL | | optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | statistics | 0.000017 | 0.000000 | 0.000000 | NULL | NULL | | preparing | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | | Creating tmp table | 0.000045 | 0.000000 | 0.000000 | NULL | NULL | | Sorting result | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | executing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | Sending data | 0.568704 | 0.546875 | 0.046875 | NULL | NULL | | Creating sort index | 0.000048 | 0.000000 | 0.000000 | NULL | NULL | | end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | removing tmp table | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | closing tables | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | freeing items | 0.000061 | 0.000000 | 0.000000 | NULL | NULL | | cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | +----------------------+----------+----------+------------+--------------+---------------+ 20 rows in set, 1 warning (0.00 sec)参数备注写在代码中
show profile cpu,block io for query 3;如此代码中的cpu,block- ALL显示所有的开销信息。
- BLOCK IO显示块lO相关开销。
- CONTEXT SWITCHES 上下文切换相关开销。
- CPU显示CPU相关开销信息。
- IPC显示发送和接收相关开销信息。
- MEMORY显示内存相关开销信息。
- PAGE FAULTS显示页面错误相关开销信息。
- SOURCE显示和Source_functionSource_fileSource_line相关的开销信息。
- SWAPS显示交换次数相关开销的信息。
-
6、
日常开发需要注意的结论Status列中的出现此四个问题严重- converting HEAP to MyISAM查询结果太大内存都不够用了往磁盘上搬了。
- Creating tmp table创建临时表拷贝数据到临时表用完再删除
- Copying to tmp table on disk把内存中临时表复制到磁盘危险!
- locked锁了
6.7 全局查询日志
永远不要在生产环境开启这个功能只能在测试环境使用
-
第一种配置文件启用。在mysq l的 my.cnf 中设置如下
#开启 general_log=1 #记录日志文件的路径 general_log_file=/path/logfile #输出格式 log_output=FILE -
第二种编码启用。命令如下
set global general_log=1;set global log_output='TABLE';
此后你所编写的sql语句将会记录到mysql库里的geneial_log表可以用下面的命令查看
mysql> select * from mysql.general_log;
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
| 2021-04-05 19:57:28.182473 | root[root] @ localhost [::1] | 5 | 1 | Query | select * from mysql.general_log |
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
1 row in set (0.00 sec)
7 MySQL锁机制
7.1 概述
定义
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中除传统的计算资源如CPU、RAM、I/O等的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂
例子京东购物
打个比方我们到京东上买一件商品商品只有一件库存这个时候如果还有另一个人买那么如何解决是你买到还是另一个人买到的问题
这里肯定要用到事务我们先从库存表中取出物品数量然后插入订单付款后插入付款表信息然后更新商品数量。在这个过程中使用锁可以对有限的资源进行保护解决隔离和并发的矛盾
锁的分类
-
从对数据操作的类型读\写分
- 读锁共享锁针对同一份数据多个读操作可以同时进行而不会互相影响。
- 写锁排它锁当前写操作没有完成前它会阻断其他写锁和读锁。
-
从对数据操作的粒度分
- 表锁
- 行锁
7.2 表锁偏读
特点偏向MyISAM存储引擎开销小加锁快无死锁锁定粒度大发生锁冲突的概率最高并发度最低。
读锁案例讲解1
案例分析
建表表
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) 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');
#查询
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
手动增加表锁lock table 表名字 read(write), 表名字2 read(write), 其他;
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)
查看表上加过的锁show open tables;
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| performance_schema | replication_connection_status | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| my | test03 | 0 | 0 |
| bigdata | mylock | 1 | 0 |
# In_use为1时表示已上锁
释放锁unlock tables;
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 再次查看
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| performance_schema | replication_connection_status | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| my | test03 | 0 | 0 |
| bigdata | mylock | 0 | 0 |
加读锁——为mylock表加read锁读阻塞写例子
读锁案例讲解2
为mylock表加write锁MylSAM存储引擎的写阻塞读例子
MyISAM在执行查询语句SELECT前会自动给涉及的所有表加读锁在执行增删改操作前会自动给涉及的表加写锁。
MySQL的表级锁有两种模式
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)
结合上表所以对MyISAM表进行操作会有以下情况
-
对MyISAM表的读操作加读锁不会阻塞其他进程对同一表的读请求但会阻塞对同一表的写请求。只有当读锁释放后才会执行其它进程的写操作。
-
对MyISAM表的写操作〈加写锁会阻塞其他进程对同一表的读和写操作只有当写锁释放后才会执行其它进程的读写操作。
重点简而言之就是读锁会阻塞写但是不会堵塞读。而写锁则会把读和写都堵塞
表锁总结
看看哪些表被加锁了show open tables;
如何分析表锁定
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 170 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
这里有两个状态变量记录MySQL内部表级锁定的情况两个变量说明如下
- Table_locks_immediate产生表级锁定的次数表示可以立即获取锁的查询次数每立即获取锁值加1 ;
Table_locks_waited重点出现表级锁定争用而发生等待的次数(不能立即获取锁的次数每等待一次锁值加1)此值高则说明存在着较严重的表级锁争用情况
此外MyISAM的读写锁调度是写优先这也是MyISAM不适合做写为主表的引擎。因为写锁后其他线程不能做任何操作大量的更新会使查询很难得到锁从而造成永远阻塞
7.3 行锁偏写
偏向InnoDB存储引擎开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度也最高。
InnoDB与MyISAM的最大不同有两点一是支持事务(TRANSACTION)二是采用了行级锁
由于行锁支持事务复习老知识
- 事务(Transaction及其ACID属性
- 并发事务处理带来的问题
- 事务隔离级别
1事务是由一组SQL语句组成的逻辑处理单元事务具有以下4个属性通常简称为事务的ACID属性
- 原子性(Atomicity事务是一个原子操作单元其对数据的修改要么全都执行要么全都不执行。
- 一致性Consistent在事务开始和完成时数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改以保持数据的完整性;事务结束时所有的内部数据结构〈如B树索引或双向链表也都必须是正确的。
- 隔离性lsolation数据库系统提供一定的隔离机制保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的反之亦然。
- 持久性Durable事务完成之后它对于数据的修改是永久性的即使出现系统故障也能够保持。
2并发事务处理带来的问题
-
更新丢失(Lost Update)
当两个或多个事务选择同一行然后基于最初选定的值更新该行时由于每个事务都不知道其他事务的存在就会发生丢失更新问题――
最后的更新覆盖了由其他事务所做的更新。例如两个程序员修改同一java文件。每程序员独立地更改其副本然后保存更改后的副本这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前另一个程序员不能访问同一文件则可避免此问题。
-
脏读(Dirty Reads)
一个事务正在对一条记录做修改在这个事务完成并提交前这条记录的数据就处于不一致状态这时另一个事务也来读取同一条记录如果不加控制第二个事务读取了这些“脏”数据并据此做进一步的处理就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话事务A读取到了事务B
已修改但尚未提交的的数据还在这个数据基础上做了操作。此时如果B事务回滚A读取的数据无效不符合一致性要求 -
不可重复读(Non-Repeatable Reads)
一个事务在读取某些数据后的某个时间再次读取以前读过的数据却发现其读出的数据已经发生了改变、或某些记录已经被删除了这种现象就叫做“不可重复读”。
一句话
事务A读取到了事务B已经提交的修改数据不符合隔离性。 -
幻读(Phantom Reads)
一个事务接相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足其查询条件的新数据这种现象就称为“幻读“。
一句话
事务A读取到了事务B体提交的新增数据不符合隔离性多说一句幻读和脏读有点类似。脏读是事务B里面修改了数据幻读是事务B里面新增了数据。
3事务隔离级别
”脏读”、“不可重复读”和“幻读”其实都是数据库读一致性问题必须由数据库提供一定的事务隔离机制来解决
数据库的事务隔离越严格并发副作用越小但付出的代价也就越大因为事务隔离实质上就是使事务在一定程度上“串行化”进行这显然与“并发”是矛盾的。同时不同的应用对读一致性和事务隔离程度的要求也是不同的比如许多应用对“不可重复读”和“幻读”并不敏感可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别show variables like 'tx_isolation';
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)
# 默认情况下MySQL避免了脏读和不可重复读
行锁案例讲解
建表
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
//查看
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql> show index from test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 8 | NULL | NULL | YES | BTREE | | |
| test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
行锁定基本演示两个客户端更新同一行记录
疑惑解答为什么两个都要commint
索引失效行锁变表锁
无索引行锁升级为表锁
间隙锁
什么是间隙锁
当我们用范围条件而不是相等条件检索数据并请求共享或排他锁时InnoDB会给符合条件的已有数据记录的索引项加锁对于键值在条件范围内但并不存在的记录叫做“间隙GAP”。
InnoDB也会对这个“间隙”加锁这种锁机制就是所谓的间隙锁Next-Key锁。
危害
因为Query执行过程中通过过范围查找的话他会锁定整个范围内所有的索引键值即使这个键值并不存在。
间隙锁有一个比较致命的弱点就是当锁定一个范围键值之后即使某些不存在的键值也会被无辜的锁定而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
面试题如何锁定一行
begin中间写自己的操作commit
行锁总结
总结
Innodb存储引擎由于实现了行级锁定虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候Innodb的整体性能和MylISAM相比就会有比较明显的优势了。
但是Innodb的行级锁定同样也有其脆弱的一面当我们使用不当的时候可能会让Innodb的整体性能表现不仅不能比MyISAM高甚至可能会更差
如何分析行锁定?
通过检查lnnoDB_row_lock状态变量来分析系统上的行锁的争夺情况show status like 'innodb_row_lock%';
mysql> 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 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
对各个状态量的说明如下:
- Innodb_row_lock_current_waits当前正在等待锁定的数量
- Innodb_row_lock_time从系统启动到现在锁定总时间长度
- Innodb_row_lock_time_avg每次等待所花平均时间
- Innodb_row_lock_time_max从系统启动到现在等待最常的一次所花的时间
- Innodb_row_lock_waits系统启动后到现在总共等待的次数
对于这5个状态变量比较重要的主要是
lnnodb_row_lock_time等待总时长Innodb_row_lock_time_avg等待平均时长lnnodb_row_lock_waits等待总次数
尤其是当等待次数很高而且每次等待时长也不小的时候我们就需要分析Show Profile系统中为什么会有如此多的等待然后根据分析结果着手指定优化计划。
优化建议
- 尽可能让所有数据检索都通过索引来完成避免无索引行锁升级为表锁。
- 合理设计索引尽量缩小锁的范围
- 尽可能较少检索条件避免间隙锁
- 尽量控制事务大小减少锁定资源量和时间长度
- 尽可能低级别事务隔离
页锁
开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间并发度一般。了解一下即可
8 主从复制
8.1 复制的基本原理
slave会从master读取binlog来进行数据同步
原理图
MySQL复制过程分成三步
- 1、master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件binary log events;
- 2、slave将master的binary log events拷贝到它的中继日志(relay log) ;
- 3、slave重做中继日志中的事件将改变应用到自己的数据库中。MySQL复制是异步的且串行化的
8.2 复制的基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个salve
复制的最大问题是延迟。
8.3 一主一从常见配置
一、mysql版本一致且后台以服务运行
二、主从都配置在[mysqld]结点下都是小写
主机修改my.ini配置文件
1、[必须]主服务器唯一IDserver-id=1
2、[必须]启用二进制日志
- log-bin=自己本地的路径/mysqlbin
- log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
3、[可选]启用错误日志
- log-err=自己本地的路径/mysqlerr
- log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
4、[可选]根目录
- basedir=“自己本地路径”
- basedir=“D:/devSoft/MySQLServer5.5/”
5、[可选]临时目录
- tmpdir=“自己本地路径”
- tmpdir=“D:/devSoft/MySQLServer5.5/”
6、[可选]数据目录
- datadir=“自己本地路径/Data/”
- datadir=“D:/devSoft/MySQLServer5.5/Data/”
7、主机读写都可以
- read-only=O
8、[可选]设置不要复制的数据库
- binlog-ignore-db=mysql
9、[可选]设置需要复制的数据库
- binlog-do-db=需要复制的主数据库名字
从机修改my.cnf配置文件
1、[必须]从服务器唯一IDvim etc/my.cnf进入修改配置文件
...
#server-id=1 //注释吊
...
server-id=1 //开启
...
2、[可选]启用二进制日志
三、配置文件请主机+从机都重启后台mysql服务
主机手动重启
Linux从机命名
- service mysql stop
- service mysql start
四、主机从机都关闭防火墙
windows手动关闭
关闭虚拟机linux防火墙 service iptables stop
五、在Windows主机上建立帐户并授权slave
- GRANT REPLICATION SLAVE ON . TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘123456’;
- 刷新
flush privileges; - 查询master的状态
- show master status;
- 记录下File和Position的值
- 执行完此步骤后不要再操作主服务器MYSQL防止主服务器状态值变化
六、在Linux从机上配置需要复制的主机
-
CHANGE MASTER TO MASTER_HOST=’主机IP’,
MASTER_USER=‘zhangsan’,
MASTER_PASSWORD=’123456’,
MASTER_LOG_FILE='File名字’,
MASTER_LOG_POS=Position数字; -
启动从服务器复制功能
start slave; -
show slave status\G下面两个参数都是Yes则说明主从配置成功!
- Slave_IO_RunningYes
- Slave_SQL_RunningYes
七、主机新建库、新建表、insert记录从机复制
- 主机操作
- 从机自动同步
八、如何停止从服务复制功能stop slave;
如果有一段数据暂时不要
从机
主机需要重新查刻度
