MySQL数据库

基础

概念

  • 数据库：按一定数据结构组织，存储，管理数据的仓库

    • 分类

      • 关系型数据库
      • 非关系型数据库

    • 表结构数据

      • 由固定列和任意行构成的表格数据集

      • 列为字段，行为记录

      • 与表格的区别

        • 以字段为基本存储和计算单位
        • 每个字段必有字段名
        • 同一表中字段不能重复
        • 每个字段数据类型一致

  • DBMS数据库管理系统：管理数据库的软件

  • SQL：结构化查询语言，和DBMS通信

  • 企业数据存储问题

    • 存储大量数据
    • 大量数据检索访问
    • 数据信息一致性，完整性
    • 数据共享和安全

MySQL服务

  • 启动和停止

    • cmd管理员身份运行--net start/stop mysql80

  • 登录和退出

    • mysql -h主机名 -P端口号 -u用户名 -p，输入密码0000

      • mysql -hlocalhost -P3306 -uroot -p

    • 退出 exit

SQL语言

  • 分类

    • 数据定义语言DDL

      • 创建，修改，删除数据库中各种对象（数据库，表，索引等）如CREATE,ALTER,DROP

    • 数据操作语言DML

      • 操作数据表的记录，如INSERT,UPDATE,DELETE

    • 数据库查询语言DQL

      • 查询数据表中记录，基本结构，如SELECT

    • 数据控制语言DCL

      • 定义数据库访问权限和安全级别，GRANT,REVOKE

  • 书写要求

    • 单行或多行，以分号结尾
    • 大小写不敏感
    • #或--单行注释。/**/多行

  • select语句书写顺序

    • SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT

  • select语句执行顺序

    • FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT

DDL数据定义语言

数据库管理

  • 查看

    • show databases;
    • show create database 数据库名称

  • 创建

    • create database 库名

  • 选择使用数据库

    • use 库名

  • 修改数据库编码

    • alter database 数据库名称 character set 编码格式

  • 删除数据库

    • drop database 库名

