mysql数据库的优化技术

时间:2022-03-29 05:21:57
  1. 表的设计合理化(遵从3NF)<3范式>
  • 1NF:表的列具有原子性,不可再分解(列的信息不能分解,只要是关系型的数据库就自动满足1NF)
  • 2NF:表中的记录是唯一的,就满足2NF(通常我们设计一个主键来实现)
  • 3NF:表中不要有冗余数据(表中的信息能被推倒出来,就不应该单独成列)

在一对多的情况,为了提高效率,可能会在1中设计字段提高效率(违反3NF)

  2. 添加适当的索引(index)[四种:主键,唯一,普通,全文]

    (1)主键索引的添加:当一张表,把某个列设为主键时,则该列就是主键索引

      create table aaa

       ( id int unsigned primary key auto_increment,

        name varchar(32) not null default ' ');

      这时id列就是主键索引。

      如果你在创建表时没有指定主键索引,也可以在创建后添加,指令:

      alter table 表名 add primary key (列名);

      举例:

         create table (id int unsigned,name varchar(32) not null default '' );

        alter table bbb add pirmary key (id);

    (2)唯一索引的添加:     

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
) CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

    (3)普通索引的创建:普通索引的创建,是先创建表,然后再创建普通索引。

      比如:create table ccc(id int unsigned,name varchar(32));

        create index 索引名 on 表(列)

    (4)全文索引的创建:主要针对文件,文本的检查,比如文章,全文索引针对MyISAM引擎有用。    

CREATE TABLE articles
(
Id_P int NOT NULL UNIQUE,
title varchar(200),
body text,
fulltext(title,body)
)egin=myisam charset utf8;

      如何使用全文索引?

      错误用法:select *from articles where body like '%mysql%';[这样不会使用到全文索引]

      正确用法:select *from articles where match(title,body) against ('database');  

    说明:

       <1>在mysql中fulltext索引只针对myisam生效。

       <2>针对英文生效---->sphinx技术处理中文(coreseek)

       <3>使用方法 match(字段名) aginst('关键字')

       <4>全文索引有一个停止词,因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词称为停止词。

    小结:查询索引命令

      desc 表表名;

      show index(es) from 表名;

      show keys from 表名;

  3.分表技术[水平分割,垂直分割]

    (1)水平分割:

      举例:如QQ登录表(假设用户有5亿)

        id    name   pw

        1      aa       aa

        2      bb       bb  

      如果验证5个亿的数据验证,这样的效率过低。假设把数据分成3个表储存,由id%3来决定放入和取出的表。三个表的结构和大表相同。

    (2)垂直分割:

       某些表的某些字段,在查询的时候,并不关心,但是数据量很大,我们建议把这些大字段单独放到另一张表里,从而提高效率,

        但是必须与原表保持关联。

  4.读写(写操作包括update、delete、add)分离

  5.存储过程(模块化编程,可以提高速度)

  6.对mysql配置优化[配置最大并发数,调整缓存大小--->my.ini]

  7.mysql服务器硬件升级

  8.定时清除不需要的数据,定时进行碎片整理。