文档章节

mysql优化知识记录

liuzeyang
 liuzeyang
发布于 2017/02/16 23:13
字数 1694
阅读 5
收藏 0
点赞 1
评论 2

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
粉丝 3
博文 17
码字总数 36172
作品 0
渝北
程序员
加载中

评论(2)

liuzeyang
liuzeyang

引用来自“lkqm”的评论

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

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

Ambitor ⋅ 2016/03/04 ⋅ 1

SQL-SQL优化-索引

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

掘金官方 ⋅ 2017/12/25 ⋅ 0

Optimizing MySQL Configuration |优化MySQL配置(一)

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

艾斯_韩 ⋅ 2016/07/27 ⋅ 0

[心得] 近期更新&关于Infobright

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

晨曦之光 ⋅ 2012/03/09 ⋅ 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 ⋅ 0

Mysql从入门到精通视频课程【典藏版】

从零基础学习MySQL/MariaDB数据库,为我们的Linux运维技术加分,每个成功的男人背后都有个她支撑着,而每个NB的架构后面都有一个MySQL支撑着。 1、Mysql/MariaDB数据库入门简介 本套课程将跟...

让往事随风 ⋅ 2016/04/08 ⋅ 3

浅谈MySQL SQL优化

本文首发于个人微信公众号《andyqian》,期待你的关注 前言 有好几天没有写文章了,实在不好意思。之前就有朋友希望我写写MySQL优化的文章。我迟迟没有动笔,主要是因为,SQL优化这个东西,很...

andyqian ⋅ 01/30 ⋅ 2

[慢查优化]慎用MySQL子查询,尤其是看到DEPENDENT SUBQUERY标记时

写在前面的话: ——MySQL 的子查询为什么有时候很糟糕—— 引子:这样的子查询为什么这么慢? 下面的例子是一个慢查,线上执行时间相当夸张。为什么呢? SELECT gid,COUNT(id) as count FR...

旁观者-郑昀 ⋅ 2013/11/29 ⋅ 1

「mysql优化专题」90%程序员都会忽略的增删改优化(2)

前文一篇「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)让大家知道msql优化,究竟在优化什么,本篇为mysql优化专题的第二篇,主要先从增删改进行优化。大家可以收藏关注一波,若...

java进阶架构师 ⋅ 2017/11/22 ⋅ 0

mysql 子查询优化一例

写在前面的话: 在慢查优化1和2里都反复强调过 explain 的重要性,但有时候肉眼看不出 explain 结果如何指导优化,这时候还需要有一些其他基础知识的佐助,甚至需要了解 MySQL 实现原理,如子...

Linland ⋅ 2015/04/28 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

个人博客的运营模式能否学习TMALL天猫质量为上?

心情随笔|个人博客的运营模式能否学习TMALL天猫质量为上? 中国的互联网已经发展了很多年了,记得在十年前,个人博客十分流行,大量的人都在写博客,而且质量还不错,很多高质量的文章都是在...

原创小博客 ⋅ 34分钟前 ⋅ 0

JavaScript零基础入门——(十一)JavaScript的DOM操作

JavaScript零基础入门——(十一)JavaScript的DOM操作 大家好,欢迎回到我们的JavaScript零基础入门。最近有些同学问我说,我讲的的比书上的精简不少。其实呢,我主要讲的是我在开发中经常会...

JandenMa ⋅ 今天 ⋅ 0

volatile和synchronized的区别

volatile和synchronized的区别 在讲这个之前需要先了解下JMM(Java memory Model :java内存模型):并发过程中如何处理可见性、原子性、有序性的问题--建立JMM模型 详情请看:https://baike.b...

MarinJ_Shao ⋅ 今天 ⋅ 0

深入分析Kubernetes Critical Pod(一)

Author: xidianwangtao@gmail.com 摘要:大家在部署Kubernetes集群AddOn组件的时候,经常会看到Annotation scheduler.alpha.kubernetes.io/critical-pod"="",以表示这是一个关键服务,那你知...

WaltonWang ⋅ 今天 ⋅ 0

原子性 - synchronized关键词

原子性概念 原子性提供了程序的互斥操作,同一时刻只能有一个线程能对某块代码进行操作。 原子性的实现方式 在jdk中,原子性的实现方式主要分为: synchronized:关键词,它依赖于JVM,保证了同...

dotleo ⋅ 今天 ⋅ 0

【2018.06.22学习笔记】【linux高级知识 14.4-15.3】

14.4 exportfs命令 14.5 NFS客户端问题 15.1 FTP介绍 15.2/15.3 使用vsftpd搭建ftp

lgsxp ⋅ 今天 ⋅ 0

JeeSite 4.0 功能权限管理基础(Shiro)

Shiro是Apache的一个开源框架,是一个权限管理的框架,实现用户认证、用户授权等。 只要有用户参与一般都要有权限管理,权限管理实现对用户访问系统的控制,按照安全规则或者安全策略控制用户...

ThinkGem ⋅ 昨天 ⋅ 0

python f-string 字符串格式化

主要内容 从Python 3.6开始,f-string是格式化字符串的一种很好的新方法。与其他格式化方式相比,它们不仅更易读,更简洁,不易出错,而且速度更快! 在本文的最后,您将了解如何以及为什么今...

阿豪boy ⋅ 昨天 ⋅ 0

Python实现自动登录站点

如果我们想要实现自动登录,那么我们就需要能够驱动浏览器(比如谷歌浏览器)来实现操作,ChromeDriver 刚好能够帮助我们这一点(非谷歌浏览器的驱动有所不同)。 一、确认软件版本 首先我们...

blackfoxya ⋅ 昨天 ⋅ 0

线性回归原理和实现基本认识

一:介绍 定义:线性回归在假设特证满足线性关系,根据给定的训练数据训练一个模型,并用此模型进行预测。为了了解这个定义,我们先举个简单的例子;我们假设一个线性方程 Y=2x+1, x变量为商...

wangxuwei ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部