mysql高级特性

最新推荐文章于 2022-05-27 10:30:00 发布
最新推荐文章于 2022-05-27 10:30:00 发布
阅读量276 收藏
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/meiziziLOLOLO/article/details/107702409
版权

文章目录

函数

1.常用函数

  • 数学函数

    select ABS(-8)    		--绝对值
select CEILING(6.4)		--向上取整
select FLOOR(6.4)		--向下取整
select RAND()			--获取随机数
select SIGN(-5)			--判断一个数符号，0是0，负数是-1，正数是1

  • 字符串函数

    select CHAR_LENGTH(`你好呀`)   			--获取字符串长度
select CONCAT(`你`,`好`,`呀`)			--拼接字符串
select INSERT(`我爱java`,1,2,`超级爱`)	--替换字符串，从第一个字符起，替换2个字符串长度，替换成后面的字符串，比如例子就变成“超级爱java”
select LOWER(`ABC`)						--获取小写字母
select UPER(`abc`)						--获取大写字母
select INSTR('abc','b')					--获取指定字符第一次出现的索引
select REPLACE('abc','b','c')			--替换指定字符，将b替换成c
select SUBSTR('abcde',2,3)				--截取字符串，返回第二个位置开始的三个长度的字符串
select REVERSE(`abc`)					--反转字符串

运用例子：将查询到的名字里的姓林的变成姓陈的
select REPLACE(name,'林','陈') FROM student where name like ‘林%’

  • 日期函数

    select CURRENT_DATE()		--获取当前日期
select CURRENT()			--获取当前日期
select NOW()				--获取当前时间
select LOCATTIME()			--获取本地时间
select SYSDATE()			--获取系统时间	
select YEAR(NOW())			--获取现在时间的年份，参数可修改
select MONTH(NOW())			--获取现在时间的月份
select DAY(NOW())			--获取现在时间的天
select HOUR(NOW())			--获取现在时间的小时
select MINUTE(NOW())		--获取现在时间的分钟
select SECOND(NOW())		--获取现在时间的秒

2.聚合函数

COUNT()			--计数
SUM()			--求和
MIN()			--最小值
MAX()			--最大值
AVG()			--求平均值

上面便是常用的聚合函数了，在这里特别的说一下COUNT()函数，它根据参数的不同，它有三种形式：

  • COUNT(*)：统计表中的所有的记录数，不会忽略NULL值
  • COUNT(1)：统计表中的所有的记录数，不会忽略NULL值，与COUNT(*)效果一样，性能在具体应用时可能会有差别，但相差不大可以认为基本相等
  • COUNT(列名)：统计指定列的记录数，会忽略NULL值

具体应用：

若列名为主键，count(列名)会比count(1)快
若列名不为主键，count(1)会比count(列名)快
若表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
若表有主键，则 count（主键）的执行效率是最优的
若表只有一个字段，则count（ * ）最优。

3.其他函数

  • 加密函数：MD5(password)，使用MD5算法对参数进行加密

    Insert INTO `user` VALUES(1,`123456`,MD5(`123456`))    --新建账号时对密码加密
select id from `user` where password = MD5(`123456`)   --校验密码

事务

对于事务，相信大家都不陌生，每一个学过 mysql 的一定都听过银行转账的例子，这里也不对事务过多概括了，重点讲一下它的四个特点ACID以及是如何实现的。

1.事务中的概念

1.ACID

  • 原子性（Atomicity）：表示事务里的动作要么一起成功，要么一起失败，不可分割只发生一个动作。用转账案例来说，就是A转给B和B收到钱这两个动作不可分割。

  • 一致性（Consistency）：数据库总是从一个一致性的状态转移到另一个一致性的状态。这个有点抽象，还是转账例子，AB之前总共有1000块钱，转账前后他们也还是总共有1000块钱。

  • 持久性（Isolation）：表示事务的数据不随着外界因素而丢失，事务提交了，则会持久化到数据库，事务一旦提交不可逆。比如说，A在转账过程中银行宕机了，这时转账失败，A的钱不会少，B的钱也不会多。

  • 隔离性（Durability）：表示多个事务之间是相互隔离开的，一个事务不会影响另一个事务，排除了其他事务对本次事务的影响。A给B转账，C给B转账，这两个事务之间不会相互影响，不会造成B收到的钱不是AC转账之和。

