数据库 mysql

发布日期: 2020-11-04

更新日期: 2021-05-16

文章字数: 7.1k

阅读时长: 28 分

阅读次数:

MySql 的一些设计规范及 Explain 工具解析

MySql 设计规范

必须使用 InnoDB 存储引擎，非特殊要求一律使用此引擎
支持事务、行级锁、并发性能更好、CPU 及内存缓存页优化使得资源利用率更高
必须使用 utf8(utf8mb4)字符集
万国码，无需转码，无乱码风险，节省空间，utf8mb4 是 utf8 的超集，emoji 表情以及部分不常见汉字在 utf8 下会表现为乱码，故需要升级至 utf8mb4，mb4 即 most bytes 4，用四个字节存储更多的字符
数据表、数据字段必须加入中文注释
N 年后谁会知道这个 a1,a2,a3 字段是干嘛的
禁止使用存储过程、视图、触发器、Event
高并发大数据的互联网业务，架构设计思路是“解放数据库 CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU 计算还是上移吧
禁止存储大文件或者大照片
为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存 URI 多好
库名、表名、字段名：小写，下划线风格，不超过 32 个字符，禁止拼音英文混用
见名知意，方便后续维护
表必须包含物理主键 id 列，主键必须自增长（auto_increment），禁止使用 varchar 作为主键，如主键字段不能满足业务需求，另建 unique 约束业务字段
- 主键递增，数据行写入可以提高插入性能，可以避免 page 分裂，减少表碎片提升空间和内存的使用
- 主键要选择较短的数据类型， Innodb 引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
- 无主键的表删除，在 row 模式的主从架构，会导致备库夯住
索引命名约定：主键：pk_columnName (或者让数据库自动命名); 唯一键：uniq_columnName; 普通索引：idx_columnName; 组合索引：idx_column1_column2_Column3
见名知意，方便后续维护
禁止使用强外键，如果有外键完整性约束，在应用程序中控制
强外键会导致表与表之间耦合，update 与 delete 操作都会涉及相关联的表，十分影响 sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
尽量把字段定义为 NOT NULL 并且提供默认值
- null 的列使索引/索引统计/值比较都更加复杂，对 MySQL 来说更难优化
- null 这种类型 MySQL 内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
- null 值需要更多的存储空间，无论是表还是索引中每行中的 null 的列都需要额外的空间来标识
- 对 null 的处理时候，只能采用 is null 或 is not null，而不能采用=、in、<、<>、!=、not in 这些操作符号。如：where name!=’crm’，如果存在 name 为 null 值的记录，查询结果就不会包含 name 为 null 值的记录
禁止使用 TEXT、BLOB 类型
原则上不允许这种字段，尽可能的拆分成小字段，如果特别需要，而又读写频繁，另外建一张表
会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
金额存储使用 decimel(xx,2)
- 网上建议使用：使用“分”作为单位，这样数据库里就是整数了
- DECIMAL(M,D)中，M 范围是 1 到 65，D 范围是 0 到 30。
- M 默认为 10，D 默认为 0，D 不大于 M。
- DECIMAL(5,2)可存储范围是从-999.99 到 999.99，超出存储范围会报错。
- 存储数值时，小数位不足会自动补 0，首位数字为 0 自动忽略。
- 小数位超出会截断，产生告警，并按四舍五入处理。
- 使用 DECIMAL 字段时，建议 M，D 参数手动指定，并按需分配
使用 varchar(20)存储手机号
- 涉及到区号或者国家代号，可能出现+-()
- 手机号会去做数学运算么？有必要用 bigint 么？
- varchar 可以支持模糊查询，例如：like“138%”
禁止使用 ENUM（枚举），可使用 TINYINT 代替
- 增加新的 ENUM 值要做 DDL 操作
- ENUM 的内部实际存储就是整数，你以为自己定义的是字符串？
优先选择符合存储需要的最小的数据类型
列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差
将字符串转换成数字类型存储，如：将 IP 地址转换成整形数据
插入数据前，先用 inet_aton 把 ip 地址转为整型，可以节省空间
显示数据时，使用 inet_ntoa 把整型的 ip 地址转为地址显示即可
对于非负型的数据（如自增 ID、整型 IP）来说，要优先使用无符号整型来存储(UNSIGNED)
无符号相对于有符号可以多出一倍的存储空间
- SIGNED INT -2147483648~2147483647
- UNSIGNED INT 0~4294967295
单表索引建议控制在 5 个以内，单索引字段数不允许超过 5 个
- 一个好的索引设计，可以让你的效率提高几十甚至几百倍，但过多反而适得其反
- 字段超过 5 个时，实际已经起不到有效过滤数据的作用了
禁止在更新十分频繁、区分度不高的属性上建立索引
- 更新会变更 B+树，更新频繁的字段建立索引会大大降低数据库性能
- “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
建立组合索引，必须把区分度高的字段放在前面
能够更加有效的过滤数据、命中索引
禁止使用 SELECT *，只获取必要的字段，需要显示说明列属性
- 读取不需要的列会增加 CPU、IO、网络消耗
- 不能有效的利用覆盖索引
- 使用 SELECT *容易在增加或者删除字段后出现程序 BUG
禁止使用 INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
容易在增加或者删除字段后出现程序 BUG
禁止使用属性隐式转换
SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中 phone 索引； int 数据类型优先级高于 varchar，该查询会把 phone 转换为 int，因此需要把表中所有数据改成 int，所以必须全盘扫描 phone 是 varchar 类型，SQL 语句带入的是整形，故不会命中索引，加个引号就好了： SELECT uid FROM t_user WHERE phone=’13812345678’
禁止在 WHERE 条件的属性上使用函数或者表达式
SELECT uid FROM t_user WHERE from_unixtime(day)>=’2017-02-15’ 会导致全表扫描正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)
禁止大表使用 JOIN 查询（大表驱动），禁止大表使用子查询
会产生临时表，消耗较多内存与 CPU，极大影响数据库性能，大表一般是指 1000 万级以上数据量
禁止使用 OR 条件，必须改为 IN 查询
旧版本 Mysql 的 OR 查询是不能命中索引的，即使能命中索引（同表走索引），为何要让数据库耗费更多的 CPU 帮助实施查询优化呢？可以使用 IN 或 Union All 优化
禁止使用负向查询，以及%开头的模糊查询
- 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE 等，会导致全表扫描
- %开头的模糊查询，会导致全表扫描往往 WHERE 过滤条件不会只带这么一个“负向查询条件”，还会有其他过滤条件，举个例子：查询 risfeng 已完成订单之外的订单： SELECT oid FROM t_order WHERE uid=123 AND status != 1; 订单表 5000w 数据，但 uid=123 就会迅速的将数据量过滤到很少的级别（uid 建立了索引），此时再接上一个负向的查询条件就无所谓了，扫描的行数本身就会很少 ,但如果要查询所有已完成订单之外的订单： SELECT oid FROM t_order WHERE status != 1; 这就挂了，CPU 立马飙升，status 索引会失效，负向查询导致全表扫描
在明显不会有重复值时使用 UNION ALL 而不是 UNION
- UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
- UNION ALL 不会再对结果集进行去重操作
不允许使用的常见列名
status,order,desc,type,add,and,all,both,is_delete,row,delayed,group,
if,set,system,undo,unique,with,specific,dec,label,modifies,both,continue,
compute,keep,loop,leave,match,optimize,over,rows,session,show,force,following,
exists,do,div,delete,insert,select,create,table,cursor,column,columns,cross,current,
condition,check,change,admin
每张表的公共字段
- 每张表包含 2 个公共字段，created_at，updated_at
- is_delete 作为标识记录逻辑删除，非必须字段，枚举类型，0 有效，1 删除，默认为 0（有效），如数据量增长过大可通过归档，归档后在表中物理删除。
- created_at,updated_at 字段作为 DT 增量拉取数据和数据回退等场景下使用，created_at 默认 current_timestamp
- updated_at 默认 current_timestamp，on update current_timestamp
- 例如：
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
is_delete tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘逻辑删除标记’
字段冗余
非严格遵守 3NF，通过业务字段冗余来减少表关联
日期
- datetime 占 8 个字节，支持 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’，默认值非 current_timestamp()下，建议设置为’1970-01-01 08:00:01’。
- timestamp 占 4 字节，能表示最大的时间毫秒为 231-1=2147483647，换成时间刚好是 2038-01-19 03:14:07.999999。支持’1970-01-01 00:00:01’到 ‘2038-01-19 03:14:07’ UTC，建表时该字段不加任何属性时默认加非空约束，default 值为 current_timestamp，默认值非 current_timestamp()下，建议设置成’1970-01-01 08:00:01’。
- date 类型,默认值非 current_date()下，建议默认值为’1970-01-01’。
- 一张表只能有一个 timestamp 列用于标识自动更新，长度不允许自定义，非自动更新的列建议统一使用 datetime。
必须包含索引的字段：公共字段 created_at 和 updated_at 必须建立索引
这 2 列经常会用作同步数据或回退数据使用
数据批量导入时需要控制数据量
数据量过大会长时间占用事务资源，导致其他小事务长时间等待；数据量太小也失去批量的意义。
涉及到大量数据迁移时请选择业务低峰期
一般选择在半夜 12 点后执行此类动作

