mysql查询深入_MySQL深入学习

mysql

1.存储引擎

1.1.mysql逻辑架构

Connectors:C,PHP,JDBC,ODBC,.NET

存储引擎:

1.连接层

2.服务层:

主要完成核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行.所有跨存储引擎的功能也在这一层实现,如过程,函数等.在该层,服务器会解析查询并创建响应的内部解析树,并对其完成响应的优化如确定查询表的顺序,是否利用索引等,左后生成响应的执行操作.如果是select语句,服务器还会查询内部的缓存.如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的额性能.

3.引擎层:

MyISAM,InnoDB

MyIASM与InnoDB对比

对比项

MyIASM

InnoDB

主外键

不支持

支持

事务

不支持

支持

行表锁

表锁(不适合高并发)

行锁(适合高并发)

缓存

只缓存索引,不缓存真实数据

不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响;

表空间

小

大

关注点

性能

事务

默认安装

Y

Y

查看引擎:show engines;

查看mysql版本:select version();

查看存储引擎:show variables like '%storage_engine%';

查看某表索引类型:show index from tableA;

4.存储层

存储硬盘,文件系统;

存储物理地址;

2.Join查询

2.1.SQL执行顺序

手写

select distinct

from

tableA a

join tableB b on

where

group by

having

order by

limit

机读

from tableA a

on

join tableB b

where

group by

having

select

distinct

order by

limit

[图片上传失败...(image-fdf8d3-1550917404115)]

2.2.SQL 7中查询语句

连接

SQL语句

说明

内连接

select from tableA a inner join tableB b on a.key=b.key

查找2表共有部分

左连接

select from tableA a left join tableB b on a.key=b.key

A表所有(包含AB共有)

右连接

select from tableA a right join tableB b on a.key=b.key

B表所有(包含AB共有)

左外

select from tableA a left join tableB b on a.key=b.key where b.key is null

A表独有(扣除与B表共有部分)

右外

select from tableA a right join tableB on a.key=b.key where a.key is null

B表独有(扣除与A表共有部分)

全连接(外)

select from tableA a full outer join tableB b on a.key=b.key(mysql不支持full outer语法)

A表和B表所有

mysql语法

select from tableA a left join tableB on a.key=b.key union select from tableA a right join tableB on a.key=b.key;

A表和B表所有

左右连接(外)

select from tableA a full outer join tableB b on a.key=b.key where a.key is null or b.key is null

A表和B表各自部分(扣除共有部分)

注意:左连接/右连接,对于没有的部分会置为null;

3.索引与数据处理

3.1.索引概念

3.1.1.定义

索引是帮助mysql高效获取数据的数据结构,也可以说索引是数据结构;

概述:排好序的快速查找数据结构

总结

数据本身之外.数据库还维护着一个满足特定查找算法的数据结构.这些数据结构以某种方式指向数据.

这样就可以在这些数据结构的基础上实现高级查找算法.这种数据结构就是索引;

说明:

一般索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上,我们平常所指的索引基本都是指B树(多路搜索树,并不一定是二叉树的)结构组织的索引;

3.2.索引优势

1.类似字典的字母索引,提高数据检索效率,降低数据库的IO成本;

2.通过索引列对数据进行排序,降低数据排序成本,降低CPU的消耗;

查看io:iostat,iotop,pidstat

iostat -xdm 1;

iostat -x 10 #查看磁盘IO的性能

fdisk -1 #查看磁盘信息

linux下获取占用CPU资源最多的10个进程，可以使用如下命令组合：

ps aux|head -1;ps aux|grep -v PID|sort -rn -k +3|head

linux下获取占用内存资源最多的10个进程，可以使用如下命令组合：

ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head

查看占用cpu最高的进程

ps aux|head -1;ps aux|grep -v PID|sort -rn -k +3|head

或者top (然后按下M，注意这里是大写)

查看占用内存最高的进程

ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head

或者top (然后按下P，注意这里是大写)

PID：进程的ID

USER：进程所有者

PR：进程的优先级别，越小越优先被执行

NInice：值

VIRT：进程占用的虚拟内存

RES：进程占用的物理内存

SHR：进程使用的共享内存

S：进程的状态。S表示休眠，R表示正在运行，Z表示僵死状态，N表示该进程优先值为负数

%CPU：进程占用CPU的使用率

%MEM：进程使用的物理内存和总内存的百分比

TIME+：该进程启动后占用的总的CPU时间，即占用CPU使用时间的累加值。

COMMAND：进程启动命令名称

3.3.索引劣势

1.实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表记录,所以索引也占用空间;

2.虽然索引很大的提高了查询速度,但会降低增删改的速度;

3.索引只是高效率的一个因素,如果数据量较大,需花时间研究最优秀的索引或优化查询语句;

3.4.分类

1.单值索引:一个索引只包含单列,一个表可以有多个单列索引;

2.唯一索引:索引列的值必须唯一,但允许空值;

3.复合索引:一个索引包含多个列;

3.5. 语法

操作

sql

