精华内容
参与话题
问答
  • mysql优化

    万次阅读 2019-03-22 19:45:49
    Mysql优化 优化的目标是尽可能减少JOIN中Nested Loop的循环次数,以此保证:永远用小结果集驱动大结果集 left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。 在WHERE 语句...

    Mysql优化

    索引优化

    • 单列索引不存储null值,复合索引不存储全为null的值。索引不能存储null,所以对这列采用is null条件时,因为索引上根本没null值,不能利用到索引,只能全表扫描。(mysql可以 参考地址)
    • 不要再重复数据较多的列设索引
    • 对于经常在where子句使用的列,最好设置索引,这样会加快查找速度
    • 对于有多个列where或者order by子句的,应该建立复合索引
    • 在WHERE 语句中,尽量避免对索引字段进行计算操作,因为在索引列上计算会导致索引失效
    • 在join表的时候使用相当类型的例,并将其索引
    • 不建议使用%或者’-'前缀模糊查询 LIKE “%name”或者LIKE “%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。
    • 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不会使用索引
    • 尽量不要对索引字段使用not in和<>操作,会导致索引失效
    • 如果条件中有or(多字段时),即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or,又想让索引生效,只能将or条件所有字段建成复合索引

    sql语句优化

    • 避免 SELECT *(增加网络传输的负载)
    • 当只要一行数据时使用 LIMIT 1
    • 优化的目标是尽可能减少JOIN中Nested Loop的循环次数,以此保证:永远用小结果集驱动大结果集
    • left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。
    • 多使用explain和profile分析查询语句
    • 对于经常使用的查询,可以开启缓存

    表的优化

    • 遵循数据库三大范式
      三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库。
      1)第一范式 如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库满足第一范式
      2)第二范式 实体中每一行的所有非主属性都必须完全依赖于主键 即:非主属性必须完全依赖于主键。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
      3)第三范式 实体中的属性不能是其他实体中的非主属性。 因为这样会出现冗余。即:属性不依赖于其他非主属性。也就是说每一列数据都和主键直接相关,而不能间接相关 ,数据不能存在传递关系
    • 表的字段尽可能用NOT NULL
    • 字段长度固定的表查询会更快
    • 分区分表
    展开全文
  • Mysql优化

    千次阅读 2018-11-21 13:31:28
    Mysql优化 Mysql的逻辑结构 组件:客户端、核心服务、存储引擎 Mysql查询的5个过程过程: 客户端向MySQL服务器发送一条查询请求 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的...

    Mysql优化

    Mysql的逻辑结构
    组件:客户端、核心服务、存储引擎
    在这里插入图片描述
    Mysql查询的5个过程过程:

    • 客户端向MySQL服务器发送一条查询请求

    • 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段

    • 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划

    • MySQL根据执行计划,调用存储引擎的API来执行查询

    • 将结果返回给客户端,同时缓存查询结果

    Mysql 优化的三个方向:

    a.设计合理Schemea
    1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助,只是如果计划在列上创建索引,就应该将该列设置为NOT NULL。
    2. 对整数类型指定宽度,比如INT(11),没有任何卵用。INT使用32位(4个字节)存储空间,那么它的表示范围已经确定,所以INT(1)和INT(20)对于存储和计算是相同的。
    3.UNSIGNED表示不允许负值,大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127,而UNSIGNED TINYINT存储的范围却是0 - 255。
    4. 通常来讲,没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时,仍然可以使用BIGINT。比如需要精确到万分之一,那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
    5. TIMESTAMP使用4个字节存储空间,DATETIME使用8个字节存储空间。因而,TIMESTAMP只能表示1970 - 2038年,比DATETIME表示的范围小得多,而且TIMESTAMP的值因时区不同而不同。
    6. 大多数情况下没有使用枚举类型的必要,其中一个缺点是枚举的字符串列表是固定的,添加和删除字符串(枚举选项)必须使用ALTER TABLE(如果只只是在列表末尾追加元素,不需要重建表)。
    7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。
    8. 大表ALTER TABLE非常耗时,MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表,从旧表中查出所有的数据插入新表,然后再删除旧表。尤其当内存不足而表又很大,而且还有很大索引的情况下,耗时更久。当然有一些奇技淫巧可以解决这个问题,有兴趣可自行查阅。

    b.创建高性能索引
    索引的类别

    • PRIMARY KEY: 主键,这意味着索引值必须是唯一的,且不能为NULL。
    • UNIQUE:创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
    • INDEX: 普通索引,索引值可出现多次。
    • FULLTEXT: 用于全文索引.

    使用索引的注意事项:

    1. 表的主键和外键必须有索引
    2. 经常出现where字句的字段
    3. 经常要查询的列
    4. 选择性高的字段
    5. 经常用户排序的字段
    6. 数据超过300以上
    7. 表的索引最好不要超过5个

    c.根据业务需求选择合理的数据类型和存储引擎

    引擎(engine)说明及区别

    ISAM引擎:读取数据速度很快,而且不占用大量的内存和存储资源;但是ISAM不支持事务处理、不支持外来键、不能够容错、也不支持索引。
    如果硬盘崩溃,数据文件也无法恢复,因此若把ISAM用在关键任务上,就必须经常备份实时数据。

    MyISAM引擎:MyISAM强调了快速读取操作。(1)做很多count 的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。
    静态MyISAM:如果数据库中的各个数据列的长度都是预先固定好的,服务器将自动选择这种表类型。(1)表中每一条记录所占用的空间都是一样的,所以存取和更新的效率非常高。(2)当数据受损时,回复工作也比较容易做。
    动态MyISAM:如果数据表中出现varchar、xxxtex或xxxBLOB字段时,服务器将自动选择这种表类型。相对于静态MyISAM,这种表存储空间比较小。
    压缩MyISAM:这种类型的表进一步减小了占用的存储,但是这种表压缩之后不能再被修改。
    memory(heap)适合:HEAP允许只驻留在内存里的临时表格。因为数据仅存储在内存中,因此数据存取速度比ISAM和MYISAM都快。利用HASH进行索引(记住,用完表格之后就删除表格)

    InnoDB引擎:具有提交、回滚和崩溃恢复能力的失误安全存储引擎。(1)可靠性要求比较高,或者要求事务;(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况。InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
    唯一一个支持外键的引擎。

    archive引擎:日志记录和聚合分析方面。archive不支持索引,仅支持insert和select语句。

    在这里插入图片描述

    展开全文
  • MySQL优化

    万次阅读 2020-03-04 22:53:27
    1.定长和非定长数据类型的选择 decimal不会损失精度,存储空间会随数据的增大而增大。double占用固定空间,较大数的存储会损失精度。...因为null在MySQL中,不好处理,存储需要额外空间,运算也需要特殊的运算符。 ...

    1.定长和非定长数据类型的选择
    decimal不会损失精度,存储空间会随数据的增大而增大。double占用固定空间,较大数的存储会损失精度。非定长的还有varchar、text.
    2.尽可能使用not null
    非null字段的处理要比null字段的处理高效些,且不需要判断是否为null.因为null在MySQL中,不好处理,存储需要额外空间,运算也需要特殊的运算符。
    3.索引分类
    普通索引:对关键字没有限制
    唯一索引:要求记录提供的关键字不能重复
    主键索引:要求关键字唯一且不为null
    4.mysql主从复制
    主从复制,是用来简历一个和住数据库完全一样的数据库环境,成为从数据库,主数据库一般是准实时的业务数据库
    作用:做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失;架构的扩展,随着业务量的增大,i/o访问频率过高,单机无法满足,此时做多库的存储,减低i/o访问的频率,提高单个机器的i/o性能;读写分离,使数据库能支撑更大的并发。再报表中尤其重要,由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用master,报表使用slave,那么报表sql将不会造成前台锁,保证了前台速度。
    主从复制产生的问题:
    主机宕机,数据可能丢失
    从库只有一个sql Thread,主库写压力大,复制很可能延时;
    解决方法:半同步复制 ——解决数据丢失的问题
    并行复制——解决从库复制延迟的问题
    主从复制的原理
    1.数据库有个bin-log二进制文件,记录了所有sql语句。
    2.主数据库中的bin-log文件的sql语句复制过来
    3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。
    4.具体需要三个线程来操作
    binlog输出线程:每当有从库连接到主库的时候,主库都会创建一个线程然后发送binlog内容到从库。在从库里,当复制开始的时候,从库就会创建两个线程进行处理:
    从库I/O线程:当START SLAVE语句在从库开始执行之后,从库创建一个I/O线程,该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件,其中包括relay log文件。
    从库的SQL线程:从库创建一个SQL线程,这个线程读取从库I/O线程写到relay log的更新事件并执行。可以知道,对于每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程,每一个从库都有它自己的I/O线程和SQL线程。
    主从复制步骤
    步骤一:主库db的更新事件(update、insert、delete)被写到binlog
    步骤二:从库发起连接,连接到主库
    步骤三:此时主库创建一个binlog dump thread线程,把binlog的内容发送到从库
    步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log.
    步骤五:还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db.
    5.读写分离
    将数据库分为读库和写库,通过主备功能实现数据同步。
    6.分库分表
    分库分表则分为水平切分和垂直切分,水平切分则是对一个数据库特大的表进行拆分,例如用户表。垂直切分则是根据业务的不同来切分,如用户业务、商品业务相关的表放在不同的数据库中。

    展开全文
  • MySQL 优化

    千次阅读 多人点赞 2020-04-14 10:44:56
    首先了解什么是优化? 合理安排资源、调整系统参数使MySQL运行更快、更节省资源。 优化是多方面的,包括查询、更新、服务器等。 原则:减少系统瓶颈,减少资源占用,增加系统的反应速度。 数据库 性能参数 ...

    首先了解什么是优化?

    • 合理安排资源、调整系统参数使MySQL运行更快、更节省资源。
    • 优化是多方面的,包括查询、更新、服务器等。
    • 原则:减少系统瓶颈,减少资源占用,增加系统的反应速度。

     

    数据库 性能参数

    1. 使用SHOW STATUS语句查看MySQL数据库的性能参数
      1. SHOW STATUS LIKE 'value‘
    2. 常用的参数:
      1. Slow_queries  慢查询次数
      2. Com_(CRUD) 操作的次数
      3. Uptime  上线时间

     

    查询 优化

    1. EXPLAIN

    在MySQL中可以使用EXPLAIN查看SQL执行计划,用法:EXPLAIN SELECT * FROM tb_item

    结果说明

    id  

    SELECT识别符。这是SELECT查询序列号。这个不重要。

     

    select_type

    表示SELECT语句的类型。

    有以下几种值:

    1. SIMPLE
      表示简单查询,其中不包含连接查询和子查询。

          2.PRIMARY
             表示主查询,或者是最外面的查询语句。

    3. UNION
    表示连接查询的第2个或后面的查询语句。

    4. DEPENDENT UNION
    UNION中的第二个或后面的SELECT语句,取决于外面的查询。

    5. UNION RESULT

        连接查询的结果。

    6  SUBQUERY
    子查询中的第1个SELECT语句。

    8. DEPENDENT SUBQUERY
    子查询中的第1个SELECT语句,取决于外面的查询。

    9. DERIVED
    SELECT(FROM 子句的子查询)。

     

    table

    表示查询的表。

    1. type(重要)

    表示表的连接类型。

    以下的连接类型的顺序是从最佳类型到最差类型:

    1. system
      表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计。

    const
    数据表最多只有一个匹配行,因为只匹配一行数据,所以很快,常用于PRIMARY KEY或者UNIQUE索引的查询,可理解为const是最优化的。

     

    3. eq_ref
    mysql手册是这样说的:"对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。

     

    4. ref
    查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。

    5. ref_or_null
    该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

    上面这五种情况都是很理想的索引使用情况。

    1. index_merge
      该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    2. unique_subquery
      该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)
      unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    3. index_subquery
      该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

    range
    只检索给定范围的行,使用一个索引来选择行。

    1. index
      该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    2. ALL
      对于每个来自于先前的表的行组合,进行完整的表扫描。(性能最差)

     

    possible_keys

    指出MySQL能使用哪个索引在该表中找到行。

    如果该列为NULL,说明没有使用索引,可以对该列创建索引来提高性能。

    key

    显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

    可以强制使用索引或者忽略索引:

     

    子查询优化

    MySQL从4.1版本开始支持子查询,使用子查询进行SELECT语句嵌套查询,可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

    子查询虽然很灵活,但是执行效率并不高。

    执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响。

     

    优化:

    可以使用连接查询(JOIN)代替子查询,连接查询时不需要建立临时表,其速度比子查询快

     

    数据库结构优化

    一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

    需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

    1. 将字段很多的表分解成多个表

    对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。

    因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

    1. 增加中间表

    对于需要经常联合查询的表,可以建立中间表以提高查询效率。

    通过建立中间表,将需要通过联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询。

    1. 增加冗余字段

    设计数据表时应尽量遵循范式理论的规约,尽可能的减少冗余字段,让数据库设计看起来精致、优雅。但是,合理的加入冗余字段可以提高查询速度。

    表的规范化程度越高,表和表之间的关系越多,需要连接查询的情况也就越多,性能也就越差。

    注意:

    冗余字段的值在一个表中修改了就要想办法在其他表中更新否则就会导致数据不一致的问题。

     

    插入数据的优化

    插入数据时,影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。

    插入数据的优化,不同的存储引擎优化手段不一样,在MySQL中常用的存储引擎有,MyISAM和InnoDB,两者的区别:http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html

     

    批量插入数据 

    插入数据时,可以使用一条INSERT语句插入一条数据,也可以插入多条数据。

    第二种方式的插入速度比第一种方式快。

     

    使用 LOAD DATA INFILE

    当需要批量导入数据时,使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。

     

    禁用外键检查

    插入数据之前执行禁止对外键的检查,数据插入完成后再恢复,可以提供插入速度。

     

    禁用:SET foreign_key_checks = 0;

    开启:SET foreign_key_checks = 1;

     

    禁止自动提交

    插入数据之前执行禁止事务的自动提交,数据插入完成后再恢复,可以提高插入速度。

     

    禁用:SET autocommit = 0;

    开启:SET autocommit = 1;

     

     

    服务器 优化

    1. 优化服务器硬件

    服务器的硬件性能直接决定着MySQL数据库的性能,硬件的性能瓶颈,直接决定MySQL数据库的运行速度和效率。

     

    需要从以下几个方面考虑:

    1. 配置较大的内存。足够大的内存,是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多,可以增加系统的缓冲区容量,使数据在内存停留的时间更长,以减少磁盘的IO。
    2. 配置高速磁盘,比如SSD。
    3. 合理分配磁盘IO,把磁盘IO分散到多个设备上,以减少资源的竞争,提高并行操作能力。
    4. 配置多核处理器,MySQL是多线程的数据库,多处理器可以提高同时执行多个线程的能力。
    1. 优化MySQL的参数

    通过优化MySQL的参数可以提高资源利用率,从而达到提高MySQL服务器性能的目的

    MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中,常用的参数如下:

    展开全文
  • MySql优化

    千次阅读 2019-01-18 09:50:19
    什么是优化? 合理安排资源、调整系统参数使MySQL运行更快、更节省资源。 优化是多方面的,包括查询、更新、服务器等。 原则:减少系统瓶颈,减少资源占用,增加系统的反应速度。 数据库性能参数 使用SHOW...
  • MySQL优化技巧

    万次阅读 多人点赞 2017-09-10 14:14:00
    MySQL优化三大方向① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。③ 对SQL语句以及表优化。MySQL参数优化1:MySQL 默认的最大...
  • Mysql 优化

    千次阅读 2018-07-03 11:09:02
     架构调优 2 mysql 配置调优 3 设计优化 4 sql 索引优化架构调优在架构调优上,我们需要关注的是提高整个mysql 的吞吐量与可用性 mysql 配置调优 per thread buffers 线程缓存调优read_buffer_size该参数用于表的...
  • mysql 优化

    千次阅读 2018-07-02 14:34:13
    sql前面加上 explain  EXPLAIN的每个输出行提供一个表的相关信息,并且每个行包括下面的列: id:SELECT识别符。这是SELECT的查询序列号。 select_type:SELECT类型。 ...SIMPLE: 简单SELECT(不使用UNION或子查询...

空空如也

1 2 3 4 5 ... 20
收藏数 64,715
精华内容 25,886
关键字:

mysql优化

mysql 订阅