MySQL学习(一):存储引擎,锁,索引,常见查询问题及其优化

拼搏现实的明天。 2022-02-20 10:47 147阅读 0赞

### 1、MySQL 相关存储引擎 ###

MySQL的存储引擎有很多种,其中常用的有两种,即MyIsAM和InnoDB,在这二者之间,又属InnoDB最为常用.

#### 1.1MyIsAM ####

MyIsAM：不支持事务，不支持外键约束，索引文件和数据文件分开，这样在内存里可以缓存更多的索引，对查询的性能会更好，适用于那种少量的插入，大量查询的场景。

报表系统,是最适合 MySQL 的 MyIsAM 存储引擎的，不需要事务，就是一次性批量导入，接下来一天之内就是纯查询了。

#### 1.3 InnoDB ####

现在一般用 MySQL 都是 InnoDB，很少用其他的存储引擎，而且国内用其他存储引擎的场景和公司也不多，所以用 InnoDB就可以了，而且这个也是 MySQL 5.5 之后的默认官方存储引擎。

主要特点就是支持事务，走聚簇索引，强制有主键，支持外键约束，并且高并发、大数据量、高可用等有相关成熟的数据库架构，分库分表、读写分离、主备切换，全部都可以基于 InnoDB存储引擎的.

### 2.MySQL 锁 ###

#### 2.1 锁的作用 ####

当多个线程对数据库进行并发读写操作的时候，可能会带来数据不一致的问题。锁主要就是在并发环境下保证数据库的一致性。

**从应用角度来说，锁分为：悲观锁，乐观锁  
从锁控制范围来说，锁分为：表级锁，行级锁  
从互斥性角度来说，锁分为：共享锁，排他锁**

从这些名词上都可以看出来，MySQL 的锁机制是一个比较难理解的知识点。

#### 2.2 乐观锁 ####

乐观锁的特点是先进行具体的业务功能实操作，等到业务功能执行完毕， 需要实际更新数据的时候再最后一步拿一下锁。

乐观锁实现完全是应用层面自己的事情，不需要数据库提供特殊的支持。 比较常见的做法，就是在需要锁的数据上增加一个版本号，然后当运行完业务功能之后， 提交结果的时候对比版本号是否发生变化。

> update set stock=stock-1,version = new\_version where varsion=old\_version

#### 2.3 悲观锁 ####

悲观锁特点是必须先获取锁，再进行业务操作，一般来说在数据库上的悲观锁都是数据库本身提供的能力。 例如我们用： `select….for update` 这个语句操作就可以实现悲观锁。 当数据库执行 `select for update` 时会获取被 select 中的数据行的行锁，因此其他并发执行的 `select for update` 如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。

#### 2.4 表/行级锁 ####

**表锁**直接锁定整张表，锁定期间，其他线程无法对该表进行写操作。锁维护代价比较小，锁的范围比较大，所以锁的冲突概率比较高，并发程度比较差。

**行锁** 对指定的记录行加锁，其它线程无法对该行记录同时进行写操作，但可以对表中其他行记录做任何操作。 行级锁实现代价比较大，加锁效率比较低， 但锁定粒度相对比较小，所以不容易发生锁冲突现象，整体的并发度高。

锁的粒度随着存储引擎的类型不同有所区别：

**InnoDB 同时支持行级锁和表级锁两个类型，并且通过索引列来查询的数据才使用行级索，否则是使用表级锁 MyIsAM 只支持表级锁，不支持行级索**

#### 2.5 共享/排他锁 ####

共享锁（ S 锁）：也叫做读锁，读锁是共享的，多个事务可以同时获取同一个锁，但不允许其他客户修改。

排他锁（ X 锁）：从名字上可以看出不能与其他锁同时存在的锁。如果一个事务得到了某个数据行的排他锁，任何其他事务就不能再获取，必须等待。 也叫做写锁：写锁是排他的，**写锁会阻塞其他的写锁和读锁**。

### 3.MySQL 索引实现原理 ###

