MySQL数据库架构--数据库索引优化

发表于 | 分类于 | | 阅读次数: |
字数统计: 5,058 字 | 阅读时长 ≈ 22 分钟

索引的作用是告诉存储引擎快速找到我们需要的数据

两个极端：除了主键没有任何索引，给每一个列都建立一个索引。太多太少的索引都会对数据库的性能带来不好的影响。只有在正确的列上建立正确的索引，才能增强数据库的查询能力。

索引是在mysql存储引擎层实现的，而不是在mysql服务器层实现的，不同的存储引擎的索引方式是不同的。

Btree索引和Hash索引

B-tree索引

B-tree索引是比较常见的，通常所说的索引就是B-tree索引

B-tree以B+树的结构存储数据

image-20190128164532382每个叶子到根部的距离都是 相同的，并且所有的记录节点都是按照键值的大小，在同一层上顺序排列的，并且各个叶子节点是由指针来链接的。

B-tree 索引能够加快数据的查询速度，通常索引的大小远小于表中数据的大小，使用B-tree索引，存储引擎就不用全表扫描来获取需要的数据，取而代之的是从根节点开始搜索，在索引的根节点中存放了指向下层子节点的指针，存储引擎根据这些指针向下层进行查找，通过比较节点页的值通过比较节点叶的值和要查找的值呢，就可以得到合适的指针进入下层的子节点，而这些指针是定义了子节点中值的上线和下线。在innodb中叶子节点指向的是主键，在myisam中叶子节点指向的是数据物理地址。

B-tree的叶子节点存储着索引关键字的值。

另外B-tree索引呢 对索引是顺序存储的，所以适合范围查找，是靠键值来存储

B-tree索引在什么情况下可以被使用到

  • 全值匹配的查询

    order_sn = '0987656789'

  • 匹配最左前缀的查询

    假设我们建立了一个由order_sn和order_date组成的联合索引，如果还是进行上面的查询，是能够用到联合索引的。也就是说，只要联合索引的第一列符合查询条件则这个联合索引是能够被用到的。如果只是查询order_date则联合索引是不会被用到的。

  • 匹配列前缀查询

    order_sn like '098%'

    匹配列前缀是指匹配某一列的开头部分（这个查询也会用到上面说的联合查询）

  • 匹配范围值的查询

    B-tree索引更适合的是范围查找

    order_sn > '123' and order_sn < '321'

  • 精确匹配左前列并范围匹配另一列

    还是上面的联合索引，可以是精确查找order_sn然后是范围查找order_date

  • 只访问索引的查询

    查询只访问索引，不访问数据行

  • 排序类

B-tree索引的使用限制

  • 如果不是按照索引最左列开始查找，则无法使用索引

    还是上面说的联合索引，当我们只查询order_date的时候则是无法使用联合索引的

  • 使用索引时不能跳过索引中的列

    这个同样是左边列不能少，假设我们对order_date、order_name、order_phone这三列建了联合索引，假设我们只搜索order_date、order_phone是不能使用这个联合索引的。只能通过order_date进行过滤查询。

  • Not in 和 < > 操作无法使用索引

  • 如果查询中有某个列的范围查询，则右边所有列都无法使用索引

Hash索引

Hash索引的特点：

  • hash索引是基于hash表实现的，hash索引只有查询条件精确匹配hash索引中的所有列时，才能够使用到hash索引。也就是说hash索引只能用到等值查询中。如果我们想使用范围查询和模糊查询就不能使用hash索引了。

  • 对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码，hash表还保存了每个hash码对应数据的指针

Hash索引的限制:

  • Hash索引必须进行二次查找

    Hash索引本身只存储了Hash码，使用Hash索引查询必须进行两次查询。因为Hash索引只是键值和Hash码以及对应行的指针，索引并没有保存值。因此必选先找到行，然后再读取值。

  • Hash索引无法用于排序（保存的是hash值）

  • hash索引不支持部分索引查找也不支持范围查找

  • Hash索引中Hash码的计算可能存在Hash冲突（不建议在性别字段使用）

为什么需要索引优化？

在学习具体的索引优化前我们必须明确为什么需要索引优化

使用索引能够使我们快速定位到数据，但是这不是索引的唯一作用。

索引大大减少了存储引擎需要扫描的数据量，索引文件大小远小于数据文件大小，innodb 默认一页是16k，一页可以存储更多

索引可以帮助我们进行排序以避免使用临时表