数据表管理

  • 查看

    • 当前数据库所有表：SHOW TABLES
    • 查看创建好的表：show create table 表名
    • 查看表结构：DESC 表名

  • 创建

    • 建表前先use数据库 CREATE TABLE 表名（字段名，字段类型，[约束条件]）

      • 字段类型

        • int（n）:n位整数
        • float（n,m）：n位数字，其中m位小数，默认float（10,2）
        • decimal（n,m）：n位数字，其中m位小数，适合精度较高数据存储，默认decimal（10,0）
        • char（n）：固定长度字符串，默认长度为1
        • varchar（n）：可变长度字符串，必须指定长度
        • text：长文本字符串，不可指定长度
        • date：日期型，yyyy-mm-dd
        • time：时间型，hh:mm:ss
        • datetime：yyyy-mm-dd hh:mm:ss

      • 约束条件

        • 定义

          • 在表上强制执行的数据检验规则，用于保证数据完整和准确，主要对空值和重复值进行约束

        • 常用约束条件

          • PRIMARY KEY

            • 主键约束

              • 每个表只有1个主键

              • 主键值必须非空不重复

              • 可设置单字段主键&多字段联合主键

              • 添加主键约束

                • CREATE TABLE 表名（字段名1，字段类型1，PRIMARY KEY，...，字段名n，字段类型n）
                • 表级：CREATE TABLE 表名（字段名1，字段类型1，......字段名n，字段类型n）constraint [主键约束名 PRIMARY KEY]（字段名1[，字段名2，...字段名n]

          • NOT NULL

            • 非空约束，不可为空

              • CREATE TABLE 表名（字段名1，字段类型1，NOT NULL，...，字段名n，字段类型n）

          • UNIQUE

            • 唯一约束，不可重复

              • 指定字段取值不能重复，可以为空，但只可出现一个空值

              • 添加唯一约束

                • 列级：CREATE TABLE 表名（字段名1，字段类型1，UNIQUE，...，字段名n，字段类型n）
                • 表级：CREATE TABLE 表名（字段名1，字段类型1，......字段名n，字段类型n）constraint [唯一约束名 UNIQUE]（字段名1[，字段名2，...字段名n]）

          • DEFAULT

            • 默认约束，默认值

              • CREATE TABLE 表名（字段名1，字段类型1，DEFAULT value，...，字段名n，字段类型n）

          • FOREIGN KEY

            • 外键约束

              • 在1张表中执行数据插入，更新，删除时，DBMS会跟另一张表进行对照
              • 某表某字段依赖于另一表某字段
              • 每个外键值和另一表主键值对应，主键所在表为主表，外键所在表为从表
              • CREATE TABLE 表名（字段名1，字段类型1，......字段名n，字段类型n constraint [外键约束名] FOREIGN KEY（字段名）reference 主表 （主键字段））

          • 标识列AUTO_INCREMENT

            • 自增

              • 指定字段值自动生成，默认从1开始，每增加一条记录，该字段取值增长1
              • 仅适用于整数，配合键一起使用，每个表至多一个
              • CREATE TABLE 表名（字段名1，字段类型1，PRIMARY KEY AUTO_INCREMENT，...，字段名n，字段类型n）

          • 主键和唯一的区别

            • 主键不允许空值，唯一可以有1个空值
            • 一个表中只能有1个主键，唯一可以有多个

  • 复制

    • 仅复制表结构

      • CREATE TABLE 表名 LIKE 旧表名

    • 复制数据+结构

      • CREATE TABLE 表名 select*from 旧表名

  • 修改

    • 修改表名

      • ALTER TABLE 表名1 rename 表名2

    • 修改字段名

      • ALTER TABLE 表名 change 字段名1 字段名2 字段类型 [约束条件]

    • 修改字段类型

      • ALTER TABLE 表名 modify 字段名 字段类型

    • 修改字段排列位置

      • ALTER TABLE 表名 modify 字段名 字段类型 after 某字段

    • 添加字段

      • ALTER TABLE 表名 add 字段名 字段类型 [位置（first/after 某字段），没有位置默认最后一行]

    • 删除字段

      • ALTER TABLE 表名 drop 字段名

    • 删除数据表

      • drop table 表名 if exists

DQL数据查询语言(先use库)

单表查询

  • 全表查询

    • select*from表名

  • 查询指定字段

    • select 字段1，字段2...from表名

  • 设置别名

    • select 字段名 as 字段别名 from 表名 as 表别名

  • 查询不重复记录

    • select distinct 字段名 from 表名

  • 条件查询

    • select 字段1，字段2...from表名 where 条件

      • 运算符

        • 算术运算符+-*/

          • 加号+

            • 两个操作数都为数值，做加法运算
            • 其中一个为字符，试着转化为数值，转化成功继续加法运算，转化失败，字符型数值为0
            • 其中一个为null，结果为null

        • 逻辑运算符not and or

        • 比较运算符= > < >= <= !=/<>,between...and, in,is[not]null

          • between...and包含数值左右，字符串不包括
          • in列表值类型必须一致，且列表值不可以使用通配符
          • =或<>不可判断null，is不可判断数值

        • 模糊查询

          • select 字段1，字段2...from表名 where 字段[not] like 通配符
          • %匹配多个字符，_匹配1个字符

  • 查询结果排序

    • select 字段1，字段2...from表名 order by 字段1 排序方向[,字段2 排序方向]
    • 多字段排序，先按第一个字段排序，第一个字段值相同时才按第二个字段排序
    • asc升序，从小到大,desc降序，从大到小

  • 限制查询结果数量

    • select 字段1，字段2...from表名 limit [偏移量,]行数

  • 聚合运算

    • 将多行数据聚集计算为1行

      • 对null以外的数据进行聚合运算

        • sum()
        • avg()
        • count(*)
        • max() min()

      • 可与distinct搭配实现去重后字段的运算

    • select 分组字段，聚合字段；注意：先分组再聚合，不聚合，用where

  • 分组聚合

    • 分组后查询

      • select 分组字段1，[分组字段2...]，分组函数from表名 [where 条件] group by 分组字段1，[分组字段2...]
      • 将查询结果按照一个或多个字段分组，字段值相同为1组，对每个组聚合运算

    • 分组筛选

      • select 分组函数，字段 from表名 [where 条件] group by 分组字段1，[分组字段2...] having筛选条件

      • where和having的区别

        • where针对于表中记录的筛选，having作用于分组结果的过滤
        • where在分组和聚合之前筛选行，having在分组和聚合后筛选分组的行，where字句不可包含聚合函数

