mysql外键名定义_MySQL索引&存储引擎，从基础到性能测试和调优

学习MySQL的索引和存储引擎，验证索引的性能，和数据库调优的基本原则

在上一篇

《Linux学习笔记，系统学习倒逼输出，曾经的碎片化是否辜负了所受的苦难》--- Linux命令的格式，常见的基本命令，tree、tar、find、chmod，文件的权限，输出重定向等

提及，我们在学习中，要避开惰性知识，学习核心知识，学习要构建自己完整的知识体系，让知识能够切实指导我们的工作和生活

在之前的实践文章，积攒了一些python操作数据库的项目实践，可以进入公众号的【测试实践】菜单查看，如下图所示：

本篇内容：

• MySQL的索引

• 验证索引的性能

• MySQL的存储引擎

• MySQL调优的基本原则

01

—

MySQL的索引

索引是对数据库中表的一列或者多列的值进行排序的一种结构

在mysql中索引用Btree方式

索引的优点：

• 加快数据的检索速度

索引的缺点：

• 当对表中的数据进行增加、修改、删除时，索引需要动态维护，降低了数据的维护速度

• 索引需要占用物理存储空间

索引的分类：

• 普通索引

• 唯一索引

• 主键索引

• 外键索引

1

 普通索引

使用规则：

• 一个表中可以有多个index字段

• 字段的值可以有重复，也可以为null值

• 经常把做查询条件的字段设置为index字段，如下一节内容中，性能测试的name字段

• index字段的key标志为：MUL

创建表时创建索引

create table t(

id int,

name varchar(15),    

index(id),

index(name)

);

在已有的表中添加索引字段

语法格式：create index 索引名 on 表名(字段名);

(索引名的取名一般和字段名一样)

查看普通索引：

• desc 表名;  查看key标志为MUL

• show index from 表名;

show index from 表名\G; (下图是加上 \G 展示效果)

删除普通索引

drop index 索引名 on 表名;

(注：删除普通索引要一个一个删除)

2

 唯一索引

使用规则：

• 一个表中可以有多个unique字段

• unique字段的值不允许重复，可以为空值null

• unique的key标志是：UNI

创建唯一索引，等同普通索引index的创建

unique(字段名),

unique(字段名)...

在已有表中创建唯一索引

create unique index 索引名 on 表名(字段名);

查看、删除唯一索引(删除只能一个一个删)

desc 表名;

show index from 表名;

drop index 索引名 on 表名;

3

 主键索引

使用规则：

• 一个表中只能有一个主键字段(primary key)

• 对应字段的值不允许重复，且不能为null

• 主键字段的key标志为：PRI

• 把表中能够唯一标识一条记录的字段设置为主键，通常把表中记录编号的字段设置为主键

主键有什么约束：字段值不允许重复，不允许为空

主键约束 = 唯一约束 + not null

创建表时，创建主键-PRI

方式1：字段名 数据类型 primary key auto_increment,

方式2：

create table t(

id int auto_increment,

....

primary key(id))auto_increment=10000;

auto_increment设置自增长起始值

从10开始，下一个是11

删除主键

1、先删除自增长属性(modify)

      alter table 表名 modify id int not null;

2、删除主键

      alter table 表名 drop primary key;

 

4

 外键索引

定义：让当前表字段的值，在另一个表的范围内去选择

语法：

foreign key(参考字段名)

references被参考表名(被参考字段名)

on delete级联动作

on update级联动作

级联动作：

  1)cascade，数据级联更新

  当主表删除记录，或者，更新被参考字段的值时，从表会级联更新

  2)restrict，默认的方式

当删除主表记录时，如果从表中有相关联记录，则不允许主表删除

更新同理

3)set null

当删除主表记录时，从表中相关联记录的参考字段值字段设置为null

更新同理

4)no action

on delete no action on update no action

同 restrict，都是立即检查外键限制

