MySQL

 

数据库优化

1. 硬件配置
2. 数据库配置
3. 表结构设计
4. sql语句优化

QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数
TPS，Transactions Per Second：每秒处理事务数

下面的sql优化方案都是基于 “ Mysql-索引-BTree类型 ” 的

一、EXPLAIN

做MySQL优化，我们要善用 EXPLAIN 查看SQL执行计划。

1. type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
2. key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
3. key_len列，索引长度
4. rows列，扫描行数。该值是个预估值
5. extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary

二、避免全表扫描，走索引

1. 在都有索引的情况下，or可以in代替，在有些有索引，有些没索引的情况下or使用union all代替
2. 避免在字段上运算
3. 避免有null值建立索引，字符串默认值为空字符串，数字默认为零
4. 避免在重复数据多的字段上建立索引or
5. 避免使用*号全字段查询
6. 使用合理的分页方式以提高分页的效率

EXPLAIN SELECT
	* 
FROM
	order_trans 
WHERE
	id > 10000 
	LIMIT 20;

// type=range 

EXPLAIN SELECT
	* 
FROM
	order_trans 
	LIMIT 10001,
	20;

// type=all

     7.分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。扫描的行数成百万级以上的时候就可以使用分段查询

SELECT
	T1.`key`,
	T1.`version`,
	GROUP_CONCAT( T2.`value` SEPARATOR ';' ) 
FROM
	`test` AS T1
	LEFT JOIN `test2` AS T2 ON T1.KEY = T2.KEY 
GROUP BY
	T1.`key` 
ORDER BY
	T1.id;
// 先分组，后排序，因为分组自带那个字段排序

SELECT
	T1.`key`,
	T1.`value`,
	MAX( T1.create_time ) AS `value` 
FROM
	rich_fintech.test AS T1 
GROUP BY
	T1.`key` 
HAVING
	T1.`key` <> '';

在有索引的条件下，使用union all 代替or

SELECT
	* 
FROM
	`order_trans` O1 
WHERE
	O1.id = 10000 
	OR O1.id = 20000;

// 百万数据用了0.079s

SELECT
	* 
FROM
	`order_trans` O1 
WHERE
	O1.id = 10000 UNION ALL
SELECT
	* 
FROM
	`order_trans` O1 
WHERE
	O1.id = 20000;

// 百万数据用了0.002s