mysql让某个字段的数据变成随机生成32位随机数 mysql随机io转换成顺序io

思维导图

一、索引的优点

1、大大减少了服务器需要扫描的数据量。

2、帮助服务器避免排序和临时表。

3、将随机io变成顺序io。

问题一、什么是随机io和顺序io。

• 顺序IO是指读写操作的访问地址连续。在顺序IO访问中，HDD所需的磁道搜索时间显着减少，因为读/写磁头可以以最小的移动访问下一个块。数据备份和日志记录等业务是顺序IO业务。
• 随机IO是指读写操作时间连续，但访问地址不连续，随机分布在磁盘的地址空间中。产生随机IO的业务有OLTP服务，SQL，即时消息服务等。

二、索引的用处

1、快速查找匹配WHERE子句的行（能够快速匹配where条件对应的数据行。）；

• 举个栗子

这里age字段建立了索引，update_time 没有建立索引，从一下查询计划可以看出没有索引的查询进行了全表扫描，而有索引的查询可以缩小扫描的数据条数；

例1：explain select * from user where update_time >=‘2020-07-20 15:27:18’;

例2：explain select * from user where age > 24;

2、从consideration中消除行,如果在多个索引之间进行选择，mysql通常会使用找到最少行的索引（虽然可以创建多个索引，但是mysql只会选择其中一个，这也就解释组合索引出现）；

• 举个栗子

例1：explain select * from user where age = 25 and name like ‘王%’;

例2：explain select * from user where age >23 and name like ‘王%’;

由上图可以看出如果建立了多个索引 mysql优化器会在这些索引中选择查询最少行的索引。如果数据库表具有多列索引，数据库优化器可以使用任何列的索引最左前缀来查找行。

3、在使用组合索引时，mysql优化器可以使用索引的任何最左前缀来查找行，也就是最左匹配原则；

最左前缀匹配，当查询条件精确匹配索引的左边连续一个或几个列时，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。

4、当有表连接的时候，从其他表检索行数据

5、查找特定索引列的min或max值

下面用同一个sql语句，图一使用的索引，图二去掉了索引，显然第一个走了索引，因为在innodb myisam 两种执行引擎下索引的结构都为b+tree,因此可以直接在有序的索引结构中获取最大值和最小值所对应的主键id，然后进行回表，查找到对应的值。

• 注：innodb引擎下只有主键索引的叶子节点中才会存储行数据，普通索引在叶子节点中值存储对应的主键值；
• 图一：gender建立了对应的索引



• 图二：gender去除了对应的索引

6、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组

7、在某些情况下，可以优化查询以检索值而无需查询数据行

当查询的列为主键id时，只需要通过普通索引进行查询就可以，因为普通索引的叶子节点存储的为主键id，这时不需要进行回表；因为已经检索到需要的主键列，这就是所谓的覆盖索引。

三、索引的分类

数据库会默认创建索引，但是并不是给主键建立索引，而是给唯一键建索引的，因为主键的特性是唯一且非空

• 主键索引： 是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）
• 唯一索引： 索引列中的值必须是唯一的，但是允许为空值。
• 普通索引： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。
• 全文索引： 只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"LOL LPL " 通过LOL LPL，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

• 组合索引： 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

例如这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。

四、索引采用的数据结构

• 哈希表
• B+树

五、面试的技术名词

1.回表

比如：select * from emp where e.ename=‘test’;先是根据ename列的索引B+树，找到叶子节点上存储的主键值，然后根据主键的索引B+树，查找整行的数据。整个这个操作叫做回表。

2.索引覆盖

比如：select empno from emp where e.ename=‘test’;先是根据ename列的索引B+树，找到叶子节点上存储的主键值，由于显示的就是empno,不需要再去主键的索引B+树去查找了，这种情况叫做覆盖索引。

3.最左匹配

• 最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

1.比如a=3 and b=4 and c>5 and d=6 如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）索引则可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a=1 and b=2 and c=3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮忙优化成索引可以识别的形式

• 注意是组合索引的情况

select * from t where a=1 and b=1 and c =1; #这样可以利用到定义的索引（a,b,c）
select * from t where a=1 and b=1; #这样可以利用到定义的索引（a,b,c）
select * from t where a=1; #这样也可以利用到定义的索引（a,b,c）
select * from t where b=1 and c=1; #这样不可以利用到定义的索引（a,b,c）
select * from t where a=1 and c=1; #这样不可以利用到定义的索引（a,b,c）
select * from t where a=1 and b>1 and c =1; #这样a,b可以用到（a,b,c），c不可以

