我是根据黑马的视频学习的，之前零零散散的学习过mysql， 会写基础的sql，所以基础部分掠过，直接看得进阶篇

索引

Hash索引

特点
  • 只能用于对等比较(=, in), 不支持范围查询(between, >, <)
  • 无法利用索引完成排序操作
  • 查询通常一次检索就可以，效率高，通常高于比B+tree索引
存储引擎支持：
  • Memory
  • InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的
为什么innoDB使用B+tree
特性 哈希表 (Hash) 二叉平衡树 (AVL/红黑树) B 树 B+ 树 (InnoDB选择)
等值查询 极快 快 快 快
范围查询 不支持 较慢 (需回溯) 一般 (需跨层) 极快 (链表遍历)
磁盘 I/O 较多 很多 (树太高) 较少 极少 (树最矮)
排序支持 不支持 支持 支持 天然支持且高效
  • 相较于二叉树，层级更少，搜索效率高
  • 相较于B树，B树每个节点既存key又存数据，导致每个节点容纳的数据更少，树会更高
  • 相较于hash，B+支持范围匹配及查询

语法

创建
1
2
3
4
5
6
7
8
-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 创建联合索引（多列索引）
CREATE INDEX index_name ON table_name (col1, col2, col3);
查看
1
show index from table_name;
删除
1
drop index index_name on table_name;

sql性能分析

查看sql执行频次
1
2
3
4
-- 查看当前所有次数统计
show status like 'Com_____';
show global status like 'Com_select'; -- 查询次数

慢查询日志

记录所有执行时间超过阈值的sql，默认情况下，mysql不开启慢查询日志

1
2
3
4
5
6
-- 查看是否开启
show variables like 'slow_query_log';
-- 临时开启（重启后失效
set global slow_query_log = 1;
-- 设置阈值，单位秒
set global long_query_time = 2;
explain
  • 使用
    只需要在sql前加上explain就行

各个字段含义

  1. id
    输出的id字段代表执行顺序
    • id相同：执行顺序从上到下
    • id不同：id大的，执行优先级更高
  2. type
    表示连接类型，性能由好到差依次是NULL,system, const, eq_ref, ref, range index, all

索引的使用

最左前缀法则

如果索引关联了多列(联合索引)，则遵循最左前缀法则，也就是说，在联合索引中，查询必须从索引的最左列开始，不能跳过索引

假设在一个user表上建立一个联合索引 index(a,b,c)
三种使用场景

1
2
3
4
5
6
7
8
9
10
11
12
-- 全值匹配
-- 完全符合最做前缀法则，sql的编写顺序不影响（比如先写 `b=2` 再写 `a=1`）
select * from user where a = 1 and b = 2 and c = 3;

-- 部分匹配
select * from user where a = 1 and b = 2;

-- 违背规则 索引部分或全部失效
-- 完全失效
select * from user where b = 2 and c = 3;
-- 部分失效
select * from user where a = 1 and c = 3;

如果联合索引中某个列使用了范围查询（>、<、between、like 'xxx%'），那么该列右侧的索引列将失效。可以使用>= <= 来规避

为什么范围查询右侧会失效，使用>=, <=可以规避

  • 在select * from user where a = 1 and b > 10 and c = 3中，B+树是按a排序的，如果a相等按b，然后再按c，a = 1：mysql很快就会定位到对应的数据块，b > 10：在a = 1 范围内，b是有序的，可以很快找到符合条件的b，c = 3: b > 10中， b可能等于11, 12, 13…… ，在b = 11范围内，c是有序的，但是在整个b > 10的结果集里，c整体是无序的，所以B+树无法按索引跳跃查找，只能扫描
  • 其实，>= 并没有从底层算法上彻底改变 B+ 树的逻辑，但在某些情况下，它能让 等值的那部分数据 继续利用索引。

索引失效

  1. 在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
  2. 字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号
  3. 模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。
  4. 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
    or是一个并集操作， 假设执行select * from where id = 10 and name = zhangsan;,id是主键使用主键索引，但是name没有索引，依然要进行全表扫描
  5. 如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL提示

