MySQL 性能优化的 9 种知识.docx

MySQL性能优化的9种知识，面试再也不怕了！1、选择最合适的字段属性Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。例如：在定义邮政编码这个字段时，如果将其设置为char (255),显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是用MEDIUM完T而不是BIGINT来定义整形字段。2、尽量把字段设置为NOT NULL在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。3、使用连接(JOIN)来代替子查询(Sub-Queries)MySQL从4. 1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如：我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询，如下图所示：DELETE FROM customerinfoWHERE Customers NOT in (SELECT custoierid FROM salesinfo)https:/blog. csdn. net/we如果使用连接（JOIN）来完成这个工作，速度将会快很多，尤其是当salesinfo表中对CustomerlD建有索引的话，性能将会更好，查询如下：SELECT * FROM customerinfoLEFT JOIN salesinfo ON customerinfo.customerid -salesinfo.customer:https:/blog. csdn. neWHERE salesinfo.customerid IS NULL连接（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 RIGHT JOIN ON B 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;sql中的连接查询有inner join（内连接）、left join（左连接）、right join （右连接）、full join （全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。例如我们有两张表:Persons1, 表:ld_PLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing“Orders” 表:M_OOrderNoW_P177895324467833224561424562153476465Orders表通过外键Td_P和Persons表进行关联。inner join （内连接），在两张表进行连接查询时，只保留两张表中完全匹配的结果集。我们使用inner join对两张表进行连接查询，sql如下：SELECT p. LastName, p. FirstName, o. OrderNoFROM Persons pINNER JOIN Orders oON p. Id_P=o. Id.P and 1=1 一用and连接多个条件ORDER BY p. LastName杳询结果集:LastNameFirstNameOrderNoAdamsJohn22456AdamsJohn24562CarterThomas77895CarterThomas44678此种连接方式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。 一 即就是内连接。但是这种写法并不符合规范，可能只对某些数据库管用，如sql servero推荐最好不要这样写。最好写成inner join的写法。内连接查询(solccl * from a join b on a. id = b. id)与关联查询(select *from a , b whore a. id = b. id)的区别left join,在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。我们使用left join时两张表进行连接查询，sql如下:SELECT p. LastName, p. FirstName, o. OrderNo.EFT JOIN Orders o查询结果如下:LastNameFirstNameOrderNoAdamsJohn22456AdamsJohn24562CarterThomas77895CarterThomas44678BushGeorge可以看到，左表（Persons表）中Las （Name为Bush的行的Id P字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。right join,在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。我们使用right join对两张表进行连接查询,sql如下:SELECT p. LastName, p. FirstZame, o. OrderNo|查询结果如F：AdamsJohn22456AdamsJohn24562CarterThomas77895CarterThomas4467834764LastNameFirstNameOrderNoOrders表中最后一条记录Id_P字段值为65,在左表中没有记录与之匹配，但依然保留。full join,在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。我们使用full join对两张表进行连接查询，sql如下：SELECT p. LastName, p. FirstName, o. OrderNoFROM Persons pFULL JOIN Orders oORDEBpastNa查询结果如下:查询结果是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）、连接我0IN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中的数据的一致性和完整性。事务以BEGTN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。BEGIN;lNSERTlNT0salesinfoSETCustomerlD=14;UPDATEinventorySETQuantity=llWHEREitem=，book'COMMIT;事务的另一个作用是当多个用户同时使用相同的数据源时，他可以使用锁定数据库的方式来为用户提供一种安全的访问机制，这样可以保证用户的操作不被其它的用户所干扰。一般来说，事务必须满足四个条件(ACID)：原子性(Atomicity,或称不可分割性)、一致性(Consistency) 隔离性(Isolation,又称独立性)、持久性(Durability).原子性：一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执行过一样。一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并