mysql版本不同会导致语法错误码_mysqlday05MySQL索引和函数.md

# 回顾

函数: 函数其实就是java中方法建议掌握常用的方法(看懂,查看讲义 或者 官方文档)

事务:(理论)

​事务的作用:将多个逻辑操作看成一个操作单元, 要么全部成功 要么全部失败

数据库中有两种事务 (了解)

​自动事务: 每操作一条语句 自动执行 commit操作

​set autocommit = 0 或 1 commit; 提交事务 rollback 回滚事务

​手动事务

​start transaction; 开启一次性事务 commit; 提交事务 rollback 回滚事务

事务的特性:

A : 原子性 C : 一致性 I:隔离性(isolation) D:持久性

多表查询案例课后练习题 (面试题)

自关联: 也是多表查询 , 将自己看成是两个表 , 一个表指向另一个表即可

**数据库优化** : 在面试之前能看下今天的内容

数据库随着时间增长, 数据库会越来越大 , 数据越来越多 , 查询的效率有可能降低

准备数据-> 在资料中 执行这个文件中的代码即可

# 慢查询日志(了解)

正常查询数据库 select * from user , 效率不高


慢日志查询: 这是数据库对sql语句执行检测的一个手段, 当查询执行到某个时间节点的时候 对这条sql进行记录(日志程序, 为了记录哪些SQL执行太慢)

query：查询

show variables like '%isolation%'

show variables like '%query%'


慢日志的记录 需要打开 时间的设置 应该更少

set global slow_query_log =1; 设置慢查询日志打开


set long_query_time = 3; 设置需要记录的比较慢的sql时间



不要使用select * from 表名 , 表示全表查询

执行sql语句自动记录日志(开发中 打开慢查询日志后 , 以后通过日志查看是否有 查询执行比较慢的sql)


扩展小内容: 数据其实保存在了本地文件夹 中的文件


# MySQL索引

所谓的索引就是:排序

第一章 MySQL索引【理解】

### 1.1 什么是索引

在现实生活中，我们经常去图书馆查阅图书。

现在我们将所有图书杂乱无章的摆放在一起，那么找一本书就像大海捞针一样效率非常低。

如果我们按分类整理排序后，根据类别去找对应的图书那么效率就很高了。其实这个整理排序的过程就是**索引**。

字典


MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索(查询)速度。

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。


索引提高查询效率

### 1.2 索引的优势与劣势

#### 1.2.1 优势

① 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本。

② 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。

#### 1.2.2 劣势

① 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

② 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。(删除数据后 id要不要往前补充)

使用主键自增 : 新增一直向后 删除 删除中间数据

总结: 建立科学合理的规范索引

### 1.3 索引分类和语法

**单列索引(给一列字段增加索引) id**

组合索引(给多列字段增加索引) id +name

#### 1.3.1 分类

- 普通索引：仅加速查询

- 唯一索引：加速查询 + 列值唯一(可以有null) 唯一约束 unique

- 主键索引：加速查询 + 列值唯一(不可以有null)+ 表中只有一个 , 主键约束 = 唯一+非空

- 组合(联合)索引：多列值组成一个索引，注意：最左匹配原则(一会演示)

#### 1.3.2 创建索引

index 索引


##### ① 直接创建(普通、唯一) (了解)

```sql

--创建主键索引

primary key 创建表的时候就有

-- 创建普通索引

create index 索引名 on 表名(列名);

-- 创建唯一索引

create unique index 索引名 on 表名(列名);

-- 创建普通组合索引

create index 索引名 on 表名(列名1,列名2....);

-- 创建唯一组合索引

create unique index 索引名 on 表名(列名1,列名2...);

```

##### ② 修改表时指定 (了解)

```sql

alter table 表名 add 修改表 增加..

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

alter table 表名 add primary key(id);

-- 添加唯一索引(除了NULL外，NULL可能会出现多次)

alter table 表名 add unique(列名); -- 索引名就是列名

-- 添加普通索引，索引值可以出现多次。

alter table 表名 add index(列名)；-- 索引名就是列名

alter table 表名 add 约束的名称

```

##### ③ 创建表时指定【掌握】

```sql

create table student(

id int ,

username varchar(32),

age int,

primary key(id), -- 主键

unique(username), -- 唯一

index(age) -- 普通

);

```


在数据库中, 我们提前将数据库设计好的 设计阶段就应该考虑到索引的问题

