mysql优化_mysql5.6.44 mysql优化 my.ini - CSDN
  • 本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧 、应用优化、数据库优化、数据库日志等方面的知识,并通过综合案例,对课程中的知识进行一个整合...
  • 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’);

    展开全文
  • ① 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中的SQL优化

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

    1. 优化SQL语句中的一般步骤


    通过show status命令了解各种SQL的执行频率

    这里写图片描述
    这里写图片描述
    这里写图片描述

    定位执行效率较低的SQL语句

    • 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL语句,用-log-slow-queries[=file_name]选 项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志 文件。

    • 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢 询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程, 包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操 作进行优化。

    通过EXPLAIN分析低效的SQL执行计划

    这里写图片描述

    这里写图片描述
    每个列的简单解释如下:

    • select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接
      或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或
      者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。

    • table:输出结果集的表。

    • type:表示表的连接类型,性能由好到差的连接类型为 system(表中仅有一行,即
      常量表)、const(单表中最多有一个匹配行,例如 primary key 或者 unique index)、 eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接 中使用primarykey或者uniqueindex)、re(f 与eq_ref类似,区别在于不是使用primary key 或者 unique index,而是使用普通的索引)、ref_or_null(与 ref 类似,区别在于 条件中包含对 NULL 的查询)、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似, 区别在于 in 的后面是查询非唯一索引字段的子查询)、range(单表中的范围查询)、 index(对于前面的每一行,都通过查询索引来得到数据)、all(对于前面的每一行,都通过全表扫描来得到数据)。

    • possible_keys:表示查询时,可能使用的索引。

    • key:表示实际使用的索引。

    • key_len:索引字段的长度。

    • rows:扫描行的数量。

    • Extra:执行情况的说明和描述。

    确定问题并且采取相应的优化措施

    这里写图片描述
    这里写图片描述
    这里写图片描述

    *2. 索引问题


    • 索引的存储分类

            MyISAM 存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件;InnoDB 存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。
            MySQL 中索引的存储类型目前只有两种(BTREE 和 HASH),具体和表的存储引擎相关: MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引;MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。
            MySQL 目前不支持函数索引,但是能对列的前面某一部分进索引,例如 name 字段,可 以只取 name 的前 4 个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计 表结构的时候也可以对文本列根据此特性进行灵活设计。

    • MySQL如何使用索引
      这里写图片描述

            索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作 性能的最佳途径。
            查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那 么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

    使用索引

    在 MySQL 中,下列几种情况下有可能使用到索引。
    (1)对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用, 举例说明如下。
    首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:
    然后按 company_id 进行表查询,具体如下:
    这里写图片描述
    这里写图片描述
    可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能 用到,这就是索引的前缀特性。但是如果只按 moneys 条件查询表,那么索引就不会 被用到,具体如下:
    这里写图片描述

    (2)对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会 被使用,来看下面两个执行计划:
    这里写图片描述
    这里写图片描述
    可以发现第一个例子没有使用索引,而第二例子就能够使用索引,区别就在于“%”的位置 不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。 另外,如果如果 like 后面跟的是一个列的名字,那么索引也不会被使用。

    (3)如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’。

    (4)如果列名是索引,使用column_name is null将使用索引。如下例中查询name为null 的记录就用到了索引:
    这里写图片描述

    存在索引但不使用索引

    在下列情况下,虽然存在索引,但是 MySQL 并不会使用相应的索引。
    (1) 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如如果列
    key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

    SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

    (2) 如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。
    (3) 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引, 那么涉及到的索引都不会被用到,例如:
    这里写图片描述
    这里写图片描述
    从上面可以发现只有 year 列上面有索引,来看如下的执行计划:
    这里写图片描述
    可见虽然在 year 这个列上存在索引 ind_sales_year,但是这个 SQL 语句并没有用到这个索引, 原因就是 or 中有一个条件中的列没有索引。
    (4) 如果不是索引列的第一部分,如下例子:
    这里写图片描述
    可见虽然在 money 上面建有复合索引,但是由于 money 不是索引的第一列,那么在查询中 这个索引也不会被 MySQL 采用。
    5) 如果 like 是以%开始,例如:
    这里写图片描述
    这里写图片描述
    可见虽然在 name 上建有索引,但是由于 where 条件中 like 的值的“%”在第一位了,那么 MySQL 也不会采用这个索引。

    (6) 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引 起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的 常量值进行转换以后才进行检索。如下面的例子中 company2 表中的 name 字段是字符型的, 但是 SQL 语句中的条件值 294 是一个数值型值,因此即便在 name 上有索引,MySQL 也不能 正确地用上索引,而是继续进行全表扫描。
    这里写图片描述
    这里写图片描述
            从上面的例子中可以看到,第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列 name,那么虽然在 name 列上有索引,但是也没有用到;而第二个 SQL 语句就可以正确使 用索引。

    查看索引使用情况

            如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的 次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。
            Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值 的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描, Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引,具体如下。
    这里写图片描述
    从上面的例子中可以看出,目前使用的 MySQL 数据库的索引情况并不理想。

    3. 两个简单实用的优化方法


            对于大多数开发人员来说,可能只希望掌握一些简单实用的优化方法,对于更多更复杂的优 化,更倾向于交给专业 DBA 来做。

    定期分析表和检查表

    分析表的语法如下:

    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

            本语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信 息,使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计 划,执行一次分析表可能会解决问题。在分析期间,使用一个读取锁定对表进行锁定。这对 于 MyISAM, BDB 和 InnoDB 表有作用。对于 MyISAM 表,本语句与使用 myisamchk -a 相当,下例中对表 msgs 做了表分析:
    这里写图片描述
    检查表的语法如下:

    CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

    检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。 对于 MyISAM 表,关键字统计数据被更新,例如:
    这里写图片描述
    CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在,举例如 下。
    (1)首先我们创建一个视图。
    这里写图片描述
    (2)然后 CHECK 一下该视图,发现没有问题。
    这里写图片描述
    (3)现在删除掉视图依赖的表。
    这里写图片描述
    (4)再来 CHECK 一下刚才的视图,发现报错了。
    这里写图片描述

    定期优化表

    优化表的语法如下:

    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表(含有 VARCHAR、 BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE 命令来进行表优化。这个 命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但 OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。
    以下例子显示了优化表 sales 的过程:
    这里写图片描述

    4. 常用的SQL优化


    前面我们介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中,除了使用查询外,我 们还会使用一些其他的常用 SQL,比如 INSERT、GROUP BY 等。

    大批量插入数据

    当用 load 命令导入数据的时候,适当的设置可以提高导入的速度。
    对于 MyISAM 存储引擎的表,可以通过以下方式快速的导入大量的数据。
    这里写图片描述
    DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入 大量的数据到一个非空的 MyISAM 表时,通过设置这两个命令,可以提高导入的效率。对于 导入大量数据到一个空的 MyISAM 表,默认就是先导入数据然后才创建索引的,所以不用进 行设置。
    下面例子中,用 LOAD 语句导入数据耗时 115.12 秒:
    这里写图片描述
    而用 alter table tbl_name disable keys 方式总耗时 6.34 + 12.25 = 18.59 秒,提高了 6 倍多。
    这里写图片描述
    这里写图片描述
    上面是对MyISAM表进行数据导入时的优化措施,对于InnoDB类型的表,这种方式并不 能提高导入数据的效率,可以有以下几种方式提高InnoDB表的导入效率。
    (1)因为 InnoDB 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺 序排列,可以有效地提高导入数据的效率。
    例如,下面文本film_test3.txt是按表film_test4的主键存储的,那么导入的时候共耗时 27.92秒。
    这里写图片描述
    而下面的 film_test4.txt 是没有任何顺序的文本,那么导入的时候共耗时 31.16 秒。
    这里写图片描述
    从上面例子可以看出当被导入的文件按表主键顺序存储的时候比不按主键顺序存储的时候 快 1.12 倍。
    (2)在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行 SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
    例如,当 UNIQUE_CHECKS=1 时:
    这里写图片描述
    当 SET UNIQUE_CHECKS=0 时:
    这里写图片描述
    可见比 UNIQUE_CHECKS=0 的时候比 SET UNIQUE_CHECKS=1 的时候要快一些。
    (3)如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自
    动提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。 例如,当 AUTOCOMMIT=1 时:
    这里写图片描述
    当 AUTOCOMMIT=0 时:
    这里写图片描述
    对比一下可以知道,当 AUTOCOMMIT=0 时比 AUTOCOMMIT=1 时导入数据要快一些。

    优化 INSERT 语句

    当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。

    • 如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大
      缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语 句快(在一些情况中几倍)。下面是一次插入多值的一个例子:
    insert into test values(1,2),(1,3),(1,4)...
    • 如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有 真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其 他用户对表的读写完后才进行插入;

    • 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);

    • 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 MyISAM 表使用;

    • 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

    优化 GROUP BY 语句

            默认情况下,MySQL 对所有 GROUP BY col1,col2….的字段进行排序。这与在查询中指定 ORDER BY col1,col2…类似。因此,如果显式包括一个包含相同的列的 ORDER BY 子句,则 对 MySQL 的实际执行性能没有什么影响。
            如果查询包括 GROUP BY 但用户想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序,如下面的例子:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    从上面的例子可以看出第一个 SQL 语句需要进行“filesort”,而第二个 SQL 由于 ORDER BY NULL 不需要进行“filesort”,而 filesort 往往非常耗费时间。

    优化 ORDER BY 语句

    在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序。 WHERE 条件和 ORDER BY 使用相同的索引,并且 ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者都是降序。
    这里写图片描述
    但是在以下几种情况下则不使用索引:
    这里写图片描述

    优化嵌套查询

            MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查 询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很 多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起 来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。
            在下面的例子中,要从 sales2 表中找到那些在 company2 表中不存在的所有公司的信息:

    这里写图片描述
    这里写图片描述
    果使用连接(JOIN)来完成这个查询工作,速度将会快很多。尤其是当 company2 表 中对 id 建有索引的话,性能将会更好,具体查询如下:
    这里写图片描述
    从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。
    连接(JOIN)之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这 个逻辑上的需要两个步骤的查询工作。

    MySQL 如何优化 OR 条件

            对于含有 OR 的查询子句,如果要利用索引,则 OR 之间的每个条件列都必须用到索引; 如果没有索引,则应该考虑增加索引。
            例如,首先使用 show index 命令查看表 sales2 的索引,可知它有 3 个索引,在 id、year 两个字段上分别有 1 个独立的索引,在 company_id 和 year 字段上有 1 个复合索引。
    这里写图片描述
    这里写图片描述
    这里写图片描述
    然后在两个独立索引上面做 OR 操作,具体如下:
    这里写图片描述
    可以发现查询正确的用到了索引,并且从执行计划的描述中,发现 MySQL 在处理含有 OR 字句的查询时,实际是对 OR 的各个字段分别查询后的结果进行了 UNION。 但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候,却不能用到索引, 具体结果如下:
    这里写图片描述这里写图片描述

    使用 SQL 提示

    SQL 提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在 SQL 语句中加入一些 人为的提示来达到优化操作的目的。
    下面是一个使用 SQL 提示的例子:

       SELECT SQL_BUFFER_RESULTS * FROM...

    这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后,所有表上的锁 定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助,因为 可以尽快释放锁资源。
    下面是一些在 MySQL 中常用的 SQL 提示。

    1.USE INDEX

    在查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可 以让 MySQL 不再考虑其他可用的索引。
    这里写图片描述

    2.IGNORE INDEX

    如果用户只是单纯地想让 MySQL 忽略一个或者多个索引,则可以使用 IGNORE INDEX 作
    为 HINT。同样是上面的例子,这次来看一下查询过程忽略索引 ind_sales2_id 的情况:
    这里写图片描述
    这里写图片描述
    从执行计划可以看出,系统忽略了指定的索引,而使用了全表扫描。

    3.FORCE INDEX

    为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 HINT。例如, 当不强制使用索引的时候,因为 id 的值都是大于 0 的,因此 MySQL 会默认进行全表扫描, 而不使用索引,如下所示:

    这里写图片描述
    但是,当使用 FORCE INDEX 进行提示时,即便使用索引的效率不是最高,MySQL 还是选择使 用了索引,这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示 后再次执行上面的 SQL:
    这里写图片描述
    这里写图片描述
    果然,执行计划中使用了 FORCE INDEX 后的索引。

    SQL优化调试在日常的开发工作中很重要,快速定位到问题不仅需要一定的技巧,更需要的是经验,熟练使用explain关键字和慢查询日志。

    展开全文
  • 百卓企业版项目一直采用的是mysql5.5版本,由于是单数据库配置,无法分担数据压力,已经达到了mysql5.5的性能瓶颈,在无法使用两个数据库分担压力的实际背景下,决定采用性能更强大的mysql8.0.16版本(当时最新)。...
  • mysql优化

    2018-08-13 18:42:41
    Mysql优化综合性的问题: A、表的是设计合理化(符合 3范式) B、添加适当的索引(index)[四种:普通索引,主键索引,唯一索引,unique,全文索引] C、分表技术(水平分割,垂直分割) D、读写[写:update/delete/add...
  • MySQL数据库优化的八种方式(经典必看) 引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿...
  • MySQL优化技巧

    2017-09-10 14:14:02
    MySQL优化三大方向① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。③ 对SQL语句以及表优化。MySQL参数优化1:MySQL 默认的最大...
  • MySQL优化   数据库操作往往是当今大多数Web应用程序的主要瓶颈。不仅是DBA(数据库管理员)不得不担心这些性能问题。作为程序员,我们需要通过正确地构造表、编写优化的查询和更好的代码来完成我们的工作。在本文...
  • mysql 优化面试题

    2020-03-04 12:22:18
    mysql优化,不用怕面试题 第一方面:30种mysql优化sql语句查询的方法1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引。 2.应尽量避免在 where 子句中使用 !=或&lt...
  • 最近项目不太忙,所以有时间静心来研究下mysql的优化,对于MySQL的设置是否合理优化,直接影响... 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.c...
  • 对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,...下面我们了解一下MySQL优化的一些基础,MySQL的优
  • 当面试官问你mysql优化 首先要知道mysql分为两个层次。你要逐一从各个层次讲解mysql优化,让面试官知道,哇,原来你对mysql这么了解。 面试官:你知道哪些mysql优化? 我: …巴拉巴拉巴拉。 mysql结构层次 首先,...
  • MySQL已经成为时下关系型数据库产品的中坚力量,备受互联网大厂的青睐,出门面试想进BAT,想拿高工资,不会点MySQL优化知识,拿offer的成功率会大大下降。 为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问...
  • 一、mysql优化,主要从哪些方面去考虑 1.存储层 数据表的存储引擎选取, 字段类型的选取, 逆范式 2.设计层 索引的使用, 分区/分表, sql语句的优化, 存储过程优化 3.架构层 分布式部署(读写分离) 4.sql语句...
  • 应用程序慢如牛,原因多多,可能是网络的原因、可能是系统...MYSQL优化技巧 程序中嵌入的一行行的SQL语句,如果使用了一些优化小技巧,定能达到事半功倍的效果。下面是微课帮小编整理的一些技巧。  技巧1 :比较...
  • 第一方面:30种mysql优化sql语句查询的方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引。  2.应尽量避免在 where 子句中使用 !=或&lt;&gt; 操作符,...
  • MySQL优化工具之profiling 使用慢查询日志分析出慢查询语句后,用profiling分析该语句的优化后执行效果。 查看慢查询设置 mysql> show variables like "%slow%"; +---------------------+------...
1 2 3 4 5 ... 20
收藏数 284,082
精华内容 113,632
关键字:

mysql优化