mysql i优化_MySQL优化i

一、增删改优化

1.insert语句

(1)批量插入时，同时插入多条数据(10条时效率最高)

(2)根据业务，考虑使用replace代替insert语句

2.delete语句

(1)truncate删除速度更快，但truncate删除后不记录日志，不可以恢复数据

(2)如果没有外键关联，innoDB执行truncate是先drop table，再创建一个跟原始表一样的空表，速度远快于delete逐条删除行记录。

(3)表有外键关联，truncate table删除数据为逐行删除，如果外键指定级联删除(delete cascade)，关联的字表也会被删除所有数据。

如果外键未指定级联(cascde)，truncate table逐行删除数据，如果是父行关联子表行数据，将会报错。

3.update语句

(1)避免update建有很多索引的列

(2)避免update在where子句条件中的列

4.replace语句

根据应用情况可以使用replace 语句代替insert/update语句。例如：如果一个表在一个字段上建立了唯一索引，当向这个表中使用已经存在的键值插入一条记录，将会抛出一个主键冲突的错误。如果我们想用新记录的值来覆盖原来的记录值时，就可以使用REPLACE语句。

使用REPLACE插入记录时，如果记录不重复(或往表里插新记录)，REPLACE功能与INSERT一样，如果存在重复记录，REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑同时使用DELETE和INSERT时添加事务等复杂操作了。

在使用REPLACE时，表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。

二、多表查询优化

三、索引优化

1.什么是索引？

索引是一种特殊的文件(innoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

2.索引优缺点

(1)可以大大加快数据的检索速度

(2)使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

(3)索引需要额外的维护成本

(4)索引文件是单独存在的文件，对数据的增删改操作都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增删改效率

3.索引原理

索引的原理就是把无序的数据变成有序的查询

(1)把创建了索引的列的内容进行排序

(2)对排序结果生成倒排表

(3)在倒排表内容上拼上数据地址链

(4)查询时，先拿到倒排表内容，再取出数据地址链，从而拿到数据

4.索引数据结构(b树，hash)

(1)B树索引

主键索引区：关联保存数据的地址，按主键查询

普通索引区：关联主键索引地址，普通索引先找到主键索引，在按主键索引查到数据

所以主键索引比普通索引块

5.创建索引原则

(1)最左前缀匹配原则

(2)较为频繁作为查询条件的字段才去创建索引

(3)更新频繁字段不适合建索引

(4)数据区分度低的列不适合建索引(例如：性别字段)

(5)尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

(6)定义外键的数据列一定要建索引

(7)索引尽量选择数据简单的列，定义为text、image的列不要建立索引

6.百万级或以上的数据如何删除？

删除数据的速度和创建的索引的数量成正比

(1)删除百万数据时，可以先删除索引

(2)然后删除数据

(3)删除完成后重新创建索引

四、表设计及优化

(1)创建规范化表，消除数据冗余

(2)合适的字段属性

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

建议不要使用double，不仅仅是存储长度问题，还存在精度问题

char是固定长度，所以它的处理速度比varchar快得多

尽量不要允许null，除非必要，可以用not null+default代替

text和blob区别：blob保存二进制数据，text保存字符数据，有字符集。blob和text不能有默认值

自增字段要慎用，不利于数据迁移

不要在数据库中放LOB类型数据

尽量字段定义为not null约束

尽量使用timestamp类型，因为其存储空间只有datetime的一半

(3)表的拆分

3.1垂直拆分(将原来有很多列的表拆分成多张表)

注意：垂直拆分应该在数据表设计之初就执行的步骤，然后查询的时候用jion关键起来即可;

通常我们按以下原则进行垂直拆分:

把不常用的字段单独放在一张表;

把text，blob等大字段拆分出来放在附表中;

经常组合查询的列放在一张表中;

缺点也很明显，需要使用冗余字段，而且需要join操作。

3.2水平拆分( 如果你发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。

水平分割的做法是，以该表主键的某个值为界线，将该表的记录水平分割为两个表。)

(4)三少原则

①：数据库的表越少越好

②：表的字段越少越好

③：字段中的组合主键、组合索引越少越好

当然这里的少是相对的，是减少数据冗余的重要设计理念。

原文：https://www.cnblogs.com/xingqisan/p/12900105.html