索引可以把随机I/O变为顺序I/O，更加好的发挥磁盘性能

索引是不是越多越好？

索引会带来一定的损耗：

  • 索引会增加写操作的成本

    更新数据也要更新维护的索引

  • 太多的索引会增加查询优化器的选择时间（当一个查询有多个索引的时候，就会增加查询优化器分析时间），只有适当的表建立适当的索引，才是正确的做法。

安装实例数据库
1
2
3
4
5
6
7
8
9
10
[root@hongshaorou ~]# wget http://downloads.mysql.com/docs/sakila-db.tar.gz

[root@hongshaorou ~]# tar -zxvf sakila-db.tar.gz
sakila-db/
sakila-db/sakila-data.sql
sakila-db/sakila-schema.sql
sakila-db/sakila.mwb
[root@hongshaorou ~]# mysql -uroot -p < sakila-db/sakila-schema.sql

[root@hongshaorou ~]# mysql -uroot -p < sakila-db/sakila-data.sql
索引优化策略

策略一：索引列上不能使用表达式或函数

如下面的这个查询

image-20190128175520575

我们在out_date字段上使用了函数即使建了索引也是无法使用的

优化查询

image-20190128175706662

策略二：前缀索引和索引列的选择性

因为索引也有大小限制，我们可以为列增加前缀索引

1
CREATE INDEX index_name ON table(col_name(n));

Innodb索引列的宽度767个字节(255个字符)，MySAM的索引列宽度是1000个字节。

当超过最大宽度则是无法建立前缀索引。

索引的选择性是不重复的索引值和表的记录数的比值

image-20190128180326408

策略三：联合索引

不推荐在每个列上都建一个索引，推荐建立联合索引

建立联合索引的时候，如何选择索引列的顺序？

  • 经常被使用到的列优先
  • 选择性高的列优先（状态列就不是一个选择性高的）
  • 宽度小的列优先

策略四：覆盖索引

直接通过B-tree索引获得数据，因为叶子节点上存储了索引关键字的值。这样我们可以通过索引的关键字获得直接获得查询数据，这样就不要数据库查询读取数据行信息 。

包含了所有需要查询的字段的全部值的索引就称之为覆盖索引（不仅是查询的还包括group by 和 order by的字段）

优点：

  • 可以优化缓存，减少磁盘io操作。

  • 可以减少随即io ，变随机io操作变为顺序io操作

  • 可以避免对innodb主键索引的二次查询

  • 可以避免myisam表进行系统调用

无法使用覆盖索引的情况

  • 不是所有的存储引擎不支持覆盖索引（只有在索引的叶子节点中包括了键值的索引才能建立覆盖索引）
  • 查询中使用了太多的列，覆盖索引索引的大小比行的数据小的多
  • 使用了双%号的like查询（内存中过滤）

覆盖查询例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select language_id from film where language_id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref
possible_keys: idx_fk_language_id
          key: idx_fk_language_id
      key_len: 1
          ref: const
         rows: 1000
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

因为 language_id 上有个单列的索引，我们使用language_id查询并且只查询language_id这时就会使用覆盖查询（使用索引获取数据）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from film where language_id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: idx_fk_language_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这个查询就不能使用覆盖查询，而是先把数据放到内存中，然后从内存中过滤。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> show  create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> explain select actor_id, last_name from actor where last_name='Joe'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

因为主键默认有索引，因此上方的查询是覆盖查询，直接从索引中取值。

使用索引来优化查询

使用索引扫描来优化排序

前面我们说过 B-tree索引是按照键值的顺序来存储数据的。我们不仅可以利用B-tree索引来查找数据还可以进行排序。

MySQL有两种方式来生成有序的结果，一种是通过排序操作，另一种是按照索引顺序扫描数据。

我们看看想要使用索引扫描来优化排序需要满足的条件：

  • 索引的列顺序和Order By子句的顺序完全一致
  • 索引中所有列的方向（升序, 降序）和Order By子句完全一致
  • 多表关联的时候Order By中的字段全部在关联表中的第一张表中

我们还是看下例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# rental 表的表结构
| rental | CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 |

InnoDB存储引擎数据的逻辑顺序和主键的顺序是一致的，因此我们可以使用主键进行排序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from rental where rental_date>'2005-01-01' order by rental_id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: rental_date
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 16008
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

现在我们将存储引擎改为MySAM

image-20190129144052432

和之前的InnoDB存储引擎具有相同的索引 进行同样的查询

