mysql相关知识

最新推荐文章于 2024-08-08 23:17:00 发布

某某灬

最新推荐文章于 2024-08-08 23:17:00 发布

阅读量284

点赞数

分类专栏： mysql 文章标签： mysql知识 mysql mysql面试点

本文链接：https://blog.csdn.net/weixin_43882997/article/details/88854919

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近在找工作，就把之前的笔记整理了一下记到这里，就当复习。

一、 mysql和oreacle的区别？

1、自动增长的数据类型处理

MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

2、单引号的处理

MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

3、翻页的SQL语句的处理

MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80。

4、日期字段的处理

5、空字符的处理

MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

二、mysql

1、存储过程：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

优点
①重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
②减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
③安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。
简单讲：
- 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
- 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
- 存储过程可以重复使用,可减少数据库开发人员的工作量
- 安全性高,可设定只有某些用户才具有对指定存储过程的使用权
缺点
- 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
- 移植问题，数据库端代码当然是与数据库相关的。
- 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
- 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

2、触发器：（创表、插入数据、更新数据等的sql都会触发触发器）

触发器：是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、 Insert、 Delete 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

强化约束(Enforce restriction)
跟踪变化Auditing changes
级联运行(Cascaded operation)
存储过程的调用(Stored procedure invocation)

3、数据库的事物

数据库事物的介绍：

是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。
一致性
事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。
隔离性
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。
持久性
事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

分布式事务

数据库并发访问会出现的问题

更新丢失
当有两个并发执行的事务，更新同一行数据，那么有可能一个事务会把另一个事务的更新覆盖掉。当数据库没有加任何锁操作的情况下会发生。
脏读
一个事务读到另一个尚未提交的事务中的数据，该数据可能会被回滚从而失效。如果第一个事务拿着失效的数据去处理那就发生错误了。
不可重复读
不可重复度的含义：一个事务对同一行数据读了两次，却得到了不同的结果。它具体分为如下两种情况：
- 虚读：在事务1两次读取同一记录的过程中，事务2对该记录进行了修改，从而事务1第二次读到了不一样的记录。
- 幻读：事务1在两次查询的过程中，事务2对该表进行了插入、删除操作，从而事务1第二次查询的结果发生了变化。
- 与『脏读』的区别？
  脏读读到的是尚未提交的数据，而不可重复读读到的是已经提交的数据，只不过在两次读的过程中数据被另一个事务改过了。

事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

Read uncommitted 读未提交
在该级别下，一个事务对一行数据修改的过程中，不允许另一个事务对该行数据进行修改，但允许另一个事务对该行数据读。因此本级别下，不会出现更新丢失，但会出现脏读、不可重复读。
Read committed 读提交
在该级别下，未提交的写事务不允许其他事务访问该行，因此不会出现脏读；但是读取数据的事务允许其他事务的访问该行数据，因此会出现不可重复读的情况。
Repeatable read 重复读
在该级别下，读事务禁止写事务，但允许读事务，因此不会出现同一事务两次读到不同的数据的情况（不可重复读），且写事务禁止其他一切事务。
Serializable 序列化
该级别要求所有事务都必须串行执行，因此能避免一切因并发引起的问题，但效率很低。
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

使用事务的注意事项
- 不要在循环中提交事务
- 不要使用自动提交
- 不要使用自动回滚
- -不要使用长事务

Mysql的索引（B+tree）

1、什么是索引？为什么要建索引？

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。　　　　　　
　例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。其中MySQL中的索引的存储类型有两种：BTREE、HASH。也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。我们现在只需要知道索引的作用，功能是什么就行。

2、MySQL中索引的优点和缺点和使用原则

优点：

1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
2、大大加快数据的查询速度

缺点：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：　　　　　　
　通过上面说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引
数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

三、索引的分类

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引　　　　　　　
索引我们分为四类来讲单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。
- 普通索引：　　　　　　　　　　　　　　　　　　
  　　MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
- 唯一索引：　　　　　　　　　　　　　　　　　　
  　　索引列中的值必须是唯一的，但是允许为空值，
- 主键索引：　　　　　　　　　　　　　　　　　　
  　　是一种特殊的唯一索引，不允许有空值。
2、组合索引　　　　　　　　　　　
　在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说
3、全文索引　　　　　　　　　　　　　　
　　全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，如果感兴趣进一步深入使用它，那么看下面测试该索引时，会给出一个博文，供大家参考。
4、空间索引　　　　　　　　　　　　　　　
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。具体细节看下面

MySql两种存储引擎的区别

MyISAM：

不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb：

支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；
一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

Mysql的 SQL 优化

负向查询不能使用索引

select name from user where id not in (1,3,4);

应该修改为:

select name from user where id in (2,5,6);

前导模糊查询不能使用索引

select name from user where name like '%zhangsan'

非前导则可以:

select name from user where name like 'zhangsan%

  建议可以考虑使用 `Lucene` 等全文索引工具来代替频繁的模糊查询。

数据区分不明显的不建议创建索引如
user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。## 字段的默认值不要为 null这样会带来和预期不一致的查询结果。

在字段上进行计算不能命中索引

select name from user where FROM_UNIXTIME(create_time) < CURDATE();

应该修改为:

select name from user where create_time < FROM_UNIXTIME(CURDATE());

最左前缀问题
如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下SQL 都是可以命中索引:

	select username from user where username='zhangsan' and pwd ='axsedf1sd'
	select username from user where pwd ='axsedf1sd' and username='zhangsan'
	select username from user where username='zhangsan'

但是使用

	select username from user where pwd ='axsedf1sd'

是不能命中索引的。

如果明确知道只有一条记录返回
```
select name from user where username='zhangsan' limit 1
```
可以提高效率，可以让数据库停止游标移动。
不要让数据库帮我们做强制类型转换
```
select name from user where telno=18722222222
```
这样虽然可以查出数据，但是会导致全表扫描。需要修改为
```
select name from user where telno='18722222222'
```
如果需要进行 join 的字段两表的字段类型要相同不然也不会命中索引。