MySQL中Innodb的索引

分手后的思念是犯贱 2023-02-16 04:19 5阅读 0赞

> **       如果想在一本书中找到某个特定主题,一般会先看输的"索引",找到对应的页码。在MySQL中,存储引擎用类似的方法使用索引,其先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。**

# ** 一.索引** #

> **索引是存储引擎用于快速找到记录的一种数据结构，索引往往以索引文件的形式存储的磁盘上。**

###  **索引总结下来有如下几个优点：** ###

> **1.索引大大减少了服务器需要扫描的数据量，提高查询性能。  
> 2.素引可以帮助服务器避免排序和临时表。  
> 3.索引可以将随机IO变为顺序IO  
> 4.建立主键索引或者唯一索引，可以保证数据库表中某列数据的唯一性  
> 5.可以加速表与表的连接  
> 6.可以显著的减少查询中分组和排序的时间**

### **使用索引的缺点：** ###

> **1.创建索引和维护索引会耗费时间,随着数据量的增加而增加。  
> 2.索引文件会占用物理空间。  
> 3.当对表进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。**

### **创建索引：** ###

CREATE index <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

> ** index：****可以替换为 unique index，代表唯一索引。**  
> ** 索引名：****索引名称。**  
> ** 表名：****要创建索引的表名。**  
> ** 列名：****要创建索引的列名。**  
> ** 长度：****可选项。指定使用列的前 length 个字符来创建索引。**  
> ** ASC|DESC：****可选项。指定索引按照升序或降序来排列** 
> 
> **索引命名规则：****主键的名称以 pk\_ 开头，唯一键以 uk\_ 或 uq\_ 开头，普通索引以 idx\_ 开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。**  
>

**例如：**

create index idx_name on student(name(10) desc);

**也可以使用类似如下语句定义主键：**

**比如定义主键索引： **

alter table student add primary key(name);

# **二.Innodb的索引** #

**InnoDB 存储引擎支持的索引数据类型只有 BTREE 类型，我们这篇文章也主要讨论 Innodb下的索引，ps：在 navicat 可视化软件中建立索引的时候，虽然是在 InnoDB 存储引擎下，但是索引类型中有个 HASH 类型，然而即使选了HASH,保存后也会自动变成 BTREE。**

**为了更好地说明索引情况，我们建个测试表**

CREATE TABLE `student` (
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL COMMENT '姓名',
      `score` double(11,2) DEFAULT NULL COMMENT '分数',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '小红', '85.00');
    INSERT INTO `student` VALUES ('2', '小明', '95.00');
    INSERT INTO `student` VALUES ('3', '小扬', '90.00');
    INSERT INTO `student` VALUES ('4', '小黄', '82.00');
    INSERT INTO `student` VALUES ('5', '小青', '99.00');
    INSERT INTO `student` VALUES ('6', '小吴', '87.50');
    INSERT INTO `student` VALUES ('7', '小张', '92.50');
    INSERT INTO `student` VALUES ('8', '小胡', '85.00');
    INSERT INTO `student` VALUES ('9', '小王', '90.00');
    INSERT INTO `student` VALUES ('10', '小狗', '60.00');
    INSERT INTO `student` VALUES ('11', '小吴', '56.00');
    INSERT INTO `student` VALUES ('12', '吴道子', '60.00');

## **1. 聚簇索引：** ##

> **InnoDB的数据文件本身就是索引文件，并使用** **B+Tree** **作为索引结构，****叶子节点保存了完整的数据记录****。这个索引我们称之为****聚簇索引****，这并不是一种单独的索引类型，而是一种数据存储方式。****InnoDB将主键组织到一颗 B+ Tree 中，在这颗 B+ Tree 的叶子节点上存储着整行记录。****所以也可以说 InnoDB 表是基于聚簇索引建立的。**

### **聚簇索引优点：** ###

> **1.数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+ Tree 中，因此从聚簇索引中获取数据比非聚簇索引更快  
> 2.聚簇索引对于主键的排序查找和范围查找速度非常快**

### **聚簇索引创建规则：** ###

> **1.当定义了主键后，InnoDB会利用主键来生成聚簇索引。  
> 2.如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引。  
> 3.如果以上都没有，InnoDB会隐式的创建一个自增的列(rowid)来作为聚簇索引。**

## **2. 非聚簇索引：** ##

