一、存储引擎

1.MySQL体系结构

2.存储引擎简介

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

建表语句：

查询数据库支持的储存引擎：

show engines;

3.储存引擎特点

InnoDB:一种兼顾高可靠性和高性能的通用储存引擎，在MySQL5.5之后，InnoDB是默认的MySQL储存引擎

特点：

DML操作遵循ACID模型，支持事务

行级锁，提高并发访问性能

支持外键FOREIGN KEY约束，保证数据的完整性和正确性

文件：

xxx.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储表的结构(frm、sdi)、数据和索引

MyISAM:MyISAM是MySQL早期的默认存储引擎

特点：

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

文件：

xxx.sdi:存储表结构信息

xxx.MYD:存储数据

xxx.MYI:存储索引

Memory:Memory引擎的表数据时存储在内存的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

特点：

内存存放

hash索引（默认）

文件：

xxx.sdi::存储表结构信息

二、索引

优点：

提高数据检索的效率，减低数据的IO成本

通过索引列对数据进行排序，降低数排序的成本，降低cpu的消耗

缺点：

索引列也是要占用空间的

索引大大提高了查询效率，同时也降低更新表的速度，如对表进行插入、更新、删除时，效率低

B树与B+树的区别：

B+树所有的数据都会出现在叶子节点

叶子节点形成一个单向链表

索引结构：

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree地基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

hash索引优点：查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

hash索引缺点：不支持范围查询，无法利用索引进行排序操作

不采用二叉树的原因：B+tree相较于二叉树，层级更少，搜索效率高

不采用B树的原因：B+tree相较于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

不采用Hash索引的原因：hash索引只支持等值匹配，不支持范围匹配及排序操作

1.索引分类

注意：

如果存在主键，主键索引就是聚集索引

如果不存在主键，将使用第一个唯一索引作为聚集索引

如果表没有主键，或没有合适的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引

2.索引语法

创建索引

查看索引

删除索引

3.SQL性能分析

（1）MySQL客户端连接成功后，通过show[session|global] status 命令可以提供服务器状态信息。

查看crud访问频次：

（2）慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有sql语句日志

注意：MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置

查看配置信息：

 show variables like "%slow%";

开启慢查询：

 set global  slow_query_log =ON;

设定慢查询的时间：

set global slow_launch_time=1;

查看慢查询信息：

（3）show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MYSQL是否支持

查看数据库是否支持profile操作：

select @@have_profiling;

开启profile:

set profiling=1;

查看sql的执行时间：

show profiles;

查看sql语句各阶段执行情况：

 show profile for query Query_ID;

查看sql语句各阶段cpu使用情况：

 show profile cpu for query Query_ID;

（4）explain命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接的和连接的顺序

 explain sql语句;

id:select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。

type:表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。

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

Key:实际使用的索引，如果为null，则没有使用索引。

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

row:MySQL认为必须要执行查询的行数，在innodb引擎表中，是一个估计值，可能并不准确的。

filtered:表示返回结果得行数占需读取行数的百分比，filtered的值越大越好

4.索引的使用

（1）、如果索引了多列，要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

（2）、联合索引中，出现范围查询（>,<),范围查询右侧的列索引失效。

  (3)   、不要在索引列上进行运算操作，索引将失效。

  (4)  、 字符串类型字段使用时，不加引号，索引将失效。

  (5)   、如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。     

（6）、 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到

（7）、如果MySQL评估使用索引比全表更慢，则不使用索引

（1）SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

use index:

select id,sn  from tb_sku use index (idx_sku_sn_name) where sn= 1000000031450099999;

ignore index:

select id,sn  from tb_sku ignore index  (idx_sku_sn_name) where sn= 1000000031450099999;

force index:

select id,sn  from tb_sku force index(idx_sku_sn)  where sn= 1000000031450099999;

（2）尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select*

（3）当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节省索引空间，从而提高效率

 语法：

 create index idx_sku_sn_2 on tb_sku(sn(2));

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值和数据表得记录总数的比值，索引的选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能是最好的

计算公式：

select count(distinct substring(sn,1,15))/count(*) from tb_sku;

（4）在业务场景中，如果存在多哥查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

注意：多条件联合查询时，MYSQL优化器会评估哪个字段的索引效率高，会选择该索引完成本次查询

（5）设计原则

5、SQL优化

插入大量数据：

客户端连接服务端：

mysql --local-infile -u root -p

设置全局参数：

set global local_infile=1;

导入数据：

load data local infile 文件名称 into table 表名 fields terminated by ',' lines terminated by '\n';

主键优化：

页分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据太多，会行溢出），根据主键排列

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用

注意：

1.满足业务需求的情况下，尽量降低主键的长度

2.插入数据时，尽量选择顺序插入，选择使用自增主键

3.尽量不要使用UUID做主键或者是其他自然主键，如身份证号

4.业务操作时，避免对主键修改

order by优化：

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在缓冲区中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort

Using index:通过有序索引排序扫描直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率高

注意：

group by优化：在分组操作时，可以通过索引来提高效率。分组操作时，索引的使用也是要满足最左前缀法则的

limit优化：limit 2000000,10,此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。一般分页查询时，通过创建覆盖能够比较好地提高性能，可以通过覆盖索引加子查询地形式进行优化

示例：

 select a.* from tb_sku as a,(select id from tb_sku order by id limit 2000000,10) as b where a.id=b.id;

count优化：

MyISAM:引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个树，效率很高

InnoDB:执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

排序效率：count(*)=count(1)>count(主键id)>count(字段）

update优化：where之后的条件需要建立索引

innoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。