创建

create [unique] index indexName on tableA(columName);

创建

alter table tableA add [unique] index [indexName] (columName);

删除

drop index [indexName] on tableA;

查看

show index [indexName] on tableA\G;

使用alter命令添加

说明

sql

添加主键(唯一且不为null)

alter table tableA add primary key (column_list);

添加索引(唯一可单可多个为null)

alter table tableA add unique index_name (column_list);

添加普通索引(可重复)

alter table tableA add index index_name (column_list);

3.6.explain

3.6.1.作用

mysql优化器:Optimizer

使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句的.分析你的查询语句或表结构的性能瓶颈;

3.6.2.功能

1.表的读取顺序(id);

2.数据读取操作的操作类型(select_type);

3.那些索引可以使用(possiable_key);

4.那些索引被实际使用(key);

5.表之间的引用;

6.每张表有多少行被优化器查询;

3.6.3.玩起来

explain sql语句

包含的字段:

id:

select 查询的序列号,表示查询中执行select子句或操作的顺序;

id相同:执行顺序自上至下;

id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先执行;

id相同不同都有:id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行;衍生 = DERIVED;

select_type:

类型:simple,primary,subquery,derived,union,union reslut;

simple:简单的查询,不含子查询或union;

primary:查询中包含复杂部分的子部分,最外层被标记为primary;

subquery:在select或where列表中包含子查询;

derived:from列表中的子查询被标记为derived(衍生),mysql会递归执行这些子查询,把结果放在临时表中;

union:如果第二个select出现在union之后,则被标记为DERIVED:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED;

union result:从UNION表获取结果的SELECT

table:显示这一行的数据是关于哪张表的

type:

好到坏:system>const>eq_ref>ref>range>index>ALL;通常得保证查询至少达到range级别，最好能达到ref。

possible_keys:

可能用到的索引

key:

实际用到的索引

key_len:

用到的索引字段的最大可能长度,并非实际长度

ref:

显示那个索引列被使用了,有可能是常数(const)

怎么使用的key

rows:

大致估算找到所需记录所需读取的行数

Extra:

1.using filesort:文件排序;说明未使用索引,sql语句有问题;

2.using temporary:使用了临时表存中间结果,常见于order by和group by;

3.using index:表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！索引覆盖:不能使用select *;

4.using where:使用了where过滤;

5.using join buffer:使用了连接缓存;

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

索引覆盖:

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

理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，

因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

3.6.4.Case

[图片上传失败...(image-b9dd01-1550917404115)]

第一行(执行顺序4)：id列为1，表示是union里的第一个select，select_type列的primary表 示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】

第二行(执行顺序2)：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】

第三行(执行顺序3)：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

第四行(执行顺序1)：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

第五行(执行顺序5)：代表从union的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

4.索引失效

序号

说明

1

全值匹配

2

最佳左前缀法则:如果建了多个索引,需要最左前列开始且不跳过中间索引去查询

3

不在索引列上做任何操作(计算,函数,类型转换(自动/手动)),会导致索引失效而转向全表扫描;(等号左边不能有运算)

4

存储引擎不能使用索引中范围条件右边的列;(>,

5

尽量使用索引覆盖!!!减少select *

6

mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描;如果一定要使用,用索引覆盖优化查询速度!或者使用>和

7

注意null/not null对索引可能的影响(分情况)(字段定义是否可null);

8

like 以通配符("%aa%")开头会导致索引失效,变成全表扫描的操作;注意:遵守做前缀原则,如果通配符在后面不会导致索引失效.另,如果非要以通配符开头,使用覆盖索引优化,且索引列遵守最佳左前缀原则;

9

字符串不加单引号会导致索引失效;(注意:mysql内部会涉及数据自动转型)

10

少用or,用它来连接时会索引失效; 可用union all代替;

口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*；

不等空值还有OR，索引影响要注意；

VAR引号不可丢， SQL优化有诀窍。

5.mysql存储过程 函数

存储过程无返回值,函数与返回值;

设置参数log_bin_trust_function_creators

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

为什么要设置这个参数？

当开启二进制日志后(可以执行show variables like 'log_bin'查看是否开启)，

如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报

“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,

or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”这样的错误

创建函数

DELIMITER $$

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

DECLARE return_str VARCHAR(255) DEFAULT '';

DECLARE i INT DEFAULT 0;

WHILE i

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 $$

DELIMITER $$

CREATE FUNCTION rand_num( ) RETURNS INT(5)

BEGIN

DECLARE i INT DEFAULT 0;

SET i = FLOOR(100+RAND()*10);

RETURN i;

END $$

创建存储过程

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(),2000,400,rand_num());

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

#删除

# DELIMITER ;

# drop PROCEDURE insert_emp;

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

#删除

# DELIMITER ;

# drop PROCEDURE insert_dept;

调用存储过程

DELIMITER ;

CALL insert_dept(100,10);

#执行存储过程，往emp表添加50万条数据

DELIMITER ;

CALL insert_emp(100001,500000);