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. 把不常用的字段单独放在一张表;

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

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

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

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

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

  (4)三少原则    

    ①:数据库的表越少越好

    ②:表的字段越少越好

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

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

      

       

 

MySQL优化i

上一篇:GDB常用调试命令


下一篇:mysql问题集合