2.脏读

一个事务读取了另一个事务未提交的数据，然后结果发生错误。如下图，A转给B200，C转给B100，B原来有200，这样结果应该是500，但由于发生了脏读，C在转账时读取了A转账事务还没提交的数据，获得B的钱是200而不是400，所以B的结果可能是400也可能是300。
在这里插入图片描述

3.幻读

指一个线程中的事务读取到了另外一个线程中事务已提交的insert的数据。即一个事务A两次或多次读取数据，在此期间，事务B新增了N条数据，然后提交了，造成了事务A两次或多次读取数据出现了数据条数不一致的情况。

和下面的虚读有点类似，不过造成原因不同，但影响却是一样，都可能造成数据丢失。

4.虚读

虚读也叫不可重复读，在一个事务中前后两次读取的结果值并不一致，导致了不可重读读，指一个线程中的事务读取到了另外一个线程中的事务已提交的update的数据。即一个事务A两次或多次读取数据，在此期间，事务B读取同一数据，并修改了此数据，然后提交了，造成了事务A两次或多次读取数据出现了数据不一致的情况。

举个例子：小明在查工资时发现自己工资是1000，事务还没结束，财务人员将工资改为2000，事务提交结束，当小明那个事务再次查工资时发现工资变成了2000，这就是不可重复读，不可重复读不一定是错误，但有可能会造成数据丢失。

第一类丢失更新就是两个事务同时更新一个数据，一个事务更新完毕并提交后，另一个事务回滚，造成提交的更新丢失。

第二类丢失更新就是两个事务同时更新一个数据，先更新的事务提交的数据会被后更新的事务提交的数据覆盖，即先更新的事务提交的数据丢失。

2.事务的作用

为什么mysql里会出现事务呢？个人理解，作用有两个：一个是可靠性，另一个是用来做并发处理。而这两个功能也是由它的特点ACID所保证的。

  • 可靠性：当一系列修改数据的操作在进行的过程中，无论数据是删除插入或者修改，都能保证成功后数据成功写入，或者失败后所有数据重新回到原点。这个可靠性就是原子性和持久性保证的，原子性保证失败后能正常恢复，持久性能保证成功后正常写入。

  • 并发处理：上面我们讲到脏读、幻读、虚读等，其实你会发现这是多线程的操作才会发生的，所以事务也是用来处理并发中发生的问题的，这个是由事务的隔离性来保证的。

  • 一致性：上面我们提到的可靠性和并发处理，其实这两个合起来也就组合成了一致性，也就是说，ACID中三个特点：原子性、持久性、隔离性都是为了一致性服务的。

3.事务原理

相信读了上面，你一定对事务的特点和作用有所了解吧。下面就来讲讲事务的各个特点到底是怎么实现的。

1.原子性的实现

上面我们说过了，原子性保证事务要么成功要么回滚，其实重点就在于回滚这个操作，个人理解原子性就是实现了回滚这个操作。那回滚操作是怎么实现的呢？其实是利用到mysql里的日志操作——undo log（回滚日志）。

什么是undo log？undo log 叫做回滚日志，用于记录数据被修改前的信息。它记录语句的“反语句”。

  • 如果在回滚日志里有新增数据记录，则生成删除该条的语句
  • 如果在回滚日志里有删除数据记录，则生成生成该条的语句
  • 如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句

undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

我们已经大概了解它是什么了，现在来看下具体实现：
在这里插入图片描述
在这里插入图片描述
由图我们也可以很清楚看到，每次操作数据都会生成undo log，不过有一点需要特别注意，就是undo log的生成时机，每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log。

undo log也是比较容易理解的，就是每次我们修改新增或者删除数据时，它会记录下这些语句的“反语句”，当出错或者执行回滚操作后，系统就会找到undo log，利用里面记录的“反语句”复原数据。

2.持久性的实现

原子性保证了事务失败后数据能够恢复，持久性保证事务成功后数据能够正确写入。这时你可能会有疑惑，原子性我还能理解，事务到一半中断了数据不完整，这个持久性是什么意思呢？难不成它事务成功了数据还不能写入？没错就是这样的，在此之前我们需要了解一下mysql的存储机制。

