mysql数据优化

目录

1、索引优化

2、查看每个客户端IP过来的连接消耗了多少资源

3、 查看某个数据文件上发生了多少IO请求

4、查看每个用户消耗了多少资源

5、查看总共分配了多少内存

6、数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？查看当前连接情况

7、查看当前正在执行的SQL和执行show full processlist的效果相当

8、数据库中哪些SQL被频繁执行？ 执行下面命令查询TOP10 SQL

9、哪个文件产生了最多的IO，读多，还是写的多

10、哪个表上的IO请求最多

11、哪个表被访问的最多

12、哪些SQL执行了全表扫描，如果没有使用索引，则考虑为大型表添加索引 

13、列出所有做过排序的规范化语句

14、哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表 

15、列出所有使用临时表的语句——访问最高的磁盘临时表，然后访问内存临时表

16、哪个表占用了最多的buffer pool

17、每个库（database）占用多少buffer pool 

---

1、索引优化

create index 索引名 on 表名 (列名[(length)]);  #普通索引

create unique index 索引名 on 表名 (列名);    #唯一索引


create table 表名 (字段1 数据类型，字段2 数据类型,primary key (列名));   #主键索引


create table 表名 (字段1 数据类型，字段2 数据类型,index 索引名 (列名1，列名2));  #组合索引


create fulltext index 索引名 on 表名 (列名);    #全文索引

#查看索引
show index from 表名;
show index from 表名\G； 竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;

2、查看每个客户端IP过来的连接消耗了多少资源

mysql> select * from sys.x$host_summary;

3、 查看某个数据文件上发生了多少IO请求

mysql> select * from sys.x$io_global_by_file_by_bytes;

4、查看每个用户消耗了多少资源

mysql> select * from sys.x$user_summary;

5、查看总共分配了多少内存

mysql> select * from sys.x$memory_global_total;

6、数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？查看当前连接情况

mysql> select host, current_connections, statements from sys.x$host_summary;

7、查看当前正在执行的SQL和执行show full processlist的效果相当

mysql> select conn_id, user, current_statement, last_statement from sys.x$session;

8、数据库中哪些SQL被频繁执行？ 执行下面命令查询TOP10 SQL

mysql> select db,exec_count,query from sys.x$statement_analysis order by exec_count desc limit 10;

9、哪个文件产生了最多的IO，读多，还是写的多

mysql> select * from sys.x$io_global_by_file_by_bytes limit 10;

10、哪个表上的IO请求最多

mysql> select * from sys.x$io_global_by_file_by_bytes where file like "%ibd" order by total desc limit 10;

11、哪个表被访问的最多

先访问statement_analysis，根据热门SQL排序找到相应的数据表。 
哪些语句延迟比较严重？ 
查看statement_analysis中avg_latency的最高的SQL。 
mysql> select * from sys.x$statement_analysis order by avg_latency desc limit 10;

12、哪些SQL执行了全表扫描，如果没有使用索引，则考虑为大型表添加索引 

mysql> select * from sys.x$statements_with_full_table_scans;

13、列出所有做过排序的规范化语句

mysql> select * from sys.x$statements_with_sorting;

14、哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表 

查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可。 
mysql> select db, query, tmp_tables, tmp_disk_tables from sys.x$statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;

15、列出所有使用临时表的语句——访问最高的磁盘临时表，然后访问内存临时表

mysql>select * from sys.statements_with_temp_tables;

16、哪个表占用了最多的buffer pool

mysql> select * from sys.x$innodb_buffer_stats_by_table order by allocated desc limit 10;

17、每个库（database）占用多少buffer pool 

mysql> select * from sys.x$innodb_buffer_stats_by_schema order by allocated desc limit 10;

select table_name,TABLE_COMMENT,CONCAT(TRUNCATE(data_length / 1024 / 1024/ 1024, 4),\"GB\") AS data_size,table_rows from information_schema.tables where table_schema="dbname"