是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

例如，使用索引：
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
不使用哪个索引：
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
必须使用哪个索引：
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

聚集索引

B+树存储，叶子节点存储的完整的行记录，每个表只能有一个聚集索引
选取规则：

  • 如果有主键，主键就是聚集索引
  • 没有主键，选择第一个不为NULL的唯一索引
  • 都没有，innoDB生成一个隐式的row_id作为聚集索引

二级索引

除聚集索引外的其他索引，都是二级索引，也叫辅助索引，椰子节点存储的是该行对应的主键值
包含的是 索引对应的列以及主键

回表

  1. 第一步：SQL 在 二级索引树 中找到 name='张三'，并获取到对应的 ID（如 ID=10）。

  2. 第二步：由于二级索引没存完整的行数据，MySQL 需要拿着这个 ID=10 回到 聚集索引树 中再次查找，以获取完整的行记录。

这个从二级索引回到聚集索引查找数据的过程，就叫“回表”。

聚集索引

不是一种实际存在的索引，而是一种查询时的现象：一个索引包含所需要查询的所有字段，它就不需要进行回表，直接从索引中返回数据

如何判断是否使用了聚集索引

查看explain结果的extra列，结果是

  • Using index:说明使用了覆盖索引
  • Using index condition: 说明使用了索引，但是有可能发生回表
  • null/where： 意味着发生了回表

前缀索引

在对一个包含大量字符，文本的列建立索引时，索引会变得很大，而且效率低下，于是产生了前缀索引，只取字段的前一部分字符建立索引

语法
1
create index idx_email_10 ON user (email(10));
确定合适的前缀长度

先了解一下选择性的概念：它是指不重复的索引值（基数）与表记录总数的比值。比值越高，查询效率越高

计算公式为

1
2
3
4
-- 查看全列的选择性
select count(distinct email) / count(*) from user;
-- 查看不同长度前缀的选择性
select count(distinct left(email, 5)) / count(*) from user;

如果前缀太短，容易重复，会增加回表的次数，太长又失去节省空间的意义

前缀索引的缺点
  • 不能成为覆盖索引，因为存储的是字段的前缀，要确定字段的值，一定会去回表查看。所以即使只查看该列，explain的extra也会显示回表
  • 不适用于排序和分组group，因为前缀索引只代表部分顺序，无法保证完整字段的排序结果

索引设计原则

image.png

SQL优化

插入数据

  1. 批量插入数据
  2. 手动提交事物
  3. 按主键顺序优化

    • 减少页分裂： InnoDB 的数据是存储在 B+ 树的叶子节点上的。顺序插入时，数据会被追加在当前页的末尾，页满后才开辟新页。

    • 避免性能骤降： 如果主键是随机的（如 UUID），为了保持 B+ 树有序，MySQL 必须频繁地在中间位置插入数据，这会导致频繁的页分裂和记录移动，产生大量的随机 I/O 和索引碎片。

大批量插入数据

如果数据量特别大，使用insert的效率会比较低，应该使用mysql自带的load
开启操作

1
2
3
4
5
6
7
# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）  
mysql --local-infile -u root -p  
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关  
set global local_infile = 1;  
select @@local_infile;  
# 执行load指令将准备好的数据，加载到表结构中  
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

  • 页分裂：页可以为空，也可以填充一般，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。

  • 页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。

主键设计原则：
  • 满足业务需求的情况下，尽量降低主键的长度
  • 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
  • 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号
  • 业务操作时，避免对主键的修改

order by优化

  • Using index：效率最高。指 MySQL 直接通过索引树的有序性完成排序，不需要额外操作。

  • Using filesort：效率较低。指 MySQL 无法利用索引，必须在内存（sort_buffer）或磁盘中进行排序操作。

如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort，如果要优化掉Using filesort，则需要另外再创建一个索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引

总结：

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
  • 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

group by优化

  • 在分组操作时，可以通过索引来提高效率
  • 分组操作时，索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则