> **除了主键索引之外的其他索引称为****辅助索引****（也可称为****非聚簇索引****或****二级索引****），辅助索引就是一个为了****寻找主键索引的二级索引****，辅助索引****由独立的 B+ Tree 来组织****，与聚簇索引的区别在于****辅助索引的叶子节点中存放的是这个索引列和与这条数据对应的用来回表的主键值****。辅助索引的存在不影响数据在聚簇索引中的组织，一张表可以有多个辅助索引。辅助索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。**

**回表：**

> **在使用辅助索引时，因为辅助索引中只存储了部分数据，如果在这个索引的叶子节点中无法查找到全部想要的字段，则会根据辅助索引叶子节点中的主键信息，去聚簇索引中查找相应的整行数据查找所有想要的列，这种在二级索引中不能找到全部列的现象称为'非索引覆盖'，需要两次B+树查询，即为回表。但是回表是可避免的，一种是直接采用主键索引，第二种是****覆盖索引****，下文会介绍。**

## **3. 我们举下例子:** ##

**对表 student 进行 id 搜索**

EXPLAIN SELECT a.id,a.`name`,a.score FROM student AS a WHERE a.id = 2

**结果如下：**

![20200608030321199.png][]

** 可以看到实际用到的索引键就是PRIMARY（直接就是聚簇索引）**

**然后按照B+树的检索算法在聚簇索引中找到对应id的叶子节点，然后获得整行数据。**

**这时我们对 name 字段建立一个普通索引：**

create index idx_name on student(name);

**然后对name作为条件进行搜索，那样会发生什么呢？**

EXPLAIN SELECT a.id,a.`name`,a.score FROM student AS a WHERE a.name = '小红'

**结果如下：**

![20200608030746230.png][]

> ** 可以看到实际用到的索引键是idx\_name，则按照 Innodb 的规则，这时会发生两个步骤：  
> 1.首先是在 idx\_name 的 B+ Tree 中检索到对应的 name值 小红，然后在叶子节点中获取对应的主键id的值。  
> 2.然后通过该id再在主键索引的 B+ Tree 中 再进行一次检索，然后在叶子节点中即可获取整行数据。**

# **三.Innodb的索引类型** #

## **1.普通索引：** ##

> **最基本的索引，是我们经常使用到的索引。**

**建立语句如下：**

create index idx_name on student(name);

**测试一下：**

![20200609020634355.png][]

##  ##

## **2.唯一索引** ##

> **与普通索引类似，但是唯一索引的列值必须唯一，不过允许为null**

**建立语句如下：**

create unique index uk_name on student(name);

**测试一下：**

![2020060902093726.png][]

##  ##

## **3.主键索引** ##

> **特殊的唯一索引，不允许有 null 值**

**建立语句如下：**

alter table student add primary key(name);

**测试一下：**

![20200609021155770.png][]

##  ##

## **4.联合索引** ##

> **也叫复合索引，索引可以简单如一个列 (a)，也可以是多个列 (a,b,c)，即多列值组成一个索引。**
> 
> **注意事项：联合索引遵循最左匹配原则，例如建立联合索引(a,b,c),那么能够生效的索引组合为 a，(a,b),(a,b,c)**

**我们再建个 book 表测试一下：**

