![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/特色图像-mysql.png)
查询缓存
- 查询缓存（ Query Cache ）原理
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写 - 优缺点
不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能
查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；
查询缓存的使用，会增加检查和清理Query Cache中记录集的开销 - 哪些查询可能不会被缓存
查询语句中加了SQL_NO_CACHE参数
查询语句中含有获得值的函数，包含自定义函数，如：NOW()
CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
事务隔离级别为Serializable时，所有查询语句都不能缓存 - 查询缓存相关的服务器变量
query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND
示例：
MariaDB [hellodb]> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 | 单个查询结果能缓存的最大值，即缓存中查询结果能分配到的空间最大值
| query_cache_min_res_unit | 4096 | 缓存中内存块的最小分配单位：4K
| query_cache_size | 0 | 查询缓存总共可用的内存空间
| query_cache_strip_comments | OFF |
| query_cache_type | ON | 是否开启缓存功能
| query_cache_wlock_invalidate | OFF | 如果表被锁定，其他yoghurt是否可以从查询缓存中返回结果
+------------------------------+---------+
set global query_cache_size=10485760 更改缓存可用的内存空间为10M
vim /etc/my.cnf 在配置文件中更改此选项
set global query_cache_size=10M - SELECT语句的缓存控制
SQL_CACHE：显式指定存储查询结果于缓存之中
SQL_NO_CACHE：显式查询结果不予缓存 - query_cache_type参数变量
query_cache_type的值为OFF或0时，查询缓存功能关闭
query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存
参看：https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html - 查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE ‘Qcache%';
Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
Qcache_hits：Query Cache 命中次数
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数
Qcache_lowmem_prunes：记录有多少条查询因为内存不足而被移除出查询缓存
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量
示例：
MariaDB [hellodb]> show status like 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 10222536 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+命中率和内存使用率估算
- 查询缓存中内存块的最小分配单位
query_cache_min_res_unit ：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache - 查询缓存命中率
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100% - 查询缓存内存使用率
(query_cache_size – qcache_free_memory) / query_cache_size * 100%
InnoDB存储引擎
- InnoDB存储引擎的缓冲池:
通常InnoDB存储引擎缓冲池的命中不应该小于99% - 查看相关状态变量：
show global status like 'innodb%read%';
Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数
Innodb_buffer_pool_read_ahead: 预读的次数
Innodb_buffer_pool_read_ahead_evicted: 预读页，但是没有读取就从缓冲池中被替换的页数量，一般用来判断预读的效率
Innodb_buffer_pool_read_requests: 从缓冲池中读取页次数
Innodb_data_read: 总共读入的字节数
Innodb_data_reads: 发起读取请求的次数，每次读取可能需要读取多个页
索引
- 索引是特殊数据结构：定义在查找时作为查找条件的字段
- 索引实现在存储引擎
- 优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O - 缺点：
占用额外空间，影响插入速度 - 索引类型：
B+ TREE、HASH、R TREE
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高
B-TREE索引
![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/B-TREE.png)
B+TREE索引
![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/BTREE.png)
![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/BTREE2.png)
- B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
- 可以使用B+Tree索引的查询类型：
全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
匹配最左前缀：即只使用索引的第一列，如：姓wang
匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
匹配范围值：如：姓ma和姓wang之间
精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
只访问索引的查询 - B+Tree索引的限制：
如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如：姓wang,名x%,年龄30，只能利用姓和名上面的索引 - 特别提示：
索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
B-TREE和B+TREE的区别：
B-TREE B树 每个节点最多3个分支，每个节点都存放数据，占用空间较大，不支持范围查询(查询效率很低)
B+TREE B+树 根节点和分支节点不存放数据，只存放索引，数据存放在叶子节点，叶子节点之间有链表关系，知道下一个叶子节点的位置，适合范围查询Hash索引
- Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
- Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
- 适用场景：只支持等值比较查询，包括=, <=>, IN()
- 不适合使用hash索引的场景
不适用于顺序查询：索引存储顺序的不是值的顺序
不支持模糊匹配
不支持范围查询
不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
其他索引
- 地理空间索引（ Geospatial indexing ）：
MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持 - 全文索引(FULLTEXT)：
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持
聚簇索引和非聚簇索引
![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/聚簇索引和非聚簇索引.png)
聚簇索引和非聚簇索引，主键和二级索引
![](https://www.yuandangsheng.top/"wp-content/uploads"/2023/04/主键和二级索引.png)
其他索引
- 冗余和重复索引：
冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引 - 索引优化策略：
独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估左前缀索引：如果某字段长度很长，可以限定其中左起多少个字符作为索引，如name字段有50个字符长度，如果用其中20个字符不会出现重复，那么就可以用前20个字符作为索引 注意：限定的长度的字符不能重复，如wang1，wang2，wang3 ，不能用wang作为索引索引选择性：不重复的索引值和数据表的记录总数的比值
多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
索引优化建议
- 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或者order by子句，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作
SQL语句性能优化
- 查询时，能不要就不用，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多表连接时，尽量小表驱动大表，即小表 join 大表
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询，可以开启缓存
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
管理索引
- 创建索引：
CREATE INDEX index_name ON tbl_name (index_col_name,...);
help CREATE INDEX使用alter命令添加索引 alter table students add index idx_age(age); 创建惟一键索引 create unique index uni_idx_name on students(name); - 删除索引：
DROP INDEX index_name ON tbl_name;使用alter命令删除惟一键索引 alter table students drop key uni_idx_name; - 查看索引：
SHOW INDEXES FROM [db_name.]tbl_name; - 优化表空间：
OPTIMIZE TABLE tb_name - 查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS
EXPLAIN
- 通过EXPLAIN来分析索引的有效性：
- EXPLAIN SELECT clause
获取查询执行计划信息，用来查看查询优化器如何执行查询
示例：
创建索引
create index index_name on students(name);
查看查询数据是否利用索引
MariaDB [hellodb]> explain select * from students where age=10;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
没有使用索引查询
MariaDB [hellodb]> explain select * from students where name like 'ren%';
+------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | index_name | index_name | 152 | NULL | 1 | Using index condition |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+
使用索引查询- 输出信息说明：
参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html - id: 当前查询语句中，每个SELECT语句的编号
复杂类型的查询有三种：
简单子查询
用于FROM中的子查询
联合查询：UNION
注意：UNION查询的分析结果会出现一个额外匿名临时表
示例：
使用复杂查询语句，有多个子句的id号
MariaDB [hellodb]> explain select stuid,name from students union select tid,name from teachers;
+------+--------------+------------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | PRIMARY | students | index | NULL | idx_name_age | 153 | NULL | 25 | Using index |
| 2 | UNION | teachers | ALL | NULL | NULL | NULL | NULL | 4 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+-------+---------------+--------------+---------+------+------+-------------+- select_type：
简单查询为SIMPLE
复杂查询：
SUBQUERY: 简单子查询
PRIMARY:最外面的SELECT
DERIVED: 用于FROM中的子查询
UNION：UNION语句的第一个之后的SELECT语句
UNION RESULT: 匿名临时表
示例：
子查询subquery
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 | index | index_age | idx_name_age | 153 | NULL | 25 | Using where; Using index |
| 2 | SUBQUERY | students | index | NULL | index_age | 1 | NULL | 25 | Using index |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
DERIVED: 用于FROM中的子查询
centos7系统想要在explain查询字段中看到derived字段，需要进行相关设置才能显示出来
set optimizer_switch='derived_merge=off';
MariaDB [hellodb]> explain select a.name,a.age from (select * from students) as a;
+------+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 25 | |
| 2 | DERIVED | students | ALL | NULL | NULL | NULL | NULL | 25 | |
+------+-------------+------------+------+---------------+------+---------+------+------+-------+
const：返回单个行
MariaDB [hellodb]> explain select * from students where stuid = 5;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+- 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：对结果使用一个外部索引排序
文章评论