精华内容
下载资源
问答
  • 管家婆官方提供的服装SIITOP系列数据库优化软件,请在SQL2000中查询执行。内有详细说明,我们的数据库存从10G缩减到3G,请注意,一定是2000的数据库才能使用。
  • 数据库优化

    2016-10-27 15:50:03
    针对数据库优化,一般分为两类: 硬件 代码 软件 硬件优化很简单粗暴,多核自然比单核快,两台要比一台好。这个取决于公司的实力,这里就不做讨论。 代码优化是个大命题,包含代码中缓存的使用、连接池等。这个话题...

    对于大部分项目而言,项目的瓶颈都是数据库。因此对数据库的优化,也是重中之重。
    针对数据库优化,一般分为三类:

    • 硬件
    • 代码
    • 软件

    硬件优化很简单粗暴,多核自然比单核快,两台要比一台好。这个取决于公司的实力,这里就不做讨论。
    代码优化是个大命题,包含代码中缓存的使用、连接池等。这个话题另开一篇进行总结。
    软件优化指的是针对数据库本身的优化,主要包括表设计(存储引擎、字段)、sql优化与索引、配置文件、系统架构等。

    表设计优化:

    1.存储引擎
    强烈建议使用Innodb类型。一般情况下认为myisam要比Innodb快,在低版本的MySQL中确实如此,但是在高版本的MySQL中,Innodb读速度并不比myisam慢,并且支持事务、外键等高级应用。在MySQL8.0版本中,官方已经决定废弃myisam引擎了。

    2.设计优化
    三范式设计并不是需要完全遵守的。三范式出现的年代,存储空间是很贵的,业务量确很小。如今存储空间很便宜,业务量极大,完全遵守三范式的话,很可能会导致join多个表、甚至跨库join表,这都是不允许的。适当的冗余字段,减少join操作,用空间换性能。

    3.字段优化

    • 选择字段类型的原则,是够用就好。
    类型 大小 范围(有符号) 范围(无符号) 用途
    TINYINT 1字节 -128~127 0 小整数值
    SMALLINT 2字节 -32768~32767 0~65535 大整数值
    MEDIUMINT 3字节 -8388608~8388607 0~16777215 大整数值
    INT或INTEGER 4字节 -2147483648~2147483647 0~4294967295 大整数值
    BIGINT 8字节 -9223372036854775808
    ~
    9223372036854775807
    0
    ~
    18446744073709551615
    极大整数值
    FLOAT 4字节 -3.402823466E+38
    ~
    1.175494351E-38,
    0,
    1.175494351E-38
    ~
    3.402823466E+38
    0,1.175494351E-38
    ~
    3.402823466E+38
    单精度浮点数值
    DOUBLE 8字节 -1.7976931348623157E+308
    ~
    2.2250738585072014E-308,
    0,
    2.2250738585072014E-308
    ~
    1.7976931348623157E+308
    0,2.2250738585072014E-308
    ~
    1.7976931348623157E+308
    双精度浮点数值
    DECIMAL DECIMAL(M,D)
    如果M>D为M+2
    否则为D+2依赖于M和D的值
    依赖于M和D的值 小数值
    • int(11) vs int(21)
      其实两种写法标识的范围是一致的,区别是前导零。假如存了一个1,那么前一种写法前导零有10个,后一种有20个。
        mysql> create table t(c1 int(11) zerofill,c2 int(21) zerofill);//zerofill 是补全零的
        Query OK, 0 rows affected (0.02 sec) 
    
        mysql> insert into t values(1,1);
        Query OK, 1 rows affected (0.02 sec)
    
        mysql> select * from t;      
        +-------------+-----------------------+     
        | c1          | c2                    |    
        +-------------+-----------------------+     
        | 00000000001 | 000000000000000000001 |     
        +-------------+-----------------------+     
        1 row in set (0.00 sec)
    • 字符串类型
    类型 大小 用途
    CHAR 0~255字符 定长字符串
    VARCHAR 0~65532字节 变长字符串
    TINYBLOB 0~255字符 不超过255个字符的二进制字符串
    TINYTEXT 0~255字符 短文本字符串
    BLOB 0~65535字符 二进制形式的长文本数据
    TEXT 0~65535字符 长文本数据
    MEDIUMBLOB 0~16777215字符 二进制形式的中等长度文本数据
    MEDIUMTEXT 0~16777215字符 中等长度文本数据
    LOGNGBLOB 0~4294967295字符 二进制形式的极大文本数据
    LONGTEXT 0~4294967295字符 极大文本数据

    CHAR是定长的,也就是当你输入的字符小于你指定的数目时,CHAR(8),你输入的字符小于8时,它会再后面补空值。当你输入的字符大于指定的数时,它会截取超出的字符。最大长度是255字符。

    VARCHAR列中的值为可变长字符串。长度可以指定为0到65535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节)。

    CHAR(4) 存储需求 VARCHAR(4) 存储需求
    ’ ‘ 4个字节 1个字节
    ‘ab’ ‘ab ‘ 4个字节 ‘ab ‘ 3个字节
    ‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节
    ‘abcdefgh’ ‘abcd’ 4个字节 ‘abcd’ 5个字节

    请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值不保存,并且会出现错误。
    从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别:

        mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
        Query OK, 0 rows affected (0.02 sec)
    
        mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
        Query OK, 1 row affected (0.00 sec)
    
        mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
        +----------------+----------------+
        | CONCAT(v, '+') | CONCAT(c, '+') |
        +----------------+----------------+
        | ab  +          | ab+            |
        +----------------+----------------+
        1 row in set (0.00 sec)

    所有MySQL版本均如此,并且它不受SQL服务器模式的影响。
    对于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形,如果列的索引需要唯一的值,在列内插入一个只是填充字符数不同的值将会造成复制键值错误。

    CHAR BYTE是CHAR BINARY的别名。这是为了保证兼容性。

    ASCII属性为CHAR列分配latin1字符集。UNICODE属性分配ucs2字符集。

    如果存储引擎用的是Innodb,那么强烈建议采用VARCHAR类型。因为VARCHAR更快更省空间。理论上CHAR比VARCHAR快的根本原因是站在CPU的角度来说的,但性能是综合各种因素后的最终结果,当Innodb buffer pool小于表大小时,”磁盘读写”成为了性能的关键因素,而VARCHAR更短,因此性能反而比CHAR高。并且Innodb采用页管理数据,数据移动是先在内存里完成,再写到磁盘,因此数据即使移动也很快。

    • date
      三个字节,如2015-05-01只能存储到天数。date精确到年月日

    • time
      三个字节,只能存小时分钟,time精确到小时分钟秒

    • datetime
      八字节,可以存储年月日时分秒

    • timestamp
      四字节,可以存储年月日时分秒

    4.锁机制

    • 了解锁机制是为了防止事务出现死锁。这个我会单独开一篇博客进行总结。

    5.查询sql优化

    6.索引优化

    未完继续

    展开全文
  • 1.课程介绍Ø1.介绍什么是mysql优化Ø2.mysql优化方法Ø3.Mysql索引的使用Ø4.分表技术2.mysql优化概述概述: 前面我们学习了页面静态化...mysql数据库优化的常见方法:1.表的设计要合理(满足3NF) 3范式2.创建适当索引...

    1. 课程介绍
    Ø 1.介绍什么是mysql优化
    Ø 2.mysql优化方法
    Ø 3.Mysql索引的使用
    Ø 4.分表技术
    2. mysql优化概述
    概述: 前面我们学习了页面静态化和redis,它们是通过不操作mysql数据库达到提速目的。但是某些功能是一定要操作数据库的,这就要求我们必须对mysql本身进行优化。
    mysql数据库优化的常见方法:
    1. 表的设计要合理(满足3NF) 3范式
    2. 创建适当索引[主键索引|唯一索引|普通索引|全文索引|空间索引]
    3. 对SQL语句优化---->定位慢查询(explain)
    4. 使用分表技术(重点【水平分表,垂直分表】), 分区技术(了解)
    5. 读写分离(配置)
    6. 创建适当存储过程,函数,触发器
    7. 对my.ini优化,优化配置
    8. 软件硬件升级
    3. 表的设计满足3NF
    概述: 目前我们的表的设计,最高级别的范式是"6NF",对PHP程序员而言,我们的表满足3NF即可。
    3.1. 1NF
    所谓1NF,就是
    (1) 指表的属性(列)具有原子性, 即表的列的不能再分了。
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    (2) 不能有重复的列
     
    特殊
    (1) 只要是关系型数据库,就天然的满足1NF
    (2) 常见数据库
    关系型数据库(mysql, oracle, sql server,informix, db2 , postgres)
    非关系型数据(Nosql类型的数据库由Redis, MongoDB)
    3.2. 2NF
    所谓2NF,就是指我们的表中不能有完全重复的一条记录(行).一般情况下通过设置一个主键来搞定,而且该主键是自增的。
    3.3. 3NF(外键)
    所谓3NF就是指,如果列的内容可以被推导(显式推导,隐式推导)出,那么我们就不要单独的用一列存放。
    举例:下面是满足3NF

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    3.4. 反3NF
    在通常情况下,我们的表的设计要严格的遵守3NF,但也有例外。有时为了提高查询的效率,我们需要违反3NF。举例:
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    4. 构建海量表,定位慢查询
    为了讲解这个优化,我们需要构建一个海量表(8000000),而且每条数据不一样。
    4.1. 构建海量表步骤
    (1) 创建一个测试数据库

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    (2) 创建表
    CREATE TABLE dept( /部门表/
    deptno   MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
    dname VARCHAR(20)  NOT NULL  DEFAULT "",
    loc VARCHAR(13) NOT NULL DEFAULT ""
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
    加入数据: dept.sql
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    #创建表EMP雇员
    CREATE TABLE emp
    (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /编号/
    ename VARCHAR(20) NOT NULL DEFAULT "", /名字/
    job VARCHAR(9) NOT NULL DEFAULT "",/工作/
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/上级编号/
    hiredate DATE NOT NULL,/入职时间/
    sal DECIMAL(7,2)  NOT NULL,/薪水/
    comm DECIMAL(7,2) NOT NULL,/红利/
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /部门编号/
    )ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
    加入数据:emp.sql
    #工资级别表
    CREATE TABLE salgrade
    (
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    losal DECIMAL(17,2)  NOT NULL,
    hisal DECIMAL(17,2)  NOT NULL
    )ENGINE=MyISAM DEFAULT CHARSET=utf8;
    加入数据: salgrade.sql
    4.2. 海量表带来的问题
    看一个案例
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
     
    4.3. 先使用索引来搞定
    l 给empno段添加主键索引
    alter table emp add primary key (empno);

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    一个表(存储引擎是MyISAM),对应三个文件 xx.frm 表结构 xx.MYD 数据文件 xx.MYI 索引文件
    l 通过测试看效果

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    l 删除emp表的主键索引
    alter table emp drop primary key
    4.4. 如何定位慢查询(slow query)
    介绍: 在默认情况下,mysql 是不会记录慢查询的,所以我们在测试时,可以指定mysql记录慢查询.
    开启慢查询的两种方法:
    l 启动时,这样启动
    cmd>bin/mysqld.exe --safe-mode --slow-query-log 
     
    或者是
    在my.ini的[mysqld]下添加一下代码并且重启
    log-slow-queries = D:/server/mysql/mysqlslowquery.log(注意斜杠)
    注:mysql5.6版本slow-query-log-file
    long_query_time = 1 指定超过1秒算慢查询
    l 为了测试,我们修改 long_query_time

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    l 记录下慢查询
     

    Time: 141122 10:39:45

    User@Host: root[root] @ localhost [127.0.0.1]

    Query_time: 1.625093  Lock_time: 0.001000 Rows_sent: 0  Rows_examined: 8000000

    use testdb;
    SET timestamp=1416623985;
    select * from emp where ename='IUYTOPUYQWE';
     
    说明: Query_time是查询的时间
    Lock_time:等待时间
     
    4.5. 开启慢查询牺牲sql的执行效率
    如何使用慢查询?

    1. 系统上线之后,将慢查询开启一个星期.
    2. 当你认为系统所由于数据增多导致系统执行缓慢.再开启慢查询找到执行慢的sql语句,然后在优化它.
       
      4.6. 如何分析一个sql语句的问题-explain 工具
      l 基本用法:
      explain  sql\G
      l 案例:

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    l 优化
    添加索引。
     
    4.7. mysql的变量查询
    mysql>show variables;
    mysql>show variables like ‘%xxxx%’;
    show  tables  like  '数据表名';//查询一个数据表是否存在
    如果需要知道每个变量的具体含义,可以查询手册.
    5. 索引的详解(重点)
    5.1. 索引创建
    5.1.1. 主键索引的创建
    主键索引的创建有两种形式, 1.在创建表的时候,直接指定某列或者某几列为主键,这时就有主键索引, 2. 添加表后,再指定主键索引
    l 直接创建主键索引
    注意:如果是自增, 该主键不能够删除
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    l 先创建表,再指定主键

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    增加主键
    ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列名2..)
    l 主键索引的特点
    1. 一个表最多只能有一个主键
    2. 一个主键可以指向多列(复合主键)
    3. 主键索引的效率是最高,因此我们应该给id,一般id是自增.
    4. 主键索引列是不能重复,也不能为null
    5.1.2. 唯一索引的创建
    l 直接在创建表的时候,指定某列或某几列为唯一索引
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    l 把表创建好后,再指定某列或者某几列为唯一索引
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    说明: 使用 create unique index 指令,必须指定索引名。
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    说明: 使用alter table 指令,可以指定索引名,也可以不指定。
    l 唯一索引的特点
    1) 一张表可以有多个唯一索引
    2) 唯一索引不能重复,但是如果你没有指定not null ,唯一索引列可以为null,而且可以有多个.
    3) 什么时候使用唯一索引,当某列数据不会重复,才能使用
    4) 唯一索引效率也很高,可以考虑优先使用
    5.1.3. 普通索引的创建
    l 在创建表时指定索引,通过key或者index

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    l 把表创建好后,再指定某列或者某几列为索引
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    l 添加普通索引(2种方式)

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试

     
    l 特点
    1) 一张表中可以有多个普通索引,一个普通索引页可以指向多列
    2) 普通索引列的数据可以重复
    3) 效率相对而言低.
    5.2. 索引的查询
    l desc 表名
    l show keys from 表名\G
    l show index from 表名\G
    l show indexes from 表名\G
    5.3. 索引的修改
    先删除,再添加。
    5.4. 索引的删除
    DROP  INDEX  索引名  ON  表;
    ALTER  TABLE 表名 DROP INDEX 索引名;
     
    5.5. 索引的注意事项

    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    索引的缺点:
    增删改速度慢..
    优点:
    查询速度快…
     
    建立索引一定要根据自己的需求来…
    实例:
    登录用户名是否适合建索引? 用户名适合建立索引
     
    操作日志:
    用户名    操作的哪个控制器的哪个方法    操作时间记录
    不合适建立索引..
    6. sql语句的优化和正确使用索引
    6.1. 对于创建的多列(复合)索引,只要查询条件使用了最左边的列,索引一般就会被使用
    name  email
     alter table xxx  add index (name,email)
    select from xx where name = ‘xxx’;
    select
    from xx where email = ‘xxx’;
     
     
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    说明: dname是左边的列,因此我们发现使用到dname,就使用到索引,而下面的sql语句,没有使用到索引。
    6.2. 对于使用like的查询,查询如果是‘%aaa’‘_aa' 不会使用到索引‘aaa%’会使用到索引
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    说明: 在like语句中,如果 '' 中最前有 或者 %就使用不到索引,如果在中间或者最后有 或者 %可以使用到索引。
    6.3. 如果条件中有or,则要求or的所有字段都必须有索引,否则不会使用索引

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    说明:因为 deptno 没有索引,所以整个sql语句就没有使用到索引。
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    如果在 deptno上也创建索引,就可以使用到索引了.
    如果mysql认为全表扫描效率更高,就不会使用索引,而会全表扫描
    6.4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    6.5. 有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表
    子查询:select  from emp where deptno in (select deptno from dept)
    连接:select
    from emp left join dept on emp.deptno=dept.deptno where emp.deptno=dept.deptno
    6.6. 管理员在导入大量数据,可以这样提高速度
    大批量插入数据(MySql管理员) 了解
    对于MyISAM:
     alter table table_name disable keys;
    执行insert语句导入
    alter table table_name enable keys;
    对于Innodb:
    1, 将要导入的数据按照主键排序
    2, set unique_checks=0,关闭唯一性校验。
    3, set autocommit=0,关闭自动提交。
    6.7. 如何选择存储引擎

     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
    l 如何选择的原则
    (1) MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子/信息表/新闻/商品表表)
    (2) InnoDB:提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM,写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高,则使用innodb)。[账户,积分,余额]
    6.8. 如何选择正确的数据类型
    6.8.1. 在满足需求的情况下尽量选择小的类型.
    6.8.2. 在精度要求高的应用中,建议使用定点数来存储数值,以保证结果的准确性。decimal 不要用float.
    举例:
     史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试

     
    说明: 这里我们看的 float(10,2) , 和 decimal(10,2) decimal 更精准。所以我们对精度高的列,要使用decimal 类型。
    6.8.3. 对存储引擎是MyISAM的表,要定时碎片整理
    举例说明:当我们在users表中有大量数据时,我们delete 数据后,我们发现磁盘空间没有回收,因此我们需要定时的进行碎片整理.如下:
    创建表:
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     
    复制大量数据到同一个表中:
    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
     

    史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试 
    optimize: 该命令可以使表中的数据彻底从数据文件中删除.

    (本文由源码时代技术老师原创发布,转载请注明来源。)

    展开全文
  • 之前写过一系列关于mysql优化的文章,今天在网上看到一篇关于SQL SERVER优化的介绍,该文将数据库优化软件开发周期结合在一起,看完后填补了很多知识的缺漏,感觉非常不错,故分享之。 =========================...

    之前写过一系列关于mysql优化的文章,今天在网上看到一篇关于SQL SERVER优化的介绍,该文将数据库优化和软件开发周期结合在一起,看完后填补了很多知识的缺漏,感觉非常不错,故分享之。

    =========================================================================


    近期因工作需要,希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项,在网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔细看发现,有很多似是而非或者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息,只好自己根据以前的经验和测试结果进行总结了。

    我始终认为,一个系统的性能的提高,不单单是试运行或者维护阶段的性能调优的任务,也不单单是开发阶段的事情,而是在整个软件生命周期都需要注意,进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。


    一、             分析阶段


    一 般来说,在系统分析阶段往往有太多需要关注的地方,系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力,但是,我们必须注意,性能 是很重要的非功能性需求,必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。

    另一方面,在分析阶段应该根据各种需求区分出系统的类型,大的方面,区分是OLTP(联机事务处理系统)和OLAP(联机分析处理系统)。


    二、             设计阶段


    设计阶段可以说是以后系统性能的关键阶段,在这个阶段,有一个关系到以后几乎所有性能调优的过程—数据库设计。

    在数据库设计完成后,可以进行初步的索引设计,好的索引设计可以指导编码阶段写出高效率的代码,为整个系统的性能打下良好的基础。

    以下是性能要求设计阶段需要注意的:

    1、 数据库逻辑设计的规范化

    数据库逻辑设计的规范化就是我们一般所说的范式,我们可以这样来简单理解范式:

    第1规范:没有重复的组或多值的列,这是数据库设计的最低要求。

    第2规范: 每个非关键字段必须依赖于主关键字,不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖,大部分情况下,数据库设计都应该达到第二范式。

    第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖,达到第三范式应该是系统中大部分表的要求,除非一些特殊作用的表。

    更高的范式要求这里就不再作介绍了,个人认为,如果全部达到第二范式,大部分达到第三范式,系统会产生较少的列和较多的表,因而减少了数据冗余,也利于性能的提高。

    2、 合理的冗余

    完全按照规范化设计的系统几乎是不可能的,除非系统特别的小,在规范化设计后,有计划地加入冗余是必要的。

    冗余可以是冗余数据库、冗余表或者冗余字段,不同粒度的冗余可以起到不同的作用。

    冗余可以是为了编程方便而增加,也可以是为了性能的提高而增加。从性能角度来说,冗余数据库可以分散数据库压力,冗余表可以分散数据量大的表的并发压力,也可以加快特殊查询的速度,冗余字段可以有效减少数据库表的连接,提高效率。

    3、 主键的设计

    主键是必要的,SQL SERVER的主键同时是一个唯一索引,而且在实际应用中,我们往往选择最小的键组合作为主键,所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的,这个在下面索引的叙述。

    在有多个键的表,主键的选择也比较重要,一般选择总的长度小的键,小的键的比较速度快,同时小的键可以使主键的B树结构的层次更少。

    主键的选择还要注意组合主键的字段次序,对于组合主键来说,不同的字段次序的主键的性能差别可能会很大,一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

    4、 外键的设计

    外键作为数据库对象,很多人认为麻烦而不用,实际上,外键在大部分情况下是很有用的,理由是:

    外键是最高效的一致性维护方法,数据库的一致性要求,依次可以用外键、CHECK约束、规则约束、触发器、客户端程序,一般认为,离数据越近的方法效率越高。

    谨慎使用级联删除和级联更新,级联删除和级联更新作为SQL SERVER 2000当年的新功能,在2005作 了保留,应该有其可用之处。我这里说的谨慎,是因为级联删除和级联更新有些突破了传统的关于外键的定义,功能有点太过强大,使用前必须确定自己已经把握好 其功能范围,否则,级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

    5、 字段的设计

    字段是数据库最基本的单位,其设计对性能的影响是很大的。需要注意如下:

    A、数据类型尽量用数字型,数字型的比较比字符型的快很多。

    B、 数据类型尽量小,这里的尽量小是指在满足可以预见的未来需求的前提下的。

    C、 尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT代替。

    D、少用TEXT和IMAGE,二进制字段的读写是比较慢的,而且,读取的方法也不多,大部分情况下最好不用。

    E、 自增字段要慎用,不利于数据迁移。

    6、 数据库物理存储和环境的设计

    在设计阶段,可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计,使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

    这里需要注意文件组的作用,适用文件组可以有效把I/O操作分散到不同的物理硬盘,提高并发能力。

    7、 系统设计

    整个系统的设计特别是系统结构设计对性能是有很大影响的,对于一般的OLTP系统,可以选择C/S结构、三层的C/S结构等,不同的系统结构其性能的关键也有所不同。

    系统设计阶段应该归纳一些业务逻辑放在数据库编程实现,数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

    8、 索引的设计

    在设计阶段,可以根据功能和性能的需求进行初步的索引设计,这里需要根据预计的数据量和查询来设计索引,可能与将来实际使用的时候会有所区别。

    关于索引的选择,应改主意:

    A、              根据数据量决定哪些表需要增加索引,数据量小的可以只有主键。

    B、              根据使用频率决定哪些字段需要建立索引,选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

    C、              把经常一起出现的字段组合在一起,组成组合索引,组合索引的字段顺序与主键一样,也需要把最常用的字段放在前面,把重复率低的字段放在前面。

    D、              一个表不要加太多索引,因为索引影响插入和更新的速度。


    三、             编码阶段


    编码阶段是本文的重点,因为在设计确定的情况下,编码的质量几乎决定了整个系统的质量。

    编码阶段首先是需要所有程序员有性能意识,也就是在实现功能同时有考虑性能的思想,数据库是能进行集合运算的工具,我们应该尽量的利用这个工具,所谓集合运算实际是批量运算,就是尽量减少在客户端进行大数据量的循环操作,而用SQL语句或者存储过程代替。关于思想和意识,很难说得很清楚,需要在编程过程中来体会。

    下面罗列一些编程阶段需要注意的事项:

    1、 只返回需要的数据

    返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节,如果返回不需要的数据,就会增加服务器、网络和客户端的无效劳动,其害处是显而易见的,避免这类事件需要注意:

    A、横向来看,不要写SELECT *的语句,而是选择你需要的字段。

    B、 纵向来看,合理写WHERE子句,不要写没有WHERE的SQL语句。

    C、 注意SELECT INTO后的WHERE子句,因为SELECT INTO把数据插入到临时表,这个过程会锁定一些系统表,如果这个WHERE子句返回的数据过多或者速度太慢,会造成系统表长期锁定,诸塞其他进程。

    D、对于聚合查询,可以用HAVING子句进一步限定返回的行。

    2、 尽量少做重复的工作

    这一点和上一点的目的是一样的,就是尽量减少无效工作,但是这一点的侧重点在客户端程序,需要注意的如下:

    A、              控制同一语句的多次执行,特别是一些基础数据的多次执行是很多程序员很少注意的。

    B、              减少多次的数据转换,也许需要数据转换是设计的问题,但是减少次数是程序员可以做到的。

    C、              杜绝不必要的子查询和连接表,子查询在执行计划一般解释成外连接,多余的连接表带来额外的开销。

    D、              合并对同一表同一条件的多次UPDATE,比如

    1. UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’
      UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’

    这两个语句应该合并成以下一个语句

    1. UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’
      WHERE EMP_ID=’ VPA30890F’

    E、               UPDATE操作不要拆成DELETE操作+INSERT操作的形式,虽然功能相同,但是性能差别是很大的。

    F、               不要写一些没有意义的查询,比如

    SELECT * FROM EMPLOYEE WHERE 1=2

    3、 注意事务和锁

    事务是数据库应用中和重要的工具,它有原子性、一致性、隔离性、持久性这四个属性,很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意:

    A、事务操作过程要尽量小,能拆分的事务要拆分开来。

    B、 事务操作过程不应该有交互,因为交互等待的时候,事务并未结束,可能锁定了很多资源。

    C、 事务操作过程要按同一顺序访问对象。

    D、提高事务中每个语句的效率,利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

    E、 尽量不要指定锁类型和索引,SQL SERVER允许我们自己指定语句使用的锁类型和索引,但是一般情况下,SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的,我们指定的可能只是在目前情况下更有,但是数据量和数据分布在将来是会变化的。

    F、 查询时可以用较低的隔离级别,特别是报表查询的时候,可以选择最低的隔离级别(未提交读)。

    4、 注意临时表和表变量的用法

    在复杂系统中,临时表和表变量很难避免,关于临时表和表变量的用法,需要注意:

    A、如果语句很复杂,连接太多,可以考虑用临时表和表变量分步完成。

    B、 如果需要多次用到一个大表的同一部分数据,考虑用临时表和表变量暂存这部分数据。

    C、 如果需要综合多个表的数据,形成一个结果,可以考虑用临时表和表变量分步汇总这多个表的数据。

    D、其他情况下,应该控制临时表和表变量的使用。

    E、 关于临时表和表变量的选择,很多说法是表变量在内存,速度快,应该首选表变量,但是在实际使用中发现,这个选择主要考虑需要放在临时表的数据量,在数据量较多的情况下,临时表的速度反而更快。

    F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择,我们做过测试,一般情况下,SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多,但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS,在多用户并发环境下,容易阻塞其他进程,所以我的建议是,在并发系统中,尽量使用CREATE TABLE + INSERT INTO,而大数据量的单个语句使用中,使用SELECT INTO。

    G、  注意排序规则,用CREATE TABLE建立的临时表,如果不指定字段的排序规则,会选择TEMPDB的默认排序规则,而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同,连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。

    5、 子查询的用法

    子查询是一个 SELECT 查询,它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。

    子查询可以使我们的编程灵活多样,可以用来实现一些特殊的功能。但是在性能上,往往一个不合适的子查询用法会形成一个性能瓶颈。

    如果子查询的条件中使用了其外层的表的字段,这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

    关于相关子查询,应该注意:

    A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如:

    1. SELECT PUB_NAME
      FROM PUBLISHERS
      WHERE PUB_ID NOT IN
      (SELECT PUB_ID
      FROM TITLES
      WHERE TYPE = ’BUSINESS’)


    可以改写成:

    1. SELECT A.PUB_NAME
      FROM PUBLISHERS A LEFT JOIN TITLES B
      ON        B.TYPE = ’BUSINESS’ AND
      A.PUB_ID=B. PUB_ID
      WHERE B.PUB_ID IS NULL


    1. SELECT TITLE
      FROM TITLES
      WHERE NOT EXISTS
      (SELECT TITLE_ID
      FROM SALES
      WHERE TITLE_ID = TITLES.TITLE_ID)


    可以改写成:

    1. SELECT TITLE
      FROM TITLES LEFT JOIN SALES
      ON SALES.TITLE_ID = TITLES.TITLE_ID
      WHERE SALES.TITLE_ID IS NULL


    B、 如果保证子查询没有重复 ,IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如:

    C、 IN的相关子查询用EXISTS代替,比如

    D、不要用COUNT(*)的子查询判断是否存在记录,最好用LEFT JOIN或者EXISTS,比如有人写这样的语句:

    1. SELECT JOB_DESC FROM JOBS
      WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

    应该改成:

    1. SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE
      ON EMPLOYEE.JOB_ID=JOBS.JOB_ID
      WHERE EMPLOYEE.EMP_ID IS NULL

    6、 慎用游标

    数据库一般的操作是集合操作,也就是对由WHERE子句和选择列确定的结果集作集合操作,游标是提供的一个非集合操作的途径。一般情况下,游标实现的功能往往相当于客户端的一个循环实现的功能,所以,大部分情况下,我们把游标功能搬到客户端。

    游标是把结果集放在服务器内存,并通过循环一条一条处理记录,对数据库资源(特别是内存和锁资源)的消耗是非常大的,所以,我们应该只有在没有其他方法的情况下才使用游标。

    另外,我们可以用SQL SERVER的一些特性来代替游标,达到提高速度的目的。

    A、字符串连接的例子

    这是论坛经常有的例子,就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起,用逗号连接,可能最容易想到的是用游标:

    B、 用CASE WHEN 实现转换的例子

    很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理,实际上这种情况,我们可以用CASE WHEN语句进行必要的判断处理,而且CASE WHEN是可以嵌套的。

    产生临时表后,已经按照A字段排序,但是在A相同的情况下是乱序的,这时如果需要更改序号字段为按照A字段分组的记录序号,就只有游标和变量参与的UPDATE语句可以实现了,这个变量参与的UPDATE语句如下:

    D、如果必须使用游标,注意选择游标的类型,如果只是循环取数据,那就应该用只进游标(选项FAST_FORWARD),一般只需要静态游标(选项STATIC)。

    E、 注意动态游标的不确定性,动态游标查询的记录集数据如果被修改,会自动刷新游标,这样使得动态游标有了不确定性,因为在多用户环境下,如果其他进程或者本身更改了纪录,就可能刷新游标的记录集。

    7、 尽量使用索引

    建立索引后,并不是每个查询都会使用索引,在使用索引的情况下,索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引,索引的选择和使用方法是SQLSERVER的优化器自动作的选择,而它选择的根据是查询语句的条件以及相关表的统计信息,这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。

    为了使得优化器能高效使用索引,写语句的时候应该注意:

    A、不要对索引字段进行运算,而要想办法做变换,比如

    SELECT ID FROM T WHERE NUM/2=100

    应改为:

    SELECT ID FROM T WHERE NUM=100*2

    SELECT ID FROM T WHERE NUM/2=NUM1

    如果NUM有索引应改为:

    SELECT ID FROM T WHERE NUM=NUM1*2

    如果NUM1有索引则不应该改。

    发现过这样的语句:

    1. SELECT 年,月,金额 FROM 结余表 WHERE 100*年+月=2007*100+10

    应该改为:

    1. 	SELECT 年,月,金额 FROM 结余表 WHERE 年=2007 AND 月=10

    B、 不要对索引字段进行格式转换

    日期字段的例子:

    WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’

    应该改为

    WHERE日期字段〉=’2008-08-15’         AND   日期字段<’2008-08-16’

    ISNULL转换的例子:

    WHERE ISNULL(字段,’’)<>’’应改为:WHERE字段<>’’

    WHERE ISNULL(字段,’’)=’’不应修改

    WHERE ISNULL(字段,’F’) =’T’应改为: WHERE字段=’T’

    WHERE ISNULL(字段,’F’)<>’T’不应修改

    C、 不要对索引字段使用函数

    WHERE LEFT(NAME, 3)=’ABC’ 或者WHERE SUBSTRING(NAME,1, 3)=’ABC’

    应改为:

    WHERE NAME LIKE ‘ABC%’

    日期查询的例子:

    WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为:WHERE 日期 >=’2005-11-30′ AND 日期 <’2005-12-1‘

    WHERE DATEDIFF(DAY, 日期,’2005-11-30′)>0应改为:WHERE 日期 <’2005-11-30‘

    WHERE DATEDIFF(DAY, 日期,’2005-11-30′)>=0应改为:WHERE 日期 <’2005-12-01‘

    WHERE DATEDIFF(DAY, 日期,’2005-11-30′)<0应改为:WHERE 日期>=’2005-12-01‘

    WHERE DATEDIFF(DAY, 日期,’2005-11-30′)<=0应改为:WHERE 日期>=’2005-11-30‘

    D、不要对索引字段进行多字段连接

    比如:

    WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’

    应改为:

    WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’

    8、 注意连接条件的写法

    多表连接的连接条件对索引的选择有着重要的意义,所以我们在写连接条件条件的时候需要特别的注意。

    A、多表连接的时候,连接条件必须写全,宁可重复,不要缺漏。

    B、 连接条件尽量使用聚集索引

    C、 注意ON部分条件和WHERE部分条件的区别

    9、 其他需要注意的地方

    经验表明,问题发现的越早解决的成本越低,很多性能问题可以在编码阶段就发现,为了提早发现性能问题,需要注意:

    A、程序员注意、关心各表的数据量。

    B、 编码过程和单元测试过程尽量用数据量较大的数据库测试,最好能用实际数据测试。

    C、 每个SQL语句尽量简单

    D、不要频繁更新有触发器的表的数据

    E、 注意数据库函数的限制以及其性能

    10、              学会分辩SQL语句的优劣

    自己分辨SQL语句的优劣非常重要,只有自己能分辨优劣才能写出高效的语句。

    A、              查看SQL语句的执行计划,可以在查询分析其使用CTRL+L图形化的显示执行计划,一般应该注意百分比最大的几个图形的属性,把鼠标移动到其上面会显示这个图形的属性,需要注意预计成本的数据,也要注意其标题,一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等,其中出现SCAN说明语句有优化的余地。也可以用语句

    SET SHOWPLAN_ALL ON

    要执行的语句

    SET SHOWPLAN_ALL OFF

    查看执行计划的文本详细信息。

    B、               用事件探查器跟踪系统的运行,可疑跟踪到执行的语句,以及所用的时间,CPU用量以及I/O数据,从而分析语句的效率。

    C、               可以用WINDOWS的系统性能检测器,关注CPU、I/O参数


    四、             测试、试运行、维护阶段


    测试的主要任务是发现并修改系统的问题,其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方,并进行必要的优化。主要进行语句优化、索引优化等。

    试运行和维护阶段是在实际的环境下运行系统,发现的问题范围更广,可能涉及操作系统、网络以及多用户并发环境出现的问题,其优化也扩展到操作系统、网络以及数据库物理存储的优化。

    这个阶段的优花方法在这里不再展开,只说明下索引维护的方法:

    A、              可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建,索引重建的目的是提高索引的效能。

    B、               可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新,其目的是使得统计信息更能反映实际情况,从而使得优化器选择更合适的索引。

    C、               可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题,这两个语句也能修复一般的问题。


    五、             网上资料中一些说法的个人不同意见


    1、 “应尽量避免在 WHERE 子句中对字段进行 NULL 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    SELECT ID FROM T WHERE NUM IS NULL

    可以在NUM上设置默认值0,确保表中NUM列没有NULL值,然后这样查询:

    SELECT ID FROM T WHERE NUM=0”

    个人意见:经过测试,IS NULL也是可以用INDEX SEEK查找的,0和NULL是不同概念的,以上说法的两个查询的意义和记录数是不同的。

    2、 “应尽量避免在 WHERE 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。”

    个人意见:经过测试,<>也是可以用INDEX SEEK查找的。

    3、 “应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

    SELECT ID FROM T WHERE NUM=10 OR NUM=20

    可以这样查询:

    SELECT ID FROM T WHERE NUM=10

    UNION ALL

    SELECT ID FROM T WHERE NUM=20”

    个人意见:主要对全表扫描的说法不赞同。

    4、 “IN 和 NOT IN 也要慎用,否则会导致全表扫描,如:

    SELECT ID FROM T WHERE NUM IN(1,2,3)

    对于连续的数值,能用 BETWEEN 就不要用 IN 了:

    SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”

    个人意见:主要对全表扫描的说法不赞同。

    5、 “如果在 WHERE 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    SELECT ID FROM T WHERE NUM=@NUM

    可以改为强制查询使用索引:

    SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”

    个人意见:关于局部变量的解释比较奇怪,使用参数如果会影响性能,那存储过程就该校除了,我坚持我上面对于强制索引的看法。

    6、 “尽可能的使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。”

    个人意见:“在一个相对较小的字段内搜索效率显然要高些”显然是对的,但是字段的长短似乎不是由变不变长决定,而是业务本身决定。在SQLSERVER6.5或者之前版本,不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多,所以对于那些版本,我们都是推荐使用定长字段存储一些关键字段。而在2000版本,修改了不定长字符串字段的比较方法,与定长字段的比较速度差别不大了,这样为了方便,我们大量使用不定长字段。

    7、 关于连接表的顺序或者条件的顺序的说法,经过测试,在SQL SERVER,这些顺序都是不影响性能的,这些说法可能是对ORACLE有效。


    阅读原文


    展开全文
  • 1.课程介绍 Ø1.介绍什么是mysql优化 Ø2.mysql优化方法 Ø3.Mysql索引的使用 Ø4....概述: 前面我们学习了页面静态化和redis,它们是通过不操作mysql数据库...mysql数据库优化的常见方法: 1.表的设计要合理(满足3...

    1. 课程介绍

    Ø 1.介绍什么是mysql优化

    Ø 2.mysql优化方法

    Ø 3.Mysql索引的使用

    Ø 4.分表技术

    2. mysql优化概述

    概述: 前面我们学习了页面静态化和redis,它们是通过不操作mysql数据库达到提速目的。但是某些功能是一定要操作数据库的,这就要求我们必须对mysql本身进行优化。

    mysql数据库优化的常见方法:

    1. 表的设计要合理(满足3NF) 3范式

    2. 创建适当索引[主键索引|唯一索引|普通索引|全文索引|空间索引]

    3. 对SQL语句优化---->定位慢查询(explain)

    4. 使用分表技术(重点【水平分表,垂直分表), 分区技术(了解)

    5. 读写分离(配置)

    6. 创建适当存储过程,函数,触发器

    7. 对my.ini优化,优化配置

    8. 软件硬件升级

    3. 表的设计满足3NF

    概述: 目前我们的表的设计,最高级别的范式是"6NF",对PHP程序员而言,我们的表满足3NF即可。

    3.1. 1NF

    所谓1NF,就是

    (1) 指表的属性(列)具有原子性, 即表的列的不能再分了。

     

    (2) 不能有重复的列

     

    特殊

    (1) 只要是关系型数据库,就天然的满足1NF

    (2) 常见数据库

    关系型数据库(mysql, oracle, sql server,informix, db2 , postgres)

    非关系型数据(Nosql类型的数据库由Redis, MongoDB)

    3.2. 2NF

    所谓2NF,就是指我们的表中不能有完全重复的一条记录(行).一般情况下通过设置一个主键来搞定,而且该主键是自增的。

    3.3. 3NF(外键)

    所谓3NF就是指,如果列的内容可以被推导(显式推导,隐式推导)出,那么我们就不要单独的用一列存放。

    举例:下面是满足3NF

     

     

    3.4. 反3NF

    在通常情况下,我们的表的设计要严格的遵守3NF,但也有例外。有时为了提高查询的效率,我们需要违反3NF。举例:

     

    4. 构建海量表,定位慢查询

    为了讲解这个优化,我们需要构建一个海量表(8000000),而且每条数据不一样。

    4.1. 构建海量表步骤

    (1) 创建一个测试数据库

     

    (2) 创建表

     

    CREATE TABLE dept( /*部门表*/

    deptno   MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,

    dname VARCHAR(20)  NOT NULL  DEFAULT "",

    loc VARCHAR(13) NOT NULL DEFAULT ""

    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

    加入数据: dept.sql

     

    #创建表EMP雇员

    CREATE TABLE emp

    (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/

    ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

    job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/

    hiredate DATE NOT NULL,/*入职时间*/

    sal DECIMAL(7,2)  NOT NULL,/*薪水*/

    comm DECIMAL(7,2) NOT NULL,/*红利*/

    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/

    )ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

    加入数据:emp.sql

    #工资级别表

    CREATE TABLE salgrade

    (

    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

    losal DECIMAL(17,2)  NOT NULL,

    hisal DECIMAL(17,2)  NOT NULL

    )ENGINE=MyISAM DEFAULT CHARSET=utf8;

    加入数据: salgrade.sql

    4.2. 海量表带来的问题

    看一个案例

     

     

    4.3. 先使用索引来搞定

    l 给empno段添加主键索引

    alter table emp add primary key (empno);

     

    一个表(存储引擎是MyISAM),对应三个文件 xx.frm 表结构 xx.MYD 数据文件 xx.MYI 索引文件

    l 通过测试看效果

     

    l 删除emp表的主键索引

    alter table emp drop primary key

    4.4. 如何定位慢查询(slow query)

    介绍: 在默认情况下,mysql 是不会记录慢查询的,所以我们在测试时,可以指定mysql记录慢查询.

    开启慢查询的两种方法:

    l 启动时,这样启动

    cmd>bin/mysqld.exe --safe-mode --slow-query-log 

     

    或者是

    在my.ini的[mysqld]下添加一下代码并且重启

    log-slow-queries = D:/server/mysql/mysqlslowquery.log(注意斜杠)

    注:mysql5.6版本slow-query-log-file

    long_query_time = 1 指定超过1秒算慢查询

    l 为了测试,我们修改 long_query_time

     

     

    l 记录下慢查询

     

    # Time: 141122 10:39:45

    # User@Host: root[root] @ localhost [127.0.0.1]

    # Query_time: 1.625093  Lock_time: 0.001000 Rows_sent: 0  Rows_examined: 8000000

    use testdb;

    SET timestamp=1416623985;

    select * from emp where ename='IUYTOPUYQWE';

     

    说明: Query_time是查询的时间

    Lock_time:等待时间

     

    4.5. 开启慢查询牺牲sql的执行效率

    如何使用慢查询?

    1. 系统上线之后,将慢查询开启一个星期.

    2. 当你认为系统所由于数据增多导致系统执行缓慢.再开启慢查询找到执行慢的sql语句,然后在优化它.

     

    4.6. 如何分析一个sql语句的问题-explain 工具

    l 基本用法:

    explain  sql\G

    l 案例:

     

    l 优化

    添加索引。

     

    4.7. mysql的变量查询

    mysql>show variables;

    mysql>show variables like ‘%xxxx%’;

    show  tables  like  '数据表名';//查询一个数据表是否存在

    如果需要知道每个变量的具体含义,可以查询手册.

    5. 索引的详解(重点)

    5.1. 索引创建

    5.1.1. 主键索引的创建

    主键索引的创建有两种形式, 1.在创建表的时候,直接指定某列或者某几列为主键,这时就有主键索引, 2. 添加表后,再指定主键索引

    l 直接创建主键索引

    注意:如果是自增, 该主键不能够删除

     

    l 先创建表,再指定主键

     

    增加主键

    ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列名2..)

    l 主键索引的特点

    1. 一个表最多只能有一个主键

    2. 一个主键可以指向多列(复合主键)

    3. 主键索引的效率是最高,因此我们应该给id,一般id是自增.

    4. 主键索引列是不能重复,也不能为null

    5.1.2. 唯一索引的创建

    l 直接在创建表的时候,指定某列或某几列为唯一索引

     

    l 把表创建好后,再指定某列或者某几列为唯一索引

     

    说明: 使用 create unique index 指令,必须指定索引名。

     

    说明: 使用alter table 指令,可以指定索引名,也可以不指定。

    l 唯一索引的特点

    1) 一张表可以有多个唯一索引

    2) 唯一索引不能重复,但是如果你没有指定not null ,唯一索引列可以为null,而且可以有多个.

    3) 什么时候使用唯一索引,当某列数据不会重复,才能使用

    4) 唯一索引效率也很高,可以考虑优先使用

    5.1.3. 普通索引的创建

    l 在创建表时指定索引,通过key或者index

     

    l 把表创建好后,再指定某列或者某几列为索引

     

    l 添加普通索引(2种方式)

     

     

    l 特点

    1) 一张表中可以有多个普通索引,一个普通索引页可以指向多列

    2) 普通索引列的数据可以重复

    3) 效率相对而言低.

    5.2. 索引的查询

    l desc 表名

    l show keys from 表名\G

    l show index from 表名\G

    l show indexes from 表名\G

    5.3. 索引的修改

    先删除,再添加。

    5.4. 索引的删除

    DROP  INDEX  索引名  ON  表;

    ALTER  TABLE 表名 DROP INDEX 索引名;

     

    5.5. 索引的注意事项

     

    索引的缺点:

    增删改速度慢..

    优点:

    查询速度快

     

    建立索引一定要根据自己的需求来…

    实例:

    登录用户名是否适合建索引? 用户名适合建立索引

     

    操作日志:

    用户名    操作的哪个控制器的哪个方法    操作时间记录

    不合适建立索引..

    6. sql语句的优化和正确使用索引

    6.1. 对于创建的多列(复合)索引,只要查询条件使用了最左边的列,索引一般就会被使用

    name  email

     alter table xxx  add index (name,email)

    select * from xx where name = ‘xxx’;

    select * from xx where email = ‘xxx’;

     

     

     

    说明: dname是左边的列,因此我们发现使用到dname,就使用到索引,而下面的sql语句,没有使用到索引。

    6.2. 对于使用like的查询,查询如果是‘%aaa’‘_aa' 不会使用到索引‘aaa%’会使用到索引

     

    说明: 在like语句中,如果 '' 中最前有 _ 或者 %就使用不到索引,如果在中间或者最后有 _ 或者 %可以使用到索引。

    6.3. 如果条件中有or,则要求or的所有字段都必须有索引,否则不会使用索引

     

    说明:因为 deptno 没有索引,所以整个sql语句就没有使用到索引。

     

    如果在 deptno上也创建索引,就可以使用到索引了.

    如果mysql认为全表扫描效率更高,就不会使用索引,而会全表扫描

    6.4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引

     

     

    6.5. 有些情况下,可以使用连接来替代子查询。因为使用joinMySQL不需要在内存中创建临时表

    子查询:select * from emp where deptno in (select deptno from dept)

    连接:select * from emp left join dept on emp.deptno=dept.deptno where emp.deptno=dept.deptno

    6.6. 管理员在导入大量数据,可以这样提高速度

    大批量插入数据(MySql管理员) 了解

    对于MyISAM:

     alter table table_name disable keys;

    执行insert语句导入

    alter table table_name enable keys;

    对于Innodb:

    1, 将要导入的数据按照主键排序

    2, set unique_checks=0,关闭唯一性校验。

    3, set autocommit=0,关闭自动提交。

    6.7. 如何选择存储引擎

     

    l 如何选择的原则

    (1) MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子/信息表/新闻/商品表表)

    (2) InnoDB:提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM,写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高,则使用innodb)。[账户,积分,余额]

    6.8. 如何选择正确的数据类型

    6.8.1. 在满足需求的情况下尽量选择小的类型.

    6.8.2. 在精度要求高的应用中,建议使用定点数来存储数值,以保证结果的准确性。decimal 不要用float.

    举例:

     

     

    说明: 这里我们看的 float(10,2) , 和 decimal(10,2) decimal 更精准。所以我们对精度高的列,要使用decimal 类型。

    6.8.3. 对存储引擎是MyISAM的表,要定时碎片整理

    举例说明:当我们在users表中有大量数据时,我们delete 数据后,我们发现磁盘空间没有回收,因此我们需要定时的进行碎片整理.如下:

    创建表:

     

    复制大量数据到同一个表中:

     

     

     

    optimize: 该命令可以使表中的数据彻底从数据文件中删除.

    (本文由源码时代技术老师原创发布,转载请注明来源。)

    展开全文
  • 数据库优化(MySQL)

    2021-04-09 10:12:21
    数据库优化 文章目录数据库优化1.... 数据库优化方案很多,主要分为两大类:软件层面、硬件层面。 软件层面:SQL 调优、表结构优化、读写分离、数据库集群、分库分表等。 硬件层面:主要是增加机器性能,性能瓶颈
  • 全面深入Mysql数据库优化

    千人学习 2019-09-26 11:44:58
    本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧 、应用优化、数据库优化、数据库日志等方面的知识,并通过综合案例,对课程中的知识进行一个整合...
  • 大家好,小C将继续与你们见面,带来精选的CSDN博文~ 又到周一啦,上周的系统化学习专栏已经结束,...今天小C挑选的文章是有关于“数据库优化”的内容,如果感兴趣的话那就继续一起学习吧! 从网上去搜数据库优化.
  • oracle数据库优化

    2017-11-08 21:26:00
    数据库优化: 一.oracle数据库软件系统调整 1.oracle初始化参数文件。 2.oracle动态视图:v$sqlv$sqlareav$sqltextv$session+v$session_waitv$processv$sysstatv$system_eventv$parameter 3.oraclesga调整. 二....
  • 大家好,小C将继续与你们见面,带来精选的CSDN博文~ 又到周一啦,上周的系统化学习专栏已经结束,我们...今天小C挑选的文章是有关于“数据库优化”的内容,如果感兴趣的话那就继续一起学习吧! 前面一篇文章从..
  • Java系统优化思路:数据库优化 原则:自顶向下优化法 具体优化: 一、单机环境 1)Java应用中SQL语句优化 2)MySQL软件优化(如:存储引擎等) 3)MySQL服务器优化(如:操作系统、硬件) 4)Java本地缓存(如JDK...
  • WP Clean Up 插件是一个wordpress数据库清理优化插件,启用...本人已对网上常见的wordpress数据库优化软件进行比较,如wp sweep 、wp dbmanger、wp optimize ,结果发现wp clean up 的清理效果最佳。我们用wp clean up
  • SQL优化 数据库优化

    2012-02-03 15:06:00
    1) 根据查询条件,建立索引,优化索引,优化访问方式,限制结果集的数据量. 2) 对于大的数据库不要设置数据库自动增长,它会降低服务器的性能. 3) 定义主健,唯一性约束和外键,这样做可以节约大量的时间....
  • nysql数据库优化

    2019-03-04 11:22:00
    软件优化 my.cnf参数优化,命令监控show global status\G 调优工具mysqlreport sql语句优化 索引的优化 白名单机制--百度,就是让一些不规范的语句执行查询前经过DBA的确认,不通过不给执行,项目开发时让DBA...
  • 数据库优化方案

    2013-10-04 15:59:27
    作者: 踏雪寻觅 来源: 博客园 发布时间: 2011-04-24 21:55 阅读: 3266 次 推荐: 4 原文链接 [收藏]  摘要:一个系统的性能的提高,不单单是试运行或者维护阶段的性能调优,也不单单是...关于数据库优化方面
  • MySQL数据库优化

    2018-08-08 10:25:35
    对于大多数的后端程序员来说,Mysql应该是很...MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。  ...
  • 个人经验总结:MySQL数据库优化技巧集锦,阅读个人经验总结:MySQL数据库优化技巧集锦,一、我们可以且应该优化什么? 硬件 操作系统/软件库 SQL服务器(设置和查询) 应用编程接口(API) 应用程序 二、优化硬件 如果你...
  • 数据库SQL优化大总结之 百万级数据库优化方案 2014-07-18 09:33 雲霏霏 雲霏霏的博客 字号:T | T 网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有...
  • 一、MySQL数据库优化策略

    千次阅读 2020-05-17 18:17:01
    MySQL数据库优化策略 数据库性能取决于数据库级别的几个因素,例如表,查询和配置设置。这些软件结构导致在硬件级别执行CPU和I / O操作,必须将这些操作最小化并使其尽可能高效。在研究数据库性能时,首先要学习软件...
  • MYSQL数据库优化

    2016-08-10 17:55:02
    数据库的性能受CPU,Cache,内存,硬盘,网络影响,数据库系统出现性能问题,其根本原因是系统资源出现瓶颈,包括硬件瓶颈和软件瓶颈。软件瓶颈则是由于软件设计的数据处理不合理导致的。很多情况下,硬件瓶颈是由于应用...
  • 大型数据库优化技巧

    2019-09-25 17:46:40
    mysql数据库优化的常用方法 1、表的设计要合理(满足3NF)3范式2、创建适当的索引【主键索引】【唯一索引】【普通索引】【全文索引】【空间索引】3、对SQL语句优化--->定位慢查询(explain)4、使用分表技术...
  • Mysql数据库优化数据库层面的优化硬件层面的优化平衡可移植性和性能 数据库性能取决于数据库级别的几个因素,例如表,查询和配置设置。 这些软件构造导致在硬件级别执行CPU和I / O操作,必须将这些操作最小化并使其...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,724
精华内容 2,289
关键字:

数据库优化软件