重点知识！！关于对mysql的优化总结

Mysql优化方案总结

mysql优化是一个综合性的技术，主要包括：

1、表的设计是否合理

2、索引的使用

3、分表技术

4、主从复制以及读写分离

5、存储过程

6、对mysql的配置优化

7、sql优化

8、mysql硬件升级

9、合理使用redis等非关系型数据库

10、合理使用solr/ES等搜索应用服务器

11、利用freemaker实现页面静态化

1、表的设计是否合理

mysql表的优化设计分为下面几部分：

1、表设计是否符合三范式

2、字段的数据类型是否选取最优

3、冗余字段合理设计

4、是否符合实际的业务要求（高并发、大数据量）

5、合理的设计一些备用字段

1.1、表结构是否符合三范式

首先说一下数据库设计的三范式：

a.第一范式：确保每列保持原子性

b.第二范式：确保表中的每列都和主键相关

c.第三范式：确保每列都和主键列直接相关,而不是间接相关

业务表和字典表？

1.1.1、确保每列保持原子性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

上表如果不拆分，当我们需要查询“城市”部分时，只能使用“like”去做模糊查询，而like会进行全表扫描，会造成索引失效。

上表如果拆分了，当我们需要查询“城市”部分时，用“=”就可以满足，而“=”是精确查找，不会造成索引失效。

1.1.2、确保表中的每列都和主键相关

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。减轻了单个表的访问压力，如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

1.1.3、确保每列都和主键列直接相关,而不是间接相关

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

如果将这2张表设计到一起，每次不管是对订单信息还是访问客户信息进行操作，都需要操作同一张表，数据少看不出来，一旦当数据量达到一定程度，比如上千万的数据。数据的操作会变的非常慢，单表压力太大。拆分之后可以大大减轻单表的压力。

1.1.4、适当拆分

当我们的表中存在类似于text或者很大的varchar类型的大字段的时候，如果我们访问这张表的时候大部分都不需要这个字段，将其拆分到另外的独立表中，减少占用的存储空间。提高内存的缓存命中率。blob

1.2、字段的数据类型是否选取最优

由于mysql是基于行（row）的数据库，数据库在操作IO的时候是以page的方式。而数据库操作中最为耗时的是IO处理，大部分数据库操作90%以上的时间都花在IO读写上面。所以尽可能减少IO读写量。

因为数据库的大部分操作都是在IO读写上面，所以选择最优的字段数据类型，可以减少IO读写量

1、数字类型：

非万不得已不要使用double,不仅仅是存储长度的问题，同时还存在精确性的问题。同样，固定精度的小数，也不建议使用decimal,建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本（例如在数据库中金额都是以分为单位存储）,在数据库中保存身高，都是以厘米为单位。

对应整数的存储，在数据量较大的情况下，建议区分开tinyint/int/bigint的选择，因为三者占用的存储空间也有很大的差别。

2、字符类型：

非万不得已不要使用text数据类型，其处理方式决定了他的性能要低于char和varchar类型的处理。定长字段，建议使用char类型，不定长字段尽量使用varchar,且仅仅设定适当的最大长度。

如果一定要使用text类型，则单独创建一个表，用来存放text类型的数据、

3、时间类型：

尽量使用timestamp类型，因为存储空间只需要datetime类型的一半。对于只需要精确到某一天的数据，建议使用date类型，因为它的存储空间只需要3个字节，比timestamp还少。（例如：存储生日字段，我们只需要存储到天，使用date类型最好，而对于平时的创建和更新时间字段，则使用timestamp类型，但是如果时间范围在'1970年' 到 '2038年'之外的，则需要使用datetime类型。）

4、blob:

强烈反对在数据库中存放BLOB 类型数据（能用来保存二进制，如图片）,用更适合的工具，才可以发挥到极致。例如我使用到了FastDFS,阿里的OSS。而且BLOB 类型数据的访问不是线程安全的，需要为其单独分配相应的数据库资源，并在操作完成后释放资源。

一些面试题：

1、为什么一定要设一个主键？
2、你们主键是用自增还是UUID?
3、主键为什么不推荐有业务含义?
4、表示枚举的字段为什么不用enum类型？
5、货币字段用什么类型?
6、时间字段用什么类型?
7、为什么不直接存储图片、音频、视频等大容量内容?
8、字段为什么要定义为NOT NULL?
9、一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15

问题1:为什么一定要设一个主键？

回答:因为你不设主键的情况下，innodb也会帮你生成一个隐藏列，作为自增主键。所以啦，反正都要生成一个主键，那你还不如自己指定一个主键，在有些情况下，就能显式的用上主键索引，提高查询效率！

mybatis返回自增主键id >>mybatis.xml insert指定3个属性》》useGeneratedKeys= true 和keyProperty

