阿里云上做网站套模板怎么做,做网页需要什么,动易网站系统,大连seo交流群目录 NOSQL和SQL的区别
数据库三大范式
MySQL 怎么连表查询
MySQL如何避免重复插入数据#xff1f;
CHAR 和 VARCHAR有什么区别#xff1f;
Text数据类型可以无限大吗#xff1f;
说一下外键约束
MySQL的关键字in和exist
mysql中的一些基本函数
SQL查询语句的执行顺…目录 NOSQL和SQL的区别
数据库三大范式
MySQL 怎么连表查询
MySQL如何避免重复插入数据
CHAR 和 VARCHAR有什么区别
Text数据类型可以无限大吗
说一下外键约束
MySQL的关键字in和exist
mysql中的一些基本函数
SQL查询语句的执行顺序
求不存在01课程但存在02课程的学生的成绩
查询总分排名在5-10名的学生id及对应的总分
执行一条SQL请求的过程
mysql引擎
MySQL为什么InnoDB是默认引擎
Innodb与MyISAM的区别
数据管理里数据文件大体分成哪几种数据文件 索引是什么有什么好处
索引的分类
MySQL聚簇索引和非聚簇索引
如果聚簇索引的数据更新它的存储要不要变化 什么字段适合当做主键
性别字段能加索引么为啥
主键用自增ID还是UUID
到了B树的叶子节点之后的查找数据是如何
B树的特性是什么
B树和B树的区别
B树的叶子节点链表是单向还是双向
为什么 MySQL 不用 跳表 创建联合索引时需要注意什么
联合索引ABC现在有个执行语句是A XXX and C XXX索引怎么走
联合索引ABC 查询条件 where b xxx and a x 会生效吗
联合索引ABCwhere条件是 a2 and c 1能用到联合索引吗
索引失效有哪些
什么是覆盖索引
索引已经建好了那我再插入一条数据索引会有哪些变化
索引字段是不是建的越多越好 索引的优缺点
怎么决定建立哪些索引
索引优化详细讲讲
事务的特性是什么如何实现的 mysql可能出现什么和并发相关问题
哪些场景不适合脏读
mysql的是怎么解决并发问题的 事务的隔离级别有哪些
可重复读隔离级别下A事务提交的数据在B事务能看见吗
举个例子说可重复读下的幻读问题
Mysql 设置了可重读隔离级后怎么保证不发生幻读
串行化隔离级别是通过什么实现的
MVCC实现原理 一条update是不是原子性的为什么
一个事务里有特别多sql的弊端
讲一下mysql里有哪些锁
数据库的表锁和行锁有什么作用 MySQL两个线程的update语句同时处理一条数据会不会有阻塞
两条update语句处理一张表的不同的主键范围的记录一个10一个15会不会遇到阻塞底层是为什么的
如果2个范围不是主键或索引还会阻塞吗
日志文件是分成了哪几种
binlog
UndoLog日志的作用是什么
有了undolog为啥还需要redolog呢
能不能只用binlog不用redo log
binlog 两阶段提交过程是怎么样的
update语句的具体执行过程是怎样的
mysql的explain有什么作用
发现查询速度很慢你有那些解决方案
如果Explain用到的索引不正确的话有什么办法干预吗
主从延迟都有什么处理方法
MySQL主从复制
分表和分库是什么有什么区别 NOSQL和SQL的区别
SQL数据库指关系型数据库 - 主要代表SQL ServerOracleMySQL(开源)PostgreSQL(开源)。关系型数据库存储结构化数据。这些数据逻辑上以行列二维表的形式存在每一列代表数据的一种属性每一行代表一个数据实体。
NoSQL指非关系型数据库 主要代表MongoDBRedis。NoSQL 数据库逻辑上提供了不同于二维表的存储方式存储方式可以是JSON文档、哈希表或者其他方式.
关系型数据库支持 ACID 即原子性一致性隔离性和持续性。相对而言NoSQL 采用更宽松的模型 BASE 即基本可用软状态和最终一致性。
NoSQL数据之间无关系这样就非常容易扩展也无形之间在架构的层面上带来了可扩展的能力。比如 redis 自带主从复制模式、哨兵模式、切片集群模式。相反关系型数据库的数据之间存在关联性水平扩展较难 需要解决跨服务器 JOIN分布式事务等问题。 数据库三大范式
第一范式1NF要求数据库表的每一列都是不可分割的原子数据项。
第二范式2NF在1NF的基础上非码属性必须完全依赖于整个主键或候选键在1NF基础上消除非主属性对主码的部分函数依赖
第三范式3NF在2NF基础上任何非主属性不依赖于其它非主属性在2NF基础上消除传递依赖
一个满足BCNF的关系模式的条件: 1.所有非主属性对每一个码都是完全函数依赖。 2.所有的主属性对每一个不包含它的码,也是完全函数依赖。 3.没有任何属性完全函数依赖于非码的任何一组属性。 MySQL 怎么连表查询
1. 内连接 (INNER JOIN)内连接返回两个表中有匹配关系的行。
2. 左外连接 (LEFT JOIN)
左外连接返回左表中的所有行即使在右表中没有匹配的行。未匹配的右表列会包含NULL。
3. 右外连接 (RIGHT JOIN)
右外连接返回右表中的所有行即使左表中没有匹配的行。未匹配的左表列会包含NULL。
4. 全外连接 (FULL JOIN)
全外连接返回两个表中所有行包括非匹配行。MySQL中FULL JOIN 需要使用 UNION 来实现 MySQL如何避免重复插入数据
方式一使用UNIQUE约束
方式二使用INSERT ... ON DUPLICATE KEY UPDATE
方式三使用INSERT IGNOREE
如果需要保证全局唯一性使用UNIQUE约束是最佳做法。如果需要插入和更新结合可以使用ON DUPLICATE KEY UPDATE。对于快速忽略重复插入INSERT IGNORE是合适的选择。 CHAR 和 VARCHAR有什么区别
CHAR是固定长度的字符串类型定义时需要指定固定长度存储时会在末尾补足空格。 VARCHAR是可变长度的字符串类型定义时需要指定最大长度实际存储时根据实际长度占用存储空间。VARCHAR适合存储长度可变的数据如用户输入的文本、备注等节约存储空间。 Text数据类型可以无限大吗
TEXT65,535 bytes ~64kbMEDIUMTEXT16,777,215 bytes ~16MbLONGTEXT4,294,967,295 bytes ~4Gb
说一下外键约束
外键约束的作用是维护表与表之间的关系确保数据的完整性和一致性。 MySQL的关键字in和exist
IN 用于检查左边的表达式是否存在于右边的列表或子查询的结果集中
EXISTS 用于判断子查询是否至少能返回一行数据。
性能差异在很多情况下EXISTS 的性能优于 IN特别是当子查询的表很大时。这是因为EXISTS 一旦找到匹配项就会立即停止查询而IN可能会扫描整个子查询结果集。使用场景如果子查询结果集较小且不频繁变动IN 可能更直观易懂。而当子查询涉及外部查询的每一行判断并且子查询的效率较高时EXISTS 更为合适。NULL值处理IN 能够正确处理子查询中包含NULL值的情况而EXISTS 不受子查询结果中NULL值的影响因为它关注的是行的存在性而不是具体值。 mysql中的一些基本函数
CONCAT(str1, str2, ...)连接多个字符串
LENGTH(str)返回字符串的长度
SUBSTRING(str, pos, len)从指定位置开始截取指定长度的子字符串。
REPLACE(str, from_str, to_str)将字符串中的某部分替换为另一个字符串。
ABS(num)返回数字的绝对值。
POWER(num, exponent)返回指定数字的指定幂次方。
NOW()返回当前日期和时间。
CURDATE()返回当前日期。
COUNT(column)计算指定列中的非NULL值的个数。
SUM(column)计算指定列的总和。
AVG(column)计算指定列的平均值。
MAX(column)返回指定列的最大值。
MIN(column)返回指定列的最小值。 SQL查询语句的执行顺序 (9) SELECT
(10) DISTINCT column,
(6) AGG_FUNC column or expression, ...
(1) FROM left_table (3) join_typeJOINright_table(2) ONjoin_condition
(4) WHERE where_condition
(5) GROUP BY group_by_list
(7) WITH {CUBE|ROLLUP}
(8) HAVING having_condtion
(11) ORDER BY order_by_list
(12) LIMIT limit_number; 求不存在01课程但存在02课程的学生的成绩
方法1使用LEFT JOIN 和 IS NULL
SELECT s.sid, s.sname, sc2.cid, sc2.score
FROM Student s
LEFT JOIN Score AS sc1 ON s.sid sc1.sid AND sc1.cid 01
LEFT JOIN Score AS sc2 ON s.sid sc2.sid AND sc2.cid 02
WHERE sc1.cid IS NULL AND sc2.cid IS NOT NULL;
方法2使用NOT EXISTS
SELECT s.sid, s.sname, sc.cid, sc.score
FROM Student s
JOIN Score sc ON s.sid sc.sid AND sc.cid 02
WHERE NOT EXISTS (SELECT 1 FROM Score sc1 WHERE sc1.sid s.sid AND sc1.cid 01
); 查询总分排名在5-10名的学生id及对应的总分
WITH StudentTotalScores AS (SELECT stu_id,SUM(score) AS total_scoreFROM student_scoreGROUP BY stu_id
),
RankedStudents AS (SELECTstu_id,total_score,RANK() OVER (ORDER BY total_score DESC) AS rankingFROMStudentTotalScores
)
SELECTstu_id,total_score
FROMRankedStudents
WHEREranking BETWEEN 5 AND 10; 执行一条SQL请求的过程 连接器建立连接管理连接、校验用户身份 查询缓存查询语句如果命中查询缓存则直接返回否则继续往下执行。MySQL 8.0 已删除该模块 解析 SQL通过解析器对 SQL 查询语句进行词法分析、语法分析然后构建语法树方便后续模块读取表名、字段、语句类型 执行 SQL执行 SQL 共有三个阶段 预处理阶段检查表或字段是否存在将 select * 中的 * 符号扩展为表上的所有列。 优化阶段基于查询成本的考虑 选择查询成本最小的执行计划 执行阶段根据执行计划执行 SQL 查询语句从存储引擎读取记录返回给客户端 mysql引擎
InnoDBInnoDB是MySQL的默认存储引擎具有ACID事务支持、行级锁、外键约束等特性。它适用于高并发的读写操作支持较好的数据完整性和并发控制。MyISAM具有较低的存储空间和内存消耗适用于大量读操作的场景。然而MyISAM不支持事务、行级锁和外键约束因此在并发写入和数据完整性方面有一定的限制。
MemoryMemory引擎将数据存储在内存中适用于对性能要求较高的读操作但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。 MySQL为什么InnoDB是默认引擎
InnoDB引擎在事务支持、并发性能、崩溃恢复等方面具有优势因此被MySQL选择为默认的存储引擎。
事务支持InnoDB引擎提供了对事务的支持可以进行ACID原子性、一致性、隔离性、持久性属性的操作。Myisam存储引擎是不支持事务的。并发性能InnoDB引擎采用了行级锁定的机制可以提供更好的并发性能Myisam存储引擎只支持表锁锁的粒度比较大。崩溃恢复InnoDB引引擎通过 redolog 日志实现了崩溃恢复可以在数据库发生异常情况如断电时通过日志文件进行恢复保证数据的持久性和一致性。Myisam是不支持崩溃恢复的。
Innodb与MyISAM的区别
事务InnoDB 支持事务MyISAM 不支持事务索引结构InnoDB 是聚簇索引MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上因此 InnoDB 必须要有主键通过主键索引效率很高。但是辅助索引需要两次查询先查询到主键然后再通过主键查询到数据。而 MyISAM 是非聚簇索引数据文件是分离的索引保存的是数据文件的指针。锁粒度InnoDB 最小的锁粒度是行锁MyISAM 最小的锁粒度是表锁。count 的效率InnoDB 不保存表的具体行数。而MyISAM 用一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快。
数据管理里数据文件大体分成哪几种数据文件
db.opt用来存储当前数据库的默认字符集和字符校验规则。t_order.frm t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件该文件是用来保存每个表的元数据信息的主要包含表结构定义。t_order.ibdt_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件文件名ibdata1里也可以存放在独占表空间文件文件名表名字.ibd。这个行为是由参数 innodb_file_per_table 控制的若设置了参数 innodb_file_per_table 为 1则会将存储的数据、索引等信息单独存储在一个独占表空间从 MySQL 5.6.6 版本开始它的默认值就是 1 了因此从这个版本之后 MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。 索引是什么有什么好处
如果查询的时候没有用到索引就会全表扫描这时候查询的时间复杂度是On如果用到了索引那么查询的时候可以基于二分查找算法通过索引快速定位到目标数据 mysql 索引的数据结构一般是 b树其搜索复杂度为O(logdN)其中 d 表示节点允许的最大子节点个数为 d 个。 索引的分类
按「数据结构」分类Btree索引、Hash索引、Full-text索引。按「物理存储」分类聚簇索引主键索引、二级索引辅助索引。按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类单列索引、联合索引。 按数据结构分类
在创建表时InnoDB 存储引擎会根据不同的场景选择不同的列作为索引
如果有主键默认会使用主键作为聚簇索引的索引键key如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键key在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键
其它索引都属于辅助索引Secondary Index也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 BTree 索引。
按物理存储分类
主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree 的叶子节点里二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。
在查询时使用了二级索引如果查询的数据能在二级索引里查询的到那么就不需要回表这个过程就是覆盖索引。如果查询的数据不在二级索引里就会先检索二级索引找到对应的叶子节点获取到主键值后然后再检索主键索引就能查询到数据了这个过程就是回表。
按字段特性分类
主键索引
主键索引就是建立在主键字段上的索引通常在创建表的时候一起创建一张表最多只有一个主键索引索引列的值不允许有空值。
唯一索引
唯一索引建立在 UNIQUE 字段上的索引一张表可以有多个唯一索引索引列的值必须唯一但是允许有空值。
普通索引按
普通索引就是建立在普通字段上的索引既不要求字段为主键也不要求字段为 UNIQUE。
前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引而不是在整个字段上建立的索引前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
按字段个数分类
使用联合索引时存在最左匹配原则也就是按照最左优先的方式进行索引的匹配。
比如如果创建了一个 (a, b, c) 联合索引如果查询条件是以下这几种就可以匹配上联合索引
where a1where a1 and b2 and c3where a1 and b2
需要注意的是因为有查询优化器所以 a 字段在 where 子句的顺序并不重要。
联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引但是在范围查询字段的后面的字段无法用到联合索引。 MySQL聚簇索引和非聚簇索引
数据存储在聚簇索引中数据行按照索引键值的顺序存储也就是说索引的叶子节点包含了实际的数据行。这意味着索引结构本身就是数据的物理存储结构。非聚簇索引的叶子节点不包含完整的数据行而是包含指向数据行的指针或主键值。数据行本身存储在聚簇索引中。
索引与数据关系由于数据与索引紧密相连当通过聚簇索引查找数据时可以直接从索引中获得数据行而不需要额外的步骤去查找数据所在的位置。当通过非聚簇索引查找数据时首先在非聚簇索引中找到对应的主键值然后通过这个主键值回溯到聚簇索引中查找实际的数据行这个过程称为“回表”。
唯一性聚簇索引通常是基于主键构建的因此每个表只能有一个聚簇索引因为数据只能有一种物理排序方式。一个表可以有多个非聚簇索引因为它们不直接影响数据的物理存储位置。效率对于范围查询和排序查询聚簇索引通常更有效率因为它避免了额外的寻址开销。非聚簇索引在使用覆盖索引进行查询时效率更高因为它不需要读取完整的数据行。但是需要进行回表的操作使用非聚簇索引效率比较低因为需要进行额外的回表操作。 如果聚簇索引的数据更新它的存储要不要变化
如果更新的数据是非索引数据也就是普通的用户记录那么存储结构是不会发生变化如果更新的数据是索引数据那么存储结构是有变化的因为要维护 b树的有序性 什么字段适合当做主键
字段具有唯一性且不能为空的特性字段最好的是有递增的趋势的如果字段的值是随机无序的可能会引发页分裂的问题造型性能影响。不建议用业务数据作为主键比如会员卡号、订单号、学生号之类的因为我们无法预测未来会不会因为业务需要而出现业务字段重复或者重用的情况。通常情况下会用自增字段来做主键对于单机系统来说是没问题的。但是如果有多台服务器各自都可以录入数据那就不一定适用了。因为如果每台机器各自产生的数据需要合并就可能会出现主键重复的问题这时候就需要考虑分布式 id 的方案了。
性别字段能加索引么为啥
区分度的计算方式 select count(DISTINCT sex)/count(*) from sys_user。区别度几乎等于 0 。
实际上与索引创建规则之一区分度有关既然走索引的查询的成本比全表扫描高优化器就会选择全表扫描的方向进行查询这时候建立的性别字段索引就没有启到加快查询的作用反而还因为创建了索引占用了空间 主键用自增ID还是UUID
下一条记录就会写入新的页中一旦数据按照这种顺序的方式加载主键页就会近乎于顺序的记录填满提升了页面的最大填充率不会有页的浪费新插入的行一定会在原有的最大数据行下一行mysql定位和寻址很快不会为计算新行的位置而做出额外的消耗减少了页分裂和碎片的产生
因为 uuid 相对顺序的自增 id 来说是毫无规律可言的新行的值不一定要比之前的主键的值要大所以 innodb 无法做到总是把新行插入到索引的最后而是需要为新行寻找新的合适的位置从而来分配新的空间。
写入的目标页很可能已经刷新到磁盘上并且从缓存上移除或者还没有被加载到缓存中innodb 在插入之前不得不先找到并从磁盘读取目标页到内存中这将导致大量的随机 IO。因为写入是乱序的innodb 不得不频繁的做页分裂操作以便为新的行分配空间页分裂导致移动大量的数据影响性能。由于频繁的页分裂页会变得稀疏并被不规则的填充最终会导致数据会有碎片。
使用 InnoDB 应该尽可能的按主键的自增顺序插入并且尽可能使用单调的增加的聚簇键的值来插入新行 到了B树的叶子节点之后的查找数据是如何
页目录就是由多个槽组成的槽相当于分组记录的索引。然后因为记录是按照「主键值」从小到大排序的所以我们通过槽查找记录时可以使用二分法快速定位要查询的记录在哪个槽哪个记录分组定位到槽后再遍历槽内的所有记录找到对应的记录
B树的特性是什么
所有叶子节点都在同一层这是B树的一个重要特性确保了所有数据项的检索都具有相同的I/O延迟提高了搜索效率。每个叶子节点都包含指向相邻叶子节点的指针形成一个链表由于叶子节点之间的链接B树非常适合进行范围查询和排序扫描。可以沿着叶子节点的链表顺序访问数据而无需进行多次随机访问。非叶子节点存储键值非叶子节点仅存储键值和指向子节点的指针不包含数据记录。这些键值用于指导搜索路径帮助快速定位到正确的叶子节点。并且由于非叶子节点只存放键值当数据量比较大时相对于B树B树的层高更少查找效率也就更高。叶子节点存储数据记录与B树不同B树的叶子节点存储实际的数据记录或指向数据记录的指针。这意味着每次搜索都会到达叶子节点才能找到所需数据。自平衡B树在插入、删除和更新操作后会自动重新平衡确保树的高度保持相对稳定从而保持良好的搜索性能。每个节点最多可以有M个子节点最少可以有ceil(M/2)个子节点除了根节点这里的M是树的阶数。 B树和B树的区别
在B树中数据都存储在叶子节点上而非叶子节点只存储索引信息而B树的非叶子节点既存储索引信息也存储部分数据。B树的非叶子节点可以存放更多的索引因此 B 树可以比 B 树更「矮胖」查询底层节点的磁盘 I/O次数会更少。B树的叶子节点使用链表相连便于范围查询和顺序访问B树的叶子节点没有链表连接。B树的查找性能更稳定每次查找都需要查找到叶子节点而B树的查找可能会在非叶子节点找到数据性能相对不稳定。B 树有大量的冗余节点所有非叶子节点都是冗余索引这些冗余索引让 B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样会发生复杂的树的变化 B树的叶子节点链表是单向还是双向
B 树的叶子节点之间是用「双向链表」进行连接实现倒序遍历或者排序。B 树点节点内容是数据页数据页里存放了用户的记录以及各种信息每个数据页默认大小是 16 KB。
为什么 MySQL 不用 跳表
B树的高度在3层时存储的数据可能已达千万级别但对于跳表而言同样去维护千万的数据量那么所造成的跳表层数过高而导致的磁盘io次数增多也就是使用B树在存储同样的数据下磁盘io次数更少。 创建联合索引时需要注意什么
越靠前的字段被用于索引过滤的概率越高建立联合索引时要把区分度大的字段排在前面这样区分度大的字段越有可能被更多的 SQL 使用到。区分度就是某个字段 column 不同值的个数「除以」表的总行数
联合索引ABC现在有个执行语句是A XXX and C XXX索引怎么走
根据最左匹配原则A可以走联合索引C不会走联合索引但是C可以走索引下推
联合索引ABC 查询条件 where b xxx and a x 会生效吗
索引会生效a 和 b 字段都能利用联合索引符合联合索引最左匹配原则。
联合索引ABCwhere条件是 a2 and c 1能用到联合索引吗
会用到联合索引但是只有 a 才能走索引c 无法走索引因为不符合最左匹配原则。虽然 c 无法走索引 但是 c 字段在 5.6 版本之后会有索引下推的优化能减少回表查询的次数。
索引失效有哪些
当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列使用函数就会导致索引失效。当我们在查询条件中对索引列进行表达式计算也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而条件语句中的输入参数是数字的话那么索引列会发生隐式类型转换由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。
什么是覆盖索引
覆盖索引是指一个索引包含了查询所需的所有列因此不需要访问表中的数据行就能完成查询。 索引已经建好了那我再插入一条数据索引会有哪些变化
插入新数据可能导致B树结构的调整和索引信息的更新以保持B树的平衡性和正确性这些变化通常由数据库系统自动处理确保数据的一致性和索引的有效性。
如果插入的数据导致叶子节点已满可能会触发叶子节点的分裂操作以保持B树的平衡性。
索引字段是不是建的越多越好
不是建的的越多会占用越多的空间而且在写入频繁的场景下对于B树的维护所付出的性能消耗也会越大 索引的优缺点
需要占用物理空间数量越大占用空间越大创建索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率因为每次增删改索引B 树为了维护索引有序性都需要进行动态维护。
怎么决定建立哪些索引
什么时候适用索引
字段有唯一性限制的经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的。
什么时候不需要创建索引
WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引表数据太少的时候不需要创建索引经常更新的字段不用创建索引 索引优化详细讲讲
常见优化索引的方法
前缀索引优化使用前缀索引是为了减小索引字段大小可以增加一个索引页中存储的索引值有效提高索引的查询速度。在一些大字符串的字段作为索引时使用前缀索引可以帮助我们减小索引项的大小。覆盖索引优化覆盖索引是指 SQL 中 query 的所有字段在索引 BTree 的叶子节点上都能找得到的那些索引从二级索引中查询得到记录而不需要通过聚簇索引查询获得可以避免回表的操作。主键索引最好是自增的 如果我们使用自增主键那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有的数据当页面写满就会自动开辟一个新页面。因为每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。如果我们使用非自增主键由于每次插入主键的索引值都是随机的因此每次插入新的数据时就可能会插入到现有数据页中间的某个位置这将不得不移动其它数据来满足新数据的插入甚至需要从一个页面复制数据到另外一个页面我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。防止索引失效 当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。 事务的特性是什么如何实现的 mysql可能出现什么和并发相关问题
原子性Atomicity一个事务中的所有操作要么全部完成要么全部不完成一致性Consistency是指事务操作前和操作后数据满足完整性约束数据库保持一致性状态。隔离性Isolation数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致因为多个事务同时使用相同的数据时不会相互干扰每个事务都有一个完整的数据空间对其他并发事务是隔离的。持久性Durability事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。MySQL InnoDB 引擎通过什么技术来保证事务的这四个特性的呢持久性是通过 redo log 重做日志来保证的原子性是通过 undo log回滚日志 来保证的隔离性是通过 MVCC多版本并发控制 或锁机制来保证的一致性则是通过持久性原子性隔离性来保证 脏读 如果一个事务「读到」了另一个「未提交事务修改过的数据」就意味着发生了「脏读」现象。 不可重复读 在一个事务内多次读取同一个数据如果出现前后两次读到的数据不一样的情况就意味着发生了「不可重复读」现象。 幻读 在一个事务内多次查询某个符合查询条件的「记录数量」如果出现前后两次查询到的记录数量不一样的情况就意味着发生了「幻读」现象。 哪些场景不适合脏读
银行系统在银行系统中如果一个账户的余额正在被调整但尚未提交另一个事务读取了这个临时的余额可能会导致客户看到不正确的余额。库存管理系统在一个库存管理系统中如果一个商品的数量正在被更新但尚未提交另一个事务读取了这个临时的数量可能会导致库存管理错误。在线订单系统在一个在线订单系统中如果一个订单正在被修改但尚未提交另一个事务读取了这个临时的订单状态可能导致订单状态显示错误客户收到不准确的信息。 mysql的是怎么解决并发问题的
锁机制Mysql提供了多种锁机制来保证数据的一致性包括行级锁、表级锁、页级锁等。通过锁机制可以在读写操作时对数据进行加锁确保同时只有一个操作能够访问或修改数据。事务隔离级别Mysql提供了多种事务隔离级别包括读未提交、读已提交、可重复读和串行化。通过设置合适的事务隔离级别可以在多个事务并发执行时控制事务之间的隔离程度以避免数据不一致的问题。MVCC多版本并发控制Mysql使用MVCC来管理并发访问它通过在数据库中保存不同版本的数据来实现不同事务之间的隔离。在读取数据时Mysql会根据事务的隔离级别来选择合适的数据版本从而保证数据的一致性。 事务的隔离级别有哪些
读未提交read uncommitted指一个事务还没提交时它做的变更就能被其他事务看到读提交read committed指一个事务提交之后它做的变更才能被其他事务看到可重复读repeatable read指一个事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的MySQL InnoDB 引擎的默认隔离级别串行化serializable会对记录加上读写锁在多个事务对这条记录进行读写操作时如果发生了读写冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行 在「读未提交」隔离级别下可能发生脏读、不可重复读和幻读现象在「读提交」隔离级别下可能发生不可重复读和幻读现象但是不可能发生脏读现象在「可重复读」隔离级别下可能发生幻读现象但是不可能脏读和不可重复读现象在「串行化」隔离级别下脏读、不可重复读和幻读现象都不可能会发生。 这四种隔离级别具体是如何实现的呢
对于「读未提交」隔离级别的事务来说因为可以读到未提交事务修改的数据所以直接读取最新的数据就好了对于「串行化」隔离级别的事务来说通过加读写锁的方式来避免并行访问对于「读提交」和「可重复读」隔离级别的事务来说它们是通过 Read View来实现的它们的区别在于创建 Read View 的时机不同「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View而「可重复读」隔离级别是「启动事务时」生成一个 Read View然后整个事务期间都在用这个 Read View。 可重复读隔离级别下A事务提交的数据在B事务能看见吗
可重复读隔离级是由 MVCC多版本并发控制实现的实现的方式是开始事务后执行 begin 语句后在执行第一个查询语句后会创建一个 Read View后续的查询语句利用这个 Read View通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据所以事务过程中每次查询的数据都是一样的即使中途有其他事务插入了新纪录是查询不出来这条数据的。 举个例子说可重复读下的幻读问题
事务 A 第一次执行普通的 select 语句时生成了一个 ReadView之后事务 B 向表中新插入了一条 id 5 的记录并提交。接着事务 A 对 id 5 这条记录进行了更新操作在这个时刻这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了于是就发生了幻读。 Mysql 设置了可重读隔离级后怎么保证不发生幻读
尽量在开启事务之后马上执行 select ... for update 这类锁定读的语句因为它会对记录加 next-key lock从而避免其他事务插入一条新记录就避免了幻读的问题。
串行化隔离级别是通过什么实现的
是通过行级锁来实现的普通的 select 查询是会对记录加 S 型的 next-key 锁其他事务就没没办法对这些已经加锁的记录进行增删改操作了从而避免了脏读、不可重复读和幻读现象。 MVCC实现原理
MVCC允许多个事务同时读取同一行数据而不会彼此阻塞每个事务看到的数据版本是该事务开始时的数据版本。
「读提交」隔离级别是在「每个select语句执行前」都会重新生成一个 Read View「可重复读」隔离级别是执行第一条select时生成一个 Read View然后整个事务期间都在用这个 Read View。
Read View 有四个重要的字段 m_ids 指的是在创建 Read View 时当前数据库中「活跃事务」的事务 id 列表注意是一个列表“活跃事务”指的就是启动了但还没提交的事务。min_trx_id 指的是在创建 Read View 时当前数据库中「活跃事务」中事务 id 最小的事务也就是 m_ids 的最小值。max_trx_id 这个并不是 m_ids 的最大值而是创建 Read View 时当前数据库中应该给下一个事务的 id 值也就是全局事务中最大的事务 id 值 1creator_trx_id 指的是创建该 Read View 的事务的事务 id。
对于使用 InnoDB 存储引擎的数据库表它的聚簇索引记录中都包含下面两个隐藏列 trx_id当一个事务对某条聚簇索引记录进行改动时就会把该事务的事务 id 记录在 trx_id 隐藏列里roll_pointer每次对某条聚簇索引记录进行改动时都会把旧版本的记录写入到 undo 日志中然后这个隐藏列是个指针指向每一个旧版本记录于是就可以通过它找到修改前的记录。 在创建 Read View 后我们可以将记录中的 trx_id 划分这三种情况 如 果记录的 trx_id 值小于 Read View 中的 min_trx_id 值表示这个版本的记录是在创建 Read View 前已经提交的事务生成的所以该版本的记录对当前事务可见。 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间需要判断 trx_id 是否在 m_ids 列表中 如果记录的 trx_id 在 m_ids 列表中表示生成该版本记录的活跃事务依然活跃着还没提交事务所以该版本的记录对当前事务不可见。 如果记录的 trx_id 不在 m_ids列表中表示生成该版本记录的活跃事务已经被提交所以该版本的记录对当前事务可见。
这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC多版本并发控制。 一条update是不是原子性的为什么
是原子性主要通过锁undolog 日志保证原子性的
执行 update 的时候会加行级锁保证了一个事务更新一条记录的时候不会被其他事务干扰。事务执行过程中会生成 undolog如果事务执行失败就可以通过 undolog 日志进行回滚。 一个事务里有特别多sql的弊端
如果一个事务特别多 sql锁定的数据太多容易造成大量的死锁和锁超时。回滚记录会占用大量存储空间事务回滚时间长执行时间长容易造成主从延迟主库上必须等事务执行完成才会写入binlog再传给备库。 讲一下mysql里有哪些锁 全局锁通过flush tables with read lock 语句会将整个数据库就处于只读状态了这时其他线程执行以下操作增删改或者表结构修改都会阻塞。全局锁主要应用于做全库逻辑备份这样在备份数据库期间不会因为数据或表结构的更新而出现备份文件的数据与预期的不一样。 表级锁MySQL 里面表级别的锁有这几种 表锁通过lock tables 语句可以对表加表锁表锁除了会限制别的线程的读写外也会限制本线程接下来的读写操作。 元数据锁当我们对数据库表进行操作时会自动给这个表加上 MDL对一张表进行 CRUD 操作时加的是 MDL 读锁对一张表做结构变更操作的时候加的是 MDL 写锁MDL 是为了保证当用户对表执行 CRUD 操作时防止其他线程对这个表结构做了变更。 意向锁当执行插入、更新、删除操作需要先对表加上「意向独占锁」然后对该记录加独占锁。意向锁的目的是为了快速判断表里是否有记录被加锁。 行级锁InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。 记录锁锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的满足读写互斥写写互斥 间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。 Next-Key Lock 称为临键锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。 数据库的表锁和行锁有什么作用
表锁的作用
整体控制表锁可以用来控制整个表的并发访问当一个事务获取了表锁时其他事务无法对该表进行任何读写操作从而确保数据的完整性和一致性。粒度大表锁的粒度比较大在锁定表的情况下可能会影响到整个表的其他操作可能会引起锁竞争和性能问题。适用于大批量操作表锁适合于需要大批量操作表中数据的场景例如表的重建、大量数据的加载等。
行锁的作用
细粒度控制行锁可以精确控制对表中某行数据的访问使得其他事务可以同时访问表中的其他行数据在并发量大的系统中能够提高并发性能。减少锁冲突行锁不会像表锁那样造成整个表的锁冲突减少了锁竞争的可能性提高了并发访问的效率。适用于频繁单行操作行锁适合于需要频繁对表中单独行进行操作的场景 MySQL两个线程的update语句同时处理一条数据会不会有阻塞
如果是两个事务同时更新了 id 1比如 update ... where id 1那么是会阻塞的。因为 InnoDB 存储引擎实现了行级锁。 两条update语句处理一张表的不同的主键范围的记录一个10一个15会不会遇到阻塞底层是为什么的
不会因为锁住的范围不一样不会形成冲突。
第一条 update sql 的话 id10锁住的范围是-♾️10第二条 update sql 的话id 15锁住的范围是15♾️ 如果2个范围不是主键或索引还会阻塞吗
如果2个范围查询的字段不是索引的话那就代表 update 没有用到索引这时候触发了全表扫描全部索引都会加行级锁这时候第二条 update 执行的时候就会阻塞了。
因为如果 update 没有用到索引在扫描过程中会对索引加锁所以全表扫描的场景下所有记录都会被加锁也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁相当于锁住了全表。 日志文件是分成了哪几种
redo log 重做日志是 Innodb 存储引擎层生成的日志实现了事务中的持久性主要用于掉电等故障恢复undo log 回滚日志是 Innodb 存储引擎层生成的日志实现了事务中的原子性主要用于事务回滚和 MVCC。bin log 二进制日志是 Server 层生成的日志主要用于数据备份和主从复制relay log 中继日志用于主从复制场景下slave通过io线程拷贝master的bin log后本地生成的日志慢查询日志用于记录执行时间过长的sql需要设置阈值后手动开启
binlog
MySQL 在完成一条更新操作后Server 层还会生成一条 binlog等之后事务提交的时候会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件binlog 是 MySQL 的 Server 层实现的日志所有存储引擎都可以使用。
binlog 是追加写写满一个文件就创建一个新的文件继续写不会覆盖以前的日志保存的是全量的日志用于备份恢复、主从复制binlog 文件是记录了所有数据库表结构变更和表数据修改的日志不会记录查询类的操作比如 SELECT 和 SHOW 操作。
binlog 有 3 种格式类型分别是 STATEMENT默认格式、ROW、 MIXED区别如下
STATEMENT每一条修改数据的 SQL 都会被记录到 binlog 中相当于记录了逻辑操作所以针对这种格式 binlog 可以称为逻辑日志主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题比如你用了 uuid 或者 now 这些函数你在主库上执行的结果并不是你在从库执行的结果这种随时在变的函数会导致复制的数据不一致ROW记录行数据最终被修改成什么样了这种格式的日志就不能称为逻辑日志了不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录比如执行批量 update 语句更新多少行数据就会产生多少条记录使 binlog 文件过大而在 STATEMENT 格式下只会记录一个 update 语句而已MIXED包含了 STATEMENT 和 ROW 模式它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式
UndoLog日志的作用是什么
undo log 是一种用于撤销回退的日志它保证了事务的 ACID 特性中的原子性
在事务没提交之前MySQL 会先记录更新前的数据到 undo log 日志文件里面当事务回滚时可以利用 undo log 来进行回滚。每当 InnoDB 引擎对一条记录进行操作修改、删除、新增时要把回滚时需要的信息都记录到 undo log 里。
在插入一条记录时要把这条记录的主键值记下来这样之后回滚时只需要把这个主键值对应的记录删掉就好了在删除一条记录时要把这条记录中的内容都记下来这样之后回滚时再把由这些内容组成的记录插入到表中就好了在更新一条记录时要把被更新的列的旧值记下来这样之后回滚时再把这些列更新为旧值就好了。
在发生回滚时就读取 undo log 里的数据然后做原先相反操作。比如当 delete 一条记录时undo log 中会把记录中的内容都记下来然后执行回滚操作的时候就读取 undo log 里的数据然后进行 insert 操作。 有了undolog为啥还需要redolog呢
Buffer Pool 是提高了读写效率没错但是问题来了Buffer Pool 是基于内存的而内存总是不可靠万一断电重启还没来得及落盘的脏页数据就会丢失。
为了防止断电导致数据丢失的问题当有一条记录需要更新的时候InnoDB 引擎就会先更新内存同时标记为脏页然后将本次对这个页的修改以 redo log 的形式记录下来这个时候更新就算完成了。
后续InnoDB 引擎会在适当的时候由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里这就是 WAL Write-Ahead Logging技术。WAL 技术指的是 MySQL 的写操作并不是立刻写到磁盘上而是先写日志然后在合适的时间再写到磁盘上。
redo log 是物理日志记录了某个数据页做了什么修改比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新。
在事务提交时只要先将 redo log 持久化到磁盘即可可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时虽然脏页数据没有持久化但是 redo log 已经持久化接着 MySQL 重启后可以根据 redo log 的内容将所有数据恢复到最新的状态。 redo log 记录了此次事务「完成后」的数据状态记录的是更新之后的值undo log 记录了此次事务「开始前」的数据状态记录的是更新之前的值
事务提交之前发生了崩溃重启后会通过 undo log 回滚事务事务提交之后发生了崩溃重启后会通过 redo log 恢复事务
所以有了 redo log再通过 WAL 技术InnoDB 就可以保证即使数据库发生异常重启之前已提交的记录都不会丢失这个能力称为 crash-safe崩溃恢复。写入 redo log 的方式使用了追加操作 所以磁盘操作是顺序写而写入数据需要先找到写入位置然后才写到磁盘所以磁盘操作是随机写。可以说这是 WAL 技术的另外一个优点MySQL 的写操作从磁盘的「随机写」变成了「顺序写」。MySQL会定期将内存中的数据刷新到磁盘同时将最新的LSNLog Sequence Number记录到磁盘中这个LSN可以确保redo log中的操作是按顺序执行的。在恢复数据时系统会根据LSN来确定从哪个位置开始应用redo log。
MySQL会定期将内存中的数据刷新到磁盘同时将最新的LSNLog Sequence Number记录到磁盘中这个LSN可以确保redo log中的操作是按顺序执行的。在恢复数据时系统会根据LSN来确定从哪个位置开始应用redo log。 能不能只用binlog不用redo log
不行binlog是 server 层的日志没办法记录哪些脏页还没有刷盘redolog 是存储引擎层的日志可以记录哪些脏页还没有刷盘这样崩溃恢复的时候就能恢复那些还没有被刷盘的脏页数据。
binlog 两阶段提交过程是怎么样的
事务提交后redo log 和 binlog 都要持久化到磁盘但是这两个是独立的逻辑可能出现半成功的状态这样就造成两份日志之间的逻辑不一致。开启 binlog 的情况下MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log为了保证这两个日志的一致性MySQL 使用了内部 XA 事务内部 XA 事务由 binlog 作为协调者存储引擎是参与者。
当客户端执行 commit 语句或者在自动提交的情况下MySQL 内部开启一个 XA 事务分两阶段来完成 XA 事务的提交。事务的提交过程有两个阶段就是将 redo log 的写入拆成了两个步骤prepare 和 commit中间再穿插写入binlog
prepare 阶段将 XID内部 XA 事务的 ID 写入到 redo log同时将 redo log 对应的事务状态设置为 prepare然后将 redo log 持久化到磁盘innodb_flush_log_at_trx_commit 1 的作用commit 阶段把 XID 写入到 binlog然后将 binlog 持久化到磁盘sync_binlog 1 的作用接着调用引擎的提交事务接口将 redo log 状态设置为 commit此时该状态并不需要持久化到磁盘只需要 write 到文件系统的 page cache 中就够了因为只要 binlog 写磁盘成功就算 redo log 的状态还是 prepare 也没有关系一样会被认为事务已经执行成功 在 MySQL 重启后会按顺序扫描 redo log 文件碰到处于 prepare 状态的 redo log就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID
如果 binlog 中没有当前内部 XA 事务的 XID说明 redolog 完成刷盘但是 binlog 还没有刷盘则回滚事务。如果 binlog 中有当前内部 XA 事务的 XID说明 redolog 和 binlog 都已经完成了刷盘则提交事务。
可以看到对于处于 prepare 阶段的 redo log即可以提交事务也可以回滚事务这取决于是否能在 binlog 中查找到与 redo log 相同的 XID如果有就提交事务如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。
所以说两阶段提交是以 binlog 写成功为事务提交成功的标识因为 binlog 写成功了就意味着能在 binlog 中查找到与 redo log 相同的 XID。 update语句的具体执行过程是怎样的
具体更新一条记录 UPDATE t_user SET name xiaolin WHERE id 1; 的流程如下:
执行器负责具体执行会调用存储引擎的接口通过主键索引树搜索获取 id 1 这一行记录 如果 id1 这一行所在的数据页本来就在 buffer pool 中就直接返回给执行器更新如果记录不在 buffer pool将数据页从磁盘读入到 buffer pool返回记录给执行器。执行器得到聚簇索引记录后会看一下更新前的记录和更新后的记录是否一样 如果一样的话就不进行后续更新流程如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层让 InnoDB 真正的执行更新记录的操作开启事务 InnoDB 层更新记录前首先要记录相应的 undo log因为这是更新操作需要把被更新的列的旧值记下来也就是要生成一条 undo logundo log 会写入 Buffer Pool 中的 Undo 页面不过在内存修改该 Undo 页面后需要记录对应的 redo log。InnoDB 层开始更新记录会先更新内存同时标记为脏页然后将记录写到 redo log 里面这个时候更新就算完成了。为了减少磁盘I/O不会立即将脏页写入磁盘后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术MySQL 的写操作并不是立刻写到磁盘上而是先写 redo 日志然后在合适的时间再将修改的行数据写到磁盘上。至此一条记录更新完了。在一条更新语句执行完成后然后开始记录该语句对应的 binlog此时记录的 binlog 会被保存到 binlog cache并没有刷新到硬盘上的 binlog 文件在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。事务提交为了方便说明这里不说组提交的过程只说两阶段提交 prepare 阶段将 redo log 对应的事务状态设置为 prepare然后将 redo log 刷新到硬盘commit 阶段将 binlog 刷新到磁盘接着调用引擎的提交事务接口将 redo log 状态设置为 commit将事务设置为 commit 状态后刷入到磁盘 redo log 文件 mysql的explain有什么作用
explain 是查看 sql 的执行计划主要用来分析 sql 语句的执行过程比如有没有走索引有没有外部排序有没有索引覆盖等等。 对于执行计划参数有
possible_keys 字段表示可能用到的索引key 字段表示实际用的索引如果这一项为 NULL说明没有使用索引key_len 表示索引的长度rows 表示扫描的数据行数。type 表示数据扫描类型我们需要重点看这个。 type 字段就是描述了找到所需数据时使用的扫描方式是什么常见扫描类型的执行效率从低到高的顺序为
All全表扫描在这些情况里all 是最坏的情况因为采用了全表扫描的方式。index全索引扫描index 和 all 差不多只不过 index 对索引表进行全扫描这样做的好处是不再需要对数据进行排序但是开销依然很大。所以要尽量避免全表扫描和全索引扫描。range索引范围扫描range 表示采用了索引范围扫描一般在 where 子句中使用 、、in、between 等关键词只检索给定范围的行属于范围查找。从这一级别开始索引的作用会越来越明显因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。ref非唯一索引扫描ref 类型表示采用了非唯一索引或者是唯一索引的非唯一性前缀返回数据返回可能是多条。因为虽然使用了索引但该索引列的值并不唯一有重复。这样即使使用索引快速查找到了第一条数据仍然不能停止要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表因为索引是有序的即便有重复值也是在一个非常小的范围内扫描。eq_ref唯一索引扫描eq_ref 类型是使用主键或唯一索引时产生的访问方式通常使用在多表联查中。比如对两张表进行联查关联条件是两张表的 user_id 相等且 user_id 是唯一索引那么使用 EXPLAIN 进行执行计划查看的时候type 就会显示 eq_ref。const结果只有一条的主键或唯一索引扫描const 类型表示使用了主键或者唯一索引与常量值进行比较比如 select name from product where id1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引不过这两个类型有所区别const 是与常量进行比较查询效率会更快而 eq_ref 通常用于多表联查中。
extra 显示的结果这里说几个重要的参考指标
Using filesort 当查询语句中包含 group by 操作而且无法利用索引完成排序操作的时候 这时不得不选择相应的排序算法进行甚至可能会通过文件排序效率是很低的所以要避免这种问题的出现。Using temporary使了用临时表保存中间结果MySQL 在对查询结果排序时使用临时表常见于排序 order by 和分组查询 group by。效率低要避免这种问题的出现。Using index所需数据只需在索引即可全部获得不须要再到表中取数据也就是使用了覆盖索引避免了回表操作效率不错 发现查询速度很慢你有那些解决方案
分析查询语句使用EXPLAIN命令分析SQL执行计划找出慢查询的原因比如是否使用了全表扫描是否存在索引未被利用的情况等并根据相应情况对索引进行适当修改。创建或优化索引根据查询条件创建合适的索引特别是经常用于WHERE子句的字段、Orderby 排序的字段、Join 连表查询的字典、 group by的字段并且如果查询中经常涉及多个字段考虑创建联合索引使用联合索引要符合最左匹配原则不然会索引失效**避免索引失效**比如不要用左模糊匹配、函数计算、表达式计算等等。查询优化避免使用SELECT *只查询真正需要的列使用覆盖索引即索引包含所有查询的字段联表查询最好要以小表驱动大表并且被驱动表的字段要有索引当然最好通过冗余字段的设计避免联表查询。**分页优化**针对 limit n,y 深分页的查询优化可以把Limit查询转换成某个位置的查询select * from tb_sku where id20000 limit 10该方案适用于主键自增的表优化数据库表如果单表的数据超过了千万级别考虑是否需要将大表拆分为小表减轻单个表的查询压力。也可以将字段多的表分解成多个表有些字段使用频率高有些低数据量大时会由于使用频率低的存在而变慢可以考虑分开。使用缓存技术引入缓存层如Redis存储热点数据和频繁查询的结果但是要考虑缓存一致性的问题对于读请求会选择旁路缓存策略对于写请求会选择先更新 db再删除缓存的策略。 如果Explain用到的索引不正确的话有什么办法干预吗
可以使用 force index强制走索引。
主从延迟都有什么处理方法
强制走主库方案对于大事务或资源密集型操作直接在主库上执行避免从库的额外延迟。 MySQL主从复制
MySQL 的主从复制依赖于 binlog 也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。
MySQL 集群的主从复制过程梳理成 3 个阶段
写入 Binlog主库写 binlog 日志提交事务并更新本地存储数据。同步 Binlog把 binlog 复制到所有从库上每个从库把 binlog 写到暂存日志中。回放 Binlog回放 binlog并更新存储引擎中的数据。
具体详细过程如下
MySQL 主库在收到客户端提交事务的请求之后会先写入 binlog再提交事务更新存储引擎中的数据事务提交完成后返回给客户端“操作成功”的响应。从库会创建一个专门的 I/O 线程连接主库的 log dump 线程来接收主库的 binlog 日志再把 binlog 信息写入 relay log 的中继日志里再返回给主库“复制成功”的响应。从库会创建一个用于回放 binlog 的线程去读 relay log 中继日志然后回放 binlog 更新存储引擎中的数据最终实现主从的数据一致性。
在完成主从复制之后你就可以在写数据时只写主库在读数据时只读从库这样即使写请求会锁表或者锁记录也不会影响读请求的执行。 分表和分库是什么有什么区别
垂直分库一般来说按照业务和功能的维度进行拆分将不同业务数据分别放到不同的数据库中核心理念 专库专用。按业务类型对数据分离剥离为多个数据库像订单、支付、会员、积分相关等表放在对应的订单库、支付库、会员库、积分库。垂直分库把一个库的压力分摊到多个库提升了一些数据库性能但并没有解决由于单表数据量过大导致的性能问题所以就需要配合后边的分表来解决。垂直分表针对业务上字段比较多的大表进行的一般是把业务宽表中比较独立的字段或者不常用的字段拆分到单独的数据表中是一种大表拆小表的模式。数据库它是以行为单位将数据加载到内存中这样拆分以后核心表大多是访问频率较高的字段而且字段长度也都较短因而可以加载更多数据到内存中减少磁盘IO增加索引查询的命中率进一步提升数据库性能。水平分库是把同一个表按一定规则拆分到不同的数据库中每个库可以位于不同的服务器上以此实现水平扩展是一种常见的提升数据库性能的方式。这种方案往往能解决单库存储量及性能瓶颈问题但由于同一个表被分配在不同的数据库中数据的访问需要额外的路由工作因此系统的复杂度也被提升了。水平分表是在同一个数据库内把一张大数据量的表按一定规则切分成多个结构完全相同表而每个表只存原表的一部分数据。水平分表尽管拆分了表但子表都还是在同一个数据库实例中只是解决了单一表数据量过大的问题并没有将拆分后的表分散到不同的机器上还在竞争同一个物理机的CPU、内存、网络IO等。要想进一步提升性能就需要将拆分后的表分散到不同的数据库中达到分布式的效果。 文章转载自： http://www.morning.gskzy.cn.gov.cn.gskzy.cn http://www.morning.mnyzz.cn.gov.cn.mnyzz.cn http://www.morning.qpntn.cn.gov.cn.qpntn.cn http://www.morning.rxwnc.cn.gov.cn.rxwnc.cn http://www.morning.pclgj.cn.gov.cn.pclgj.cn http://www.morning.bkkgt.cn.gov.cn.bkkgt.cn http://www.morning.xdpjs.cn.gov.cn.xdpjs.cn http://www.morning.wcft.cn.gov.cn.wcft.cn http://www.morning.gbljq.cn.gov.cn.gbljq.cn http://www.morning.cwqln.cn.gov.cn.cwqln.cn http://www.morning.080203.cn.gov.cn.080203.cn http://www.morning.tgtrk.cn.gov.cn.tgtrk.cn http://www.morning.mkygc.cn.gov.cn.mkygc.cn http://www.morning.drjll.cn.gov.cn.drjll.cn http://www.morning.wjmb.cn.gov.cn.wjmb.cn http://www.morning.jzxqj.cn.gov.cn.jzxqj.cn http://www.morning.nfbxgtj.com.gov.cn.nfbxgtj.com http://www.morning.qgmwt.cn.gov.cn.qgmwt.cn http://www.morning.pxmyw.cn.gov.cn.pxmyw.cn http://www.morning.xtqld.cn.gov.cn.xtqld.cn http://www.morning.mrnnb.cn.gov.cn.mrnnb.cn http://www.morning.dskzr.cn.gov.cn.dskzr.cn http://www.morning.trqhd.cn.gov.cn.trqhd.cn http://www.morning.yrcxg.cn.gov.cn.yrcxg.cn http://www.morning.ghphp.cn.gov.cn.ghphp.cn http://www.morning.yxplz.cn.gov.cn.yxplz.cn http://www.morning.juju8.cn.gov.cn.juju8.cn http://www.morning.qrpx.cn.gov.cn.qrpx.cn http://www.morning.hhxpl.cn.gov.cn.hhxpl.cn http://www.morning.supera.com.cn.gov.cn.supera.com.cn http://www.morning.lfsmf.cn.gov.cn.lfsmf.cn http://www.morning.rgsgk.cn.gov.cn.rgsgk.cn http://www.morning.qsmdd.cn.gov.cn.qsmdd.cn http://www.morning.rgnq.cn.gov.cn.rgnq.cn http://www.morning.bnxfj.cn.gov.cn.bnxfj.cn http://www.morning.rhgtc.cn.gov.cn.rhgtc.cn http://www.morning.pqhgn.cn.gov.cn.pqhgn.cn http://www.morning.hotlads.com.gov.cn.hotlads.com http://www.morning.spdyl.cn.gov.cn.spdyl.cn http://www.morning.ddgl.com.cn.gov.cn.ddgl.com.cn http://www.morning.yqqxj26.cn.gov.cn.yqqxj26.cn http://www.morning.kcypc.cn.gov.cn.kcypc.cn http://www.morning.phlrp.cn.gov.cn.phlrp.cn http://www.morning.zbkdm.cn.gov.cn.zbkdm.cn http://www.morning.zcwzl.cn.gov.cn.zcwzl.cn http://www.morning.dmjhp.cn.gov.cn.dmjhp.cn http://www.morning.qgmwt.cn.gov.cn.qgmwt.cn http://www.morning.kfyqd.cn.gov.cn.kfyqd.cn http://www.morning.hrdx.cn.gov.cn.hrdx.cn http://www.morning.dmkhd.cn.gov.cn.dmkhd.cn http://www.morning.yysqz.cn.gov.cn.yysqz.cn http://www.morning.xcyhy.cn.gov.cn.xcyhy.cn http://www.morning.ggfdq.cn.gov.cn.ggfdq.cn http://www.morning.jcffp.cn.gov.cn.jcffp.cn http://www.morning.khxyx.cn.gov.cn.khxyx.cn http://www.morning.zbkwj.cn.gov.cn.zbkwj.cn http://www.morning.wrbf.cn.gov.cn.wrbf.cn http://www.morning.csxlm.cn.gov.cn.csxlm.cn http://www.morning.glwyn.cn.gov.cn.glwyn.cn http://www.morning.tkrwm.cn.gov.cn.tkrwm.cn http://www.morning.wqbhx.cn.gov.cn.wqbhx.cn http://www.morning.fmznd.cn.gov.cn.fmznd.cn http://www.morning.ptqpd.cn.gov.cn.ptqpd.cn http://www.morning.rnmmh.cn.gov.cn.rnmmh.cn http://www.morning.xxlz.cn.gov.cn.xxlz.cn http://www.morning.xgmf.cn.gov.cn.xgmf.cn http://www.morning.hclplus.com.gov.cn.hclplus.com http://www.morning.tsyny.cn.gov.cn.tsyny.cn http://www.morning.chbcj.cn.gov.cn.chbcj.cn http://www.morning.bpmnz.cn.gov.cn.bpmnz.cn http://www.morning.nqlkb.cn.gov.cn.nqlkb.cn http://www.morning.xrwbc.cn.gov.cn.xrwbc.cn http://www.morning.zlgth.cn.gov.cn.zlgth.cn http://www.morning.rycbz.cn.gov.cn.rycbz.cn http://www.morning.jgttx.cn.gov.cn.jgttx.cn http://www.morning.fkflc.cn.gov.cn.fkflc.cn http://www.morning.xlbtz.cn.gov.cn.xlbtz.cn http://www.morning.fyglr.cn.gov.cn.fyglr.cn http://www.morning.hflrz.cn.gov.cn.hflrz.cn http://www.morning.sgfpn.cn.gov.cn.sgfpn.cn