我们都知道mysql的表是持久化到硬盘的，我们修改数据其实就是修改表里的数据，所以一定会涉及到磁盘IO，但是这时就有一个老生常谈的问题了：性能不行啊，磁盘IO太耗资源了。那mysql这个小天才怎么解决呢？为此，为了提升性能InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用：

  • 读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
  • 写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中；

这样一来，就极大提升了MySQL的性能，但问题也来了：它写数据时是过了一段时间后再写入磁盘的，那假如在这段时间系统出问题了，数据不就丢失了吗？这时就体现事务的持久性了，保证了即使出问题数据也不会丢失。

那持久性怎么实现呢？原理和原子性的实现有点类似，都是使用了日志，不过这次的日志叫redo log（重做日志）。redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。它可不像undo log，它记录的就是执行的语句。

start transaction;
select balance from bank where name="zhangsan";
// 生成 重做日志 balance=600
update bank set balance = balance - 400; 
// 生成 重做日志 amount=400
update finance set amount = amount + 400;
commit;

在这里插入图片描述
事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，和undo log一样，在事务的执行过程中，便开始写入redo log文件中。

总结一下，redo log就是在事务执行时，记录下事务修改数据的语句，当事务结束后发生系统宕机且数据还没来得及写入磁盘时，会找到这个redo log文件来恢复数据。

关于redo log和undo log其实还是挺复杂的，这里只是简单简述下，如果有兴趣的话还可以继续深入。

3.隔离性的实现

上面提到，隔离性就是用来解决数据库中的并发问题的，所以要了解这一部分最好是要有一定并发编程基础。sql里分了四个隔离级别，什么是隔离级别？隔离级别就是不同事务之间的交互程度，每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是不可见的。

在将隔离级别之前，先简单了解下保证隔离性的两种手段：mysql锁技术和MVCC机制。

  • mysql锁技术
    共享锁(shared lock),又叫做"读锁"，读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。

    排他锁(exclusive lock),又叫做"写锁"，写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。
    在这里插入图片描述
    总结：通过读写锁，可以做到读读可以并行，但是不能做到写读，写写并行 事务的隔离性就是根据读写锁来实现的

  • MVCC机制
    MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制，InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际的时间值，而是系统版本号。

Mysql 隔离级别有以下四种（级别由低到高），等级越低并发性能越高：

  • READ UNCOMMITED (未提交读)
    事务中的修改即使还没提交，对其他事务是可见的。因为读不会加任何锁，所以写操作在读的过程中修改数据，所以缺点是会造成脏读。好处是可以提升并发处理性能，能做到读写并行。

    换句话说，只是用了写锁，写写互斥，读写不互斥，读的操作不能排斥写请求。

  • READ COMMITED (提交读)
    一个事务的修改在他提交之前的所有修改，对其他事务都是不可见的。其他事务能读到已提交的修改变化。很明显了，这个会造成不可能重复读，也就是虚读，但不会造成脏读。

    InnoDB在 READ COMMITTED，写数据时使用写锁,读数据不加锁而是使用了MVCC机制。或者换句话说他采用了读写分离机制。

    为什么会产生不可重复读？

    这跟 READ COMMITTED 级别下的MVCC机制有关系，在该隔离级别下每次 select的时候新生成一个版本号，所以每次select的时候读的不是一个副本而是不同的副本。

    在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读。
    在这里插入图片描述

  • REPEATABLE READ (可重复读)
    这个也就是mysql默认的事务隔离级别，在一个事务内的多次读取的结果是一样的。这种级别下可以避免脏读，不可重复读等查询问题，但还是存在幻读的问题。mysql 有两种机制可以达到这种隔离级别的效果，分别是采用读写锁以及MVCC。

    • 使用读写锁
      为什么能可重复读？只要没释放读锁，在次读的时候还是可以读到第一次读的数据。
      • 优点：实现起来简单
      • 缺点：无法做到读写并行在这里插入图片描述
    • 采用MVCC
      为什么能可重复读？因为多次读取只生成一个版本，读到的自然是相同数据。
      • 优点：读写并行
      • 缺点：实现的复杂度高
        在这里插入图片描述

  • SERIALIZABLE (串行化)
    这个就最好理解了，读写是串行的，在隔离级别下除了不会造成数据不一致问题，没其他优点，并发度很低。其实就是每个过程都加锁了。
    在这里插入图片描述

索引

学过mysql，相信大家都是知道索引这个东西的。顾名思义，索引就像一本书里的目录，在我们使用数据库查询数据时，使用索引能大大提高我们的查询效率，下面具体讲一下。

