• 专注于网站建设,网络推广,关键词优化,SEO优化,专业的网站建设开发团队!

叶景网络之Mysql优化的学习笔记

2015-06-13 17:21:45   来源:叶景网络
    mysql 的优化
    1.数据库设置要合理(符合 3NF 三范式)有时需要适当的逆范式。
    2.sql语句的优化
    3.数据的配置
    4.适当硬件配置和操作
    //================================
    通俗地理解三个范式,对于数据库设计大有好处。
    第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;(只要是关系型数据库都满足1NF)
    第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
    第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。 没有冗余的数据库设计可以做到。
    但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是: 在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。 
    //========================================
    SQL优化的一般步骤
    //========================================
    通过show status命令了解各种SQL的执行频率。(查看数据库当前的状态)
    定位执行效率较低的SQL语句-(重点select)
    通过explain分析低效率的SQL语句的执行情况
    确定问题并采取相应的优化措施
    //========================================
    sql语句有几类?
    ddl(数据定义语句)【create alter drop】
    dml(数据操作语句)【insert delete update】
    select
    dtl(数据事务语句)【commit rollback savepoint】
    dcl(数据控制语句)【grant revoke】
    //============================================
    重点:select
    用命令show status,可以在mysql控制台下执行
    我们主要关心的是com 开头的指令
    show status like 'Com%' 可以查看执行次数(等价于下一句)
    show session status like 'Com%' 查看当前会话
    show global status like 'Com%' 查看全部的
    //==============================================
    还有几个常用的参数便于用户了解数据库的基本情况。
    Connections:试图连接MySQL服务器的次数
    命令如下[在mysql控制台下执行]:show status like 'Connections';
    --------------------------------------------------------------
    Uptime:服务器工作的时间(单位秒)
    命令如下[在mysql控制台下执行]:show status like 'Uptime';
    --------------------------------------------------------------
    Slow_queries:慢查询的次数 (默认是10s)慢查询:是查询时间大于等于10s
    命令如下[在mysql控制台下执行]:show status like 'Slow_queries';
    查看慢查询的默认时间:
    命令如下[在mysql控制台下执行]:show variables like 'long_query_time';
    --------------------------------------------------------------
    设置mysql 控制器的的编码命令:set names gbk;
    #-----------------------------------------------------------
    #关于MYISAM存储引擎的文件解析
    db.frm ——结构表
    db.MYD ——数据表
    db.MYI ——索引表
    #-----------------------------------------------------------
    《创建一个海量的数据表》mysql 的存储过程
    -------------------------------------------------------------
    #创建表DEPT
    #------------------------------------------------
    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 ;
    #创建表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 ;
    #工资级别表
    #------------------------------------------------
    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;
    INSERT INTO salgrade VALUES (1,700,1200);
    INSERT INTO salgrade VALUES (2,1201,1400);
    INSERT INTO salgrade VALUES (3,1401,2000);
    INSERT INTO salgrade VALUES (4,2001,3000);
    INSERT INTO salgrade VALUES (5,3001,9999);
    # 随机产生字符串
    #定义一个新的命令结束符合
    #------------------------------------------------
    delimiter $$
    #删除自定义的函数
    #-----------------------------------------------
    drop  function rand_string $$
    #这里创建了一个函数(储存过程),主要用于产生随机的名字
    #------------------------------------------------
    create function rand_string(n INT)
    returns varchar(255)
    begin 
    declare chars_str varchar(100) default
    'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do 
    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    set i = i + 1;
    end while;
    return return_str;
    end $$
    delimiter ;
    select rand_string(6);
    # 随机产生部门编号
    #------------------------------------------------
    delimiter $$
    #删除自定义的函数
    #-----------------------------------------------
    drop  function rand_num $$
    #这里又自定了一个函数(储存过程),主要用于产生随机的数字
    #------------------------------------------------
    create function rand_num( )
    returns int(5)
    begin 
    declare i int default 0;
    set i = floor(10+rand()*500);
    return i;
    end $$
    #------------------------------------------------
    delimiter ;
    select rand_num();
    #******************************************
    #向emp表中插入记录(海量的数据)
    #------------------------------------------------
    delimiter $$
    #删除自定义的函数
    #-----------------------------------------------
    drop procedure insert_emp $$
    #------------------------------------------------
    create procedure insert_emp(in start int(10),in max_num int(10))
    begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
    set i = i + 1;
    insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i = max_num
    end repeat;
    commit;
    end $$
    delimiter ;
    #调用刚刚写好的函数, 1800000条记录,从100001号开始
    call insert_emp(100001,5000000);#添加五百万条记录
    #**************************************************************
    #  向dept表中插入记录
    delimiter $$
    #删除自定义的函数
    #-----------------------------------------------
    drop procedure insert_dept $$
    #------------------------------------------------
    #这里又自定了一个函数(储存过程),主要用于添加数据
    create procedure insert_dept(in start int(10),in max_num int(10))
    begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
    set i = i + 1;
    insert into dept values ((start+i) ,rand_string(10),rand_string(8));
    until i = max_num
    end repeat;
    commit;
    end $$
    #------------------------------------------------
    delimiter ;
    call insert_dept(100,10);
    #------------------------------------------------
    #以下测试不需要了
    #------------------------------------------------
    #向salgrade 表插入数据(此表测试时不用了,前面已经用insert 语句添加了)
    delimiter $$
    drop procedure insert_salgrade $$
    create procedure insert_salgrade(in start int(10),in max_num int(10))
    begin
    declare i int default 0; 
    set autocommit = 0;
    ALTER TABLE emp DISABLE KEYS;  
    repeat
    set i = i + 1;
    insert into salgrade values ((start+i) ,(start+i),(start+i));
    until i = max_num
    end repeat;
    commit;
    end $$
    delimiter ;
    #------------------------------------------------
    #测试不需要了
    #call insert_salgrade(10000,1000000);
    #------------------------------------------------
    在一个项目中,找到慢查询的select mysql数据库支持的慢查询语句,记录到日志中,程序员分析,但是:默认情况下不启用
    mysql启用方式:进入到mysql 的安装目录-》mysql/bin/mysql.exe
    同理启用日志
    设置慢查询的时间(1秒):set long_query_time=1;
    找出慢查询的语句:show status like 'slow%';
    =》开始优化
    优化方式:加索引(主键索引。唯一索引。普通索引。全文索引)四种索引
    在海量表emp的列上建立索引
    alter table emp add primary key(empno);(加主键索引)
    #删除主键索引
    alter table emp drop primary key
    #-----------------------------------------------------------------------
    可行性评价分析:
    1.加索引可以加快查询的速度,是以牺牲删除、修改、添加(dml语句)为代价
    2.索引信息是存在*.myi文件中的,添加索引以后,些文件加大会占用空间(以空间换取时间)
    #-----------------------------------------------------------------------
    一款标非常重要的工具 explain(在控制台下直接用下面命令),可以对sql语句进行分析,可以预测sql语句的运行效率
    例如:explain select * from emp where empno=2000000\G
    执行结果如下:
    id: 1
    select_type: SIMPLE
    table: emp
    type: const
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 3
    ref: const
    rows: 1
    Extra:
    1 row in set (0.03 sec)
    #---------------------------
    参数说明:
    type:有三个参数(all const system)
    all(全表扫描,通常不这样做,很浪费时间)
    system (表只有一行《系统表》,这是const联接类型的一个特例)
    const (表最多有一个匹配行)
    #--------
    Extra:有四个参数(No tables、Using filesort、Using temporary、Using where)
    参数说明:
    No tables  (Query 语句中使用FROM DUAL 或不含任何FROM语句)
    如: explain select max(10) from emp\G
    Using filesort 当Query中包含ORDER BY 操作,而且无法利用索引完成的排序,
    Using temporary某些操作必须使用临时表,常见GROUP BY ORDER BY
    Using where 不用读取表中所有的信息,仅通过索引就可以获取所需的数据
    #--------------------------------------------------------
    在什么列上加索引比较合适?
    #--------------------------------------------------------
    1.较频繁的作为查询条件字段应该创建索引(经常查询列上加索引)
    例如:select * from emp where empno=1
    2.唯一性太差的字段不适合单独创建索引
    例如:select * from emp where sex="男"
    3.更新非常频繁的字段不适合创建索引
    例如:select * from where logincount =1
    4、不会出现在WHERE子句中字段不该创建索引
    #--------------------------------------------------------
    索引的种类:
    1.主键索引(把某列高为主键,则就是主键的索引)
    2.唯一索引(unique)(即该列具有唯一性,同时又是索引)
    如创建一张表:create table aa(id int primary key,name varchar(32) unique);
    3.index (普通索引)如: show indexes from emp\G
    4. 全文索引(FULLTEXT)(只有MYISAM存储引擎支持)
    sphinx + 中文分词 coreseek(sphinx技术)
    如:select * from article where content like '碧波君子';
    综合使用=》复合索引
    5.复合索引(不是一个具体的索引)多列和在一起的索引
    例如:create index myind on 表名(列一,列二)
    #================================================================
    建立索引:
    create[UNIQUE|FULLTEXT] index index_name on tbl_name(col name[(length)][ASC|DESC],……);
    或:alter table table_name ADD INDEX[index_name][index_col_name,……]
    如:alter table emp add primary key(empno);
    #---------------------------------------------------------------
    如果创建unique \普通\fulltext索引
    1.create [unique|fulltext] index 索引名 on 表名(列名,……);
    2.alter table 表名 add index 索引名(列名,……);
    如:alter table emp add index myindex(ename);#添加一个索引(共有两个索引empno ename)
    再查看索引:show keys from emp \G
    如果如下:
    *************************** 1. row ***************************
    Table: emp
    Non_unique: 0
    Key_name: PRIMARY
    Seq_in_index: 1
    Column_name: empno
    Collation: A
    Cardinality: 5000000
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 2. row ***************************
    Table: emp
    Non_unique: 1
    Key_name: myindex
    Seq_in_index: 1
    Column_name: ename
    Collation: A
    Cardinality: 833333
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    2 rows in set (0.03 sec)
    #--------------------------------------------------
    另一种方法查看索引:
    desc emp;
    结果如下:
    +----------+-----------------------+------+-----+---------+-------+
    | Field    | Type                  | Null | Key | Default | Extra |
    +----------+-----------------------+------+-----+---------+-------+
    | empno    | mediumint(8) unsigned | NO   | PRI | 0       |       |
    | ename    | varchar(20)           | NO   | MUL |         |       |
    | job      | varchar(9)            | NO   |     |         |       |
    | mgr      | mediumint(8) unsigned | NO   |     | 0       |       |
    | hiredate | date                  | NO   |     |         |       |
    | sal      | decimal(7,2)          | NO   |     |         |       |
    | comm     | decimal(7,2)          | NO   |     |         |       |
    | deptno   | mediumint(8) unsigned | NO   |     | 0       |       |
    +----------+-----------------------+------+-----+---------+-------+
    -----------------------------------------
    建立多列索引
    -----------------------------------------
    利用另一个表:desc dept;
    +--------+-----------------------+------+-----+---------+-------+
    | Field  | Type                  | Null | Key | Default | Extra |
    +--------+-----------------------+------+-----+---------+-------+
    | deptno | mediumint(8) unsigned | NO   |     | 0       |       |
    | dname  | varchar(20)           | NO   |     |         |       |
    | loc    | varchar(13)           | NO   |     |         |       |
    +--------+-----------------------+------+-----+---------+-------+
    alter table dept add index myindex (dname,loc);
    然后执行下语句:explain select * from dept where dname='aaa'\G
    如果如下(用到的所建的索引):
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: dept
    type: ref
    possible_keys: myindex
    key: myindex
    key_len: 62
    ref: const
    rows: 1
    Extra: Using where
    1 row in set (0.00 sec)
    然后执行下语句:explain select * from dept where loc='aaa'\G
    如果如下(没用到的所建的索引):
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: dept
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 15
    Extra: Using where
    1 row in set (0.00 sec)
    #------------------------------------
    注:
    下列情况一般不使用索引:
    1.条件中有or ,即使其中有条件带有索引也不使用
    2.对于多列的索引,不是使用的第一部分,则不使用索引
    3.like 查询中,如果以%开头的不使用索引
    4.如果列类型是字符串,那必须用引号将其引起来(字符型),否则不使用索引
    5.如果 mysql 估计(这是数据库底层的算法)使用全表查询要比使用索引快的话,则不使用索引
    例如:explain select * from dept where dname like 'aaa%'\G//可以用索引
    explain select * from dept where dname like '%aaa'\G//没用到索引,%写在前面了(必须注意的小技巧)
    explain select * from dept where dname='aaa' or loc='bbb'\G
    explain select * from dept where loc='aaa' or dname='fff'\G(这两个中用到了or 而致使没用索引,所以少用or)
    explain select * from dept where dname=11111\G(注意dname='11111'用引号,才能使用索引)
    #--------------------------------------------------
    如果需要添加主键索引
    alter table 表名 add primary key(列名1,列名2……)
    删除索引:
    1.drop index 索引名 on 表名
    2.alter talbe 表名 drop index index_name,
    3.alter table 表名 drop primary key
    显示索引
    show index (es) from 表名
    show keys from 表名
    #--------------------------------------------------
    如何检测所建的索引是否有效?
    命令如下:
    show status like 'handler_read%';
    执行结果如下:
    +-----------------------+----------+
    | Variable_name         | Value    |
    +-----------------------+----------+
    | Handler_read_first    | 1        |
    | Handler_read_key      | 12       |
    | Handler_read_next     | 0        |
    | Handler_read_prev     | 0        |
    | Handler_read_rnd      | 0        |
    | Handler_read_rnd_next | 88600472 |
    +-----------------------+----------+
    说明 :
    Handler_read_key(这个值越大越好)
    Handler_read_rnd_next(这个值越小越好)
    #================================================================
    对于大量地插入数据时:
    1.对于MYISAM
    alter talbe table_name disable keys;#关闭禁用Keys
    loading data;#插入数据
    alter table table_name enable keys;#启用Keys
    2.对innodb 
    将要导入的数据按照主键排序
    set unique_checks=0 关闭唯一性校验
    set autocommit=0 关闭自动提交
    #--------------------------------------
    MYISAM 和Innodb 的区别是什么?
    MYISAM 不支持外键,Innodb支持
    MYISAM 不支持事务
    MYISAM 的删除时默认空间不释放的(注)
    新建另一张表tt:create table tt(id int);
    添加数据: insert into tt values(1);
    不断添加自身数据:insert into tt select * from tt;
    对于MYISAM 的删除时默认空间不释放的的问题解决方法:
    清理数据表的命令:optimize table 表名;
    如:
    mysql> optimize table bb;
    +-----------+----------+----------+----------+
    | Table     | Op       | Msg_type | Msg_text |
    +-----------+----------+----------+----------+
    | testdb.bb | optimize | status   | OK       |
    +-----------+----------+----------+----------+
    #--------------------------------------
    常见的sql 优化手法:
    1.使用group by 语句 (用order by null来禁用排序)
    explain select * from dept group by dname\G      #排序
    结果:
    *************************** 1. row *******************
    id: 1
    select_type: SIMPLE
    table: dept
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 15
    Extra: Using temporary; Using filesort
    1 row in set (0.00 sec)
    explain select * from dept group by dname order by null\G #禁用排序
    结果:
    *************************** 1. row *****************
    id: 1
    select_type: SIMPLE
    table: dept
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 15
    Extra: Using temporary
    1 row in set (0.01 sec)
    #-------------------------------------------------------
    有些情况下,可以使用连接来替代了查询
    如:join   MYSQL不需要在内存中创建临时表,速度快。(好用的东西,哈哈!)
    如果想要在有OR的查询语句 中利用索引,则or之间的每个条件列都必须用到索引,如果没有索引,则应该考虑增加索引
    #-------------------------------------------------------
    重点:(注意:数据类型)
    一、在一个要求高精度的应用系统中,建议作用定点数(decimal),来存储数值,以保证结果的准确性(金融、财务系统)
    如有 5000000.22万
    create table salg(s float(10,2));//这个可能导致数据丢失
    create table sal(s decimal(10,2));//用定点数
    二、日期类型要根据实际所需要选择能够满足应用的最小存储的早期类型
    create talbe bbs(id int,con varchar(2048),pub_time int);
    如:date('Ymd',时间-3*24*60*60);
    date 函数的问题是它最大值为2038年1月19日(其实的windows系统的问题)
    #-------------------------------------------------------
    表的水平划分
    #-------------------------------------------------------
    当一个表很大时(如10亿条数据):得用