4.索引下推

• 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
• MySQL大概的架构

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

• 没有使用ICP的情况:

1.存储引擎读取索引记录；
2.根据索引中的主键值，定位并读取完整的行记录；
3.存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

• 使用ICP的情况:

1.存储引擎读取索引记录（不是完整的行记录）；
2.判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
3.条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
4.存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

1.具体实践

• 使用一张用户表user，表里创建联合索引（name, age）。检索出表中名字第一个字是张，而且年龄是10岁的所有用户.

select * from tuser where name like ‘张%’ and age=10;

• 没有使用ICP的情况

在MySQL 5.6之前，存储引擎根据通过联合索引找到name likelike ‘张%’ 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

• 使用ICP

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name likelike ‘张%’，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

除此之外我们还可以看一下执行计划，看到Extra一列里Using index condition，这就是用到了索引下推。

2.索引下推使用条件

• 1.只能用于range、 ref、 eq_ref、ref_or_null访问方法；
• 2.只能用于InnoDB和 MyISAM存储引擎及其分区表；
• 3.对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

• 4.引用了子查询的条件不能下推；
• 5.引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数

3.相关系统参数

• 索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

查看默认状态：mysql> select @@optimizer_switch\G;

• 切换状态：

set optimizer_switch=“index_condition_pushdown=off”;
set optimizer_switch=“index_condition_pushdown=on”;

六、索引的匹配方式

1.准备条件

create table staffs(
id int primary key auto_increment,
name varchar(24) not null default ‘’ comment ‘姓名’,
age int not null default 0 comment ‘年龄’,
pos varchar(20) not null default ‘’ comment ‘职位’,
add_time timestamp not null default current_timestamp comment ‘入职时间’
) charset utf8 comment ‘员工记录表’;
-----alter table staffs add index idx_nap(name, age, pos);

2.全值匹配

• 全值匹配指的是和索引中的所有列进行匹配

explain select * from staffs where name = ‘July’ and age = ‘23’ and pos = ‘dev’;

3.匹配最左前缀

• 只匹配前面的几列

explain select * from staffs where name = ‘July’ and age = ‘23’;

explain select * from staffs where name = ‘July’;

4.匹配列前缀

• 可以匹配某一列的值的开头部分

explain select * from staffs where name like ‘J%’;

explain select * from staffs where name like ‘%y’;

5.匹配范围值

• 可以查找某一个范围的数据

explain select * from staffs where name > ‘Mary’;

6.精确匹配某一列并范围匹配另外一列

• 可以查询第一列的全部和第二列的部分

explain select * from staffs where name = ‘July’ and age > 25;

explain select * from staffs where name = ‘July’ and pos > 25;

说明只是匹配到name

7.只访问索引的查询

• 查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’;

七、通用规则

• 索引并不是越多越好，一张表中建议不超过5个。因为索引本身也占资源，再者数据的更新需要维护索引
• 单个组合索引字段数不允许超过5个
• 索引尽量建在where后面经常使用的字段上
• 索引必须建立在离散程度大的列上，比如身份证号、员工工号等，而不是性别、职位等字段，因为性别、职位等字段重复值太多，筛选度低
• 禁止在select语句后面使用*，而是写出具体要返回的列
• 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

在验证这条规则前，先添加一个索引

– age 列创建索引

alter table staffs add index idx_age(age)

仔细观察这两条SQL语句的执行计划，结果一目了然

• 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询
• 强制类型转换可能会全表扫描（包括where条件和连接查询时的连接条件）

• 表中尽量不要有为null的数据，可以用默认值代替
• union all, in, or都能够使用索引，但是推荐使用in，仔细观察以下几条SQL语句的执行计划

可以看到union all, in, or都能够使用索引，如果数据量小的话就不一定了。

• 范围列可以用到索引，但是范围列后面的列无法用到索引，包括：<、<=、>、>=、between
• 模糊查询不要以通配符开头
• 能使用union all就不使用union，因为union all不需要执行类似distinct操作
• 使用group by语句时，尽量先过滤再分组。即把条件写在where子句里而不是having子句
• 利用索引完成排序(order by)操作（同为升序或者降序）

创建索引的时候，索引本身就是按照升序排列的，如果排序的规则能利用索引来排序，就不需要用文件来排序(extra不会出现using filesort)。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，MySQL才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查询子句的限制是一样的，需要满足索引的最左前缀的要求。否则，MySQL都需要执行额外的排序操作，而无法利用索引排序。仔细观察以下几条SQL语句的执行计划就明白了，主要关注type和Extra