CREATE TABLE `book` (
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL COMMENT '书名',
      `money` double(11,2) DEFAULT NULL COMMENT '售价',
      `public_time` datetime DEFAULT NULL COMMENT '出版日期',
      `writer` varchar(255) DEFAULT NULL COMMENT '作者',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `book` VALUES ('1', '水浒传', '25.20', '2020-06-02 22:05:01', '施耐庵');
    INSERT INTO `book` VALUES ('2', '西游记', '26.50', '2020-06-25 22:05:29', '吴承恩');
    INSERT INTO `book` VALUES ('3', '三国演义', '40.50', '2020-06-30 22:05:48', '罗贯中');
    INSERT INTO `book` VALUES ('4', '红楼梦', '75.20', '2020-04-14 22:06:10', '曹雪芹');
    INSERT INTO `book` VALUES ('5', '哈利波特', '35.60', '2020-06-29 22:06:42', 'JK罗琳');
    INSERT INTO `book` VALUES ('6', '心理罪', '48.60', '2020-03-10 22:07:02', '雷米');
    INSERT INTO `book` VALUES ('7', '灌篮高手', '45.50', '2020-06-18 22:07:32', '井上雄彦');
    INSERT INTO `book` VALUES ('8', '红与黑', '12.00', '2020-06-26 22:07:51', '谁？');
    INSERT INTO `book` VALUES ('9', '海贼王', '25.60', '2020-06-10 22:08:11', '尾田荣一郎');
    INSERT INTO `book` VALUES ('10', '天龙八部', '26.90', '2020-06-08 22:08:49', '金庸');

**为 name，money，public\_time三个字段建立联合索引：**

create index idx_name_money_public_time on book(name,money,public_time);

**开始测试：**

### **4.1 按顺序三个字段都作为查询条件** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE a.`name` = "红楼梦" and a.money = '75.2' and a.public_time = '2020-04-14 22:06:10'

![20200609021705192.png][]

**可以看到三个字段都走了索引**

### **4.2 按顺序只要前两个字段作为查询条件** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE a.`name` = "红楼梦" and a.money = '75.2'

![20200609021856423.png][]

**可以看到两个字段都走了索引**

### **4.3 只有第一个字段作为查询条件** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE a.`name` = "红楼梦"

![20200609022111972.png][]

**可以看到第一个字段走了索引**

### **4.4 我们继续用第一个字段 name 和第三个字段 public\_time 做下测试** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE a.`name` = "红楼梦" and a.public_time = '2020-04-14 22:06:10'

![20200609022223111.png][]

**可以看到只有一个字段走了索引**

### **4.5 用第二个字段 money 和第三个字段 public\_time 做下测试** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE a.money = '75.2' and a.public_time = '2020-04-14 22:06:10'

![2020060902234580.png][]

**索引失效，转为全表扫描**

### **4.6 我们再打乱一下 三个 字段的查询顺序** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE  a.money = '75.2' and a.public_time = '2020-04-14 22:06:10' and a.`name` = "红楼梦"

![20200609022505791.png][]

**可以看到，即使打乱顺序，联合索引的三个字段仍然都起了作用（优化器会帮我们优化顺序）**

### **说道联合索引，这里我们在说一下索引下推的概念：** ###

**MySQL 5.6引入了索引下推优化，索简称ICP，默认开启，可以使用以下语句关闭：**

SET optimizer_switch = ‘index_condition_pushdown=off’;

**我们举个例子：**

**对 book 表中的 name 字段和 money 字段建立联合索引**

create index idx_name_money on book(name,money);

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book AS a WHERE a.`name` like "红%" AND a.money = '75.2'

![20200609044036372.png][]

** Using index condition，表示已经使用了索引下推。**

> **如果mysql没有使用索引下推，是这样执行查询的：**
> 
> **1.先对name进行like查询，此时数据中有两个本书符合‘红’开头分别是 “红楼梦” 和 “红与黑” ，那么 mysql 会立刻将符合条件的两条数据 id 依次进行回表，查到全部数据，每条数据回表一次，一共回表两次  
> 2.然后再筛选出价格是 75.2 的数据。**

> **如果使用索引下推，则会发生：**
> 
> **先对name进行like查询，此时数据中有两个本书符合‘红’开头分别是 “红楼梦” 和 “红与黑” ，然后立刻再在该索引中过滤出 money 等于 75.2 的数据，所以得到了唯一的数据，再将该数据回表一次即可**

**索引下推在非主键索引（毕竟聚簇索引也没必要回表了）上的优化，可以有效减少回表的次数，大大提升了查询的效率**

## **5.覆盖索引** ##

> **索引覆盖是联合索引查询时的最优情况，它包括在查询里的Select、Join和Where子句用到的所有列，由于在覆盖索引中就能得到所有数据，所以不用再到聚簇索引中进行回表查询了。**

**我们利用对book建立的联合索引测试下覆盖索引，只搜索联合索引中的三个字段**：**：**

EXPLAIN SELECT a.`name`,a.money,a.public_time FROM book AS a WHERE a.`name` = "红楼梦"

![20200609025957249.png][]

**Extra中 出现 Using index 即代表使用了覆盖索引**

###  ###

## **6.索引合并** ##

> **这个概念容易跟组合索引弄混，组合索引的意思是多个单列组成一个索引，索引合并是多个单列索引。MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描，然后对结果进行合并，例如我们在 where 条件中可能涉及多个字段，它们之间用** **AND 或者 OR** **连接，那么此时就有可能会使用到** **index merge** **技术。合并方式分为三种：对 and 结果进行 union 求并集；对 or 结果进行 intersect 求交集；对 and 和 or 组合语句求结果。**

> **该特性存在不稳定性，例如：**  
> **1.当各个索引间的选择性比较高时，进行条件查询返回的数据集比较小，对数据求交集的成本也比较低，整个语句查询效率就高。  
> 2.当各个索引间的选择性比较差，查询返回的数据集很大时，则求交集的成本也高。**

**所以最好使用覆盖索引，并且当涉及到 or 条件查询时，最好采用 union all 语句。**

# **四.利用索引排序** #

## 1.覆盖索引的排序 ##

**我们重新建立 一个 book 表，然后看看索引时怎么排序的,我们建个表，并给除了id之外的四个字段共同设置一个联合索引**

CREATE TABLE `book` (
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL COMMENT '书名',
      `money` double(11,2) DEFAULT NULL COMMENT '售价',
      `public_time` datetime DEFAULT NULL COMMENT '出版日期',
      `writer` varchar(255) DEFAULT NULL COMMENT '作者',
      `country` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_combine` (`public_time`,`money`,`name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `book` VALUES ('1', '水浒传', '25.20', '2020-03-10 22:07:02', '施耐庵', '中国');
    INSERT INTO `book` VALUES ('2', '西游记', '26.50', '2020-06-25 22:05:29', '吴承恩', '中国');
    INSERT INTO `book` VALUES ('3', '三国演义', '40.50', '2020-06-30 22:05:48', '罗贯中', '中国');
    INSERT INTO `book` VALUES ('4', '红楼梦', '75.20', '2020-04-14 22:06:10', '曹雪芹', '中国');
    INSERT INTO `book` VALUES ('5', '哈利波特', '35.60', '2020-06-29 22:06:42', 'JK罗琳', '英国');
    INSERT INTO `book` VALUES ('6', '心理罪', '36.90', '2020-03-10 22:07:02', '雷米', '中国');
    INSERT INTO `book` VALUES ('7', '灌篮高手', '45.50', '2020-06-18 22:07:32', '井上雄彦', '日本');
    INSERT INTO `book` VALUES ('8', '龙珠', '12.00', '2020-06-26 22:07:51', '鸟山明', '日本');
    INSERT INTO `book` VALUES ('9', '海贼王', '25.60', '2020-06-10 22:08:11', '尾田荣一郎', '日本');
    INSERT INTO `book` VALUES ('10', '天龙八部', '26.90', '2020-06-08 22:08:49', '金庸', '中国');
    INSERT INTO `book` VALUES ('11', '红与黑', '30.36', '2020-03-10 22:07:02', '谁？', '法国');

**我们查询一下看看：**

SELECT a.`name`,a.money,a.public_time FROM book AS a

**结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70][]

**再 explan 一下：**

![20200614033829924.png][]

**这就是 利用索引排序的结果，没有 order by，但是却自动排好了序，这是为什么呢？因为我们建的是覆盖索引，那如果不是覆盖索引的话，还能排序吗？**

## 2.非覆盖索引的排序 ##

**我们多搜索两个字段**

SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book a

**结果： **

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 1][]