1.索引特点

  • 优势

    • 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
    • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

  • 劣势

    • 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
    • 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息，不是索引越多越好

2.索引种类划分

关于索引划分和结构的这部分，很大程度参照了这位博主的博客：

https://www.cnblogs.com/wyc1994666/p/10831039.html

在这里插入图片描述

我们已经知道索引就是用来提高查询效率的，那它的本质实现就是利用了一些查找算法。那是怎么样实现的呢？首先我们先来考虑下索引的特点：

  • 存储数据非常多
  • 数据动态变化，不停地增删改

所以我们要找到一个合适的数据结构算法来满足这种特点，常见的算法如下图：
在这里插入图片描述
由于数据是动态的，所以只剩下下面的查找树和Hash查找了。对于查找树来说，由于数据量过多，二叉树的高度会很高，查询会耗费较多时间，所以二叉树不适用于索引，所以只剩下多叉树了。

多叉树里又有B树和B+树，那用哪个呢？答案是B+树，因为B+树的独有特点，使得它支持区间查询。如果不懂得这些数据结构，下面有两篇推文图文讲解了什么是B+树和B+树的查找策略，大家有兴趣也可以去看下：

什么是B+树：https://mp.weixin.qq.com/s/Krm8xmVCU9cnDUSAG18kJw
B+树查找策略：https://mp.weixin.qq.com/s/F9QllnZbmkARp1DTdXVd6A

总结一下，索引在查找算法中利用了两个算法：B+树和哈希查找，对应的也就生成了两种索引：B-tree索引和Hash索引。

在mysql中，索引按不同划分方式有很多种，大概如下：

  • 按实现原理：BTREE索引、Hash索引
  • 按应用方式：主键索引、唯一索引、全文索引、单列索引、联合索引、前缀索引等
  • 按数据组织方式：聚簇索引、非聚簇索引

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

  • BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
  • HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
  • R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
  • Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
索引InnoDB引擎MyISAM引擎Memory引擎
BTREE索引支持支持支持
HASH 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

上面我们已经讲完一种实现原理的划分方式了。下面来讲一下其他两种划分，首先是应用方式，有主键索引、唯一索引、单列索引、联合索引。

  • 主键索引：主键创建时会自动创建索引，这个索引也就是主键索引
  • 唯一索引：使用UNIQUE修饰的字段，也会自动创建索引，也叫唯一索引，与主键索引的区别就是主键索引只有一个，唯一索引有多个
  • 单列索引：由一个字段组成的索引
  • 联合索引：有多个字段组成的索引

还有最后一种划分方式，按数据组织方式，这个可能理解起来有点抽象，下一节会讲到：

  • 聚簇索引：数据和索引在一起
  • 非聚簇索引：数据不和索引在一起

还有三个索引，空间索引、全文索引和前缀索引，个人对这个了解还不大足够，不过一般很少见，这里就不误导大家了。

其实还有许多中索引，不止上面提到的，个人理解，网上的博客有一些缺点，就是讲了很多索引，但他们没把索引划分好，所以很乱，学习这些索引最好要先划分一下，然后逐一学习较简单。

3.索引具体实现

在这里插入图片描述

本节主要会按上述讲解。

首先是B+树索引是怎么操作数据的？举个例子，下面建一个表，uid作为索引，传入96~102的值：

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`)
);

在这里插入图片描述
在叶子节点存放所有的索引值，非叶子节点值是为了更快定位包含目标值的叶子节点，叶子节点的值是有序的，叶子节点之间以链表形式关联。

上面是B+树索引也是单列索引，下面看下B+树联合索引是怎么实现的？还是举个例子，建一个表，uid和name是联合索引：

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`,`name`)
);

在这里插入图片描述
特点跟单列索引一样，不同之处在于他的排序，如果第一个字段相同时会按第二个索引字段排序，这里也给我们透露了一个信息：联合索引的组合字段是有顺序的，这一点很重要下面我们会讲到。

下面讲一下聚簇索引和非聚簇索引，什么是聚簇索引？聚簇索引指的是他的 索引和行数据 在一起存储。也就是在一颗B+树的叶子结点上存储的不仅是他的索引值，还有对应的某一行的数据。待会儿看图便知。