问题2:主键是用自增还是UUID?

回答:肯定答自增啊。innodb 中的主键是聚簇索引。如果主键是自增的，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果不是自增主键，那么可能会在中间插入，就会引发页的分裂，产生很多表碎片！。

上面那句话看不懂没事，大白话一句就是:用自增插入性能好！

另外，附一个测试表给你们，表名带uuid的就是用uuid作为主键。大家看一下就知道性能差距了:

如上图所示，当主键是UUID的时候，插入时间更长，而且占用空间更大！

额，大家千万不要忘了，当你回答自增主键后，想一下

ps：这个问题，你要是能把UUID讲出合理的理由也行。

问题3:主键为什么不推荐有业务含义?

回答:有如下两个原因

(1)因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。
(2)带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。

问题4:表示枚举的字段为什么不用enum类型？

回答:在工作中表示枚举的字段，一般用tinyint类型。

那为什么不用enum类型呢？下面两个原因

(1)ENUM类型的ORDER BY操作效率低，需要额外操作

(2)如果枚举值是数值，有陷阱

举个例子，表结构如下

CREATE TABLE test(foobar ENUM( '0', '1', '2'));

此时，你执行语句

mysql>INSERT INTO test VALUES (1);

查询出的结果为

foobar

就产生了一个坑爹的结果。

插入语句应该像下面这么写，插入的才是1

mysql>INSERT INTO test VALUES (`1`);

问题5:货币字段用什么类型?

回答:如果货币单位是分，可以用Int类型,比如我们在对接微信支付的时候用到的金额单位就是分。如果坚持用元，用Decimal。

千万不要答float和double，因为float和double是以二进制存储的，所以有一定的误差。

打个比方，你建一个列如下

CREATETABLE`t`(

`price`float( 10, 2) DEFAULTNULL,

) ENGINE= InnoDBDEFAULTCHARSET=utf8

然后insert给price列一个数据为1234567.239，你会发现显示出来的数据变为1234567.23，精度失准！

问题6:时间字段用什么类型?

回答:此题无固定答案，应结合自己项目背景来答！把理由讲清楚就行！

(1)varchar，如果用varchar类型来存时间，优点在于显示直观。但是坑的地方也是挺多的。比如，插入的数据没有校验，你可能某天就发现一条数据为2013111的数据，请问这是代表2013年1月11日，还是2013年11月1日？

其次，做时间比较运算，你需要用STR_TO_DATE等函数将其转化为时间类型，你会发现这么写是无法命中索引的。数据量一大，是个坑！

(2)timestamp，该类型是四个字节的整数，它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间，是无法用timestamp类型存储的。

但是它有一个优势，timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区

SETTIME_ZONE= "america/new_york";

你会发现，项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！

(3)datetime，datetime储存占用8个字节，它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，他存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！

(4)bigint，也是8个字节，自己维护一个时间戳，表示范围比timestamp大多了，就是要自己维护，不大方便。

问题7:为什么不直接存储图片、音频、视频等大容量内容?

回答:我们在实际应用中，都是用HDFS来存储文件。然后mysql中，只存文件的存放路径。mysql中有两个字段类型被用来设计存放大容量文件，也就是text和blob类型。但是，我们在生产中，基本不用这两个类型！

主要原因有如下两点

(1)Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢
(2)binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！

因此，不推荐使用text和blob类型！

问题8:字段为什么要定义为NOT NULL?

回答:OK，这问题从两个角度来答

(1)索引性能不好

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。 —— 出自《高性能mysql第二版》

(2)查询会出现一些不可预料的结果

这里举一个例子，大家就懂了。假设，表结构如下

create table table_2 (

`id`INT( 11) NOTNULL,

name varchar( 20) NOTNULL

)

表数据是这样的

id	name
1	孤独烟
3	null
5	肥朝
7	null

你执行语句

selectcount( name) fromtable_2;

你会发现结果为2，但是实际上是有四条数据的！类似的查询问题，其实有很多，不一一列举。

记住，因为null列的存在，会出现很多出人意料的结果，从而浪费开发时间去排查Bug.

问题9：一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15

2、索引的使用

MySQL目前主要有以下几种索引类型：

1.普通索引

2.唯一索引

3.主键索引

4.组合索引

5.全文索引

2.1、普通索引

就经常使用到的字段（品牌id），创建一个普通索引。这种索引也是我们经常用到的索引，比如按照姓名(全名)查找。在姓名字段上创建普通索引，注册时，判断手机号是否存在，在手机号字段上添加普通索引

select count(*) from tb_user where phone = 123455

创建普通索引

ALTER TABLE logs1 ADD INDEX index_htmlname(htmlname)

删除索引

DROP INDEX index_name ON table

2.2、唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。比如我们用户在注册时判断手机号是否已经注册，手机号这个字段就可以添加一个唯一索引。

