谷粒商城项目学习笔记：MySQL

数据库 MySQL

创建时间:2020-03-28 01:16

字数:2.8k 阅读:

介绍：
复习：

索引

MySQL索引方法
Explain

Mysql锁

表锁
行锁
案例分析：
事务（Transaction）
死锁

¶介绍：

索引优化（海量数据查询）
数据库函数（用户数据库导入大量测试数据）
数据库锁（如何防止数据库超卖）

¶复习：

# 手写顺序
select * from 
where
join on
where
group by
having
order by
limit
# 机器执行顺序
from
join on
where
group by
having
select
order by
limit

order by浪费性能

¶索引

定义：索引（Index）是帮助mysql高效获取数据的重要数据结构。
索引可分为：单值索引、复合索引（建立在多个字段上的索引）、唯一索引（指该字段的数据不可重复）
```
ALTER TABLE 'classes' ADD UNIQUE INDEX 'c'('id','classNo','classname') USING BTREE;
```

¶MySQL索引方法

B-Tree

B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）
B-Tree在MyISAM里的形式和Innodb稍有不同：
MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址
InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录
Hash索引

1.仅支持"=",“IN"和”<=>"精确查询，不能使用范围查询：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash
2.不支持排序：
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
3.在任何时候都不能避免表扫描：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
4.检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
5.只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引
R-Tree

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

¶Explain

使用explain关键字可以模拟【优化器】执行SQL查询语句，通过模拟执行的参数，知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或者是表结构性能的瓶颈。

使用：explain+SQL语句

Explain的性能指标参数：

id	说明每个表（对象）的执行顺序，id越大执行越早，id越小执行越晚，id相同按照顺序从前到后执行
select_type	查询类型 1. 简单查询；2.主查询；3.子查询；4.衍生查询； 5.联合查询；
type	显示了查询使用何种索引类型从好到差依次为：system > const > eq_ref > ref > range > index > ALL 简单查询一般都用到ALL，select * from XX，即没有用到索引 system (表中只有一条记录，即东西已经查出来了,内存中再查一次而已) ：select id from (select id from table where id = 1) t const（常量）：select * from table where id =1; eq_ref（唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描） ref（非唯一索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，可能会找到多个符合条件的行） range（索引树上小于或大于某个范围的索引） index（读全表，与all的区别在于只读索引内容）select id from t; all 实际开发中，复杂查询优化到ref、range、index级别较为常见
possible key	多表关联查询时，可能用到的索引（一般是两张表的所有索引）
key	多表关联查询时，实际用到的索引
key_len	被用到索引的长度： int（4字节）、varchar（utf-8字符占三个字节*字符数+1(null)+2（varchar的存储长度））
ref	显示哪一索引被用到了，如果可能的话，是一个常数。哪些列或常量被用于查找索引树上的值
rows	根据表统计信息及索引的使用情况

复合索引在建立和使用时，尽量考虑用户在应用查询时，常用的排序方向和字段的组合顺序

如何防止索引失效，提高表单查询效率？：将表索引的建立与业务应用时经常用到的排序和过滤规则结合到一起综合考虑

防止索引失效：

复合索引，全匹配：
最佳左前缀法则：带头大哥不能死（最左索引不可无），中间兄弟不能断（从左到右不能跳过某一索引）
不在索引上做任何操作（计算，函数，类型转换）
存储引擎的范围条件不能使用复合索引右边的列：
尽量使用覆盖索引（只访问索引的查询（索引列与查询列一致）），减少select *
mysql中使用不等于（<>或者!=）的时候有时会导致无法使用索引进一步造成全表扫描
注意null/not null对性能的影响

like后的字段不能以通配字符（%）开头

select * from user_tb where name like "小王%"  # type：range
select * from user_tb where name like "%小王%" # type: ALL

字符串(var)不加单引号索引失效

select * from user_tb where name = 111	# type: ALL
select * from user_tb where name='111'	# type: ref, ref: const

小总结：

全职匹配我最爱，最左前缀要遵循。带头大哥不能死，中间兄弟不能断。索引列上少计算，范围之后全失效。LIKE百分写最右，覆盖索引不写 * 。不等空值还有OR，影响索引需谨记。VAR引号不可丢，SQL优化有诀窍。

¶Mysql锁

库存超卖问题：在高并发的情况下，多人同时抢购一个库存，由于数据库读写操作可并行执行，会导致在修改库存时库存不足超卖的情况（redis应对策略：redission、lua脚本、分布式锁）

用锁将查库存和写库存的操作互斥。

悲观锁解决库存问题，在select时加入一个行锁，与更新库存的语句互斥，可保证在查询库存时库存不被修改。

乐观锁解决库存问题：在select时加入一个版本字段，每次更新，同时查询和更新版本字段，如果版本字段发生变化，则sql语句不会执行成功

select kucun,version from sku_info where sku_id=?
update sku_info set kc=kc-1,version=version+1 where sku_id=? and version=version

InnoDB区别于MyISAM： 1. 支持事务、2. 采用行锁机制

¶表锁

在偏读型数据库中使用表锁，例如myisam
特点：开销小，加锁快；无死锁；锁的粒度大；发生锁冲突的概率最高，并发粒度最低
对于MyISAM表进行操作：读锁会阻塞写（只有当读锁释放后，才会执行其他进程的写操作），但是不会阻塞读；写锁堵塞读与写（只有当写锁释放后，才能进行其他进程的读写操作）
```
lock table table_name read; #加读锁
lock table table_name write; #加写锁
unlock tables;	#释放锁
```

¶行锁

偏向InnoDB存储引擎。
特点：开销大，加锁慢；会出现死锁；锁的粒度小，发生锁冲突的概率低，并发粒度最高

行锁：

update操作默认自带行锁：

set autocommit = 0;	#不自动提交
update table_name set field_name='a' where id=1	#默认写互斥，由于不自动提交，锁一直未释放

update table_name set field_name='b' where id=1	#另开一个进程去写，将被阻塞

select操作默认无行锁：

# 进程1
set autocommit=0;
select kucun from product where product_name='电脑'
update product set kucun=kucun-1 where product_name='电脑'
commit;

# 进程2
set autocommit=0;
update product set kucun=kucun-1 where product_name='电脑'
commit;

未采用行锁：进程1先执行前两条语句，进程2做多次更新，进程1里我们单独再执行第二条语句，发现库存仍未变化。等进程1提交后才能发现已经超卖了。

采用行锁：

# 进程1
set autocommit=0;
select kucun from product where product_name='电脑' for update;
update product set kucun=kucun-1 where product_name='电脑'
commit;

如上：查询时加行锁，避免查询和更新同时发生，导致读到的库存数量发生不可重复读的错误。

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

¶案例分析：

无索引行锁升级为表锁

在无索引操作时，注意锁的升级（行锁升级为表锁）
间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙（GAP）
InnoDB也会为间隙加锁，这种机制就是所为间隙锁（Next-Key锁）

危害：Query执行过程中通过范围查找的话，它会锁定整个范围内的所有索引值，即使这个键值并不存在。

间隙锁害处在于当锁定一个范围键值后，某些并不实际存在的键值也会被无辜地锁定，从而造成在锁定的时候无法插入锁定键值范围内的任何数据。某些场景下这回造成很大的危害