【Database】MySql优化初学

0.Purpose

---

• 合理安排资源、调整系统参数使MySQL运行更快、更节省资源。
• 优化是多方面的，包括查询、更新、服务器等。
• 原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

 

1.How to Optimize from different Aspect?

---

 

1.1 Storage Engine（选择正确的存储引擎）

存储引擎 --- (影响) ---> 插入数据速度 --- (影响因素) ---> 索引、唯一性校验、一次插入的数据条数等

MySql中主要的存储引擎有①InnoDB ②MyISAM

两个引擎区别：

 

InnoDB优化：

①禁用唯一性检查：插入记录之前禁用唯一性检查，插入数据完成后再开启

②禁用外键检查：插入数据之前执行禁止对外键的检查，数据插入完成后再恢复

③禁止自动提交事务（自动提交事务：每执行一条sql语句，就同步到数据库中）

 

 

MyISAM优化：

①禁用唯一性检查

②禁用索引

**对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度

**对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引

③批量插入数据：一个Insert插入多条，不用多个Insert插入多条数据（MySql要解析多次Sql）

④使用LOAD DATA INFILE：当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多

 

 

1.2 Structure of Database（数据库结构）

将字段很多的表分解为多个表

字段较多的表，有一些字段的使用频率很低，就可以从原表中分离出一张新表

 

增加中间表

对于经常需要联合查询的表，将需要联合查询的数据插入到同一张表，将原来的联合查询改为对中间表的查询

 

适当增加冗余字段

冗余字段：

eg：商品图片url，在商品表中有这个字段，在购物车表中也有这个字段。

如果没有这个冗余字段，就可能要联合查询这两张表才能得到商品图片url以及其他数据

*注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

 

1.3 Query Optimization（查询优化）

查询SQL执行计划： EXPALIN + (SELECT SQL)

 

使用索引的优化

 

①使用LIKE关键字

第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效

eg1：LIKE '%apple%' = 索引失效

eg2：LIKE 'apple%' = 索引成功

 

②使用联合索引

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

 

③使用OR关键字

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效

 

子查询优化

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

 

2.How to Check Performance of the Database（性能）

---

• 使用SHOW STATUS语句查看MySQL数据库的性能参数

SHOW STATUS LIKE 'value‘

• 常用的参数：
  • Slow_queries  慢查询次数
  • Com_(CRUD) 操作的次数
  • Uptime  上线时间