叶景网络,网络推广,网站建设
特别推荐RECOMMEND
搜索引擎网站关键词seo优化的意义
视频推广怎么做
网站seo优化怎么做关键词排上首页?
SEO介绍什么叫搜索引擎网站关键词优化
seo优化怎样做提升关键词排名
热点图文Hot
没有加www域名打不开的解决方案 没有加www域名打不
叶景网络之CodeIgniter框架的学习 叶景网络之CodeIgni
叶景网络之Ajax学习笔记 叶景网络之Ajax学习
叶景网络之DIV+CSS学习笔记 叶景网络之DIV+CSS
关注排行RANKING
1

没有加www域名打不开的解决方案

没有加www域名打不开的解决方案 网站上线了,在做SEO时发现没带www的时候访问不了,是以前的域名绑定时没做好,这对se...

2

叶景网络之CodeIgniter框架的学习

叶景网络之CodeIgniter框架的学习 叶景网络(www cngxit com),致力于互联网品牌建设与网络营销,专业领域包括网站建...

3

叶景网络之Ajax学习笔记

叶景网络之Ajax学习笔记 叶景网络(www cngxit com),致力于互联网品牌建设与网络营销,专业领域包括网站建...

4

叶景网络之DIV+CSS学习笔记

叶景网络之DIV+CSS学习笔记 叶景网络(www cngxit com),致力于互联网品牌建设与网络营销,专业领域包括网站建设...

5

叶景网络之SQL注入与安全学习笔记

叶景网络之SQL注入与安全学习笔记 叶景网络(www cngxit com),致力于互联网品牌建设与网络营销,专业领域包括网站建...

6

叶景网络之Mysql常用命令学习笔记

叶景网络之Mysql常用命令学习笔记 叶景网络(www cngxit com),致力于互联网品牌建设与网络营销,专业领域包括网站建...

7

叶景网络之Mysql优化的学习笔记

叶景网络之Mysql优化的学习笔记 通俗地理解三个范式,对于数据库设计大有好处。 第一范式:1NF是对属性的原子性...

8

叶景网络之Gvim编辑器的学习笔记

叶景网络之Gvim编辑器的学习笔记 叶景网络(www cngxit com),致力于互联网品牌建设与网络营销,专业领域包括网...

9

叶景网络之memcached 学习笔记

叶景网络之memcached 学习笔记 memcached 是一个高效的分布式的内存对象系统,他可以支持把各种php的数据放入...