MySQL7/8：MySQL5：性能优化&视图

一： 性能优化：

性能优化

优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库的整体性能；一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时应该尽可能节省系统资源，以便系统可以提供更大负荷的服务。

MySQL数据库优化是多方面的，有几条优化原则：

1、减少系统的瓶颈

2、减少资源的占用

3、增加系统的反应速度

 

优化查询

1、索引对查询的影响

我有一个book表，里面有10条记录，没有索引查询的时候：

使用了索引：

看到不使用索引的时候rows为10，也就是说扫描了表中的10条记录，而使用索引，rows为1，说明扫描了表中的1条记录，其查询速度自然比不使用索引快。而且possible_keys和key的值都是BkNameIdx，说明查询时使用了BkNameIdx索引

2、使用索引查询

使用索引查询有几种特殊情况，特别提一下：

（1）使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不会起作用，只有"%"不在第一个位置，索引才会起作用

（2）使用多列索引的查询语句

MySQL可以为多个字段创建索引，一个索引可以包含16个字段，对于多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用

（3）使用OR关键字的查询语句

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

3、避免使用SELECT *命令

从表中读取的数据越多，查询会变得越慢，始终指定需要的列，是一个良好的习惯

4、永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID作为其主键，而且最好是一个INT型的，并设置上自动增加的AUTO_INCREMENT标识。就算有些VARCHAR字段类型的值，它永远不会重复，也不要设置为主键，使用VARCHAR类型来当主键会使性能下降

 

数据库结构优化

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果，合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面内容。

下面提供几条优化数据库结构的建议：

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

对于字段很多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢，通过分解字段，可以提高表的查询效率

2、增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率

3、优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等，根据这些情况，可以分别进行优化（以使用InnoDB的表为例）：

（1）禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度，为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启：

SET UNIQUE_CHECKS = 0;
开启唯一性检查：

SET UNIQUE_CHECKS = 1;
（2）禁用外键检查

插入数据之前禁止对外键的检查，数据插入之后再恢复对外键的检查，禁用外键检查的语句如下：

SET foreign_key_checks = 0;
恢复对外键检查的语句为：

SET foreign_key_checks = 1;
（3）禁止自动提交

插入数据之前禁止事物的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交的语句如下：

SET autocommit = 0;
恢复自动提交的语句为：

SET autocommit = 1;

 

---

什么是视图

数据库中的视图是一个虚拟表。视图是从一个或者多个表中导出的表，视图的行为与表非常相似，在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录。视图可以使用户操作方便，而且可以保障数据库系统安全。

视图一经定义便存储在数据库中，预期相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。当对通过视图看到的数据进行修改时，相应的基本表中的数据也要发生变化；同时，若基本表的数据发生变化，那么这种变化也自动地反映到视图中。

使用视图的作用

上面创建了视图了，看到与直接从数据表中读取相比，视图有以下优点：

1、简单化

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件

2、安全性

通过视图，用户只能查询和修改他们所能看见的数据，数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定列上。通过视图，用户可以被限制在数据的不同子集上：

（1）使用权限可被限制在基表的行的子集上

（2）使用权限可被限制在基表的列的子集上

（3）使用权限可被限制在基表的行和列的子集上

（4）使用权限可被限制在多个基表的连接所限定的行上

（5）使用权限可被限制在基表的数据的统计汇总上

（6）使用权限可被限制在另一个视图的一个子集上，或是一些视图和基表合并后的子集上

3、逻辑数据独立性

视图可以帮助用户屏蔽真实表结果变化带来的影响

MySQL中视图和表的区别

最后总结一下MySQL中视图和表的区别：

1、视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是

2、视图没有实际的物理记录，而基本表有

3、表是内容，视图是窗口

4、表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改

5、视图是查看数据表的一种方法，可以查询数据表中的某些字段构成的数据，只是一些SQL语句的集合。从安全的角度讲，视图可以防止用户接触数据表，因而用户不知道表结构

6、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表

7、视图的建立和删除只影响视图本身，不影响对应的基本表