mysql5_primary key (`id`) using btree-优快云博客

本文链接：https://blog.youkuaiyun.com/Markland_l/article/details/120500495

本文深入探讨了MySQL中索引的使用，包括如何在查询中有效利用索引以及不同搜索条件下的索引应用。对于AND和OR操作符的组合，解释了其对索引使用的影响。此外，分析了复杂查询条件下如何确定扫描区间，并举例说明了联合索引在不同查询场景下的表现。还介绍了MyISAM和InnoDB索引的区别，以及创建、删除索引的代价。提出了高性能索引创建策略，如选择性高的列、前缀索引、多列索引和三星索引的概念。最后讨论了主键选择、冗余索引和未使用索引的管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql5

深入思考索引在查询中的使用

所有搜索条件都可以使用某个索引的情况

SELECT * FROM order_exp WHERE order_no > ‘DD00_6S’ AND order_no > ‘DD00_9S’;
- 搜索条件是两个，用and符取交集，所以是能用到索引的
SELECT * FROM order_exp WHERE order_no > ‘DD00_6S’ OR order_no > ‘DD00_9S’;
- 搜索条件是两个，用or符取并集，也是可以用到索引的

有的搜索条件无法使用索引的情况

SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ AND order_note = ‘abc’;
- expire_time有索引，order_note 没有索引，但是先走expire_time的索引，回表找到具体的范围数据，然后再按order_note 条件过滤
SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;
- 这个不能用到索引，因为用的or，这个是缩小不了范围，虽然用到了expire_time的索引，回表找到对应主键的所有数据，然后检查order_note 的条件时，即使前面expire_time判断找出的记录，也无法判断在聚簇索引里对应的这些数据是否满足order_note 的条件，此时mysql执行引擎必须进行全表扫描才能判定哪些数据是满足order_note 这个条件的

复杂搜索条件下找出范围匹配的区间

SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’ AND expire_time = ‘2021-03-22 18:35:09’ ) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_9S’) OR (order_no LIKE ‘%0S’ AND order_no > ‘DD00_12S’ AND (expire_time < ‘2021-03-22 18:28:28’ OR order_note = ‘abc’)) ;
- ```
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `u_idx_day_status`(`insert_time`, `order_status`, `expire_time`),
INDEX `idx_order_no`(`order_no`) USING BTREE,
INDEX `idx_expire_time`(`expire_time`) USING BTREE 
```
- 使用idx_order_no执行查询
- 使用idx_expire_time执行查询
对于这种语句的分析
- 首先看where条件，哪些列上有索引，order_no和expire_time是有索引的，而order_note 是没有索引的
- 假设mysql使用order_no作为索引搜索条件，把其他凡是用不到这个索引的用true代替，上面的语句变形成
  - SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’ AND True ) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_9S’) OR (True AND order_no > ‘DD00_12S’ AND (True OR True )) ;
  - SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_9S’) OR (order_no > ‘DD00_12S’) ;
  - SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’) OR (False) OR (order_no > ‘DD00_12S’) ;
  - SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’) OR (order_no > ‘DD00_12S’) ;
  - SELECT * FROM order_exp WHERE (order_no > ‘DD00_12S’) ;
  - 最终是否使用索引是mysql整体判断的，也有可能是全表扫描
- 如果使用expire_time作为索引搜索条件时，最后简化成true，反证了此时mysql一定不会以expire_time作为索引搜索条件的，还不如全表扫描
对于这个复制sql，order_no的全表扫描代价大概是2199，而expire_time的全表扫描代价是6211，expire_time的执行代价更大，而全表扫描的代价要比两者都小，所以mysql执行器最后会决定走全表扫描，这个就牵扯到mysql中的成本分析

使用联合索引执行查询时对应的扫描区间

order_exp表的u_idx_day_status联合索引
UNIQUE INDEX `u_idx_day_status`(`insert_time`, `order_status`, `expire_time`)