create table ref_stu(

id int primary key auto_increment,

name varchar(15)

);

create table ref_score(

id int,

score float(5,2) unsigned,

foreign key(id) references ref_stu(id)

);

删除外键：

alter table表名drop foreign key外键名;

删除外键前要查看一下外键名

外键名!=字段名

外键的查看方式：show create table表名;

在已有表中添加外键(会受到表中原有数据的限制)

alter table表名 add foreign key(参考字段名)

references被参考表名(被参考字段名)

on delete级联动作

on update级联动作;

外键的使用规则

• 两张表的被参考字段和参考字段，数据类型要一致

• 被参考字段必须是key的一种，通常是primary key

02

—

验证索引的性能

本节通过加索引，验证索引的性能

准备表和数据：

create table index_pro(

id int,

name varchar(15)

)

用Python脚本插入数据

查看profiling的值：show variables like "%pro%";

默认的profiling是off

执行：set profiling =1; 把profiling设置为on

执行：show profiles; 可以查看各个语句的执行时长

未加索引，执行：

查看执行速度是0.35s

添加索引：create index name on index_pro(name);

再次执行，查看执行速度是0s

执行：show profiles; 查看性能情况

可以看到，未加索引时，查询时长是0.35459100，添加普通索引后，查询时长是：0.00054300，效率提升了99.8%

验证索引性能的步骤：

• 执行查询语句

• 查看性能分析结果：show profiles;

• 在name字段创建索引：

  create index name on index_pro(name);

• 再次执行查询语句

• 查看性能分析结果，进行对比

• 关闭性能分析：set profiling = 0

03

—

MySQL的存储引擎

存储引擎是，用来处理表的处理器

查看已有表的存储引擎

show create table bank1;

创建表时指定存储引擎

create table eng_db(

id int

)engine=myisam;

查看所有的存储引擎

show engines;

工作中常用的存储引擎：InnoDB、MyISAM

InnoDB的特点：

1)共享表空间

    表名.frm  表结构

    表名.idb  表记录&索引信息

2)支持行级锁

MyISAM的特点：

1)独享表空间

    表名.sdi  表结构  有的是.frm后缀

    表名.myd  表记录

表名.myi  索引信息

2)支持表级锁

查看库的路径，可用命令：

show variables like '%dir%';

锁的目的：解决客户端并发访问的冲突问题

锁类型：

• 读锁(select)也称共享锁：加读锁之后，不能更改表中的内容，但可以查询

• 写锁(insert update delete)也叫互斥锁，排他锁

锁粒度(操作完成后，自动释放锁)

• 表级锁

• 行级锁

执行查询操作多的表，使用myisam存储引擎，因为innodb是行级锁，若使用innodb会浪费资源

执行写操作多的表，使用innodb存储引擎

04

—

MySQL调优的基本原则

1、选择合适的存储引擎(经常用来读的表使用myisam存储引擎，修改操作多的表用innodb存储引擎)

2、sql语句的调优(原则：尽量避免全表扫描)：

1)在select、where、order by 常涉及到的字段上建立索引

2)where子句中不使用 !=，否则将放弃使用索引，而进行全表扫描

3)表里的数据尽量不要插入null，如：select id from t1 where number is null 可优化为：number字段的值设置为0

4)尽量避免用 or 来连接条件，这将导致全表扫描

      如：select id from t1 where id=10 or id=20;

      优化为：

      select id from t1 where id=10

      union all

      select id from t1 where id=20;

5)模糊查询尽量避免使用第1个字符是%，这也将导致全表扫描

     如：select id from t1 where name like “%a” 若知道首字母是A，可改 为：A%a

6)尽量避免in和not in，这也将导致全表扫描

      如：select id from t1 where id in(1,2,3);

      可优化为：

      select id from t1 where id between 1 and 3;

7)尽量避免使用 select * ...，要用具体的字段列表代替*，不要返回用不到的字段