Linux:MySQL（八）服务器选项变量、缓存及索引

服务器配置

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/

选项和变量的区别

注意别把选项和变量弄混，变量写到配置文件中无法启动服务
*选项：加到配置/etc/my.cnf文件中，
		名字都带杠- 
		重新启动服务生效
*变量：不支持加入配置文件，
		名字都是_ 
		设置命令：set 
		查询命令：show variables like'';
*状态变量只能查询：show status like'';

一、msqld选项

获取mysqld的可用选项列表、默认设置

[[email protected] ~]#/usr/libexec/mysqld --verbose --help
[[email protected] ~]#/usr/libexec/mysqld --print-defaults --

设置服务器选项方法：
- 在命令行中设置
  shell> ./mysqld_safe --skip-name-resolve=1
- 在配置文件my.cnf中设置
  skip_name_resolve=1

二、服务器系统变量（全局和会话）

SHOW VARIALES 显示系统变量

MariaDB [(none)]> show variables like '%datadir%';
MariaDB [(none)]> show global variables like '%chara%';

SET 修改服务器变量：（会话变量和全局变量）

MariaDB [(none)]> set globalcharacter_set_results=gb2312;
MariaDB [(none)]> set character_set_results=gb2312;
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

三、服务器状态变量（全局和会话）

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

MariaDB [(none)]> show status like 'uptime';
MariaDB [(none)]> show global status like 'uptime';

服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/
由文档可知既是服务器选项也是变量
常见MODE:
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
NO_ZERO_DATE
在严格模式，不允许使用‘0000-00-00’的时间
ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，
没有在GROUP BY中出现，那么将认为这个SQL是不合法的
NO_BACKSLASH_ESCAPES
反斜杠“\”作为普通字符而非转义字符
PIPES_AS_CONCAT
将"||"视为连接操作符而非“或运算符”
举例理解：

MariaDB [db1]> insert name_id values(1,'aaaaaa');        
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [db1]> select * from name_id;
+------+-------+
| name | id    |
+------+-------+
|    1 | aaaaa |
+------+-------+
1 row in set (0.00 sec)
MariaDB [db1]>  set sql_mode='TRADITIONAL' 
MariaDB [db1]> insert name_id values(2,'bbbbbbbbb');
ERROR 1406 (22001): Data too long for column 'id' at row 1
MariaDB [db1]> select * from name_id;
+------+-------+
| name | id    |
+------+-------+
|    1 | aaaaa |
+------+-------+
1 row in set (0.00 sec)vi
MariaDB [db1]> show variables like '%sql_mode%'
未设置前可以插入，设置后错误不能插入
设置后可以查到，未设置前不能看到

show status like ‘%select%’; 查询状态
show status like ‘%insert%’; 插入状态
show variables like ‘%connect%’ 查看最大连接数，默认151
show status like ‘threads’; 查看线程可看正在连接数

max_connections 
由官方文档可知既是选项也是变量，是全局的
数据库最多1000多并发就可以了，nginx最多能到3万
set global max_connections=1000; 查询官方文档可知connections是global级别，不能设置session级别
下次还需生效需更改配置文件

set global skip_name_resolve=no 设置成no 禁止反向解析，将来集群时候可能遇坑,全局选项，停服务写到配置文件中

Linux:MySQL（八）服务器选项变量、缓存及索引

数据库的block是16K,如想改为别的需源码编译

查询缓存

查询缓存（ Query Cache ）原理
- 缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写
  （对执行的SQL语句进行hash运算）
优缺点
- 不需要对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，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足

MariaDB [(none)]> show variables like 'query_cache%'
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |   默认1M
| 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     |
+------------------------------+---------+

query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报（既是选项又是变量，支持动态更改，全局模式）

直接set设置
MariaDB [(none)]> set global query_cache_size=10240000;
(不支持10M写法，配置文件可以）
Query OK, 0 rows affected (0.01 sec)
或写到配置文件
vim /etc/my.cnf
[mysqld]
query_cache_size=10M

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

优化缓存流程

Linux:MySQL（八）服务器选项变量、缓存及索引

查询缓存

查询缓存相关的状态变量：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 数量

命中率和内存使用率估算

查询缓存中内存块的最小分配单位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缓冲池命中率计算：
平均每次读取的字节数：

总结

设置set global query_cache_size=10240000即可，工作中按需增加大小

索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现
优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点：
占用额外空间，影响插入速度

索引类型：

B+ TREE、HASH、R TREE
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

B-tree

根节点，叶子节点都放数据
元素个数=分支个数+1
叶子之间没有联系
效率不稳定，看运气。

B+tree

根节点不放数据，叶子节点放数据，元素数更多，层数更少，更矮。
元素个数=分支个数
叶子之间有连接关系
效率稳定。

Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
适用场景：只支持等值比较查询，包括=, <=>, IN()
不适合使用hash索引的场景
不适用于顺序查询：索引存储顺序的不是值的顺序
不支持模糊匹配
不支持范围查询
不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

索引

空间数据索引R-Tree（ Geospatial indexing ）
MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持
全文索引(FULLTEXT)
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持

聚簇和非聚簇索引

聚簇索引：索引和数据放在一起

非聚簇索引：索引和数据不放在一起

mysql 是myisam 非聚簇索引 .frm表结构，还有一个放索引，一个文件放数据

主键和二级索引

主键索引：数据摆放次序和索引次序一致

二级索引：（唯一键）通过主键来定位

管理索引百万级别利用索引才有效率（1万，10万没什么用）

创建索引：
CREATE INDEX index_name ON tbl_name (index_col_name[(length)],…);
help CREATE INDEX;

MariaDB [hellodb]>create index idx_numstudent on classes(NumOfStu);

删除索引：
DROP INDEX index_name ON tbl_name;

MariaDB [hellodb]> drop index idx_numstudent on classes;

查看索引：
SHOW INDEXES FROM [db_name.]tbl_name;

MariaDB [hellodb]>show indexes from classes\G;

优化表空间：
OPTIMIZE TABLE tb_name;
查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

show variables like 'userstat'; 查看是否打开
set global userstat=1 打开开关
show index_statistics;显示索引统计 查看索引使用次数，是否使用率高

B+Tree索引 balanced

B+Tree索引的限制：
- 如不从最左列开始，则无法使用索引
- 不能跳过索引中的列
- 如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引
特别提示：
- 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
- 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

索引

冗余和重复索引：
冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引
索引优化策略：
- 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
- 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
  索引选择性：不重复的索引值和数据表的记录总数的比值
- 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
- 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引
对于有多个列where或者order by子句，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作

SQL语句性能优化

查询时，不要用*，尽量写全字段名
大部分情况连接效率远大于子查询
多表连接时，尽量小表驱动大表，即小表 join 大表
在有大量记录的表分页时使用limit
对于经常使用的查询，可以开启缓存
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化

Linux:MySQL（八）服务器选项变量、缓存及索引

服务器配置

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

一、msqld选项

二、服务器系统变量（全局和会话）

三、服务器状态变量（全局和会话）

服务器变量SQL_MODE

查询缓存

查询缓存

查询缓存

查询缓存相关的服务器变量

查询缓存

优化缓存流程

查询缓存

命中率和内存使用率估算

InnoDB存储引擎

总结

索引

索引类型：

B-tree

B+tree

Hash索引

索引

聚簇和非聚簇索引

主键和二级索引

管理索引 百万级别利用索引才有效率（1万，10万没什么用）

B+Tree索引 balanced

索引

索引优化建议

SQL语句性能优化

相关推荐

管理索引百万级别利用索引才有效率（1万，10万没什么用）