MYSQL性能调优＜性能监控，存储引擎,表空间，磁盘读写，内存使用＞

╰半橙微兮° 2023-10-08 15:54 1阅读 0赞

### 性能监控 ###

查看链接信息

myusql > show status like '%connect%';
    Aborted_connects     尝试连接到MySQL服务器失败的次数，
    Threads_connected    当前打开的连接的数量
    Connections		     表示MySQL从启动至今，成功建立连接的连接数，这个值是不断累加的。
    max_connect_errors   允许单用户连接错误最大值，超过后在不刷新状态的情况下，禁止该用户新连接
    max_connections      实例最大连接数限制
    max_user_connections 但用户连接最大限制，默认0表示无限制
    connect_timeout     用户连接超时限制，超过10秒，如果依旧无法连接到mysql，则终止连接

连接线程参数(thread variabls and status)

mysql>  show variables like 'thread%'; 
    thread_cache_size    设置连接线程缓存的数目。这个缓存相当于MySQL线程的缓存池
    thread_handling      默认值是： one-thread-per-connection 表示为每个连接提供或者创建一个线程来处理请求，直至请求完毕，连接销毁或者存入缓存池。当值是no-threads 时，表示在始终只提供一个线程来处理连接，一般是单机做测试使用的。
    thread_stack stack   是堆的意思
    thread_concurrency   参数用于向操作系统建议期望的并发线程数，mysql 5.6后不再使用

查看正在执行的链接进程信息
    
    mysql > show processlist;   常用
    关闭正在执行的链接进程
    mysql> kill ID值;

**性能调优除了预先能知道的点，还有绝大部分需要在性能监控后做出对应的优化**

1、show profile
    使用show profile查询剖析工具，指定具体的type。此工具默认是禁用的，通过服务器变量在绘画级别动态修改
    set profiling=1;
    当设置完成之后，会测量其耗费的时间和其他一些查询执行状态变更相关的数据，使用select * from actor查看。 
    
    也可以使用如下命令查看具体信息
    show profiles;
    
    执行如下命令可以查看详细的每个步骤的时间：
    show profile for query 1;
    
    show profile 具体使用如下：
    (1)  all：显示所有性能信息	show profile all for query n
    
    (2)  block io：显示块io操作的次数	show  profile block io for query n
    
    (3)  context switches：显示上下文切换次数，被动和主动	show profile context switches for query n
    
    (4)  cpu：显示用户cpu时间、系统cpu时间	show profile cpu for query n
    
    (5)  IPC：显示发送和接受的消息数量	show profile ipc for query n
    
    (6)  page faults：显示页错误数量	show profile page faults for query n
    
    (7)  source：显示源码中的函数名称与位置	show profile source for query n
    
    (8)  swaps：显示swap的次数	show profile swaps for query n
    
    2、performance schema
    	performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
    3、show processlist
    	查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。
    	id表示session id
    	user表示操作的用户
    	host表示操作的主机
    	db表示操作的数据库
    	command表示当前状态（sleep：线程等待客户端发送新请求、query：线程正执行查询或正在发送结果、
    		locked：在mysql的服务层，该线程正在等待表锁、analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划、
    		Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中、sorting result：线程正在对结果集进行排序、
    		sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据）
    	info表示详细的sql语句
    	time表示相应命令执行时间
    	state表示命令执行状态

### 1，mysql存储引擎的选择 ###

1，MyISAM：仅支持表锁，只有它支持全文索引(5.6版本后InnoDB也支持)；
    	使用B+Tree作为索引结构，索引文件仅仅保存数据记录的地址；
    	非聚簇索引，主索引和辅助索引在结构上没有任何区别。
    2，InnoDB：默认引擎，支持行锁，只有它支持事务和外键
    	聚簇索引，必须有主键，叶节点data域保存了完整的数据记录，数据文件本身就是索引文件
    3，MEMORY：RAM存储，只有它支持哈希索引
    	存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。
    4，Archive：只支持insert、replace和select
        支持行级锁和专用的缓存区，可实现高并发
        只允许在自增ID列上加索引， 支持分区，不支持事务处理
    
    修改存储引擎：
    不繁忙的表：ALTER TABLE mytable ENGINE = InnoDB;
    	（执行时间较长。MySQL会按行将数据从原表复制到一张新表中，在复制过程中会消耗系统所有的I/O能力，同时会对原表加上读锁） 
    繁忙的表：CREATE TABLE innodb_table LIKE myisam_table;
    	ALTER TABLE innodb_table ENGINE=InnoDB;
    	INSERT INTO innodb_table SELECT * FROM myisam_table;