Explain 工具解析

了解一下 MySql 索引数据存储结构

推荐一个学习数据结构可视化站点：旧金山大学：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
数据储存物理文件：
1. MyISAM:物理文件有 3 个：.frm .myi .myd
2. InnoDb：物理文件有 2 个：.frm .ibd
B-Tree 索引数据结构图

B+Tree（B-Tree 变种）索引数据结构图
1. 非叶子节点不存储 data，只存储索引(冗余)，可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用指针连接，提高区间访问的性能

MyISAM 索引文件和数据文件是分离的(非聚集)

InnoDB 索引实现(聚集索引+非聚集索引)
1. 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
2. 聚集索引-叶子节点包含了完整的数据记录
3. 为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键？
  1. 如果设置了主键，那么 InnoDB 会选择主键作为聚集索引、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引(ROWID 随着行记录的写入而主键递增)
  2. 如果表使用自增主键那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页
  3. 如果使用非自增主键（如 uuid、身份证号等）由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。
4. 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
5. 为什么 mysql 页文件默认 16K？
  1. 查看 mysql 文件页大小（16K）：SHOW GLOBAL STATUS like ‘Innodb_page_size’;
  2. 不建议修改，mysql 设置为 16K 是通过各方面考虑、验证、衡量的结果；
  3. 计算一下：假设我们一行数据大小为 1K，那么一页就能存 16 条数据，也就是一个叶子节点能存 16 条数据；再看非叶子节点，假设主键 ID 为 bigint 类型，那么长度为 8B，指针大小在 Innodb 源码中为 6B，一共就是 14B，那么一页里就可以存储 16K/14=1170 个(主键+指针) 那么一颗高度为 2 的 B+树能存储的数据为：117016=18720 条，一颗高度为 3 的 B+树能存储的数据为：11701170*16=21902400（千万级条）

