[大数据]mysql高级

1.mysql逻辑架构

1.连接层

2.服务层

3.引擎层

4.存储层

2.mysql存储引擎

3.join理论

1.A+AB

select <select_list> from tableA a left [outer] join tableB b on a.key = b.key;

2.AB+B

select <select_list> from tableA a right [outer] join tableB b on a.key = b.key;

3.A-A^B

select <select_list> from tableA a left [outer] join tableB b on a.key = b.key where b.key is null;

4.B-A^B

select <select_list> from tableA a right [outer] join tableB b on a.key = b.key where a.key is null;

5.A^B

select <select_list> from tableA a inner join tableB b on a.key = b.key;

6.A并B

select <select_list> from tableA a full [outer] join tableB b on a.key = b.key;

7.A+B-A^B

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;

3.索引

1.索引基本知识

• 索引是一种排好序的快速查找数据结构
• B树索引
• 功能：
  1）查找
  2）排序

1.优势

• 提高数据的检索效率
• 降低数据的排序成本

2.劣势

• 占用空间
• 降低表更新速度

3.索引分类

• 单值索引
• 唯一索引
• 复合索引

4.索引结构

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

2.Explain

1.是什么

2.能干嘛

• 表的读取数据
• 数据读取操作的操作类型
• 那些索引可以使用
• 那些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

3.怎么用

explain select * from tableName;

4.执行计划包含的信息

• id
  1）id相同，执行顺序由上而下
  2）id不同，id大的优先级更高，先执行
  3）id相同、不同同时存在
• select_type
  1）SIMPLE：简单查询
  2）PRIMARY：最外层查询
  3）SUBQUERY：子查询
  4）DERIVED：衍生
  5）UNION：
  6）UNION RESULT：
• table
• type：访问类型
  1）ALL：全表扫描
  2）index：Full Index Scan，index与ALL区别为，index只遍历索引树
  
  3）range：只检索给定范围的行，使用一个索引来选择行
  
  
  4）ref：非唯一性索引扫描，返回匹配某个单独值的所有行
  
  
  5）eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于primary key或unique 索引扫描
  
  6）const：表示通过索引一次找到，常用于primary key和unique索引
  
  7）system：表只有一行记录，可以不考虑

以上其中访问类型，从上到下越来越优

• possible_keys
  显示可能应用在这张表中的索引，一个或多个。
  查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

• key
  实际使用的索引，如果为null，则没有使用索引
  查询中若使用了覆盖索引，则该索引仅出现在key列表中
  覆盖索引：查询字段和建的索引个数和顺序一致
  对比下图两个 sql 语句。和 key 的值：当查询具体某一字段时，且那个字段有索引时，key 值会显示为索引：
  

• key_len
  表示索引中使用的字节数，可通过该列计算出查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。
  key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算得出的，不是通过表内检索出来的

• ref
  显示索引的那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引上的值

• rows
  根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数

• Extra
  包含不适合在其他列中显示但十分重要的额外信息
  1)Using filesort:文件内排序
  mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
  mysql中无法利用索引完成的排序操作称为“文件排序”
  出现filesort的情况：
  
  优化后，不再出现filesort的情况：(给 ename 加上了索引)
  
  2）Using temporary：
  使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
  优化前存在 using temporary 和 using filesort
  
  create index idx_deptno_ename on emp(deptno,ename) 后解决
  优化前存在的 using temporary 和 using filesort 不在，性能发生明显变化：
  
  3）Using index
  表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
  如果同时出现using where，表明索引被用来执行索引键值的查找;
  如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
  4)Using where
  表明使用了where过滤
  5)Using join buffer
  使用了连接缓存：
  6)impossible where
  where子句的值总是false，不能用来获取任何元组
  
  7）select tables optimized away
  在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
  对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
  查询执行计划生成的阶段即完成优化。
  8）distinct
  优化distinct操作，在找到第一个匹配的元组后即停止查找同样值的动作

3.索引优化

1.单表优化

####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` 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'),
(1, 1, 3, 3, '3', '3');

2.案例分析

案例：#查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。

1.很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的

2.第一次优化：

ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);
EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;

-经过第一次优化：
type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
但是我们已经建立了索引,为啥没用呢?
这是因为按照 BTree 索引的工作原理如下：
先排序 category_id,
如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
当 comments 字段在联合索引里处于中间位置时,
因comments > 1 条件是一个范围值(所谓 range),
MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

3.第二次优化：

#删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;
#第2次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);

可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
Backward index scan（反向扫描）： 8.0 版本的新特性。这里先不研究，有兴趣的同学可以看下这篇文章

2.两表优化

1.建表语句

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 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)));

