MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

1.sql 分类

  • DDL 数据定义语言（操作表）
  • DML 数据操作语言 （增删改）
  • DQL 数据查询语言(查询)
  • DCL 数据控制语言（创建数据库用户）

2.DDL(数据定义语言)

1.查询

show databases;

2.创建

如果不存在则会创建，如果存在则不会创建

create database 数据库名字;

指定默认字符集

create database 数据库名字 default charset utf8mb4;

3.删除数据库

drop database 数据库名;

drop database if exists 数据库名;

4.使用数据库

use 数据库名

查询当前目录

select database();

5.查询所有表

show tables

6.创建表

1.创建

create table 表名(
    字段1 字段1类型[],
    #id int comment '编号'
    #comment "1" <-芝士注释
    ...
)[表注释];
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| idcard | text        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

如果有上图的表，则可以创建字段如下图所示

create table id(
    id int comment 'id',
    name varchar(20) comment 'name',
    age int comment 'age',
    gender varchar(10) comment 'gender',
    idcard text comment 'idcard'
);

用int表示整数

vechar（长度）表示字符

2.显示表

desc 表名

show create table 表名;

  richu_user | CREATE TABLE `richu_user`(
  idint DEFAULT NULL COMMENT 'id',
  name varchar(20) DEFAULT NULL COMMENT 'name',
  age int DEFAULT NULL COMMENT 'age',
  gender varchar(10) DEFAULT NULL COMMENT 'gender',
  idcardtext COMMENT 'idcard'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

7.数据类型

1.数值类型

1.tinyint 1byte (小整数) （unsigned）(无符号)

2.smallint 2byte

3.mediumint 3byte

4.int或integer 4byte

5.bigint 8byte

6.float (小数) 4byte

7.double (小数) 8byte

9.decimal (小数)

double(4,1)->4(长度),1(小数点位数)

2.字符类型

(二进制一般用于记录图片以及音频，但由于不方便管理，因此使用相对较少)

1.char 定长字符串

2.varchar 变长字符串

3.tinyblob 不超过255个字符的二进制数据

4.tinytaxt 短文本字符

5.blob(二进制) 长文本

6.text(文本) 长文本

7.mediumblob(二进制)

8.mediumtext(文本)

9.longblob(二进制)

10.longtext(文本)

3.日期类型

1.date YYYY-MM-DD 3byte

2.time HH:MM:SS 3byte

3.year YYYY 1byte

4.datetime YYYY-MM-DD HH:MM:SS 8byte

5.timestamp (最大到2038年) YYYY-MM-DD HH:MM:SS 4byte

练习:创建一张员工信息表

1.编号（数字）

2.员工工号（字符）

3.员工姓名（字符）

4.性别（字符）

5.年龄（无负数）

6.身份证 （字符）

7.入职时间（年月日）

8.修改表

1.添加字段

alter table 表名 add 新增内容;

alter table richu add new_id int comment ‘new id’;

2.修改字段

alter table 表名 modify 字段名 新数据类型（长度）;

alter table 表名 change 旧字段名 新字段名 类型;

3.删除字段

alter table 表名 drop 字段名

4.修改表名

alter table 表名 rename to 新表名;

5.删除表

drop table [if exists] 表名;

6.清空表

truncate table 表名;

3.DML(数据操作语言)

1.添加数据(insert)

1.给指定字段添加数据

insert into 表名(字段1，字段2) values(值1，值2);

2.给全部字段添加数据

insert into 表名 values(值1，值2);

3.批量添加数据

insert into 表名(字段1，字段2) values(值1，值2),(值1，值2),(值1，值2),(值1，值2);

insert into 表名 valuse(值1，值2),(值1，值2),(值1，值2);

2.修改数据(update)

updata 表名 set 字段1=值1，字段二=值2，...[where 条件];(在使用where时不用加上括号)

where 条件接 字段=值

如果不带where语句则会影响全局

在指定的字段中指定的值行修改set后面指定的字段的内容。

3.删除数据(delete)

delete from 表名 [where 条件];(在使用where时括号不用加)

如果不加where条件则会将整张表删除

3.DQl(数据查询语言)

1.基本查询

1.查询多个字段

多个字段

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

所有字段

select* from 表名;

2.在查询的时候起别名

select id as id1 from 表名;

selct id id1 from 表名;

其中id1是别名

3.查询后去掉重复值

select distinct 字段 from 表名;

2.条件查询(where)

1.语法

select 字段列表 from 表名 where 条件

2.条件

1.=

2.>

3.<

4.>=

5.<=

6.is null

7.is not null

8.<> (不等于)

9.&& and

10.|| or

11.between 1 and 2 (大于等于1，并且小于等于2)

12.! not

13.in(1,2,3) (满足=1，=2，=3即可)

14like 占位符 (模糊匹配) _(单个字符) %(匹配任意个字符，在最后一位，在中间可以写成%X%)

一个下划线为一个字符，两个下划线则为两个字符

3.聚合函数(count、max、min、avg、sum)

1.count(统计数量)

select count(字段) from 表名

不统计null值

2.max(最大值)

select max(字段) from 表名;

3.min(最小值)

select min(字段) from 表名;

4.avg(平均值)

select avg(字段) from 表名;

5.sum(求和)

select sum(字段) from 表名;

4.分组查询(group by)

where 是分组前进行过滤，having是分组后进行过滤。

1.语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 having [分组后的过滤条件];

2.选择分组

select 字段1,count(id) from 表名 group by id;

3.条件选择分组

select 字段1,avg(id) from 表名 group by id having count(*) >=3;

根据id分组，分组后进行过滤数量大于3的

5.排序查询(order by)

1.语法

select 字段名 from 表名 order by 字段名 asc;

select 字段名 from 表名 order by 字段名 desc;

select 字段名 from 表名 order by 字段名1 desc,字段名2 asc;

2.排序方式

ASC (升序) (默认可以省略)
DESC (降序)

6.分页查询(Limit)

select 字段列表 from 表名 limit 起始索引，查询记录数;

起始索引从0开始，开始索引=（查询页码-1）*每页显示记录数

7.DQL的执行顺序

1.书写顺序

select

​ 字段列表

from

​ 表名列表

where

​ 条件列表

group by

​ 分组字段列表

having

​ 分组后条件列表

order by

​ 排序字段列表

limit

​ 分页参数

2.执行顺序

1.from

2.where

3.group by having

4.select

5.group by having

6.limit

4.DCL(数据控制语言)

1.介绍

管理数据库用户、控制数据库的访问权限

2.用户管理

1.查询用户

localhost本机

%任意主机

use mysql;

selct * from user;

用win+R 进入mysql

MySQL -u 用户名 -p

2.创建用户

create user 用户名 @主机名 identified by 密码;

3.修改用户密码

alter user 用户名@主机名 identified with 旧密码 by 新密码;

4.删除用户

drop user 用户名@主机名;

3.权限控制

1.权限描述

*.* (全部表)

数据库名.* 指定数据库的所有表

1.all all privileges (所有权限)

2.select (查询数据)

3.insert (插入数据)

4.update (修改数据)

5.delete (删除数据)

6.alter (修改表)

7.drop(删除数据库)

8.create(创建数据库)

2.查询权限

show grants fro 用户名@主机名;

3.授予权限

grant 权限列表 on 数据库名.表名 to 用户名@主机名;

3.撤销权限

revoke 权限列表 on 数据库名.表名 from 用户名@表名;

1.多个权限之间，用逗号分隔

2.授权时，数据库名和表名可以用*进行通配，代表所有。

5.函数

1.字符串函数

1.concat（字符串拼接）

select concat("Hello","World");

2.lower（转为小写）

select lower("HELLO");

3.upper （转为大写）

select upper("hello");

4.lpad(左填充)

用字符串pad对str进行填充，达到n个字符串长度

select lpad(str,n,pad);

5.rpad(右填充)

用字符串pad对str进行填充，达到n个字符串长度

select rpad(str,n,pad);

6.trim(去掉头部的空格和尾部的空格)

select trim(str);

7.substr(求子串)

从start开始的n个字符

select substr(str,start,len);

2.数值函数

1.ceil （向上取整）

select ceil(2.5); (=3)

2.floor （向下取整）

select floor(2.5); (=2)

3.mod （取模）

select mod(7,4);

4.rand（随机数）

select rand();

5.round（四舍五入，保留y位小数）

select round(2.5);

select round(2.5,0); (=3)

3.日期函数

1.curdate (年月日)

select curdate();

2.curtime (时分秒)

select curtime();

3.now (年月日时分秒)

select now();

4.year (当前年份)

select year(now());

5.month (当前月份)

select month(now());

6.day (当前日期)

select day(now());

7.date_add (增加时间)

interval(间隔)a

70(间隔时间)

day、month、year

select date_add(now(),interval 70 day );

8.datediff(第一个时间减去第二个时间)

select datediff('2025-3-26','2024-3-26');

4.流程函数

1.if(value,t,f)

如果value为true返回t否则返回f

select if(true,9,0); ->9
select if(false,9,0); ->0

2.ifnull(value1,value2)

如果value1不为null则返回value1，否则返回value2

select ifnull('ok','default'); ->ok
select ifnull('','default'); ->''
select  ifnull(null,'default'); ->default```

3.case when [val] the [res]… else[default] end;

如果value为true，返回res1，…否则返回default默认值

select name,(case richu_user.gender when '男' then '0' when '女' then '1' else 'default' end) as '性别编号' from richu_user;

4.case[expr] when [val] then [res1].. res[default] end;

如果expr的值等于val，返回res1，…否则返回default默认值

select name,(case  when date<=200 then '短' when date<= 500 then '中' else '长' end) '时间' from richu_user;

6.约束

1概念

作用于表中字段上的规则，用于限制存储在表中的数据

2.目的

保证数据库中数据的正确，有效性和完整性

3.分类

1.非空约束(not null)

限制该字段数据不能为null

name varchar(10) not null unique comment '姓名'

2.唯一约束(unique)

保证该字段的数据是唯一、不重复的

name varchar(10) not null unique comment '姓名'

3.主键约束(primary key)

主键是一行的标识，要求非空且唯一

id int primary key auto_increment comment '主键'

//auto_increment (自增)

4.默认约束(default)

保存该数据时，如果未指定值则采用默认值

status varchar(10) default '1' comment '状态'
//插入
insert into richu_user2(id,name,age) values (4,'d',40);

5.检查约束(check)

保证字段满足一条件

age int check(age >=0 and age <=100) comment '年龄'

6.外键约束（两张表）(foreing key)

让两张表的数据之间建立联系，保证数据的一致性和完整性

1.创建外键
create table 表名(

 				...

				[constraint] [外键名称] foreign key [外字段名] references 主表 [主表列表]

 );
2.建立外键关联

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主列表名);

3.删除外键

alter table 表名 drop foreign key 外键名称;

4.目的

维护数据的完整性和一致性

5.外键删除更新行为

1.删除/更新行为

  • no action 检查该记录是否存在对应外键，如果有则不允许删除/更新 (与restrict相同)
  • restrict 检查该记录是否存在对应外键，如果有则不允许删除/更新 (与no action相同)
  • cascade 检查该记录是否存在对应外键，如果有，则一起删除/更新
  • set null 检查该记录是否存在对应外键，如果有则子表中该外键的值为null (这要求该外键允许取null)
  • set default 父表有变动时，子表将外键列设置成一个默认的值(Innodb不支持)

2.添加行为

alter table 表名 add constraint 外键名称 foreign (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

update ->更新行为

delete->删除行为

7.多表查询

1.多表关系

  • 多对多

多个表的主键通过一个中间表相关联

  • 一对一

多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段就放在另外一张表中，以提升操作效率

外键与主键一一对应，在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(unique)

2.多表查询概述

1.概述

指在多个表中查询数据

笛卡尔积：两个集合的所有组合情况

2.分类

1.连接查询

内连接：相当于查询A、B交集部分数据

外连接：

​ 1.左外连接：查询左表所有数据，以及两张表交集部分数据

​ 2.右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：

​ 当前表与自身的连接查询，自连接查询，自连接必须使用表别名

2.子查询

3.内连接

1.隐式内连接

select 字段列表 from 表1，表2 where 条件

2.显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件 (inner可以省略)

4.外连接

1.左外连接

select 字段列表 from 表1 left[outer] join 表2 on 条件...

2.右外连接

select 字段列表 from 表1 right[outer] join 表2 on 条件...

5.自连接

select 字段列表 表A 别名 join 表A 别名B on 条件...

自连接可以是内连接也可以是外连接

自连接必须使用别名

6.联合查询-union,union all

select 字段列表 from 表A

union [all]

select 字段列表 from 表B;

select * from richu_user where age <20
union all
select * from richu_user where id <2; ->会有重复
select * from richu_user where age <20
union
select * from richu_user where id <2;->去重

7.子查询

1.概念

sql语句中嵌套select语句，称为嵌套查询，又称子查询

select * from t1 where column1=(select column from t2)

2.分类

  • 根据子查询结果
    - 标量子查询 （结果为单个值）
    - 列子查询 （子查询结果为一列）
    - 行子查询（子查询结果为一行）
    - 表子查询 （子查询结果为多行多列）

  • 根据位置

​ where之后、from之后、select之后

3.标量子查询

常用操作符= <> >= < <=

select 字段列表 from 表=(select 字段列表 from 表 where 条件)

4.列子查询

常用操作符 in、not in、any、some、all

  • in 在指定的集合范围之内，多选一
  • not in 不在指定的集合范围之内
  • any 子查询返回列表中，有任意一个满足即可
  • some 与any等同，使用some的地方都可以使用any
  • all 返回列表的所有值必须满足

select 字段列表 from 表 where 条件> all (select 字段列表 from 表 where 条件)

5.行子查询

常用操作符 =、<>、in、not in

select 字段列表 from 表 where (条件1，条件2) > all (select 字段列表 from 表 where 条件)

6.表子查询

常用操作符 in

用select语句查询的表作为一张新表并且进行连查

8.事务

1.简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功要么同时失败。

2.事务操作

  • 查看/设置事务提交方式

select @@autocommit; (查看当前事务提交方式)

0 -> 手动 1->自动

set @@autocommit =0 ; (设置当前事务提交方式)

select @@autocommit; --注释

set @@autocommit = 0;
select @@autocommit;
  • 开启事务

start transaction 或 begin

  • 提交事务

成功则提交

commit;

  • 回滚事务

失败则回滚

rollback;

3.四大特性（ACID）

1.原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败

2.一致性：事务完成时，必须使所有的数据都保持一致

3.隔离性：数据库系统提供的隔离机制，保证事务在不受到外部并发操作影响的独立环境下运行

4.持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

4.并发事务引发的问题

1.脏读：一个事务读到另外一个事务还没有提交的数据

2.不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

3.幻读：一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时，又发现这行数据已经存在，好像出现了一个幻影

5.事务隔离等级

  • read uncommitted 无法解决脏读、不可重复读、幻读
  • read committed 无法解决不可重复读、幻读，可以解决脏读
  • repeatable read （默认） 无法解决幻读，可以解决脏读、不可重复读
  • serializable 可以解决脏读、不可重复读、幻读

查看事务隔离级别

select @@transaction_isolation;

更改事务隔离级别

set session transaction isolation level [read committed];

安全级别越高，性能越低

9.存储引擎

1.MySQL体系结构

  • 连接层
  • 服务层
  • 引擎层
  • 存储层

pFTrAqf.png

2.存储引擎简介

存储引擎就是存储数据、建立索引

查询建表语句

show create table 表名;

查询当前数据库支持的存储引擎

show engines;

设置创建表的引擎

在后面加上endine=innoDB

3.存储引擎特点

1.innoDB

1.特点
  • DML操作遵循ACID模型，支持事务
  • 行级锁，提高访问性能；
  • 支持外键foreign key 约束，保证数据的完整性和正确性
2.文件

xxx.ibd:xxx代表的是表名，innoDB引擎的每一张表都会有一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

打开sdi文件,先打开cmd

ibd2sdi ibd文件

3.逻辑存储结构
  • tableSpace:表空间
  • segment：段
  • extent：区
  • page：页
  • row：行

2.myisam

1.特点

  • 不支持事务

  • 支持表锁，不支持行锁

  • 访问速度快

2.文件

xxx.sdi 存储表结构信息

xxx.myd 存储数据

xxx.myi 存储索引

3.Memory

1.特点

  • 内存存放
  • hash索引（默认）

2.文件

xxx.sdi:存储表结构信息

innoDB 相对于myisam 支持事务安全、支持锁机制（行级锁）、支持外键

Supports transactions, row-level locking, and foreign keys

4.存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎，对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB引擎

    InnoDB是MySQL中的一种事务性存储引擎，它支持事务、行级锁和外键等特性。InnoDB引擎适合于需要处理大量事务和并发操作的应用场景。

  • MyISAM引擎

MyISAM是MySQL中的一种非事务性存储引擎，它具备较高的插入和查询性能，适用于读操作远多于写操作的应用场景

  • MEMORY引擎

​ MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因 此，基于MEMORY的表的生命周期很短，一般是一次性的。

10.索引

1.索引概述

index

是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向数据），这样就可以在这些数据结构上实现高级查找算法，这些数据结构就是索引。

全表扫描->无索引

二叉搜素树->有索引

优势 劣势
提高数据检索的效率，降低数据库的IO成本 列索引也是要占用空间的
通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗 索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行insert、update、delete时，效率降低

2.索引结构

索引结构 描述
b+Tree索引 最常见的索引类型，大部分引擎都支持b+树
Hash索引 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引) 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引) 是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES
索引 InnoDB MyISAM MEMORY
b+Tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-tree(空间索引) 不支持 支持 不支持
Full-text(全文索引) 5.6版本后支持 支持 不支持

1.b-tree（多路平衡查找树）

以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）

每个结点的key顺序排列，如果大于最后一个key则指向最后一个指针，如果小于第一个key则指向第一个指针，如果在两个key之间则指向两个key之间的那个指针

pFTbQSI.jpg

2.b+tree

以一颗最大度数为4（4阶）的b+tree为例

所有元素都会出现在叶子结点，叶子结点组成了一个单向链表

pFTbGm8.png

Mysql在b+树的基础上增加了一个指向相邻叶子节点的链表指针

3.hash

哈希索引采用一定的hash算法，将键值换成新的hash值，映射到对应的槽位，然后存储在hash表中

特点

  • hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<，…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常只要一次检索

3.索引分类

分类 含义 特点 关键字
主键索引 针对于表中某数据列中的值重复 默认自动创建，只能有一个 primary
唯一索引 避免同一个表中某数据列的值重复 可以有多个 unique
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键字，而不是比较索引中的值 可以有多个 fulltext

1.在InnoDB中

分类 含义 特点
聚焦索引（Clustered Index） 将数据存储于索引放到了一块，索引结构的用叶子结点保存了行数据 必须有，而且只有一个
二级索引（Secondary Index） 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取原则

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

4.索引语法

1.创建索引

create [unique][fulltext] index index_name(索引名称) on table_name(字段名);

2.查看索引

show index from 表名;

3.删除索引

drop index 索引名 on 表名

5.SQL性能分析

1.sql的执行频率

通过show[session|global] status like ‘com_______’命令可以提供服务器状态信息

show global status like 'com_______'(7个_)

2.慢查询日志

慢日志记录了所有执行时间超过指定参数的所有SQL语句的日志。默认是未开启。

开启MySQL慢日志查询开关

slow_query_log=1

设置慢日志的时间为2秒，SQL语句的执行时间超过2秒，就会视为慢查询，记录查询日志

slow_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

systemctl restart mysqld(重新启动MySQl服务器)

show variables like 'slow_query_log'(查看日志)

tail -f localhost-slow.log (访问日志)

3.profile详情

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时

select @@have_profilre;(查看数据库是否支持profile)

select @@profiling; (查看是否打开profile)

set profiling = 1; (打开)

查看每一条SQL的耗时基本情况

show profiles;

查看指定query_id的SQL语句各个阶段的耗时情况

show profile for query query_id

查看指定query_id的SQL语句CPU使用情况

show profile cpu for query query_id

4.explain

explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

1.语法

explain select 字段列表 from 表名 where 条件

2.执行计划
  • id

​ select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。

  • select_type

​ 表示select的类型，最常见的取值有simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等。

  • type

​ 表示连接类型，性能由好到差依次为NULL、system、eq_ref、ref、range、index、all。

  • possible_key

​ 显示可能应用在这张表上的索引，一个或者多个。

  • key

​ 实际使用的索引，如果为NULL，则没有使用索引。

  • key_len

​ 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度，在不损失精确性的前提下，长度越短越好。

  • rows

​ MySQL认为必须要执行查询的行数，在InnoDB引擎的表中是一个估计值，可能并不是准确的。

  • filtered

​ 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

6.索引使用规则

1.最左前缀法则

mysql 会从左向右，匹配直到遇到范围查询（>,<,between,like）就停止匹配(之后的不会用到索引)

比如建立（a,b,c,d）顺序的索引

使用这样的查询语句 a = 1 and b=2 and c>3 and d =4 ; d是用不到索引的

但如果建立（a,b,d,c）的索引则都可以用到 ， a,b,d 的顺序可以调整

2.索引失效情况1

  • 1.要遵守最左前缀法则(最左边的字段必须存在,跳过某一字段后面字段索引失效).

  • 2.索引字段不能使用函数否则失效.(不要使用运算操作)

  • 3.索引字段,字符串字段使用时,字符串不加引号,导致索引失效.

  • 4.索引字段进行模糊查询时,如果是头部进行模糊查询,则索引失效,若果是尾部进行模糊查询,索引可正常使用.

3.索引失效情况2

  • 1.索引字段使用or时,or的两边都必须有存在索引的字段,否则索引失效.

  • 2.当查询时需要数据大于不需要数据时,索引失效,会全表扫描,当查询时需要数据小于不需要数据时,索引正常使用

4.索引使用

1.sql提示

是优化数据库的一个重要手段，即在数据库中加入一些人为的提示来优化操作

use index:

use表示建议使用某个索引(语法:select * from 表名 use index(索引名称) where .......)

ignore index:

ignore表示建议不使用某个索引(语法:select * from 表名 ignore index(索引名称) where .......)

force index:

force表示强制使用某个索引(语法:select * from 表名 force index(索引名称) where .......)

2.覆盖索引

查询语句中尽量少使用” select * “,全表查询很容易造成回表查询 (即:查询时当查询字段不存在已经使用过的索引时,会将查询到的二级索引上挂载的主键重新传回表中到聚集索引中利用主键ID再次进行查询查到整行数据 (二级索引中挂载的是当前数据的id,聚集索引挂载的是当前整行数据) )

  • using where using index 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
  • using index condition 查找使用了索引，但是需要回表查询
3.前缀索引

查询语句时如果当前字段储存的数据太长建立索引耗费空间太大,可以利用前缀索引进行对数据的前几个字符进行截取.从而利用少量空间来建立索引,加速查询.(较长字符串、文本时使用)

create index idx_xxx on table_name(column(n));

  • n代表的是使用前n个字符建立索引

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct richu) / count(*) from richu_user

select count(distinct substring(richu,1,10)) / count(*) from richu_user

substring(截取目标，开始位置，长度)

4.联合索引和单列索引的选择

当存在多个查询字段时最好使用联合索引,单列索引很容易造成回表查询.

多个查询的字段都有单列索引，此时查找会造成回表查询，此时最好创建联合索引

（尽量使用联合索引）

5.索引设计原则

  • 1.针对数据量较大,数据查询较为频繁的表建立索引

  • 2.针对常用于查询条件,分组,排序操作的字段(where,group by,order by)建立索引.

  • 3.尽量针对区分度高的字段创建索引,比如(学生表中的学生id,身份证等等),区分度越高,索引查询效率也就越高

  • 4.如果字符串类型的字段,字符串较长可以建立前缀索引,截取前几个字段来建立索引提高查询效率,这里要注意的是选择性(表中截取前面的字段数据总数(去重)/表中不截取的字段数据总数,这个值越接近于1越好)

  • 5.尽量使用联合索引,少使用单一索引,这样可以减少回表查询,而且还能节省空间,提高效率.

  • 6.要控制索引数量,不是越多越好,索引越多,维护索引代价也就越大,索引过多会影响增删改的效率.

  • 7.如果需要建立索引的字段不能为空值,那么建立字段时就给他设置 NOT NULL ,这样优化器知道那一列不能为空时,会更好的选择索引优化

11.SQL优化

1.插入数据

inset优化

批量插入

  • 大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时使用load函数进行插入

#客户端连接服务端时，加上参数 –local-infile

mysql –local-infile -u -root -p;

#设置全局参数local_infile为1

set global local_infile=1;

#执行load指令将准备好的数据，加载到表结构中

load data local infile ‘linux路径’ into table ‘表名’ fields terminated by ‘,’ lines terminated by ‘\n’;

2.主键优化

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）

主键顺序插入性能优于乱序插入

主键顺序插入(插入效率高)

主键乱序插入(插入效率低)

插入效率低的原因

  • 页分裂

    • 页可以为空，页可以填充一半，也可以填充100%，每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列

  • 页合并

    • 当删除一行记录时，实际上记录没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用

      当页中删除的记录达到MEREG-THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否能将两个页合并以优化空间使用

附录（本文出现的英语单词及其翻译）

1.alter v.改变、变动

2.truncate v.截短、去尾

3.medium adj.中等的、适中的

4.grant v.允许、同意，准予

5.revoke v.撤回、取消；废除

6.pad v.填塞

7.constraint n.限制、拘束、强迫

8.duplicate v.复制、重复

9.transaction n.交易、办理、事务、学报

10.profile n.简介、概述

11.query v.询问

12.profile n.简介，概述

13.terminate v.使结束，终止