多表查询

  • 通过不同表中具有相同意义的字段，将多表进行连接，查询不同表中字段信息

  • 笛卡尔乘积现象：由于缺少连接条件，m行n行表相连，结果为mn行表

  • 联合查询

    • 把多条select结果合并为一个结果集

    • 被合并表的列数，顺序，数据类型必须一致

    • union去重

      • select 字段1，字段2...from表1 union select 字段1，字段2...from表2

    • union all 不去重

  • 连接方式

    • SQL92标准仅支持内连接，SQL99标准支持除全连接的其他连接

    • 内连接

      • 等值连接

        • select 字段1，字段2...from表1 inner join 表2 on 表1.字段=表2.字段

      • 非等值连接

        • select 字段1，字段2...from表1 inner join 表2 on 表1.字段between表2.字段1 and 表2.字段2

      • 自连接

        • select 字段1，字段2...from表1 inner join 表1 on 表1.字段1=表1.字段2

      • 连接满足条件的行

    • 外连接

      • 左连接

        • 查询结果为主表中所有记录
        • 若从表中有连接条件成立的，显示匹配值，若连接条件不成立，显示null

      • 右连接

      • 全连接

    • 连接结果确定

      • 方向性：写在前面的是左表，写在后面的是右表
      • 主附关系：主表要出所有数据范围，附表与主表无匹配时标记为null，内连接无主附关系
      • 对应关系：关键字段有重复值为多表，无重复值为一表

子查询

  • 1个select语句中包含1个或多个select语句

  • 位置

    • where/having 后：作为主查询条件
    • from后：作为主查询的一个表
    • exists后：表子查询

  • 分类

    • 标量子查询：返回单行单列
    • 行子查询：返回1行多列
    • 列子查询
    • 表子查询

  • 操作符

    • [not]in：字段in数据表（子查询）

    • any

      • 字段 比较 any 数据表（子查询）

    • all

常用函数