**完全是按id排的序**

**再 explan 一下：**

![20200614034843808.png][]

**连索引都没用,如果我们 手动 order by 的话会发生什么呢?**

SELECT a.id,a.`name`,a.money,a.public_time,a.writer FROM book a ORDER BY a.public_time

**再 explan 一下：**

![20200614035043885.png][]

**用的文件排序，真是失败！！！**

## **3.覆盖索引中的排序规则和顺序** ##

**由上文所知,覆盖索引中，排序规则相当于 order by (建立联合索引时的顺序，即最左前缀匹配原则)，我们测试下**

### **3.1 用 a 作为搜索条件，用 b 来排序，看看索引排序是否生效** ###

EXPLAIN SELECT a.`name`,a.money,a.public_time FROM book a WHERE a.public_time = '2020-03-10 22:07:02' ORDER BY a.money

**再 explan 一下：**

![20200614042125630.png][]

**索引排序生效！**

### **3.2** **用 a 作为搜索条件，用 c 来排序，看看索引排序是否生效** ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time FROM book a WHERE a.public_time = '2020-03-10 22:07:02' ORDER BY a.name

**再 explan 一下：**

![20200614042745920.png][]

**用的是文件排序,没有走索引！**

### 3.3 如果 a 是范围查询，用 b 来进行排序，看看索引排序是否生效 ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time FROM book a WHERE a.public_time >= '2020-03-10 22:07:02' ORDER BY a.money

**再 explan 一下：**

![20200614043537814.png][]

**用的是文件排序,没有走索引！！！**

### 3.4 那么如果用 b 做过滤，用 a 排序呢？ ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time FROM book a WHERE a.money <= '2020-03-10 22:07:02' ORDER BY a.public_time

**再 explan 一下：**

