文档章节

mysql优化知识记录

liuzeyang
 liuzeyang
发布于 2017/02/16 23:13
字数 1694
阅读 7
收藏 0

mysql 优化


1.mysql优化是一个综合性的,主要方向包括
    a:表的设计要符合三范式(3NF)
    b:添加适当的索引(index)四中常见索引:普通索引、主键索引、唯一索引unique、全文索引
    c:分表技术(水平分割、垂直分割)
    d:读写(update/delete /add)分离技术
    e:存储过程 
    f:对mysql配置优化(对配置最大并发数修改 my.ini文件 max_connection一般调整到1000左右、调整缓存大小)
    g:mysql服务器硬件升级
    h:定时清除不需要的数据,定时进行碎片整理(MyISAM存储引擎)


2.sql语句本身的优化
    问题是:如何从一个大项目中,迅速的定位执行速度慢的语句?定位慢查询
    ①首相了解mysql数据库的一些运行状态如何查询(比如当前mysql运行的时间/一共执行了多少次select语句/当前连接数等)
    show status   //显示状态
    show [session|global] status like ……   //默认是session 指的是取出当前窗口执行的次数
    show status like 'com_select'   //显示执行了多少次select语句
    show status like 'connections'  //显示连接数
    show status like 'slow_queries'  //显示慢查询次数
    步骤:①显示mysql的慢查询时间默认值单位是秒
        show variables like 'long_query_time'
          ②修改mysql的慢查询时间默认值
        set long_query_time=1

    如何启动慢查询日志?记录查询过的慢查询语句
        默认情况下mysql不记录慢查询,需要在启动时指定参数
        启动mysql的命令:    bin\mysql.exe --safe-mode --slow-query--log
        生成日志文件在my.ini中的datadir指定的路径下


    3.添加索引
    1.主键
        添加索引
       方式a:    create table tableName(id int unsigned primary key auto_increment,name varchar(32
                    not null default '');
       方式b:    alter table tableName add primary key (列名);
    2.普通索引
        添加索引
        create table tableName (
            id int unsigned
            name varchar(10) not null default ''
        )
        create index 索引名 on 表名(列名)

    3.全文索引
        全文索引主要是针对对文本的检索比如文章。
        添加索引
        create table tableName(
            id int unsigned primary key auto_increment,
            title varchar(200),
            body TEXT,
            FULLTEXT(title,body)
        )engine=myisam charset utf8;

        如何使用全文索引
        错误的用法:
        select * from tableName where body like '%mysql%';【不会使用全文索引】
        正确的使用方法
        select * from tableName where match(title,body) against('querycharacter');

        说明:
        a.在mysql中fulltext索引只针对myisam生效
        b.mysql自己提供的fulltext只针对英文生效,要处理中文可以使用sphinx()
        c.使用方法是使用match(字段名……)
        

    4.唯一索引
        添加索引
        方式a:  当表的某列被指定为unique约束时,这就是一个唯一索引
            create table tableName(
                id int unsinged primary key auto_increment,
                name varchar(32) unique
            )engine = innodb charset utf8;

        这是name列就是一个唯一索引,unique字段可以为null,可以多个 
        方式b:
            create table tableName(
                id int unsinged primary key auto_increment,
                name varchar(32)
           )engine = inodb charset utf8;
        create unique index 索引名 on 表名(列名)


        删除索引
        alter table tableName drop index indexName

        查询索引
        show index from tableName 


4.索引的注意事项
    a.占用磁盘
    b.对dml语句有影响,会变慢,因为dml语句会影响btree。


5.何时何地添加索引
    a.频繁的作为查询条件的字段应该创建索引
    b.唯一性太差的字段不适合作为索引,因为构建的btree不优秀,例如性别字段
    c.更新非常频繁的字段不适合建立索引,dml语句会重建btree。
    d.不出现在where中的字段不应该建立索引

6.应该创建索引总结:
    a.肯定在where条件中经常使用,
    b.该字段的内容不是唯一的几个值例如gender
    c.字段内容不是经常变化的


7.sql语句中使用索引时的注意
    a.当sql中使用复合索引时,当条件语句中的字段是复合索引字段最左边的字段就会使用索引
否则不会使用。explain sql语句 可以帮助我们不真正的执行sql语句,去看mysql的执行计划。从为去
优化sql 情况。
    b.对于使用like查询,如果‘%’写在前面,是不会使用索引。非要使用%在前,那就只能不使
用索引,或者使用全文索引sphinx
    c.如果条件中有or,使用的所有字段都必须建立索引才能使用索引,否则不会使用索引建议大
家尽量避免使用or,or的效率不高。
    d.如果列是字符串,查询时,使用单引号引起可以使用索引,不使用不会使用索引
    e.如果mysql发现扫描全表比使用索引快时,就不会使用索引。

8.如何查看索引使用的情况
    show status 'handler_read%';
    值越高越好

9.常用sql优化
    a.group by 会对分组的数据做默认排序,当业务不需要排序时,可以把order by null
    b.很多时候使用左连接比使用子查询效率要高


10.存储引擎选择
    myisam存储:对事务要求不高,同时是以查询和添加为主的。比如评论回复表
    INNODB存储:对事务要求高,保存数据重要。比如订单表,账号表
    Memory存储:比如我们数据变化频繁,不需要入库,同事有频繁的查询修改。


11.myisam和innodb的区别
                      myisam              innodb
    批量插入       高                        低
    事务安全      不支持                支持
    全文索引       支持                  (5.5之后)支持
    锁机制          表锁                    行锁
    外键           不支持                   支持

 

12.碎片整理
    如果数据库引擎是myisam的需要定时做碎片整理。
    例如:
    create table tableName (
        id int unsigned primary key auto_increment,
        name varchar(100) not null default '' 
    )engine=myisam charset utf8;

    insert into tableName values('aaa');

    delete from tableName where id = 1;

    使用delete语句并没有真实删除数据,在这种情况下,我们应该定时对myisam进行整理。
    命令:optimize table tableName;


题外话,技术就是窗户纸,捅破了,就简单了,所以要经常与大神交流,为此,我辞职了……


13.数据库备份
    a.手动备份
       cmd控制台    mysqldump -u root -proot 数据库[表名1,表名2……]  >文件路径
        mysqldump -u root -proot temp >d:\temp.bak
        如果希望备份的是数据库的某几张表    

    b.自动备份
        把备份数据库的指令写入到bat文件里,然后通过任务调度器定时调用bat文件


    c.使用备份文件恢复数据
        mysql 控制台 source 备份文件路径 例如 source d:\temp.dept.bak

    
14.mysql配置文件优化

    修改
        a.端口号
        b.最大连接数一般1000
        c.innodb的缓存(一般扩大10倍)innodb_addition_mem_pool_size=64M
    innodb_buffer_pool_size=1G,对于myisam,需要调整key_buffer_size。
    


 

 

© 著作权归作者所有

共有 人打赏支持
liuzeyang
粉丝 4
博文 26
码字总数 44450
作品 0
渝北
程序员
加载中

评论(2)

liuzeyang
liuzeyang

引用来自“lkqm”的评论

老刘,我只能说我看不懂
我留给自己的笔记,只是一个记录而已
lkqm
lkqm
老刘,我只能说我看不懂
Mysql SQL语句优化

最近找时间看了下Mysql 5.1的参考手册,作为一个开发者我对自己的要求是重点看看Mysql Replication的原理及机制,以及各种Mysql的Sql优化,毕竟稍微大点的公司都会有更专业的DBA来做数据库优...

Ambitor
2016/03/04
117
1
Optimizing MySQL Configuration |优化MySQL配置(一)

优化MySQL配置 彼得·扎伊采夫 Percona首席执行官 Percona MySQL的大学社区 议程 MySQL 配置文件优化基础知识 配置MySQL的工具 介绍部分重要的变量选项 关于MySQL 配置文件需要掌握的内容 默...

艾斯_韩
2016/07/27
455
0
[心得] 近期更新&关于Infobright

新的环境,新的机会,喜欢自由的我现在感觉不错,终于可以自在的记录我想记录的东西了,哈哈~关于GoogleApp的企业套件我也给自己弄了一套,感觉挺不错,和Outlook可以直接整合起来,不过只能...

晨曦之光
2012/03/09
0
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
MySQL JOIN连接查询知识点

MySQL JOIN连接查询知识点 MySQL支持的join操作:JOIN、STRAIGHT_JOIN、LEFT JOIN、RIGHT JOIN。不支持FULL OUTER JOIN。 在MySQL中,JOIN, CROSS JOIN, 和INNER JOIN相互等价。但是在正常的...

Gen_zhou
2016/08/12
99
0

没有更多内容

加载失败,请刷新页面

加载更多

linux 系统的运行级别

运行级别 运行级别 | 含义 0 关机 1 单用户模式,可以想象为windows 的安全模式,主要用于修复系统 2 不完全的命令模式,不含NFS服务 3 完全的命令行模式,就是标准的字符界面 4 系统保留 5 ...

Linux学习笔记
今天
2
0
学习设计模式——命令模式

任何模式的出现,都是为了解决一些特定的场景的耦合问题,以达到对修改封闭,对扩展开放的效果。命令模式也不例外: 命令模式是为了解决命令的请求者和命令的实现者之间的耦合关系。 解决了这...

江左煤郎
今天
3
0
字典树收集(非线程安全,后续做线程安全改进)

将500W个单词放进一个数据结构进行存储,然后进行快速比对,判断一个单词是不是这个500W单词之中的;来了一个单词前缀,给出500w个单词中有多少个单词是该前缀. 1、这个需求首先需要设计好数据结...

算法之名
昨天
15
0
GRASP设计模式

此文参考了这篇博客,建议读者阅读原文。 面向对象(Object-Oriented,OO)是当下软件开发的主流方法。在OO分析与设计中,我们首先从问题领域中抽象出领域模型,在领域模型中以适当的粒度归纳...

克虏伯
昨天
1
0
Coding and Paper Letter(四十)

资源整理。 1 Coding: 1.Tomislav Hengl撰写的非官方作者指南:Michael Gould•Wouter Gerritsma。 UnofficialGuide4Authors 2.R语言包rwrfhydro,社区贡献的工具箱,用于管理,分析和可视化...

胖胖雕
昨天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部