Q1：SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’;
- 满足最左前缀原则，能够根据insert_time在联合索引中找到对应的主键，然后回表找到所有的数据
Q2：SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0;
- 先根据insert_time找到对应的数据集，然后再从里面过滤掉，找出符合order_status 条件的数据
Q3：SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0 AND expire_time = ‘2021-03-22 18:35:13’;
- 先根据insert_time找出对应的数据集，然后先按order_status 字段过滤，再按expire_time 字段过滤
Q4：SELECT * FROM order_exp WHERE insert_time < ‘2021-03-22 18:34:55’;
- 根据insert_time 字段确定扫描区间，从第一条数据到’2021-03-22 18:34:55’这条数据之间
Q5：SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND order_status > =0 ;
- 先根据insert_time = ‘2021-03-22 18:34:55’ 找到对应的数据集，然后再根据order_status 确定扫描区间
Q6：SELECT * FROM order_exp WHERE order_status = 1;
- 无法使用索引，因为联合索引是按照insert_time、order_status、expire_time这个顺序来排序的，无法只根据order_status 这一个后面的字段来减少扫描数
Q7：SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND expire_time = ‘2021-03-22 18:35:12’;
- 可以用上索引减少扫描数，但是只有这一个字段insert_time 起作用，后面一个字段expire_time 不能减少扫描数
Q8：SELECT * FROM order_exp WHERE insert_time < ‘2021-03-22 18:34:57’ AND order_status = 1;
- 两个字段都能用上，都可以减少扫描数

简单了解MyISAM中的索引

和innodb索引的区别

索引和数据是分开存放的
myisam中的索引记录也没有分页，就是一条一条的存放的，查找时，同时主键索引找到对应数据在磁盘中的行号，然后再去找到对应的数据，这就意味着myisam中所有的查询都要回表

创建、删除索引和索引的代价

索引相关的基础命令

查看索引
SHOW INDEX FROM table_name\G

