Mysql性能优化

最新推荐文章于 2024-07-28 02:48:40 发布

知青先生

最新推荐文章于 2024-07-28 02:48:40 发布

阅读量200

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

浩明

本文链接：https://blog.youkuaiyun.com/YHM_MM/article/details/109713007

数据库专栏收录该内容

14 篇文章

订阅专栏

1、查看表结构

describe 表名

2、载入数据文件

数据格式：
xx xx
data data
load data local infile ‘绝对路径’ into table lines terminated by ‘\r\t’;

3、年份截取year()|月份、日期right()

（year(列名)-year(列名)）-(right(列名,5)<right(列名,5))

4、提取月份 month(列名)|月份加1：

DATE_ADD(列名,列名INTERVAL 1 MONTH)

5、月份取模得出下月：

MOD(MONTH(CURDATE()), 12) + 1

6、要想找出正好包含5个字符的名字，使用“_”模式字符：

SELECT * FROM pet WHERE name LIKE '_____';

7、REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 |REGEXP 区分大小写

8、执行脚本：

mysql -e "source batch-file"

9、源代码或 .命令从mysql提示符运行脚本：

source filename; || \. filename

10、批模式中得到交互输出格式：

mysql -t  、回显以输出被执行的命令：mysql -vvv

11、启动命令行工具mysql并选择数据库：

 mysql your-database-name

12、MySQL特定LIMIT子句只得到第一行：

 limit 1

13、使用用户变量：

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;||输出:SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

14、BIT_COUNT(xxx):

返回列名中的二进制表达式中“1” 的个数，带去重功能 | BIT_OR(xxx):返回bit的bitwiseor。计算的精度为64比特

15、更改Apache日志格式：

LogFormat
“”%h",%{%Y%m%d%H%M%S}t,%>s,"%b","%{Content-Type}o",
“%U”,"%{Referer}i","%{User-Agent}i""

16、服务器支持哪个存储引擎：

SHOW ENGINES;

17、显示实例使用的选项：

SHOW INSTANCE OPTIONS mysqld3;mysqld3：【实例名】

18、提供实例使用的所有日志文件：

SHOW mysqld LOG FILES;

19、检查MySQL服务器是否提供查询缓存功能：

SHOW VARIABLES LIKE ‘have_query_cache’;

20、清理查询缓存碎片以提高内存使用性能：

FLUSH QUERY CACHE

21、从查询缓存中移出所有查询：

RESET QUERY CACHE

22、查看缓存状态变量：

SHOW STATUS LIKE ‘Qcache%’;

23、禁用自己的查询缓存：

SET SESSION query_cache_type = OFF;

1或ON将允许缓存，以SELECT SQL_NO_CACHE开始的查询语句除外。
2或DEMAND，仅对以SELECT SQL_CACHE开始的那些查询语句启用缓存。

24、检查mysql查询性能问题：

SELECT BENCHMARK(1000000,1+1)；

25、借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。：

EXPLAIN：EXPLAIN tbl_name || EXPLAIN [EXTENDED] SELECT select_options

26、EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, from table |EXPLAIN语句产生下列信息：

table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
如果type对每张表是ALL，这个输出显示MySQL正在对所有表产生一个笛卡尔乘积
如何优化：1、将关联的列长度保持一致。2、

27、计算数据行在磁盘上的读取次数：

500,000行的数据，索引块通常是1024个字节，数据指针通常是4个字节，对于有一个长度为3(中等整数)的索引：log(500,000)/log(1024/3*2/(3+4))+1= 4

28、对select优化：

1、检查是否加索引（数据量很大、还是会很慢）

29、怎样优化WHERE子句：

1、去掉不必要的括号2、去除重叠的常量

30、优化查询范围：

尽可能的优化查询范围语法

31、索引合并排序并集访问算法

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
(key3=‘foo’ AND key4=‘bar’) AND key5=5;

32、索引合并交集访问算法

SELECT * FROM tbl_name

WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

32、索引合并并集访问算法

SELECT * FROM tbl_name

WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

33、索引合并优化

交集

· 联合

· 排序并集
SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)

WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

34、MySQL如何优化IS NULL：

利用索引和范围来搜索控制。如果where中有一个字段包含一个is null条件，而这个列结构被声明为not null，
那么这个is null 条件会被优化掉，反之不走索引。is not null 异曲同工。

35、MySQL如何优化DISTINCT：

在大多数情况下，DISTINCT子句可以视为GROUP BY的特殊情况。适用于GROUP BY查询的优化也适用于有DISTINCT子句的查询。

36、优化left join right join 字段属性为 is not null 时：

找到匹配LEFT JOIN条件的一个行后停止

37、MySQL如何优化嵌套Join：

table_reference项列表内的每个逗号等价于内部联接
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
等价于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
列子二：
SELECT *
-> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;

t1 LEFT JOIN (t2，t3) ON t1.a=t2.a
等价于表达式
t1，t2 LEFT JOIN t3 ON P(t2.b,t3.b)
列三：嵌套查询：
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3)
WHERE P(T1,T2,T3).
数据执行逻辑:
FOR each row t1 in T1 {
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
列四：嵌套外链接
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3)。
嵌套环模式执行：
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}

38、MySQL如何简化外部联合:

列子：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

             INNER JOIN T3 ON T3.B=T2.B

WHERE T3.C > 0
转换为：
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3

WHERE T3.C > 0 AND T3.B=T2.B
转化为没有外连接匹配
SELECT * FROM T1 LEFT JOIN

          (T2 INNER JOIN T3 ON T3.B=T2.B)

          ON T2.A=T1.A

WHERE T3.C > 0，

39、MySQL如何优化ORDER BY：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1；可以使用索引来解决查询

40、MySQL如何优化GROUP BY

两种方法通过索引访问执行GROUP BY查询，
1、使用索引时最有效的途径是直接搜索组域。通过该访问方法，MySQL使用某些关键字排序的索引类型(例如，B-树)的属性。（松散索引扫描）
2、紧凑式索引扫描可以为索引扫描或一个范围索引扫描，取决于查询条件。如果WHERE子句中有范围条件，该方法只读取满足这些条件的关键字。否则，进行索引扫描（紧凑索引扫描）

MyISAM表__:

41、MySQL如何优化LIMIT：

使用row_count进行limit的数据长度截取
得到列类型：应使用SHOW COLUMNS或DESCRIBE）。
使用临时表来进行查询时，使用LIMIT row_count子句来计算需要多少空间。

42、如何避免表扫描

使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布

43、INSERT语句的速度

记录需要的时间由下列因素组成：
连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1x记录大小）
插入索引：（1x索引）
锁定表可以加速用多个语句执行的INSERT操作：
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
如果在一行中进行多次插入，可以执行LOCK TABLES，随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。
在MyISAM表得到更快的速度，通过增加key_buffer_size系统变量来扩大键高速缓冲区
关闭：(1)

44、UPDATE语句的速度

锁定表，同时做多个更新比一次做一个快得多。

45、DELETE语句的速度

delete 与 truncat删除的区别：

delete from后面可以写条件，truncate不可以。
delete from记录是一条条删的，所删除的每行记录都会进日志，而truncate一次性删掉整个页，因此日至里面只记录页释放，简言之，delete
from更新日志，truncate基本不，所用的事务日志空间较少。
delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。
当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。
.如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始。
.truncate不会触发delete的触发器，因为truncate操作不记录各个行删除。
无关于事务时删除使用truncate ，需要做条件删除使用delete删除，需要删除表使用drop