Day7.存储引擎&7种Join&Sql执行顺序&索引 -Mysql高级

存储引擎

show engines;查看MySQL5.7支持的存储引擎。

SHOW VARIABLES LIKE 'DEFAULT_storage_engine%';查看当前数据库正在使用的存储引擎。

InnoDB与MyISAM对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整张表，不适合高并发操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

SQL性能下降的原因

• 查询语句写的烂；
• 索引失效：索引建了，但是没用上；
• 关联查询太多join（设计缺陷或者不得已的需求）；
• 服务器调优以及各个参数的设置（缓冲/线程数等）；

SQL执行顺序

select 		#5
	...
from 		#1
	...
where		#2
	...
group by	#3
	...
having		#4
	...
order by	#6
	...
limit		#7

	[offset]

7种JOIN理论

/* 1 */
SELECT * FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;

/* 2 */
SELECT * FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

/* 3 */
SELECT * FROM TableA A INNER JOIN TableB B ON A.Key=B.Key;

/* 4 */
SELECT * FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT * FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.Key=B.Key;
/* MYSQL不支持FULL OUTER JOIN语法，可以改成1+2 */
SELECT * FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key
UNION
SELECT * FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;


/* 7 */
SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.Key=B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
/* MYSQL不支持FULL OUTER JOIN语法，可以改成4+5 */
SELECT * FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key WHERE B.Key IS NULL
UNION
SELECT * FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key WHERE A.Key IS NULL;

索引

索引简介

索引是什么？

MySQL官方对索引的定义为：索引(INDEX)是帮助MySQL高效获取数据的数据结果。

从而可以获得索引的本质：索引是排好序的快速查找数据结构。

索引的目的在于提高查找效率，可以类比字典的目录。如果要查找mysql这个单词，我们肯定要先定位到m字母上，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a--z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？

重点：索引会影响到MySQL查找(Where的查询条件)和排序(ORDER BY)两大功能！

除了数据本身外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

# Linux下查看次磁盘空间命令 df -h
[root@03 ~]# df -h
文件系统        容量  已用  可用 已用% 挂载点
devtmpfs        476M     0  476M    0% /dev
tmpfs           487M     0  487M    0% /dev/shm
tmpfs           487M  7.7M  479M    2% /run
tmpfs           487M     0  487M    0% /sys/fs/cgroup
/dev/sda3        18G  3.1G   15G   18% /
/dev/sda1       197M  110M   88M   56% /boot
tmpfs            98M     0   98M    0% /run/user/0

我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖所有，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引(Hash Index)等

索引的优势和劣势

优势：

• 查找：类似于大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本；
• 排序：通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

劣势：

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询速度，打死你同时会降低表的更新速度，例如对表频繁的进行INSERT UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但是允许空值。
• 复合索引：一个索引包含多个字段。

建议：一张表建的索引最好不要超过5个！

/* 基本语法 */

/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));

/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

使用ALTER命令来为数据表添加索引

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

MySQL索引数据结构
索引数据结构：

• BTree索引；
• Hash索引；
• Full-text全文索引；
• R-Tree索引；

BTree索引检索原理：

哪些情况需要建立索引

• 主键自动建立主键索引（唯一+非空）
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段（group by也和索引有关）

哪些情况不需要建立索引

• 记录太少的表
• 经常增删改的表
• 频繁更新的字段不适合创建索引
• Where条件中用不到的字段不创建索引
• 假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

参考：https://github.com/RingoTangs/LearningNote/blob/master/MySQL/MySQL.md