常用的加

#### 1.3.3 删除索引(了解)

开发中千万不要随意删除索引 致命的 测试自己玩可以

```sql

-- 直接删除

drop index 索引名 on 表名;

-- 修改表时删除 【掌握】

alter table 表名 drop index 索引名；

```

```sql

CREATE DATABASE web04;

USE web04;

-- 创建表指定索引

CREATE TABLE student(

id INT,

username VARCHAR(32),

age INT,

PRIMARY KEY(id),

UNIQUE(username),

INDEX(age)

);

-- 删除age索引

ALTER TABLE student DROP INDEX age;

```

#### 1.3.2 索引创建原则

索引加速查询效率

```markdown

1. 在经常需要 搜索 的列上建索引，这样会大大加快查找速度、

2. 在经常需要 连接(外键) 的列上建索引，可以加快连接的速度。 (多表查询 笛卡尔积=两个表数据的乘积)

3. 在经常需要 排序(日期) 的列上建索引，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。

* 注意:

那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的

创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率。

不要随意加入索引 不要随意篡改索引 不要随意删除索引

索引不是越多越好

```

### 1.4 效果演示

**1)准备千万记录**


```sql

-- 1. 准备表

CREATE TABLE `user`(

id INT,

username VARCHAR(32),

`password` VARCHAR(32),

sex VARCHAR(6),

email VARCHAR(50)

);

-- 2. 创建存储过程，实现批量插入记录

DELIMITER $$ -- 声明存储过程的结束符号为$$

CREATE PROCEDURE auto_insert()

BEGIN

DECLARE i INT DEFAULT 1;

START TRANSACTION; -- 开启事务

WHILE(i<=10000000)DO

INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));

SET i=i+1;

END WHILE;

COMMIT; -- 提交

END$$ -- 声明结束

DELIMITER ; -- 重新声明分号为结束符号

-- 3. 查看存储过程

SHOW CREATE PROCEDURE auto_insert;

-- 4. 调用存储过程

CALL auto_insert();

```

**2)没有索引的查询**

```sql

-- sql默认有缓存 下次在查询这条数据 时间成本降低

reset query cache; #重置查询缓存

-- 根据id

select * from user where id = 1234567;

-- 根据用户名

select * from user where username = 'jack1234567';

-- 根据邮箱模糊查询

select * from user where email like 'jack12345%';

```



**3)添加索引**

索引能够提升查询效率 , 索引不是越多越好

```sql

-- 在添加索引的时候 也会有时间的损耗 对数据进行排序

-- id设置为主键

alter table user add primary key(id);

-- username 设置为唯一

alter table user add unique(username);

-- 邮箱设置为普通

alter table user add index(email);

辅助索引 :除主键外的索引 (帮助索引) 主键索引索引只有一个 (帮助主键索引 快速查询 存储引擎中聊)

加索引的目的: 加快查询效率

软优化: 代码 编程 (索引 加入索引 提高查询效率 以空间换时间 )

硬优化: 硬件(最后方案)

在我们使用索引的过程中 索引有可能失效

```

## 索引失效(开发中经常问)

查询不改变原表数据

注意: 索引失效 , 全表的扫描

1. 模糊匹配查询 通配符字符串 %多个任意字符(索引注意 最左匹配原则)

% 所有 相当于整个表( 全表查询 ) 全表扫描

select * from user where username like '%jack123456';

2. 尽量避免使用or or的条件如果没有索引 采用全表扫描 (如果要使用 or 多个条件必须有都有索引)

select * from user where username like 'jack123456%' or password='e10adc3949ba59abbe56e057f20f883e';

3. 在有索引的列上进行计算会导致索引失效

id 列 id + 1 意味重新排序 重新打造目录

id = 123456 - 1 目录不变 快速找到123456 减去1

select * from user where id + 1 =123456;

4. 使用is null 或者is not null 导致索引失效 (为什么建议大家 在数据库都填入默认值 尽量不要使用null字段)

select * from user where username is not null;

1. 使用!= 或者 <> 导致索引失效 id <> 1

select * from user where id != 1;

2. 尽量不使用not in语法

select * from user where id not in (1,2,3);

3. 尽量省略隐式或者显示类型转换 cast 没用

使用cast函数可以将数据强转类型 . 数据库本身是存在 数据库自动类型强转