#### 3.1索引存储结构 ####

MySQL 的索引就是用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构去找到那一列的值，然后找到对应的行的物理地址即可。

MySQL 的索引实现是基于 B+ 树，这样查找数据的高度就算数据量很大，也很低， [B+ 树原理和实现][B_].

MyIsAM 存储引擎的索引中，每个叶子节点的 data 存放的是数据行的物理地址，比如 0x07 之类的东西，一行一行的，每行对应一个物理地址。

InnoDB 存储引擎的索引实现，跟 MyIsAM 最大的区别在于，InnoDB 的数据文件本身就是个索引文件，就是 key 就是主键，然后叶子节点的 data 就是那个数据行。(只有叶子节点存放具体的数据)

InnoDB 存储引擎，必须有主键，可以默认内置的就会根据主键（6 字节的 rowid）建立一个索引，叫做聚簇索引，InnoDB 的数据文件本身同时也是个索引文件，这个索引就是默认根据主键建立的叫做聚簇索引。

InnoDB 这种原生的数据文件就是索引文件的组织结构，就叫默认的主键索引为聚簇索引。就是因为这个原因，InnoDB 表是要求必须有主键的，但是 MyIsAM 表不要求必须有主键。另外一个是，InnoDB 存储引擎下，如果对某个非主键的字段创建个索引，那么最后那个叶子节点的值就是主键的值，因为可以用主键的值到聚簇索引里根据主键值再次查找到数据。

一般 InnoDB 表里，建议统一用 `auto_increment` 自增值作为主键值，因为这样可以保持聚簇索引直接加记录就可以，如果用那种不是单调递增的主键值，可能会导致 b+ 树分裂后重新组织，会浪费时间。这也就是为啥 InnoDB 下不要用 UUID 生成的超长字符串作为主键？因为这么玩儿会导致所有的索引的 data 都是那个主键值，最终导致索引会变得过大，浪费很多磁盘空间。

#### 3.2 索引使用规则 ####

1.全列匹配  
尽量 where 条件里，根据最左匹配原则，建立联合索引。  
比如 :

> select name,place\_id from car\_parking\_place where car\_id=xxxx and ctime=‘2018-01-10 00：00:00’;

就可以建立 create index (car\_id,ctime).

2.最左前缀匹配  
如果你的 sql 里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列。

3.前缀匹配

如果你不是等值的，比如 =，>=，<= 的操作，是 like 操作，那么必须要是 like ‘XX%’ 这种才可以用上索引。

4.范围列匹配

如果你是范围查询，比如 >=，<=，between操作，你只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引。

5.禁用函数

目前 mysql 还不支持函数索引，如果你对某个列用了函数，那么那一列不走索引。

#### 3.3 索引的问题及优化方案 ####

针对上面的一些索引规则,产生了一些常见的索引问题,针对这些问题该如何优化呢?  
1.前导模糊查询利用不到索引。

比如:

> select \* from order where extension like ‘%XXX’;

该 SQL 在查询索引字段的时候，由于查询条件开始是模糊的，会导致索引失效，会导致查询全局扫描或者全索引扫描。因此在页面严禁做模糊或者全模糊搜索，如果需要可以通过使用搜索引擎来解决。

2.union、in、or 都能够命中索引，建议使用 in。

select * from order where id = 1 
    union all
    select * from doc where id = 2;

使用 union 可以命中索引，消耗 CPU 也是最少的，但是一般不这么写 SQL。

select * from order where id in (1, 2);

**in 同样可以命中索引，查询时消耗的 CPU 比 union all 要多一些**，但是通常情况下可以忽略不计，建议使用这种方式

select * from order where id = 1 or id = 2;

在新版的 MySQL 中 or 可以命中索引，但是查询时消耗 CPU 比 in 还要多，因此不建议频繁使用 or。

3.负向条件(含有否定意思的)查询不能使用索引，可以优化为 in 查询。

负向条件包含 ：<>（!=）、not in、 not like、 not exists等  
例如：