![20200614045126917.png][]

**可以利用索引排序了！！！**

### 3.4 那么如果用 b,c 做过滤，并且 b是范围查询，再用 a,b,c 排序呢？ ###

EXPLAIN SELECT a.id,a.`name`,a.money,a.public_time FROM book a WHERE a.money < 40 and a.name like '红%'  ORDER BY a.public_time,a.money

**再 explan 一下：**

![20200614045432670.png][]

**同样可以利用索引排序了！！！**

### 4.连表查询时覆盖索引中的排序规则和顺序 ###

**建立一个 连接表：**

CREATE TABLE `book_word` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `word_count` int(11) DEFAULT NULL,
      `dynasty` varchar(255) DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `idx_book_word_combine` (`name`,`word_count`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `book_word` VALUES ('1', '西游记', '10000', '明代');
    INSERT INTO `book_word` VALUES ('2', '水浒传', '12225', '元末明初');
    INSERT INTO `book_word` VALUES ('3', '三国演义', '45555', '元末明初');
    INSERT INTO `book_word` VALUES ('4', '红楼梦', '585545', '清代');
    INSERT INTO `book_word` VALUES ('5', '哈利波特', '545454', '当代');
    INSERT INTO `book_word` VALUES ('6', '心理罪', '558445', '当代');
    INSERT INTO `book_word` VALUES ('7', '灌篮高手', '556987', '当代');
    INSERT INTO `book_word` VALUES ('8', '海贼王', '112354', '当代');
    INSERT INTO `book_word` VALUES ('9', '龙珠', '447888', '当代');
    INSERT INTO `book_word` VALUES ('10', '天龙八部', '126478', '当代');
    INSERT INTO `book_word` VALUES ('11', '红与黑', '85265', '现代');

**执行下sql语句：**

SELECT
    	a.`name`,
    	a.money,
    	a.public_time,
    	b.word_count
    FROM
    	book AS a
    LEFT JOIN book_word AS b ON a.`name` = b.`name`
    WHERE
    	a.public_time BETWEEN '2020-03-10 22:07:02'
    AND '2020-06-10 22:07:02'
    AND b.dynasty IN ('当代', '现代', '清代')

**结果：**

![20200614054529557.png][]

**可以看到，仍然是按照 public\_time 和 money 进行排好序的！！！**

**再 explan 一下：**

![20200614054719900.png][]

**即利用了索引的查询优化，又利用了排序！！！**

> **那么总结一下: 覆盖索引(符合索引)的排序规则是:**
> 
> **1.要么从 where 开始 到 order 结束向右的顺序符合最左匹配原则,并且****一直向右匹配直到遇到范围查询(>、<、 between 、 like)就停止匹配****，比如 a=1 and b=2 and c>3 and d=4 如果建立(a,b,c,d)顺序的索引，则到 d=4 时无法用索引的。**
> 
> **2.要么不管 where 如何定义的,order by 的字段要符合最左匹配原则**
> 
> **3.****因此****建立联合索引时的字段顺序是很重要****的。不仅涉及到查询时是否用到索引，排序时也会涉及到。**

**注：这里只是单独演示了 覆盖索引 中的排序规则，不代表采用的查询方式就是最好的,比如有些查询中出现的 type 类型是index 甚至是all，这完全没有充分利用索引，我们不仅要考虑用索引排序，也要考虑到用索引查询。**

# **四.如何查看我们建立的索引是否生效了** #

### **1. EXPLAIN 关键字** ###

**在查询语句前加上 EXPLAIN  即可，上面已经做过了很多测试，我们说一下各个字段的具体含义：**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 2][]**

**如果我们在 EXPLAIN 关键字后加上 ****EXTENDED****，则会多查询出一列名为** **filtered** **的关键字：**

![20200609135631621.png][]

**如果我们在 EXPLAIN 关键字后加上 ****PARTITIONS****，则会多查询出一列名为** **partitions ****的关键字：**

![20200609135852345.png][]

# 五.索引建立规则 #

## **1.建立索引规则** ##

