mysql构架:
connection pool:因为单进程多线程,所以需要一个线程池接收请求提供并发,线程重用,还能完成认证
SQL interface:接收并分析SQL语句
Parser:分析器,翻译sql语句,验证用户权限,执行响应指令,生成执行树
Optimizer:优化器,通过分析索引结构,统计情况等衡量多个访问路径哪个开销最小,生成统计数据,查询语句改写
Cache & Buffer:热点数据装入内存,实现查询时加速,查询时不需要每次都调用存储引擎
存储引擎:与数据块上的数据交互,实现物理层(文件系统层面)和逻辑层(数据库层面)映射。mariadb支持自定义引擎,默认使用innodb
MySQL数据文件类型:
数据文件、索引文件
重做日志、撤消日志、二进制日志、错误日志、查询日志、慢查询日志、(中继日志)
请求处理实现流程:
基本法则:索引应该构建在被用作查询条件的字段上;
索引类型:
B+ Tree索引:顺序存储,每一个叶子节点到根结点的距离是相同的;左前缀索引,适合查询范围类的数据;
可以使用B-Tree索引的查询类型:全键值、键值范围或键前缀查找;
全值匹配:精确某个值, "Jinjiao King";
匹配最左前缀:只精确匹配起头部分,"Jin%"
匹配范围值:
精确匹配某一列并范围匹配另一列:
只访问索引的查询
不适合使用B-Tree索引的场景:
如果不从最左列开始,索引无效; (Age,Name)
不能跳过索引中的列;(StuID,Name,Age)
如果查询中某个列是为范围查询,那么其右侧的列都无法再使用索引优化查询;(StuID,Name)
Hash索引:基于哈希表实现,特别适用于精确匹配索引中的所有列;
注意:只有Memory存储引擎支持显式hash索引;
适用场景:
只支持等值比较查询,包括=, IN(), <=>;
不适合使用hash索引的场景:
存储的非为值的顺序,因此,不适用于顺序查询;
不支持模糊匹配;
空间索引(R-Tree):
MyISAM支持空间索引;
全文索引(FULLTEXT):
在文本中查找关键词;
索引优点:
索引可以降低服务需要扫描的数据量,减少了IO次数;
索引可以帮助服务器避免排序和使用临时表;
索引可以帮助将随机I/O转为顺序I/O;
高性能索引策略:
独立使用列,尽量避免其参与运算;
左前缀索引:索引构建于字段的左侧的多少个字符,要通过索引选择性来评估
索引选择性:不重复的索引值和数据表的记录总数的比值;
多列索引:
AND操作时更适合使用多列索引;
选择合适的索引列次序:将选择性最高放左侧;
冗余和重复索引:
不好的索引使用策略
索引管理:按特定数据结构存储的数据;
索引类型:
聚集索引、非聚集索引:数据是否与索引存储在一起;多级形式一般前几级非聚集,最后一级聚集,非聚集索引索引到数据块上,找到元数据进而找到数据
主键索引、辅助索引
稠密索引、稀疏索引:是否索引了每一个数据项;
B+ TREE、HASH、R TREE
简单索引、组合索引:在一个字段还是多个字段上创建索引
左前缀索引:只针对某个字段的最左边部分做索引
覆盖索引:无需查找源数据,直接通过查找索引即可得到结果
管理索引的途径:
创建索引:创建表时指定;CREATE INDEX
创建或删除索引:也可以使用修改表的命令实现创建和删除索引
删除索引:DROP INDEX
查看表上的索引:
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
示例:
MariaDB [hellodb]> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
使用EXPLAIN分析出查询过程中是否用到索引,以及如何获取数据索引
id: 当前查询语句中,每个SELECT语句的编号;
复杂类型的查询有三种:
简单子查询;
用于FROM中的子查询;
联合查询:UNION;
注意:UNION查询的分析结果会出现一外额外匿名临时表;
select_type:
简单查询为SIMPLE
全表扫描ALL
范围查询RANGE
复杂查询:
SUBQUERY: 简单子查询;
DERIVED: 用于FROM中的子查询;
UNION:UNION语句的第一个之后的SELECT语句;
UNION RESULT: 匿名临时表;
table:SELECT语句关联到的表;
type:关联类型,或访问类型,即MySQL决定的如何去查询表中的行的方式;
ALL: 全表扫描;
index:根据索引的次序进行全表扫描;如果在Extra列出现“Using index”表示了使用覆盖索引,而非全表扫描;
range:有范围限制的根据索引实现范围扫描;扫描位置始于索引中的某一点,结束于另一点;
ref: 根据索引返回表中匹配某单个值的所有行;
eq_ref:仅返回一个行,但与需要额外与某个参考值做比较;
const, system: 直接返回单个行;
possible_keys:查询可能会用到的索引;
key: 查询中使用了的索引;
key_len: 在索引使用的字节数;
ref: 在利用key字段所表示的索引完成查询时所有的列或某常量值;
rows:MySQL估计为找所有的目标行而需要读取的行数;
Extra:额外信息
Using index:MySQL将会使用覆盖索引,以避免访问表;
Using where:MySQL服务器将在存储引擎检索后,再进行一次过滤;
Using temporary:MySQL对结果排序时会使用临时表;
Using filesort:对结果使用一个外部索引排序;
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE StuID=3;
如下:利用索引的主键实现了一对一查询
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
如下:没使用索引,就是取得所有行,一行一行查询,效率较低
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE AGE=53;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
为AGE字段添加索引:
MariaDB [hellodb]> ALTER TABLE students ADD INDEX(Age);
Query OK, 25 rows affected (0.00 sec)
Records: 25 Duplicates: 0 Warnings: 0
Age字段也有了索引:
MariaDB [hellodb]> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | |
| students | 1 | Age | 1 | Age | A | NULL | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
为Name字段创建一个索引:
MariaDB [hellodb]> CREATE INDEX name ON students (Name);
Query OK, 25 rows affected (0.01 sec)
Records: 25 Duplicates: 0 Warnings: 0
查看:
MariaDB [hellodb]> SELECT * FROM students WHERE Name LIKE 'X%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
+-------+-------------+-----+--------+---------+-----------+
查看语句执行分析:是一个基于范围的查询,查询了6个
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%';
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | name | name | 152 | NULL | 6 | Using index condition |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
全表扫描式查询:查询了25个
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name LIKE '%X%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
视图:view虚表,一个select语句得到的结果保存为虚表,可以对这个虚表继续执行查询作,修改
创建方法:
CREATE
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
创建一个视图:
MariaDB [hellodb]> CREATE VIEW test AS SELECT StuID,Name,Age FROM students;
Query OK, 0 rows affected (0.05 sec)
查看发现出现了视图表test:
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| test |
| toc |
+-------------------+
8 rows in set (0.00 sec)
查看虚表状态:
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'test'\G;
*************************** 1. row ***************************
Name: test
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
ERROR: No query specified
查询一下发现只有3个字段,因为我们创建时指定了这3个字段
MariaDB [hellodb]> SELECT * FROM test WHERE Age=22;
+-------+---------------+-----+
| StuID | Name | Age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 21 | Huang Yueying | 22 |
+-------+---------------+-----+
3 rows in set (0.00 sec)
查看执行分析:
MariaDB [hellodb]> EXPLAIN SELECT * FROM test WHERE Age=22\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ref
possible_keys: Age
key: Age
key_len: 1
ref: const
rows: 2
Extra:
1 row in set (0.00 sec)
ERROR: No query specified
#视图中的数据事实上存储于“基表”中,因此,其修改操作也会针对基表实现;其修改操作受基表限制;
删除视图:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
mysql中SELECT专题:
查询执行路径中的组件:查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎;
SELECT FROM ORDER BY 查询后做排序
SELECT FROM GROUP BY HAVING 查询后分组,分组后指明过滤条件做过滤
SELECT FROM WHERE
SELECT FROM WHERE GROUP BY LIMIT 查询后分组,然后仅显示一部分内容
SELECT FROM HAING
GROUP和HAVING,LIMIT是可选的
总结:FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause --> ORDER BY --> SELECT --> LIMIT
典型的不使用缓存查询:(因为时间一直在变)
MariaDB [(none)]> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2015-11-03 09:37:24 |
+---------------------+
1 row in set (0.00 sec)
1.单表查询:
去重使用DESTINCT:
MariaDB [hellodb]> SELECT GENDER FROM students;
+--------+
| GENDER |
+--------+
| M |
| M |
| M |
| M |
| M |
| M |
| F |
| F |
| F |
| F |
| M |
| F |
| M |
| F |
| M |
| M |
| M |
| M |
| F |
| F |
| F |
| F |
| M |
| M |
| M |
| M |
+--------+
发现变化了么吗:DISTINCT查询后数据去重
MariaDB [hellodb]> SELECT DISTINCT GENDER FROM students;
+--------+
| GENDER |
+--------+
| M |
| F |
+--------+
2 rows in set (0.00 sec)
SQL_CACHE: 显式指定存储查询结果于缓存之中;
SQL_NO_CACHE: 显式查询结果不予缓存;
MariaDB [hellodb]> SELECT SQL_CACHE DISTINCT Gender FROM students;
+--------+
| Gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.00 sec)
query_cache_type的值为'ON'时,查询缓存功能打开;SELECT的结果符合缓存条件即会缓存,否则,不予缓存;如果显示指定SQL_NO_CACHE: 显式查询结果不予缓存;
query_cache_size值为0也不会缓存,想缓存就需要调整大于0
query_cache_type的值为'DEMAND'时,查询缓存功能按需进行;显式指定SQL_CACHE的SELECT语句才会缓存;其它均不予缓存
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
8 rows in set (0.00 sec)
查看缓存状态属性:
MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 | #命中次数
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.01 sec)
MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 8 | #查询总次数
+---------------+-------+
1 row in set (0.00 sec)
缓存命中率的评估:Qcache_hits命中次数/(Qcache_hits+Com_select)总次数
字段显示可以使用别名:
col1 AS alias1, col2 AS alias2, ...
MariaDB [hellodb]> SELECT Name AS StuName FROM students;
+---------------+
| StuName |
+---------------+
| Diao Chan |
| Ding Dian |
WHERE子句:
指明过滤条件以实现“选择”的功能:
过滤条件:布尔型表达式;
算术操作符:+, -, *, /, %
比较操作符:=, !=, <>, <=>, >, >=, <, <=
!=和<>都表示不等于
<==>:在做比较时如果对方方出现空值没法比较,这个表示跟空值比较时也可安全执行
BETWEEN min_num AND max_num
IN (element1, element2, ...)等值或条件表达式
IS NULL
IS NOT NULL
示例:
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age IN (18,100);
+-------------+-----+
| Name | Age |
+-------------+-----+
| Xue Baochai | 18 |
| Sun Dasheng | 100 |
+-------------+-----+
2 rows in set (0.03 sec)
MariaDB [hellodb]> SELECT Name,ClassID FROM students WHERE ClassID IS NULL;
+--------------+---------+
| Name | ClassID |
+--------------+---------+
| Xu Xian | NULL |
| Sun Dasheng | NULL |
| Yinjiao King | NULL |
+--------------+---------+
3 rows in set (0.00 sec)
LIKE:
%: 任意长度的任意字符;
_:任意单个字符;
RLIKE:基于正则表达式匹配
REGEXP:匹配字符串可用正则表达式书写模式;
逻辑操作符:组合多个条件进行查询NOT,AND,OR,XOR
GROUP字句:根据指定的条件把查询结果进行“分组”以用于做“聚合”运算:
avg(), max(), min(), count(), sum()
统计男女同志的平均年龄:
MariaDB [hellodb]> SELECT avg(Age),Gender FROM students GROUP BY Gender;
+----------+--------+
| avg(Age) | Gender |
+----------+--------+
| 19.0000 | F |
| 37.0625 | M |
+----------+--------+
2 rows in set (0.00 sec)
HAVINGD对group后的结果作过滤:
MariaDB [hellodb]> SELECT avg(Age)as AAge,Gender FROM students GROUP BY Gender HAVING AAge>20;
+---------+--------+
| AAge | Gender |
+---------+--------+
| 37.0625 | M |
+---------+--------+
1 row in set (0.00 sec)
count()函数可以计算总数
MariaDB [hellodb]> SELECT count(StuID) AS NOS,ClassID FROM students GROUP BY ClassID HAVING NOS >2;
+-----+---------+
| NOS | ClassID |
+-----+---------+
| 3 | NULL |
| 4 | 1 |
| 3 | 2 |
| 4 | 3 |
| 4 | 4 |
| 4 | 6 |
| 3 | 7 |
+-----+---------+
7 rows in set (0.00 sec)
3.ORDER BY: 根据指定的字段对查询结果进行排序;
升序:ASC
降序:DESC
MariaDB [hellodb]> SELECT count(StuID) AS NOS,ClassID FROM students GROUP BY ClassID HAVING NOS >2 ORDER BY NOS DESC;
+-----+---------+
| NOS | ClassID |
+-----+---------+
| 4 | 6 |
| 4 | 1 |
| 4 | 4 |
| 4 | 3 |
| 3 | NULL |
| 3 | 2 |
| 3 | 7 |
+-----+---------+
7 rows in set (0.00 sec)
4.LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制;
显示第11到20个,第一个10表示偏移量,第二个做计数
MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 10,10;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Hua Rong | 23 |
| Yuan Chengzhi | 23 |
| Huang Yueying | 22 |
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xu Zhu | 21 |
| Ren Yingying | 20 |
| Xiao Qiao | 20 |
| Xi Ren | 19 |
| Duan Yu | 19 |
+---------------+-----+
10 rows in set (0.00 sec)
对查询结果中的数据请求施加“锁”:
FOR UPDATE: 写锁,排他锁;
LOCK IN SHARE MODE: 读锁,共享锁
2.多表查询:
交叉连接:笛卡尔乘积;
内连接:
等值连接:让表之间的字段以“等值”建立连接关系;让两张表中表示同一意义的字段建立等值连接关系
不等值连接
自然连接
自连接
外连接:
左外连接:
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
笛卡尔乘积:不建议已使用,效率很差
SELECT * FROM students;
26 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
SELECT * FROM students,teachers;
104 rows in set (0.00 sec) #是前面两个表行数之积
等值连接:让表之间的字段以“等值”建立连接关系
MariaDB [hellodb]> SELECT * FROM students,teachers WHERE students.TeacherID=teachers.TID;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
两张表中按照指定字段字段进行等值连接
MariaDB [hellodb]> SELECT s.Name AS StuName,t.Name AS TeaName FROM students AS s,teachers AS t WHERE s.TeacherID=t.T;
+-------------+---------------+
| StuName | TeaName |
+-------------+---------------+
| Yu Yutong | Song Jiang |
| Shi Zhongyu | Miejue Shitai |
| Ding Dian | Lin Chaoying |
+-------------+---------------+
3 rows in set (0.00 sec)
看下分析结果:
MariaDB [hellodb]> EXPLAIN SELECT s.Name AS StuName,t.Name AS TeaName FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 26
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where; Using join buffer (flat, BNL join)
2 rows in set (0.00 sec)
左外连接:
MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QingCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QingCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QingCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | QingCheng Pai |
| Duan Yu | Wudang Pai |
| Xu Zhu | Shaolin Pai |
| Lin Chong | Wudang Pai |
| Hua Rong | Ming Jiao |
| Xue Baochai | Lianshan Pai |
| Diao Chan | Ming Jiao |
| Huang Yueying | Lianshan Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
| Xu Xian | NULL |
| Sun Dasheng | NULL |
| Yinjiao King | NULL |
+---------------+----------------+
26 rows in set (0.03 sec)
自连接:
MariaDB [hellodb]> SELECT s.Name,t.Name FROM students AS s,students AS t WHERE s.TeacherID=t.StuID;
+-------------+-------------+
| Name | Name |
+-------------+-------------+
| Shi Zhongyu | Xie Yanke |
| Shi Potian | Xi Ren |
| Xie Yanke | Xu Zhu |
| Ding Dian | Ding Dian |
| Yu Yutong | Shi Zhongyu |
+-------------+-------------+
5 rows in set (0.00 sec)
子查询:在查询语句嵌套着查询语句,基于某语句的查询结果再次进行的查询
用在WHERE子句中的子查询:
(1) 用于比较表达式中的子查询;子查询仅能返回单个值;
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
+--------------+-----+
| Name | Age |
+--------------+-----+
| Ding Dian | 32 |
| Tian Boguang | 33 |
| Shi Qing | 46 |
| Xie Yanke | 53 |
| Yinjiao King | 98 |
| Sun Dasheng | 100 |
+--------------+-----+
6 rows in set (0.00 sec)
MariaDB [hellodb]> EXPLAIN SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | students | range | Age | Age | 1 | NULL | 7 | Using where |
| 2 | SUBQUERY | students | index | NULL | Age | 1 | NULL | 26 | Using index |
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
(2) 用于IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表;
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
(3) 用于EXISTS;
用于FROM子句中的子查询;
使用格式:SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
示例:
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
+---------+---------+
| aage | ClassID |
+---------+---------+
| 36.0000 | 2 |
| 46.0000 | 5 |
+---------+---------+
2 rows in set (0.00 sec)
联合查询:UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
MariaDB [hellodb]> EXPLAIN SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | students | ALL | NULL | NULL | NULL | NULL | 26 | |
| 2 | UNION | teachers | ALL | NULL | NULL | NULL | NULL | 4 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
查询缓存:
如何判断是否命中:
通过查询语句的哈希值判断:哈希值考虑的因素包括
查询本身、要查询的数据库、客户端使用协议版本,...
查询语句任何字符上的不同,都会导致缓存不能命中;
哪此查询可能不会被缓存?
查询中包含UDF、存储函数、用户自定义变量、临时表、mysql库中系统表、或者包含列级权限的表、有着不确定值的函数(Now());
查询缓存相关的服务器变量:
query_cache_min_res_unit: 查询缓存中内存块的最小分配单位;
较小值会减少浪费,但会导致更频繁的内存分配操作;
较大值会带来浪费,会导致碎片过多;
query_cache_limit:能够缓存的最大查询结果;
对于有着较大结果的查询语句,建议在SELECT中使用SQL_NO_CACHE
query_cache_size:查询缓存总共可用的内存空间;单位是字节,必须是1024的整数倍;
query_cache_type: ON, OFF, DEMAND
query_cache_wlock_invalidate:如果某表被其它的连接锁定,是否仍然可以从查询缓存中返回结果;默认值为OFF,表示可以在表被其它连接锁定的场景中继续从缓存返回数据;ON则表示不允许;
查询相关的状态变量
SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759688 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
转载于:https://blog.51cto.com/leeyan/1708360