mysql优化_mysql优化技巧 - CSDN
精华内容
参与话题
  • MySQL优化面试准备

    万次阅读 多人点赞 2018-11-28 21:42:18
    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是: MySql的特点与...

    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是:

    MySql的特点与存储引擎

    MySql高性能索引

    MaySql的数据类型优化与查询性能优化

    MySql的特点与存储引擎

    MySql的总体架构图

    用户执行Mysql的流程图:

    上层的一些服务并不是Mysql特有的,比如说Mysql的服务器会启动连接池然后对客户端做连接处理授权认证安全等工作。

    中间的架构包括查询解析、分析、优化和缓存等跨存储引擎的过程。

    底层是存储引擎,负责Mysql数据的存储和提取,但是一般的存储引擎是不会去解析SQL语句的(但是InnoDB可以去解析外键的定义),不同的存储引擎之间是不会通信的而只是单纯的处理上层服务器的请求。

     

    数据库中的

    按照互斥性来说

    共享锁(之间不互斥),排他锁(与其他任意锁都互斥)。在一般情况下读锁是共享锁,写锁是排他锁。需要注意的是,写锁的优先级比较高一个写锁的请求可能被插入到读锁队列的前面反之不然。

    按照粒度来说

    一般有表锁(粗粒度效率低)与行锁(细粒度并发效率高)。所谓的锁的策略就是在锁的开销与数据的安全性之间寻找一个平衡。

    死锁

    就是多个事务在同一资源上相互占用,并锁定对方占用的资源从而导致的恶性循环的现象。好在Mysql会自动检测和处理死锁。InnoDB的处理策略是将持有最少行级排他锁的事物回滚。

    事务(略)

    这里只强调一点就是,没有100%的持久化,否则备份是不会增加持久性的。

    另一种高并发策略——MVCC

    Mysql大多数的存储引擎并不是简单是实现了行级锁,他们同时实现了MVCC(多版本并发控制)。MVCC可以认为是行级锁的一个变种,但是它在很多的情况下都避免了加锁(只有在写入操作的时候才加锁)。MVCC是通过保存数据在某一个时间点的快照来实现的,也就是不管什么时候每个事物看到的数据都是一致的。也就是可能存在不同的事务在同一时刻看到的数据是不同的。别着急我们慢慢来。

    MVCC的原理就是在数据的每一行后面增加两个列,一列保存的是创建时间另一列保存的是删除时间(准确的说是系统的版本号)。下面我们以REPEATABLE READ的隔离级别举个例子。

     

    SELECT

    首先是查询,当一个事务开启是能够查到的数据得满足两个条件

    • 创建时间在当前事务开启之前(包含当前事务)
    • 删除时间在当前事务之后

    只有同时满足这两个条件的行才能够被当前的事务所查到。

    INSERT

    当当前的事务新增一条数据的时候,会将当前的版本号分配给当前新增的每一行。

     

    DELETE

    会将当前的版本号分配给删除的每一行作为删除时间。

    UPDATE

    会新插入一条数据版本号为当前的事务版本号,旧的那一条数据会设置当前事物的版本号为删除时间。

     

    总结:增加了这样的两个版本号,会使得效率大大提高但是会花掉空间的成本。

     

    InnoDB与MyISAM存储引擎的区别

    比较项目

    InnoDB

    MyISAM

    数据存储

    存在表空间(是由InnoDB管理的黑盒子由一系列数据文件组成)

    存储在数据文件与索引文件中

    事务

    支持事务

    不支持事务

    行级锁

    支持行级锁

    不支持行级锁(但是支持表级索)

    全文检索

    不支持全文检索

    支持全文检索

    外键

    支持外键

    不支持外键

     

    Momory引擎

    所有的数据都保留在内存中,不需要进行磁盘的IO所以读取的速度很快。但是一旦关机的话表的结构会保留但是数据就会丢失。

    Memory表支持Hash索引,因此查找速度很快。即便如此它还是不能代替基于磁盘的引擎因为它只是支持表锁所以并发写入的效率不高。同时它不支持TEXT与BLOB数据类型,而且没有varchar类型(即使写了varchar也会自动转换成char类型)所以会造成内存的浪费。

     

    总结:默认使用InnoDB存储引擎。例如:即使是需要全文索引我们也可以通过InnoDB与Sphinx的组合,而不是直接使用MyISAM。

     

    创建高性能的索引

    索引的概念就不多说了,这种数据结构可以大幅度的减少我们查找数据的时间。从架构上来说它是在存储引擎层面实现的。

    B-Tree与B+Tree索引

    先上图:

    (图片来自csdn gitchat)

    从图中我们可以看到数据的特点。所有真实的数据都是排好序的在B-Tree的叶子节点上,这些节点到根的距离是一样的。当我们要查找一个具体的数据的时候,首先会从根节点开始找每一次都会根据大小选择一个分支,这样的话大大减少了查找的次数。从此再也不需要全表的扫描了。同时B-Tree的数据是按照顺序来存放的对顺序值的查找(对索引列进行OrderBy操作)也是非常快的。这样的话避免了很多随机的IO。

    这里需要注意的是如果存在多个索引数据库的排序规则。按照create 表的时候的索引出现的先后顺序来排序的。如果前一个指标一样那么则使用后一个指标来排序。同时如果有多列索引的话那么我们只能够从最左列开始查找。不能跳过索引的列,所以对于列的先后顺序也是我们优化的一个指标。

    最后,B+Tree比B-Tree多了一个节点之间的顺序扫描。

     

    哈希索引

    哈希索引的底层是Hash表,每查一个值之前首先计算它的hashcode然后找到对应数据的指针,最后根据指针去找到真实数据。它有以下几点注意事项:

    • 只能使用精确查询,这个与hash表的特点有关。
    • 它不是按照索引值的顺序来存储的所以不能用于排序。
    • 只能有等值查询不能查询>  < 等关系运算。
    • Hash冲突时会遍历对应Hash码的对应的链表的所有节点,而且维护成本高每删除一条记录时就会修改对应的Hash表。

    自适应Hash索引

    在innodb中如果有些索引使用的非常频繁的时候,就可以考虑使用自适应Hash索引。它对查询的性能的提升是非常明显的。

    它的设计原理非常简单,就是让一个字段的Hash值作为索引,然后再使用B+Tree来进行查询。从这里我们可以看出这与真正的Hash索引是不同的,真正执行查询的是B+Tree,我们只是在查询之前计算了一次Hash值。这样我们可以做出以下的优化,案例如下:

    Select id from url where url = “http://www.mysql.com”;

     

    Select id from url where

    url = “http://www.mysql.com” 

    and url_crc=crc32(“http://www.mysql.com”);

     

    其中crc32就是我们所指定的Hash函数。如果以它为索引,那么Mysql的优化器就会选择性能高的url_crc来实现查找。即使出现了hash冲突(也就是同一个Hash值会对应不同的url)我们也早已过滤掉了大部分的无用信息而不再像从前一样进行逐个比较了。当然自适应Hash索引也有自己的优点就是需要维护Hash索引,而且要处理Hash冲突尤其是在数据量较大的情况下。这里给出一个处理Hash冲突的建议就是使用多个where条件来保证查询的准确性。

    小节:索引的优点

    1. 索引大大减少了服务器需要扫描的数据量。
    2. 索引可以避免服务器排序和建立临时表。
    3. 索引可以将随机的IO变成顺序的IO。

     

    高性能索引的策略

    独立的列

    独立的列是指,索引列不能成为表达式或者参数的一部分否则就会失效。比如说下面的索引列就没有发挥作用。

    比如actor_id列为索引列那么

    Select actor_id from mytable where actor_id + 1 = 5;

     

    前缀索引和索引的选择性

    在数据量大索引的字段过长的时候,索引本身也会占用较大的空间。这样会使得索引变得大而且慢,所以我们有时候使用索引的前缀来代替索引以此来减短索引列的长度减少索引所占的空间。

    这里不得不提出一个概念就是索引的选择性,它是指不重复的索引值与记录总数的比值。索引的选择性越高则查询性能越高,因为在查询时会过滤掉更多的行。唯一索引的选择性是1,而大多数的索引往往是一对多的。与此同时在数据库上有这样的一个事实,使用的索引前缀越短则索引的选择性广义的单调递减,使用的索引越长则索引的选择性广义单调递增。这就出现了一个高选择性与短前缀之间的一种平衡。我们的策略是尽可能的选择一个临界值的长度一旦超过这个长度索引的选择性会提升的很慢。那么这个时候我们的前缀索引是性价比最高的。

    选择合适的索引列顺序

    当有多列索引时,索引列的顺序极大的影响着我们的查询效率。通常情况下(不考虑排序和分组的情况下)我们把高选择性的索引列放在最前面,以保证在查询刚开始的时候就可以过滤大部分的数据。实务上我们需要根据经验来调整索引的顺序,比如说把IO的优化放在第一位,甚至是修改程序代码来达到查询的优化。

    举个栗子:

    查找一个名字叫“主流7”,而且在15软件G2班的同学的信息。

    已知名字叫主流7的人有10000条,而15软件G2班只有30人。这个时候我们优先查出15软件G2班的人然后再去找名字叫主流7的人的效率要远远高于,找出所有名字叫主流7的人然后再看那个是属于15软件G2班的人的效率。

    聚簇索引

    聚簇索引不是一种单独的数据存储类型而是一种数据的存储方式,。所谓聚簇就是数据和相邻的键值紧凑的存放在一起(也就是数据怎么存放索引就怎么建立)所以一个表只能够有一个聚簇索引,因为它的数据只能以一种方式来存储。(聚簇索引的定义还待考究)。

     

    聚簇索引与非聚簇索引(二级索引)

    聚簇索引与非聚簇索引的具体实现与底层的存储引擎相关。下面分别以MySQL中的两大主流引擎来展开讨论。

    InnoDB

    InnoDB的聚簇索引就是表本身,它由表的数据与B+Tree索引组成。

    其中内部节点中包括索引列和指向下一节点的指针,而叶子节点包括表所有列上的数据,比如主键列,MVCC列,回滚列其它列等等。所以我们在查询数据时直接就能够查找到我们想要的数据。

    InnoDB的二级索引(非聚簇索引)

    内部结点中包含索引列与指向下个节点的指针,而叶子节点则包括索引列和主键值。这也就造成了如果我们想要通过二级索引去查询一条数据的啥时候需要两轮的查询,第一轮是通过B+Tree查找到主键值,第二轮就是拿着主键值再经过一次B+Tree的查找找到真实的值。

    MyISAM

    MyISAM的索引聚簇索引和非聚簇索引原理相同,就是内部结点都包含有索引列和指向下一个节点的指针,在叶子节点中包含的是行号,指向实际的物理地址。

     

    避免随机的聚簇索引

    当表中没有什么数据需要聚集时,我们一般可以使用一个代理主键(与业务无关的键)去作为聚簇索引比如说一列自增的键。自增的键有一个好处就是在增加一条记录的时候会按照顺序插在最后面这样的话会非常节省资源因为它既避免了页分裂同时也避免了由页分裂而产生的内存碎片化。

    另一方面,随机的聚簇索引(特别是对于一些IO密集的应用)使用随机的聚簇索引是很糟糕的,它使得聚簇索引的插入变得完全随机,使得数据完全没有聚集特性。下面具体说说随机的聚簇索引的缺点:

    • 将要写入的目标页有可能已经不在内存中(或者是缓存中了),所以在写入时首先需要将目标页从硬盘读到内存中。这将导致大量的随机IO。
    • 因为写入时乱序的,所以InnoDB不得不频繁的做页分裂操作,这将会大量的移动数据。
    • 由于页分裂,所以会导致页变得稀疏并且不规则的填充,最终数据会有碎片。

    综上我们要避免随机的聚簇索引。顺便自增的索引在高并发的时候也会产生线程安全问题,所以我们可以使用innodb_autoinc_lock_mode参数配置来保证自增的原子性。

    小节

    简单说一下InnoDB的聚簇索引的特点:

    1. 通过一个指标把与这个指标相关联的数据聚集起来,当查询与这个指标相关的数据时能够避免多次IO带来的效率底下。但是,当数据都在内存中进行运算时这时也就不会有

    IO带来的效率问题了。

    1. 查询速度快,不像二级索引一样需要两轮查找。
    2. 索引的维护成本高,更新聚簇索引插入新的行可能会造成如果按照索引的顺序插入还好但是如果一旦在中间插入新的行就会导致页分裂的问题这样既使得更新的效率低下,又会造成内存的浪费。

    覆盖索引

    如果一个索引中包含我们想要查的那一列的值,我们就说这个索引就是覆盖索引。当我们需要查询被覆盖的列的值的时候可以直接查询覆盖索引中的值而无需回到表中查询数据。这样我们就可以说这次查询的表中的数据被索引中的数据给覆盖了。下面介绍它的一些优点:

    • 可以配合InnoDB的二级索引来实现单次查询,如果二级索引能够覆盖查询就能够办到。
    • 因为索引是按照顺序的(或者是局部有序)那么对于当前列值的IO密集型范围查询会大大减少随机IO。
    • 索引的条目通常远小于数据行的大小,如果能直接从索引中获取数据那么会极大地减少数据的访问。减少了服务器的负载压力。

    不是所有的引擎都支持覆盖索引,MySQL只支持基于B-Tree的覆盖索引。有的查询是是不能够使用覆盖索引的,比如说非前缀的like模糊查询。还有的查询他们要查询表中的很多列没有一个所以是可以覆盖很多列的所以只能够先利用覆盖索引过滤掉大多数的数据行,然后再在这个基础上进行查询,我们把这种查询方式叫做延迟关联。

     

    数据类型优化与查询优化的一些经验法则

    数据类型优化

    MySQL支持的数据类型是很多的,选择好合适的数据类型对于节省内存和提升性能意义重大。下面介绍几点经验法则:

    1. 如果能够正常存储数据那么一般使用更小的数据类型。
    2. 尽量避免null值,null值可以是任何数据类型的默认值,但是null使得索引索引统计和值的比较都变得复杂。
    3. TIMESTAMP与DATATIME的选择,TIMESTAMP的时间范围较DATATIME小的多,但是

    TIMESTAMP占用的内存是DATATIME的一半并且能够随着时区不断地变化。

    1. 整数类型有可选的unsigned属性,如果我们的数值无需负数的话那么推荐使用这种类型,举例来说TINYINT UNSIGNED与TINYINT的存储范围都是256但是前者是0~255,后者是-128~127。
    2. DECIMAL类型是一种存储方式,它可以用于精确计算。在计算过程中它会转化成double类型,但是DECIMAL的精确计算代价很高。为了减少这种高消耗我们可以在保留精度的条件下使用BIGINT来存储数据。举例来说,比如要保留6位小数,那么我们在存储数据的时候就可以给每一个数乘以100万,然后在取数据的时候在给每一个数除以100万。以减少代价。
    3. Varchar与char。它们的存储方式与存储引擎相关,下面以InnoDB或MyISAM为例。

    Varchar长度可变一般来说是节省空间的,但是如果使用ROW_FORMAT=FIXED参数创建表的话那么varchar也是固定的长度。Varchar有一个特点就是需要在存储空间末尾开辟一到两个字节记录字符串的长度。当字符串长度的方差大的时候(比如说使用了UTF-8这样的字符集)那么推荐使用varchar。

    Char是定长的数据类型,非常适合存储定长的数据比如说MD5码。对于非常短的列也有非常高的效率,比如说表示一个只有true/false的值char(1)的效率要高于varchar(1)因为varchar(1)后面会附加一个记录长度的额外字节。

    1. 当存储IP地址时使用无符号整数,因为IP地址本来就是32位的无符号整数。使用

    INET_ATON()与INET_NTOA()完成他们之间的转换。

    查询优化

    搞清楚查询的生命周期对于优化查询是十分必要的,如果把查询看作是一个任务那么它就是由很多的子任务所构成。查询的生命周期大致可以分为这样的几个阶段:从客户端到服务器,然后在服务器上解析,生成执行计划、执行最后返回结果给客户端。这里我再次贴上这张图

    其中执行阶段可以认为是最重要的阶段这一阶段包括大量的检索数据到存储引擎的调用以及调用后的数据处理,包括排序,分组等。除此之外查询还要在不同的地方花费时间,包括网络,CPU计算,生成统计信息和执行计划,锁等待等等。

    优化数据访问

    减少请求无用的数据,比如说我们查询了100条数据但是最后只在得到的结果中取了其中的10条(在应用程序中完成的)。这样不仅仅是耗费了大量的计算机资源也耗费了大量的网络资源。所以在这种情况下我们尽早使用limit语句来过滤或者是使用索引来过滤。

    重构查询方式

    一般情况下我们倾向于使用尽可能少的SQL语句去完成查询操作,这样能够减少发送查询的次数。但是有时候我们需要将大的查询拆分成一些小的查询。比如说在删除大量的数据时数据库(假设数据库支持锁)会将大量的数据锁住这样的话其它的语句就会在队列中等待。这时候将删除的任务分为几波就可以更加高效。

    有时候我们也需要将查询的连接放在应用中,将一个连接查询拆分成几个小的查询。这样带来的的好处就是避免了几张表连接时占用的大量内存,而且更加有利于利用缓存(如果关联的表中某个表发生了改变将无法使用缓存,但是当拆开之后某个表发生了细微的变换之后还可以使用缓存)。最后拆开之后还可以减少锁的竞争。

    优化特定类型的查询

    这里仅仅介绍几个经验的法则:

    • 当统计行数的时候尽量使用count(*),当我们以某一列为统计指标的时候,如果碰到null的时候就会不把这一行统计在内。而在MySQL的底层也会将count(col)优化成count(*)。
    • 要尽可能的保证GROUP BY和ORDERBY语句中只包含一个列,这样MySQL才有可能使用索引来优化查询。
    • 要时时注意子查询,子查询构成的虚表是没有任何索引的。
    • 优化LIMIT分页,在一般情况下我们会使用LIMIT加上偏移量来搞定,这时如果有合适的索引的话效率一般不会错,但是如果只是一味的使用LIMIT语句的话,比如有1000,20这样的语句的话前面的需要查询1020条记录然后抛弃前面的1000条数据,这个代价是非常高的。一般的做法是尽可能的使用覆盖索引(里面可以包含我们想要的值)做延迟关联这样的效率就会很高。举个例子:以下代码使用方案2

    select film_id,description, from sakila.film order by title limit 50,5;

    Select film.film_id,film.description

    from sakila.film

    inner join(

    Select film_id from sakila.film order by title limit 50,5

    ) as lim using(film_id);

     

    至此我的有关于数据库的优化的知识点输出完毕。同时也希望能够帮助到你。最后,欢迎批评。

    展开全文
  • MYSQL 八大优化方案

    万次阅读 多人点赞 2018-04-05 16:38:00
    关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂。 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望...

    关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂。
      偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望对大家今后开发中也有帮助

    1、选取最适用的字段属性

    MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。

    例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

    另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。

    对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。

    2、使用连接(JOIN)来代替子查询(Sub-Queries)

    MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示:

    DELETE  FROM  customerinfo
    
    WHERE  CustomerID  NOT  in  (SELECT customerid  FROM  salesinfo)
    

    使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

    SELECT  *  FROM  customerinfo
    
    WHERE  customerid  NOT IN (SELECT customerid   FROM   salesinfo)
    

    如果使用连接(JOIN)..来完成这个查询工作,速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:

    SELECT  *  FROM  customerinfo
    
    LEFT  JOIN  salesinfo  ON   customerinfo.customerid =salesinfo.customerid
    
    WHERE  salesinfo.customerid   IS NULL
    

    连接(JOIN)..之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

    3、使用联合(UNION)来代替手动创建的临时表

    MySQL从4.0的版本开始支持union查询,它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们只需要用UNION作为关键字把多个select语句连接起来就可以了,要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

    SELECT   name,phone  FROM  client UNION
    
    SELECT  name,birthdate  FROM  author  UNION
    
    SELECT  name,supplier FROM product
    

    4、事务

    尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

    BEGIN;
      INSERT   INTO   salesinfo   SET   customerid=14;
      UPDATE   inventory   SET   quantity =11   WHERE   item='book';
    COMMIT;
    

    事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。

    5、锁定表

    尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。

    其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

    LOCK TABLE inventory WRITE SELECT quantity  FROM   inventory   WHERE Item='book';
    
    ...
    
    UPDATE   inventory   SET   Quantity=11   WHERE  Item='book';UNLOCKTABLES
    

    这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其它的访问来对inventory进行插入、更新或者删除的操作。

    6、使用外键

    锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

    例如,外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以把customerinfo表中的customerid映射到salesinfo表中customerid,任何一条没有合法customerid的记录都不会被更新或插入到salesinfo中。

    CREATE  TABLE   customerinfo( customerid   int primary key) engine = innodb;
    
    CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;
    
    

    注意例子中的参数“on delete cascade”。该参数保证当customerinfo表中的一条客户记录被删除的时候,salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=INNODB。如例中所示。

    7、使用索引

    索引是提高数据库性能的常用方法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候,性能提高更为明显。

    那该对哪些字段建立索引呢?

    一般说来,索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况

    例如customerinfo中的“province”..字段,在这样的字段上建立索引将不会有什么帮助;相反,还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引,也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外,MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引,但仅能用于MyISAM类型的表。对于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTERTABLE或CREATEINDEX创建索引,将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会非常慢。

    8、优化的查询语句

    绝大多数情况下,使用索引可以提高查询的速度,但如果SQL语句使用不恰当的话,索引将无法发挥它应有的作用。

    下面是应该注意的几个方面。

    a、 首先,最好是在相同类型的字段间进行比较的操作

    在MySQL3.23版之前,这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较;但是作为特殊的情况,在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候,可以将它们进行比较。

    b、 其次,在建有索引的字段上尽量不要使用函数进行操作

    例如,在一个DATE类型的字段上使用YEAE()函数时,将会使索引不能发挥应有的作用。所以,下面的两个查询虽然返回的结果一样,但后者要比前者快得多。

    c、第三,在搜索字符型字段时,我们有时会使用LIKE关键字和通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的

    例如下面的查询将会比较表中的每一条记录。

    
    SELECT  *  FROM  books  WHERE  name  like   "MySQL%"
    
    

    但是如果换用下面的查询,返回的结果一样,但速度就要快上很多:

    
    SELECT  *  FROM  books  WHERE  name >=  "MySQL"  and  name  <"MySQM"
    

    最后,应该注意避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用。


    展开全文
  • Mysql的sql优化方法

    万次阅读 多人点赞 2018-08-17 16:55:11
     Mysql是一种关系型数据库,可以很好地支持大数据量的存储,但是一般来说,数据库中的表越小,在它上面执行的查询也就越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度舍得尽可能小。 ...

    1、选择最合适的字段属性

           Mysql是一种关系型数据库,可以很好地支持大数据量的存储,但是一般来说,数据库中的表越小,在它上面执行的查询也就越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度舍得尽可能小。

            例如:在定义邮政编码这个字段时,如果将其设置为char(255),显然给数据库增加了不必要的空间,甚至使用varchar这种类型也是多余的,因为char(6)就可以很好地完成了任务。同样的如果可以的话,我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。

    2、尽量把字段设置为NOT NULL

         在可能的情况下,尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。对于某些文本字段来说,例如“省份”或者“性别”,我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中,ENUM类型被当做数值型数据来处理,而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。

    3、使用连接(JOIN)来代替子查询(Sub-Queries)

          MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果,然后把这个结果作为过滤条件用在另一个查询中。例如:我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来,然后将结果传递给主查询,如下图所示:

          

         如果使用连接(JOIN)来完成这个工作,速度将会快很多,尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:

          

          连接(JOIN)之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。

          另外:如果你的应用程序有很多JOIN查询,你应该确认两个表中JOIN的字段是被建立过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。而且这些被用来JOIN的字段,应该是相同的类型的。例如:如果你要把DECIMAL字段和一个INT字段JOIN在一起,MySQL就无法使用他们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集可能不相同)。

           inner join内连接也叫做等值连接,left/right join是外链接。

    SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;

          

    SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
    SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

           经过多方面的证实inner join性能比较快,因为inner join是等值连接,或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接,如:

    SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

           推荐:能用inner join连接诶就用inner join连接。

           sql中的连接查询有inner join(内连接)、left join(左连接)、right join(右连接)、full join(全连接)四种方式,它们之间其实并没有太大区别,仅仅是查询出来的结果有所不同。

    例如我们有两张表: 
    这里写图片描述

    Orders表通过外键Id_P和Persons表进行关联。

    inner join(内连接),在两张表进行连接查询时,只保留两张表中完全匹配的结果集。

    我们使用inner join对两张表进行连接查询,sql如下:

    SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    INNER JOIN Orders o
    ON p.Id_P=o.Id_P and 1=1  --用and连接多个条件
    ORDER BY p.LastName

    查询结果集: 
    这里写图片描述

    此种连接方式Orders表中Id_P字段在Persons表中找不到匹配的,则不会列出来。

    注意:单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据,b表有3条数据,那么最后的结果有5*3=15条数据。

    但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了,此时就等价于:

    select * from a inner join b on a.id = b.id。即就是内连接。

    但是这种写法并不符合规范,可能只对某些数据库管用,如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

    内连接查询 (select * from a join b on a.id = b.id) 与 关联查询 (select * from a , b where a.id = b.id)的区别

    left join,在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。

    我们使用left join对两张表进行连接查询,sql如下:

    SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    LEFT JOIN Orders o
    ON p.Id_P=o.Id_P
    ORDER BY p.LastName

    查询结果如下: 
    这里写图片描述 
    可以看到,左表(Persons表)中LastName为Bush的行的Id_P字段在右表(Orders表)中没有匹配,但查询结果仍然保留该行。

    right join,在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。

    我们使用right join对两张表进行连接查询,sql如下:

    SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    RIGHT JOIN Orders o
    ON p.Id_P=o.Id_P
    ORDER BY p.LastName

    查询结果如下:

    这里写图片描述 
    Orders表中最后一条记录Id_P字段值为65,在左表中没有记录与之匹配,但依然保留。

    full join,在两张表进行连接查询时,返回左表和右表中所有没有匹配的行。

    我们使用full join对两张表进行连接查询,sql如下:

    SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    FULL JOIN Orders o
    ON p.Id_P=o.Id_P
    ORDER BY p.LastName

    查询结果如下: 
    这里写图片描述 
    查询结果是left join和right join的并集。

    4、使用联合(UNION)来代替手动创建的临时表

           MySQL从4.0版本开始支持union查询,他可以把需要使用临时表的两条或更多的select查询合在一个查询中。在客户端查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们只需要用union作为关键字把多个select语句连接起来就可以了,要注意的是所有select语句中的字段数目要相同。下面一个例子就演示了一个使用union额查询。

           当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候尽量使用union all而不是union,因为union和union all的差异主要是前者需要将两个或者多个结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,增大资源消耗及延迟。

    5、事务

           尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作,都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。

            设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL语句操作失败,那么Rollback命令就可以把数据库恢复到begin开始之前的状态。

    BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;

            事务的另一个作用是当多个用户同时使用相同的数据源时,他可以使用锁定数据库的方式来为用户提供一种安全的访问机制,这样可以保证用户的操作不被其它的用户所干扰。

            一般来说,事务必须满足四个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability).

             原子性:一个事物(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始的状态,就像这个事务从来没有执行过一样。

            一致性:在事务开始之前和事务结束之后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

            隔离性:数据库允许多个事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别,包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).

            持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

     事务的并发问题:

            1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据就是脏数据

            2、不可重复读:事务A多次读取同一事物,事务B在事务A多次读取的过程中,对数据做了更新并提交,导致事务A多次读取同一数据时,结果不一致。

           3、幻读:系统管理员A将数据库中的所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

    小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

    MySQL事务隔离级别

    事务隔离级别 脏读 不可重复读 幻读
    读未提交(read-uncommitted)
    不可重复读(read-committed)
    可重复读(repeatable-read)
    串行化(serializable)

    事务控制语句:

           BEGIN或START TRANSACTION:显式的开启一个事物。

           COMMIT:也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改成为永久性的。

           Rollback:也可以使用Rollback work,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。

           SAVEPOINT identifier:SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有很多个SAVEPOINT;

           RELEASE SAVEPOINT identifier:删除一个事物的保存点,当没有指定的保存点时,执行该语句会抛出一个异常。

           ROLLBACK TO inditifier:把事务回滚到标记点。

           SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。

    7、锁定表

            尽管事务是维护数据库完整性的一个非常好的方法,但却因为他的独占性,有时会影响数据库的性能,尤其是很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其他的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。

            其实,有些情况下我们可以通过锁定表的方式来获得更好的性能。下面的例子就是锁定表的方法来完成前面一个例子中事务的功能。

           这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其他访问来对inventory进行插入、更新或者删除的操作。

     6、使用外键

           锁定表的方法可以维护数据的完整性,但是他却不能保证数据的关联性。这个时候我们可以使用外键。例如:外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以把customerinfo表中的customerid映射到salesinfo表中customerid,任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.

    CREATE TABLE customerinfo(customerid int primary key) engine = innodb;
    CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;
    

          注意例子中的参数“on delete cascade”.该参数保证当customerinfo表中的一条客户记录也会被自动删除。如果要在mysql中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是mysql表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=innoDB。

    8、使用索引

          索引是提高数据库性能的常用方法,他可以令数据库服务器比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候,性能提高更为明显。

           那该对那些字段进行索引呢?

           一般来说,索引应该建立在那些将用于join,where判断和orderby排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引,对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况。
           例如customerinfo中的“province”..字段,在这样的字段上建立索引将不会有什么帮助;相反,还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引,也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外,MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引,但仅能用于MyISAM类型的表。对于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTERTABLE或CREATEINDEX创建索引,将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会非常慢。

    9、优化de的查询语句

           1 不使用子查询

           例:SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);

           子查询在MySQL5.5版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会非常慢。
           在MariaDB10/MySQL5.6版本里,采用join关联方式对其进行了优化,这条SQL会自动转换为
              SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
          但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE子查询无效,固生产环境应避免使用子查询

    2 避免函数索引

    例:SELECT * FROM t WHERE YEAR(d) >= 2016;
    由于MySQL不像Oracle那样支持函数索引,即使d字段有索引,也会直接全表扫描。
    应改为—–>
    SELECT * FROM t WHERE d >= ‘2016-01-01’;

    3 用IN来替换OR

    低效查询
    SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
    —–>
    高效查询
    SELECT * FROM t WHERE LOC_IN IN (10,20,30);

    4 LIKE双百分号无法使用到索引

    SELECT * FROM t WHERE name LIKE ‘%de%’;
    —–>
    SELECT * FROM t WHERE name LIKE ‘de%’;
    目前只有MySQL5.7支持全文索引(支持中文)

    5 读取适当的记录LIMIT M,N

    SELECT * FROM t WHERE 1;
    —–>
    SELECT * FROM t WHERE 1 LIMIT 10;

    6 避免数据类型不一致

    SELECT * FROM t WHERE id = ’19’;
    —–>
    SELECT * FROM t WHERE id = 19;

    7 分组统计可以禁止排序

    SELECT goods_id,count(*) FROM t GROUP BY goods_id;
    默认情况下,MySQL对所有GROUP BY col1,col2…的字段进行排序。如果查询包括GROUP BY,想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序。
    —–>
    SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

    8 避免随机取记录

    SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
    MySQL不支持函数索引,会导致全表扫描
    —–>
    SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

    9 禁止不必要的ORDER BY排序

    SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
    —–>
    SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

    10 批量INSERT插入

    INSERT INTO t (id, name) VALUES(1,’Bea’);
    INSERT INTO t (id, name) VALUES(2,’Belle’);
    INSERT INTO t (id, name) VALUES(3,’Bernice’);
    —–>
    INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);

    展开全文
  • 实践中如何优化MySQL(精)

    万次阅读 多人点赞 2020-06-17 10:13:27
    ① SQL语句及索引的优化 SQL语句的优化: 1、尽量避免使用子查询 ​2、避免函数索引 3、用IN来替换OR 4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引,如果查询的是索引列则可以 5、读取...

    目录

    ① SQL语句及索引的优化

    SQL语句的优化:

    1、尽量避免使用子查询

    ​2、避免函数索引

    3、用IN来替换OR

    4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引,如果查询的是索引列则可以

    5、读取适当的记录LIMIT M,N,而不要读多余的记录

    6、避免数据类型不一致

    7、分组统计可以禁止排序sort,总和查询可以禁止排重用union all

    8、避免随机取记录

    9、禁止不必要的ORDER BY排序

    10、批量INSERT插入

    11、不要使用NOT等负向查询条件

    12、尽量不用select *

    13、区分in和exists

    索引的优化:

    1、Join语句的优化:

    2、避免索引失效

    ② 数据库表结构的优化:使得数据库结构符合三大范式与BCNF

    ③ 系统配置的优化

    ④ 硬件的优化


     

    在开始介绍如何优化sql前,先附上mysql内部逻辑图让大家有所了解

    ① SQL语句及索引的优化

    SQL语句的优化:

    1、尽量避免使用子查询

    2、避免函数索引

    3、用IN来替换OR

        另外,MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from table_name where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。

    4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引,如果查询的是索引列则可以

    5、读取适当的记录LIMIT M,N,而不要读多余的记录

    select id,name 
    from table_name limit 866613, 20

    使用上述sql语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

    优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。sql可以采用如下的写法:

    select id,name from table_name 
    where id> 866612 limit 20

     

    6、避免数据类型不一致

    7、分组统计可以禁止排序sort,总和查询可以禁止排重用union all

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。

    另外,如果排序字段没有用到索引,就尽量少排序;

     

    8、避免随机取记录

    9、禁止不必要的ORDER BY排序

    10、批量INSERT插入

    11、不要使用NOT等负向查询条件

    你可以想象一下,对于一棵B+树,根节点是40,如果你的条件是等于20,就去左面查,你的条件等于50,就去右面查,但是你的条件是不等于66,索引应该咋办?还不是遍历一遍才知道。

     

    12、尽量不用select *

    SELECT *增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前者也需要经常更新。所以要求直接在select后面接上字段名。

     

    13、区分in和exists

    select * from 表A 
    where id in (select id from 表B)

    上面sql语句相当于

    select * from 表A 
    where exists(select * from 表B where 表B.id=表A.id)

    区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
     

     

    索引的优化:

    1、Join语句的优化

    Join 性能点

    当我们执行两个表的Join的时候,就会有一个比较的过程,逐条比较两个表的语句是比较慢的,因此可以把两个表中数据依次读进一个内存块中,在Mysql中执行:show variables like 'join_buffer_size',可以看到join在内存中的缓存池大小,其大小将会影响join语句的性能。

    在执行join的时候,数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer。

    如果是有索引的情况,则直接读取两个表的索引树进行比较就可以了。

    若没有索引,则会使用 'Block nested loop' 算法,Block 块,也就是说每次都会取一块数据到内存以减少I/O的开销

    另外,Innodb会为每个数据表分配一个存储在磁盘的 表名.ibd 文件,若关联的表过多,将会导致查询的时候磁盘的磁头移动次数过多,从而影响性能

    所以实践中,尽可能减少Join语句中的NestedLoop的循环次数:“永远用小结果集驱动大的结果集”

    1. 用小结果集驱动大结果集,将筛选结果小的表首先连接,再去连接结果集比较大的表,尽量减少join语句中的Nested Loop的循环总次数
    2. 优先优化Nested Loop的内层循环(也就是最外层的Join连接),因为内层循环是循环中执行次数最多的,每次循环提升很小的性能都能在整个循环中提升很大的性能;
    3. 对被驱动表的join字段上建立索引
    4. 当被驱动表的join字段上无法建立索引的时候,设置足够的Join Buffer Size
    5. 尽量用inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用Left Join的场景是大表驱动小表)和NULL,那么如何优化Left Join呢?
      1、条件中尽量能够过滤一些行将驱动表变得小一点,用小表去驱动大表 
      2、右表的条件列一定要加上索引(主键、唯一索引、前缀索引等),最好能够使type达到range及以上(ref,eq_ref,const,system) 
    6. 适当地在表里面添加冗余信息来减少join的次数
    7. 使用更快的固态硬盘

               性能优化,left join 是由左边决定的,左边一定都有,所以右边是我们的关键点,建立索引要建在右边。当然如果索引是在左边的,我们可以考虑使用右连接,如下

    select * from atable
    left join btable on atable.aid=btable.bid;//最好在bid上建索引

    (Tips:Join左连接在右边建立索引;组合索引则尽量将数据量大的放在左边,在左边建立索引)

    2、避免索引失效

    1.最佳左前缀法则

           如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进,判断查询的字段以哪种形式组合能使得查询更快,所有比如创建的是(a,b)索引,查询的是(b,a),查询优化器会修改成(a,b)后使用索引查询。

    2.不在索引列上做任何操作

           (计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。

    3.存储引擎不能使用索引中范围条件右边的列。

            如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引,只有username和age会生效,phone的索引没有用到。

    4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))

           如select age from user减少select *

    5.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。

    6.is null, is not null 也无法使用索引,在实际中尽量不要使用null。

    7.like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。

           所以最好用右边like 'abc%'。如果两边都要用,可以用select age from user where username like '%abc%',其中age是必须是索引列,才可让索引生效

            假如index(a,b,c), where a=3 and b like 'abc%' and c=4,a能用,b能用,c不能用,类似于不能使用范围条件右边的列的索引

           对于一棵B+树来讲,如果根是字符def,如果通配符在后面,例如abc%,则应该搜索左面,例如efg%,则应该搜索右面,如果通配符在前面%abc,则不知道应该走哪一面,还是都扫描一遍吧。

    8.字符串不加单引号索引失效

    9.少用or,用它来连接时会索引失效

    10.尽量避免子查询,而用join

    11、在组合索引中,将有区分度的索引放在前面

           如果没有区分度,例如用性别,相当于把整个大表分成两部分,查找数据还是需要遍历半个表才能找到,使得索引失去了意义。

    12、避免在 where 子句中对字段进行 null 值判断

           对于null的判断会导致引擎放弃使用索引而进行全表扫描。

     

    ② 数据库表结构的优化:使得数据库结构符合三大范式与BCNF

    ③ 系统配置的优化

    ④ 硬件的优化

     

     

    参考链接:

    https://www.zhihu.com/question/36996520

    http://liucw.cn/2018/01/07/mysql/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%E5%88%86%E6%9E%90/

    展开全文
  • 全面深入Mysql数据库优化

    千人学习 2019-09-26 14:36:37
    本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧 、应用优化、数据库优化、数据库日志等方面的知识,并通过综合案例,对课程中的知识进行一个整合...
  • 真·mysql中的SQL优化

    万次阅读 2018-08-01 18:15:42
    1. 优化SQL语句中的一般步骤 通过show status命令了解各种SQL的执行频率 定位执行效率较低的SQL语句 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL...
  • MySQL从5.6到8.0及性能优化

    千次阅读 2019-09-22 18:25:46
    百卓企业版项目一直采用的是mysql5.5版本,由于是单数据库配置,无法分担数据压力,已经达到了mysql5.5的性能瓶颈,在无法使用两个数据库分担压力的实际背景下,决定采用性能更强大的mysql8.0.16版本(当时最新)。...
  • MySQL数据库优化的八种方式(经典必看)

    万次阅读 多人点赞 2019-03-13 15:48:28
    MySQL数据库优化的八种方式(经典必看) 引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿...
  • mysql优化

    千次阅读 2018-08-13 18:42:41
    Mysql优化综合性的问题: A、表的是设计合理化(符合 3范式) B、添加适当的索引(index)[四种:普通索引,主键索引,唯一索引,unique,全文索引] C、分表技术(水平分割,垂直分割) D、读写[写:update/delete/add...
  • MySQL优化技巧

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

    万次阅读 2018-10-16 22:53:17
    MySQL优化   数据库操作往往是当今大多数Web应用程序的主要瓶颈。不仅是DBA(数据库管理员)不得不担心这些性能问题。作为程序员,我们需要通过正确地构造表、编写优化的查询和更好的代码来完成我们的工作。在本文...
  • MySQL性能优化——易实现的MySQL优化方案汇总

    万次阅读 多人点赞 2016-08-26 16:49:00
    1、索引优化及索引失效情况汇总2、表结构优化小技巧3、临时表的优化4、其它优化技巧
  • MySQL查询语句in子查询的优化

    万次阅读 2018-08-28 16:18:18
    项目中有需要,使用MySQL的in子查询,查询符合in子查询集合中条件的数据,但是没想到的是,MySQL的in子查询会如此的慢,让人无法接收,于是上网搜索解决办法,下面记录下: 一、原始in子查询 SELECT * FROM basic_...
  • MySQL left join优化

    万次阅读 2017-09-17 20:24:56
    left join 优化
  • MySQL数据库:SQL语句优化、性能优化详细总结

    千次阅读 多人点赞 2020-09-13 22:16:10
    二、MySQL 常用的SQL语句优化方法: 三、MySQL 常用的索引优化方法: 四、MySQL数据库的优化目标、常见误区和基本原则: 五、MySQL数据库的表结构优化: 六、MySQL数据库的缓存参数优化: 数据库最常用的优化...
  • Mysql根据时间查询日期的优化技巧

    万次阅读 2019-04-18 18:10:30
    这篇文章主要介绍了Mysql根据时间查询日期的优化技巧,非常不错,具有参考借鉴价值,需要的朋友可以参考下 例如查询昨日新注册用户,写法有如下两种: where DATE_FORMAT(u.register_time,'%Y-%m-%d')='2018-01-...
  • mysql优化方面的面试题

    万次阅读 2019-08-15 18:25:48
    1.MySQL数据库作发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化? a. 设计良好的数据库结构,允许部分数据冗余,尽量避免join查询,提高效率。 b. 选择合适的表字段数据类型和存储引擎,适当的添加...
  • MySQL性能优化的最佳20+条经验

    万次阅读 2017-07-28 22:22:03
    MySQL性能优化的最佳20+条经验
  • MySQL中optimize优化

    万次阅读 热门讨论 2016-05-24 20:31:27
    前言当对MySQL进行大量的增删改操作的时候,很容易产生一些碎片,这些碎片占据着空间,所以可能会出现删除很多数据后,数据文件大小变化不大的现象。当然新插入的数据仍然会利用这些碎片。但过多的碎片,对数据的...
  • mysql not in 或in 优化

    千次阅读 2018-08-13 18:28:01
    mysql 中,not in 或in 优化思路, 利用left join 来优化,类似如下的查询方式   select id from a where id in (select id from b )   如这样的查询方式,在大数据量的情况下,查询很慢,需要改写...
1 2 3 4 5 ... 20
收藏数 294,649
精华内容 117,859
关键字:

mysql优化