我要评论mysql性能调优
mysql 性能调优是一个复杂且多维度的过程，下面从数据库设计、查询优化、配置参数调整、硬件优化几个方面为你介绍相关的调优方法。
数据库设计优化
- 合理设计表结构：确保表结构遵循数据库设计范式，减少数据冗余，同时要根据实际业务需求灵活调整，避免过度范式化导致的查询复杂度过高。
- 选择合适的数据类型：使用合适的数据类型可以减少存储空间，提高查询性能。例如，对于固定长度的字符串使用
char，对于可变长度的字符串使用varchar；对于整数类型，根据取值范围选择合适的类型，如tinyint、smallint等。 - 建立适当的索引：索引可以加快数据的查找速度，但过多的索引会增加写操作的开销，因此需要根据查询需求建立适当的索引。例如，对于经常用于
where子句、join条件和order by子句的列，可以考虑创建索引。
查询优化
- 避免全表扫描：尽量使用索引来避免全表扫描，例如在
where子句中使用索引列进行过滤。 - 优化子查询：子查询可能会导致性能问题，可以考虑使用
join来替代子查询。 - 减少不必要的列：在查询时只选择需要的列，避免使用
select *。
配置参数调整
- 调整内存分配：根据服务器的硬件资源和业务需求，调整
innodb_buffer_pool_size、key_buffer_size等参数，以提高缓存命中率。 - 调整日志参数：根据业务需求调整
log_bin、innodb_log_file_size等参数，以平衡数据安全性和性能。
硬件优化
- 使用高速存储设备：如 ssd 可以显著提高磁盘 i/o 性能。
- 增加内存：足够的内存可以减少磁盘 i/o，提高查询性能。
mysql中的系统库
1.3.sys系统库
1.3.1.sys使用须知
sys系统库支持mysql 5.6或更高版本，不支持mysql 5.5.x及以下版本。
sys系统库通常都是提供给专业的dba人员排查一些特定问题使用的，其下所涉及的各项查询或多或少都会对性能有一定的影响。
因为sys系统库提供了一些代替直接访问performance_schema的视图，所以必须启用performance_schema（将performance_schema系统参数设置为on），sys系统库的大部分功能才能正常使用。
同时要完全访问sys系统库，用户必须具有以下数据库的管理员权限。
如果要充分使用sys系统库的功能，则必须启用某些performance_schema的功能。比如：
启用所有的wait instruments：
call sys.ps_setup_enable_instrument('wait');
启用所有事件类型的current表：
call sys.ps_setup_enable_consumer('current');
注意： performance_schema的默认配置就可以满足sys系统库的大部分数据收集功能。启用所有需要功能会对性能产生一定的影响，因此最好仅启用所需的配置。
1.3.2.sys系统库使用
如果使用了use语句切换默认数据库，那么就可以直接使用sys系统库下的视图进行查询，就像查询某个库下的表一样操作。也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式，在不指定默认数据库的情况下访问sys 系统库中的对象（这叫作名称限定对象引用）。
在sys系统库下包含很多视图，它们以各种方式对performance_schema表进行聚合计算展示。这些视图大部分是成对出现的，两个视图名称相同，但有一个视图是带 x $前缀的.$
host_summary_by_file_io和 x$host_summary_by_file_io
代表按照主机进行汇总统计的文件i/o性能数据，两个视图访问的数据源是相同的，但是在创建视图的语句中，不带x$前缀的视图显示的是相关数值经过单位换算后的数据（单位是毫秒、秒、分钟、小时、天等），带 x$ 前缀的视图显示的是原始的数据（单位是皮秒）。
1.3.3.查看慢sql语句慢在哪里
如果我们频繁地在慢查询日志中发现某个语句执行缓慢，且在表结构、索引结构、统计信息中都无法找出原因时，则可以利用sys系统库中的撒手锏：sys.session视图结合performance_schema的等待事件来找出症结所在。那么session视图有什么用呢？使用它可以查看当前用户会话的进程列表信息，看看当前进程到底再干什么，注意，这个视图在mysql 5.7.9中才出现。
首先需要启用与等待事件相关功能：
call sys.ps_setup_enable_instrument('wait');
call sys.ps_setup_enable_consumer('wait');
然后模拟一下：
一个session中执行
select sleep(30);
另外一个session中在sys库中查询：
select * from session where command='query' and conn_id !=connection_id()\g
查询表的增、删、改、查数据量和i/o耗时统计
select * from schema_table_statistics_with_buffer\g
1.3.4.小结
除此之外，通过sys还可以查询查看innodb缓冲池中的热点数据、查看是否有事务锁等待、查看未使用的，冗余索引、查看哪些语句使用了全表扫描等等。
具体可以参考官网：mysql :: mysql 5.7 reference manual :: 26 mysql sys schema
1.4.information_schema
1.4.1.什么是information_schema
information_schema提供了对数据库元数据、统计信息以及有关mysql server信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为mysql的数据字典或系统目录。
在每个mysql 实例中都有一个独立的information_schema，用来存储mysql实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用use语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行insert、update、delete等数据变更操作。
针对information_schema下的表的查询操作可以替代一些show查询语句（例如：show databases、show tables等）。
注意：根据mysql版本的不同，表的个数和存放是有所不同的。在mysql 5.6版本中总共有59个表，在mysql 5.7版本中，该schema下总共有61个表，
在mysql 8.0版本中，该schema下的数据字典表（包含部分原memory引擎临时表）都迁移到了mysql schema下，且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问。
information_schema下的所有表使用的都是memory和innodb存储引擎，且都是临时表，不是持久表，在数据库重启之后这些数据会丢失。在mysql 的4个系统库中，information_schema也是唯一一个在文件系统上没有对应库表的目录和文件的系统库。
1.4.2.information_schema表分类
server层的统计信息字典表
（1）columns
提供查询表中的列（字段）信息。
（2）key_column_usage
提供查询哪些索引列存在约束条件。
该表中的信息包含主键、唯一索引、外键等约束信息，例如：所在的库表列名、引用的库表列名等。该表中的信息与table_constraints表中记录的信息有些类似，但table_constraints表中没有记录约束引用的库表列信息，而key_column_usage表中却记录了table_constraints表中所没有的约束类型。
（3）referential_constraints
提供查询关于外键约束的一些信息。
（4）statistics
提供查询关于索引的一些统计信息，一个索引对应一行记录。
（5）table_constraints
提供查询与表相关的约束信息。
（6）files
提供查询与mysql的数据表空间文件相关的信息。
（7）engines
提供查询mysql server支持的引擎相关信息。
（8）tablespaces
提供查询关于活跃表空间的相关信息（主要记录的是ndb存储引擎的表空间信息）。
注意：该表不提供有关innodb存储引擎的表空间信息。对于innodb表空间的元数据信息，请查询innodb_sys_tablespaces表和innodb_sys_datafiles表。另外，从mysql 5.7.8开始，information_schema.files表也提供查询innodb表空间的元数据信息。
（9）schemata
提供查询mysql server中的数据库列表信息，一个schema就代表一个数据库。
server层的表级别对象字典表
（1）views
提供查询数据库中的视图相关信息。查询该表的账户需要拥有show view权限。
（2）triggers
提供查询关于某个数据库下的触发器相关信息。
（3）tables
提供查询与数据库内的表相关的基本信息。
（4）routines
提供查询关于存储过程和存储函数的信息（不包括用户自定义函数）。该表中的信息与mysql.proc中记录的信息相对应（如果该表中有值的话）。
（5）partitions
提供查询关于分区表的信息。
（6）events
提供查询与计划任务事件相关的信息。
（7）parameters
提供有关存储过程和函数的参数信息，以及有关存储函数的返回值信息。这些参数信息与mysql.proc表中的param_list列记录的内容类似。
server层的混杂信息字典表
（1）global_status、global_variables、session_status、
session_variables
提供查询全局、会话级别的状态变量与系统变量信息。
（2）optimizer_trace
提供优化程序跟踪功能产生的信息。
跟踪功能默认是关闭的，使用optimizer_trace系统变量启用跟踪功能。如果开启该功能，则每个会话只能跟踪它自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一条跟踪的sql语句。
（3）plugins
提供查询关于mysql server支持哪些插件的信息。
（4）processlist
提供查询一些关于线程运行过程中的状态信息。
（5）profiling
提供查询关于语句性能分析的信息。其记录内容对应于show profiles和show profile语句产生的信息。该表只有在会话变量 profiling=1时才会记录语句性能分析信息，否则该表不记录。
注意：从mysql 5.7.2开始，此表不再推荐使用，在未来的mysql版本中删除，改用performance schema代替。
（6）character_sets
提供查询mysql server支持的可用字符集。
（7）collations
提供查询mysql server支持的可用校对规则。
（8）collation_character_set_applicability
提供查询mysql server中哪种字符集适用于什么校对规则。查询结果集相当于从show collation获得的结果集的前两个字段值。目前并没有发现该表有太大的作用。
（9）column_privileges
提供查询关于列（字段）的权限信息，表中的内容来自mysql.column_priv列权限表（需要针对一个表的列单独授权之后才会有内容）。
（10）schema_privileges
提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息，该表中的信息来自mysql.db表。
（11）table_privileges
提供查询关于表级别的权限信息，该表中的内容来自mysql.tables_priv表。
（12）user_privileges
提供查询全局权限的信息，该表中的信息来自mysql.user表。
10.2.4 innodb层的系统字典表
（1）innodb_sys_datafiles
提供查询innodb所有表空间类型文件的元数据（内部使用的表空间id和表空间文件的路径信息），包括独立表空间、常规表空间、系统表空间、临时表空间和undo空间（如果开启了独立undo空间的话）。
该表中的信息等同于innodb数据字典内部sys_datafiles表的信息。
（2）innodb_sys_virtual
提供查询有关innodb虚拟生成列和与之关联的列的元数据信息，等同于innodb数据字典内部sys_virtual表的信息。该表中展示的行信息是与虚拟生成列相关联列的每个列的信息。
（3）innodb_sys_indexes
提供查询有关innodb索引的元数据信息，等同于innodb数据字典内部sys_indexes表中的信息。
（4）innodb_sys_tables
提供查询有关innodb表的元数据信息，等同于innodb数据字典内部sys_tables表的信息。
（5）innodb_sys_fields
提供查询有关innodb索引键列（字段）的元数据信息，等同于innodb数据字典内部sys_fields表的信息。
（6）innodb_sys_tablespaces
提供查询有关innodb独立表空间和普通表空间的元数据信息（也包含了全文索引表空间），等同于innodb数据字典内部sys_tablespaces表的信息。
（7）innodb_sys_foreign_cols
提供查询有关innodb外键列的状态信息，等同于innodb数据字典内部
sys_foreign_cols表的信息。
（8）innodb_sys_columns
提供查询有关innodb表列的元数据信息，等同于innodb数据字典内部
sys_columns表的信息。
（9）innodb_sys_foreign
提供查询有关innodb外键的元数据信息，等同于innodb数据字典内部sys_foreign表的信息。
（10）innodb_sys_tablestats
提供查询有关innodb表的较低级别的状态信息视图。 mysql优化器会使用这些统计信息数据来计算并确定在查询innodb表时要使用哪个索引。这些信息保存在内存中的数据结构中，与存储在磁盘上的数据无对应关系。在innodb内部也无对应的系统表。
innodb层的锁、事务、统计信息字典表
（1）innodb_locks
提供查询innodb引擎中事务正在请求的且同时被其他事务阻塞的锁信息（即没有发生不同事务之间锁等待的锁信息，在这里是查看不到的。例如，当只有一个事务时，无法查看到该事务所加的锁信息）。该表中的内容可用于诊断高并发下的锁争用信息。
（2）innodb_trx
提供查询当前在innodb引擎中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁、事务什么时间点开始，以及事务正在执行的sql语句文本信息等（如果有sql语句的话）。
（3）innodb_buffer_page_lru
提供查询缓冲池中的页面信息。与innodb_buffer_page表不同，innodb_buffer_page_lru表保存有关innodb缓冲池中的页如何进入lru链表，以及在缓冲池不够用时确定需要从中逐出哪些页的信息。
（4）innodb_lock_waits
提供查询innodb事务的锁等待信息。如果查询该表为空，则表示无锁等待信息；如果查询该表中有记录，则说明存在锁等待，表中的每一行记录表示一个锁等待关系。在一个锁等待关系中包含：一个等待锁（即，正在请求获得锁）的事务及其正在等待的锁等信息、一个持有锁（这里指的是发生锁等待事务正在请求的锁）的事务及其所持有的锁等信息。
（5）innodb_temp_table_info
提供查询有关在innodb实例中当前处于活动状态的用户（只对已建立连接的用户有效，断开的用户连接对应的临时表会被自动删除）创建的innodb临时表的信息。它不提供查询优化器使用的内部innodb临时表的信息。该表在首次查询时创建。
（6）innodb_buffer_page
提供查询关于缓冲池中的页相关信息。
（7）innodb_metrics
提供查询innodb更为详细的性能信息，是对innodb的performance_schema的补充。通过对该表的查询，可用于检查innodb的整体健康状况，也可用于诊断性能瓶颈、资源短缺和应用程序的问题等。
（8）innodb_buffer_pool_stats
提供查询一些innodb缓冲池中的状态信息，该表中记录的信息与show engineinnodb status语句输出的缓冲池统计部分信息类似。另外，innodb缓冲池的一些状态变量也提供了部分相同的值。
innodb层的全文索引字典表
（1）innodb_ft_config
（2）innodb_ft_being_deleted
（3）innodb_ft_deleted
（4）innodb_ft_default_stopword
（5）innodb_ft_index_table
innodb层的压缩相关字典表
（1）innodb_cmp和innodb_cmp_reset
这两个表中的数据包含了与压缩的innodb表页有关的操作状态信息。表中记录的数据为测量数据库中的innodb表压缩的有效性提供参考。
（2）innodb_cmp_per_index和innodb_cmp_per_index_reset
这两个表中记录了与innodb压缩表数据和索引相关的操作状态信息，对数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性能和实用性提供参考数据。
（3）innodb_cmpmem和innodb_cmpmem_reset
这两个表中记录了innodb缓冲池中压缩页的状态信息，为测量数据库中innodb表压缩的有效性提供参考。
1.4.3.information_schema应用
查看索引列的信息
innodb_sys_fields表提供查询有关innodb索引列（字段）的元数据信息，等同于innodb数据字典中sys_fields表的信息。
innodb_sys_indexes表提供查询有关innodb索引的元数据信息，等同于innodb数据字典内部sys_indexes表中的信息。
innodb_sys_tables表提供查询有关innodb表的元数据信息，等同于innodb数据字典中sys_tables表的信息。
假设需要查询lijin库下的innodb表order_exp的索引列名称、组成和索引列顺序等相关信息，
则可以使用如下sql语句进行查询
select t. name as d_t_name, i. name as i_name, i.type as i_type, i.n_fields as i_column_numbers, f. name as i_column_name, f.pos as i_position from innodb_sys_tables as t join innodb_sys_indexes as i on t.table_id = i.table_id left join innodb_sys_fields as f on i.index_id = f.index_id where t. name = 'lijin/order_exp';
结果中的列都很好理解，唯一需要额外解释的是i_type(innodb_sys_indexes.type)，它是表示索引类型的数字id：
0 =二级索引
1=集群索引
2 =唯一索引
3 =主键索引
32 =全文索引
64 =空间索引
128 =包含虚拟生成列的二级索引。
到此这篇关于mysql中的系统库(sys系统库、information_schema)调优方法的文章就介绍到这了,更多相关mysql sys系统库 information_schema介绍内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论