select 函数名（实参）from 表

  • 字符串函数

    • CONCAT(str1,str2...)

    • 分组合并GROUP_CONCAT([distinct]str[orderby str][separator])

    • INSTR(str,substr):返回子字符串在文本字符串中第一次出现的位置（从1开始）

    • LEFT(str,len):返回左边len个字符

    • RIGHT(str,len):返回右边len个字符

    • SUBSTR(exp,start,len)

      • exp:字符串，二进制字符串，文本，列，包含列的表达式
      • start：整数或可转为int的表达式，指定子字符串开始位置（从1开始）
      • len：整数或可转为int的表达式，指定子字符串长度

    • TRIM(STR):删除两遍空格

    • REPLACE(str,from,to)

    • REPEAT(str,count)

    • REVERSE(str)

    • UPPER(str) LOWER(str)

  • 数学函数

    • ABS(n)
    • FLOOR(n) CEILING(n)
    • ROUND(n,d)
    • RAND(n)：返回0-1的浮点数，若指定n，则生成随机数不变化

  • 日期与时间函数

    • DATE(date):返回指定日期、时间表达式的日期部分

    • WEEK(date):返回指定日期是一年中的第几周 MONTH(date) QUARTER(date) YEAR(date)

    • ADDDATE(date,INTERVAL expr,type) SUBDATE(date,INTERVAL expr,type)

      • expr:对date进行加减的表达式字符串，如 2
      • type：指明expr如何被解释，如 day

    • DATE_FORMAT(date,format)

      • 根据format字符串格式化date值
      • %Y-%m-%d %h-%i-%s

    • CURDATE()：日期 CURTIME()：时间 NOW()：日期时间

    • DATEDIFF(expr1,expr2)：返回两者之间的天数

  • 逻辑函数

    • IFNULL(expr,alt_val)

      • if expr==null,return alt_val;else return expr_val

    • IF(expr1,expr2,expr3)

      • if expr==true,return expr2;else return expr3

    • CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4] ELSE expr END

      • if expr1==true,return expr2;if expr3==false, return expr4

  • 开窗函数

    • 在满足某种条件的记录集合上执行的特殊函数。本质是聚合运算，只不过更具灵活性，对数据的每一行都使用以该行相关的行并返回计算结果

      • 静态窗口：对于每条记录都要在此窗口执行函数，窗口大小固定
      • 动态窗口：窗口大小不固定

    • 语法

      • 开窗函数名（[字段名]over（[partition by 分组字段][order by 排序字段][细分窗口]））

      • 当前行属于某个窗口，窗口由over关键字指定函数执行的窗口范围。

        • 若为空，开窗函数基于where字句的所有行计算

        • 不为空，有3个参数设置窗口

          • partition by：按指定字段分区，2个分区由边界分隔，开窗函数在不同分区分别执行，跨越边界时重新初始化

          • order by：按指定字段排序，可单独使用

          • frame：定义分区子集规则，通常作为滑动窗口

            • 滑动窗口范围指定：between frame-start and frame-end

              • current row:边界是当前行
              • unbounded preceding：边界是分区第一行
              • unbounded following：边界是分区最后一行
              • expr preceding：边界是当前行减去expr
              • expr following：边界是当前行加上expr

    • 序号函数

      • ROW_NUMBER()：显示分区中不重复不间断的序号
      • DENSE_RANK()：显示分区中重复不间断的序号
      • RANK()：显示分区中重复间断的序号

    • 开窗函数与聚合函数区别

      • 普通聚合函数是将多条记录聚合为一条；
      • 开窗函数是每条记录都会执行函数，有几条记录执行完还是几条。

DML数据操作语言

插入数据

  • 字段名与字段值数据类型，个数，顺序必须一一对应

  • 指定字段名插入

    • INSERT INTO 表名（字段名1[，字段名2，...字段名n]）values （字段值1[，字段值2，...字段值n]）

  • 不指定字段名插入

    • INSERT INTO 表名 values （字段值1[，字段值2，...字段值n]）

  • 批量导入

    • LOAD DATA INFILE “文件路径.csv” into table 表名 fields terminated by "," ignore 1 lines

    • 文件路径获取：选中文件后，shift+右键，选择复制为路径

      • 路径不可为中文
      • 路径分隔符\要转换为/或\，Ctrl+H进行替换

更新

  • UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2,....]where 更新条件
  • 若无where，则为批量更新，需设置权限：set sql_safe_updates=0

删除

  • 数据表：DROP TABLE 表名

  • 数据，保留表结构

    • DELETE FROM 表名[where 条件]
    • TRUNCATE 表名

MySQL高级

