数据库管理员可以使用 SHOW STATUS 语句查询MySQL数据库的性能,语法格式如下:
SHOW STATUS LIKE 'VALUE';
其中,VALUE参数如下:
- Connections:连接MySQL服务器的次数;
- Uptime:MySQL服务器的上线时间;
- Slow_queries:慢查询的次数;
- Com_select :查询操作的次数;
- Com_insert:插入操作的次数;
- Com_delect:删除操作的次数
分析查询语句
EXPLAIN SELECT 语句
其中各字段所代表的意义如下:
- id列:指出在整个查询中SELECT 的位置
- select_type列:
- table列:存放所查询的表名
- type列:连接类型,范围从const到ALL
- possible_keys:指出为了提高查找速度,在MySQL中可以使用的索引
- key列:指出实际使用的键
- rows列:指出MySQL需要在相应表中返回查询结果所检验的行数
- Extra列:包含一些其他信息,设计MySQL如何处理查询
在MySQL中,也可以使用 DESCRIBE 语句来分析查询语句。DESCRIBE 语句的使用方法与 EXPLAIN 的语法是相同的,两者的分析结果也大体相同。
DESCRIBE SELECT 语句
查询优化
- 使用索引查询
-
使用关键字 LIKE 优化索引查询
-
使用多列索引
-
使用关键字 OR
使用 OR 时,要求查询的两个字段必须同为索引。如果搜索条件中,有一个字段不为索引,则在查询中不会使用索引。 -
在使用 JOIN 时,要先尽可能多的给出匹配满足条件,尽可能满足 On 的条件,减少where的执行。
-
尽量避免子查询,而用 JOIN。
- 分页查询:LIMIT
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
SELECT S.country,S.city,count(M.name) as cnt FROM (SELECT country,name FROM mingjiang WHERE age >60 LIMIT 0,10000) as M
INNER JOIN sanguo S
ON M.country = S.country AND M.name = S.name
GROUP BY M.country,S.city,M.name
UNION
SELECT S.country,S.city,count(M.name) as cnt FROM (SELECT country,name FROM mingjiang WHERE age >60 LIMIT 10000,20000) as M
INNER JOIN sanguo S
ON M.country = S.country AND M.name = S.name
GROUP BY M.country,S.city,M.name
- 优化数据库结构
-
将字段很多的表分解成多个表:使用频率较低且内容较多的字段可以分解成另外一张表
-
增加中间表:如果经常需要联表查询,可以将经常需要查询的字段建立一张新表;
- 优化插入记录的速度
- 禁用索引:在插入记录之前先禁用索引,等到记录都插入完毕后再开启索引
禁用索引的语句
ALTER TABLE 表名 DISABLE KEYS;
重新开启索引的语句
ALTER TABLE 表名 ENABLE KEYS;
- 禁用唯一性检查:
禁用
SET UNIQUE_CHECKS = 0;
开启
SET UNIQUE_CHECKS = 1;
- 优化 INSERT 语句
插入多条记录时,可以采取两种写 INSERT 语句的方式。第一种是一个 INSERT 语句插入多条记录:
INSERT INTO food VALUES
(NULL, '果冻', 'CC果冻厂', 1.8, '北京'),
(NULL, '咖啡', 'CF咖啡厂', 23, '天津'),
(NULL, '奶糖', '旺仔奶糖厂', 13, '广州');
第二种是一个 INSERT 语句只插入一条记录,执行多个 INSERT 语句:
INSERT INTO food VALUES(NULL, '果冻', 'CC果冻厂', 1.8, '北京');
INSERT INTO food VALUES(NULL, '咖啡', 'CF咖啡厂', 23, '天津');
INSERT INTO food VALUES(NULL, '奶糖', '旺仔奶糖厂', 13, '广州');
- 分析表、检查表和优化表
分析表
ANALYZE TABLE 表名;
检查表
CHECK TABLE 表名;
优化表
OPTIMIZE TABLE 表名;
- 查询高速缓存
在 MySQL 中,用户通过 SELECT 语句查询数据时,该操作将结果集保存到一个特殊的高级缓存中,从而实现查询操作。首次查询后,当用户再次做相同查询操作时,MySQL 即可从高速缓存中检索结果。
- 检查告诉缓存是否开启:
SHOW VARIABLES LIKE '%query_cache%';
- 使用高速缓存
SELECT SQL_CACHE * FROM 表名;
- 优化多表查询
在多表查询中,可以应用子查询来优化多表查询,即在 SELECT 语句中嵌套其他 SELECT 语句。这样,可以将分步查询的结果整合成一个查询,这样就不需要执行多个单独查询。