select * from order where id not in (1, 2)；

可以优化为 in 查询：

select * from order where id in （3, 4, 5);

4.创建索引时避免以下错误观念

第一、索引多多益善，过多的索引会占用更多的系统空间，而且维护起来难度也会相应增高； 第二、索引宁缺毋滥，认为索引会消耗空间，降低新增和更新的速度； 第三、抵制唯一索引，在应用层面通过“先查后插”来对唯一性进行控制； 第四、**优化索引的时间不正确，过早优化索引，可能会因为不了解系统而优化不完全；过晚的优化索引又可能增加修改索引的工作量。**

索引多多益善和索引宁缺毋滥是使用索引的两种极端表现，还是需要根据具体的情况分析如何使用索引，才能提高数据库的使用效率。

5.超过三个表最好不要 join。

join 字段的数据类型，要求必须一致；  
多表关联查询时，被关联的字段必须有索引。

6.如果明确知道只有一条结果返回，limit 1 能够提高效率。

在知道只有一条结果的时候，我们需要明确的告诉数据库我们只要查这一条结果，让数据库停止继续查询。

select * from order where user_id = ‘XXX’ limit 1；

7.MySQL limit 优化

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

优化:  
当一个查询语句**偏移量offset很大的时候**，如select \* from table limit 10000,10 , 最好不要直接使用limit，**而是先获取到offset的id后，再直接使用limit size来获取数据**,效果会好很多。

如：

select * From customers Where customer_id >=(
    select customer_id From customers Order By customer_id limit 10000,1
    ) limit 10;

8.联合索引最左前缀原则（又叫最左侧查询）

如果在(a,b,c)三个字段上建立联合索引，那么它能够在 a | (a,b) | (a,b,c) 三个地方生效。 例如：

select * from order where order_code = 'XXXX' and state = XX;

建联合索引的时候，区分度最高的字段在最左边。 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a > XX and b = XX的时候，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。 最左侧查询，并不是指 SQL 语句的 where 查询条件顺序要和联合索引顺讯一致。 下面的 SQL 语句也可以命中 (order\_code， state) 这个联合索引。(这是因为MySQL会对语句进行执行前的优化)

select * from order where state = XX and order_code = 'XXXX';

但我们还是建议 where 后查询条件的顺序与联合索引的顺序保持一致。 如果建立了 (a,b) 联合索引，就不必再单独建立 a 索引，如果建立了 (a,b,c) 联合索引，就不必再单独建立 a、(a,b) 索引。

9.范围列可以用到索引（联合索引必须是最左前缀）。

范围列可以命中索引；在联合索引中，如果存在两个或两个以个上范围列，则最多只有一个可以命中索引（命中原则为：最左前缀）。 范围条件包括：>、>=、<=、<、between 等。 例如有 (id，ordercode，enddate) ，那么下面的 SQL 中 id 可以命中索引，而 price 和 from\_date 则使用不到索引。

select  * from order where id < 10300 and order_code = 'XXXX' and end_date between '2019-01-01' and '2019-12-31';

10.把计算放到业务层而不是数据库层。

select * from order where price = (amount * unit_price);

在上面的 SQL中，即使 price 上创建了索引，也会全表扫描。这是因为在字段上计算是不能够命中索引的。 可以把计算放到业务层，这样做既节省数据库的对 CPU 的占用，还可以优化查询缓存。

11.强制类型转换会全表扫描  
如果 varchar 类型在查询的时候不加引号，该值会被强制转成 int 类型，而强转之后的字段不能命中索引。 例如：

select * from order_code = XXXX；   //不能命中索引
    select * from order_code = 'XXXX';    //可以命中索引

12.更新十分频繁、数据区分度不高的字段上不宜建立索引。

MySQL目前主要有以下几种索引方法： B-Tree，Hash，R-Tree。不论用那种索引方法，更新频繁都会大大降低数据库的性能。

