1 MySQL存储引擎种类及区别, 当前项目用的啥引擎, 为啥?
MySQL的存储引擎有: MyISAM, InnoDB,MEMEORY
常用MyISAM, InnoDB
二者区别:
MyISAM
它是MySQL5.5之前的默认存储引擎
- 不支持事务, 但是整个操作是原子性的
- 不支持外键, 支持表锁(每次锁住的是整张表)
- MyISAM 在磁盘上存储为3个文件,文件名和表名相同,拓展名分别是: .frm(存储表定义), .MYD(MyData存储数据), .MYI(MyIndex存储索引)
- 访问速度快
适用场景
如果对事务的完整性没有要求, 或以 select insert 为主的应用基本都可以选用 MyISAM
InnoDB
它是MySQL5.5之后的默认值存储引擎
- 支持自动增长列(主键自增)
- 外键约束
- 支持事务(提供了具有 提交(commit), 回滚(rollback), 崩溃恢复能力的事务安全)
- 相比较于MyISAM, 写的处理效率差一点, 并且会占用更多的磁盘空间来存储数据和索引
适用场景
如果应用 对事务的完整性有较高的要求, 在并发条件下要求数据的一致性, 数据操作中包含 增删改查 最好使用该引擎;
2 MySQL 四种事务隔离级别, RC是如何解决RU的脏读问题的?
事务(Transcation)是MySQL等关系型数据库区别于 NoSQL 的重要一面, 是保证数据一致性的重要手段;
事务是访问和更新数据库的程序执行单元, 事务中可能包含一个或多个SQL语句, 这些语句要么都执行, 要么都不执行. 具有原子性(Atomicity) 一致性(Consistency) 隔离性(Isolation) 持久性(Durability) 是衡量事务的四个维度;
-
原子性
语句要么全部执行, 要么全不执行, 是事务最核心的特性, 事物本身就是以原子性来定义的,
-
一致性
事务追求的最终目标, 一致性的实现既需要数据库层面的保障,也需要应用层面的保障;
-
隔离性
保证事务执行尽可能不受其他事务影响;
-
持久性
保证食物提交后不会因为宕机等问题导致数据丢失;
MySQL事务隔离级别
SQL标准中定义了四种隔离级别, 并规定了每种隔离级别下, 上述几个问题是否存在; 一般来说, 隔离级别越低, 系统开销越低, 可支持并发越高, 但隔离性也越差;
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read Uncommitted 读未提交 | 可能 | 可能 | 可能 |
| Read Committed 读已提交 | 不可能 | 可能 | 可能 |
| Repeatable Read 可重复读 | 不可能 | 不可能 | 可能 |
| Serializable 可串行化 | 不可能 | 不可能 | 不可能 |
在实际应用中, 读未提交在并发时会导致很多问题, 而性能相对于其他隔离级别提高却很有限, 因此使用较少. 可串行化强制事务串行, 并发效率低,只有当对数据一致性要求极高且可以接受没有并发时才使用.
因此在大多数数据库系统中,默认的隔离级别是 读已提交(RC) 或 可重复读(RR)
InnoDB 默认的隔离级别是 RR
需要注意的是 RR 无法避免幻读问题, 但是 InnoDB 实现的 RR 避免了幻读问题;
2.1 RC隔离级别是如何解决RU的脏读问题的
2.2 说到MySQL的事务隔离级别,那你知道MVCC吗?它解决了什么问题?
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁. 基于并发性能的考虑,他们一般都同时实现了多版本并发控制(MVCC)
可以认为MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低. 大多数的MVCC都实现了非阻塞的读操作,写操作也只锁定必要的行;
MVCC只在RC和RR两个隔离级别下工作,其解决了在RR和RC隔离级别下读同一行和写同一行的两个事务的并发.
简单讲,MVCC就是, 对数据库的任何修改的提交都不会直接覆盖之前的数据,而是产生一个新的版本与老版本共存,使得读取时可以完全不加锁; 这样读取某一个数据时,事务可以根据隔离级别选择要读取哪个版本的数据.过程中不需要加锁
- RC: 一个事务读取数据时,总是读这个数据最近一次被commit的版本;
- RR: 一个事务读取数据时,总是读取当前事务开始之前最后一次被commit的版本(所以底层实现时需要比较当前事务和数据被commit的版本号)
- 其他两个隔离级别都和MVCC不兼容,因为RU总是读取最新的数据行.而不是符合当前事务版本的数据行,而Serializable会对所有读取到的行都加锁
举个简单的例子:(面试中可以说,因为很形象)
- 一个事务A（txnId=100）修改了数据X，使得X=1，并且commit了
- 另外一个事务B（txnId=101）开始尝试读取X，此时X=1。
- 第三个事务C（txnId=102）修改了数据X，使得X=2。并且提交了
- 事务B又一次读取了X。这时👇
- 如果,事务B是RC,那么就读取X的最新commit的版本,也就是x=2
- 如果事务B是RR,那么读取的就是当前事务(txnId=101)之前x的最新版本,也就是x被事务A提交的版本,即x=1
注意这里事务B不论是RC,还是RR,都不会被锁,都能立刻拿到结果,这就是MVCC存在的意义
MVCC:解决了在REPEATABLE READ和READ COMMITTED两个隔离级别下读同一行和写同一行的两个事务的并发
3 MySQL explain 的参数?
EXPLAIN 命令是查询性能优化不可缺少的一部分
参数
id/select_type/table/partitions/type/possible_keys/key/key_len/ref/rows/filted/Extra
- id
执行编号, 标识select所属的行. 如果在语句中没子查询或关联查询, 只有唯一的select, 每行都将显示1. 否则内存的select语句一般会顺序编号, 对应于其在原始语句中的位置;
id相同，从上往下执行
id不同，若包含子查询，id的递增的，id越大，优先级越高
id相同也存在不同的，可认为相同为一组，整体是id越大优先级越高，id相同的从上往下
- select_type
显示本行是简单或复杂select.
SIMPLE ： 简单查询
PRIMARY：包含子查询，外层查询为PRIMARY
DEVIRED: from后的子查询为DEVIRED
SUBQUERY： select 或where后的子查询为SUBQUERY
DEPENDENT SUBQUERY : 外层查询要依赖子查询的查询结果，则子查询称为
UNCACHEABLE SUBQUERY : 外层查询不依赖于内存结果
UNION : union前后为两个单独的查询
UNION RESULT: select中用到了UNION后的结果
- table
访问引用那个表
- partitions
是否命中分区表，未命中为null
- type
数据访问/读取操作类型,是较为重要的一个指标
结果值从好到坏一次是:
system/const/eq_ref/ref/range/index/all
system: 针对系统表，生产中不可能达到
const：主键或唯一索引作为查询条件
eq_ref: 唯一性索引扫描
ref：此索引（并不是唯一性索引）可能对应多行数据
range: 只检索指定范围的行。多用在where后between and/ > / <
index：使用了索引，但没有通过索引进行过滤。多见于覆盖索引或者排序分组
all：全表扫描
一般来说, 得保证查询至少达到range级别, 最好能达到 ref
- possible_keys
揭示哪一些索引可能有利于高效的查询
- key
显示MySQL决定采用哪个索引来优化查询
- key_len
显示MySQL在索引里使用的字段数
- ref
显示索引的那一列被使用了
- rows
可能扫描的行数
- filtered
引擎返回的结果经server过滤后符合条件的结果占中结果的比重，%表示
- extra
额外信息 如 using index, filesort等
4 如何优化SQL?
-
有条件的开启查询缓存
查询缓存是提高性能最有效的方法之一,而且这是被MySQL数据库引擎处理的. 当有很多相同的查询被执行了多次,这些查询结果就会被放到一个缓存中.这样后续的相同的查询就不应操作表而直接访问缓存结果(如何开启缓存,哪种查询可以使用缓存)
-
EXPLAIN你的SELECT查询使用
EXPLAIN关键字可以让你知道MySQL是如何处理的SQL语句的, 这可以帮你分析你的查询语句或者表结构的性能瓶颈(EXPLAIN的结果中的重要参数) -
当只要一行数据时 使用LIMIT 1
在查询中使用LIMIT 1 时, MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查找下一条符合条件的数据;
-
为搜索字段创建索引
索引并不一定就是给主键或是唯一的字段, 当你在某表中某个字段总要经常用来做搜索,并且符合创建索引的条件(都有哪些条件), 请为其建立索引
-
在 Join 表时,使用相同类型的字段, 并建立索引
-
千万不要 ORDER BY RAND()
RAND() 函数很耗CPU时间, 数据库性能会降低
-
避免 SELECT *
请用到什么字段就 查什么字段
-
永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个 ID 作为其主键, 而且最好的是一个 Int 类型(推荐使用 UNSIGNED), 并设置上自动增加的 AUTO_INCREMENT 标志;
-
使用 ENUM 而不是 VARCHAR
MySQL 官方建议, 当你有一个字段 其取值有限而且固定,并且被你定义为 VARCHAR , 此时请考虑使用 ENUM
ENUM类型是非常快和紧凑的; -
从 PROCEDURE ANALYSE() 取得建议
PROCEDURE ANALYSE() 会让MySQL帮你去分析你的字段和其实际的数据,并会给你一些有用的建议;(数据越多,建议越准)
-
尽可能的使用 NOT NULL
MySQL难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要MySQL内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MyISAM中固定大小的索引变成可变大小的索引。
-
Prepared Statements
Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。
Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候，这样的问题会好一些。
在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。
虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。
当然，也有一些情况下，我们需要避免使用Prepared Statements，因为其不支持查询缓存。但据说版本5.1后支持了。 -
把 IP 地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。
我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。 -
固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。 -
垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。
示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。
示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。
另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。 -
拆分大的 DELETE 或 INSERT 语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
-
越小的列会越快
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。
如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。 -
选择正确的存储引擎
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。 -
使用一个对象关系映射器(Object Relational Mapper)
使用 ORM (Object Relational Mapper)，你能够获得可靠的性能增涨。一个ORM可以做的所有事情，也能被手动的编写出来。但是，这需要一个高级专家。
ORM 的最重要的是“Lazy Loading”，也就是说，只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用，因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。
ORM 还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多得多。
4.1 使用索引为什么这么快?什么情况下使用索引?索引失效的情况?索引的类型?
使用索引为什么可以变快?
索引用来快速地寻找那些具有特定值的记录。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。
索引的原理是把无序的数据变成有序的查询
1、把创建了索引的列的内容进行排序
2、对排序结果生成倒排表
3、在倒排表内容上拼上数据地址链
4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
什么情况下使用索引?
- 在经常需要搜索的列上，可以加快搜索的速度。
- 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
- 在经常用于连接两张表的列上，这些列主要是一些外键，可以加快连接的速度。
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
索引失效的情况?
-
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
-
使用< > 做比较的时候,索引也会失效
-
like查询是以%开头（以%结尾是可以的）
-
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
-
如果mysql估计使用全表扫描要比使用索引快,则不使用索引
索引的类型?
索引类型主要包括：普通索引，唯一索引，主键索引和组合索引。
-
普通索引，就是直接创建简单的索引。
CREATE INDEX indexName ON mytable(username(length));
-
唯一索引，与普通索引类似，不同的是，Mysql的索引列值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。有以下几种创建方式：CTEATE UNIQUE INDEX indexName ON mytable(username(length))修改表结构；
ALTER mytable ADD UNIQUE [indexName] ON (username(length))创建表的时候直接指定。
-
主键索引，它是一种特殊的唯一索引，不允许有空值，一般是在建表的时候同时创建主键索引。
CREATE TABLE mytable（ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID)）;
当然也可以用ALTER命令，记住：一个表只能有一个主键。 -
组合索引
CTEATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,age INT NOT NULL);
5 SQL回表查询
如果索引的列在 select 所需获得的列中或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
6 Redis与MySQL 的数据一致性如何保证
保证 Redis 与 MySQL 的数据一致性,是为了防止多线程环境中, 缓存中会出现脏数据的情况;
举个栗子:
- 如果删除了缓存Redis, 还没来得及写库, 另一个线程就来读取,发现缓存为空, 则就会去数据库中读取数据写入缓存, 此时缓存中为脏数据;
- 如果先写了库, 在删除缓存前, 写库的线程当机了, 没有删除掉缓存,则也会出现数据不一致情况;
方案:
1 延时双删策略
在写库前后都进行redis.del(key)操作, 并且设定合理的超时时间. 具体步骤如下:
- 先删除缓存; 2) 再写数据库 3) 休眠500毫秒(根据业务定); 4) 再次删除缓存;
这里500毫秒的确定,需要根据自己的项目读数据业务逻辑的耗时. 这样做的目的就是确保读请求结束,写请求可以删除读请求造成的缓存脏数据;
2 设置缓存的过期时间
从理论上来说,给缓存设置过期时间, 是保证最终一致性的解决方案. 所有的写操作以数据库为准, 只要到达缓存过期时间, 则后面的读请求自然会从数据库中读新值然后回填缓存;
3 保障重试
如果操作万数据库后,由于种种原因删除缓存失败,这时候也会出现数据不一致的情况,这个时候我们需要提供一个保障重试的方案
- 更新数据库数据;
- 数据库会将操作信息写入binlog日志当中;
- 订阅程序提取出所需要的数据以及key;
- 另起一段非业务代码, 获得该信息;
- 尝试删除缓存信息, 发现删除失败的话;
- 将这些信息发送至消息队列;
- 重新从消息队列中获得该数据, 重试操作;
7 为什么先更新数据库在更新Redis
如果我们是先删除缓存信息再写库的话, 当线程A写库操作还没完成呢, 线程B来查询数据,发现缓存为空,这时就直接读取数据库,并更新缓存信息, 线程A写库完成后, 就造成库和缓存中的数据不一致(脏数据)
8 如何判断数据库是否打满了？
可以通过查看当前服务器的状态,比较物理磁盘读取和内存读取的比例来判断缓冲池的命中率, 通常 InnoDB 存储引擎的缓冲池的命中率不应该小于 99%;
通过MySQL命令
show global status like 'innodb%read%\G;
9 MySQL的主从复制,读写分离实现
MySQL的主从复制
是什么?
将主数据库中DDL和DML操作通过二进制日志(BINLOG)传输到从数据库上, 然后将这些日志中的SQL在从数据库中重新执行从而使得从数据库的数据与主数据库保持一致;
MySQL基础知识恶补:
主从复制的作用?
- 主数据库出现问题, 可以切换到从数据库.
- 可以进行数据库层面的读写分离.
- 可以在从数据库进行日常备份
复制过程
Binary log: 主数据库的二进制日志
Relay log: 从数据库的中继日志
- master在每个事务更新数据完成之前,将该操作记录串行的写入到binlog文件中.
- slave开启一个I/O thread,该线程在master打开一个普通连接, 主要工作是binlog dump process(binlog 倾倒流程). 如果读取的进度已经跟上了master, 就进入睡眠状态并等待master产生新的事件. I/O线程最终的目的是将这些事件写入到中继日志中;
- slave的SQL Thread会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致;
提示: 记住两个文件,两个线程, 一个终极目标
MySQL的读写分离
MySQL的读写分离具体实现,可借助工具MaxScale或者MyCat
MaxScale
MyCat
两个工具的工作原理都是一样的,配置好读写路由分发的条件就可以
10 聚簇索引和非聚簇索引
聚簇索引
聚簇索引是物理索引, 数据表就是按顺序存储的, 物理上是连续的.
一旦创建了聚簇索引, 表中的所有列都根据聚簇索引的key来存储.
MySQL中InnoDB表的聚簇索引
每个InnoDB表都需要一个聚簇索引.该聚簇索引可以帮助表优化增删改查操作.
- 如果你为表定义了一个主键, MySQL将使用主键作为聚簇索引.
- 如果某表没有指定一个主键,MySQL会将第一个列中数据都 not null 的唯一索引作为聚簇索引.
- 如果 InnoDB 某表既没有主键又没有适合的唯一索引, MySQL将自动创建一个隐藏的名字为"
GEN_CLUST_INDEX"的聚簇索引.
因此每个InnoDB表都有且只有一个聚簇索引
非聚簇索引
所有不是聚簇索引的索引都叫做 非聚簇索引 或者 辅助索引
在InnoDB存储引擎中, 每个辅助索引的每条记录都包含主键. 也包含非聚簇索引指定的列
MySQL使用这个主键值来检索聚簇索引
因此应该均可能将主键缩短,否则辅助索引占用空间会更大
一般来说用自增的整数型列作为主键列
两种索引值存储
主键是采用B+Tree的数据结构(上左图), 根据上文可知主键为聚簇索引,物理存储是根据ID的增加排序递增连续存储的.
(上右图)普通索引K也是B+Tree的数据结构,但是他不是聚簇索引, 因此为非聚簇索引; 它的叶子节点存储的是索引的列的值,它的数据域是聚簇索引即ID
238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
