2020-6-22-MySQL高级

数据库引擎对比、索引、SQL语句的性能分析、优化、其他

数据库引擎对比

	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁	行锁
缓存	只缓存索引	索引、数据都缓存
表空间	小	大
关注点	性能	事务

---

索引

1索引分类

单值索引：一个索引只包含单个列

复合索引：一个索引包含多个列

唯一索引：索引值唯一，单允许有空值

2索引相关操作

1）创建

create index index_name on table_name(column);

2）删除

drop index index_name on table_name;

3）查看

show index from table_name;

3创建索引适合的情况和不适合的情况

1）适合情况

(1)表的主键，创建表后会自动创建索引

(2)频繁作为查询条件的字段

(3)与其他表相关联的字段，如外键所在的字段

2）不适合情况

索引能时查询速度和排序速度增快，但也会使更新速度变慢，以下情况不适合创建索引

(1)频繁更新的字段

(2)where后的条件用不到的字段

(3)数据量小的表

(4)重复项较多的字段，比如性别

3索引下推

一张表有一个联合索引(name,age)，执行以下语句

select * from user where name like "zhang%" and age<=10;

对于这个语句有两种执行方式

1）联合索引查询所有满足“zhang”开头的索引，再回表中查询出满足年龄小于等于10的数据

2）联合索引查询所有满足“zhang”开头的索引，再继续筛选出年龄小于等于10的索引，最后再返回表中查询全行数据（这种方式称为索引下推）

Mysql默认开启索引下推

---

SQL语句的性能分析

1）查看SQL语句的性能

explain SQL

2）查询性能结果中的字段含义分析

字段	可能出现值	含义
id	数字	指向顺序，相同表示从上至下执行，不同表示从下到上执行(数值大的先执行)
select_type	simple,primary,devived,union,union_result	查询的类型
type	扫描类型值	扫描类型，与性能相关
possible_keys	索引名	可能涉及到的索引，查询涉及到的字段上若存在索引，则索引被列出，但查询过程中不一定使用
key	索引名	实际使用的索引，若为null表示未使用索引
key_len	数字	索引的大小
rows	数字	找到记录所读取的行数
extra	useing filesort,using temporarymusing index,coving index	额外信息

3）type对应扫描类型值对应含义

扫描类型	含义
const	索引扫描，一次找到
eq-ref	唯一索引扫描，对于每个索引键，表中有唯一一条记录与之匹配
ref	非唯一索引扫描，返回某个单独值的所有行
range	给定范围扫描
index	所有扫描
all	全表扫描

一般来说，该值达到range或ref级别即可

---

优化

1Mysql性能达瓶颈原因

1）cpu饱和

2）需装入的数据远远大于内存容量

3）硬件性能瓶颈

2Mysql优化建议

1）对于复合索引，想让它生效，在语句WHERE后条件的第一个字段需用到复合索引的第一个字段

2）对于复合索引，想让它效率更高，在语句WHERE后条件中的字段不跳过过索引中的列

3）查询条件中对应的索引列，不要做任何操作如计算cacl等

4）对于复合索引，在语句WHERE后条件中有范围的条件，之后其他关联索引的字段全部失效

5）查询内容尽量覆盖索引，减少使用select *（导致索引失效，占用过多sort_buffer，效率降低）

6）不用不等于，会导致全表扫描

7）查询null或not null，无法使用索引

8）like后的%，尽量加到右侧，否则导致索引失效（利用覆盖索引可解决）

9）在vachar类型字段作为查询条件时，一定要加上引号，否则可能导致索引失效，同时也可能造成行锁变成表锁

10）少用or，连接时导致索引失效

3项目中优化SQL思路

1）慢查询的开启与捕获

2）explain慢SQL分析

3）show profile查询SQL在myql服务器里执行细节和生命周期情况

4）SQL数据库服务器的参数优化

---

常见的锁

1共享锁

读锁，可查看但无法修改和删除的数据锁。若事务对数据加上共享锁后，其他事务只能对该数据加共享锁

2排他锁

写锁、独占锁。若事务对数据对象加上该锁，其他数据不能对该数据再加任何类型的锁，直至该事务释放该锁

3互斥锁

编程中引入的概念，来保证共享数据操作的完整性，每个锁都有一个互斥锁的标记，以保证任一时刻，只有一个线程访问该对象

4悲观锁

假设最坏的情况，每次在拿到数据的时候都会上锁。只有拿到锁后才可对数据进行操作，如传统关系性数据库用到的行锁、表锁，或如java里的同步原语sychronized关键字的实现

5乐观锁

每次拿数据时认为别人不会修改，所以不会上锁。但更新时会判断此期间别人有没有去更新数据，如版本号机制实现。乐观锁适用于多读的应用类型，像数据库提供的类似于write_condition机制或如java.util.conCurrent.atomic包下的原子变量类就是乐观锁的一种实现方式（cas）

6行级锁

Mysql中粒度最小的一种锁，只针对当前行进行加锁。分为共享锁和排他锁。优点是冲突少，并发高；缺点是开销大，会出现死锁

7表级锁

Mysql中粒度最大的一种锁，实现简单，资源消耗小。分为共享锁和独占写锁。优点是开销小，不会出现死锁；缺点是发生冲突概率高

8页级锁

粒度介于行级锁和表级锁之间，会出现死锁

---

其他

1）exists查询

主查询的数据，放到子查询中做条件验证，根据验证结果（true，false）决定查询结果是否保留（小表驱动大表）

select * from A where exists(select 1 from B where B.id=A.id);

相当于

select * from A where id in (select id from B);

2）Mysql排序方式

FileSort和Index，其中Index效率更高。当索引失效时，会用FileSort进行排序

3）间隙锁

SQL查询条件是一个范围时，InnoDb会给满足这一条件的数据记录加锁，这些在条件范围内不存在的记录称为间隙，而此时形成的锁称为间隙锁

4）锁住一行

select * from table where id=9 for update;