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关键起来即可;　　　　　　

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

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

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

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

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

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

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

　　（4）三少原则　　　　

　　　　①：数据库的表越少越好

　　　　②：表的字段越少越好

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

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