聚簇索引不是一种索引，而是一种数据存储组织方式 ！！！

crreate table test(
  col1 int not null,
  col2 int not null,
  PRIMARY KEY(col1),
  KEY(col2)
);

如上所示，表test 由两个索引，分别是主键 col1 和 普通索引 col2。那么这俩索引跟聚簇非聚簇有啥关系呢？

会生成一个聚簇索引和一个非聚簇索引(二级索引)，也就是说会组织两个索引树。主键索引会生成聚簇索引的树 以及以col2为索引的非聚簇索引的树。

InnoDb 将通过主键来实现聚簇索引 ，如果没有主键则会选选一个唯一非空索引来实现。如果没有唯一非空索引则会隐式生成一个主键。

在这里插入图片描述
与聚簇索引不同的是非聚簇索引在索引树叶子节点上除了索引值之外只存了主键值。而聚簇索引则存了一行数据。

假如有一条sql 语句select * from test where col2=93，上面这条语句会经历两次从索引树查找过程

1.第一步从非聚簇索引的索引树上找到包含col2=93的叶子节点，并定位到行的主键 3
2.第二步 根据主键 3 在从聚簇索引定位包含 主键=3的叶子节点并返回全部行数据。

以上说的都是基于InnoDb存储引擎的,MyISAM是不支持聚簇索引的，因为他的数据文件和索引文件是相互独立存储的 MyISAM存储引擎的索引树的叶子节点不会寸主键值，而存一个指向对应行的地址或者说是指针，然后再从表数据文件里去找，如下面图所示。

在这里插入图片描述

结论：

  • 聚簇索引:通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据

  • 非聚簇索引：又称二级索引，就是我们常用的普通索引，叶子节点存了索引值和主键值，在根据主键从聚簇索引查

对于InnoDb 存储引擎的B-tree索引，会按以下步骤通过索引找到行数据

  • 如果使用了聚簇索引(主键)，则叶子节点上就包含行数据，可直接返回
  • 如果使用了非聚簇索引(普通索引),则在叶子节点存了主键，再根据主键查询一次上面的聚簇索引，最后返回数据

对于MyISAM 存储引擎的B-tree索引，会按一下步骤通过索引找到行数据

  • 在MyISAM 的索引树的叶子节点上除了索引值之外即没存储主键，也没存储行数据，而是存了指向行数据的指针，根据这个指针在从表文件查询数据。

上了上面讲了B+树的单例、联合、聚簇和非聚簇索引，下面讲一下Hash索引：

哈希索引是基于哈希表来实现的，只有精确匹配所有的所有列才能生效。

也就是说假设有个hash索引 key (col1,col2) 那么每次只有 col1和col2两个字段都用才能够生效。因为生成hash索引的时候是根据一个hash函数对所有的索引列取hash值来实现的。

如下方图，有个hash索引key(name)
在这里插入图片描述

当我们执行 select * from User where name='张三'； 时怎么利用hash索引快速查找的？

第一步，计算出hash值，hash(张三) = 1287
第二步，定位行号，比如key=1287 对应的行号为3
第三步，找到指定行并且比较name列值是否为张三做个校验

4.索引的使用

关于索引的创建删除等语句问题这里就不再赘述了，这里主要讲一下使用的时候要注意的问题。

1.避免索引失效

下面使用一个联合索引来举例，表明使用索引可能出现的索引失效问题，这里规定了一个联合索引，有三个字段分别是：name、status、address

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

  • 全值匹配：对索引中所有列都指定具体值，该情况下索引不会失效

    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

  • 最左前缀法则：这个也就是前面所说的联合索引的字段是有顺序的原因，指的是查询从索引的最左前列开始，并且不跳过索引中的列。

    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;	 全值匹配，索引生效

explain select * from tb_seller where name='小米科技'; 使用了最左边的索引，索引生效

explain select * from tb_seller where name='小米科技' and status='1' ;   使用了左边两个索引，索引生效

explain select * from tb_seller where name='小米科技'  and address='北京市'\G;  跳过了中间索引，索引不生效

explain select * from tb_seller where and status='1' and address='北京市'\G;   跳过了左边第一个索引，索引不生效

explain select * from tb_seller where status='1';    跳过了左边第一个索引，索引不生效

