SQL高级 --优化

一、SQL查询的解析

• 关联查询过多
• 索引失效（单值、符合）
  
  
  

二、mysql explain使用简介

1、关于id的说明：

2 、select_type

常见和常用的值有如下几种：

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

1、 SIMPLE 简单的select查询，查询中不包含子查询或者UNION

2、 PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

3、 SUBQUERY 在SELECT或WHERE列表中包含了子查询

4、DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

5、UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6、UNION RESULT 从UNION表获取结果的SELECT

3、 关于 type 说明

4 、 table

指的就是当前执行的表

5 、possible_keys 和 key

possible_keys： 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key ： 实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

6、 explain 关于 EXtra介绍

包含不适合在其他列中显式但十分重要的额外信息

6.1 Using filesort（九死一生）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

6.2 Using temporary（十死无生）

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

6.3 Using index（发财了）

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

6.4 Using where

表明使用了where过滤

6.5 Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

6.6 impossible where

where子句的值总是false，不能用来获取任何元组

SELECT * FROM t_user WHERE id = '1' and id = '2'

### 6.7 select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

### 6.8 distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

覆盖索引(Covering Index), 一般说为索引覆盖。

• 理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
• 理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

- 小练习

答案：

Join语句的优化

常见失效：

第2点解释、最佳左侧法则：

如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
自己理解：（在复合索引中）按照复合索引中的第一个字段进行引用。

第4点解释、存储引擎不能使用索引中范围条件右边的列。

第5点解释、

第6点解释、

第7点解释、

第8点解释、

解决方法

• like 匹配的值可以在左边写 % ，右边不能写。
• 可以使用覆盖索引解决

第9点解释、字符串不加单引号索引失效

in与exists

ORDER BY优化

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

MYSQLDUNMPSLOW

三、批量导入数据时（1000w）时注意

• 示例
• 

CREATE TABLE demo_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 ;

#创建表 demo_emp
CREATE TABLE demo_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 ;

• 2.1随机产生字符串
  

##随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghjklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUWWXYZ';
	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 $$

• 2.2随机产生部门编号
  

#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i= FLOOR(100+RAND()*10);
RETURN i;
END $$

• 2.3创建往emp表中插入数据的存储过程（进行一次提交）
  

CREATE DEFINER=`root`@`%` PROCEDURE `insert_emp`(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
##set autocommit =）把autocommit设置成0
SET autocommit = 0;
REPEAT
SET i = i+ 1;
INSERT INTO demo_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

3、验证

• 练习
  

四 Profile进行sql分析

4.1 是否支持

查询时不要出现的：

4.2全局日志

• 1 、配置启用
• 1 、编码置启用
  

五、数据库锁机制

查看锁的命令 : show open tables;

释放锁

unlock 表名

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

六、ACID原理

事务隔离级别

手动提交事务

七、大坑

7.1 无索引行锁升级为表锁

我们知道锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁 , 从上面的测试中也可以验证这个观点。

结论
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

所以建表的时候 ，结合你的业务，如果有更新的操作，切记要对操作的字段建立索引，不然并发下这个问题就非常明显

7.2 间隙锁危害

对条件范围进行加锁

7.3 面试常见问题： 如何锁一行

杭锁总结：

# 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show STATUS LIKE 'innodb_row_lock%'

八、 主从复制

8.1原理

8.2 规则

复制的最大问题：有延时 ！

一主一从配置

1. MySQL版本必须一致。
2. 主从都配置在[mysqld]结点下，都是小写
3. 主机 my.ini配置
   
   
   
4. 从机配置

• [必须]从服务器唯一ID
• [可选]启用二进制日志

5. 关闭防火

6. 在Windows主机上建立帐户并授权slave
   
   

7. 从机配置
   

8. stop slave ; -如何停止从服务复制功能