mysql 索引外键优化_Mysql索引优化

Mysql索引优化

一、概述

索引的类型：

1、主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

PRIMARY KEY ("ID")

2、唯一索引

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构

3、普通索引

最基本的索引，他没有任何限制。可以在创建表的时候指定，也可以修改表结构

4、组合索引 INDEX

索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，组合索引，即一个索引包含多个列。

注意组合索引必须要前面的索引先使用，后面的索引才能使用

5、全文索引 fulltext

全文索引就是通过粉刺技术来筛选出我们想要的搜索结果

二、哈希索引

使用的较少，就是通过哈希算法，将需要的键值进行hash运算，将hash值存入一个hash表中，每次检索条件进行相同算法的hash运算，然后再和hash表中的hash值进行比较并得出相应的信息。

三、全文索引

myisam和innoDB两种引擎支持。

对于文本的大对象，使用普通索引，那么匹配文本前几个字符还是可行的。但是想要匹配文本中间的几个单词，那么就要使用like%Word%来匹配

这就可以使用FULLTEXT，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

四、索引的使用

索引能够为查找带来速度上的提升，但是也会对性能有一些损失。

索引会增加写操作的成本

太多的索引会增加查询优化器的选择时间

使用场景：

主键自动建立唯一索引

经常作为查询条件在WHERE或者ORDER BY语句中出现的列要建立索引

作为排序的列要建立索引

查询中与其他表关联的字段，外键关系建立索引

高并发条件下倾向建立组合索引

用于聚合函数的列可以建立索引，例如使用count(number)时，number列就要建立索引

不使用索引的场景

有大量重复的列不单独建立索引

表记录太少不要建立索引，因为没有太大作用

不会作为查询的列不要建立索引。

4、存储优化

禁用索引

对于使用索引的表，插入记录时，Mysql会对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据的速度。可以在批量插入之前禁用索引，数据插入完成后再开启索引

禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启

禁用外键检查

插入数据之前执行禁制对外键的检查，数据插入完成后再恢复。可以提供插入速度

禁止自动提交

插入数据之前禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度

四、数据库结构优化

优化表结构

将表字段定义为NOT NULL约束，这是因为mysql对含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得复杂。

数值型字段的比较比字符串的比较效率高的多，字段类型尽量使用最小，最简单的数据类型。

varchar的长度只分配真正需要的空间

单表不要有太多的字段，建议20以内

表拆分

垂直拆分

垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同表中。例如把用户一些经常使用的信息可以放进一张表里，另外一些不经常使用的信息就可以放进另外一张表里

水平拆分

按照行来拆分，比如分库分表。

五、读写分离。数据库集群的方案

主从复制