逻辑架构

  • 第一层是服务器层，主要提供连接处理、授权认证、安全等功能。

  • 第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等内置函数。

  • 第三层是存储引擎层，负责数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应服务器请求。

    • 存储引擎

      • InnoDB

        • ① MySQL5.1 开始的默认引擎，最大的优点是支持事务和外键，InnoDB 的性能和自动崩溃恢复特性使它在非事务型需求中也很流行，一般应该优先考虑使用 InnoDB。
        • ② 底层存储结构是 B+ 树，每个节点都对应 InnoDB 的一个页。非叶子节点只有 key 值，叶子节点包含完整的数据。
        • ③ 支持行锁，采用 MVCC 支持高并发，实现了四个标准的隔离级别，默认级别是可重复读，通过间隙锁防止幻读。
        • ④ 基于聚簇索引，对主键查询有很高的性能。
        • ⑤ 内部做了很多优化，例如加速读操作的自适应哈希索引、加速插入操作的缓冲区等。

      • MyISAM

        • ① MySQL5.1及之前的默认引擎，提供的特性包括全文索引、空间索引等，不支持事务、行锁和外键。
        • ② 最大的缺陷是崩溃后无法恢复，在插入和更新数据时需要锁定整张表，效率低。
        • ③ 对于只读的数据或者表比较小、可以忍受修复操作的情况可以使用 MyISAM。

      • Memory

        • ① 如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，可以使用 Memory 表。
        • ② 数据保存在内存，不需要磁盘 IO，表的结构在重启后会保留，数据会丢失。
        • ③ 支持哈希索引，查找速度快。
        • ④ 使用表锁，并发性能低。

视图

  • 概念：虚拟表，行列来自定义视图的查询中使用的表，在使用视图时动态生成，保存SQL逻辑，不保存查询结果

  • 用处：封装SQL语句，便于代码重用

  • 创建并使用视图

    • CREATE VIEW 视图名 AS 查询语句； SELECT * FROM 视图名 where...

  • 修改视图

    • CREATE OR REPLACE VIEW 视图名 AS 查询语句；
    • ALTER VIEW 视图名 AS 查询语句；

  • 删除视图

    • DROP VIEW 视图名，视图名

  • 查看视图

    • SHOW CREATE VIEW 视图名

游标

  • 游标是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

数据库的三大范式

  • 范式是数据库设计规范，范式越高则数据库冗余越小，但查询也更复杂，一般只需满足第三范式。

  • 第一范式：每一张表都有主键，每一个字段必须有原子性，不可再分。

    • 记录不可有重复的属性，某个属性不可有多个值

  • 第二范式：建立在第一范式的基础之上，要求所有的非主键字段完全依赖主键，不要产生部分依赖。

  • 第三范式：建立在第二范式的基础之上，要求所有非主键直接依赖主键，不要产生传递依赖。