联合索引的底层存储结构

Explain 工具介绍

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 语句，分析你的查询语句或是结构的性能瓶颈
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条 SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain 分析示例

# 示例表
CREATE TABLE actor
(
    id          INT         NOT NULL PRIMARY KEY,
    name        VARCHAR(45) NULL,
    update_time DATETIME    NULL
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 示例数据
INSERT INTO my_demo.actor (id, name, update_time) VALUES (1, 'a', '2020-11-06 00:59:54');
INSERT INTO my_demo.actor (id, name, update_time) VALUES (2, 'b', '2020-11-06 00:59:54');
INSERT INTO my_demo.actor (id, name, update_time) VALUES (3, 'c', '2020-11-06 00:59:54');

CREATE TABLE film
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) NULL
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX idx_name ON film ( name );

INSERT INTO my_demo.film (id, name) VALUES (2, 'film 2');
INSERT INTO my_demo.film (id, name) VALUES (3, 'film0');
INSERT INTO my_demo.film (id, name) VALUES (1, 'film1');

CREATE TABLE film_actor
(
    id       INT          NOT NULL PRIMARY KEY,
    film_id  INT          NOT NULL,
    actor_id INT          NOT NULL,
    remark   VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX idx_film_actor_id ON film_actor ( film_id, actor_id );

INSERT INTO my_demo.film_actor (id, film_id, actor_id, remark) VALUES (1, 1, 1, null);
INSERT INTO my_demo.film_actor (id, film_id, actor_id, remark) VALUES (2, 1, 2, null);
INSERT INTO my_demo.film_actor (id, film_id, actor_id, remark) VALUES (3, 2, 1, null);

执行如下语句会得到分析结果，下面会根据如下结果分析。

explain select * from actor;

Explain 中的列分析

Id 列
- id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按 select 出现的顺序增长的。
- id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。
select_type 列：
表示对应行是简单还是复杂的查询。

1）simple：简单查询。查询不包含子查询和union
explain select * from film where id = 2;

2）primary：复杂查询中最外层的 select
3）subquery：包含在 select 中的子查询（不在 from 子句中）
4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
用下面来这个SQL来了解primary、subquery和derived类型

#关闭mysql5.7+新特性对衍 生表的合并优化
set session optimizer_switch='derived_merge=off';
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
#还原默认配置
set session optimizer_switch='derived_merge=on';

5）union：在 union 中的第二个和随后的 select
explain select 1 union all select 1;

table 列
- 这一列表示 explain 的一行正在访问哪个表。
- 当 from 子句中有子查询时，table 列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
- 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1 和 2 表示参与 union 的 select 行 id。

explain select * from film where id = 1 UNION  select * from film where id = 2 UNION select * from film where id = 3;

** type 列：**
这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：null>system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到 range 级别，最好达到 ref

1）null：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

explain select min(id) from film;

2）system，const：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多 有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为

set session optimizer_switch='derived_merge=off';
explain select * from (select * from film where id = 1) tmp;
show warnings;

3）eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种type。

explain select * from film_actor left join film on film_actor.film_id = film.id;

4）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
a. 简单 select 查询，name是普通索引（非唯一索引）
explain select * from film where name = 'film1';

b.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
explain select film_id from film left join film_actor on film.id = film_actor.film_id;

5）range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定 范围的行。
explain select * from actor where id > 1;

6）index：扫描全表索引，这通常比ALL快一些。
explain select * from film;

7）ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
explain select * from actor;

** possible_keys 列**
这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql 认为索引对此查询帮助不大，选择了全表查询。如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
** key 列**
这一列显示 mysql 实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制 mysql 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。
** key_len 列**
这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是 4 字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id 列来执行索引查找。

explain select * from film_actor where film_id = 2;

key_len计算规则如下：
1）字符串
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8（1-3个字节）utf-8mb4(1-4个字节)，则长度 3n + 2（<255使用1个字节存储长度 >255使用2个字节存储长度）
2）数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节

3）时间类型
date：3字节
timestamp：4字节
datetime：8字节

4）如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref 列
这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
** rows 列**
这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。
filtered 列
是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）

explain  select * from film where id = 1;

Extra 列
这一列展示的是额外信息。常见的重要值如下：

1）Using index：使用覆盖索引
explain select film_id from film_actor where film_id = 1;

2）Using where：使用 where 语句来处理结果，查询的列未被索引覆盖
explain select * from actor where name = 'a';

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范 围；
explain select * from film_actor where film_id > 1;

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
a) actor.name没有索引，此时创建了张临时表来distinct
explain select distinct name from actor;