explain select * from tb_seller where  status='1' and name='小米科技' and address='北京市'\G;	 虽然where语句后面顺序没满足索引字段的顺序，但是索引还是生效，与where语句后面顺序无关

  • 不要在索引列上进行运算操作， 索引将失效

    explain select * from tb_seller where substring(name,3,2) =‘科技’； 索引失效

  • 字符串不加单引号，造成索引失效（这个一般不会发生，只会发生在数字字符串，如果大家有养成好习惯，这个可以忽略）

    explain select * from tb_seller where  status= 1  这里其实status是varchar类型的，这里没使用单引号会失效

  • 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

    explain select * from tb_seller where name like `科技%`  索引不会失效

explain select * from tb_seller where name like `%科技`  索引失效

  • in 走索引， not in 索引失效

  • or 前后条件都要是索引，否则会索引失效

2.尽量使用覆盖索引

什么是覆盖索引呢？

就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。就是要查询的列和条件都用索引覆盖了。

覆盖索引是非聚簇联合索引的一种优化方案，我们都知道非聚簇索引里面没有存储整行的数据，所以当我们查到的时候通过主键回表再查一次获得整行数据。

但是假如我们所需要的列都是索引或者说我们可以适当舍弃一些列来提高性能（如果划算的话），我们就可以使用覆盖索引。可能这么说有点抽象，下面举个例子，还是上面的那个联合索引：

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;  查询条件满足都是索引，但是查询结果不满足，这时候会回表搜索

explain select name,status from tb_seller where name='小米科技' and status='1' and address='北京市'\G; 查询条件和查询结果都是索引，满足索引覆盖，不会回表检查

explain select name,id from tb_seller where name='小米科技' and status='1';   假如ID也是索引，这时也会满足索引覆盖，不仅仅只是要求是联合索引里面的字段
3.全表扫描优于索引

是的，在某些情况下，进行全表扫描的效率会高于索引，下面有篇博客，这里不多说了：

https://blog.csdn.net/ystyaoshengting/article/details/43154157?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522159629017119724811823887%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=159629017119724811823887&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allfirst_rank_ecpm_v3~pc_rank_v2-1-43154157.first_rank_ecpm_v3_pc_rank_v2&utm_term=%E7%B4%A2%E5%BC%95%E6%AF%94%E5%85%A8%E8%A1%A8%E6%89%AB%E6%8F%8F%E6%85%A2&spm=1018.2118.3001.4187

虽然全表扫描由于索引，但是由于我们数据有有优化器，它会提前发现这种情况，这个时候就不会使用索引了，而是直接使用全表扫描。

4. 索引设计原则

​ 索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 对查询频次较高，且数据量比较大的表建立索引。

  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

  • 使用唯一索引，区分度越高，使用索引的效率越高。

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

  • 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

    创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

触发器

1 介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型NEW 和 OLD的使用
INSERT 型触发器NEW 表示将要或者已经新增的数据
UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据

2 创建触发器

语法结构 :

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器

begin

	trigger_stmt ;

end;

示例

需求

通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

首先创建一张日志表 :

create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

创建 insert 型触发器，完成插入数据时的日志记录 :

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
end $

DELIMITER ;

创建 update 型触发器，完成更新数据时的日志记录 :

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

创建delete 行的触发器 , 完成删除数据时的日志记录 :

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);

update emp set age = 39 where id = 3;

delete from emp where id = 5;

3 删除触发器

语法结构 :

drop trigger [schema_name.]trigger_name

如果没有指定 schema_name，默认为当前数据库 。

4 查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

语法结构 ：

show triggers ；

锁

如果你学过Java并发编程，那么你一定对锁不会陌生，mysql里也有锁，因为MySQL也是要进行并发操作的。但是MySQL里的锁相对java来说就简单了很多，比较易学。

1.锁分类

相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

存储引擎表级锁行级锁页面锁
MyISAM支持不支持不支持
InnoDB支持支持不支持
MEMORY支持不支持不支持
BDB支持不支持支持
  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

上面是从数据操作的粒度来分，还有一种角度是从数据操作的类型来分，分成读锁（共享锁）和写锁（排他锁），这个就和并发编程的读写锁很像了。

从对数据操作的粒度分 ：

  • 表锁：操作时，会锁定整个表。

  • 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

  • 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

2.读锁与写锁

