MySQL数据库高级篇（二）

1、存储引擎

1.1 存储引擎概述

存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可称为表类型。

 1.2 各种存储引擎特性

1.3 InnoDB

Mysql事务默认自动提交。

1.4 MyISAM

1.3 存储引擎的选择

 

2、优化SQL步骤

在应用的开发过程中，由于初期数据量小，开发人员写SQL时更重视功能上的实现；但是当应用系统上线后，随着生产数据量的急剧增长，很多SQL语句逐渐出现性能问题，对生产的影响越来越大。因此，必须对SQL语句进行优化。

2.1 查看SQL执行频率：分析当前数据库（InnoDB）操作插入为主还是查询为主

2.2 定位低效率执行SQL

通过 show processlist : State字段来查看慢查询语句 --> Sending data，Info 字段显示正在执行的sql语句

慢查询的sql语句： 

 

2.3 explain分析执行计划

2.3.1 explain之id

2.3.2 explain之select_type

 

2.3.3 explain之type

type 显示的是访问类型，是较为重要的一个指标。通过type能过大概知道当前sql语句的耗时情况、执行效率的情况。

可取值为：

创建普通索引：

index（走索引） 与 all（全表扫描）： 

2.3.4 explain之key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 

key ： 实际使用的索引。key对于sql语句优化的判定很重要。如果key为NULL， 则没有使用索引。有可能建了索引但没有走索引（WHY）。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

2.3.5 explain之rows

2.3.6 explain之extra

前两个若出现则需要考虑优化sql，效率低、非常耗性能。

2.4 show profile分析SQL

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

show profiles;

通过show profile for query query_id ：语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

 Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

 

3、索引的使用

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

创建复合索引：

3.1 避免索引失效

1). 全值匹配 ，对索引中所有列都指定具体值。

该情况下，索引生效，执行效率高。

注意点：跟where后面字句的顺序无关。where status =  and  address =   and name =   ;

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

3). 尽量使用覆盖索引，避免select *，因为可能会需要回表查询

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 

4）范围查询右边的列不能使用索引，也就是范围查询之后的字段索引失效

范围查询之后的字段索引会失效 ：address索引失效

5）不要在索引列上进行运算操作，索引将失效

6）字符串不加单引号，索引失效

status是一个varchar字符串类型

7）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么索引失效。

8）以%开头的like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

9）如果MySql评估使用索引比全表扫描更慢，则不使用索引

10) is NULL，is NOT NULL，有时索引失效（根据数据库中的数据量来决定：数据量很少则使用索引）

11）in走索引，not in 索引失效

4、SQL优化

4.1 大批量插入数据

1）导入数据时，按主键自增顺序插入

2) 关闭唯一性校验

4.2 优化insert语句

4.3 优化oreder by 语句

通过索引直接返回有序数据。where条件和Order by 使用相同的索引，并且Order by 的顺序和索引顺序相同。Order by 的字段要么都是升序，或者都是降序。