Mysql数据库优化技巧总结

Mysql数据库优化技巧总结

小技巧:创建健壮mysql table

 a:指定表存储引擎,字符集
   字符集
   latin_1 支持欧洲国家文字[不支持中文]
   iso-8859-1 latin_1别名
   很多软件默认 latin_1

   gb2312       中文英文3 6000多简体中文
   gbk 简繁 19000[*]
   gb18030      简繁 20000
   
   gbk 保存一个英文字符占1个字节
       保存一个汉字符占2个字节

   utf-8 支持全世界所有文字
       保存一个英文字符占1个字节
       保存一个汉字符占3个字节
       mysql当叫 utf8        
   
 a1:乱码
    a11:创建数据库指定编码utf8
    a12:创建表时指定编码utf8
    a13:set names utf8
        {设置mysql客户端}
        {设置mysql与服务器连接}
        {设置mysql查询结果集}
        统三处设utf8
 稍候演示
  a2:mysql '表'存储引擎
    a21 innodb[goole,baidu]
     特点:支持事务，外键，行级锁
     缺点:控制复杂，优化难度
    a22 myisam
     特点:不支持事务，外键，查询速度快
     缺点:坏了不修
    a23 memory 
     特点:所有数据在内存[速度极快]
     缺点：重启，仃电数据丢失
 
  下面范例:
  set names utf8;
  1:创建库
  CREATE DATABASE `test1`  DEFAULT CHARACTER SET utf8;
  注意 ` [ESC] 下面键
  use test1;
  2:创建表
  create table t_user15(
    id int primary key auto_increment,
    name varchar(20),
    age  int
  )ENGINE=myisam  DEFAULT CHARSET=utf8;

  创建表列选择重要:

  小技巧:
   a:一张表通用列 id int primary key 选项 auto_increment
   b:常见错误
     b.1:age int(4) [full zero]
         int 整型 -21亿 ~  21亿
         age tinyint -128 ~ 127  {练习错误}
         设计一张表：如果有数据[变化]不能设定为一列
         bdate  
     b.2 货币[有更高精度要求]
         float
         double
         decimal(10,2) 高精度浮点数
     b.3 性别
         enum('F','M')  性别
     b.4 字符串
         新闻 标题  内容
         varchar();  优选
         varchar(65535)   mysql低版本65535字节
         text();   
         mysql 5.0 65535 字符[汉字算一个英文算一个]
         varchar(20);
     练习:
         如果中文输入mysql{windows} 
         1:开发工具创建文本，将sql写;!!!不能'记事本'
         2:保存utf8
         3:set names utf8

         4:source d:/db.sql

   c:mysql 优化 !!mysql部分重点
   c1:安装优化


      安装linux mysql软件
      a:rpm 安装
      b:yum 安装[最快，最简便!!] 整体性能差一些
      c:bin 安装[公司用 繁锁]        
      d:source 源代码安装[最优方式]


      安装注意版本
      mysql 5.0
      mysql 5.1 10w/4分钟
      mysql 5.5 
      mysql 5.6 GA [正式版] 3000w 16/8分钟
      
      尽量选高版本mysql
      
      MySQL AB -> sun -> oracle
      mysql 自由软件
      percona 美国公司->mysql
 
      国内大多公司mysql oracle;percona

   c2:优化配置
      windows my.ini
      linux   my.cnf


      mysql服务器同时处理连接最大数量!
      可以修改 1024
      max_connections=100 
      max_connections=800
      a:复制 my.ini my_bankup.ini
      b:修改1024->2048
      c:保存
      d:仃止 mysql 启动 mysql


      注意:下面命令windows
        net stop mysql
        net start mysql
   
     show variables like '%max%';
     图示:max_connections.xls 

   c3:创建表优化
      1:一个数据库最多不要超过300-400张表
      2:单张表不超过50个纯INT字段
      3:单张表不要超过20 char(10)字段


      create table t_demo1(
       id int,
       name varchar(21000)
      );
      //desc 不能作用列名冲突
      //des1 列太长 name (max = 21845);
      //建议varchar(4000)

      4:如何保存ip地址
       4.1: 255.255.255.255  varchar(15)
       4.2: bigint
            int    4个字节
            bigint 8个字节
       示例演示ip存储表中
       create table t_demo2(
        id int,
        ip bigint
       );
      insert into t_demo2 values(1,inet_aton('202.207.1.9'));
      insert into t_demo2 values(2,inet_aton('192.168.10.1'));

      select id,inet_ntoa(ip) 
      from t_demo2;
      总结:inet_aton 将ip地址转数字
           inet_ntoa 将数字转ip地址

     5:保存性别 enum('M','F');
       CREATE TABLE t_demo3(
         id int,
         leven enum('0','1','2')
       );
       insert into t_demo3 values(1,'0');
       insert into t_demo3 values(2,'0');
       insert into t_demo3 values(3,'3');

       select * from t_demo3 where leven = 0;
       select * from t_demo3 where leven = '0';

     6:避免使用在字段使用 null
       create table t_emp(
        id int,
        name varchar(20),
        sal  int,
        comm int
       );
insert into t_emp(id,name,sal)values(1,'tom',1000);
insert into t_emp(id,name,sal)values(2,'jerry',1000);
insert into t_emp(id,name,sal)values(3,'kaka',1000);
        
       整数 字符型 
       sal int not null default 0,
       name varchar(20) not null default '',

      create table t_user16(
        id int primary key auto_increment,
        name varchar(25) not null default '',
        sal  int not null default 0
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      7:细致处理日期类型
      datetime 日期时间 8字节
      timestamp 日期时间 4字节
      date 日期 3字节
      time 时间 3字节
      year  年 1字节

      datetime 1000-01-01 00:00:00 - 9999-12-31 23:59:59
      timestamp 1000-01-01  2038
      mysql 用户输入日期宽松
      
      create table t_demo4(
       d datetime
      );
      insert into t_demo4 values('2000-10-10 10:01:01');
      insert into t_demo4 values('2000/10/10 10+01+01');
      insert into t_demo4 values('20001010100101');

      练习:t_demo5
      id rdate
      create table t_demo5(
        id int primary key,
        rdate timestamp not null default current_timestamp
      );
      insert into t_demo5(id)values(1);
      insert into t_demo5(id)values(2);
      insert into t_demo5(id)values(3);
      常见日期类型函数  表示当前时间
      now();current_timestamp();sysdate();
      select now(),current_timestamp(),sysdate() \G;

      注意下面示例 sleep();
      select now(),current_timestamp(),sysdate(),sleep(2),
    now(),current_timestamp(),sysdate() \G;

      sysdate() 执行到函数时间;
      now(),curent_timestamp() 执行sql时间

      日期时间运算
      select now(),now()+7  \G
      date_add(date,INTERVAL expr unit);
      date_sub();
    
      select now(),date_add(now(),INTERVAL 1 DAY);
      select now(),date_add(now(),INTERVAL 3 DAY);
      select now(),date_add(now(),INTERVAL -1 DAY);
      select now(),date_add(now(),INTERVAL 1 MONTH);
      select now(),date_add(now(),INTERVAL 1 YEAR);
     
      单位 YEAR;MONTH;DAY
           SECOND;MINUTE;HOUR;WEEK

     二个日期相差天数
     select DATEDIFF('2007-10-10','2000-10-10');
     日期格式[不同格式的年月日时分秒]
     select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
     select DATE_FORMAT(now(),'%Y年%m月%d日 %H:%i:%s');

     总结数据库优化{表}

      1:一个数据库最多不要超过300-400张表
      2:单张表不超过50个纯INT字段
      3:单张表不要超过20 char(10)字段
      4:保存ip地址 bigint
           inet_aton 将ip地址转数字
           inet_ntoa 将数字转ip地址
      5:性别 enum 类型
        操作  ''
      6:避免使用在字段使用 null
        int not null default 0;
        name varchar(20) not null default ''
        rdate timestamp not null default current_timestamp
      7:日期时间
        建议 timestamp  4
        now()                 当前时间
        sysdate()             当前时间[执行函数时间]
        current_timestamp();  当前时间
        date_add(date,INTERVAL expr unit); 加减运算
        DATEDIFF()    二个日期相差天数
        date_format() 日期格式化 
      8:最好使用数值类型 int {电脑处理数字}
      9:优先使用enum
      10:少用text/blob
      11:不要在数据库中保存图片

谢谢关注websites博客!