MySQL 学习笔记
2021-06-11 16:04:55 71 举报AI智能生成
登录查看完整内容
MySQL学习笔记主要记录了对MySQL数据库的基本操作、查询语句、数据类型、表结构设计等方面的理解和实践。通过学习，掌握了如何使用CREATE、DROP、ALTER等命令创建、删除和修改数据库和表；掌握了SELECT语句的基本语法和高级查询技巧，如连接查询、子查询、聚合函数等；了解了数据类型的概念和使用，如整数型、浮点型、字符型等；学会了如何设计合理的表结构，包括主键、外键、索引等。此外，还学习了事务处理、存储过程和触发器等高级功能。通过不断实践和总结，逐步提高了自己的MySQL技能水平。
作者其他创作
大纲/内容
MySQL学习笔记
事务
概念：事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成
哪些地方用到事务
DML
INSERT DELETE UPDATE
DDL
CREATE DROP
DCL
GRANT REVOKE
支持事务的存储引擎
InnoDB
NDB
事务的四大特性
原子性（Atomicity)
数据库的一系列操作要么成功，要么失败，不可能出现部分成功，部分失败。
Undo log 实现
一致性（Consistent)
数据库自身的完整性约束
用户自定义的完整性
隔离性（Isolation）
就是很多个事务，对表或者行的并发操作，是透明的，互相不干扰的。
持久性
对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久的
持久性怎么实现呢？数据库崩溃恢复（crash-safe）是通过什么实现的？
持久性是通过 redo log 和 double write 双写缓冲来实现的，
我们操作数据的时候，会先写到内存的 buffer pool 里面，同时记录 redo log，如果在刷盘之前出现异常，在重启后就可以读取 redo log 的内容，写入到磁盘，保证数据的持久性。当然，恢复成功的前提是数据页本身没有被破坏，是完整的，这个通过双写缓冲（double write）保证
数据库什么时候会出现事务？
Nacvicat 等客户端工具
Java 等代码 JDBC 或者 @Transactional
最终都是发送一个指令到数据库去执行，Java 的JDBC只不过是把这些命令封装起来了
事务并发带来什么问题？
数据库的读一致性问题
具体提现：
脏读
两个事务，一个事务首先查询 ，返回一个结果，第二个事务更新这条记录，但是没提交，第一个事务再次查询可以查看更改后的值。
幻读
两个事务，第一个事务执行范围查询，这个时候只有一条数据满足条件，第二个事务，往这个条件插入一行数据并且提交了。此时第一个事务再次去查询，他发现多了一条数据。
不可重复读
两个事务，第一个事务执行查询，返回一个结果，第二个事务，对这条数据进行Update操作，并且提交了。此时第一个事务再次去查询，他发现结果值变了。
如何解决事务并发的问题？
SQL92标准
未提交读
没用解决任何问题
已提交读
解决脏读
可重复读
解决不可重复读
解决不可重复和幻读（InnoDB）
串行化
解决所有问题
解决方案
LBCC
基于锁的并发控制
适合场景：多读写少
MVCC
概念：在修改诗句的时候给它建立一个备份和快照，后面再来的读取读这个快照就可以了
核心思想
我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。
实现方式
InnoDB 为每行记录都实现了两个隐藏字段：DB_TRX_ID 和 DB_ROLL_PTR
DB_TRX_ID，6 字节：插入或更新行的最后一个事务的事务 ID，事务编号是自动递增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事 务 ID）。
DB_ROLL_PTR，7 字节：回滚指针（我们把它理解为删除版本号，数据被删除或记录为旧数据的时候，记录当前事务 ID）。
查找规则
只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）
隔离级别的实现
Read Uncommited（未提交读）
没做处理
Serializable（串行化）
所有的 select 语句都会被隐式的转化为 select ... in share mode，会和 update、delete 互斥。
Repeatable Read（可重复读）
普通的 select 使用快照读(snapshot read)，底层使用 MVCC 来实现。
Read Commited（已提交读）
RC 隔离级别下，普通的 select 都是快照读，使用 MVCC 实现。
加锁的 select 都使用记录锁，因为没有 Gap Lock。
除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁封锁区间。
所以 RC 会出现幻读的问题。
事务隔离级别怎么选？
RU 和 Serializable 肯定不能用。
RC 和 RR 主要有几个区别：
RR 的间隙锁会导致锁定范围的扩大。
条件列未使用到索引，RR 锁表，RC 锁行。
RC 的“半一致性”（semi-consistent）读可以增加 update 操作的并发性。
锁
锁锁定的是什么？
锁住的是索引
锁的粒度
行级别的锁
表级别的锁
行锁
共享锁（shard Locks）
概念： 获取一行数据的读锁以后，可以用来读取数据，所以它也叫读锁。注意：不要再加上读锁以后去写数据，不然的话可能会出现死锁的情况
加锁方式
select .... lock in shard mode;
释放方式
只要事务结束，锁就会自动释放，包括提交事务和结束事务
排他锁（Exclusive Locks）
概念：它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排他锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。
自动加锁
手动加锁
select * from table for update
表锁
意向锁
如果一张表上面至少有一个意向共享锁，说明有其他的事务给其他的某些数据行加上共享锁
如果一张表上面至少有一个意向排它锁，说明有其他的事务给其中某些数据加上了排它锁
意向共享锁
当我们给一行数据加上共享锁之前，数据库会自动在这张表上加上一个意向共享锁
意向排它锁
当我们给一行数据加上排它锁之前，数据库会自动在这张表上面加一个意向排它锁
锁的算法
记录锁
概念：当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁
使用不同的key 去加锁，不会冲突，它只会所锁住这个record
间隙锁（gap lock）【解决幻读问题】
当查询的记录不存在的时候，使用间隙锁
注意：间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突
临键锁（默认的行锁算法）
概念：当我们使用了范围查询，不仅仅命中了Record 记录，还包含了Gap 间隙，在这个情况下我们使用的就是临键锁
锁的退化
唯一性索引，等值查询匹配到一条记录的时候，退化成记录表
没有匹配到任何记录的时候，退化成间隙锁
死锁
概念：一个事务或者说一个线程持有锁的时候，会阻塞其他的线程获取锁，这个时候会造成阻塞等待，如果循环等待，就会有可能造成死锁。
锁的释放和阻塞
什么时候释放
事务结束（commit | rollback）;
客户端连接断开
阻塞多久
如果一个事务一直未释放锁，其他事务会被阻塞。 mysql 默认50s
死锁的发生和检测
条件
互斥锁
同一时刻只能一个事务持有这把锁
其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强制剥夺
当多个事务形成等待环路的时候，即发生死锁
日志
show status like 'innodb_rowPlock%'
select * from information_schema.INNODB_TRX;
当前运行的事务，还有具体的语句
select * from information_schema.INNODB_LOCKS; -- 当前出现的锁
select * from information_schema.INNODB_LOCK_WAITS; -- 锁等待的对应关系
如何杀死死锁
kill 4，kill 7，kill 8
以 kill 事务对应的线程 ID，也就是INNODB_TRX 表中的trx_mysql_thread_id
如何避免死锁
在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）；
批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）；
申请足够级别的锁，如果要操作数据，就申请排它锁；
尽量使用索引访问数据，避免没有 where 条件的操作，避免锁表
如果可以，大事务化成小事务；
使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的响。
性能优化
连接（配置优化）
引入连接池
Hikari 默认最大连接池 10
Druid 默认最大连接池 8
连接池最大公式
机器核心数*2+1
缓存（架构优化）
redis
主从复制
读写分离
分库分表
垂直分表
水平分表
优化器（SQL语句分析和优化）
首先要知道哪些SQL慢，慢在哪里？
慢日志分析
打开慢日志分析
永久设置：修改配置文件 my.cnf
slow_query_log = ON long_query_time=2 slow_query_log_file=/var/lib/mysql/localhost-slow.log
临时设置
set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效set @@global.long_query_time=3; -- mysql 默认的慢查询时间是10秒
慢sql 日志分析
show global status like 'slow_queries'; --查看有多少慢查询
show variable like '%slow_query%'; -- 获取慢日志目录
show variables like '%long_query%'; -- 查看设置的慢查询时间（秒）
mysqldumpslow 分析
例如：查询用时最多的20条慢SQL
mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log
count 代表这个SQL执行了多少次
Time 代表执行的时间，括号里面是累计时间
Lock 表示锁定的时间，括号里面是累计时间
Rows 表示返回的记录数，括号里面是累计时间
show profile
查看SQL语句执行的时候使用的资源情况。例如CPU\\IO
查看是否开启
select @@profiling
set @@profiling =1
查看profile 统计
show profiles;
查看所有
shou profile;
最后一条
shou profile to query 1;
可以根据ID 查看执行详细信息
其他命令
显示用户运行线程
show processlist;
查表
select * from information_schema.processlist;
服务器运行状态
show status;
查看select 次数
show global status like 'com_select';
存储引擎运行信息
show engine;
show engine innodb status;
把监控信息输出到错误信息error log 中（15秒一次）
show variables like 'innodb_status_output%';
set global innodb_status_output=ON;
set global innodb_status_output_locks=ON;
然后根据EXPLAIN 执行计划，开始优化
id（查询顺序）
值相同
表的查询顺序是从上往下顺序执行
值不同
先查询id值大的（先大后小）
既有相同也有不同
ID不同的先大后小，ID相同的从上往下
select type(查询类型)
SIMPLE
简单查询，不包含子查询，不包含关联查询union
PRIMARY
子查询SQL语句中的主查询，也就是最外层那层查询
SUBQUERY
子查询中所有的内层查询都是SUBQUERY类型
DERVIED
衍生查询，表示在得到最终结果之前会用到临时表
UNION
用到了UNION 查询
UNION RESULT
主要是显示那些表之间存在UNION 查询
type (连接类型)
常用连接类型优先级：system > const > eq_ref_> ref > range > index > all
不要优化的类型
cost
system
er_ref
通常出现在多表join 查询，表示对于浅表的每一个结果，都只有一行结果。
一般是唯一性索引查询（UNIQUE 或 PRIMARY）
需要优化的类型
ref(可以不用优化)
查询用到了非唯一性索引，或者关联操作只使用到了索引的最左前缀。
range(最低这个标准)
索引范围扫描
index
Explain select tid from teacher;
all
Null
不用访问表或者索引就能得到结果
Explain select 1 from dual where 1=1;
possible_key(可能用到的索引)
key（使用的索引）
possible_key为空，key 可能有值吗？
是有可能的（这里是覆盖索引的情况）
key_len(索引的长度)
跟索引字段的类型、长度有关
rows(扫描的行数)
MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。
filtered
这个字段表示存储引擎返回的数据在Server层过滤后，剩下多少满足查询的记录数量的比例，他是一个百分比。
ref
使用那个列或者常数和索引一起从表中筛选数据
Extra(额外说明)
using index
使用了覆盖索引
using where
使用了where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要server层进行过滤（跟是否使用索引没有关系）
using index condition
索引条件下推
需要优化的地方
using filesort （复合索引的前提）
不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）
order by 引起
using temporary
用到了临时表
可能出现的原因
distinct 非索引列
group by 非索引列
使用join 的时候，group 任意列
如果优化
例如：创建复合索引
存储引擎
存储引擎的选择
查询插入操作多的业务表，用MyISAM
临时数据用Memory
常规的并发大更新多的表用InnoDB
分区或分表
字段定义
原则：使用可以正确存储数据的最小数据类型
整数类型
Int 有8种类型
int
integer
bigint
bit
tinyint
smallint
mediumint
不同的类型最大存储范围是不一样的
字符类型
变长情况 varchar
固定长度 char
非空
Null 类型的存储、优化、使用都会存在问题
不要用外键、触发器、视图
降低了可读性
影响数据库性能，应该把计算的事情交给程序，数据库专心做存储
大文件存储
不要用数据库存储图片（比如 base64 编码）或者大文件
表拆分
把不常用的字段拆分出去，避免列数过多和数据量过大
应用层面
尽量减轻数据库的压力
比如：
限流
MQ削峰
架构分层
连接层
通信协议
TCP/IP
Unix Socket
Named Pipes
Share Memory
连接方式
长连接（连接池使用）
短链接
通信方式
单工
数据单向传输
半双工（MySQL）
数据双向传输，但不能同时传输
全双工
Server层
查询缓存（8.0弃用 Caches &Buffers）
分析器（解析器Parser）
步骤
语法解析 把一段SQL 打碎成一个个单词
词法解析 对SQL做一些语法检查比如单引号有没有闭合。
结果：生成解析树
预处理器
检查解析器生成的解析树，解决解析器无法解析的语义。
比如检查表和列名是否存在，检查名字和别名，保证没用歧义。
结果生成新的解析树。
查询优化器（Optimizer）
概念：根据解析树生成执行计划，然后选择一种最优的执行计划
优化过程是一个JSON类型数据，包含三个部分
准备阶段
优化阶段
执行阶段
结果：生成执行计划
优化器优化过程
启用优化器跟踪：
SHOW VARIABLES LIKE 'optimizer_trace';set optimizer_trace='enabled=on';
执行sql 语句：
查看优化器分析过程 select * from information_schema.optimizer_trace\\G
分析完记得关闭 set optimizer_trace=\"enabled=off\";SHOW VARIABLES LIKE 'optimizer_trace';
expanded_query 优化后的SQL语句
considered_execution_plans 里面列出了所有的执行计划。
查看执行计划 EXPLAIN select name from user where id=1;
执行器（操作存储引擎，返回结果到客户端）
是谁使用执行计划去操作存储引擎？执行引擎，他利用存储引擎提供的相应的API来完成操作。
为什么修改了表的存储结构，操作方式不需要做任何改变？不同功能的存储引擎实现的API是相同的。
存储引擎层
查看存储引擎 show table status from `数据库名称`;
数据库存放数据路径 show variables like 'datadir';
存储引擎类型
文件
frm表结构
ibd数据和索引
mysql 5.7 默认存储引擎
特点
支持事务，支持外键，因此数据的完整性、一致性更高
支持行级别锁和表级别锁
支持读写并发，写不阻塞读（MVCC）
适合场景
经常更新的表，存在并发读写或者有事务处理的业务系统
MyISAM
概念：利用索引，顺序存取数据的方式
MYD索引
MYI数据
支持表级别锁（插入和更新会锁表）。不支持事务
拥有较高的插入和查询速度
存储表的行数（count 速度更快）
场景
适合只读之类的数据分析的项目
Memory
概念：将所有数据存储都在内存，以便在需要快速查找非关键数据的环境中快速访问
特点：将数据放在内存，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。知识和做临时表
CSV
Archive
如何选择存储引擎
对数据一致性要求比较高，需要事务支持，可以选择InnoDB
数据查询多更新少，对查询性能要求比较高，可以选择MyISAM
需要一个用于查询的临时表，可以选择Memory
文件系统（磁盘）
概念：InnoDB的数据都是放在磁盘上的，InndDB 操作数据有一个最小逻辑单位，叫做页（索引页和数据页）
内存结构（缓冲池）
Buffer Pool
概念：对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了，InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。
第一次把磁盘把读取到数的页到缓冲池里面。
下一次读取相同的页，就先判断是否在缓冲池里面。有就直接读取，不在访问磁盘
修改数据，先修改缓冲池里面的页。内存数据和磁盘数据不一致的事情，这个叫脏页。InnoDB 里面有专门的后台线程把Buffer Pool数据写道磁盘，每隔一段时间就一次性把多个修改写入到磁盘，这个动作就做刷脏。
缓存的内容
数据页
索引页
内存的缓冲池写满了怎么办？
InnoDB 用LRU 算法来管理缓冲池
Chnage Buffer
概念：数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一检查）。可以先把修改记录在内存的缓冲池中，从而提升更新语句（INSERT、UPDATE、DELETE）的执行速度
适合场景：如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。
Log Buffer
概念： 为了避免Buffer Pool 里面的脏页还没刷到磁盘就宕机或者重启导致数据丢失的问题。InnoDB把多页的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作。（实现crash-safe）--用他来实现事务的持久性
red log
WAL技术
日志和磁盘配合的过程
关键点是 先写日志，再写磁盘
Adaptive Hash Index
自适应哈希索引
磁盘结构
系统（共享）表空间
数据字典
存储表和索引的元数据（定义信息）
双写缓冲
是为了解决存储引擎写入页的数据到磁盘是发生宕机，出现部分写失效，导致数据丢失的且内存red log页本身损坏 无法恢复的问题
double write分两步
一部分内存的double write
一部分磁盘的double write
因为是顺序写入，不会带来很大的开销
独占表空间
每张表独占一个表空间
存储表的索引和数据
通用表空间
存储不同数据库的表
临时表空间
存放临时表的数据，包括用户创建的临时表和磁盘的内部临时表
Redo log tablespace
undo log tablespace
存储撤销日志和回滚日志
用于修改数据时出现异常，可以用来实现回滚操作（保持原子性）
后台线程
master thread 负责刷新缓存数据到磁盘并协调调度其他后台线程
IO thread 分为 insert buffer、log、 read、write 进程，分别处理inser buffer、 重做日志（red log）、读写请求的IO回调
purge thread 用来回收 undo 页
page cleaner thread 用来刷新脏读
Binlog
概念： MySQL 的Server 层的日志文件，叫做binlog以事件的形式记录所有DDL和DML语句（记录的是操作而不是数据值，属于逻辑日志）
场景：用于做主从复制和数据恢复
red log （重做日志）
存储引擎层：内存、磁盘
物理日志，记录页做了什么改动
两个指针writer pos当前写的位置，check point当前要覆盖的位置
写满了怎么办？写满了同步到磁盘 red log
undo log
执行流程
1. 先把记录写内存，再写日志文件
2.记录red log 分两个阶段
prepare状态
提交事务时，red log 设置成commit状态。
3. 存储引擎和Server 记录不同日志（双写缓冲）
索引
概念：数据库索引，是数据库管理系统钟一个排序得数据结构，以协助快速查询、更新数据库表中数据。
作用：是为了提高查询效率
InnoDB 逻辑存储结构
表空间（table space）
概念：表空间可以看做是InnoDB存储引擎逻辑结构得最高层，所有得数据都存放在表空间中。
五大类：
系统表空间
双写缓冲区
Undo表空间
段(segment)
概念：表空间的组成部分
段的分类
数据段（non-leaf node segment）
管理叶子节点的数据
索引段（leaf node segment）
管理非叶子节点数据
回滚段
一个表的段数就是索引个数乘以2
簇(区extent)
概念
段的组成部分一个段至少有一个簇一个簇大小为1MB（64个连续的页）
一个簇的所有页都被用完，会从当前页面的段新分配一个簇。
如果数据数据不是连续的，往以写满的页中插入数据，会导致页的分裂。
页(page)
概念：为了高效管理物理空间，对簇进一步细分，得到了页，簇是有连续的页组成的空间，一个簇有64个连续的页
行(row)
索引的实现模型
1. 哈希表
适应场景：适合只有等值查询的场景。
局限：数据不是有序的，做区间查询的速度很慢
优势：查询快，修改快
2. 有序数组
优势：有序数组在等值查询和范围查询场景中的性能就都非常优秀
局限：往中间插入一条记录就必须得挪动后面所有得记录，成本太高
适合场景：只适用于静态存储引擎
3. 树
BST(二叉搜索树)
概念：每个节点左子树小于父节点，右子树大于父节点
AVL树（平衡二叉树）
概念：左右子树深度差绝对值不能超过1，左子树深度是2，右子树深度（只能是1或者3）
实现：在插入和更新数据的时候执行了一系列的计算和调整的操作。左-左型，会发生右旋。右-右型，会发生左旋
局限：树得深度过高，访问一个树节点，进行一次IO一个树的节点是16K大小，一个节点存储键值、数据磁盘地址、子节点引用。浪费大量的空间
B-Tree(多路平衡查找树)
分叉树（路数）永远比关键字数多1比如：每个节点存储两个关键字那么就会有三个指针指向三个子节点节点存储磁盘地址分裂合并
节点存储磁盘地址
分裂
合并
B+Tree(加强版多路平衡查找树)
概念：关键字的数量是跟路数相等的。非子节点不存储数据。叶子节点存储数据
单表，binint类型，深度为2的B+tree.可以存储2000W作用数据
单表可以存储多少条数据呢？
假设一条记录是1K，一页大小16K（16384字节），一页能存储16条数据binint 8个字节，指针6个字节，1页大小16384字节，非叶子节点一页可以存储 16384/14=1170 个指针对于第二层来源 1170个指针相当于1170个页，1170*11701170（1层的指针树）*1170（页）*16（一页存储的条数）=21902400条记录
特点：
每个节点存储更多的关键字，路数更多
扫库、扫表能力更强
排序能里更强因为叶子节点上有下一个数据区的指针，数据形成了链表
效率更加稳定B-Tree 永远是在叶子节点拿到数据，所以IO次数是稳定的
B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
红黑树
约束条件
1、节点分为红色或者黑色。
2、根节点必须是黑色的。
3、叶子节点都是黑色的 NULL 节点。
4、红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）。
5、从任意节点出发，到其每个叶子节点的路径中包含相同数量的黑色节点。
缺陷
只有两路
不够平衡
适应场景
内存中使用
Java TreeMap
Jdk 1.8 HashMap...
索引类型
唯一索引(Unique)
唯一索引要求键值不能重复。
主键索引
是特殊得唯一索引，要求简直不能为空
主键索引用 primay key创建
普通索引（Normal）
概念：也叫非唯一索引，是最普通的索引，没有任何的限制。
存储的是辅助索引和主键值
全文索引(Fulltext)
针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。
使用原则
列的离散度
公式：count(distinct(column_name)) : count(*)，
数据行数相同的情况下，分子越大，列的离散度就越高。
联合索引的最左匹配原则
在建立联合索引的时候，一定要把最常用的列放在最左边。
覆盖索引
回表
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
如果查询的数据列在辅助索引中就能够取到，就不需要去主键索引中读取，这个时候就使用到了覆盖索引，避免了回表，减少了IO次数，减少了数据访问量，提升查询效率
什么适合使用索引
再用where 判断 order 排序 和 join的 on 字段上创建索引
索引的个数不要过多，浪费空间，更新变慢
区分度低的字段，例如性别，不要建索引。离散度太低，导致扫描行数过多
频繁更新的值，不要作为主键或者索引，页分裂
组合算计把散列性高的值（区分度高）的值放在前面
创建复合索引，而不是修改单列索引
过长的字段，可以使用前缀索引
什么适合用不到索引
索引列上使用函数（replace\\SUBSTR\\CONCAT\\sum count avg）、表达式、计算（+ - * /）：
字符串不加引号，出现隐式转换
like 条件中前面带%
负向查询 NOT LIKE
如果一张表没用主键索引怎么办？
1、如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引
2、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
3、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增
0 条评论
回复 删除
下一页
