MySQL学习记录(四）

之前单独地学习过MySQL，项目中又有讲，作为对之前笔记的补充

Select *from
Join on
Where
Group By
Having
Order by
Limit

From
Join on
Where
Group by
Having
Select
Order by
Limit

FROM ON
JOIN WHERE
GROUP BY    HAVING 
SELECT   ORDER BY
LIMIT

左连接、右连接、内连接--LEFT、RIGHT、INNER
------可以延伸出七种JOIN-----加上where及and、or字段
FULL OUTER JOIN -- UNION ALL

资料补充：MySQL 索引及查询优化

1、索引

1、什么是索引？

MySQL索引

索引是一种帮助MySQL 高效获取数据的数据结构。
----是一种为了提高数据获取速度的数据结构
----主键一定是唯一性索引，唯一性索引并不一定就是主键。

创建每张表的时候都有个.MYI文件生成
索引一般都是指B树(多路搜索树，不一定是二叉树)结构组织的索引---MySQL的索引结构都是B+树
				---除了B+Tree索引，还有Hash索引、full-text全文索引、R-Tree索引
B-Tree---多路平衡查找树-----对应一个数据表，一共两列数据  id及值，最左边是数据记录的物理地址--查找速度快

索引类型：单值索引、唯一索引(该字段数据不可重复)、复合索引(建立在多个字段上)
				--ALTER TABLE ..... ADD UNIQUE INDEX .....USING BTREE;

2、MySQL索引

索引在存储引擎层实现的，不是在服务器层实现的。不同的存储引擎具有不同的索引类型和实现。

1、B+Tree索引

是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

2、哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
1、无法用于排序与分组；
2、只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

3、全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4、空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。
空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

3、索引的优点及使用条件

索引的优点:

1、大大减少了服务器需要扫描的数据行数。
2、帮助服务器避免进行排序和分组，以及避免创建临时表
	（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。
	临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
3、将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的使用条件：

1、对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
2、对于中到大型的表，索引就非常有效；
3、但是对于特大型的表，建立和维护索引的代价将会随之增长。
	这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

2、Explain关键字：

将SQL语句执行过程中MySQL引擎的执行参数陈列出来
---可以模拟执行SQL语句，通过模拟执行的参数，知道MySQL怎么处理SQL语句，
	分析查询语句或表结构的性能瓶颈，进一步优化SQL。
用法：在SQL语句前加上EXPLAIN关键字

作用：---可以得知以下信息：
	表的读取顺序
	数据读取操作的操作类型
	哪些索引可以使用
	哪些索引被实际使用
	表之间的引用
	每张表有多少行被优化器查询

EXPLAIN得到的参数概念：

id：
	--EXPLAIN中的标识，表示select查询的序列号，包含一组数字，表示查询顺序
	--说明每个对象(表)的执行顺序，id越大执行越早，id越小执行越晚，id一样的按照从前到后的顺序执行
select_type:
	--是指查询方式的类型、查询类型
	---主查询PRIMARY、子查询SUBQUERY、简单查询SIMPLE(单表--无伤大雅)、
		联合查询UNION(union all)、衍生表查询DERIVED(假表、虚拟表、衍生表)
table:
	--查询所涉及到的表，真实存在的表或者衍生表
type:
	--索引使用情况的类型--说本次查询所使用到的索引类型，如果没有用到索引则为ALL
	--速度优劣由最好到最差：
		system(系统常量，写死在程序中--const特例)
		>const(记录在表中的常量，从表中匹配一下)
		>eq_ref(关联别的表--唯一性索引扫描，对每个索引键，表中只有一条记录与之匹配--主键)
		（前三个要求过高，过于特殊）
		>ref(索引不是单独用的，关联别的表--有一个表提供数据----非唯一性索引扫描，返回匹配每个单独值的所有行)
		>range(范围索引，使用索引树上的范围)
		>index(查询的东西正好在索引上，也是读全表，但是index是从索引中读取的)
		（尽量到达上面三种级别，加快速度）
		>ALL (没有用到索引，遍历全表找到匹配的行)

		 
possible_keys:
	--在多表关联时，可能用到的索引(一般是两张表的所有索引）
	--可能应用到这张表中的索引，一个或者多个，查询到的索引不一定被查询实际使用。
key:
	--实际使用的索引。如果为NULL，则没有使用索引
key_len:
	--被用到的索引的长度
	--所使用的的索引的果实越短，查询越快
	----int:4、Varchar：3字节(utf-8)*20(字段长度)+1(null)+2(varchar的存储长度)=63
ref:
	--两张表在索引关联查询时所关联的索引字段是什么---两表联合查询联合索引是什么
	--type为ref时，这里的显示所关联的表的名字
rows:
	--查询字段表中的数据数量
	--根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数
Extra:
	--包含不适合在其他列中显示但是重要的额外信息
	--using filesort文件内排序(检索到的结果不符合order by)及using temporary(临时空间)尽量不要出现，出现说明效率低

3、索引优化（几十万条以下都差不多）

原则：—避免索引失效

1、全值匹配我最爱—用上所有的组合索引—key_len优先级低于ref
–全值匹配我最爱
–最左前缀要遵守
2、最佳左前缀法则–如果索引了多列，查询从索引的最左前列开始并且不跳过索引中的列。
–带头大哥不能死，中间兄弟不能断
3、不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效转向全表扫描。
–索引列上少计算
4、存储引擎不能使用索引中范围条件右边的列。
–范围之后全失效
5、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
– 覆盖索引不写 *
6、mysql在使用不等于(!=、>、<）的时候有时候无法使用索引会导致全表扫描。
7、null及not null也尽量不要有。
8、like以通配符开头(%…),导致mysql索引失效会变成全表扫描的操作。----匹配时左侧尽量不要模糊
–like百分写最右
9、字符串不加单引号也会导致索引失效。
–VAR引号不可丢
10、少用or，用它连接会导致索引失效。
–不等空值还有OR

如何注意索引优化？

1、SQL语句的优化，充分利用索引
2、创建数据结构时，要尽量考虑到索引的使用，
	复合索引在建立和使用时，尽量考虑在用户应用查询时，常用的排序方向和字段组合顺序。

4、数据库函数（用户数据库函数导入大的测试数据–上百万级别）

plsql---首创于oracle的一种扩展sql语言，PL/SQL是Oracle数据库对SQL语句的扩展。---SQL的程序化语言
	--运行在oracle内部的一个类似js脚本的语言
	--有返回值的叫函数；
	--没有返回值的叫存储过程(执行一些列的复杂的数据库操作，如自动生成表数据，如对多张表的关联增删改查)

设置参数log_bin_trust_function_creators为true
-------其实就是在Navicat中写函数，表示各种
新建函数----------------随机生成字符串函数、生成随机数字-部门编号
新建存储过程----------插入部门、调用函数—生成部门、员工参数(部门编号起始值，最大值)

5、数据库锁（如何防止库存超卖）

如何解决库存超卖现象？

在高并发的情况下，多个人同时抢购同一库存时，由于数据库的读写操作可以并行执行的原因，会导致修改库存时，库存不足出现超卖现象。
---redis解决：分布式锁、lua脚本、Redisson
---mysql解决：用锁将查询库存的操作和写入库存的操作互斥
	(1)悲观锁解决库存问题：
		在select时，将select语句加入一个行锁，与更新库存是语句互斥，可以保证在查询库存时库存不被修改。
	(2)乐观锁解决库存问题：
		在select时，加入一个版本字段，每次更新，同时查询和更新版本字段，如果版本字段发生变化，则SQL语句不会执行成功。
		Select kc,version from sku_info where sku_id = ?----查询两个字段
		Update sku_info set kc = kc - 1, version = version + 1 where sku_id = ? and version = version

MyISAM–查询用—不存在事务—基本已经淘汰
InnoDB数据库引擎–事务型–支持行锁、表锁----三种锁指行锁、表锁、页锁

表锁

在偏读型数据库中使用表锁----如MyISAM
–很少用表锁–安全–

Lock table 表名
Unlock tables

写锁和读锁阻塞（读写互斥），读与读共享，写和写阻塞（写写互斥）

行锁

------行锁支持事务----没有行锁就没有事务
------没开启事务就是默认提交—开启事务后提交需要手动commit

------行锁可以让读读互斥，可以避免在查询到库存时，库存已经被修改的情况发生
-----update自带行锁—事务

-----在无索引操作时，注意锁的升级(行锁升级表锁)

--- 如何锁定一行？--加上一个行锁
-----查询加上行锁for update ,避免查询和更新同时发生，导致读到的库存数量发生不可重复读的错误
set autocommit = 0;
select kc from product where productname = '电脑' for update
update product set kc = kc - 1 where productname = '电脑'
commit

间隙锁

当使用范围条件不是相等条件检索数据时，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。
对于键值在条件范围内但是并不存在的记录叫作“间隙”(GAP)
InnoDB也会对这个间隙加锁–这种锁机制就是间隙锁(Next-Key锁)

可能会锁定一个并不存在的记录。
可能也会导致锁的升级，危害别的数据。—因此查询时尽量不使用范围条件检索

页锁

开销和加锁时间介于表锁和行锁之间，会出现死锁；
锁定粒度介于表锁和行锁之间，并发度一般。

数据库的死锁
----多线程死锁

形成了一种因果关系，死循环。—彼此占用了对方的解锁的钥匙