性能优化:
(1)查看状态:
show status like 'connections';链接次数
show status like 'uptime';服务器上线时间。
show status like 'Slow_queries'。本次服务器上线慢查询次数
show status like 'Com_select':本次服务器上线select次数。
show status like 'com_insert';
show status like 'com_update';
show status like 'com_delete';
| Innodb_rows_deleted | 1 |
| Innodb_rows_inserted | 1 |
| Innodb_rows_read | 14 |
| Innodb_rows_updated | 0 |
Innodb_rows记录历史总数。
(2)分析查询语句:explain/describe
mysql> explain select * from suremployer;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | suremployer | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
mysql> describe select * from suremployer where num=201500007 \G;
id: 1
select_type: SIMPLE简单查询,还有PRIMARY主查询,最外层查询语句,UNION链接查询的第二条或后面的查询语句
table: suremployer查询的表名
type: const----all完整扫描。index对字段完整扫描,const只会一条返回。
possible_keys: PRIMARY可能用到的索引
key: PRIMARY使用到的索引。
key_len: 4索引字段的长度
ref: const参考的哪个列或常数 与 索引一起进行查询。这里是用常数与索引进行查询。
rows: 1查询行数。没有索引的话,有多少行数据将查询多少次。使用索引后这里就是1.
Extra: NULL使用的附加语句:using where
---------解释一下:num在本表中时PRIMARY KEY,用它来查找就是建立了索引。
(3)索引就是利用字段关键字来建立B-TREE,HASH table,实现快速查找的。
---'%'对索引的影响:'%'不再首位时,索引生效。'LIK%'查询rows=1,'%lik'查询rows=10。
---建立多字段索引,只有第一个字段索引生效。
---使用OR,只要OR其中一个条件不是索引,则不索引。
(4)子查询:效率不高(如多个select)。
(5-1)一张表字段不要太多,太多分成小表。
(5-2)多表联合查询,效率低,可建立中间表。如一个表students保存学生基本信息,一个表scores保存学生成绩。经常查询学生ID,名字和成绩,可以先建立一个中间表mid,保存学生ID,名字和成绩,然后将2表的这些信息保存到mid中,便于查询。
create table mid(id int,name varchar(30),score float);
insert into mid select a.id,a.name,b.score from students as a,scores as b where a.id=b.id;
(5-3)多表联合查询,效率低。还可以将经常查询的字段添加到另外一个表中,增加了沉余字段,但速度提高。
(6)优化插入语句:
(6-1)在插入语句时会对索引进行修改,大量插入导致效率降低。可以先停止索引---进行插入---开启索引。
ALTER TALBE mytable DISABLE KEYS;
ALTER TALBE mytable ENABLE KEYS;
(6-2)MYSQL对插入数据进行 <唯一校验> 也会影响效率。可以先停止唯一校验--进行插入---开启唯一校验-。
set unique_checks=0
set unique_checks=1
(6-2)一次插入多行 要比 多次插入 效率高。如:
insert into mytable values(1,2,3),(4,5,6),(7,8,9);一次插入多行。
load data infile 导入表格是很快的。
(7)优化表:以下操作过程都会对表加锁,不能更新,插入,删除。只能查询。
analyze table employer;分析表
check table employer;检查表
optimize table employer;对字符串等一些磁盘空间进行整理。
---注意在使用optimize在InnoDB中可能出现错误:Table does not support optimize, doing recreate + analyze instead;需要重新启动mysql服务器:service mysqld restart --skip-new;
(8)提高内存和磁盘的存取速度。
(9)修改variables里面参数,可以将这些参数写到my.cnf中。
key_buffer_size:索引缓存大小,值大,索引查询速度块。
table_open_cache:打开表格个数。如2000表示缓存区最多可容纳2000个table。
query_cache_size,query_cache_type:我show时为0;
max_connections:最大连接数。
sort_buffer_size:排序缓存区
read_buffer_size:为线程保留的缓存,连续读时用到。
innodb_buffer_pool_size:innoDB格式表和索引的最大缓存。
innodb_flush_log_at_trx_commit:缓冲区写入日志,并将日志写入磁盘的策略。0:每1秒写入日志写入磁盘。1:每次事务写入日志磁盘。2:每次事务写磁盘,每1秒写磁盘。
(1)查看状态:
show status like 'connections';链接次数
show status like 'uptime';服务器上线时间。
show status like 'Slow_queries'。本次服务器上线慢查询次数
show status like 'Com_select':本次服务器上线select次数。
show status like 'com_insert';
show status like 'com_update';
show status like 'com_delete';
| Innodb_rows_deleted | 1 |
| Innodb_rows_inserted | 1 |
| Innodb_rows_read | 14 |
| Innodb_rows_updated | 0 |
Innodb_rows记录历史总数。
(2)分析查询语句:explain/describe
mysql> explain select * from suremployer;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | suremployer | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
mysql> describe select * from suremployer where num=201500007 \G;
id: 1
select_type: SIMPLE简单查询,还有PRIMARY主查询,最外层查询语句,UNION链接查询的第二条或后面的查询语句
table: suremployer查询的表名
type: const----all完整扫描。index对字段完整扫描,const只会一条返回。
possible_keys: PRIMARY可能用到的索引
key: PRIMARY使用到的索引。
key_len: 4索引字段的长度
ref: const参考的哪个列或常数 与 索引一起进行查询。这里是用常数与索引进行查询。
rows: 1查询行数。没有索引的话,有多少行数据将查询多少次。使用索引后这里就是1.
Extra: NULL使用的附加语句:using where
---------解释一下:num在本表中时PRIMARY KEY,用它来查找就是建立了索引。
(3)索引就是利用字段关键字来建立B-TREE,HASH table,实现快速查找的。
---'%'对索引的影响:'%'不再首位时,索引生效。'LIK%'查询rows=1,'%lik'查询rows=10。
---建立多字段索引,只有第一个字段索引生效。
---使用OR,只要OR其中一个条件不是索引,则不索引。
(4)子查询:效率不高(如多个select)。
(5-1)一张表字段不要太多,太多分成小表。
(5-2)多表联合查询,效率低,可建立中间表。如一个表students保存学生基本信息,一个表scores保存学生成绩。经常查询学生ID,名字和成绩,可以先建立一个中间表mid,保存学生ID,名字和成绩,然后将2表的这些信息保存到mid中,便于查询。
create table mid(id int,name varchar(30),score float);
insert into mid select a.id,a.name,b.score from students as a,scores as b where a.id=b.id;
(5-3)多表联合查询,效率低。还可以将经常查询的字段添加到另外一个表中,增加了沉余字段,但速度提高。
(6)优化插入语句:
(6-1)在插入语句时会对索引进行修改,大量插入导致效率降低。可以先停止索引---进行插入---开启索引。
ALTER TALBE mytable DISABLE KEYS;
ALTER TALBE mytable ENABLE KEYS;
(6-2)MYSQL对插入数据进行 <唯一校验> 也会影响效率。可以先停止唯一校验--进行插入---开启唯一校验-。
set unique_checks=0
set unique_checks=1
(6-2)一次插入多行 要比 多次插入 效率高。如:
insert into mytable values(1,2,3),(4,5,6),(7,8,9);一次插入多行。
load data infile 导入表格是很快的。
(7)优化表:以下操作过程都会对表加锁,不能更新,插入,删除。只能查询。
analyze table employer;分析表
check table employer;检查表
optimize table employer;对字符串等一些磁盘空间进行整理。
---注意在使用optimize在InnoDB中可能出现错误:Table does not support optimize, doing recreate + analyze instead;需要重新启动mysql服务器:service mysqld restart --skip-new;
(8)提高内存和磁盘的存取速度。
(9)修改variables里面参数,可以将这些参数写到my.cnf中。
key_buffer_size:索引缓存大小,值大,索引查询速度块。
table_open_cache:打开表格个数。如2000表示缓存区最多可容纳2000个table。
query_cache_size,query_cache_type:我show时为0;
max_connections:最大连接数。
sort_buffer_size:排序缓存区
read_buffer_size:为线程保留的缓存,连续读时用到。
innodb_buffer_pool_size:innoDB格式表和索引的最大缓存。
innodb_flush_log_at_trx_commit:缓冲区写入日志,并将日志写入磁盘的策略。0:每1秒写入日志写入磁盘。1:每次事务写入日志磁盘。2:每次事务写磁盘,每1秒写磁盘。