mysql学习笔记（六）

25性能优化

优化原则：减少系统的瓶颈，减少资源的占用，增加系统的反应速度。

使用show status语句查询mysql数据库的性能参数

show status like ‘value’；

value对应的参数

Connection：连接mysql服务器的次数

Uptime：mysql服务器的上线时间

Slow_queries：慢查询的次数

Com_select：查询操作的次数

Com_insert：插入操作的次数

Com_update：更新操作的次数

Com_delete：删除操作的次数

（1）分析查询语句

用explain和describe

mysql>explain select * from person;

 

mysql>describe select * from person;

 

在使用like关键字查询的查询语句中，如果匹配字符串的第一个字符为“%”，则索引不会起作用，只有“%”不放在第一个位置的时候，索引才会起作用。

 

查询语句的查询条件只有OR关键字，且OR的前后的两个条件中的列都是索引时，查询中才使用索引。

 

(3)优化

对MyISAM引擎的表优化

优化子查询，在mysql中，可以使用连接（JOIN）来代替子查询。

优化数据库结构

将字段多的表分解成多个表

增加中间表

合理增加冗余字段

优化插入记录的速度

①.禁用索引

alter table table_name disable keys；//禁用索引

alter table table_name enable keys；//恢复索引

②.禁止唯一性检查

set unique_checks=0 //禁用

set unique_checks=1 //开启

使用批量插入

使用load data infile批量导入

 

对于InnoDB引擎的表优化

①禁止唯一性检查

set unique_checks=0 //禁用

②禁止外键检查

set foreign_key_checks=0 //禁用

set foreign_key_checks=1 //开启

③禁止自动提交

set autocommit=0 //禁用

set autocommit=1 //开启

 

(3)分析表、检查表和优化表

分析表

mysql>analyze table table_name;

检查表

mysql>check table table_name;

quick、fast、medium、extended、changed

优化表

optimize table table_name;

 

(4)优化mysql服务器

优化硬件

优化mysql的参数

key_buffer_size：表示索引缓冲区的大小。不是越大越好，需要根据内存的大小。如果这个值太大容易导致操作系统频繁换页，也会降低系统性能。

table_cache:表示同时打开表的个数。数值越大表示打开的表的个数越多，不是越大也好，数值到会影响系统的性能。

query_cache_size：表示查询缓冲区的大小。需要配合query_cache_type一起使用，当query_cache_type为0时，表示不使用缓冲区；当query_cache_type=1时，表示所有查询都使用缓冲区，除非在查询语句中指定SQL_NO_CACHE,如select sql_no_cache * from tb1_name;当query_cache_type=2时，只有在查询语句中使用了SQL_CACHE关键字才能使用查询缓冲区，使用查询缓冲区可以提高查询的速度，只适用于修改操作少且经常执行重复操作的。

sort_buffer_size：表示排序缓冲区的大小，数值越大，进行排序的速度越快。

read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节），当线程从表中连续读取记录时需要用到这个缓冲区。set session_read_buffer_size=n可以临时设置这个值。

read_rnd_buffer_size：表示每个线程保留的缓冲区的大小，与read_buffer_size相似，但主要用于存储按特定顺序读取出来的记录，也可以使用set session_read_buffer_size=n可以临时设置这个值，如果频繁的进行多次扫描，可以增加改值。

innodb_buffer_pool_size：表示innodb类型的表和索引的最大缓存，这个值越大，查询速度会越快，但是太大会影响操作系统性能。

max_connection：数据库最大连接。

innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入到磁盘中。这个参数对innodb引擎非常重要。0、1、2三个值。

back_log：表示在mysql暂时停止回答新请求之前的短时间内，多少个请求可以存着堆栈中。

interaction_timeout：表示服务器在关闭连接前等待行动的秒数。

sort_buffer_size：表示每个要进行排序的线程分配的缓冲区的大小。增加这个参数可以提高order by 和group by的操作速度。

thread_cache_size：表示可以复用的线程数量。如果有很多新的线程，为了提高性能可以增大改参数的值。

wait_timeout：表示服务器在关闭一个连接时等待行动的秒数。默认值为28800。