实践中如何优化MySQL

目录

一、sql语句优化

二、索引的优化

三、数据库表结构的优化（使得数据库结构符合三大范式与BCNF）

四、系统配置的优化

五、硬件的优化

---

 

https://blog.csdn.net/qq_35642036/article/details/82820129

https://zhuanlan.zhihu.com/p/59818056

https://blog.csdn.net/orecle_littleboy/article/details/88534160

引言：mysql的性能优化无法一蹴而就，必须一步一步慢慢来，从各个方面进行优化，最终性能就会有大的提升。mysql优化是一个综合性的技术，从sql语句优化、sql索引优化、数据表结构优化、

一：sql语句优化

1、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

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

2、避免函数索引

3、使用in来替换OR条件

4、LIKE前缀%号、双百分号、_下划线查询非索引列或者 *无法使用到索引，如果查询的是索引列则可以

5、读取适当的记录LIMIT M,N，而不是读取多余的记录

6、选取最适当的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

7、分组统计可以禁止排序sort，总和查询可以禁止排重用union all

8、避免随机取记录（MySQL不支持函数索引，会导致全表扫描）

9、禁止不必要的ORDER BY排序

10、不要使用NOT等负向查询条件

11、尽量不用select *

12、区分in和exists

 

二：索引的优化：

1、Join语句的优化

Join 性能点

当我们执行两个表的Join的时候，就会有一个比较的过程，逐条比较两个表的语句是比较慢的，因此可以把两个表中数据依次读进一个内存块中，在Mysql中执行：show variables like 'join_buffer_size'，可以看到join在内存中的缓存池大小，其大小将会影响join语句的性能。

2、避免索引失效

2.1* 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

Mysql查询优化器会对查询的字段进行改进，判断查询的字段以哪种形式组合能使得查询更快，所有比如创建的是(a,b)索引，查询的是(b,a)，查询优化器会修改成(a,b)后使用索引查询。

2.不在索引列上做任何操作

(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。

 

3.存储引擎不能使用索引中范围条件右边的列。

如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。

 

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))

如select age from user减少select *

5.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。

 

6.is null, is not null 也无法使用索引，在实际中尽量不要使用null。

 

7.like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。

8.字符串不加单引号索引失效

 

9.少用or，用它来连接时会索引失效

 

10.尽量避免子查询，而用join

 

11、在组合索引中，将有区分度的索引放在前面

 

12、避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描