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以内

表拆分

  垂直拆分

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

  水平拆分

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

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

  主从复制

原文地址:https://www.cnblogs.com/gushiye/p/14016400.html