2.案例分析

1.type 有All

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

2.进行第一次优化，将索引建在右表

ALTER TABLE `book` ADD INDEX Y ( `card`);

• 结论：
  可以看到第二行的 type 变为了 ref,rows 变为1，优化比较明显。
  这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
  所以右边是我们的关键点,一定需要建立索引。

3.进行第二次优化，将索引建在左表

DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

• 结论:
  左连接建在左表没有明显优化
• 由以上案例得出结论，左连接索引建在右表；右连接索引建在左表

3.三表优化

• 同两表优化

4.建议

• 保证被驱动表的join字段已经被索引
• left join 时，选择小表作为驱动表，大表作为被驱动表。
• inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
• 子查询尽量不要放在被驱动表，有可能使用不到索引。

5.索引失效

• 全值匹配我最爱
• 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列；
• 不在索引列上做任何操作（计算、函数、（自动或者手动）类型转换），否则会导致索引失效
• 存储引擎不能使用索引列中范围条件右边的列
• 尽量使用覆盖索引
• mysql在使用不等于的时候无法使用索引，会导致全表扫描
• is null,is not null也无法使用索引
• like以通配符开头（%abc…）会导致索引失效
  –通过覆盖索引解决
• 字符串不加单引号会导致索引失效：因为发生了隐式类型转换
• 少用or，用它来连接时会导致索引失效

6.建议

• 对于单键索引，尽量选择针对当前query过滤性最好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
• 在选择组合索引的时候，尽量选择可以包含当前query中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

7.永远小表驱动大表

• in
• exists
  最终选择in还是exists，只需遵循小表驱动大表原则即可

8.order by关键字优化

• 尽量使用Index方式排序,避免使用FileSort方式排序

1.建表

CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL,
  name varchar(200)
);
 
INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');

CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);
SELECT * FROM tblA; 

2.案例

• 案例一
  
• 案例二
  

3.mysql排序方式

MySQL支持二种方式的排序，FileSort和Index，Index效率高.
它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

4.ORDER BY满足两情况，会使用Index方式排序

• ORDER BY 语句使用索引最左前列
• 使用Where子句与Order BY子句条件列组合满足索引最左前列
• where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

4.总结

5.如果不在索引列上，filesort有两种算法

1.双路排序

• 读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序

2.单路排序

• 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
• 由于单路是后出的，总体而言好过双路；但是用单路会存在一些问题：
  在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

3.优化策略

• 增大sort_buffer_size参数的设置
• 增大max_length_for_sort_data参数的设置
• 去掉select 后面不需要的字段
• why？
  1） Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：
  1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
  1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
  2）尝试提高 sort_buffer_size
  不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
  3）尝试提高 max_length_for_sort_data
  提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.。

9.group by关键字优化

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

4.查询截取分析

1.慢查询日志

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
• long_query_time的默认值为10，意思是运行10秒以上的语句。
• 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

1.说明

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

2.查看是否开启及如何开启

• 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，
  可以通过设置slow_query_log的值来开启

SHOW VARIABLES LIKE '%slow_query_log%';

• 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效

set global slow_query_log=1;

• 如果要永久生效，就必须修改配置文件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/mysql/atguigu-slow.log
  关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

3.那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
命令：SHOW VARIABLES LIKE ‘long_query_time%’;
可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，
在mysql源码里是判断大于long_query_time，而非大于等于。

4.case

• 查看当前多少秒算慢

SHOW VARIABLES LIKE 'long_query_time%';

• 设置慢的阙值时间
  使用命令
  set global long_query_time=1
  修改为阙值到1秒钟的就是慢sql
  
  修改后发现long_query_time并没有改变。
• 为什么设置后看不出变化？
  需要重新连接或新开一个会话才能看到修改值。 SHOW VARIABLES LIKE ‘long_query_time%’;
  或者通过set session long_query_time=1来改变当前session变量;
• 记录慢sql

select sleep(4);
select sleep(2);

• 查询当前系统中有多少条慢sql

show global status like '%Slow_queries%';

5.日志分析工具mysqldumpslow

• 查看mysqldumpslow的帮助信息
  
• 工作常用参考
  得到返回记录集最多的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 /var/lib/mysql/atguigu-slow.log | more

6.批量数据脚本

1.建表

#1 建表dept
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 ;  
#2 建表emp
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   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 ##声明一个 字符窜长度为 100 的变量 chars_str ,默认值 
 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));
##concat 连接函数  ，substring(a,index,length) 从index处开始截取
 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))  
