java面试总结（六）sql性能优化

一、分类
sql性能优化从思路上讲可以分为三类：

1、从数据库设计上优化：

（1）例如，订单信息统计查询这个业务，一般这种统计查询业务会涉及到订单表，流水表，商品表等，如果每张表在数据量达到百万级的时候，在统计查询的时候多张表关联查询会影响性能。 对如这种业务情况，在数据库设计时，可以设计专门用于查询的统计表，将这些订单、流水、商品的信息在创建订单和订单状态变更的时候将对应的信息插入或变更到这些统计表中，可以使用mq异步解耦合将这些信息冗余到统计表中。
（2）继续上续例子，对于这种专门用于统计查询的表，如果是mysql数据库，那么表的引擎可以设置为MyISAM （MyISAM 不支持事务。适合执行大量的SELECT），对于订单、流水、商品这些表的引擎可以设置为InnoDB （InnoDB 支持事务，适合执行大量的update和insert）。
（3）在设计表时，主键尽量设置数字类型（自增长），尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销；尽可能的使用 varchar/nvarchar 代替 char/nchar ， 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
（4）在mysql中，选择存储引擎，并发选用myisam,事务选择innodb，myisam比innodb更容易出错，出错了恢复的时间也比较长，只有myisam支持全文检索。
（5）尽可能的要把field定义为Not NULL， mysql比较难优化使用了可空列的查询，它会使索引，索引统计更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理，当可空列被索引时，每条记录都需要一个格外的字节，即使要在表中存储"没有值"的字段，考虑使用0，特殊字段或者空字符串来代替。

2、从业务设计上优化

（1）对于需要关联多张表数据量大但对数据实时性要求不高的业务查询功能，这种可以设计一张业务查询表，将需要查询的数据通过定时任务或者其他技术方案刷到这张业务查询表，这样业务查询功能就可以直接查询这张表，单张表查询的性能还是可以的。
（2）对于关联多张表数据量大但对实时性有要求的业务查询功能，这种程序中可以集成ES，通过mq将数据实时插入ES中，然后业务查询功能就可以直接查询ES。

3、从编写sql上优化

（1）编写查询sql，不要使用select *这种，查询哪些列就写哪些列，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
（2）应尽量避免在 where 子句中对字段进行 null 值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值； 应尽量避免在 where 子句中使用!=或<>操作符， MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。
（3）应尽量避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行全表扫描， 可以 使用UNION合并查询： select id from t where num=10 union all select id from t where num=20；尽量少用like‘%abc‘’ 或者 like‘%abc%’，可以使用like‘abc%’。
（4）避免在 where 子句中对字段进行表达式操作，应尽量避免在where子句中对字段进行函数操作，尽量少用in，可以使用exists 代替in。
（5）简化sql的时候，可以考虑使用临时表暂存中间结果，避免多次扫描主表。
（6）少用子查询，一个sql语句不要有5个以上的join，一般一张表尽量不要超过6个索引。

二、索引

1、索引的原理：

a、想要理解索引原理必须清楚一种数据结构平衡树(非二叉)，也就是Btree或者 B+ tree， 有的数据库也使用哈希桶作用索引的数据结构 ，但是目前主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

b、我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。

c、实际上一个加了主键的表，并不能被称之为表，一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我们认知中的表很接近。如果给表加上主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是平衡树结构，换句话说，就是整个表就变成了一个索引，也就是所谓的聚集索引。

d、 这就是为什么一个表只能有一个主键， 一个表只能有一个聚集索引，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

e、非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据

2、分析mysql的引擎MyISAM和 InnoDB 两个存储引擎的索引实现方式：
a、 InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

b、同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。
MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址，MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。

3、不要在建立的索引的数据列上进行下列操作：

    避免对索引字段进行计算操作
	避免在索引字段上使用not，<>，!=
	避免在索引列上使用IS NULL和IS NOT NULL 
	避免在索引列上出现数据类型转换
	避免在索引字段上使用函数 
	避免建立索引的列中使用空值
	避免建立索引的列中使用like‘%abc’或者like‘%abc%’，这种会造成全表扫描
	如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
    频繁更新的字段不要使用索引