MySQL之变量、查询缓存和索引

MySQL中的系统数据库

mysql数据库：是mysql的核心数据库，类似于sql server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

PERFORMANCE_SCHEMA:MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

information_schema数据库:MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在。information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

服务器配置

mysqld选项，服务器系统变量和服务器状态变量

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html

https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置

获取mysqld的可用选项列表：

mysqld --help –verbose

mysqld --print-defaults 获取默认设置

服务器系统变量：分全局和会话两种

服务器状态变量：分全局和会话两种

获取运行中的mysql进程使用各服务器参数及其值

mysql> SHOW GLOBAL VARIABLES;

mysql> SHOW [SESSION] VARIABLES;

设置服务器系统变量三种方法：

在命令行中设置:

shell> ./mysqld_safe –-skip-name-resolve=1; #跳过DNS反向解析，加快连接速度

在配置文件my.cnf中设置：

skip_name_resolve=1;

在mysql客户端使用SET命令：

mysql>SET GLOBAL sql_log_bin=0

服务器端设置

修改服务器变量的值：

mysql> help SET

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

mysql> SET GLOBAL system_var_name=value;

mysql> SET @@global.system_var_name=value;

修改会话变量：

mysql> SET [SESSION] system_var_name=value;

mysql> SET @@[session.]system_var_name=value;

状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

mysql> SHOW GLOBAL STATUS;

mysql> SHOW [SESSION] STATUS;

服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/

常见MODE:

NO_AUTO_CREATE_USER

禁止GRANT创建密码为空的用户

NO_AUTO_VALUE_ON_ZERO

在自增长的列中插入0或NULL将不会是下一个自增长值

NO_BACKSLASH_ESCAPES

反斜杠“\”作为普通字符而非转义字符

PAD_CHAR_TO_FULL_LENGTH

启用后，对于CHAR类型将不会截断空洞数据

PIPES_AS_CONCAT

将"||"视为连接操作符而非“或运算符”

例：

set sql_mode='traditional';

show variables like 'sql_mode'可以看到traditional由很多项组成

MariaDB配置文件需要增加的选项

innodb_file_per_table

       每个表存成一个独立的文件

log_bin=/data/mysqllog/mysql-bin

       开启二进制日志的两个选项之一

       二进制日志不要跟数据放在同一块磁盘

       注意文件夹的所有者所属组权限需是mysql

       预留足够空间，通常二进制日志比数据库本身还要大

binlog_format=row

       二进制日志基于行记录

innodb_log_group_home_dir=/data/mysqllog/transaction_log/

       指定事务日志存放目录，建议跟数据存放在不同磁盘中

skip_name_resolve=on

       禁止名字解析

query_cache_size=10M

       查询缓存size调成不为0，否则查询缓存不开启

频繁使用的字段可以加索引

MySQL架构

数据库的数据块大小是4K的整数倍，MySQL是16K

查询的执行路径

查询缓存

查询缓存（ Query Cache ）原理：

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写（因为使用的是hash值）

优缺点

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能

查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；

查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

哪些查询可能不会被缓存

查询语句中加了SQL_NO_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，低于此值有警报（默认为0，缓存不启动）

query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果， 默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许

query_cache_type: 是否开启缓存功能，取值为ON, OFF, DEMAND

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：当 Query Cache 内存容量不够，需要删除老的 Query Cache 以给新的 Cache 对象使用的次数

Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句

Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量

命中率和内存使用率估算

查询缓存中内存块的最小分配单位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%'\G

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: 发起读取请求的次数，每次读取可能需要读取多个页

Innodb缓冲池命中率计算：

平均每次读取的字节数：

索引

索引是特殊数据结构：定义在查找时作为查找条件的字段

索引实现在存储引擎

优点：

索引可以降低服务需要扫描的数据量，减少了IO次数

索引可以帮助服务器避免排序和使用临时表

索引可以帮助将随机I/O转为顺序I/O

缺点：

占用额外空间，影响插入速度（因要同时改索引）

索引类型：

B+ TREE、HASH、R TREE

聚簇（集）索引、非聚簇索引：数据和索引存储顺序是否一致

主键索引、二级（辅助）索引

稠密索引、稀疏索引：是否索引了每一个数据项

简单索引、组合索引（分别指在1个和多个字段上建立索引）

左前缀索引：取前面的字符做索引

覆盖索引：从索引中即可取出要查询的数据，性能高

聚簇和非聚簇索引，主键和二级索引

聚簇索引相当于书的目录，非聚簇索引相当于书的附录

一般主键索引是和数据在一起的，二级索引存放的是指向主键的指针，跟数据不在一起，一般不是主键的索引

MyISAM不支持聚簇索引

B+TREE索引

B+tree索引

B+tree索引

B+ Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

可以使用B-Tree索引的查询类型：

全值匹配：精确所有索引列，如：姓qin，名jianyuan，年龄27

匹配最左前缀：即只使用索引的第一列，如：姓qin

匹配列前缀：只匹配一列值开头部分，如：姓以q开头的

匹配范围值：如：姓qin和姓wang之间

精确匹配某一列并范围匹配另一列：如：姓qin，名以j开头的

只访问索引的查询

B-Tree索引的限制：

如果不从最左列开始，则无法使用索引：如：查找名为xiaochun，或姓为g结尾

不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如：姓wang,名x%,年龄30，只能利用姓和名上面的索引

特别提示：

索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引

为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好

只有Memory存储引擎支持显式hash索引

适用场景：

只支持等值比较查询，包括=, IN(), <=>

不适合使用hash索引的场景：

不适用于顺序查询：索引存储顺序的不是值的顺序

不支持模糊匹配

不支持范围查询

不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

其他索引

空间索引（R-Tree）：

MyISAM支持空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多

全文索引(FULLTEXT)：

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎

聚簇和非聚簇索引

聚簇和非聚簇索引，主键和二级索引

冗余和重复索引：

冗余索引：（A），（A，B）

此为不好的索引使用策略，建议扩展索引，而非冗余

重复索引：已经有索引，再次建立索引

索引优化策略：

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧

左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值

多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

尽量使用短索引，如果可以，应该制定一个前缀长度

对于经常在where子句使用的列，最好设置索引

对于有多个列where或者order by子句，应该建立复合索引

对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

尽量不要在列上进行运算（函数操作和表达式操作）

尽量不要使用not in和<>操作

SQL语句性能优化

查询时，能不要*就不用*，尽量写全字段名

大部分情况连接效率远大于子查询

多表连接时，尽量小表驱动大表，即小表 join 大表

在千万级分页时使用limit

对于经常使用的查询，可以开启缓存

多使用explain和profile分析查询语句

查看慢查询日志，找出执行时间长的sql语句优化

管理索引

创建索引：

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name,...);

help CREATE INDEX

删除索引：

DROP INDEX index_name ON tbl_name;

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name（表发生大变化的时候建议做一下整理）

查看索引的使用

SET GLOBAL userstat=1;

SHOW INDEX_STATISTICS

EXPLAIN

通过EXPLAIN来分析索引的有效性：

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

输出信息说明：

参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

id: 当前查询语句中，每个SELECT语句的编号

复杂类型的查询有三种：

简单子查询

用于FROM中的子查询

联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

select_type：

简单查询为SIMPLE

复杂查询：

SUBQUERY: 简单子查询

PRIMARY:最外面的SELECT

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：对结果使用一个外部索引排序