存储引擎
MySQL 中存在多种存储引擎，比如：
- InnoDB：1.支持事务；2.支持外键；3.同时支持行级别的锁和表级别的锁。适用场景：经常更新的表，存在并发读写或者有事务处理的业务场景。
- MyISAM：1.支持表级别的锁（插入更新操作会锁表）；2.不支持事务；3.拥有较高的插入和查询速度。适用场景：只读类的数据分析的业务场景。
- MEMORY：所有数据存储在内存中，读写速度快；数据库重启，数据会消失。适用场景：临时表。
如何选择合适的存储引擎？
- 数据一致性要求较高，需要事务，选择 InnoDB
- 数据查询多，更新少，对查询性能要求较高，选择 MyISAM
- 用于查询的临时表，选择 MEMORY
索引
InnoDB 中有 3 中索引类型：
- 普通索引，没有任何限制
- 唯一索引，要求键值不能重复，
主键索引是特殊的唯一索引，主键索引还要求键值不能为空 - 全文索引，大文本的场景下，可以用全文索引解决 like 查询效率低的问题，全文索引只有 char、varchar、text 类型的字段才能创建
create table t_user(
id bigint(13) not null,
name varchar(20),
remark varchar(200),
primary key(id),
fulltext index(remark) -- 创建全文索引
);
select * from t_user where match(remark) against('xkcoding' IN NATURAL LANGUAGE MODE); -- 使用全文索引
索引使用原则
-
联合索引最左匹配原则：建立联合索引时，把最常用的字段放在最左边。假设
index(a,b)建立了 a 和 b 的联合索引，查询条件where a = ? and b = ?和where a = ?都可以匹配该索引，而where b = ?这样子就无法使用当前索引了。 -
覆盖索引：非主键索引会先通过索引查询到主键索引，再通过主键索引找到数据，比单独的主键索引的查询多扫描了一颗索引树，这个过程叫做回表。
如果需要查询的字段从索引中直接可以获取，不需要回表操作，那么此时就是覆盖索引。
假设创建
index(a,b)我们查询的时候select b from t where a = ?，这就用到了覆盖索引。需要注意的是select *的操作用不到覆盖索引。
InnoDB 锁
锁的模式
InnoDB 支持表级别的锁，也支持行级别的锁。锁的粒度，表锁大于行锁；加锁的效率，表锁高于行锁；锁的并发性能，行锁优于表锁。
共享锁
共享锁是个行级别的锁，主要用于读取数据的场景，也叫读锁，不要在获取了读锁之后去写数据，可能会出现死锁的情况，多个事务可以共享一把读锁。
添加读锁：
select * from t_user lock in share mode;
排他锁
排他锁是个行级别的锁，主要用于操作数据的场景，也叫写锁。只要一个事务获取了一行数据的写锁，那么其他事务就不能再获取这一行数据的读锁和写锁了。
添加排他锁：
① 数据库会默认为增删改操作加上排他锁
② 使用 FOR UPDATE 语法
select * from t_user for update;
行锁的原理
InnoDB 的行锁，是通过锁住索引来实现的。如果一个表没有创建索引怎么办？如果定义了主键，InnoDB 会选择主键作为聚集索引。
优化
sql 优化要从几个方面考虑：sql 语句优化、架构优化、存储引擎优化。
SQL 语句优化
看执行计划
定位到慢 sql 后，通过 explain 分析 sql 执行计划。
explain 得到的结果，如果存在多条记录，执行顺序会按照 id 的大小进行排序，id 越大的会越先执行。MySQL会优先选择数据量小的表作为驱动表优先执行。id 相同时第一条记录代表先执行，id不同时，可能是因为子查询，有子查询时会先执行子查询，在查询外层。
执行计划需要重点关注 type、rows、filtered、extra 字段。
type 字段表示连接类型，包括：system > const > eq_ref > ref > range > index > all，一般来说，至少需要达到 range 级别，尽量保证 ref 级别，ALL 和 index 都是需要进行优化的。
- ALL 全表扫描
- index 索引全扫描
- range 索引范围扫描，常用语<,<=,>=,between,in等操作
- ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
- eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
- const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
Extra：
- Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
- Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化。比如使用了 union。
- Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
- Using index condition：MySQL5.6 之后新增的 ICP，using index condtion 就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
优化场景
1. 不要使用 select *
首先，只返回需要的列能提高效率，且避免数据库解析 * 带来的时间消耗；其次使用 * 的情况下，可能导致索引失效，比如 where 条件是大于，或者 in 等范围条件的时候，如果查询的列只有带索引的列时，执行计划会走索引，否则全表扫描。
2. 建索引
索引创建原则
- 在用于 where 判断 order by 排序的字段创建索引
- 在 join 的 on 字段创建索引
- 索引的个数不要过多，浪费空间，过多的索引还会导致更新变慢
- 在离散度高的字段创建索引
- 频繁更新的值，不要作为主键或者索引
- 联合索引最好将离散度高的字段放在前面，最左匹配原则
- 创建联合索引，而不是去修改单列索引
- 不建议使用无序的值作为索引
- 字段较长时，可以通过截取字段的前一部分来创建索引，具体截取多少，可以通过计算离散度，找到最合适的长度
索引失效场景
- 索引字段使用函数(
replace/SUBSTR/CONCAT/sum/count/avg)、表达式、或者计算(+ - * /) - 隐式转换，比如字符串类型的字段未加单引号
- like 条件出现
%在左，like '%xxx' - 反向查询，比如 NOT LIKE 则使用不到索引，但是
<>、NOT IN在某些情况下可以使用到索引
3. 少用 union，尽量用 union all
union 去重是基于临时表，临时表的特性是如果在 InnoDB 上设置的缓存够大则使用内存来完成临时表的高效处理；如果union 的结果特别大，超出了内存能够承载的范围，MySQL 会自动创建 MyISAM 引擎表，MyISAM 表是在磁盘上处理的， IO 效率变差，造成 sql 执行效率低。如果一定要用 union，可以加 limit 限制结果集数量。
4. 大分页
数据量极大的情况下，分页尽量减少偏移量。比如：
-- 大偏移量
select * from t_user limit 900000,10;
-- 修改为先过滤，再 limit
select * from t_user where id >= 900000 limit 10;
5. 选择合适的字符类型
6. 关联查询（NLJ）
NLJ（Nest Loop Join）嵌套循环联接与编程中的二层嵌套类似。外表（驱动表）中的每一条记录与内表（被驱动表）中的记录进行比较判断。
- 小表驱动大表：联接查询的执行计划，按上下顺序，第一个是驱动表。驱动表的大小直接决定关联查询时的效率。
- 外键要加索引：多表关联查询时，只有在外键上也加索引，关联表的索引才能生效。通过建索引能解决大部分的关联查询的效率问题，只要做好单表的数据查询优化，在主外键关联都建索引的情况下，MySQL 优化器会自动选择最优的驱动表，这样执行效率就不会低。如果关联表太多，驱动表的选择可能会有问题，所以一些大厂会建议关联查询的表最好不要超过3个。
存储引擎优化
- 为不同的业务选择合适的存储引擎，查询插入操作多的业务选择 MyISAM，临时数据选择 MEMORY，并发大更新操作多的业务选择 InnoDB。
- 尽量选择合适的数据类型和数据长度。
- 非空字段定义为 NOT NULL，通过提供默认值、特殊值的方式，代替 NULL，减少 NULL 类型的存储。
- 不要使用外键。
- 尽量不使用触发器、视图。
- 不要使用数据库存储二进制文件或者大文件存储，尽量使用文件服务器解决，数据库只存储文件的相对路径。
架构优化
- 引入缓存服务，如 Redis，降低查询对数据库带来的压力。
- 分库分表，垂直分库减少并发压力，水平分表解决存储瓶颈。垂直分库是按照业务拆分成不同的数据库。水平分库分表是按照一定规则分不到不同的数据库中。
- 使用主从复制，从而实现读写分离，可以一定程度减轻数据库访问压力。但是需要注意主从数据一致性问题。
面试题
如何快速往 MySQL 中插入 100W 行的数据？
先建立一张存储引擎为 MyISAM 的表，往里插入数据，插入完成之后，修改存储引擎为 InnoDB。
如何避免死锁的情况
标签：存储,使用,查询,索引,调优,InnoDB,MySQL,主键 From： https://www.cnblogs.com/cloudrich/p/17449715.html
- 批量操作单表时，先对数据进行排序（避免出现等待环路）
- 申请足够级别的锁，如果操作数据，申请排他锁
- 尽量使用索引访问数据，避免锁表
- 尽量拆分大事务为小事务
- 尽量使用等值查询，减少范围查询