无锡做网站 选众诺,百度快照优化公司,婚纱网站开发,姓氏logo免费设计在线生成文章目录 一、SQL1、介绍一下数据库分页2、介绍一下SQL中的聚合函数3、表跟表是怎么关联的?4、说一说你对外连接的了解#xff1f;5、说一说数据库的左连接和右连接#xff1f;6、SQL中怎么将行转成列#xff1f;7、谈谈你对SQL注入的理解#xff1f;8、将一张表的部分数据… 文章目录 一、SQL1、介绍一下数据库分页2、介绍一下SQL中的聚合函数3、表跟表是怎么关联的?4、说一说你对外连接的了解5、说一说数据库的左连接和右连接6、SQL中怎么将行转成列7、谈谈你对SQL注入的理解8、将一张表的部分数据更新到另一张表该如何操作呢9、WHERE和HAVING有什么区别?二、索引10、说一说你对MySQL索引的理解11、索引有哪几种12、如何创建及保存MySQL的索引13、MySQL怎么判断要不要加索引14、只要创建了索引就一定会走索引吗15、如何判断数据库的索引有没有生效16、如何评估一个索引创建的是否合理17、索引是越多越好吗18、数据库索引失效了怎么办19、所有的字段都适合创建索引吗20、说一说索引的实现原理21、介绍一下数据库索引的重构过程22、MySQL的索引为什么用B树23、联合索引的存储结构是什么它的有效方式是什么24、MySQL的Hash索引和B树索引有什么区别25、聚簇索引和非聚簇索引有什么区别26、什么是联合索引27、select in语句中如何使用索引28、模糊查询语句中如何使用索引三、事务29、说一说你对数据库事务的了解30、事务有哪几种类型它们之间有什么区别31、MySQL的ACID特性分别是怎么实现的32、谈谈MySQL的事务隔离级别?33、MySQL的事务隔离级别是怎么实现的34、事务可以嵌套吗35、如何实现可重复读36、MySQL事务如何回滚四、锁38、了解数据库的锁吗39、介绍一下间隙锁40、InnoDB中行级锁是怎么实现的41、数据库在什么情况下会发生死锁42、说说数据库死锁的解决办法五、优化43、说一说你对数据库优化的理解44、该如何优化MySQL的查询45、怎样插入数据才能更高效46、 表中包含几千万条数据该怎么办47、 MySQL的慢查询优化有了解吗48、说一说你对explain的了解49、explain关注什么六、其他50、介绍一下数据库设计的三大范式51、说一说你对MySQL引擎的了解52、说一说你对redo log、undo log、binlog的了解53、谈谈你对MVCC的了解54、MySQL主从同步是如何实现的 一、SQL
1、介绍一下数据库分页
MySQL的分页语法 在MySQL中SELECT语句默认返回所有匹配的行它们可能是指定表中的每个行。为了返回第一行或前几行可使用LIMIT子句以实现分页查询。LIMIT子句的语法如下
-- 在所有的查询结果中返回前5行记录。
SELECT prod_name FROM products LIMIT 5;
-- 在所有的查询结果中从第5行开始返回5行记录。
SELECT prod_name FROM products LIMIT 5,5;总之带一个值的LIMIT总是从第一行开始给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。 优化LIMIT分页 在偏移量非常大的时候例如 LIMIT 10000,20 这样的查询这时MySQL需要查询10020条记录然后只返回最后20条前面的10000条记录都将被抛弃这样的代价是非常高的。如果所有的页面被访问的频率都相同那么这样的查询平均需要访问半个表的数据。要优化这种查询要么是在页面中限制分页的数量要么是优化大偏移量的性能。 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的列然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会提升非常大。考虑下面的查询
SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;如果这个表非常大那么这个查询最好改写成下面的样子
SELECT film.film_id,film.description
FROM sakila.film
INNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5
) AS lim USING(film_id);这里的“延迟关联”将大大提升查询效率它让MySQL扫描尽可能少的页面获取需要访问的记录后再根 据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。有时候也可以将LIMIT查询转换为已知位置的查询让MySQL通过范围扫描获得对应的结果。例如如果在一个位置列上有索引并且预先计算出了边界值上面的查询就可以改写为
SELECT film_id,description FROM skila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;对数据进行排名的问题也与此类似但往往还会同时和GROUP BY混合使用在这种情况下通常都需要预先计算并存储排名信息。 LIMIT和OFFSET的问题其实是OFFSET的问题它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数的位置那么下次就可以直接从该书签记录的位置开始扫描这样就可以避免使用OFFSET。例如若需要按照租赁记录做翻页那么可以根据最新一条租赁记录向后追溯这种做法可行是因为租赁记录的主键是单调增长的。首先使用下面的查询获得第一组结果
SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;假设上面的查询返回的是主键16049到16030的租赁记录那么下一页查询就可以从16030这个点开始
SELECT * FROM sakila.rental
WHERE rental_id 16030 ORDER BY rental_id DESC LIMIT 20;该技术的好处是无论翻页到多么后面其性能都会很好。
2、介绍一下SQL中的聚合函数
常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()下面以MySQL为例说明这些函数的作用。
COUNT()函数 COUNT()函数统计数据表中包含的记录行的总数或者根据查询结果返回列中包含的数据行数它有两种用法 COUNT(*)计算表中总的行数不管某列是否有数值或者为空值。 COUNT(字段名)计算指定列下总的行数计算时将忽略空值的行。 COUNT()函数可以与GROUP BY一起使用来计算每个分组的总和。AVG()函数() AVG()函数通过计算返回的行数和每一行数据的和求得指定列数据的平均值。 AVG()函数可以与GROUP BY一起使用来计算每个分组的平均值。SUM()函数 SUM()是一个求总和的函数返回指定列值的总和。 SUM()可以与GROUP BY一起使用来计算每个分组的总和!MAX()函数 MAX()返回指定列中的最大值。 MAX()也可以和GROUP BY关键字一起使用求每个分组中的最大值。 MAX()函数不仅适用于查找数值类型也可应用于字符类型。MIN()函数: MIN()返回查询列中的最小值。 MIN()也可以和GROUP BY关键字一起使用求出每个分组中的最小值。 MIN()函数与MAX()函数类似不仅适用于查找数值类型也可应用于字符类型。
3、表跟表是怎么关联的?
表与表之间常用的关联方式有两种内连接、外连接下面以MySQL为例来说明这两种连接方式。 内连接 内连接通过INNER JOIN来实现它将返回两张表中满足连接条件的数据不满足条件的数据不会查询出来。 外连接 外连接通过OUTER JOIN来实现它会返回两张表中满足连接条件的数据同时返回不满足连接条件的数据。外连接有两种形式左外连接LEFT OUTER JOIN、右外连接RIGHT OUTER JOIN。 左外连接可以简称为左连接LEFT JOIN它会返回左表中的所有记录和右表中满足连接条件的记录。 右外连接可以简称为右连接RIGHT JOIN它会返回右表中的所有记录和左表中满足连接条件的记录。 除此之外还有一种常见的连接方式等值连接。这种连接是通过WHERE子句中的条件将两张表连接在一起它的实际效果等同于内连接。出于语义清晰的考虑一般更建议使用内连接而不是等值连接。 以上是从语法上来说明表与表之间关联的实现方式而从表的关系上来说比较常见的关联关系有一对多关联、多对多关联、自关联。 一对多关联这种关联形式最为常见一般是两张表具有主从关系并且以主表的主键关联从表的外键来实现这种关联关系。另外以从表的角度来看它们是具有多对一关系的所以不再赘述多对一关联了。 多对多关联这种关联关系比较复杂如果两张表具有多对多的关系那么它们之间需要有一张中间表来作为衔接以实现这种关联关系。这个中间表要设计两列分别存储那两张表的主键。因此这两张表中的任何一方都与中间表形成了一对多关系从而在这个中间表上建立起了多对多关系。 自关联自关联就是一张表自己与自己相关联为了避免表名的冲突需要在关联时通过别名将它们当做两张表来看待。一般在表中数据具有层级树状时可以采用自关联一次性查询出多层级的数据。
4、说一说你对外连接的了解
外连接通过OUTER JOIN来实现它会返回两张表中满足连接条件的数据同时返回不满足连接条件的数据。常见的外连接有两种形式左外连接LEFT OUTER JOIN、右外连接RIGHT OUTER JOIN。 左外连接可以简称为左连接LEFT JOIN它会返回左表中的所有记录和右表中满足连接条件的记录。 右外连接可以简称为右连接RIGHT JOIN它会返回右表中的所有记录和左表中满足连接条件的记录。 实际上外连接还有一种形式完全外连接FULL OUTER JOIN但MySQL不支持这种形式。
5、说一说数据库的左连接和右连接
外连接通过OUTER JOIN来实现它会返回两张表中满足连接条件的数据同时返回不满足连接条件的数据。常见的外连接有两种形式左外连接LEFT OUTER JOIN、右外连接RIGHT OUTER JOIN。
左外连接可以简称为左连接LEFT JOIN它会返回左表中的所有记录和右表中满足连接条件的记录。右外连接可以简称为右连接RIGHT JOIN它会返回右表中的所有记录和左表中满足连接条件的记录。
6、SQL中怎么将行转成列
我们以MySQL数据库为例来说明行转列的实现方式。 首先假设我们有一张分数表tb_score表中的数据如下图 然后我们再来看一下转换之后需要得到的结果如下图 可以看出这里行转列是将原来的subject字段的多行内容选出来作为结果集中的不同列并根据userid进行分组显示对应的score。通常我们有两种方式来实现这种转换。
使用 CASE…WHEN…THEN 语句实现行转列参考如下代码
SELECT userid,
SUM(CASE subject WHEN 语文 THEN score ELSE 0 END) as 语文,
SUM(CASE subject WHEN 数学 THEN score ELSE 0 END) as 数学,
SUM(CASE subject WHEN 英语 THEN score ELSE 0 END) as 英语,
SUM(CASE subject WHEN 政治 THEN score ELSE 0 END) as 政治
FROM tb_score
GROUP BY userid注意SUM() 是为了能够使用GROUP BY根据userid进行分组因为每一个userid对应的subject语文的记录只有一条所以SUM() 的值就等于对应那一条记录的score的值。假如userid‘001’ and subject‘语文’ 的记录有两条则此时SUM() 的值将会是这两条记录的和同理使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下一个user对应一个subject只有一个分数因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。 2. 使用 IF() 函数实现行转列参考如下代码
SELECT userid,
SUM(IF(subject语文,score,0)) as 语文,
SUM(IF(subject数学,score,0)) as 数学,
SUM(IF(subject英语,score,0)) as 英语,
SUM(IF(subject政治,score,0)) as 政治
FROM tb_score
GROUP BY userid注意 IF(subject‘语文’,score,0) 作为条件即对所有subject语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作如果score没有值则默认为0。
7、谈谈你对SQL注入的理解
SQL注入的原理是将SQL代码伪装到输入参数中传递到服务器解析并执行的一种攻击手法。也就是说在一些对SERVER端发起的请求参数中植入一些SQL代码SERVER端在执行SQL操作时会拼接对应参数同时也将一些SQL注入攻击的“SQL”拼接起来导致会执行一些预期之外的操作。 举个例子: 比如我们的登录功能其登录界面包括用户名和密码输入框以及提交按钮登录时需要输入用户名和密码然后提交。此时调用接口/user/login/ 加上参数username、password首先连接数据库然后后台对请求参数中携带的用户名、密码进行参数校验即SQL的查询过程。假设正确的用户名和密码为ls和123456输入正确的用户名和密码、提交相当于调用了以下的SQL语句。
SELECT * FROM user WHERE username ls AND password 123456SQL中会将#及–以后的字符串当做注释处理如果我们使用 ’ or 11 # 作为用户名参数那么服务端构建的SQL语句就如下
select * from user where username or 11 # and password123456而#会忽略后面的语句而11属于常等型条件因此这个SQL将查询出所有的登录用户。其实上面的SQL注入只是在参数层面做了些手脚如果是引入了一些功能性的SQL那就更危险了比如上面的登录功能如果用户名使用这个 ’ or 11;delete * from users; # 那么在;之后相当于是另外一条新的SQL这个SQL是删除全表是非常危险的操作因此SQL注入这种还是需要特别注意的
8、将一张表的部分数据更新到另一张表该如何操作呢
可以采用关联更新的方式将一张表的部分数据更新到另一张表内。参考如下代码
update b set b.cola.col from a,b where a.idb.id;
update b set cola.col from b inner join a on a.idb.id;
update b set b.cola.col from b left Join a on b.id a.id;9、WHERE和HAVING有什么区别?
WHERE是一个约束声明使用WHERE约束来自数据库的数据WHERE是在结果返回之前起作用的WHERE中不能使用聚合函数。 HAVING是一个过滤声明是在查询返回结果集以后对查询结果进行的过滤操作在HAVING中可以使用 聚合函数。另一方面HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。 从性能的角度来说HAVING子句中如果使用了分组字段作为过滤条件应该替换成WHERE子句。因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据性能会更好。
二、索引
10、说一说你对MySQL索引的理解
索引是一个单独的、存储在磁盘上的数据库结构包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行所有MySQL列类型都可以被索引对相关列使用索引是提高查询操作速度的最佳途径。 索引是在存储引擎中实现的因此每种存储引擎的索引都不一定完全相同并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种即BTREE和HASH具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持BTREE索引MEMORY/HEAP存储引擎可以支持HASH和BTREE 索引。 索引的优点主要有以下几条
通过创建唯一索引可以保证数据库表中每一行数据的唯一性。可以大大加快数据的查询速度这也是创建索引的主要原因。在实现数据的参考完整性方面可以加速表和表之间的连接。在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。
增加索引也有许多不利的方面主要表现在如下几个方面
创建索引和维护索引要耗费时间并且随着数据量的增加所耗费的时间也会增加。索引需要占磁盘空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。当对表中的数据进行增加、删除和修改的时候索引也要动态地维护这样就降低了数据的维护速度。
11、索引有哪几种
MySQL的索引可以分为以下几类
普通索引和唯一索引 普通索引是MySQL中的基本索引类型允许在定义索引的列中插入重复值和空值。唯一索引要求索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。主键索引是一种特殊的唯一索引不允许有空值。单列索引和组合索引 单列索引即一个索引只包含单个列一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引只有在查询条件中使用了这些字段的左边字段时索引才会被使用。使用组合索引时遵循最左前缀集合。全文索引 全文索引类型为FULLTEXT在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。空间索引 空间索引是对空间数据类型的字段建立的索引MySQL中的空间数据类型有4种分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列必须将其声明为NOT NULL空间索引只能在存储引擎为MyISAM的表中创建。
12、如何创建及保存MySQL的索引
MySQL支持多种方法在单个或多个列上创建索引 在创建表的时候创建索引 使用CREATE TABLE创建表时除了可以定义列的数据类型还可以定义主键约束、外键约束或者唯一性约束而不论创建哪种约束在定义约束的同时相当于在指定列上创建了一个索引。创建表时创建索引的基本语法如下
CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length])
[ASC|DESC]其中UNIQUE、FULLTEXT和SPATIAL为可选参数分别表示唯一索引、全文索引和空间索引INDEX与KEY为同义词两者作用相同用来指定创建索引。 例如可以按照如下方式在id字段上使用UNIQUE关键字创建唯一索引
CREATE TABLE t1 (
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);在已存在的表上创建索引 在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATEINDEX语句。 ALTER TABLE创建索引的基本语法如下
ALTER TABLE table_name ADD
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...)
[ASC|DESC]例如可以按照如下方式在bookId字段上建立名称为UniqidIdx的唯一索引
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId);CREATE INDEX创建索引的基本语法如下
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (col_name [length],...) [ASC|DESC]例如可以按照如下方式在bookId字段上建立名称为UniqidIdx的唯一索引
CREATE UNIQUE INDEX UniqidIdx ON book (bookId);13、MySQL怎么判断要不要加索引
建议按照如下的原则来创建索引
当唯一性是某种数据本身的特征时指定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度。在频繁进行排序或分组即进行group by或order by操作的列上建立索引如果待排序的列有多个可以在这些列上建立组合索引。
14、只要创建了索引就一定会走索引吗
不一定。 比如在使用组合索引的时候如果没有遵从“最左前缀”的原则进行搜索则索引是不起作用的。 举例假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放索引可以搜索id、id,name、id, name, age字段组合。如果列不构成索引最左面的前缀那么MySQL不能使用局部索引如age或者name,age组合则不能使用该索引查询。
15、如何判断数据库的索引有没有生效
可以使用EXPLAIN语句查看索引是否正在使用。 举例假设已经创建了book表并已经在其year_publication字段上建立了普通索引。执行如下语句
EXPLAIN SELECT * FROM book WHERE year_publication1990;EXPLAIN语句将为我们输出详细的SQL执行信息其中
possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
key行是MySQL实际选用的索引。如果possible_keys行和key行都包含year_publication字段则说明在查询时使用了该索引。
16、如何评估一个索引创建的是否合理
建议按照如下的原则来设计索引
避免对经常更新的表进行过多的索引并且索引中的列要尽可能少。应该经常用于查询的字段创建索引但要避免添加不必要的字段。数据量小的表最好不要使用索引由于数据较少查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。在条件表达式中经常用到的不同值较多的列上建立索引在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值因此就无须建立索引如果建立索引不但不会提高查询效率反而会严重降低数据更新速度。当唯一性是某种数据本身的特征时指定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度。在频繁进行排序或分组即进行group by或order by操作的列上建立索引如果待排序的列有多个可以在这些列上建立组合索引。
17、索引是越多越好吗
索引并非越多越好一个表中如有大量的索引不仅占用磁盘空间还会影响INSERT、DELETE、UPDATE等语句的性能因为在表中的数据更改时索引也会进行调整和更新。
18、数据库索引失效了怎么办
可以采用以下几种方式来避免索引失效
使用组合索引时需要遵循“最左前缀”原则不在索引列上做任何操作例如计算、函数、类型转换会导致索引失效而转向全表扫描尽量使用覆盖索引之访问索引列的查询减少 select * 覆盖索引能减少回表次数MySQL在使用不等于!或者的时候无法使用索引会导致全表扫描LIKE以通配符开头%abcMySQL索引会失效变成全表扫描的操作字符串不加单引号会导致索引失效可能发生了索引列的隐式转换少用or用它来连接时会索引失效。
19、所有的字段都适合创建索引吗
不是。 下列几种情况是不适合创建索引的
频繁更新的字段不适合建立索引where条件中用不到的字段不适合建立索引数据比较少的表不需要建索引数据重复且分布比较均匀的的字段不适合建索引例如性别、真假值参与列计算的列不适合建索引。
20、说一说索引的实现原理
在MySQL中索引是在存储引擎层实现的不同存储引擎对索引的实现方式是不同的下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。 MyISAM索引实现 MyISAM引擎使用BTree作为索引结构叶节点的data域存放的是数据记录的地址MyISAM索引的原理图如下。这里假设表一共有三列假设我们以Col1为主键则上图是一个MyISAM表的主索引Primary key示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中主索引和辅助索引Secondary key在结构上没有任何区别只是主索引要求key是唯一的而辅助索引的key可以重复。 如果我们在Col2上建立一个辅助索引则此索引的结构如下图所示。同样也是一颗BTreedata域保存数据记录的地址。因此MyISAM中索引检索的算法为首先按照BTree搜索算法搜索索引如果指定的Key存在则取出其data域的值然后以data域的值为地址读取相应数据记录。 InnoDB索引实现 虽然InnoDB也使用BTree作为索引结构但具体实现方式却与MyISAM截然不同。 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道MyISAM索引文件和数据文件是分离的索引文件仅保存数据记录的地址。而在InnoDB中表数据文件本身就是按BTree组织的一个索引结构这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键因此 InnoDB表数据文件本身就是主索引。
下图是InnoDB主索引同时也是数据文件的示意图可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集所以InnoDB要求表必须有主键MyISAM可以没有如果没有显式指定则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整形。 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效但是辅助索引搜索 需要检索两遍索引首先检索辅助索引获得主键然后用主键到主索引中检索获得记录。 了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助例如知道了InnoDB的索引实现后就很容易明白为什么不建议使用过长的字段作为主键因为所有辅助索引都引用主索引过长的主索引会令辅助索引变得过大。再例如用非单调的字段作为主键在InnoDB中不是个好主意因为InnoDB数据文件本身是一颗BTree非单调的主键会造成在插入新记录时数据文件为了维持BTree的特性而频繁的分裂调整十分低效而使用自增字段作为主键则是一个很好的选择。
21、介绍一下数据库索引的重构过程
什么时候需要重建索引呢
表上频繁发生update,delete操作表上发生了alter table …move操作move操作导致了rowid变化。
怎么判断索引是否应该重建
一般看索引是否倾斜的严重是否浪费了空间对索引进行结构分析
analyze index index_name validate structure;在相同的session中查询index_stats表
select height,DEL_LF_ROWS/LF_ROWS from index_stats;当查询的height4索引的深度即从根到叶节点的高度或DEL_LF_ROWS/LF_ROWS0.2的情况下就应该考虑重建该索引。
如何重建索引
drop原索引然后再创建索引
drop index index_name;
create index index_name on table_name (index_column);这种方式相当耗时一般不建议使用! 直接重建索引
alter index indexname rebuild;
alter index indexname rebuild online;此方法较快建议使用。 rebuild是快速重建索引的一种有效的办法因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在因此使用这种重建方法需要有额外的磁盘空间可供临时使用当索引建完后把老索引删除如果没有成功也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。
rebuild重建索引的过程
Rebuild以index fast full scan或table full scan方式采用那种方式取决于cost读取原索引中的数据来构建一个新的索引重建过程中有排序操作rebuild online执行表扫描获取数据重建过程中有排序的操作Rebuild会阻塞DML操作rebuild online不会阻塞DML操作rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表所有rebuildonline时索引的变化都记录在这个表中当新的索引创建完成后把这个表的记录维护到新的索引中去然后drop掉旧的索引rebuild online就完成了。
重建索引过程中的注意事项 4. 执行rebuild操作时需要检查表空间是否足够 5. 虽然说rebuild online操作允许DML操作但还是建议在业务不繁忙时间段进行 6. Rebuild操作会产生大量Redo Log
22、MySQL的索引为什么用B树
B树由B树和索引顺序访问方法演化而来它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树在B树中所有记录节点都是按键值的大小顺序存放在同一层的叶子节点各叶子节点通过指针进行链接。如下图 B树索引在数据库中的一个特点就是高扇出性例如在InnoDB存储引擎中每个页的大小为16KB。在数据库中B树的高度一般都在24层这意味着查找某一键值最多只需要2到4次IO操作这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作24次的IO操作意味着查询时间只需0.020.04 秒。
23、联合索引的存储结构是什么它的有效方式是什么
从本质上来说联合索引还是一棵B树不同的是联合索引的键值数量不是1而是大于等于2参考下图。另外只有在查询条件中使用了这些字段的左边字段时索引才会被使用所以使用联合索引时遵循最左前缀集合。
24、MySQL的Hash索引和B树索引有什么区别
hash索引底层就是hash表进行查找时调用一次hash函数就可以获取到相应的键值之后进行回表查询获得实际数据。B树底层实现是多路平衡查找树对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据。它们有以下的不同
hash索引进行等值查询更快(一般情况下)但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询。而B树的的所有节点皆遵循(左节点小于父节点右节点大于父节点多叉树也类似)天然支持范围。hash索引不支持使用索引进行排序原理同上。hash索引不支持模糊查询以及多列索引的最左前缀匹配原理也是因为hash函数的不可预测。hash索引任何时候都避免不了回表查询数据而B树在符合某些条件(聚簇索引覆盖索引等)的时候可以只通过索引完成查询。hash索引虽然在等值查询上较快但是不稳定性能不可预测当某个键值存在大量重复的时候发生hash碰撞此时效率可能极差。而B树的查询效率比较稳定对于所有的查询都是从根节点到叶子节点且树的高度较低。
因此在大多数情况下直接选择B树索引可以获得稳定且较好的查询速度。而不需要使用hash索引
25、聚簇索引和非聚簇索引有什么区别
聚集索引一个表只能有一个而非聚集索引一个表可以存在多个。聚集索引存储记录是物理上连续存在物理存储按照索引排序而非聚集索引是逻辑上的连续物理存储并不连续物理存储不按照索引排序。
平时习惯逛图书馆的童鞋可能比较清楚如果你要去图书馆借一本书最开始是去电脑里面查书名然后根据书名来定位藏书在那个区哪个书柜哪一行第多少本。。。清晰明确一目了然因为藏书的结构与图书室的位置书架的顺序书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的所以很容易找到。比如你的目标藏书在C区2柜3排5仓那么你走到B区你就很快知道前面就快到了C区了你直接奔着2柜区就能找到了。 这就是雷同于聚簇索引的功效了聚簇索引实际存储的循序结构与数据存储的物理机构是一致的所以通常来说物理顺序结构只有一种那么一个表的聚簇索引也只能有一个通常默认都是主键设置了主键系统默认就为你加上了聚簇索引当然有人说我不想拿主键作为聚簇索引我需要用其他字段作为索引当然这也是可以的这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引然后再设置主键这样就木有问题啦。 总而言之聚簇索引是顺序结构与数据存储物理结构一致的一种索引并且一个表的聚簇索引只能有唯一的一条
同样的如果你去的不是图书馆而是某城市的商业性质的图书城那么你想找的书就摆放比较随意了由于商业图书城空间比较紧正藏书通常按照藏书上架的先后顺序来摆放的所以如果查询到某书籍放在C区2柜3排5仓但你可能要绕过F区而不是A.B.C.D…连贯一致的也可能同在C区的2柜书柜上第一排是计算机类的书记也可能最后一排就是医学类书籍
那么对照着来看非聚簇索引的概念就比较好理解了非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系与数据的存储物理结构没有关系一个表对应的非聚簇索引可以有多条根据不同列的约束可以建立不同要求的非聚簇索引
26、什么是联合索引
联合索引是指对表上的多个列进行索引联合索引的创建方法与单个索引创建的方法一样不同之处仅在于有多个索引列。从本质上来说联合索引还是一棵B树不同的是联合索引的键值数量不是1而是大于等于2参考下图。另外只有在查询条件中使用了这些字段的左边字段时索引才会被使用所以使用联合索引时遵循最左前缀集合!
27、select in语句中如何使用索引
索引是否起作用主要取决于字段类型
如果字段类型为字符串需要给in查询中的数值与字符串值都需要添加引号索引才能起作用。如果字段类型为int则in查询中的值不需要添加引号索引也会起作用。 IN的字段在联合索引中按以上方法也会起作用。
28、模糊查询语句中如何使用索引
在MySQL中模糊查询 mobile like ‘%8765’ 这种情况是不能使用 mobile 上的索引的那么如果需 要根据手机号码后四位进行模糊查询可以用一下方法进行改造。
我们可以加入冗余列MySQL5.7之后加入了虚拟列使用虚拟列更合适思路相同比如
mobile_reverse内部存储为 mobile 的倒叙文本如 mobile为17312345678那么 mobile_reverse
存储 87654321371为 mobile_reverse 列建立索引查询中使用语句 mobile_reverse like
reverse(’%5678’) 即可。reverse 是 MySQL 中的反转函数这条语句相当于 mobile_reverse like ‘8765%’ 这种语句是可以使用索引的。三、事务
29、说一说你对数据库事务的了解
事务可由一条非常简单的SQL语句组成也可以由一组复杂的SQL语句组成。在事务中的操作要么都执行修改要么都不执行这就是事务的目的也是事务模型区别于文件系统的重要特征之一。 事务需遵循ACID四个特性 Aatomicity原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的 数据库操作都执行成功整个事务的执行才算成功。事务中任何一个SQL语句执行失败那么已经 执行成功的SQL语句也必须撤销数据库状态应该退回到执行事务前的状态。 Cconsistency一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务 开始之前和事务结束以后数据库的完整性约束没有被破坏。 Iisolation隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分 离即该事务提交前对其他事务都不可见这通常使用锁来实现。 Ddurability 持久性。事务一旦提交其结果就是永久性的即使发生宕机等故障数据库 也能将数据恢复。持久性保证的是事务系统的高可靠性而不是高可用性。
30、事务有哪几种类型它们之间有什么区别
事务可以分为以下几种类型
扁平事务是事务类型中最简单的一种而在实际生产环境中这可能是使用最为频繁的事务。在 扁平事务中所有操作都处于同一层次其由BEGIN WORK开始由COMMIT WORK或 ROLLBACK WORK结束。处于之间的操作是原子的要么都执行要么都回滚。带有保存点的扁平事务除了支持扁平事务支持的操作外允许在事务执行过程中回滚到同一事务 中较早的一个状态这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效 放弃整个事务不合乎要求开销也太大。保存点savepoint用来通知系统应该记住事务当前的 状态以便以后发生错误时事务能回到该状态。链事务可视为保存点模式的一个变种。链事务的思想是在提交一个事务时释放不需要的数据 对象将必要的处理上下文隐式地传给下一个要开始的事务。注意提交事务操作和开始下一个事 务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果就好像在一个事务 中进行的。嵌套事务是一个层次结构框架。有一个顶层事务top-level transaction控制着各个层次的事 务。顶层事务之下嵌套的事务被称为子事务subtransaction其控制每一个局部的变换。分布式事务通常是一个在分布式环境下运行的扁平事务因此需要根据数据所在位置访问网络中 的不同节点。对于分布式事务同样需要满足ACID特性要么都发生要么都失效。
对于MySQL的InnoDB存储引擎来说它支持扁平事务、带有保存点的扁平事务、链事务、分布式事 务。对于嵌套事务MySQL数据库并不是原生的因此对于有并行事务需求的用户来说MySQL就无能为力了但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。
31、MySQL的ACID特性分别是怎么实现的
原子性实现原理 实现原子性的关键是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚靠的是 undo log当事务对数据库进行修改时InnoDB会生成对应的undo log。如果事务执行失败或调用了 rollback导致事务需要回滚便可以利用undo log中的信息将数据回滚到修改之前的样子。undo log属于逻辑日志它记录的是sql执行相关的信息。当发生回滚时InnoDB会根据undo log的内容做与之前相反的工作。对于insert回滚时会执行delete。对于delete回滚时会执行insert。对于update回滚时则会执行相反的update把数据改回去。持久性实现原理 InnoDB作为MySQL的存储引擎数据是存放在磁盘中的但如果每次读写数据都需要磁盘IO效率会很低。为此InnoDB提供了缓存(Buffer Pool)Buffer Pool中包含了磁盘中部分数据页的映射作为访问数据库的缓冲。当从数据库读取数据时会首先从Buffer Pool中读取如果Buffer Pool中没有则从磁盘读取后放入Buffer Pool。当向数据库写入数据时会首先写入Buffer PoolBuffer Pool中修改的数据会定期刷新到磁盘中这一过程称为刷脏。 Buffer Pool的使用大大提高了读写数据的效率但是也带了新的问题如果MySQL宕机而此时BufferPool中修改的数据还没有刷新到磁盘就会导致数据的丢失事务的持久性无法保证。 于是redo log被引入来解决这个问题。当数据修改时除了修改Buffer Pool中的数据还会在redo log记录这次操作。当事务提交时会调用fsync接口对redo log进行刷盘。如果MySQL宕机重启时可以读取redo log中的数据对数据库进行恢复。redo log采用的是WALWrite-ahead logging预写式日志所有修改先写入日志再更新到Buffer Pool保证了数据不会因MySQL宕机而丢失从而满足了持久性要求。 既然redo log也需要在事务提交时将日志写入磁盘为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢主要有以下两方面的原因
刷脏是随机IO因为每次修改的数据位置随机但写redo log是追加操作属于顺序IO。刷脏是以数据页Page为单位的MySQL默认页大小是16KB一个Page上一个小修改都要整页写入。而redo log中只包含真正需要写入的部分无效IO大大减少。
隔离性实现原理 隔离性追求的是并发情形下事务之间互不干扰。简单起见我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明)那么隔离性的探讨主要可以分为两个方面。
第一方面(一个事务)写操作对(另一个事务)写操作的影响锁机制保证隔离性。 隔离性要求同一时刻只能有一个事务对数据进行写操作InnoDB通过锁机制来保证这一点。锁机制的基本原理可以概括为事务在修改数据之前需要先获得相应的锁。获得锁之后事务便可以修改数据。该事务操作期间这部分数据是锁定的其他事务如果需要修改数据需要等待当前事务提交或回滚后释放锁。按照粒度锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表并发性能较差。行锁则只锁定需要操作的数据并发性能好。但是由于加锁本身需要消耗资源因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的例如MyIsam只支持表锁而InnoDB同时支持表锁和行锁且出于性能考虑绝大多数情况下使用的都是行锁。 第二方面(一个事务)写操作对(另一个事务)读操作的影响MVCC保证隔离性。 InnoDB默认的隔离级别是RRREPEATABLE READRR解决脏读、不可重复读、幻读等问题使用的是MVCC。MVCC全称Multi-Version Concurrency Control即多版本的并发控制协议。它最大的优点是读不加锁因此读写不冲突并发性能好。InnoDB实现MVCC多个版本的数据可以共存主要基 于以下技术及数据结构隐藏列InnoDB中每行数据都有隐藏列隐藏列中包含了本行数据的事务id、指向undo log的指 针等。基于undo log的版本链每行数据的隐藏列中包含了指向undo log的指针而每条undo log也会 指向更早版本的undo log从而形成一条版本链。ReadView通过隐藏列和版本链MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本则需要根据ReadView来确定。所谓ReadView是指事务记做事务A在某一时刻给整个事务系统trx_sys打快照之后再进行读操作时会将读取到的数据中的事务id与trx_sys快照比较从而判断数据对该ReadView是否可见即对事务A是否可见。
一致性实现原理 可以说一致性是事务追求的最终目标。前面提到的原子性、持久性和隔离性都是为了保证数据库状态的一致性。此外除了数据库层面的保障一致性的实现也需要应用层面进行保障。实现一致性的措施包括
保证原子性、持久性和隔离性如果这些特性无法保证事务的一致性也无法保证。数据库本身提供保障例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等。应用层面进行保障例如如果转账操作只扣除转账者的余额而没有增加接收者的余额无论数据库实现的多么完美也无法保证状态的一致。
32、谈谈MySQL的事务隔离级别?
SQL 标准定义了四种隔离级别这四种隔离级别分别是
读未提交READ UNCOMMITTED
读提交 READ COMMITTED
可重复读 REPEATABLE READ
串行化 SERIALIZABLE事务隔离是为了解决脏读、不可重复读、幻读问题下表展示了 4 种隔离级别对这三个问题的解决程 度 上述4种隔离级别MySQL都支持并且InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ但是与标准SQL不同的是InnoDB存储引擎在REPEATABLE READ事务隔离级别下使用Next-Key Lock的锁算法因此避免了幻读的产生。所以InnoDB存储引擎在默认的事务隔离级别下已经能完全保证事务的隔离性要求即达到SQL标准的SERIALIZABLE隔离级别。
并发情况下读操作可能存在的三类问题
脏读当前事务(A)中可以读到其他事务(B)未提交的数据脏数据这种现象是脏读。不可重复读在事务A中先后两次读取同一个数据两次读取的结果不一样这种现象称为不可重复读。脏读与不可重复读的区别在于前者读到的是其他事务未提交的数据后者读到的是其他事务已提交的数据。幻读在事务A中按照某个条件先后两次查询数据库两次查询结果的条数不同这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为前者是数据变了后者是数据的行数变了。
33、MySQL的事务隔离级别是怎么实现的
READ UNCOMMITTED 它是性能最好、也最野蛮的方式因为它压根儿就不加锁所以根本谈不上什么隔离效果可以理解为没有隔离。SERIALIZABLE 读的时候加共享锁其他事务可以并发读但是不能写。写的时候加排它锁其他事务不能并发写也不能并发读。REPEATABLE READ READ COMMITTED 为了解决不可重复读MySQL 采用了 MVVC (多版本并发控制) 的方式。 我们在数据库表中看到的一行记录可能实际上有多个版本每个版本的记录除了有数据本身外还要有一个表示版本的字段记为 row trx_id而这个字段就是使其产生的事务的 id事务 ID 记为transaction id它在事务开始的时候向事务系统申请按时间先后顺序递增。如下图一行记录现在有 3 个版本每一个版本都记录这使其产生的事务 ID比如事务A的transactionid 是100那么版本1的row trx_id 就是 100同理版本2和版本3。 可重复读是在事务开始的时候生成一个当前事务全局性的快照而读提交则是每次执行语句的时候都重新生成一次快照。对于一个快照来说它能够读到那些版本数据要遵循以下规则
当前事务内的更新可以读到版本未提交不能读到版本已提交但是却在快照创建后提交的不能读到版本已提交且是在快照创建前提交的可以读到。 再强调一次两者主要的区别就是在快照的创建上可重复读仅在事务开始是创建一次而读提交每次执行语句的时候都要重新创建一次。MySQL 已经在可重复读隔离级别下解决了幻读的问题用的是间隙锁。MySQL 把行锁和间隙锁合并在一起解决了并发写和幻读的问题这个锁叫做 Next-Key锁。假设现在表中有两条记录并且 age 字段已经添加了索引两条记录 age 的值分别为 10 和 30。此时在数据库中会为索引维护一套B树用来快速定位行记录。B索引树是有序的所以会把这张表的索引分割成几个区间。 此时在数据库中会为索引维护一套B树用来快速定位行记录。B索引树是有序的所以会把这张表的索引分割成几个区间。如图所示分成了3 个区间在这3个区间是可以加间隙锁的。
34、事务可以嵌套吗
可以因为嵌套事务也是众多事务分类中的一种它是一个层次结构框架。有一个顶层事务控制着各个层次的事务顶层事务之下嵌套的事务被称为子事务它控制每一个局部的变换。 需要注意的是MySQL数据库不支持嵌套事务。
35、如何实现可重复读
为了实现可重复读MySQL 采用了 MVVC (多版本并发控制) 的方式。 我们在数据库表中看到的一行记录可能实际上有多个版本每个版本的记录除了有数据本身外还要有一个表示版本的字段记为 row trx_id而这个字段就是使其产生的事务的 id事务 ID 记为 transaction id它在事务开始的时候向事务系统申请按时间先后顺序递增。如下图一行记录现在有 3 个版本每一个版本都记录这使其产生的事务 ID比如事务A的transactionid 是100那么版本1的row trx_id 就是 100同理版本2和版本3。 可重复读是在事务开始的时候生成一个当前事务全局性的快照。对于一个快照来说它能够读到那些版 本数据要遵循以下规则
当前事务内的更新可以读到版本未提交不能读到版本已提交但是却在快照创建后提交的不能读到版本已提交且是在快照创建前提交的可以读到
36、MySQL事务如何回滚
在MySQL默认的配置下事务都是自动提交和回滚的。当显示地开启一个事务时可以使用ROLLBACK语句进行回滚。该语句有两种用法
ROLLBACK要使用这个语句的最简形式只需发出ROLLBACK。同样地也可以写为ROLLBACKWORK但是二者几乎是等价的。回滚会结束用户的事务并撤销正在进行的所有未提交的修改。ROLLBACK TO [SAVEPOINT] identifier 这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点而不回滚在此标记点之前的任何工作。
四、锁
38、了解数据库的锁吗
锁是数据库系统区别于文件系统的一个关键特性锁机制用于管理对共享资源的并发访问。下面我们以MySQL数据库的InnoDB引擎为例来说明锁的一些特点。 锁的类型 InnoDB存储引擎实现了如下两种标准的行级锁
共享锁S Lock允许事务读一行数据。排他锁X Lock允许事务删除或更新一行数据。
如果一个事务T1已经获得了行r的共享锁那么另外的事务T2可以立即获得行r的共享锁因为读取并没有改变行r的数据称这种情况为锁兼容。但若有其他的事务T3想获得行r的排他锁则其必须等待事务T1、T2释放行r上的共享锁这种情况称为锁不兼容。下图显示了共享锁和排他锁的兼容性可以发现X锁与任何的锁都不兼容而S锁仅和S锁兼容。需要特别注意的是S和X锁都是行锁兼容是指对同一记录row锁的兼容性情况。
39、介绍一下间隙锁
InnoDB存储引擎有3种行锁的算法间隙锁Gap Lock是其中之一。间隙锁用于锁定一个范围但不包含记录本身。它的作用是为了阻止多个事务将记录插入到同一范围内而这会导致幻读问题的产生。
40、InnoDB中行级锁是怎么实现的
InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据InnoDB才使用行级锁否则InnoDB将使用表锁。 当表中锁定其中的某几行时不同的事务可以使用不同的索引锁定不同的行。另外不论使用主键索 引、唯一索引还是普通索引InnoDB都会使用行锁来对数据加锁。
41、数据库在什么情况下会发生死锁
死锁是指两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象。若无外力作用事务都将无法推进下去。下图演示了死锁的一种经典的情况即A等待B、B等待A这种死锁问题被称为AB-BA死锁。
42、说说数据库死锁的解决办法
解决死锁问题最简单的一种方法是超时即当两个事务互相等待时当一个等待时间超过设置的某一阈值时其中一个事务进行回滚另一个等待的事务就能继续进行。 除了超时机制当前数据库还都普遍采用wait-for graph等待图的方式来进行死锁检测。较之超时的解决方案这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息
锁的信息链表事务等待链表 通过上述链表可以构造出一张图而在这个图中若存在回路就代表存在死锁因此资源间相互发生等待。这是一种较为主动的死锁检测机制在每个事务请求锁并发生等待时都会判断是否存在回路若存在则有死锁通常来说InnoDB存储引擎选择回滚undo量最小的事务。
五、优化
43、说一说你对数据库优化的理解
MySQL数据库优化是多方面的原则是减少系统的瓶颈减少资源的占用增加系统的反应速度。- - 例如通过优化文件系统提高磁盘I\O的读写速度通过优化操作系统调度策略提高MySQL在高负荷情况下的负载能力优化表结构、索引、查询语句等使查询响应更快。
针对查询我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。针对慢查询我们可以通过分析慢查询日志来发现引起慢查询的原因从而有针对性的进行优化。针对插入我们可以通过禁用索引、禁用检查等方式来提高插入速度在插入之后再启用索引和检查。针对数据库结构我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。
44、该如何优化MySQL的查询
如果查询时没有使用索引查询语句将扫描表中的所有记录。在数据量大的情况下这样查询的速度会很慢。如果使用索引进行查询查询语句可以根据索引快速定位到待查询记录从而减少查询的记录数达到提高查询速度的目的。
索引可以提高查询的速度但并不是使用带有索引的字段查询时索引都会起作用。有几种特殊情况在这些情况下有可能使用带有索引的字段查询时索引并没有起作用。
使用LIKE关键字的查询语句 在使用LIKE关键字进行查询的查询语句中如果匹配字符串的第一个字符为“%”索引不会起作 用。只有“%”不在第一个位置索引才会起作用。使用多列索引的查询语句 MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。使用OR关键字的查询语句 查询语句的查询条件中只有OR关键字且OR前后的两个条件中的列都是索引时查询中才使用索引。否则查询将不使用索引。
优化子查询 使用子查询可以进行SELECT语句的嵌套查询即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活但执行效率不高。 执行子查询时MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后再撤销这些临时表。因此子查询的速度会受到一定的影响。如果查询的数据量比较大这种影响就会随之增大。 在MySQL中可以使用连接JOIN查询来替代子查询。连接查询不需要建立临时表其速度比子查询要快如果查询中使用索引性能会更好。
45、怎样插入数据才能更高效
影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况可以分别进行优化。 对于MyISAM引擎的表常见的优化方法如下
禁用索引 对于非空表插入记录时MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据建立索引会降低插入记录的速度。为了解决这种情况可以在插入记录之前禁用索引数据插入完毕后再开启索引。对于空表批量导入数据则不需要进行此操作因为MyISAM引擎的表是在导入数据之后才建立索引的。禁用唯一性检查 插入数据时MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响可以在插入记录之前禁用唯一性检查等到记录插入完毕后再开启。使用批量插入 插入多条记录时可以使用一条INSERT语句插入一条记录也可以使用一条INSERT语句插入多条记录。使用一条INSERT语句插入多条记录的情形如下而这种方式的插入速度更快。使用LOAD DATA INFILE批量导入 当需要批量导入数据时如果能用LOAD DATA INFILE语句就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快。
对于InnoDB引擎的表常见的优化方法如下
禁用唯一性检查 插入数据之前执行 set unique_checks0 来禁止对唯一索引的检查数据导入完成之后再运行 set unique_checks1 。这个和MyISAM引擎的使用方法一样。禁用外键检查 插入数据之前执行禁止对外键的检查数据插入完成之后再恢复对外键的检查。禁用自动提交 插入数据之前禁止事务的自动提交数据导入完成之后执行恢复自动提交操作。
46、 表中包含几千万条数据该怎么办
建议按照如下顺序进行优化
优化SQL和索引增加缓存如memcached、redis读写分离可以采用主从复制也可以采用主主复制使用MySQL自带的分区表这对应用是透明的无需改代码但SQL语句是要针对分区表做优化的做垂直拆分即根据模块的耦合度将一个大的系统分为多个小的系统做水平拆分要选择一个合理的sharding key为了有好的查询效率表结构也要改动做一定的冗余应用也要改sql中尽量带sharding key将数据定位到限定的表上去查而不是扫描全部的表。
47、 MySQL的慢查询优化有了解吗
优化MySQL的慢查询可以按照如下步骤进行 开启慢查询日志 MySQL中慢查询日志默认是关闭的可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打 开也可以在MySQL服务启动的时候使用 --log-slow-queries[file_name] 启动慢查询日志。 启动慢查询日志时需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值如果某 条查询语句的查询时间超过了这个值这个查询过程将被记录到慢查询日志文件中。 分析慢查询日志 直接分析mysql慢查询日志利用explain关键字可以模拟优化器执行SQL查询语句来分析sql慢查询语句。
48、说一说你对explain的了解
MySQL中提供了EXPLAIN语句和DESCRIBE语句用来分析查询语句EXPLAIN语句的基本语法如下
EXPLAIN [EXTENDED] SELECT select_options使用EXTENED关键字EXPLAIN语句将产生附加信息。执行该语句可以分析EXPLAIN后面SELECT语句的执行情况并且能够分析出所查询表的一些特征。下面对查询结果进行解释
idSELECT识别符。这是SELECT的查询序列号。
select_type表示SELECT语句的类型。
table表示查询的表。
type表示表的连接类型。
possible_keys给出了MySQL在搜索数据记录时可选用的各个索引。
key是MySQL实际选用的索引。
key_len给出索引按字节计算的长度key_len数值越小表示越快。
ref给出了关联关系中另一个数据表里的数据列名。
rows是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
Extra提供了与关联操作有关的信息DESCRIBE语句的使用方法与EXPLAIN语句是一样的分析结果也是一样的并且可以缩写成DESC。DESCRIBE语句的语法形式如下
DESCRIBE SELECT select_options49、explain关注什么 六、其他
50、介绍一下数据库设计的三大范式
目前关系数据库有六种范式一般来说数据库只需满足第三范式(3NF就行了。
第一范式1NF 是指在关系模型中对于添加的一个规范要求所有的域都应该是原子性的即数据库表的每一列都是不可分割的原子数据项而不能是集合数组记录等非原子数据项。即实体中的某个属性有多个值时必须拆分为不同的属性。在符合第一范式表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之第一范式就是无重复的域。第二范式2NF 在1NF的基础上非码属性必须完全依赖于候选码在1NF基础上消除非主属性对主码的部分函数依赖。第二范式是在第一范式的基础上建立起来的即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组作为实体的唯一标识。 例如在员工表中的身份证号码即可实现每个一员工的区分该身份证号码即为候选键任何一个候选键都可以被选作主键。在找不到候选键时可额外增加属性以实现区分如果在员工关系中没有对其身份证号进行存储而姓名可能会在数据库运行的某个时间重复无法区分出实体时设计辟如ID等不重复的编号以实现区分被添加的编号或ID选作主键。第三范式3NF 在2NF基础上任何非主属性不依赖于其它非主属性在2NF基础上消除传递依赖。 第三范式是第二范式的一个子集即满足第三范式必须满足第二范式。简而言之第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如存在一个部门信息表其中每个部门有部门编号dept_id、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表则根据第三范式3NF也应该构建它否则就会有大量的数据冗余。
51、说一说你对MySQL引擎的了解
MySQL提供了多个不同的存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中不需要在整个服务器中使用同一种存储引擎针对具体的要求可以对每一个表使用不同的存储引擎。MySQL 8.0支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。其中最常用的引擎是InnoDB和MyISAM。 InnoDB存储引擎 InnoDB是事务型数据库的首选引擎支持事务安全表ACID支持行锁定和外键。MySQL 5.5.5之后InnoDB作为默认存储引擎主要特性如下
InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全ACID兼容存储引擎。 InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中可以自由地将InnoDB类型的表与其他MySQL表的类型混合起来甚至在同一个查询中也可以混合。InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。InnoDB存储引擎完全与MySQL服务器整合为在主内存中缓存数据和索引而维持它自己的缓冲 池。InnoDB将它的表和索引存在一个逻辑表空间中表空间可以包含数个文件或原始磁盘分 区。这与MyISAM表不同比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸即使在文件尺寸被限制为2GB的操作系统上。InnoDB支持外键完整性约束FOREIGN KEY。存储表中的数据时每张表的存储都按主键顺序存放如果没有显示在表定义时指定主键InnoDB会为每一行生成一个6B的ROWID并以此作为主键。InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录使用InnoDB时 MySQL将在数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件以及两个名为 ib_logfile0和ib_logfile1的5MB大小的日志文件。
MyISAM存储引擎 MyISAM基于ISAM存储引擎并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度但不支持事务。MyISAM的主要特性如下 6. 在支持大文件达63位文件长度的文件系统和操作系统上被支持。 7. 当把删除和更新及插入操作混合使用的时候动态尺寸的行产生更少碎片。这要通过合并相邻被删 除的块以及若下一个块被删除则扩展到下一块来自动完成。 8. 每个MyISAM表最大的索引数是64这可以通过重新编译来改变。每个索引最大的列数是16个。 9. 最大的键长度是1000B这也可以通过编译来改变。对于键长度超过250B的情况一个超过1024B 的键将被用上。 10. BLOB和TEXT列可以被索引。 11. NULL值被允许在索引的列中这个值占每个键的0~1个字节。 12. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩。 13. 每个表一个AUTO_INCREMENT列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列这使得AUTO_INCREMENT列更快至少10%。在序列顶的值被删除之后就不能再利用。 14. 可以把数据文件和索引文件放在不同目录。 15. 每个字符列可以有不同的字符集。 16. 有VARCHAR的表可以固定或动态记录长度。 17. VARCHAR和CHAR列可以多达64KB。
52、说一说你对redo log、undo log、binlog的了解
binlogBinary Log 二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作然后以二进制的形式记录在日志文件中其中还包括每条语句所执行的时间和所消耗的资源以及相关的事务信息。默认情况下二进制日志功能是开启的启动时可以重新配置 --log-bin[file_name] 选项修改二进制日志存放的目录和文件名称。redo log 重做日志用来实现事务的持久性即事务ACID中的D。它由两部分组成一是内存中的重做日志缓冲redo log buffer其是易失的二是重做日志文件redo log file它是持久的。 InnoDB是事务的存储引擎它通过Force Log at Commit机制实现事务的持久性即当事务提交 COMMIT时必须先将该事务的所有日志写入到重做日志文件进行持久化待事务的COMMIT操作完成才算完成。这里的日志是指重做日志在InnoDB存储引擎中由两部分组成即redo log和undolog。redo log用来保证事务的持久性undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。undo log 重做日志记录了事务的行为可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作这时就需要undo。因此在对数据库进行修改时InnoDB存储引擎不但会产生redo还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了又或者用户用一ROLLBACK语句请求回滚就可以利用这些undo信息将数据回滚到修改之前的样子。redo存放在重做日志文件中与redo不同undo存放在数据库内部的一个特殊段segment中这 个段称为undo段undo segmentundo段位于共享表空间内。
53、谈谈你对MVCC的了解
InnoDB默认的隔离级别是RRREPEATABLE READRR解决脏读、不可重复读、幻读等问题使用的是MVCC。MVCC全称Multi-Version Concurrency Control即多版本的并发控制协议。它最大的优点是读不加锁因此读写不冲突并发性能好。InnoDB实现MVCC多个版本的数据可以共存主要基于以下技术及数据结构
隐藏列InnoDB中每行数据都有隐藏列隐藏列中包含了本行数据的事务id、指向undo log的指 针等。基于undo log的版本链每行数据的隐藏列中包含了指向undo log的指针而每条undo log也会 指向更早版本的undo log从而形成一条版本链。ReadView通过隐藏列和版本链MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本则需要根据ReadView来确定。所谓ReadView是指事务记做事务A在某一时刻给整个事务系统trx_sys打快照之后再进行读操作时会将读取到的数据中的事务id与trx_sys快照比较从而判断数据对该ReadView是否可见即对事务A是否可见。
54、MySQL主从同步是如何实现的
复制replication是MySQL数据库提供的一种高可用高性能的解决方案一般用来建立大型的应用。总体来说replication的工作原理分为以下3个步骤
主服务器master把数据更改记录到二进制日志binlog中。从服务器slave把主服务器的二进制日志复制到自己的中继日志relay log中。从服务器重做中继日志中的日志把更改应用到自己的数据库上以达到数据的最终一致性。
复制的工作原理并不复杂其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是复制不是完全实时地进行同步而是异步实时。这中间存在主从服务器之间的执行延时如果主服务器的压力很大则可能导致主从服务器延时较大。复制的工作原理如下图所示其中从服务器有2个线程一个是I/O线程负责读取主服务器的二进制日志并将其保存为中继日志另一个是SQL线程复制执行中继日志。