image-20190129144205410

MySAM存储引擎使用的不再是索引而是使用文件进行排序。

我们看下二级索引的使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select rental where rental_date='2005-05-09' order by inventory_id, customer_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date
          key: rental_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

在MySAM的存储表查询

image-20190129145215426

我们看到这个二级查询是可以使用到索引的，并没有使用文件排序的信息。

我们看下其中使用规则之一 ：索引中所有列的方向（升序, 降序）和Order By子句完全一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> explain select * from rental where rental_date='2005-05-09' order by inventory_id desc , customer_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date
          key: rental_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort

这样就会使用到文件排序

还有一个条件是如果联合索引左边的是范围查找 则整个联合索引排序将失效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> explain select * from rental where rental_date>'2005-05-09' order by inventory_id , customer_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ALL
possible_keys: rental_date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16008
     filtered: 50.00
        Extra: Using where; Using filesort

使用文件排序

模拟Hash索引优化查询

当索引的长度受到限制的时候

image-20190129150506491

现在我们模拟Hash索引进行这个字段的查询优化

1
2
3
4
5
6
7
8
9
10
11
mysql> alter table film add title_md5 varchar(32);
Query OK, 0 rows affected (1.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  update film set title_md5=md5(title);
Query OK, 1000 rows affected (0.22 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> create index idx_md5 on film(title_md5);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

进行查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> explain select * from film  where  title='EGG IGBY'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref
possible_keys: title_index
          key: title_index
      key_len: 767
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)


mysql> explain select * from film  where  title_md5=md5('EGG IGBY') and title='EGG IGBY'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref
possible_keys: idx_md5,title_index
          key: idx_md5
      key_len: 99
          ref: const
         rows: 1
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这种使用B-tree索引模拟Hash索引也是有限的：

  • 只能处理键值的全值匹配查找

  • 所使用的Hash函数决定着索引键的大小

使用索引来优化锁

InnoDB是行级锁，只有修改对应的行的时候才会对需要的行加锁。但是只有在InnoDB存储引擎层过滤掉不需要的行的时候，这种情况才会有效。如果存储引擎不能过滤掉我们不需要的行，则需要锁定所有的行，在内存中通过where条件进行过滤。可以通使用索引来减少锁定的行数。 通过索引在存储引擎层过滤掉我们不需要的行，减少锁带来的开销。

利用索引优化锁

  • 索引可以减少锁定的行数
  • 索引可以加快处理速度，同时也加快了锁的释放

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> show create table actor;

| Table | Create Table                                     |

| actor | CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> drop index  idx_actor_last_name  on actor;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from actor where last_name='WOOD'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

我们看到查询是没有用到索引的。

我们在一个shell会话中开启一个事务 使用排它锁进行查询

1
2
3
4
5
6
7
8
9
10
11
12
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from actor where last_name='WOOD' for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|      156 | FAY        | WOOD      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

上面的这个查询会将所有的数据进行加锁放到内存中进行过滤

现在我们在另一个shell会话中开启另一个事务 使用排它锁进行查询

1
2
3
4
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where last_name='willis' for update;

查询将会一直被阻塞直至报错。

现在我们第一个会话中重新建上索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> create index idx_actor_last_name on actor(last_name);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from actor where last_name='WOOD'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>  begin;
Query OK, 0 rows affected (0.00 sec)

# 排它锁的查询
mysql> select * from actor where last_name='WOOD' for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|      156 | FAY        | WOOD      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

在另一个会话重新进行查询

1
2
3
4
5
6
7
8
9
10
11
12
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where last_name='willis' for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       83 | BEN        | WILLIS    | 2006-02-15 04:34:33 |
|       96 | GENE       | WILLIS    | 2006-02-15 04:34:33 |
|      164 | HUMPHREY   | WILLIS    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)

这个时候不再阻塞。

上面就是索引优化锁的过程。

使用索引之后可以在数据查询时锁定更少的行，增加了数据处理的并发性。

索引本身的维护和优化

MySQL允许在一个列上建立一个或多个索引，无论是一个还是多个MySQL都需要单独的去维护这些索引。然后在优化器查询的时候不断的对这些索引进行选择，这肯定会影响数据库的性能。

删除重复和冗余的索引：

image-20190129160351737

主键已经包括了唯一

image-20190129160451796

上面的联合索引的第一个 在查询a列的时候 是可以使用到组合索引的。