总结：  
一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，  
使用合适的存储引擎，将会提高整个数据库的性能。  
选用InnoDB：数据安全，并发控制（大部分业务功能的主要记录表）  
选用MyISAM：读写为主，快速访问（大部分业务功能的操作记录表）  
选用MEMORY：涉及数据比较少，安全性要求不高，且需要进行快速访问  
选用Archive：高并发的插入操作。非常适合存储归档数据，如记录日志信息

### 2，为每个innodb表开启innodb\_flush\_method ###

innodb_file_per_table参数设置开启后innodb可以把每个表的数据单独保存。方便管理又能提升性能。
    
    1，没有innodb_file_per_table之前所有的innodb表数据都统一保存到innodb系统表空间文件中的，在drop table ，truncate table或部分alter table（inplace可优化一部分）后表空间文件并不会进行收缩。
    2，在linux系统中不允许并行的对一个文件进行写入(innodb_flush_method=O_DIRECT的情况下是这样的)，这就可能成为一个性能问题
    3，对于使用innodb_file_per_table=ON的情况下，默认创建出来的ibd文件的格式是Barracuda，
    	在这个文件格式下innodb数据行的格式就可以设置为compressed 或 dynamic 格式了。
    	compressed 提供压缩功能节约空间，dynamic能优化对blob,text这样的数据类型的存储以提升性能。

### 3，尽可能的保证从内存中读取数据 ###

1，按存储量设置innodb_buffer_pool_size的容量。这样可以完全从内存中读取数据，最大限度减少磁盘操作。
    1.1，如何确定innodb_buffer_pool_size足够大
    	执行：SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
    	其中Innodb_buffer_pool_pages_free为0，则说明buffer pool 已用光，需要增大innodb_buffer_pool_size
    1.2，用iostat -d -x -k 1 命令，查看硬盘的操作。	
    1.3 检查服务器上是否有足够内存用来增大innodb_buffer_pool_size
    	执行echo 1 > /proc/sys/vm/drop_caches清除操作系统的文件缓存，可看到真正的内存使用量。
    	
    2，数据预热
    	默认情况，某条数据被读取一次，才会缓存在innodb_buffer_pool。
    	所以，数据库刚启动，需进行数据预热，将磁盘上的所有数据缓存到内存中，以提高读取速度。
    2.1，对于mysql5.0版本，方法比较简单，执行下面sql：
    	SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES;
    2.2，对于mysql5.1版本，执行下面sql，并执行该sql的结果来进行预热：
    	SELECT DISTINCT
        CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
        ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
        FROM
        (
            SELECT
                engine,table_schema db,table_name tb,
                index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
            FROM
            (
                SELECT B.engine,A.table_schema,A.table_name,
    				A.index_name,A.column_name,A.seq_in_index
                FROM
                    information_schema.statistics A INNER JOIN
                    (
                        SELECT engine,table_schema,table_name
                        FROM information_schema.tables WHERE
                        engine='InnoDB'
                    ) B USING (table_schema,table_name)
                WHERE B.table_schema NOT IN ('information_schema','mysql')
                ORDER BY table_schema,table_name,index_name,seq_in_index
            ) A
            GROUP BY table_schema,table_name,index_name
        ) AA
    	ORDER BY db,tb;
    2.3，人为SQL预热
    	加载主键索引：select count(*) from tbl where no_index_col=0;
    	加载非主键索引：select count(*) from tbl where index_col like “%0%”;
    	加载BLOB/TEXT列：select count(*) from tbl where blob_col like “%0%”；
    	分段加载大表数据：select count(*) from tbl where id between 1 and 10000000 and 	no_index_col=0;
    
    3，避免使用swap（虚拟内存，当内存不足的时候，把一部分硬盘空间虚拟成内存使用）
    3.1，专用MYSQL服务器，可以禁用SWAP
    	/proc/sys/vm/swappiness的内容改成0(临时)，
    	/etc/sysctl.conf上添加vm.swappiness=0(永久)
    	此参数决定了Linux是倾向用swap，还是倾向释放文件系统cache。
    	在内存紧张的情况下，数值越低越倾向于释放文件系统cache。（只能减少使用swap的概率，并不能避免Linux使用swap）
    3.2，共享服务器
    3.2.1，MySQL的配置参数innodb_flush_method，开启O_DIRECT模式。
    	此时InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但redo log依旧会使用文件系统cache。
    	因Redo log是覆写模式的，虽使用了文件系统的cache，但占用少。
    3.2.2，添加MySQL的配置参数memlock
    	此参数可强迫mysqld进程的地址空间一直锁定在物理内存。但必须要用root帐号来启动MySQL才能生效。

