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