b) film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
explain select distinct name from film;

5） Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
a) actor.name未创建索引，会查询actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录 explain select * from actor order by name;
b)  film.name建立了idx_name索引,此时查询时extra是using index
explain select * from film order by name;

6)Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段，table列显示为Null (SELECT操作已经优化到不能再优化了)
explain select min(id) from film;

索引最佳实践

示例表

CREATE TABLE `employees`
(
    `id`        INT(11)     NOT NULL AUTO_INCREMENT,
    `name`      VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
    `age`       INT(11)     NOT NULL DEFAULT '0' COMMENT '年龄',
    `position`  VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
    `hire_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时 间',
    PRIMARY KEY ( `id` ),
    KEY `idx_name_age_position` ( `name`, `age`, `position` ) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8 COMMENT ='员工记录表';


INSERT INTO employees( NAME, age, position, hire_time )
VALUES ( 'LiLei', 22, 'manager', NOW() );
INSERT INTO employees( NAME, age, position, hire_time )
VALUES ( 'HanMeimei', 23, 'dev', NOW() );
INSERT INTO employees( NAME, age, position, hire_time )
VALUES ( 'Lucy', 23, 'dev', NOW() );

全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

-- 下列哪些SQL左右索引了，why？
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

不在索引列上做任何操作
比如：计算、函数、（自动 or 手动）类型转换，会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

存储引擎不能使用索引中范围条件右边的列
联合索引中当遇到范围查找时右边剩余索引不使用
< > >= <= like

-- 下列SQL会命中哪些索引？why？
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

尽量使用覆盖索引
只访问索引的查询（索引列包含查询列），减少 select *语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

mysql 在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

like 以通配符开头（’$abc…’）mysql 索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';

问题：解决like'%字符串%'索引不被使用的方法？
1）使用覆盖索引，查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

2）如果不能使用覆盖索引则可能需要借助搜索引擎

字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

少用 or 或 in，用它查询时，mysql 不一定使用索引，mysql 内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

范围查询优化

给年龄添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE;

1）eg1:
explain select * from employees where age >=1 and age <=200000;
分析：没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索 引。
比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以讲大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;

还原最初索引状态：
ALTER TABLE `employees` DROP INDEX `idx_age`;

索引使用总结

假设索引为：index(a,b,c)
where 语句	是否使用了索引，请列举出来
where a=3	yes，使用到 a
where a=3 and b=5	yes，使用到 a，b
where a=3 and b=5 and c=4	yes，使用到 a,b,c
where b=3 或 where b=3 and c=4 或者 where c=4	no
where a=3 and c=5	yes，使用到 a ，c 不走，被 b 中断了
where a=3 and b like ‘kk%’ and c=4	yes，使用到 a,b,c
where a=3 and b like ‘%kk’ and c=4	yes，只使用到 a
where a=3 and b like ‘%kk%’ and c=4	yes，只使用到 a
where a=3 and b like ‘k%kk%’ and c=4	yes，使用到 a,b,c
说明：like KK%相当于=常量，%KK 和%KK% 相当于范围