锁住非主键索引是行锁吗 mysql非主键索引

转载

mob64ca1404baa2 2024-06-19 11:09:39

文章目录

事务特性(ACID)：
**三大范式：**
MySQL外连接、内连接与自然连接的区别
**数据库优化方式**
**索引手册：**
存储引擎
日志分析
delete、drop、truncate区别
备份容灾
数据库锁
典型问题：

事务特性(ACID)：

原子性 要么全部成功，要么全部失败
一致性 只会有前状态和后状态，绝不会出现中间态。
隔离性 事务之间不能相互干扰
持久性 一个事物提交之后，数据库状态永远的发生了改变，即使出现宕机。

事务隔离级别：

		描述
Read Uncommitted	读取未提交	一个事务中可读取另一个事务尚未提交的数据
Read Committed	读取已提交	一个事务中可读取另一个事务已提交的数据
Repeated Read	可重复读	一个事务中多次读到的数据总是一致的不受其他事务影响
Serializable	串行化	事务“串行化顺序执行”，也就是一个一个排队执行。

三大范式：

第一范式：
当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：
如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：
设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

MySQL外连接、内连接与自然连接的区别

内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。

外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中
的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。

左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。

**自然连接(natural join)**是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。

数据库优化方式

SQL 优化：
尽量避免使用 SELECT *；
只查询一条记录时使用 limit 1；
使用连接查询代替子查询；
尽量使用一些能通过索引查询的关键字。
表结构优化：
尽量使用数字类型字段，提高比对效率；
长度不变且对查询速度要求高的数据可以考虑使用 char，否则使用 varchar；表中字段过多时可以适当的进行垂直分割，将部分字段移动到另外一张表；表中数据量过大可以适当的进行水平分割，将部分数据移动到另外一张表。
其它优化：
对查询频率高的字段适当的建立索引，提高效率；根据表的用途使用合适的数据库引擎；读写分离；分库分表

索引手册：

1、主键索引与唯一性索引比较
均要求不能重复，但主键索引一个表只能有一个且列值不能为空

2、普通索引与主键索引哪个快
主键索引和普通索引的原理了，普通索引保存的是主键的地址，然后再根据主键去查询，所以要检索两次，主键索引只需要检索一次就性了
可以这么说主键所以只需要检索一次，普通索引要检索两次，

3、Mysql回表
回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

4、mysql执行计划参数解释
explain update t set cell=456 where cell=55555555555;
select_type：SIMPLE
这是一个简单类型的SQL语句，不含子查询或者UNION。
type：index
访问类型，即找到所需数据使用的遍历方式，潜在的方式有：
（1）ALL（Full Table Scan）：全表扫描；
（2）index：走索引的全表扫描；
（3）range：命中where子句的范围索引扫描；、
（4）ref/eq_ref：非唯一索引/唯一索引单值扫描；
（5）const/system：常量扫描；
（6）NULL：不用访问表；
上述扫描方式，ALL最慢，逐步变快，NULL最快。
possible_keys：NULL
可能在哪个索引找到记录。
key：PRIMARY
实际2、使用索引。
ref：NULL
哪些列，或者常量用于查找索引上的值。
rows：5
找到所需记录，预估需要读取的行数。

存储引擎

InnoDB

支持事务，行级锁
底层为B+树结构，时间复杂度为logN，
一般来说B+Tree的高度一般都在2-4层
聚集索引的磁盘IO次数大概是1-3次

MyIsam
5. 支持全文索引
6. 不支持事务
7. 插入，读取速度快

日志分析

**1、重做日志（redo log）：**是数据页面的修改之后的物理记录，恢复数据效率>binlog
2、回滚日志（undo log）： 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
3、二进制日志（binlog）：记录全部有效的数据修改日志
**4、错误日志（errorlog）：**记录mysql在启动、运行和停止时出现的问题诊断分析。
5、慢查询日志（slow query log）：记录符合条件的查询，经常用来分析哪些速度慢的sql需要优化
6、一般查询日志（general log）：记录所有mysql客户端发向mysql服务器的请求
7、中继日志（relay log）：用于主从复制，临时存储从主库同步的二进制日志。

delete、drop、truncate区别

truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。
删除数据的速度，drop> truncate > delete
delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚。
使用场合：
当你不再需要该表时，用 drop;
当你仍要保留该表，但要删除所有记录时，用 truncate;
当你要删除部分记录时（always with a where clause), 用 delete.

注意：对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够激活触发器

备份容灾

双机热备
配置步骤：
主服务器：
开启二进制日志
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号
从服务器：
配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启用slave服务

**原理：

**主库db的更新事件(update、insert、delete)被写到binlog
主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动并发起连接，连接到主库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
从库启动之后，创建一个SQL线程，从relay log里面读取内容，从 Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

3个线程以及之间的关联

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
从：sql执行线程——执行relay log中的语句；

数据库锁

**表级锁：**开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
**行级锁：**开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
**页面锁：**开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
**共享锁（s）：**又称读锁。 SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
**排他锁（Ｘ）：**又称写锁。SELECT * FROM table_name WHERE … FOR UPDATE
**意向共享锁（IS）：**事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
**意向排他锁（IX）：**事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

尽量使用较低的隔离级别；精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
选择合理的事务大小，小事务发生锁冲突的几率也更小；
给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

典型问题：

1、count(*)、count(1)、count(id)、count(字段)的效率区别
count(id)
　　InnoDB引擎会遍历整张表，把每一行行的id值全部取出来，返回给server层，server层拿到id后，判断是不可能为空的，就按行累加。
count(1)
　　InnoDB引擎遍历整张表，但不取值，server层对于返回的每一行，放一个数字 1 进去，判断是不可能为空的，累计增加。
count(字段)
　　1.如果这个字段是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加
　　2.如果这个字段定义允许为null的话，判断到有可能是null，还要把值取出来在判断一下，不是null才累加。
count()
　　不会把全部的字段取出来，而是做专门的优化，不取值，count()肯定不是null，按行累加。
总结：count(*)>count(1)>count(id)>count(字段)

2、、对于内容基本重复的列，比如只有1和0，禁止建立索引，因为该索引选择性极差，在特定的情况下会误导优化器做出错误的选择，导致查询速度极大下降。

3、当一个索引有多个列构成时，应注意将选择性强的列放在前面。仅仅前后次序的不同，性能上就可能出现数量级的差异

4、对小表进行索引可能不能产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长，设计索引时需要考虑表的大小。记录数不大于100的表不要建立索引。频繁操作的小数量表不建议建立索引（记录数不大于

5、mysql能存储的最大数据量
mysql的最大数据存储量没有最大限制
据D.V.B 团队以及Cmshelp 团队做CMS 系统评测时的结果来看，MySQL单表大约在2千万条记录（4G）下能够良好运行，经过数据库的优化后5千万条记录（10G）下运行良好。
事实上，这个数值和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为，MySQL 为了提高性能，会将表的索引装载到内存中。InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。这里，增加硬件配置，可能会带来立竿见影的性能提升

6、InooDB和MyISAM的select count（*）哪个更快，为什么
myisam更快，因为myisam内部维护了一个计算器，可以直接调取。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

7、一张表里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

锁住非主键索引是行锁吗 mysql非主键索引_数据