mysql优化知识记录
mysql优化知识记录
liuzeyang 发表于10个月前
mysql优化知识记录
  • 发表于 10个月前
  • 阅读 5
  • 收藏 0
  • 点赞 1
  • 评论 2

腾讯云 技术升级10大核心产品年终让利>>>   

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。
    


 

 

共有 人打赏支持
粉丝 2
博文 9
码字总数 4112
评论 (2)
lkqm
老刘,我只能说我看不懂
liuzeyang

引用来自“lkqm”的评论

老刘,我只能说我看不懂
我留给自己的笔记,只是一个记录而已
×
liuzeyang
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: