原文链接: https://www.nowcoder.com/discuss/150059?type=0&order=0&pos=8&page=1
概述
为什么要优化
- 系统的吞吐量瓶颈往往出现在数据库的访问速度上
- 随着应用程序的运行，数据库的数据会越来越多，处理时间会相应变慢
- 数据是存放在磁盘上的，读写速度无法和内存相比
如何优化
- 设计数据库时: 数据库表、字段设计、存储引擎
- 利用好MySQL自身提供的功能，如索引等
- 横向扩展：MySQL集群、负载均衡、读写分离等
- SQL语句优化(收效甚微)
字段设计
字段类型的选择,设计规范，范式，常见设计案例
原则1：尽量使用整型字符串
// 存储IP
INET_ATOP(str) -----> address to number
INET_NTOA(number) ------> number to address
// 具体可以参考 https://blog.csdn.net/doublefay/article/details/89065743
// ip地址使用varbinary(4)存储;java 接收使用byte[],接收完成后使用new String(byte[]xx)获取为String
// 参考：https://www.cnblogs.com/skynet/archive/2011/01/09/1931044.html
// MySQL内部的枚举类型(单选)和集合（多选)类型
原则2: 定长和非定长数据类型选择
decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text
// 金额--->对数据的精度要求高,小数的运算和存储存在精度问题
// 字符串存储---->定长char,非定长varchar、text(上限65535，其中varchar还会消耗1-3字节记录长度，而text使用额外空间记录长度)
原则3: 尽可能选择小的数据类型和指定短的长度
原则4：尽可能使用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 ‘’
原则5: 字段注释要完整，见名知意
原则6：单表字段不宜过多
原则7：可以预留字段
使用以上原则之前首先要满足业务需求
范式(Normal Format)
第一范式
- 数据库表中的字段保证都是原子性，不可再分的。
第二范式
- 确保表中的每个字段都与主键相关，而不是部分相关，主键与非主键是成完全依赖关系的。
第三范式
- 在2NF的基础上,任何非主属性不依赖与其他非主属性(在2NF的基础上消除传递依赖)
存储引擎选择
- InnoDB和MyISAM
| 类型 | MyISAM | InnoDB |
|---|---|---|
| 文件格式 | 数据和索引分别存储,数据.myd,索引.myi | 数据和索引是集中存储的.ibd |
| 文件能否移动 | 能,一张表对应.frm、.myd、.myi三个文件 | 否,因为关联的还有data下的其他文件 |
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 空间碎片 | 产生。定时整理:使用命令optimize table表名实现 | 不产生 |
| 事物 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 锁支持 | 表级锁 | 行级锁、表级锁,锁定力度小并发能力高 |
选择依据
如果没有特别的需求，使用默认的Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。
MySQL锁
锁种类
常见的7种锁
- 行锁(Record Locks):单个记录上的锁
- 间隙锁(Gap Locks): 锁定一个范围,但不包括记录本身
- 临键锁(Next-Key Lock): 锁定记录本身,并且锁定记录本身
- (读)共享锁/(写)排他锁: 属于行级锁，悲观锁
- 意向共享和意向排他锁: 属于表级锁,悲观锁
划分锁
- 按照对数据操作的锁粒度来分:(锁定粒度依次递增)
- 行级锁
- 间隙锁
- 临键锁
- 页级锁
- 表级锁
- 按照锁的共享策略来分：
- 共享锁
- 排他锁
- 意向共享锁
- 意向排他锁
- 从加锁策略分:
- 乐观锁
- 悲观锁
- 其他
- 自增锁
解释
行级锁介绍
行级锁（记录锁）是MySQL中锁定粒度最细的一种锁。表示单个行记录上的锁，行锁一定是作用在索引上的。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。
特点: 开销大,加锁慢,会产生死锁。发生锁冲突的概率最低，并发度也最高。
间隙锁
间隙锁，锁定一个范围，但不包括记录本身（它的锁粒度比记录锁的锁整行更大一些，他是锁住了某个范围内的多个行，包括根本不存在的数据），隙锁一定是开区间，比如（3，5）。
GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR(可重复读)或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。
临键锁
它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁，临键锁是是一个左开右闭的区间，比如（3，5]。
next-key lock的效果相当于一个记录锁加一个间隙锁。当next-key lock加在某索引上，则该记录和它前面的区间都被锁定。假设有记录1, 3, 5, 7，现在记录5上加next-key lock，则会锁定区间(3, 5]，任何试图插入到这个区间的记录都会阻塞。
record lock、gap lock、next-key lock，都是加在索引上的。假设有记录1,3,5,7，则5上的记录锁会锁住5，5上的gap lock会锁住(3,5)，5上的next-key lock会锁住(3,5]。
注意，next-Key锁规定是左开右闭区间!
表级锁
表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MYISAM与InnoDB都支持表级锁定。
特点: 开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。
页级锁
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB引擎默认 支持页级锁。
特点: 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
意向共享和意向排他锁
IS锁：意向共享锁Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
IX锁：意向排他锁Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。
悲观锁
悲观锁 认为对于同一个数据的并发操作，一定是会发生修改的（增删改多，查少），哪怕没有修改，也会认为修改。因此对于同一个数据的并发操作，悲观锁采取加锁的形式。悲观的认为，不加锁的并发操作一定会出问题。
悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务。
乐观锁
乐观锁 则认为对于同一个数据的并发操作，是不会发生修改的（增删改少，查多）。在更新数据的时候，会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用，直接取申请操作，如果没有产生冲突，那就操作成功，如果产生冲突，有其他线程已经在使用了，那么就不断地轮询。乐观的认为，不加锁的并发操作是没有事情的。就是通过记录一个数据历史记录的多个版本，如果修改完之后发现有冲突再将版本返回到没修改的样子，乐观锁就是不加锁。好处就是减少上下文切换，坏处是浪费CPU时间。
乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。
乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段（版本号）来实现。乐观锁可以用来避免更新丢失。接下来我们看一下乐观锁在数据表和缓存中的实现。
- 乐观锁数据表中的实现
利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。
自增锁
在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有 AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级 锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉， 并不需要等到整个插入语句执行完才释放锁。
索引
索引类型
普通索引，唯一索引， 主键索引 ， 全文索引
语法
-- 创建索引
-- 更改表结构
alter table user_index
-- 创建一个first_name和last_name的复合索引，并命名为name
add key name (first_name,last_name),
-- 创建一个id_card的唯一索引，默认以字段名作为索引名
add UNIQUE KEY (id_card),
-- 鸡肋，全文索引不支持中文
add FULLTEXT KEY (information);
-- 删除索引
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
执行计划explain
# 创建一个用户表：
create table user(
id int primary key auto_increment,
name varchar(32) not null default '',
age tinyint unsigned not null default 0,
email varchar(32) not null default '',
classid int not null default 1
)engine InnoDB charset utf8;
insert into user values(null,'xiaogang',12,'gang@sohu.com',4),
(null,'xiaohong',13,'hong@sohu.com',2),
(null,'xiaolong',31,'long@sohu.com',2),
(null,'xiaofeng',22,'feng@sohu.com',3),
(null,'xiaogui',42,'gui@sohu.com',3);
# 创建一个班级表：
create table class(
id int not null default 0,
classname varchar(32) not null default ''
)engine InnoDB charset utf8;
insert into class values(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios');
- 语法分析
返回结果分析：
在name列上建立索引: alter table user add index(name);
使用场景
- 见原网页
索引失效
- 使用覆盖索引(即查询所有值,把索引覆盖掉了),减少使用select *
- like以通配符开头索引会失效
- or，两边条件都有索引可用
那些列可以建立索引
- 经常需要搜索的列,建立索引,增加搜索速度
- 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构
- 在经常用在连接的列上,加快连接速度
- 经常需要根据范围进行搜索的列上建立索引,因为索引已经排序,所以指定的范围是连续的.
- 经常需要排列的列上建立索引,因为索引已经排序,所以其指定的范围是连续的
- 经常需要使用where字句的列上建立索引,加快条件的判断速度
索引的存储结构
B+树
分区依据字段必须是主键的一部分
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
