MySQL优化细节

MySQL优化官网参考

如何优化

  • 设计数据库时：数据库表、字段的设计，存储引擎
  • 利用好MySQL自身提供的功能，如索引等
  • 横向扩展：MySQL集群、负载均衡、读写分离
  • SQL语句的优化（收效甚微）

字段设计

字段类型的选择，设计规范，范式，常见设计案例

存储IP地址

常规做法： varchar（15）

优化选择：int unsigned，占用4个字节，节省空间，IP运算速度块

inet_aton；

inet_ntoa;

a—address,n----number

尽量使用整数去表示字符串

整型的优势：存储空间固定。往往是少量空间,运算速度块

MySQL 内部的枚举（单选）类型和集合（多选）类型但是，实操的时候enum和set很少用

原因：维护成本高

使用的替代方案：关联表

存储金额

金额，价格，统计数据的存储：对数据的精度要求高,

优化选择：

  1. price decimal（8，2）；定点数，有两位小数的定点数。

  2. price int ，bigint，整数，小单位-->大数额，如9.01 在数据库中存储2为901，在程序中进行相关处理

    注意：定点数，支持很大的数，

    转成小单位后，整数存储不了！

定长数据类型和变长数据类型的选择

定点数和浮点数的选择

定点数：不会精度丢失。 占用空间随者数字的增加而增加

浮点数：导致精度丢失 。占用固定的存储空间，无论存储多大的数据，占用的空间是固定的（类似于int）

定长类型：存储空间固定：int，float,double.char,date,time,datetime,year,timestamp

变长类型：存储空间可变：varchar,decimal,text

字符串类型的选择

使用varchar

char(10) 如果你存abc，需要空间为10，3个存abc，7个存空字符，因为char是固定长度

varchar(10)如果你存abc，需要空间为4，3个存abc，1个存长度，varchar是可变长度

超过10的部分，都会被截断

text,类型独立存储，不占用字段总空间

尽可能选择小的数据类型和指定短的长度

尽可能使用 not null

非null字段的处理要比null字段的处理高效些！且不需要判断是否为null。

null在MySQL中，不好处理，存储需要额外空间，运算也需要特殊的运算符。如select null = null和select null <> null（<>为不等号）有着同样的结果，只能通过is null和is not null来判断字段是否为null。

MySQL中每条记录都需要额外的存储空间，表示每个字段是否为null。因此通常使用特殊的数据进行占位，比如int not null default 0、string not null default ‘’

字段注释要完整，见名知意

单表字段不宜过多

当单表字段字段过多，可以考虑拆成多张表，将小长度的字段放在一张表，其余的放在其他表中

可以预留字段

优先满足业务，可以预留一些字段

关联表的设计

外键foreign key只能实现一对一或一对多的映射

一对多

使用外键

多对多

使用中间表存储两表之间的关联关系

一对一

如商品的基本信息（item）和商品的详细信息（item_intro），通常使用相同的主键或者增加一个外键字段（item_id）

索引

关键字与数据的映射关系称为索引（包含关键字和对应的记录在磁盘中的地址）。关键字是从数据当中提取的用于标识、检索数据的特定内容。

索引检索为什么快

  1. 关键字相对于数据本身，数据量小

  2. 关键字是有序的，二分查找可快速确定位置

    图书馆为每本书都加了索引号（类别-楼层-书架）、字典为词语解释按字母顺序编写目录等都用到了索引。

MySQL中索引类型

  1. 普通索引（key）
  2. 唯一索引（unique key）
  3. 主键索引（primary key）
  4. 全文索引（fulltext key）

索引的关键字有不同的限制：

  • 普通索引：对关键字没有限制
  • 唯一索引：要求记录提供的关键字不能重复
  • 主键索引：要求关键字唯一且不为null

索引管理语法

查看索引

--查看建表语句
show create table 表名;

show CREATE table `user`;
--查看表中字段
desc 表名;
desc user;
--查看表中所有索引
SHOW index FROM `表名`
SHOW keys FROM `表名`;
SHOW index FROM `user_index`;
SHOW keys FROM `user_index`;

image-20210312094403347

image-20210312094416711

创建索引

创建表之后建立索引
create TABLE user_index(
	id int auto_increment primary key,
	first_name varchar(16),
	last_name VARCHAR(16),
	id_card VARCHAR(18),
	information text
);

-- 创建一个first_name和last_name的复合索引，并命名为name
alter table user_index add key name (first_name,last_name),
-- 创建一个id_card的唯一索引，默认以字段名作为索引名
alter table user_index add UNIQUE KEY (id_card),
-- 鸡肋，全文索引不支持中文
alter table user_index add FULLTEXT KEY (information);
创建表时指定索引
CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

删除索引

删除普通索引、唯一索引、全文索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

-----删除普通索引、唯一索引、全文索引
alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
删除主键索引

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

---删除主键索引
alter table 表名 drop primary key
alter table user_index drop primary key

image-20210312100733734

需要取消自增长：

--取消主键自增
ALTER TABLE `表名` MODIFY COLUMN 字段名 字段类型（长度） NOT NULL FIRST ;
ALTER TABLE `user_index` MODIFY COLUMN id int(10) NOT NULL FIRST ;
---删除主键索引
alter table 表名 drop primary key
alter table user_index drop primary key

索引失效

  1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

    image-20210312104045534

  2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

    image-20210312104107314

  3. 组合索引，不是使用第一列索引，索引失效。

    image-20210312104314829

  4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

    image-20210312104329349

  5. 在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效，可能在某些情况会失效

    image-20210312104455798

    此处我将重新创建一个emp表（此处索引为InnoDB类型索引）

    image-20210312104819712

    image-20210312104905292

    ----创建新索引
create index test2 on emp(name)

    image-20210312105105179

    image-20210312105210686

  6. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

  7. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

  8. 不要在字段中做运算

    ----下面两条SQL语句在语义上相同，(id为主键)但是第一条会使用主键索引而第二条不会。
select * from user where id = 20-1;
select * from user where id+1 = 20;

索引使用场景（重点）

where

根据id查询记录，因为id字段仅建立了主键索引，因此此SQL执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据。

-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';

image-20210312103826193

可以尝试在一个字段未建立索引时，根据该字段查询的效率，然后对该字段建立索引（alter table 表名 add index(字段名)），同样的SQL执行的效率，你会发现查询效率会有明显的提升（数据量越大越明显）。

order by

  • 该字段没有建立索引

    将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果）

  • 该字段建立索引

    直接按照索引的顺序和映射关系逐条取出数据。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

posted @ 2021-06-08 15:33  知白守黑，和光同尘  阅读(67)  评论(0)  编辑  收藏  举报