下面的之所以是冗余索引是因为 InnoDB存储引擎在每个二级索引之后都会加上主键信息。

使用工具pt-duplicate-key-checker查找冗余索引

1
2
3
4
# 新建冗余索引
mysql> create index idx_customerid_staffid on payment(customer_id, staff_id);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
# 查询冗余索引
[root@hongshaorou ~]# pt-duplicate-key-checker --host=127.0.0.1 --user=root --password=root >test.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 查看
[root@hongshaorou ~]# more test.sql

# A software update is available:
# ########################################################################
# sakila.payment
# ########################################################################

# idx_fk_customer_id is a left-prefix of idx_customerid_staffid
# Key definitions:
#   KEY `idx_fk_customer_id` (`customer_id`),
#   KEY `idx_customerid_staffid` (`customer_id`,`staff_id`),
# Column types:
#	  `customer_id` smallint(5) unsigned not null
#	  `staff_id` tinyint(3) unsigned not null
# To remove this duplicate index, execute:
ALTER TABLE `sakila`.`payment` DROP INDEX `idx_fk_customer_id`;

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes   32172
# Total Duplicate Indexes  1
# Total Indexes            128

上面给出了建议 删除单个索引

查找未被使用过的索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> SELECT a.object_schema, a.object_name, a.index_name, b.TABLE_ROWS
    ->   FROM performance_schema.table_io_waits_summary_by_index_usage a
    ->   JOIN information_schema.tables b ON
    ->   a.`OBJECT_SCHEMA` = b.`TABLE_SCHEMA` AND a.`OBJECT_NAME` = b.`TABLE_NAME`
    ->  WHERE index_name IS NOT NULL
    ->    AND index_name != 'PRIMARY'
    ->    AND count_star = 0
    ->    AND object_schema NOT IN ('mysql', 'performance_schema')
    ->  ORDER BY object_schema, object_name;
+---------------+---------------+-----------------------------+------------+
| object_schema | object_name   | index_name                  | TABLE_ROWS |
+---------------+---------------+-----------------------------+------------+
| sakila        | address       | idx_location                |        603 |
| sakila        | address       | idx_fk_city_id              |        603 |
| sakila        | city          | idx_fk_country_id           |        600 |
| sakila        | customer      | idx_fk_address_id           |        599 |
| sakila        | customer      | idx_last_name               |        599 |
| sakila        | customer      | idx_fk_store_id             |        599 |
| sakila        | film          | idx_fk_original_language_id |       1000 |
| sakila        | film          | idx_md5                     |       1000 |
| sakila        | film          | title_index                 |       1000 |
| sakila        | film          | idx_fk_language_id          |       1000 |
| sakila        | film_actor    | idx_fk_film_id              |       5462 |
| sakila        | film_category | fk_film_category_category   |       1000 |
| sakila        | film_text     | idx_title_description       |       1000 |
| sakila        | inventory     | idx_store_id_film_id        |       4581 |
| sakila        | inventory     | idx_fk_film_id              |       4581 |
| sakila        | payment       | fk_payment_rental           |      16086 |
| sakila        | payment       | idx_customerid_staffid      |      16086 |
| sakila        | payment       | idx_fk_staff_id             |      16086 |
| sakila        | payment       | idx_fk_customer_id          |      16086 |
| sakila        | rental        | idx_fk_inventory_id         |      16008 |
| sakila        | rental        | idx_fk_customer_id          |      16008 |
| sakila        | rental        | idx_fk_staff_id             |      16008 |
| sakila        | rental        | rental_date                 |      16008 |
| sakila        | staff         | idx_fk_store_id             |          2 |
| sakila        | staff         | idx_fk_address_id           |          2 |
| sakila        | store         | idx_unique_manager          |          2 |
| sakila        | store         | idx_fk_address_id           |          2 |
| windfall      | user          | ix_user_phone               |          0 |
| windfall      | user          | ix_user_username            |          0 |
| windfall      | user_tag      | ix_user_tag_uid             |          0 |
| windfall      | verify        | ix_verify_phone             |          0 |
+---------------+---------------+-----------------------------+------------+
31 rows in set (0.22 sec)

使用上面的SQL将会打印出库名，表名，索引名，使用次数。

更新索引统计信息及减少索引碎片化

1
2
# 更新索引统计信息 提高优化器查询
analyze table table_name
1
2
# 减少索引碎片化 使用不当会导致锁表
optimize table table_name
知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水！