事务和锁

  • 事务

    • 事务定义：1个或1组SQL语句组成1个执行单元，要么全部执行，要么全部不执行

    • 事务ACID属性

      • 原子性Atomicity

        • 事务是不可分割的工作单位，事务中的操作要么都发生要么都不发生

      • 一致性Consistency

        • 只有合法的数据可以被写进数据库，否则事务回滚

      • 隔离性Isolation

        • 一个事务的执行不能被另一个事务干扰

      • 持久性Durability

        • 一个事务一旦被提交，对数据库中的数据改变是永久的，接下来其他操作对其无影响

    • 事务的隔离级别

      • 未提交读：事务中的修改即使没有提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还存在不可重复读和幻读，很少使用。
      • 提交读：多数数据库的默认隔离级别，事务只能看见已提交事务的修改。存在不可重复读，两次执行同样的查询可能会得到不同结果。
      • 可重复读：是 MySQL 的默认隔离级别，解决了不可重复读，保证同一个事务中多次读取同样的记录结果一致，InnoDB 通过 MVCC 解决。但无法解决幻读，幻读指当某个事务在读取某个范围内的记录时，会产生幻行。
      • 可串行化：最高隔离级别，通过强制事务串行执行避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少使用，只有非常需要确保数据一致性时考虑。

    • 并发问题：同时运行的多个事务访问数据库中相同的数据

      • 脏读：T2更新了字段但没提交，同时T1读取数据，如果T2回滚，那么T1读取了无效数据

        • read uncommitted

      • 不可重复读：T1读取了一个字段，然后T2更新字段并成功，T1再读此字段值不同

        • read uncommitted,read committed

      • 幻读：T1读取了一个字段，然后T2插入记录并成功，T1再读此表，会多出几行

        • read uncommitted,read committed,repeatable read

    • 创建事务

      • 隐式事务

        • 无开启和结束标志，如insert,update,delete

      • 显式事务

        • 设置自动提交功能为禁用

          • set autocommit=0; start transaction;可选 语句1， (savepoint 回滚点名称） 语句n... commit;提交事务 rollback;回滚 (rollback to 回滚点名称)

    • MVCC

      • 多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞读，写也只锁定必要行。
      • InnoDB 通过在每行记录保存两个隐藏列来实现，这两个列分别保存了行的创建和过期时间，但不是实际时间而是系统版本号，每开始一个新的事务系统版本号会自动递增，事务开始时的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号比较。
      • MVCC 只能在提交读和可重复读两个级别工作，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行，而可串行化则会对所有读取的行都加锁。

  • 锁

    • 乐观锁和悲观锁

      • 乐观锁：每次读数据时都认为别人不会修改，所以不会加锁，但在更新时会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。适用于多读场景，可以提高吞吐量。

      • 悲观锁：每次读数据时都认为别人会修改，所以每次读数据时都会加锁，这样别人想拿数据就会阻塞直到拿到锁。比如 MySQL 的行锁、表锁、读锁、写锁等，都是在做操作前先加锁。

        • 读写锁

          • 读锁即共享锁（S 锁），写锁即排它锁（X 锁），读锁是共享的，相互不阻塞，多个客户在同一时刻可以读取同一资源。写锁是排它的，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入。
          • 写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但读锁不能插入到写锁前面。

        • 表锁和行锁

          • 表锁会锁定整张表，加锁快，开销小，不会出现死锁，但并发度低、锁冲突概率高。适合以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如 Web 应用。
          • 行锁可以最大程度地支持并发，锁冲突概率低，但开销大，会出现死锁。行锁只在存储引擎层实现，而服务器层没有实现，适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如在线事务处理系统

    • 死锁

      • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。锁的行为和顺序和存储引擎相关，以同样的顺序执行语句，有些存储引擎会产生死锁有些则不会。

      • 解决和避免死锁

        • 发生死锁后，InnoDB 一般都能自动检测到并使一个事务释放锁并回滚，另一个事务获得锁继续完成事务。但在涉及外部锁或表锁时，InnoDB 并不能完全自动检测到死锁，需要设置锁等待超时参数 innodb_lock_wait_timeout 解决。
        • MyISAM：在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，不会出现死锁。
        • InnoDB：① 如果要更新记录，应该直接申请足够级别的锁，即排它锁。② 多个事务应尽量约定以相同的顺序来访问表。

存储过程、函数、触发器

  • 存储过程

    • 存储过程是由流控制和 SQL 语句组成的程序，经过编译和优化后存储在数据库服务器中，使用时只需要调用即可。

    • 好处

        • 使用流控制语句编写，具有较强的灵活性。

  • 保证数据安全性，使没有权限的用户间接存取数据库。

  • 保证数据完整性，使一组相关动作在一起执行。

  • 调用存储过程前，数据库已经对其进行了语法分析，并给出优化执行方案，可以改善 SQL 语句的性能。

  • 降低网络通信量，减小负载。

  • 函数

    • 由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用

    • 和存储过程的区别

        • 存储过程的参数有 in，out，inout 三种，存储过程声明时不需要返回类型；函数参数只有 in，需要描述返回类型，且函数中必须包含一个有效的 return 语句。

  • 存储过程可以有 0 或多个返回值，适合做批量插入、更新；函数有且仅有一个返回值，针对性更强。

  • 存储过程可以返回参数，如记录集，函数只能返回值或者表对象。

  • 存储过程一般作为独立部分执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，所以在查询中位于from 关键字后面，SQL 语句中不可以含有存储过程。

  • 触发器

    • 触发器是一段能自动执行的程序，和存储过程的区别是，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。触发器在对某一个表或者数据进行操作时触发，例如进行 UPDATE、INSERT、DELETE 操作时，系统会自动调用和执行该表对应的触发器。触发器一般用于数据变化后需要执行一系列操作的情况，比如对系统核心数据的修改需要通过触发器来存储操作日志的信息等。

MySQL索引

索引也叫键，是帮助存储引擎快速找到记录的一种数据结构。

在 MySQL 中，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。索引可以包括一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能使用索引的最左前缀。

优缺点

  • 优点

      • 大大减少服务器需要扫描的数据量、帮助服务器避免排序和临时表、将随机 IO 变成顺序 IO。

  • 通过索引列对数据排序可以降低 CPU 开销

  • 缺点

      • 实际上索引也是一张表，保存了主键与索引字段，并指向实体类的记录，也要占用空间。

  • 降低了更新表的速度，因为更新表时 MySQL 不仅要保存数据，还要保存索引文件。

  • 对于非常小的表，大部分情况下会采用全表扫描。对于中到大型的表，索引非常有效。

索引建立的规范

  • 应该创建索引的列

    • 在经常需要搜索的列上，可以加快搜索的速度 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

  • 不该创建索引的列

    • 对于那些在查询中很少使用或者参考的列不应该创建索引。增加索引降低了系统的维护速度和增大了空间需求。 对于那些只有很少数据值或者重复值多的列也不应该增加索引。 这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 对于那些定义为text, image和bit数据类型的列不应该增加索引。 这些列的数据量要么相当大，要么取值很少。 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

索引结构

  • B Tree

    • 所有的值都是顺序存储的，并且每个叶子页到根的距离相同。B-Tree 索引能够加快访问数据的速度，存储引擎从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点的指针指向的是被索引的数据。
      • 必须按照索引的最左列开始查找。

  • 不能跳过索引中的列，例如索引为 (id,name,sex)，不能只使用 id 和 sex 而跳过 name。

  • 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

  • B+ Tree

    • 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；不可能在非叶子结点命中；非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。更适合文件索引系统；

  • Hash

    • 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。
      • 数据不是按照索引值顺序存储的，无法排序。

  • 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。

  • 只支持等值比较查询，不支持范围查询。

索引分类

  • 逻辑分类

    • 按功能划分

      • 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL
      • 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
      • 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
      • 全文索引：它查找的是文本中的关键词，主要用于全文检索。

    • 按列数划分

      • 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
      • 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

  • 物理分类

    • 聚簇索引

      • 聚簇索引不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B 树索引和数据行。当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，由于无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
      • 优点：可以把相关数据保存在一起；将索引和数据保存在同一个 B 树中，获取数据比非聚簇索引要更快。
      • 缺点：如果数据全部在内存中会失去优势；更新代价高，强制每个被更新的行移动到新位置；插入行或主键更新时，可能导致页分裂，占用更多磁盘空间。

    • 非聚簇索引

      • 非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

索引失效

  • ① 隐式类型转换，常见情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。
  • ② 如果条件中 OR 只有部分列使用了索引，索引会失效。
  • ③ 执行 LIKE 操作时，最左匹配会被转换为比较操作，但如果以通配符开头，存储引擎就无法做比较，索引失效
  • ④ 如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
  • ⑤ 对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。
  • ⑥ 如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。

优化查询

  • 避免全表扫描 考虑在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，尽量用 BETWEEN 取代。
  • 优化 COUNT 查询 COUNT 可以统计列的数量，统计列值时要求列非空；COUNT 还可以统计行数，当 MySQL 确定括号内的表达式不可能为 NULL 时，实际上就是在统计行数。当使用 COUNT(*) 时，会忽略所有列而直接统计行数。 某些业务场景不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。
  • 避免子查询 在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。
  • 禁止排序 当查询使用 GROUP BY 时，结果集默认会按照分组的字段进行排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。
  • 优化分页 在偏移量非常大的时候，需要查询很多条数据再舍弃，代价非常高。最简单的优化是尽可能地使用覆盖索引扫描，而不是查询所有的列，然后做关联操作再返回所需的列。还有一种方法是从上一次取数据的位置开始扫描，避免使用 OFFSET。
  • 优化 UNION MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。
  • 使用用户自定义变量 用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。