美文网首页
mysql分享

mysql分享

作者: 渐入晚冬 | 来源:发表于2018-07-10 17:36 被阅读0次

    1.mysql基础

    1.1 sql语言基础--类别

    DML(Data Manipulation Language)数据操纵语言。这个模块可以让我们检索、修改、增加、删除数据库中的数据。(重点)

    DDL(Data Definition Language)数据定义语言。是的我们能够创建和修改数据库本身。如:DDL提供ALTER语句,他让我们可以修改数据库中表的设计。如create、drop、alter等

    DCL(Data Control Language)数据控制语言,用于维护数据库的安全。如grant、revoke等

    1.2 索引

    索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

    1.2.1 索引设计原则

        最合适索引的列是出现在where字句中的列,或连接子句中指定的列,而不是出现在select关键字后的选择列中的列

        对于唯一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行,而用来记录性别的列,只含有m和f,则对此列进行索引没有多大用处

        使用短索引,例如,如果有个CHAR(200)列,如果在前10或者20个字符内,多数值是唯一的,那么久不要对整列进行索引,对前10个或20个字符醒醒索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘io较少,较短的值比较起来更快。

        不要过度索引,不要以为索引越多越好,什么东西都用索引是错误的,每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时候,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长

    1.2.2索引类型

        普通索引-最基本的索引,它没有任何限制

        唯一索引-索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一

    1.2.3 索引失效

    隐式转换导致索引失效,容易被开发忽略:<>  not in   not exist  != like

    对索引列进行运算导致索引失效,对索引列进行运算包括:+ - * / ! 等,错误列子:select * from test where id-1=9;

    不要将空的变量直接与比较运算符号比较: 如果变量可能为空,使用IS NULL 或 NOT NULL进行比较

    不要再sql中使用双引号

    1.3 测试数据准备

        使用存储过程- 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合

        使用语言就脚本批量insert

        使用工具datafactory

        使用INSERT INTO SELECT语句,数据呈几何倍数增长

    1.4 status与variables

    ---    show global status; mysql服务器运行各种状态值,status是状态是系统的状态不可更改,是系统现在的运行状态参数

    ---    show global variables ; 查看的是mysql系统变量,是MySQL系统运行时的参数,如字符集设置、版本信息、默认参数等,除非手动修改,否则运行时一般不会改变

        查看mysql允许的最大连接数     show variables like 'max_connections' 这个参数过大占用的内存也会大

        查看mysql以往响应的最大连接数   show global status like 'Max_used_connections'

    1.5 query cache

        缓存机制简单的说就是缓存sql文本及查询结果,如果运行相同的sql,服务器直接从缓存中渠道结果,而不需要再去解析和执行sql。如果表更改了,那么使用这个表的所有缓存查询将不在有效,查询缓存的相关条目被清空。

        查询必须是完全相同的才能够被认为是相同的。

        开启query cache方法:1.修改my.cnf配置   2.set global query_cache_type='ON'

        query cache运行状态分析

            show status like '%qcache%'

            query cache 命中率=Qcache_hits/( Qcache_hits+Qcache_inserts)

            查询缓存利用率=(query_cache_size-qcache_free_memory)/query_cache_size*100%

            查询缓存利用率在25%之下的话说明query_cache_size设置的过大,可适当的减少;查询缓存利用率在80%以上而却Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小,要不就是碎片太多

    1.6 开启慢查询

        修改配置文件my.cnf添加内容:log-show-queries=/var/log/slow.log    long_query_time=2重启

        执行show variables like '%slow%'查看慢查询日志是否开启

        慢查询分析

        mysqldmpslow -s c -t 20 /var/log/slow.log 可以看出访问次数最多的20个sql语句

        mysqldumpslow -s r -t 20 /var/log/slow.log 可以看出返回记录集最多的20个sql

        mysqldumpslow -t 10 -s t -g 'left join' /var/log/slow.log 按照时间返回前十调里面包含做链接的语句

        -s 标识按照何种方式排序,c t l r 分别是按照次数、时间、查询时间、返回的记录数来排序,ac at alar 表示相应的倒序

        -t 是top n的意思,即返回签名多少条数据

        -g 后面可以跟正则表达式,大小写不敏感

    1.7 explain分析查询语句

        id:所选定执行计划中的查询的序列号,没有具体含义

        select_type:所使用的查询类型,主要是区别普通查询和联合查询子查询之类的复杂查询

        table:所访问的数据库中的表名

        type:对表所使用的访问方式。是较为重要的一个指标,一般来说,得保证查询至少到range级别,最好能到ref

        possible_keys:该查询可以利用的索引。指出mysql能使用哪个索引在该标中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验where字句,看是否引用某些字段,或者检查字段不是适合索引

        type分析:表示mysql在表中找到所需行的方式,即访问类型,常见类型如下,由左到右,由最差到最好

        key分析:显示mysql在查询中实际使用的索引,若没有使用索引,显示为NULL

        rows:表示mysql根据表统计信息及索引选用情况,估算的找到所需的距离所需要读取的行数

        extra(重点):如果是only index,意味着信息只用索引树种的信息检索出的,这比扫描整个表要快

            如果是where used 就是使用上了where限制

            如果是impossible where 表示用不着where,一般就是没查出来啥。

            如果此信息显示using filesort或者是using temporary的话会很吃力,where和order by的索引以经常无法兼顾,如果按照where来确定索引那么在order by时,就必要会引起using filesort,这就要看是先过滤在排序划算,还是先排序再过滤划算。

    explain局限性

        explain不会告诉你关于触发器、存储过程的信息或者用户自定义函数对查询的影响情况

        explanin不考虑各种cache

        explain不能显示mysql在执行查询时所做的优化工作,只能解释select操作

    相关文章

      网友评论

          本文标题:mysql分享

          本文链接:https://www.haomeiwen.com/subject/jkmoyxtx.html