MySQL存储引擎和索引简介

存储引擎

1.如何用命令查看你当前mysql已经提供了哪些存储引擎：

mysql>show engines;

2.看你的mysql默认的存储引擎：

mysql>show variables like '%storage_engine%';

3.常用两种Mysql存储引擎MyISAM和InnoDB简单对比：

索引优化分析

SQL性能下降原因

性能下降SQL慢，执行时间长，等待时间长

• 查询语句写的太烂
• 索引失效（单值索引/复合索引）

什么是单值索引？什么是复合索引？

假设有一张user表，里面有id,name,email等字段

有一种频繁查询是根据name查询

select * from user where name='';

为了查询优化，我们可以给name这个字段建索引

create index idx_user_name on user(name);

idx_user_name就是索引名（索引命名通用做法就是idx_表名_要索引的字段名）这种就是单值索引

另一种频繁查询是根据name，email两个字段查询

select * from user where name = '' and email = '';

为了查询优化，我们可以给name这个字段建索引

create index idx_user_nameEmail on user(name,email);

idx_user_nameEmail就是索引名,这种就是复合索引

• 关联查询太多的Join(设计缺陷或者不得已的需求)
  Join图
  注意：MySQL默认是不支持FULL OUTER JOIN的，如果使用全连接：需要使用union（合并并去重）

全连接（A独有加B独有加AB公有）（图六）= 左连接（全A）union 右连接（全B）
SQL:
select <table_list> from TableA a left join TableB b on a.key = b.key
union
select <table_list> from TableA a right join TableB b on a.key = b.key

全连接（A独有加B独有）（图七）= 左连接（独A）union 右连接（独B）
SQL:
select <table_list> from TableA a left join TableB b on a.key = b.key where b.key is null
union
select <table_list> from TableA a right join TableB b on a.key = b.key where a.key is null

• 服务器调优及各个参数设置（缓冲、线程池数等）

索引

• 什么是索引？
  MySQL官方：索引（index）是帮助MySQL高效获取数据的一种数据结构
  由此可得索引的本质：一种数据结构
  索引的目的：排好序、提高查找效率
  简单理解索引：排好序的快速查找数据结构
  索引会影响到sql语句后的where查询和order by排序
  索引类似于一种指针，每一个指针指向索引后面的每一个数据，结构就是一种B树，并不适合频繁的删改操作，会打乱索引的指向，或使指向失效，需要重建索引

• 索引分类
  

• 基本语法

创建索引语句：

1）：create index 索引名 on 表名（需要建索引的表中的字段名）;
2）：alter table add index 索引名 on (需要建索引的表中的字段名) ;

如果创建的是唯一索引(要加索引的字段的值是唯一的，不重复的，但允许空值)，则要加上unique

1）：create unique index 索引名 on 表名（需要建索引的表中的字段名）;
2）：alter table add unique index 索引名 on (需要建索引的表中的字段名) ;

删除索引语句：

drop index 索引名 on 表名 ;

查看该表索引：

show index from 表名 ;

• 索引结构
  
  主要看BTree索引结构，所有的非叶子节点都不保存具体数据，所有的具体数据只保存在叶子结点，横向扩展，降低深度

• 哪些情况下需要创建索引
  

• 哪些情况下不需要创建索引

为什么经常增删改的表不适合建索引？

从列数据重复性，怎么判断一个列是否要建立索引的必要性？