一般字段区分度在 80% 以上的时候就可以在该字段上建立索引了，区分度可以使用 count(distinct( 字段名称 )/ count(\*)) 来计算。像是“性别”这种区分度很低的字段，建立索引的意义也是非常小的。

13.建立索引的列，不允许为 null。

a.单列索引无法储 null 值，复合索引无法储全为 null 的值。需要使用 not null 约束以及默认值。 b.查询时，采用 is null 条件时，不能命中索引，只能全表扫描。

索引无法存储 null 值的原因如下： a. 索引是有序的。null 值进入索引时，无法确定其应该放在哪里。 (null 值不能进行比较，无法确定 null 出现在索引树的叶子位置的节点位置。) b. 如果需要把空值存入索引，方法有二：其一，把 null 值转为一个特定的值，在 where 中检索时，用该特定值查找。其二，建立一个复合索引。

14.使用短索引（又叫前缀索引）来优化索引。

前缀索引 对文本的前一部分建立索引，这样可以节约空间，也可以优化查询效率。可以使用 count(distinct left ( 列名, 索引长度 )) / count(\*) 来计算前缀索引的区分度。 其缺点是不能使用 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引，全字段建立索引有时候没必要，就可以根据实际文本区分度决定索引长度即可。 例如：

select * from order where order_code = 'XXXX' and extension = '110108XXXXXXXXXXXX'；

就可以创建索引：(order\_code， extension(6))

15.SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

all：表示全表扫描 index ：扫描顺序是按照索引的顺序的全表全扫，速度非常慢。 consts ：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 ref ：查找条件列使用了索引而且不为主键和 unique。使用普通的索引 (Normal Index) range ：对索引进行范围检索。

--------------------

### 4.MySQL 索引常见面试题 ###

1.MySQL 的存储引擎 MyIsAM 和 InnoDB 的区别。

MyIsAM 适合用于频繁查询的应用。表级锁，适合小数据，没有死锁，不支持事务，不支持外键;  
InnoDB 支持事务，适合插入和更新频繁的场景，支持行锁，当然也支持表锁，适合数据量比较大，并发量比较大的场景，支持外键.

2.数据库优化从几个方面思考？

*  SQL 语句的优化
 *  表结构优化，索引优化
 *  分库分表操作
 *  使用缓存，减少对于数据库的压力

3.经常使用的索引类型有哪些？

普通索引， 联合索引，唯一索引， 主键索引

4.什么情况下，索引无法被使用？

*  以 “%” 开头的 LIKE 语句，模糊匹配
 *  OR 语句前后没有同时使用索引
 *  数据类型出现隐式转化（如 varchar 不加单引号的话可能会自动转换为 int 型）

5.建立索引的原则有哪些？

在最频繁使用的、用以缩小查询范围的字段上建立索引。 在频繁使用的、需要排序的字段上建立索引

6.什么是事务？详细描述其特点

事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。

事务特性：  
ACID  
原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。  
一致性:事务的执行使得数据库从一种正确状态转换成另一种正确状态。  
隔离性:在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务。  
持久性:事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

7.char 和 varchar 的区别是什么？

char(M) 类型的数据列里，每个值都占用 M 个字节，如果某个长度小于 M ， MySQL 就会在它的右边用空格字符补足．  
varchar(M) 类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为 L+1 字节）

--------------------

为方便自己学习,整理自:  
[https://gitbook.cn/books/5c92fd312cc79178471b68db/index.html][https_gitbook.cn_books_5c92fd312cc79178471b68db_index.html]  
[https://gitbook.cn/books/5b84e8628df8920ee5bffca8/index.html][https_gitbook.cn_books_5b84e8628df8920ee5bffca8_index.html]

[B_]: https://www.cnblogs.com/tiancai/p/9024351.html
[https_gitbook.cn_books_5c92fd312cc79178471b68db_index.html]: https://gitbook.cn/books/5c92fd312cc79178471b68db/index.html
[https_gitbook.cn_books_5b84e8628df8920ee5bffca8_index.html]: https://gitbook.cn/books/5b84e8628df8920ee5bffca8/index.html