MySQL优化以及索引的使用

关忆北.

发布于 2020-10-15 15:55:49

8360

发布于 2020-10-15 15:55:49

文章被收录于专栏：关忆北.关忆北.

MySQL优化

选取最适用的字段长度

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

使用连接（JOIN）来代替子查询(Sub-Queries)
使用事务
优化SQL语句 SQL语句优化的41条建议
- 是否请求了不需要的数据
- 拆分复杂查询,不方便掌握其性能
- 改写子查询,使用连接查询
- 优化关联查询

确保on/where语句中的列上有索引. 确保order by / group by 只根据一个表上的字段进行,这样才有使用索引进行排序分组的可能性.

使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。

CREATE    TABLE    customerinfo( CustomerIDINT    NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATE    TABLE    salesinfo( SalesIDNT    NOT    NULL,CustomerIDINT    NOT    NULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)    REFERENCES    customerinfo(CustomerID)    ON    DELETE    CASCADE)TYPE=INNODB;

使用索引

索引的优点

加快数据的检索速度
通过创建唯一性索引,保证数据库每一行数据的唯一性
加速表和表之间的连接
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

注意:索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

索引的缺点

创建索引和维护索引需要消耗时间
占用物理内存

所以单表数据太少，索引反而会影响速度；更新非常频繁的数据不适宜建索引

索引的类型

唯一索引:唯一索引不允许其中任何两行具有相同索引值的索引
主键索引:表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它允许对数据的快速访问从物理存储角度，索引分为聚集索引和非聚集索引
聚集索引:表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引

索引最左匹配原则

索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。