centos7安装mysql
https://www.cnblogs.com/starof/p/4680083.html
1. mysql的架构介绍
1.1. mysql 简介
1.2. 高级Mysql
- 完整的mysql优化需要很深的功底，大公司甚至有专门的DBA写上述
- mysql内核
- sql优化工程师
- mysql服务器的优化
- 各种参数常量设定
- 查询语句优化
- 主从复制
- 软硬件升级
- 容灾备份
- sql编程
1.3. mysqlLinux版的安装
mysql5.5
- 下载地：https://dev.mysql.com/downloads/mysql/5.5.html#downloads
- 检查当前系统是否安装过mysql：
- 查询命令：rpm -qa|grep -i mysql
- 删除命令：rpm -e RPM软件包名称
- 删除自带的mysql：yum -y remove mysql-libs-5.1.73-7.el6.x86_64
安装mysql服务端（注意提示）：
- rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm
- 如果报错libc.so.6：https://blog.csdn.net/xiyuliuyang/article/details/90750049
- 如果警告key ID 5072e1f5: NOKEY：https://blog.csdn.net/Aaron960214/article/details/78451321
- rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm
安装mysql客户端
- rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm
查看MySQL安装时创建的mysql用户和mysql组
- cat /etc/passwd|grep mysql
- cat /etc/group|grep mysql
- mysqladmin —version
mysql服务的启+停
- service mysql start
service mysql start
- 如果报错ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).
- 解决办法：https://www.cnblogs.com/bingco/p/8068243.html
mysql_install_db --datadir=/var/lib/mysqlchown mysql:mysql /var/lib/mysql -R
查看mysql的进程：ps -ef|grep mysql
- mysql服务启动后，开始连接
- 首次成功：不需要输入密码
- 给root用户设置密码：/usr/bin/mysqladmin -u root password 123456
- 首次成功：不需要输入密码
自启动mysql服务
- 设置开启自启动 ：chkconfig mysql on
- 查看mysql的等级：chkconfig —list | grep mysql
- 查看不同等级代表的含义：cat /etc/inittab
- 查看开机自动服务有哪些：ntsysv
- 设置开启自启动 ：chkconfig mysql on
修改配置文件的位置
- 版本5.5：cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
- 版本5.6：cp /usr/share/mysql/my-default.cnf /etc/my.cnf
- 修改字符集和数据存储路径
- 查看字符集（由于默认客户端和服务器都是用latin1，所以都是乱码）
- show variables like ‘character%’;
- show variables like ‘%char%’;
- 重启mysql
- 重新连接后，原来的库由于建立于修改字符集之前，所以中文依然是乱码，而新建表中文不是乱码 ```sql [client] port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set=utf8
- 查看字符集（由于默认客户端和服务器都是用latin1，所以都是乱码）
[mysqld] character_set_server = utf8 character_set_client = utf8 collation-server = utf8_general_ci
[mysql] no-auto-rehash default-character-set=utf8
将这段话注释掉，否则将会报错
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
- **MySQL的安装位置**- /var/lib/mysql：mysql数据库文件的存放路径- /usr/share/mysql：配置文件目录- /usr/bin：相关命令目录- /etc/init.d/mysql：启停相关脚本<a name="WMe4E"></a>## 1.4 Mysql 配置文件<a name="yjc6q"></a>### 1、二进制日志 log-bin ：- 主从复制<a name="OYow7"></a>### 2、错误日志 log-error：- 默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等<a name="KD5ev"></a>### 3、查询日志log- 默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的。<a name="UY7HZ"></a>### 4、数据文件- 两系统- windows： D:\devSoft\MySQLServer5.5\data目录下可以挑选很多库- linux- 看看当前系统中的全部库后再进去- 默认路径：/var/lib/mysql- frm 文件：存放表结构- myd 文件：存放表数据- myi 文件： 存放表索引<a name="N92Xk"></a>### 5、如何配置：- windows：my.ini文件- Linux：/etc/my.cnf文件<a name="lD4ZG"></a>## 1.5 mysql逻辑架构介绍<a name="MNU1c"></a>### 1.5.1 架构简介和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。**这种架构可以根据业务的需求和时机需要选择合适的存储引擎<br />从上到下，连接层，服务层，引擎层，存储层<br /><a name="NFVn4"></a>### 1.5.2 mysql存储引擎**1、查看命令**- 如何用命令查看- 看你的mysql现在已提供什么存储引擎：**show engines;**- 看你的mysql当前默认的存储引擎：**show variables like '%storage_engine%'；****<br />**2、MyISAM和InnoDB**<br />** **<br />**3、阿里巴巴、淘宝用哪个**<br />****<a name="7lup1"></a># 2. 索引优化分析<a name="Uk96H"></a>## 2.1性能下降SQL慢执行时间长、等待时间长<br />**1、查询语句写的烂**<br />**2、索引失效**```sqlid name email numberselect * from user where name = '' and email = '';# 创建索引 给字段进行底层 单值查询create index idx_user_name on user(name);# 复合查询create index idx_user_nameEmail on user(name,email);
3、关联查询太多 join （设计缺陷或不得已的需求）
4、服务器调优及各个参数设置（缓冲、线程数等）
2.2 常见通用的Join查询
1、SQL 执行顺序
- 手写
- 机读
- 总结
2、Join图-7种JOIN
# 内连接select * from TableA A inner join TableB B on A.key = B.key# 左连接 （左表的全部）select * from TableA A left join TableB B on A.key = B.key# 右连接select * from TableA A right join TableB B on A.key = B.key# 左内连接select * from TableA A left join TableB B on A.key = B.key where B.key is null# 右内连接select * from TableA A right join TableB B on A.key = B.key where A.key is null# 全连接select * from TableA A full outer join TableB B on A.key=B.key#select * from TableA A full outer join TableB B on A.key=B.key where A.key is null and B.key is null
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`))ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;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 CHARACTER SET = 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',5);# 查询emp表select * from tbl_emp;# 查询dept表select * from tbl_dept;# 双表查询select * from tbl_emp,ebl_dept;
# 只要共有部分 内连接select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
# 左表全部 左连接select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
# 右连接 右表全部 左表补nullselect * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
# 左表独有的数据select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
# 右表独有的select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptid is null;
# 两表所有mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id-> union-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
# 左表独有 和 右表独有mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is nullunionselect * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
2.3 索引简介
2.3.1 是什么
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
索引的目的在于提高查找效率，可以类比字典。
如果要查找“mysql”，我们肯定需要定位到 m 字母，然后从上往下找 y 字母，找找剩下的 sql。
如果没有索引，那么你可能需要 a—-z ，如果我们想查找别的单词呢？
是不是觉得如果没有索引，这个事情根本无法完成。
你可以简单理解为“**排好序的 快速查找** 的 数据结构”。
详解（B树）
结论:
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。**其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认的都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。
2.3.2 优势
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
2.3.3 劣势
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询。
2.3.4 mysql 索引分类
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：一个索引包含多个列
- 基本语法
- 创建：create [unique] index indexName on table(cloumnName(length));
- alter table add [unique] index indexName on (columnName(length));
- 如果是char，varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。
- alter table add [unique] index indexName on (columnName(length));
- 删除：drop index indexName on table;
- 查看：show index from table_name
- 创建：create [unique] index indexName on table(cloumnName(length));
使用四种方式来添加数据表的索引：**
alter table tbl_name add primary key (column_list);添加一个主键，这意味着索引值必须是唯一的，不能为nullalter table tbl_name add unique index_name(column_list);创建索引，值必须唯一（除了null之外，null可能会出现多次）alter table tbl_name add index index_name(column_list);添加普通索引，索引值可能出现多次alter table tbl_name add fulltext index_name(column_list);指定了索引为fulltext，用于全文索引
2.3.5 mysql 索引结构
2.3.5.1 BTree 索引
2.3.5.2 Hash索引
2.3.5.3 full-text全文索引
2.3.5.4 R-Tree索引
2.3.6 哪些情况需要索引
- 主键自动创建唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系创建索引
- 频繁更新的字段不适合创建索引——-因为每次更新不只是更新了记录还会更新索引
- where 条件里不到的字段不创建索引
- 单值/组合索引的选择问题，who？（在高并发下倾向常见组合索引）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
-
2.3.6 哪些情况不需要索引
表记录太少
- 经常增删改的表
- Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
2.4.5 Explain
2.4.5.1 是什么（查询执行计划）
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
2.4.5.2 能干嘛
表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的应用
-
2.4.5.3 怎么玩
Explain+SQL语句
执行计划包含的信息
select_type：
- 有哪些
- simple
- primary
- subquery
- derived
- union
- union result
- 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
- PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。最后查询的
- SUBQUERY：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
- UNION RESULT：从UNION表中获取结果的SELECT。
table：显示这一行的数据是关于哪些表的。
type：all index range ref eq_ref const,system null
- 访问类型排列
- type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All
- 显示查询使用了哪一种类型，从最好到最差依次是：
- system>const>eq_ref>ref>range>index>all
- system: 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
- const： 表示通过索引一次就找简历，const用于比较primary key 或者 unique 索引，因为只匹配一行数据，所以很快，比如将主键置于where 列表中，mysql就能将查询转换成一个常量
- eq_ref: 唯一检索扫描，对于每一个检索值，表中只有一条数据与之匹配。常见与主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引。
- index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
- all：Full Table Scan，将遍历全表以找到匹配的行。
- 一般来说，得保证查询至少达到range级别，最好能达到ref。
- system>const>eq_ref>ref>range>index>all
possible_keys：**
- 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。
key：
- 实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）
key_len：**
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref**：
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。
rows：**
- 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
Extra：**包含不适合在其他列中显示但十分重要的额外信息。
Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
覆盖索引：
Using where：表明使用了where过滤。
Using join buffer：使用了连接缓存。
impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。
2.4.5.5 热身 case
2.5 索引优化
2.5.1 索引分析
- 单表
建表SQL
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);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');SELECT * FROM ARTICLE;
案例
2.两表
建表SQL
CREATE TABLE IF NOT EXISTS `class`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL);CREATE TABLE IF NOT EXISTS `book`(`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL);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)));
案例：
- 三表
建表SQL
CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL)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)));
案例：
- 总结：Join语句的优化
- 尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
- 优先优化NestedLoop的内层循环。
- 保证Join语句中被驱动表上Join条件字段已经被索引。
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。
2.5.2 索引失效（应该避免）
建表SQL
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`)
案例：
- 全值匹配我最爱
- 最佳左前缀法则：
- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断哈哈哈）
- 不在索引列上作任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 中间 兄弟别搞范围，要搞等值
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
- 按需取数据，用多少取多少，尽量与索引一致
- Extra中出现了using index很好！
- mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
- is null，is not null也无法使用索引
- like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
- like%加右边 ——- july%
- 问题：解决like ‘%字符串%’时索引不被使用的方法？
``sql CREATE TABLEtbl_user(idINT(11) NOT NULL AUTO_INCREMENT,nameVARCHAR(20) DEFAULT NULL,ageINT(11) DEFAULT NULL,emailVARCHAR(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’);
- **利用覆盖索引解决两边%的优化问题。**<br />- 字符串不加单引号索引失效- - 该问题同问题3，是索引列上做了类型转换！- **VARCHAR类型绝对不能失去单引号**- 少用or，用它来连接时会索引失效- - **小总结**- ****```markdown优化总结口诀：全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；LIKE百分写最右，覆盖索引不写星；不等空值还有or，索引失效要少用；VAR引号不可丢，SQL高级也不难！
- 面试题讲解
- sql ```sql 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’);
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
问题：我们创建了复合索引 idx_test03_c1234 根据以下SQL分析索引使用情况？- **explain select * from test03 where c1='a1';**- **explain select * from test03 where c1='a1' and c2='a2' ;**- **explain select * from test03 where c1='a1' and c2='a2' and c3='a3';**- **explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';****<br />**(1) explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';**<br />**(2) explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3;**<br />****<br />**(3) explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';**<br />****<br />**(4) ** **explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';**<br />**底层先进行排序**<br />****<br />**（5）explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;**<br />**c3 作用在排序不在查找**<br />****<br />**（6） explain select * from test03 where c1='a1' and c2='a2' order by c3;**<br />****<br />**（7）explain select * from test03 where c1='a1' and c2='a2' order by c4;**<br />****<br />**（8.1）explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;**<br />**只用到了c1，但是 c2 c3 用于排序**<br />****<br />**（8.2）explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;**<br />**出现了filesort 我们建立的索引是12134，他没有按照顺序来，23颠倒了。**<br />****<br />**（9） explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;**<br />****<br />**（10） explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;**<br />**c1c2 用于索引查找，c2 c3用于排序**<br />****<br />**（11）explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;**<br />**c2 已经查找出来（常量）**<br />****<br />**（12）explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;**<br />****- 定值、范围还是排序，一般order by是给个范围<br />- group by基本上都需要进行排序，会有临时表产生<br /><a name="DlSXM"></a>###<a name="r7UTP"></a>### 2.5.3 一般性建议- 对于单键索引，尽量选择针对当前query过滤性更好的索引- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的<a name="Cjok3"></a># 3.查询截取分析- 分析- 1、观察，至少跑一天，看看生产的慢SQL情况- 2、开启慢查询日志，设置阈值，比如超过5s，就是慢SQL，并抓取出来- 3、explain + 慢SQL分析- 4、show profile- 5、运维经理 DBA 进行SQL数据库服务器的参数调优- 总结- 1、慢查询的开启并捕获。- 2、explain + 慢SQL分析。- 3、show profile 查询SQL在MySQL服务器里面的执行细节和生命周期- 4、SQL数据库服务器的参数调优。<a name="4cJr7"></a>## 3.1 查询优化<a name="KGUnE"></a>### 3.1.1 永远小表驱动大表，类似嵌套循环Nested Loop- - 优化原则：小表驱动大表，即小的数据集驱动大的数据集。- - 当B表的数据集必须小于A表的数据集时，用in优于exists<br />- 当A表的数据集必须小于B表的数据集时，用exists优于in<br />- 注意：A表与B表的ID字段应建立索引。<br />- EXISTS- SELECT … FROM table WHERE EXISTS(subquery)- 该语法可以理解为：**将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。**- 提示- EXISTS（subquery）只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或SELECT ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。- EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。- EXISTS子查询往往也可以用条件表达式/其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。- <a name="Gr2Ou"></a>### 3.1.2 order by 关键字优化1、ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序<br />建表```sqlcreate 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);select * from tblA;
CASE ：
case1：
case2：
- MySQL支持两种方式的排序
- FileSort和Index，Index效率高。FileSort方式效率较低。
- Using Index，它指MySQL扫描索引本身完成排序。
- ORDER BY满足两种情况，会使用Index方式排序：
- ORDER BY语句使用索引最左前列
- 使用Where子句与ORDER BY子句条件列组合满足索引最左前列
2、尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀，如果不在索引列上，filesort有两种算法：
- mysql就要启动双路排序和单路排序
- 双路排序
- MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。
- 从磁盘取排序字段，在buffer进行排序，再从磁盘读取其他字段。
- 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
- 单路排序
- 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。
- 双路排序
3、优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
- Why
3.1.3 group 关键字优化
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
- 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
where高于having，能写在where限定的条件就不要去having限定了。
3.2 慢查询日志
1、是什么
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- long_query_time的默认值是10，意思是运行10秒以上的语句。
- 由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的explain进行全面分析。
2、怎么玩？
- 说明
- 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
- 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看是否开启及如何开启
- 默认：SHOW VARIABLES LIKE ‘%slow_query_log%’;
- 开启：
- 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？
Case
- 查看当前多少秒算慢：SHOW VARIABLES LIKE ‘long_query_time%’;
- 设置慢的阈值时间：set global long_query_time=3;
为什么设置后看不出变化（设置3之后，查询依然显示10）：
需要重新连接或新开一个会话才能看到修改值。
- SHOW VARIABLES LIEK ‘long_query_time%’;
- show global variables like ‘long_query_time’;
- 记录慢SQL并后续分析
- 查询当前系统中有多少条慢查询记录：
show global status like ‘%Slow_queries%’;
- 配置版
- 默认：SHOW VARIABLES LIKE ‘%slow_query_log%’;
- 日志分析工具mysqldumpslow
- 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
- 查看mysqldumpslow的帮助信息
- mysqldumpslow —help
- s：是表示按照何种方式排序
- c：访问次数
- I：锁定时间
- r：返回记录
- t：查询时间
- al：平均锁定时间
- ar：平均返回记录数
- at：平均查询时间
- t：即为返回前面多少条的数据
- g：后边搭配一个正则匹配模式，大小写不敏感
- 工作常用参考
- 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
3.3批量数据脚本
往表里插入1000w数据
1、建表
create database bigData;use bigData;CREATE TABLE `dept` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',`dname` varchar(20) NOT NULL DEFAULT '',`loc` varchar(13) NOT NULL DEFAULT '',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=GBK;CREATE TABLE `emp` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`empno` mediumint(8) unsigned NOT NULL DEFAULT '0',/*编号*/`ename` varchar(20) NOT NULL DEFAULT '',/*名字*/`job` varchar(9) NOT NULL DEFAULT '',/*工作*/`mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',/*上级编号*/`hiredate` date NOT NULL,/*入职时间*/`sal` decimal(7,2) NOT NULL,/*薪水*/`comm` decimal(7,2) NOT NULL,/*红利*/`deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',/*部门编号*/PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=GBK;
2、设置参数 log_bin_trust_funcation_creators
3、创建函数，保证每条数据都不同
随机产生字符串
随机产生部门编号
delimiter $$CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)BEGINdeclare char_str VARCHAR(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';declare return_str VARCHAR(255) default '';declare i INT default 0;WHILE i < n DOSET return_str = CONCAT(return_str,SUBSTRING(char_str,FLOOR(1+RAND()*52),1));SET i = i+1;END WHILE;RETURN return_str;END $$DELIMITER $$CREATE FUNCTION rand_num() RETURNS INT(5)BEGINDECLARE 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))BEGINdeclare i INT DEFAULT 0;SET autocommit = 0;#把自动提交关闭repeatSET 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_numEND repeat;COMMIT;END $$创建往dept表中插入数据的存储过程delimiter $$CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO dept (deptno,dname,loc) VALUES((START+i),rand_string(10), rand_string(8));UNTIL i = max_numEND REPEAT;COMMIT;END $$
3.4 Show Profile
- 是什么：是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
- 官网：http://dev.mysql.com.doc/refman/5.5/en/show-profile.html
- 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤：
- 是否支持，看看当前的mysql版本是否支持
- show variables like ‘profiling’;
- 开启功能，默认是关闭，使用前需要开启
- set profiling = on;
- 运行SQL
- select * from emp group by id%10 limit 150000;
- select * from emp group by id%20 order by 5;
- 查看结果，show profiles;
- 诊断SQL，show profile cpu, block io for query [上一步前面的问题SQL数字号码];
- 参数备注：
- 日常开发需要注意的结论：
- 是否支持，看看当前的mysql版本是否支持
从对数据操作的类型（读/写）分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的粒度分
开销、加锁速度、死锁、粒度、并发性能
- 只能就具体应用的特点来说那种锁更合适
4.2.1 表锁（偏读）
（1）特点：
偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
（2）案例分析：
1、建表SQL ```sql 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;
2、手动增加表锁- lock table 表名字 read(write), 表名字2 read(write), 其他;3、查看表上加过的锁- show open tables;4、释放表锁- unlock tables;5、加读锁（我们为mylock表加read锁（读阻塞写例子））<br /><br />6、加写锁（我们为mylock表加write锁（MyISAM存储引擎的写阻塞读例子））<br /><br /><br />7、案例结论<br />**简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。**<br />****<br />8、表锁分析- 看看哪些表被加锁了：show open tables;- 如何分析表锁定：可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。- show status like ‘table%’;- 这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量的说明如下：- Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；- Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。- **此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。**<a name="57Ltq"></a>### 4.2.2 行锁<a name="uONK0"></a>#### （1）、特点- 偏向Innodb存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。- Innodb与MyISAM的最大不同有两点：- 一是支持事务（TRANSACTION）- 而是采用了行级锁<a name="XB7UR"></a>#### （2）、由于行锁支持事务，复习老知识1、事务（transcation）及其 ACID 属性<br />事务是由一组SQL语句组成的逻辑处理单元，事务具有一下四个属性，通常简称为事务的ACID属性- 原子性：事务是一个原子操作单位，对数据的修改，要么全都执行，要么全都不执行- 一致性：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用与事务的修改，以保持数据的完整性；事物结束时，所有的内部数据结构（如B树所有或双向链表）也都必须是正确的。- 隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行，这意味着事务处理过程的中间状态对外部是不可见的，反之亦然。- 持久性：事务完成之后，他对数据的修改是永久性的，及时出现系统故障也能够保持2、并发事务处理带来的问题：- 更新丢失- 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。- 例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。- 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。- 脏读- 一个事务正在对 1 条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态;这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。- 一句话:事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。- 不可重复度- 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被刪除了!这种现象就叫做“不可重复读”。- 一句话:事务A读取到了事务B已经提交的修改数据，不符合隔离性- 幻读- 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”- 一句话:事务A读取到了事务B体提交的新增数据，不符合隔离性。- 多说一句:幻读和脏读有点类似，- 脏读是事务B里面修改了数据，- 幻读是事务B里面新增了数据。3、事务隔离级别<br /><a name="S0mc2"></a>#### （3）案例分析1、建表SQL```sqlcreate 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);select * from test_innodb_lock;
2、行锁定基本演示
3、无索引行锁升级为表锁
如果在更新数据的时候出现了强制类型转换导致索引失效，使得行锁变表锁，即在操作不同行的时候，会出现阻塞的现象。
4、间隙锁危害
- 什么是间隙锁：当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
- 危害：
- 因为Query执行过程中通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在。
- 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
5、面试题：常考如何锁定一行
select * from 表 where 某一行的条件 for update;
6、案例结论
- InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
- 但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
7、行锁分析
如何分析行锁定
- 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况- show status like ‘innodb_row_lock%’;- - 对各个状态量的说明如下：- Innodb_row_lock_current_waits：当前正在等待锁定的数量；- innodb_row_lock_time：从系统启动到现在锁定总时间长度；- innodb_row_lock_time_avg：每次等待所花平均时间；- innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；- innodb_row_lock_waits：系统启动后到现在总共等待的次数。- 对于这5个变量，比较重要的是- innodb_row_lock_time_avg（等待平均时长）- innodb_row_lock_waits（等待总次数）- innodb_row_lock_time（等待总时长）- 这三项- 尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
（4）优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量缩小锁的范围。
- 尽可能减少索引条件，避免间隙锁。
- 尽量控制事务大小，减少锁定资源量和时间长度。
-
4.3 页锁
开销和加锁时间介于表锁和行锁之间。
- 会出现死锁。
- 锁定粒度介于表锁和行锁之间。
-
5. 主从复制
5.1 复制的基本原理
slave会从master读取binlog来进行数据同步
- 三步骤+原理图
-
5.2 复制的基本原则
每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
-
5.3 复制的最大问题
-
5.4 一主一从常见配置
mysql版本一致且后台以服务运行
- 主从都配置【mysqld】结点下，都是小写
- 主机修改my.ini配置文件
- 1、[必须] 主服务器唯一 ID：server-id=1
- 2、[必须] 启用二进制文件
- log-bin=自己本地的路径/mysqlbin
- log-bin=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysqlbin
- log-bin=自己本地的路径/mysqlbin
- 3、启用错误日志
- log-err=自己本地的路径/mysqlerr
- log-err=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysqlerr
- log-err=自己本地的路径/mysqlerr
- 4、根目录
- basedir=”自己的本地路径”
- basedir=”C:/ProgramData/MySQL/MySQL Server 5.7/“
- basedir=”自己的本地路径”
- 5、临时目录
- tmpdir=”C:/Program Files/MySQL/MySQL Server 5.7/“
- 6、数据目录
- datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data
- 7、read-only=0
- 主机，读写都可以
- 8、设置不要复制的数据库
- binlog-ignore-db=mysql
- 9、设置需要复制的数据库
- binlog-do-db=需要复制的数据库name
- 从机修改my.cnf配置文件
- 【必须】从服务器唯一ID
- server-id=2
- 【可选】启用二进制日志
- 【必须】从服务器唯一ID
- 因修改过配置文件，请主机+从机都重启后台mysql服务
- 主机从机都关闭防火墙
- windows手动关闭
- 关闭虚拟机linux防火墙：service iptables stop
在Windows主机上建立账户并授权slave
grant replication slave on *.* to 'root'@'192.168.75.137' identified by '123456';
- flush privileges;
- 查询master的状态
- show master status
- 记录下File和Position的值
- show master status
- 执行完此步骤后不要再操作主服务器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【\G是为了以键值的形式显示，好看一些】
- 下面两个参数都是Yes，则说明主从配置成功！
- Slave_IO_Running：Yes
- Slave_SQL_Running：Yes
- 下面两个参数都是Yes，则说明主从配置成功！
- 主机新建库、新建表、insert记录，从机复制
- 如何停止从服务复制功能
- stop slave;
若有收获，就点个赞吧
0 人点赞