创建修改索引
CREATE TALBE 表名 (
各种列的信息 ··· ,
[KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)
CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 

删除索引
DROP INDEX [indexName] ON mytable;
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

索引的代价

空间上的代价
- 建立索引需要额外的物理空间
时间上的代价
- 对索引列进行修改时（增删改），相关的B+树就要进行变动，B+树里面的数据要从页上移动，会发生页的分裂、回收、合并可能都要做

高性能的索引创建策略

1.索引列的类型尽量小

数据类型越小，在查询时进行的比较操作越快，这是站在cpu运行角度考虑的
- 和创建表是一样的，比如对于整型数据，越小越好
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

2.索引选择性/离散性和前缀索引

索引选择性/离散性

不重复的值和数据表总记录数的比值
1~（1/N），N是数据表总记录数

为什么离散型高的列作为索引好？

扫描区间小

例子

姓名年龄性别区号
Peter 18 1 0731
Jack 20 1 0734
Allen 21 1 0731
Lisa 22 0 0746
Sam 23 1 0739
King 18 1 0745
James 20 1 0744
Mark 21 1 0731
在这个表中离散度最高的是姓名，没有重复，假设用性别作为索引，用某一个条件扫描，扫描出了50%的数据，根本没有发挥太大的作用

姓名	年龄	性别	区号
Peter	18	1	0731
Jack	20	1	0734
Allen	21	1	0731
Lisa	22	0	0746
Sam	23	1	0739
King	18	1	0745
James	20	1	0744
Mark	21	1	0731

怎么判断离散型

select count(distinct order_no)/count(*) cnt from order_exp;

前缀索引

有时候需要索引很长的字符列，通常可以索引开始的部分字符(前缀索引)，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。
对于很长的字符串，除了使用前缀索引，还可以模拟哈希索引，需要独立维护一个hash(复杂字符串)的字段，而且受哈希算法影响很大，不好的哈希算法重复值会很多，同时不支持范围查找，所以如果一定要使用很长的字符串作为索引，相比而言，前缀索引会更好

怎么确定前缀索引

第一步：select count(*) as cnt,order_note from order_exp group by order_note order by cnt desc limit 20;
- 根据这个命令找出复杂字符串中数量最多的前20个
第二步：SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT() AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT()AS sel4, COUNT(DISTINCT LEFT(order_note,5))/COUNT() AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT() As sel6,COUNT(DISTINCT LEFT(order_note, 7))/COUNT() As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT() As sel8,COUNT(DISTINCT LEFT(order_note, 9))/COUNT() As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT() As sel10,COUNT(DISTINCT LEFT(order_note, 11))/COUNT() As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT() As sel12,COUNT(DISTINCT LEFT(order_note, 13))/COUNT() As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT() As sel14,COUNT(DISTINCT LEFT(order_note, 15))/COUNT() As sel15,
COUNT(DISTINCT order_note)/COUNT() As total FROM order_exp;
- sel3 sel4 sel5 sel6 sel7 sel8 sel9 sel10 sel11 sel12 sel13 sel14 sel15 total
  0.0008 0.0008 0.0008 0.0015 0.0107 0.0844 0.1628 0.3455 0.4723 0.6834 0.8564 0.9197 0.9592 0.9676
- 从sel14开始，基本和total差别就不大了，具体取多大需要根据业务判断
第三步：ALTER TABLE order_exp ADD KEY (order_note(14));
- 使用前14个字段作为索引

sel3	sel4	sel5	sel6	sel7	sel8	sel9	sel10	sel11	sel12	sel13	sel14	sel15	total
0.0008	0.0008	0.0008	0.0015	0.0107	0.0844	0.1628	0.3455	0.4723	0.6834	0.8564	0.9197	0.9592	0.9676

后缀索引怎么处理？

如果要把某个域名的邮箱地址全部找出来，比如@163.com，此时如果使用"%@163.com"是无法用到索引的
但是mysql不支持反向索引，所以在业务层面做存储，将字段反转存储，同时建立前缀索引

3.只为用于搜索、排序或分组的列创建索引

只为出现在WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引；又或者为出现在ORDER BY或GROUP BY子句中的列创建索引。
- SELECT * FROM order_exp ORDER BY insert_time, order_status,expire_time;
  - 联合索引如果按照insert_time, order_status,expire_time;顺序建立，此时order by就不需要排序了，因为B+树里面已经排序好了
- SELECT insert_time, order_status,expire_time,count(*) FROM order_exp GROUP BY insert_time, order_status,expire_time;
  - GROUP BY insert_time, order_status,expire_time的意思就是先按insert_time分组，每一个insert_time再按order_status分组，每一个order_status再按expire_time分组，如果按照这个字段顺序建立联合索引，也是天然就是分好组的

4.多列索引

怎么选择合适的索引列顺序？

1、经验法则:将选择性最高的列放到索引最前列。
- 通过选择性高的列可以尽可能的缩小查询范围
- 但是不是绝对的，因为还跟具体的查询条件有关系，也就是跟业务相关，比如某些字段的查询频率很高
2、需要根据那些运行频率最高的查询来调整索引列的顺序
3、在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求
- 可以建立多个相同字段但是顺序不同的联合索引

5.设计三星索引

三星索引概念是在《Rrelational Database Index Design and the optimizers》一书中提出来的。原文如下：
The index earns one star if it places relevant rows adjacent to each other,
a second star if its rows are sorted in the order the query needs,
and a final star if it contains all the columns needed for the query.
- 索引将相关的记录放到一起则获得一星；
  - mysql查询具体的数据时，发现存储的数据在物理是相邻的，就可以一次io把数据读取到
- 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星）；
- 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）；

这三颗星哪颗最重要

第三颗星最重要，回表会导致很多的随机io读
- mysql怎么处理回表的？在二级索引读到一条数据，就去主键索引回表一次，有多少记录就回表多少次，这里是典型的随机io
第一颗星和第二颗星的作用差不多，一星略优于二星

达成三星索引

create table customer(
cno int,
lname varchar(10),
fname varchar(10),
sex int,
weight int,
city varchar(10));
create index idx_cust on customer(city,lname,fname,cno);

select cno,fname from customer where lname =’xx’ and city =’yy’ order by fname;
- 此时是满足三星索引的， lname =’xx’ and city =’yy’ 是可以利用联合索引idx_cust的，这两个等于条件可以把值收缩的很窄，此时第一颗星符合
- 在前两个字段等于的情况下，B+树本身就是按照fname排序的，所以第二颗星符合
- cno,fname都是索引中的字段，所以符合第三颗星，不需要回表

达不成三星索引

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `c_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

select user_name,sex,age from test where user_name like ‘test%’ and sex =1 ORDER BY age
假设建立(user_name,sex,age)的联合索引
- 第三颗星满足
- 第一颗星满足，通过user_name like ‘test%’ and sex =1 两个条件是可以把搜索范围缩到很小的
- 第二颗星不满足，因为user_name like ‘test%’ and sex =1 两个条件扫描出来的数据不一定是按age排序好的，因为user_name like ‘test%’ 是一个范围查询查出一堆数据，在这一堆数据里假设有test1和test2，而test1下面sex=1的age对应是19和91，而test2下面sex=1的age对应的是10和18，所以得到的age排列是19,91,10,18，此时只能保证在扫描出user_name 范围内的某一条数据内部的age是有序的，而不能保证所有数据都是有序的
假设建立(sex,age,user_name)的联合索引
- 第三颗星和第二颗星满足
- 第一颗星不满足，虽然是等于条件，但是还是会扫描出一半的数据来
所以是很难设计三星索引的，尽量满足两个，根据业务决定选择哪一个，优先保证第三颗星，第一颗星和第二颗星具体选择哪个，根据业务确定

6.主键是很少改变的列

行是按照聚集索引物理排序的，如果主键频繁改变，物理顺序会改变，性能会急剧降低。

7.冗余和重复索引

CREATE TABLE test (
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
mysql是允许在同一个列重复建立索引的，比如上面的建表数据建立三个id的索引