BEGIN
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成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(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一个循环
 END REPEAT;  ##满足条件后结束循环
 COMMIT;   ##执行完成后一起提交
 END $$

• 创建往dept表中插入数据的存储过程

#执行存储过程，往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

DELIMITER ; #将结束标志换回 ;
CALL insert_dept(100,10); 

• emp

DELIMITER ;    #将结束标志换回 ;
CALL insert_emp(100001,500000); 

7.show Profile

1.什么是show Profile

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

2.官网

官网

3.默认情况下，参数处于关闭状态，并保存最近15次的运行结果

4.分析

• 看看当前的mysql版本是否支持show Profile

show  variables like 'profiling';

• 开启功能，默认是关闭，使用前需要开启

show variables like 'profiling';
set profiling=1;

• 运行慢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 n (n为上一步前面的问题SQL数字号码);

show profile cpu,block io for query  n;  

• 参数备注
  type:
  | ALL --显示所有的开销信息
  | BLOCK IO --显示块IO相关开销
  | CONTEXT SWITCHES --上下文切换相关开销
  | CPU --显示CPU相关开销信息
  | IPC --显示发送和接收相关开销信息
  | MEMORY --显示内存相关开销信息
  | PAGE FAULTS --显示页面错误相关开销信息
  | SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
  | SWAPS --显示交换次数相关开销的信息

• 日常开发需要注意的结论
  1）converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
  2）Creating tmp table 创建临时表：拷贝数据到临时表，用完再删除
  3）Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
  4）locked

8.全局查询日志

• 配置启用
  在mysql的my.cnf中，设置如下：
  #开启
  general_log=1
  记录日志文件的路径
  general_log_file=/path/logfile
  #输出格式
  log_output=FILE
• 编码启用
  set global general_log=1;
  #全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中
  set global log_output=‘TABLE’;
  此后 ，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
  select * from mysql.general_log;
• 尽量不要在生产环境开启这个功能

5.mysql锁机制

5.1定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

5.2锁的分类

5.2.1从对数据操作的类型分

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

5.2.2从对数据操作的粒度分

??为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。
??一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

• 表锁
• 行锁

  5.3表锁（偏读）

  5.3.1特点

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

  5.3.2案例分析

  5.3.2.1建表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;
  #手动增加表锁
  lock table 表名字1 read(write)，表名字2 read(write)，其它;
  #查看表上加过的锁
  show open tables;
  #释放表锁
  unlock tables;
  

####5.3.2.2 加读锁

session_1	session_2
获得表mylock的READ锁定	连接终端
当前session可以查询该表记录	其他session也可以查询该表的记录
当前session不能查询其它没有锁定的表	其他session可以查询或者更新未锁定的表
当前session中插入或者更新锁定的表都会提示错误：	其他session插入或者更新锁定表会一直等待获得锁：
释放锁	Session2获得锁，插入操作完成：

5.3.2.3 加写锁

获得表mylock的WRITE锁定	连接终端
当前session对锁定表的查询+更新+插入操作都可以执行：	其他session对锁定表的查询被阻塞，需要等待锁被释放：在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞住。
释放锁	Session2获得锁，查询返回：

5.3.2.4 结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
结论：
结合上表，所以对MyISAM表进行操作，会有以下情况：
1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

5.3行锁（偏写）

5.3.1 建表sql

create table test_innodb_lock (
a int(11),
b varchar(16)
)engine=innodb;

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

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

select * from test_innodb_lock;

5.3.2 行锁定基本演示

Session_1	Session_2
更新但是不提交	Session_2被阻塞，只能等待
提交更新	解除阻塞，更新正常进行

5.3.3 无索引行锁升级为表锁

Session_1	Session_2
正常情况，各自锁定各自的行，互相不影响，一个2000另一个3000
由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁
比如没加单引号导致索引失效，发生自动类型转换，导致行锁变表锁	被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新

5.3.4 间隙锁危害

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

• 危害:
  ??因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
  ??间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

Session_1	Session_2
	阻塞产生，暂时不能插入
commit;	阻塞解除，完成插入

5.3.4 案例结论

??Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
??但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

5.3.5 行锁分析

• 通过检查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（等待总时长）这三项。
  尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
  最后可以通过

SELECT * FROM information_schema.INNODB_TRX;

来查询正在被锁阻塞的sql语句。

5.3.6 优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
• 涉及相同表的事务，对于调用表的顺序尽量保持一致。
• 在业务环境允许的情况下,尽可能低级别事务隔离

5.3页锁

??开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

6.主从复制

6.1复制的基本原理

• slave会从master读取binlog来进行数据同步
• 三步骤+原理图
  
  MySQL复制过程分成三步：
  1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
  2 slave将master的binary log events拷贝到它的中继日志（relay log）；
  3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的