第1条和第2条对比，说明order by的排序方式要和索引保持一致，或者order by后面的两个字段都按照desc排序也是可以的；第1条和第3条对比，说明范围查找会使组合索引中，范围查找条件之后的列索引失效，也就是>筛选符号之后的列不能用到索引；第1条和第4条对比，说明order by子句和where一样也需要满足最左前缀。

• 优化limit，该关键字常用于分页查询，如果数据量较大时，分页查询可能会变得很慢

查询第800万条数据开始的连续5条数据，查询花费了很长时间，可以改写成如下SQL语句

可以看到，在不添加任何过滤条件的情况下，耗时相对少一些。在有过滤条件的情况下效果可能会更显著，对比下面这两条SQL就一目了然了

• 为什么改写之后会比直接写limit快。主要是因为直接写limit m, n，访问数据的指针并不会直接找到第m条数据。而是依然从第一条数据开始依次往下找，所以m越大，耗时越久。而改写之后的写法，先利用索引覆盖，找到对应记录的ID，再根据ID来进行关联，所以会比直接写limit快。

既然利用索引覆盖找到了需要数据的id，或许我们可以试试in关键字会不会更快
mysql> select a.* from sicimike a where a.id in (select id from sicimike where name like ‘c6%’ order by id limit 30000, 5);
ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
MySQL5.6不支持这种语法。

新加:1.使用前缀索引

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

案例演示：

• 创建数据表

create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;
city数据库在mysql演示数据里面

• 重复执行5次下面的sql语句

insert into citydemo(city) select city from citydemo;

• 更新城市表的名称

update citydemo set city=(select city from city order by rand() limit 1);

• 查找最常见的城市列表，发现每个值都出现45-65次，

select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

• 查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数

select count() as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
select count() as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
此时前缀的选择性接近于完整列的选择性

• 还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了

select count(distinct left(city,3))/count() as sel3,
count(distinct left(city,4))/count() as sel4,
count(distinct left(city,5))/count() as sel5,
count(distinct left(city,6))/count() as sel6,
count(distinct left(city,7))/count() as sel7,
count(distinct left(city,8))/count() as sel8
from citydemo;

• 计算完成之后可以创建前缀索引

alter table citydemo add key(city(7));

• 注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

新加:2.使用索引扫描来排序

新加:3 更新十分频繁，数据区分度不高的字段上不宜建立索引

• 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
• 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据
• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

新加:4.当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

• 比如A表jponB表是通过int类型那么B表joinC表也最好是int类型
• Simple Nested-Loop Join
• Index Nested-Loop Join
• Block Nested-Loop Join

join_buffer_size可以设置大小，命令查询：show variables like ‘%join_buffer%’;

新加:5.能使用limit的时候尽量使用limit,比如确定返回只有一条数据可以使用limit 1

新加:6所以and和where区别

新加:7.创建索引的时候应该避免以下错误概念

• 索引越多越好
• 过早优化，在不了解系统的情况下进行优化

八、索引监控

• show status like ‘Handler_read%’;
• 参数解释(一般观察Handler_read_key和Handler_read_rnd_next越大说明用到索引越多)

• Handler_read_first：读取索引第一个条目的次数
• Handler_read_key：通过index获取数据的次数
• Handler_read_last：读取索引最后一个条目的次数
• Handler_read_next：通过索引读取下一条数据的次数
• Handler_read_prev：通过索引读取上一条数据的次数
• Handler_read_rnd：从固定位置读取数据的次数
• Handler_read_rnd_next：从数据节点读取下一条数据的次数

九、案例说明

1.准备好数据

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `itdragon_order_list`;
CREATE TABLE `itdragon_order_list` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
  `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
  `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  `net` double DEFAULT NULL COMMENT '净收入(RMB)',
  `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
  `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
  `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
  `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
  `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
  `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
  `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
  `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');

2.第一个案例

select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--通过查看执行计划发现type=all,需要进行全表扫描
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

--优化一、为transaction_id创建唯一索引
 create unique index idx_order_transaID on itdragon_order_list (transaction_id);
--当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
 explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 
 --优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

3.第二个案例

--创建复合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

--创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
explain select * from itdragon_order_list order by order_level,input_date;

--可以使用force index强制指定索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
--其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
explain select * from itdragon_order_list where order_level=3 order by input_date;