### 4，定期重建数据库 ###

缘由：使用InnoDB引擎时，ibdata1(InnoDB的共有表空间)这个文件会随着数据库使用时间增长，从而过度占用磁盘空间
    解决：1、停止业务，备份一次全库
        mysqldump -uroot -ppassword --all-databases --add-dorp-table > /root/all_mysql.sql
    2、备份完成，停止数据库
        systemctl stop mariadb 或者 service mysqld stop
    3、修改配置文件
        在[mysqld]下增加下面配置 innodb_file_per_table=1 验证配置是否生效，可以重启mysql后,执行 #service mysqld restart
    4、验证
         -uroot -ppassword mysql
        show variables like '%per_table%';
    5、删除ibdata1文件和日志
        rm -rf ibdata1
        rm -rf ib_logfile*
    6、还原数据库
        mysql -uuser -ppassword
        source /root/all_mysql.sql
    注意：
    1，即使innodb_file_per_table=1为每个表建立独有空间。ibdata1依然存储着《InnoDB表的元数据，Buffer，UNDO日志。
    事务回滚，高I/O负载来不及刷新或更新缓冲区也会令其增长》，故需要根据ibdata1的占用量定期重建数据库。
    
    2，单表独立空间增长过快。最根本方法创建新表替换旧表，再删除旧表。
    CREATE TABLE mydb.newtable LIKE mydb.oldtable;
    INSERT INTO mydb.newtable SELECT * FROM mydb.oldtable;
    ALTER TABLE mydb.oldtable RENAME mydb.oldtabledrop;
    ALTER TABLE mydb.newtable RENAME mydb.oldtable;
    DROP TABLE mydb.oldtabledrop;

3，使用Mysqlcheck来检查和修复, 优化表  
[https://blog.csdn.net/orangleliu/article/details/63275154][https_blog.csdn.net_orangleliu_article_details_63275154]

### 5，减少磁盘写入操作 ###

1， 使用足够大的写入缓存innodb_log_file_size
    	注意：用1G的innodb_log_file_size ，假如服务器当机，需10分钟来恢复。
    	推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
    2，innodb_flush_log_at_trx_commit
    	innodb_flush_log_at_trx_commit = 1 则每次修改写入磁盘
    	innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘
    	应用不涉及很高的安全性 (金融系统)，或者基础架构足够安全，或者事务都很小，都可以用0或2来降低磁盘操作。
    4.3 避免双写入缓冲
    	innodb_flush_method=O_DIRECT

### 6，提高磁盘读写速度 ###

根据业务需要使用软RAID0
    
    Raid0（条带存储）优点：1.磁盘空间利用率最高。2.速度最快
    缺点：1.无冗余，一个硬盘损坏，所有数据无法使用。2.不适合关键业务。
    Raid1（镜像/双工）优点：1.数据安全性高
    缺点：1.磁盘利用率50%，利用率最低
    Raid5（条带技术+分布式校验）优点：1.高可用性2.磁盘利用效率较高（N-1），没有固定的校验盘，奇偶校验信息均匀分布在阵列所属的磁盘中3.随机读写性能高允许在同一组进行多个并发操作
    缺点：1.异或校验影响存储性能

各种RAID级别比较  
[https://www.zhangshilong.cn/work/21124.html][https_www.zhangshilong.cn_work_21124.html]

### 7，合理创建并充分使用索引 ###

文章参考 [https://blog.csdn.net/xukaiqiang123/article/details/123841797][https_blog.csdn.net_xukaiqiang123_article_details_123841797]

使用自动加索引的框架或者自动拆分表结构的框架
    比如，Rails 这样的框架，会自动添加索引，Drupal 这样的框架会自动拆分表结构

### 收藏加关注，再来不迷路！！！ ###

[https_blog.csdn.net_orangleliu_article_details_63275154]: https://blog.csdn.net/orangleliu/article/details/63275154
[https_www.zhangshilong.cn_work_21124.html]: https://www.zhangshilong.cn/work/21124.html
[https_blog.csdn.net_xukaiqiang123_article_details_123841797]: https://blog.csdn.net/xukaiqiang123/article/details/123841797