创建唯一索引

ALTER TABLE logs1 ADD UNIQUE index_htmlname(htmlname)

分布式锁:

zookeeper+table+redis

数据库实现分布式锁的时候，在method_name(方法名称)上面加了一个唯一索引。

2.3、主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

2.4、组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。比如在登陆日志（几千万数据)中我想知道某人在某年登陆了多少次。可以把username和time建立一个组合索引。

创建组合索引

ALTER TABLE article ADD INDEX index_titme_time (username(50),time(10))。

2.5、什么情况下索引会失效

1、like >> 模糊查询，全表扫描，除了左like

2、聚合函数 sum(name)、min、max、avg（一般和having结合使用）。避免有索引的字段进行函数运算

3、or

4、聚合索引查询语句的字段顺序和索引的字段顺序不一致。

3、分表技术

我们这里使用的数据库中间件mycat来实现数据的切分。根据其切分规则的类型，可以分为两种切分模式:

sechme:>>逻辑库

table》》逻辑表

datanode ="dh1"

datanode ="dh2"

datahost = url:0.2

datahost = url:0.3

一种是按照不同的表来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；

另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分

3.1、垂直拆分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。

优点：

拆分后业务清晰，拆分规则明确；

系统之间整合或扩展容易；

数据维护简单。

缺点：

部分业务表无法join，只能通过接口方式解决，提高了系统复杂度；

受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高；

事务处理复杂（TCC）》》try confirm cancle

分布式锁解决方案：redis

分布式事务方案：seata TM:主事务 RM：分事务 TC：seata server @Transcation

单体同步方案：同步方法，同步代码块，volatile，threadlocal

单体事务：@Trancation

3.2、水平切分

由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决。

典型拆分规则：

1、从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分，不同的数据按照会员 ID 做分组，这样所有的数据查询 join 都会在单库内解决；

2、按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中；

3、按照日期，将不同月甚至日的数据分散到不同的库中；

4、按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中，具体需求，具体分析

5、从数据的角度来分析，iot中设备的上报数据量比较大，大棚里查询某个设备某个时间点上报的数据，或者时间段内上报的数据，那么就需要按照大棚来进行拆分，不同大棚的数据拆分到不同的数据库中，不同的数据按照大棚id做分组，这样所有的数据查询 join 都会在单库内解决；

优点：

拆分规则抽象好，join 操作基本可以数据库做；

不存在单库大数据，高并发的性能瓶颈；

应用端改造较少；

提高了系统的稳定性跟负载能力。

缺点：

拆分规则难以抽象；

分片事务一致性难以解决；

数据多次扩展难度跟维护量极大；

跨库 join 性能较差。

4、主从复制以及读写分离

如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。

为了提升业务系统性能，优化用户体验，可以通过做主从复制（读写分离）来减轻主数据库的负载。

而且表的数据量大，处理慢，CPU时不时100%，如果出现主数据库宕机，可快速将业务系统切换到从数据库上，可避免数据丢失。

因此，从保护数据库的角度来说，我们应该尽量避免没有主从复制机制的单节点数据库。

4.1、主从复制

对于 MySQL 来说，标准的读写分离是主从模式，一个写节点 Master 后面跟着多个读节点，读节点的数量取决于系统的压力。

MySQL Replication 可以将一个主数据库中的数据同步到一个或多个从数据库中（二进制日志）。并且这个同步过程默认以异步方式工作，不需要保持主从数据库的实时连接（即允许连接中断）。同时允许自定义配置需同步的数据库及数据表。

同步：发送马上收到结果。毛念民给张晓龙打电话借钱，张晓龙在电话里就回复了没钱。

异步：发送，过会收到结果。毛念民给张晓龙打电话借钱，张晓龙说我正忙呢，先挂了，一会回给你。一会给毛念民回电话：么钱！

5、存储过程

优点：

1、通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。在编译存储过程之后，MySQL将其放入缓存中。MySQL为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。 2、存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。 3、存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。 4、存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

缺点：

1、如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。

此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。

2、存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。

3、很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。而且，MySQL不提供调试存储过程的功能。 4、开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。

这可能会导致应用程序开发和维护阶段的问题。

6、mysql配置优化

6.1：存储引擎选择

查看当前存储引擎：show engines;

MyISAM：

1、特性

不支持事务

表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小，但是也同时大大降低了其并发性能。

读写相互阻塞：不仅会在写的时候阻塞读取，MyISAM还会在读的时候阻塞写入，单读本身不会阻塞另外的读。

只会缓存索引：MyISAM可以用过key_buffer缓存大大提高访问性能减少磁盘IO,但这个缓存是缓存索引，不会缓存数据。

2、使用场景

不需要事务支持

并发相对较低

数据修改相对较少

