网站建设的市场情况,淄博网站推广公司那些,运城 网站制作,软件项目管理案例教程第四版一、SQL查询的解析
关联查询过多索引失效#xff08;单值、符合#xff09;
二、mysql explain使用简介 1、关于id的说明#xff1a; 2 、select_type
常见和常用的值有如下几种#xff1a;
分别用来表示查询的类型#xff0c;主要是用于区别普通查询、联合查询、子…一、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和IndexIndex效率高.它指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
)ENGINEINNODB ;#创建表 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##*部门编号*
)ENGINEINNODB ;
2.1随机产生字符串
##随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGINDECLARE chars_str VARCHAR(100) DEFAULT abcdefghjklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUWWXYZ;DECLARE return_str VARCHAR(255) DEFAULT ;DECLARE i INT DEFAULT 0;WHILE in DOSET return_strCONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SET ii1;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(100RAND()*10);
RETURN i;
END $$
2.3创建往emp表中插入数据的存储过程进行一次提交 CREATE DEFINERroot% 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((STARTi),rand_string(6),SALESMAN,0001,CURDATE(),2000,400,rand_num());
UNTIL i max_num END REPEAT;
COMMIT;
END3、验证
练习
四 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 规则
复制的最大问题有延时
一主一从配置
MySQL版本必须一致。主从都配置在[mysqld]结点下都是小写主机 my.ini配置 从机配置
[必须]从服务器唯一ID[可选]启用二进制日志 关闭防火 在Windows主机上建立帐户并授权slave 从机配置 stop slave ; -如何停止从服务复制功能