> **1.表的主键，外键必须有索引，InnoDB表建议主键为 id int/bigint auto\_increment,且主键值禁止被更新。**
> 
> **2.超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。**
> 
> **3.尽量使用数据量少的索引，或者采用前缀索引，比如在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。**
> 
> **4.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。**
> 
> **5.建组合索引的时候，区分度最高的在最左边。例如 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要对a字段建立索引即可。**
> 
> **6.组合索引和多个单列索引的检索查询效果不同，推荐利用覆盖索引来进行查询操作，避免回表。**
> 
> **7.防止因字段类型不同造成的隐式转换，导致索引失效。**
> 
> **8.尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可，不要存在冗余的索引。**
> 
> **9.建议单个表上的索引个数不能超过7个，索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。**
> 
> **10.数据量超过300的表应该有索引，数据比较少的表不需要建索引。**
> 
> **11.频繁进行数据操作的表，不要建立太多的索引。**
> 
> **12.为经常需要排序、分组和联合操作的字段建立索引,注意索引的有序性,order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file\_sort 的情况，影响查询性能，例如利用组合索引(a,b,c) 可以优化 where a=? and b=? order by c，但是当出现范围查询时，索引的有序性将无法利用，例如 WHERE a>10 ORDER BY b; 索引（a,b) 无法排序。**

## **2.查询过程中导致索引失效的行为** ##

> **有的时候我们明明建立了索引，但是索引却没有生效，除了我们建立索引和查询数据时没有遵循规则外，还有Mysql自带的查询优化器：在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。** 
> 
> **优化过程大致如下： **  
> **1.根据搜索条件，找出所有可能使用的索引。   
> 2.计算全表扫描的代价。  
> 3.计算使用不同索引执行查询的代价。  
> 4.对比各种执行方案的代价，找出成本最低的那一个。**

**一些常见的导致索引失效的行为:**

> **1.where条件里等号左右字段类型必须一致，否则无法利用索引。**
> 
> **2.生产数据库中强烈不推荐大表上发生全表扫描，但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%，否则不会利用索引。**
> 
> **3.WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引。**
> 
> **4.索引列不要使用函数或表达式，否则无法利用索引。如 where id+1 = 2。**
> 
> **5.减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。如 where a=1 or b=2 优化为 where a=1… union …where b=2, key(a),key(b) 。(or 会影响索引吗?测试了几遍,还没找到规律)**
> 
> **6.表中所有字段必须都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT 值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。(null 会影响索引吗?测试了几遍,还没找到规律)**

[20200608030321199.png]: https://img-blog.csdnimg.cn/20200608030321199.png
[20200608030746230.png]: https://img-blog.csdnimg.cn/20200608030746230.png
[20200609020634355.png]: https://img-blog.csdnimg.cn/20200609020634355.png
[2020060902093726.png]: https://img-blog.csdnimg.cn/2020060902093726.png
[20200609021155770.png]: https://img-blog.csdnimg.cn/20200609021155770.png
[20200609021705192.png]: https://img-blog.csdnimg.cn/20200609021705192.png
[20200609021856423.png]: https://img-blog.csdnimg.cn/20200609021856423.png
[20200609022111972.png]: https://img-blog.csdnimg.cn/20200609022111972.png
[20200609022223111.png]: https://img-blog.csdnimg.cn/20200609022223111.png
[2020060902234580.png]: https://img-blog.csdnimg.cn/2020060902234580.png
[20200609022505791.png]: https://img-blog.csdnimg.cn/20200609022505791.png
[20200609044036372.png]: https://img-blog.csdnimg.cn/20200609044036372.png
[20200609025957249.png]: https://img-blog.csdnimg.cn/20200609025957249.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020061403293875.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg=,size_16,color_FFFFFF,t_70
[20200614033829924.png]: https://img-blog.csdnimg.cn/20200614033829924.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200614034622534.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg=,size_16,color_FFFFFF,t_70
[20200614034843808.png]: https://img-blog.csdnimg.cn/20200614034843808.png
[20200614035043885.png]: https://img-blog.csdnimg.cn/20200614035043885.png
[20200614042125630.png]: https://img-blog.csdnimg.cn/20200614042125630.png
[20200614042745920.png]: https://img-blog.csdnimg.cn/20200614042745920.png
[20200614043537814.png]: https://img-blog.csdnimg.cn/20200614043537814.png
[20200614045126917.png]: https://img-blog.csdnimg.cn/20200614045126917.png
[20200614045432670.png]: https://img-blog.csdnimg.cn/20200614045432670.png
[20200614054529557.png]: https://img-blog.csdnimg.cn/20200614054529557.png
[20200614054719900.png]: https://img-blog.csdnimg.cn/20200614054719900.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200611012100337.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg=,size_16,color_FFFFFF,t_70
[20200609135631621.png]: https://img-blog.csdnimg.cn/20200609135631621.png
[20200609135852345.png]: https://img-blog.csdnimg.cn/20200609135852345.png