已读为主

数据一致性要求不高

InnoDB:

1、特性

具有较好的事务支持，支持4个事务隔离级别，支持多版本读

MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。

未提交读：另一个事务修改了数据，但尚未提交，而本事务中的select会读到这些未被提交的数据（脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。）（隔离级别最低，并发性能高）

提交读：本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的select会读到不同的结果（不重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据。）

可重复读：在同一个事务里，select的结果是事务开始时时间点的状态，因此，同样的select操作读到的结果会是一致的。但是会有幻读现象（指一个线程中的事务读取到了另外一个线程中提交的insert的数据。）

串行化：读操作会隐式获取共享锁，可以保证不同事务间的互斥（锁表）。

行级锁定：通过索引实现，全表扫描仍然会是表锁。

读写阻塞与事务隔离级别有关

具有非常高效的缓存特性：能缓存索引，也能缓存数据

2、适用场景

需要事务支持

行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的

数据更新较为频繁的场景

数据一致性要求高

6.2 、参数配置

InnoDB配置

innodb_file_per_table

表的数据和索引存放在共享表空间里或者单独表空间里。我们的工作场景安装是默认设置了innodb_file_per_table = ON，这样也有助于工作中进行单独表空间的迁移工作。MySQL 5.6中，这个属性默认值是ON。

_flush_log_at_trx_commit

默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。

如果将它的值设置为2会导致不太可靠（unreliable）。因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。

innodb_buffer_pool_size

这个参数应该是运维中必须关注的了。缓冲池是数据和索引缓存的地方，它属于MySQL的核心参数，默认为128MB，正常的情况下这个参数设置为物理内存的60%~70%。（不过我们的实例基本上都是多实例混部的，所以这个值还要根据业务规模来具体分析。）

max_connections

MySQL服务器默认连接数比较小，一般也就100来个最好把最大值设大一些。一般设置500~1000即可每一个链接都会占用一定的内存，所以这个参数也不是越大越好。有的人遇到too many connections会去增加这个参数的大小，但其实如果是业务量或者程序逻辑有问题或者sql写的不好，即使增大这个参数也无济于事，再次报错只是时间问题。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

7、SQL优化

1、count(column) 和 count(*) 、count(1) 、count(0)适用区别

count(column) 是表示结果集中有多少个column字段不为空的记录；

count(*) 是表示整个结果集有多少条记录；

2、order by 一定需要排序操作

利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段

3、不适用子查询

例：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子查询无效，固生产环境应避免使用子查询

4、避免函数索引

例：SELECT * FROM t WHERE YEAR(createtime) >= 2016;

由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。

应改为—–>

SELECT * FROM t WHERE d >= '2016-01-01';

5、用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);

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

SELECT * FROM t WHERE name LIKE ‘%de%’;

—–>

SELECT * FROM t WHERE name LIKE ‘de%’;

目前只有MySQL5.7支持全文索引（支持中文）

思考一下：mysql的like中%与_的区别？

%对应于0个或更多字符，_只是LIKE语句中的一个字符

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’);

11、explain+慢SQL分析

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

使用方式：Explain+SQL语句

8、mysql硬件升级

9、使用redis等非关系型数据库

9.1、redis解决的问题

1.少量数据存储，高速读写访问。比如抢购和秒杀

　 2.海量数据存储，分布式系统支持，数据一致性保证，方便的集群节点添加/删除。

9.2、redis特点：

1 、Redis不仅仅支持简单的k/v类型的数据，同时还提供list，set，zset，hash等数据结构的存储。

2 、Redis支持数据的备份，即master-slave模式的数据备份。

3 、Redis支持数据的持久化，可以将内存中的数据保持在磁盘中，重启的时候可以再次加载进行使用。aof和rdb

9.3、redis数据类型

Redis最为常用的数据类型主要有以下：

String
Hash
List
Set
Sorted set

String：

是最常用的一种数据类型，普通的key/ value 存储都可以归为此类.即可以完全实现目前 Memcached 的功能，并且效率更高。还可以享受Redis的定时持久化，操作日志及 Replication等功能。

list：

的应用场景非常多，也是Redis最重要的数据结构之一，比如twitter的关注列表，粉丝列表等都可以用Redis的list结构来实现。

Set/Zset：

集合的概念就是一堆不重复值的组合。利用Redis提供的Sets数据结构，可以存储一些集合性的数据，比如在微博应用中，可以将一个用户所有的关注人存在一个集合中，将其所有粉丝存在一个集合。Redis还为集合提供了求交集、并集、差集等操作，可以非常方便的实现如共同关注、共同喜好、二度好友等功能，对上面的所有集合操作，你还可以使用不同的命令选择将结果返回给客户端还是存集到一个新的集合中。

10、合理使用solr/Es等搜索应用服务器