select * from user where username =12345;

约束和索引的区别

约束!=索引

主键约束 主键索引

唯一约束 唯一索引

index:是索引 不是约束

not null : 是约束 不是索引

create table 表名(

字段 值 [约束] 不是写索引

字段 值 primary key

字段 值 unique

字段 值 index 语法错误

)

### 1.5 索引数据结构【了解】

#### 1.5.1 介绍

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

索引 : 就是排序, 排序后能快速定位数据 而不是杂乱无章的排序

我们知道**索引**是帮助MySQL高效获取**排好序**的**数据结构**。

**索引= 排序后的数据结构**

为什么使用索引后查询效率提高很多呢？接下来我们来了解下。


在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。

```sql

select * from user where col1=6;

```

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

```sql

select * from user where col2=89;

```

#### 1.5.2 索引数据结构

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 数据结构的网站

数组

链表

1. 二叉树 左边子节点比父节点小，右边子节点比父节点大 每一个父节点有两个子节点(退化)

2. 红黑树(平衡二叉树): 左旋和右旋实现自平衡 : 保证节点 始终左右平衡(高度不可控)

每一次查询必须要经过磁盘的IO , 如果树的高度不可控 导致 查询效率慢

3. B-Tree (多路搜索平衡树) : 完全二叉树 , 实现整体树的自平衡 (所有的数据 开始平衡树) 减少树的高度 控制节点

4. B+Tree【MySQL使用】 B-Tree的变种

5. Hash 散列: 查询速度 I(O) 无延迟 , hash(hash碰撞算法) key是唯一的, 只能根据key获得value 解决不了范围查询的问题

1. JDK1.7 (数组+链表)

2. JDK1.8 (数组+红黑树) 如果链表长度《=8

叶子节点 ： 最后一层子节点 数据存储在叶子节点

非叶子节点： 中间 存储是索引+指针

#### 1.5.3 MySQL中的B+Tree

show global status like 'innodb_page_size'; 查看mysql默认的查询数据大小

MySQL数据库默认一次读取一页的大小(4个磁盘块) mysql 默认将一级节点加载到内存

一般来说 超过千万级数据 采取分库分表


```sql

-- 查看mysql索引节点大小

show global status like 'innodb_page_size';

```

MySQL中的 B+Tree 索引结构示意图：


今天的注意重点: 索引的作用 索引如何创建

# 数据库存储引擎【了解】

5.5..以后的版本 :每个mysql版本是有差异性的

MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎**MyISAM**和**InnoDB**。

当前版本 默认存储引擎:**InnoDB**


## 1 MyISAM

5.5之后的mysql 默认使用的都是 InnonDB引擎

MySQL5.5版本之前默认的存储引擎，不支持事务。

myisam创建之后 是三个文件

innodb创建之后是两个文件

```sql

CREATE TABLE myisam_tab(

id INT,

username VARCHAR(32)

)ENGINE = MYISAM;

```

它的索引文件和数据文件是分离的(**非聚集索引**)



## 2 InnoDB

MySQL5.5版本之后默认的存储引擎，支持事务

```sql

CREATE TABLE innodb_tab(

id INT,

username VARCHAR(32)

)ENGINE = INNODB;

```

它的索引和数据在同一个文件中(聚集索引)




设置不一样 查询和增加效率不一样

# 总结:

目标: mysql索引是啥? 索引如何创建? 面试中说出-> 索引失效的问题(开发经验)

了解-数据结构(不会 不影响开发)

**索引增加查询效率**

**创建索引 alter table 表名 add primary key / unique / index (索引的字段)**

**创建表的时候加索引**

**create table 表名( 字段 类型 , 字段 类型 ,primary key(字段) , unique (字段) , index(字段) )**

**索引创建的原则:** **经常 查询 的字段 , 外键字段 ,排序字段**

索引不是越多越好 , 索引也是要占据磁盘空间

数据结构: 了解, 能说出来每个数据结构的特点即可

数据库存储引擎: 不是必须用哪个引擎 , 而是哪个适合 就用哪个(特殊的场景 日志 一般情况下使用 innodb就可以)

一般情况下使用数据库默认的存储引擎就可以

作业:

1. 会索引的创建

2. 了解数据结构 , 了解 存储引擎

3. 多表练习(继续做- 继续交)

一键复制

编辑

Web IDE

原始数据

按行查看

历史