注意：上面我们也知道锁其实还有表锁和行锁的，这里就用表锁来演示下读锁与写锁了。

  • 读锁（共享锁）

    1. 当一个线程获得一个表的读锁，它只能读取这个表的数据
    2. 当一个线程获得一个表的读锁，它不能操作别的表的数据
    3. 当一个线程获得一个表的读锁，别的线程也能读取这个表，但不能进行写操作，写操作会被阻塞，但要特别注意，别的线程能读取这个表并不是说他也获得这个读锁，读锁只有一个

  • 写锁（排他锁）

    1. 当一个线程获得一个表的写锁，它可以对这个表读取数据，也可以修改数据
    2. 当一个线程获得一个表的写锁，它不能操作别的表的数据
    3. 当一个线程获得一个表的写锁，其他线程不能对这个表做任何操作
meiziziLOLOLO
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL高级特性
06-14
MySQL高级特性，尚硅谷的教学笔记
Mysql 高级特性
showadwalker的博客
02-16 855
文章目录1. 字符集1.1 服务器级别1.2 数据库级别1.3 表级别1.4 列级别1.5 utf8 和 utf8mb42. sql_mode2.1 宽松模式2.2 严格模式3. linux 数据库目录结构3.1 数据库文件存储路径3.2 数据库命令路径3.3 数据库配置文件4. 默认数据库 mysql5. 权限控制5.1 权限表5.2 角色管理6. Mysql 逻辑架构7. SQL 执行过程8. 缓冲池9. 存储引擎9.1 InnoDB 和 MyISAM 对比 1. 字符集 以下四个级别自上而下，下层级
MySQL的一些高级特性（读书笔记）
程序员小潘
11-17 3363
分区表 对用户来说，分区表是一个独立的逻辑表，但是底层是由多个物理子表组成。 MySQL实现分区表的方式：对底层表的封装。 意味着索引也是按照分区的子表定义的，没有全局索引。 这和Oracle不同，Oracle可以更加灵活的定义索引和表是否分区。 MySQL在创建表时使用 PARTITION BY 子句定义每个分区存放的数据。 执行查询时，优化器会根据分区定义过滤掉不需要查询的分区，这样就无需扫描...
高性能mysql读书笔记四-MySQL高级特性
热门推荐
liushangzaibeijing的博客
03-24 3万+
本篇博文是高性能Mysql一书中 mysql高级特性分析。主要介绍了分区表，视图、存储代码三大模块，详细内容包含：分区表概念，优劣,类型，原理、使用、示例等, 视图的概念，示例，存储代码主要包含 触发器、事件、存储过程、存储函数。本篇在介绍高级特性的时候，辅助以详尽的语法、示例，希望读者能有所收获。
Mysql-day01-安装索引视图存储过程触发器
tobebetter9527的博客
12-02 301
文章目录1. Linux 系统安装MySQL1.1 下载Linux 安装包1.2 安装mysql 略 1. Linux 系统安装MySQL 1.1 下载Linux 安装包 https://dev.mysql.com/downloads/mysql/5.7.html#downloads 1.2 安装mysql 略
高性能MYSQL（高级特性）
随风的博客
09-01 1322
分区表是一个独立的逻辑表，底层由多个物理表组成。实现代码实际是一组底层表的句柄对象。分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据存放在一起。 作用：1.表非常大以至于无法全部存放于内存中，或者最后的是热点数据其他是历史数据 2.分区表的数据更容易维护。 3.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 4.使用分区表避免某些特殊的瓶
MySQL高级特性篇.txt
01-13
MySQL课程天花板：6大范式讲解、7大日志剖析、7大SQL性能分析工具、9大存储引擎剖析、10大类30小类优化场景、15个不同锁的应用讲解、18种创建索引的规则、300+张高清无码技术剖析图
mysql高级特性 mysql 架构、索引调优、事务、日志备份文档
09-05
MySQL高级特性之索引优化 MySQL架构中，索引是提高数据库查询效率的关键技术之一。索引是帮助MySQL高效获取数据的数据结构，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上...
MYSQL入门第四章MYSQL高级特性.doc
04-10
在《MYSQL入门第四章MYSQL高级特性》中，我们深入探讨了MySQL数据库的高级功能，这些功能对于有效管理和分析数据至关重要。本章主要涵盖了以下几个关键知识点： 1. **集合函数**： - **COUNT(\*)**：用于计算查询...
Mysql——高级特性
不想逆天
08-22 928
分区表对用户来说分区表是一个独立的逻辑表，但是底层是多个物理字表组成的，实现分区的代码实际上是对一组底层表的引用的封装。MySQL实现分区表的方式——对底层表封装，因而没有全局索引，分区可以将相关数据存放在一起，如果 一次性批量删除会变得简单。 应用场景： 表很大无法全部放到内存中，或者只有部分热点数据，其他事历史数据。 分区操作，更容易维护，如可以清除整个分区。 数据分布在不同物理设备，高效利用
史上最详细MySQL入门教程，11个MySQL高级特性你知道吗？
f5465245的博客
10-12 369
MySQL作为目前最流行的关系型数据库管理系统之一，MySQL数据库有着许多的特性，在这些特性之中，有一些光彩夺目的高级特性，为MySQL数据库的强大功能实现贡献了不菲的力量，这篇文章我们就来一起看看这11个MySQL高级特性。 1.分区表 分区表是一种粗粒度的、简易的索引策略，适用于大数据量的过滤场景。最适合的场景是，在没有合适的索引时，对其中几个分区进行全表扫描，或者是只有一个分区和索引是热点，而且这个分区和索引都能够在内存中；限制单表分区数不要超过150个，并且注意某些导致无法做分区过滤的细节，分
MySQL详解（四）：MySQL的高级特性
qq_36807862的博客
08-05 1335
MySQL的高级特性 1. MySQL查询缓存 MySQL服务器有一个重要的特征是查询缓存。缓存机制简单地说就是缓存SQL语句和查询的结果，如果运行相同的SQL语句，服务器会直接从缓存中取出结果，而不需要再次去执行SQL语句。当数据被修改之后，在查询缓存中的任何数据均被清除。对于频繁更新的表，查询缓存是不适合的；而对于一些不经常改变数据并且有大量相同SQL查询的表，查询缓存会提高很大的性能； ...
MYSQL高级特性以及优化
weixin_41631245的博客
05-25 262
安装 以及 配置等忽略 1）Linux进入Mysql的语句的语句 mysql -u 用户名 -p 回车 输入密码 回车即可 进入mysql以后可以用sql对数据库进行操作（他安装了一个服务端 一个客户端是否需要？） mysql的配置文件有哪些 二进制日志文件 login-bin 用于主从复制 错误日志文件 记录启动 日志 关闭日志 错误日志 的文件 默认是关闭的 查询日志log文件（慢查询日志 可以查询哪条sql的执行速率较慢） 数据文件：两系统，frm文件（存放的是数据库表...
mysql高级特性篇,可以多看看,应付面试
weixin_47303191的博客
05-27 288
mysql高级特性篇,可以多看看,应付面试
MySQL系列-- 5. MySQL高级特性
weixin_34025051的博客
10-22 305
5. MySQL高级特性 5.1 分区表 对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理字表组成。 实现分区的代码实际上是对一组底层表的句柄对象(Handler Object)的封装。 对分区表的请求，都会通过句柄对象转换成对存储对象的接口调用。 所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是对底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔...
【详解】MySQL高级特性
xzzz2020的博客
04-12 645
目录1. 分区表1.1 原理1.2 分区表类型1.3 分区的作用1.4 分区的问题2. 查询缓存 1. 分区表 分区表是一个独立的表，由多个子表组成 分区表是对底层表的封装，索引也只是每个分区的索引而不是全局索引 作用 表特别大的时候，其中只有一部分是热点数据，其他的都是历史数据 分区表的数据更容易维护，可以单独对一个分区执行一些操作，如删除、优化、检查、修复、备份等。 分区表分布在不同物理...
高级特性——mysql必知必会（三）
qq_40369829的博客
01-09 1466
视图 使用 索引 使用 存储过程 使用 游标 使用 触发器 使用 事务处理 字符集和校对 安全管理 设置账户 设置账户权限 性能分析 EXPLAIN 视图 作用： 重用 sql语句。 简化复杂的sql。 保护数据，给予用户部分数据的访问权限而不是整个表。 更改数据的格式和表示。 视图不包含数据，是虚拟的表。每次使用视图时，必须处理查询的所有检索，...
mysql 高级特性
最新发布
08-16
- *2* [高性能mysql读书笔记四-MySQL高级特性](https://blog.csdn.net/liushangzaibeijing/article/details/123707221)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值