Mysql优化方法详细介绍

时间:2023-02-03 ldcaws 人气:0

下面聊聊mysql常见的优化方法。

1、字段属性

mysql是一种关系型数据库，可以很好地支撑大数据量的存储，但一般来说，数据库中的表越小，执行的查询也就越快，因此在建表时，选择最合适的字段属性，使字段的长度尽可能的小。

如，在定义名称字段时，若将其设置为char(255)，显然给数据库增加了不必要的空间。

2、字段NOT NULL

尽量把字段设置为NOT NULL，这样在执行查询的时候，数据库不用去比较NULL值了。

3、JOIN代替子查询

在sql查询语句中，尽量使用连接join代替子查询，提高查询效率。

如，查询客户基本信息表中没有任何订单的客户，可以利用子查询先从销售信息表中将所有订单的客户id取出来，然后将结果传递给主查询。

select * from custominfo where customId not in (select customId from salesinfo)

利用连接join来实现，速度会更快。

select * from custominfo left join salesinfo on custominfo.customId = salesinfo.customId where salesinfo.customId is NULL

连接join之所以更有效率，是因为mysql不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

sql中连接查询方式有inner join（内连接）、left join（左连接）、right join（有连接）、full join（全连接）四种。

inner join：在两张表进行连接查询时，只保留两张表中完全匹配的结果集；
left join：在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录；
right join：在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录；
full join：在两张表进行连接查询时，返回左表和右表中所有没有匹配的行；

4、UNION代替手动创建临时表

在客户端查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用union作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。

select name,phone from client union
select name,birthday from author union
select name,supplier from product

当确认各个select查询不可能出现重复结果集或不在乎重复结果集的时候尽量用union all而不是union，因为union和union all的差异主要是前者需要将两个或多个结果集合并后再进行唯一性过滤，会涉及到排序，增加了cpu运算，和资源消耗。

5、事务

尽管我们可以使用子查询、JOIN和UNION来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。要避免这种情况，就出现了事务，要么每条语句都执行成功，要么都失败。

事务以begin关键字开始，commit关键字结束。若其中的一条sql执行失败，那么rollback命令就可以把数据库恢复到begin开始之前的状态。

begin;
update salesinfo set customerId = 1;
update custominfo set name = 'daivid';
commit;

事务必须满足四个条件ACID：

原子性：Atomicity，一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节；
一致性：Consistency，在事务开始之前和事务结束之后，数据库的完整性没有被破坏；
隔离性：Isolation，数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable)；
持久性：Durability，事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失；

6、索引

索引是提高数据库性能的常用方法。

一般来说，索引应该建立在那些将用于join，where判断和orderby排序的字段上。

尽量不要对数据库中某个含有大量重复的值的字段建立索引，对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。

如customerinfo中的“province”…字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。

避免索引失效的场景：

SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描，应改为

SELECT * FROM t WHERE d >= '2016-01-01';

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

应改为

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

SELECT * FROM t WHERE name LIKE ‘%de%';

应改为

SELECT * FROM t WHERE name LIKE ‘de%';

加载全部内容