2.MYSQL索引 索引结构 B+Tree 主键索索引 唯一索引 常规索引 全文索引 聚集索引 二级索引 SQL性能工具 慢查询日志 pfofile explain

索引是一种数据结构,高效获取数据的数据结构

红黑树,二叉树,B-Tree,B+Tree

索引演示

默认全表扫描

索引使用二叉树算法

增删改小 查询多

索引结构

红黑树

B-Tree(多路平衡查找数)

以一颗最大度数(max-degree),为例子 每个节点最多存储4个Key5个指针

 

当B-Tree维度设置为3时每个节点最多能添加2个key

添加两个数据,100和101 大的会被放到右边

在添加一个99 此时到达节点最大值3个 会从三个数据中取中间的数据变为新的根节点 小的分裂到左边,大的分裂到右边

继续添加数据,添加98 比99小所以放到99的左边

当添加到第二个节点的满值的时候会将当前节点抽
取到第一个节点上 如果第一节点也到达满值 就会将第一节点的中间值提取出来称为最新的根节点

B+Tree

在B+树中只只有叶子节点会存储数据根上会保存索引

B加树维度为三,节点最多存储两个个指针

插入1 插入2 当插入3的时候到达节点最大值 将中间值2提取到根节点

跟节点上只保存指向当前节点值的索引 将第一个链表的尾部指向分裂出的链表中

添加4

第二链表到达最大值 向上提取中间值的索引将跟节点索引更新

继续添加第二个节点到达3最大值继续提取中间值提取向上

会对比2 3 4 把3给继续提取向上

在Mysql数据结构的中的B+Tree 进行了优化 将单向列表变成了双向列表

1.存储引擎 逻辑存储结构对应

 

索引分类

 聚集索引的row下存储的直接的对应的行数据二级索引叶子节点存储

存储的是对应的idr

回表查询 先通过了二级索引得到对应的值在通过这个值去聚集索引中查询

思考

select *    from user where id=10;
select * from user where name = 'Arm';
谁效率高
第一条数据高因为直接聚集索引得到数据
第二条需要回表查询

高度三层就可以存储上千万的数据

创建索引

查看索引

 

查找索引
mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |          22 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.48 sec)

mysql> show index from tb_user\G;
*************************** 1. row ***************************
        Table: tb_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 22
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

创建索引
mysql> create index idx_user_name on tb_user(name);
Query OK, 0 rows affected (1.44 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show index from tb_user\G;
*************************** 1. row ***************************
        Table: tb_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 22
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: tb_user
   Non_unique: 1
     Key_name: idx_user_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

创建唯一索引
mysql> create unique index idx_user_phone on tb_user(phone);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tb_user\G;
*************************** 1. row ***************************
        Table: tb_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 22
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: tb_user
   Non_unique: 0
     Key_name: idx_user_phone
 Seq_in_index: 1
  Column_name: phone
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: tb_user
   Non_unique: 1
     Key_name: idx_user_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
3 rows in set (0.01 sec)

ERROR: 
No query specified

创建联合索引

mysql> create unique index idx_user_pro_age_sta  on tb_user(profession,age,status);
Query OK, 0 rows affected (0.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建单行索引

mysql> create index idx_user_email on tb_user(email);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引

mysql> drop index idx_user_email on tb_user;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引性能分析工具

主要优化查询语句

查询当前语句类型查询了次数
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 11    |
| Com_delete    | 2     |
| Com_import    | 0     |
| Com_insert    | 158   |
| Com_repair    | 0     |
| Com_revoke    | 1     |
| Com_select    | 10611 |
| Com_signal    | 0     |
| Com_update    | 30    |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

mysql> select * from emp;
+------+--------------+------+--------------+--------+------------+-----------+---------+
| id   | name         | age  | job          | salary | entrydate  | managerid | dept_id |
+------+--------------+------+--------------+--------+------------+-----------+---------+
|    1 | 张三         |   25 | 工程师       |   5000 | 2021-01-01 |         1 |       1 |
|    2 | 李四         |   30 | 经理         |   8000 | 2020-12-15 |         1 |       2 |
|    3 | 王五         |   28 | 销售员       |   4000 | 2021-02-10 |         2 |       1 |
|    4 | 赵六         |   35 | 人事         |   6000 | 2021-03-20 |         2 |       2 |
|    5 | 刘七         |   27 | 技术支持     |   4500 | 2021-04-05 |         3 |       1 |
|    6 | 陈八         |   32 | 财务         |   7000 | 2021-05-12 |         3 |       2 |
|    7 | 杨九         |   29 | 市场         |   5500 | 2021-06-18 |         4 |       1 |
|    8 | 黄十         |   31 | 运营         |   6500 | 2021-07-25 |         4 |       2 |
|    9 | 罗十一       |   26 | 设计师       |   4800 | 2021-08-30 |         5 |       1 |
|   10 | 吴十二       |   33 | 采购         |   7500 | 2021-09-08 |         5 |       2 |
|   11 | 郑十三       |   28 | 客服         |   4200 | 2021-10-14 |         6 |       1 |
|   12 | 周十四       |   34 | 行政         |   6800 | 2021-11-21 |         6 |       2 |
|   13 | 朱十五       |   27 | 品控         |   5200 | 2021-12-27 |         7 |       1 |
|   14 | 秦十六       |   32 | 研发         |   7800 | 2022-01-02 |         7 |       2 |
|   15 | 许十七       |   29 | 测试         |   4400 | 2022-02-09 |         8 |       1 |
|   16 | 何十八       |   31 | 市场         |   6200 | 2022-03-16 |         8 |       2 |
|   17 | 冯十九       |   26 | 销售员       |   4900 | 2022-04-22 |         9 |       1 |
|   18 | 邓二十       |   33 | 人事         |   7200 | 2022-05-29 |         9 |       2 |
|   19 | 肖二十一     |   28 | 技术支持     |   5300 | 2022-06-04 |        10 |       1 |
|   20 | 潘二十二     |   34 | 财务         |   6900 | 2022-07-11 |        10 |       2 |
|   21 | 张三         |   28 | 经理         |  10000 | 2022-01-01 |         1 |       1 |
|   22 | 李四         |   35 | 主管         |   8000 | 2022-02-01 |         2 |       2 |
|   23 | 王五         |   25 | 员工         |   5000 | 2022-03-01 |         1 |       3 |
|   24 | 赵六         |   30 | 员工         |   5000 | 2022-04-01 |         2 |       4 |
|   25 | 刘七         |   32 | 员工         |   5000 | 2022-05-01 |         1 |       5 |
|   26 | 陈八         |   28 | 员工         |   5000 | 2022-06-01 |         2 |       6 |
|   27 | 杨九         |   29 | 员工         |   5000 | 2022-07-01 |         1 |       1 |
|   28 | 黄十         |   27 | 员工         |   5000 | 2022-08-01 |         2 |       2 |
|   29 | 周十一       |   31 | 员工         |   5000 | 2022-09-01 |         1 |       3 |
|   30 | 吴十二       |   26 | 员工         |   5000 | 2022-10-01 |         2 |       4 |
|   31 | 郑十三       |   33 | 员工         |   5000 | 2022-11-01 |         1 |       5 |
|   32 | 孙十四       |   28 | 员工         |   5000 | 2022-12-01 |         2 |       6 |
|   33 | 钱十五       |   30 | 员工         |   5000 | 2023-01-01 |         1 |       1 |
|   34 | 冯十六       |   29 | 员工         |   5000 | 2023-02-01 |         2 |       2 |
|   35 | 朱十七       |   27 | 员工         |   5000 | 2023-03-01 |         1 |       3 |
|   36 | 徐十八       |   32 | 员工         |   5000 | 2023-04-01 |         2 |       4 |
|   37 | 林十九       |   25 | 员工         |   5000 | 2023-05-01 |         1 |       5 |
|   38 | 许二十       |   31 | 员工         |   5000 | 2023-06-01 |         2 |       6 |
|   39 | 尹二一       |   28 | 员工         |   5000 | 2023-07-01 |         1 |       1 |
|   40 | 姚二二       |   27 | 员工         |   5000 | 2023-08-01 |         2 |       2 |
| NULL | 张四         |   25 | 经理         |   5000 | 2021-01-01 |         1 |    NULL |
+------+--------------+------+--------------+--------+------------+-----------+---------+
41 rows in set (0.01 sec)

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 11    |
| Com_delete    | 2     |
| Com_import    | 0     |
| Com_insert    | 158   |
| Com_repair    | 0     |
| Com_revoke    | 1     |
| Com_select    | 10612 |
| Com_signal    | 0     |
| Com_update    | 30    |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

mysql> select * from emp;
+------+--------------+------+--------------+--------+------------+-----------+---------+
| id   | name         | age  | job          | salary | entrydate  | managerid | dept_id |
+------+--------------+------+--------------+--------+------------+-----------+---------+
|    1 | 张三         |   25 | 工程师       |   5000 | 2021-01-01 |         1 |       1 |
|    2 | 李四         |   30 | 经理         |   8000 | 2020-12-15 |         1 |       2 |
|    3 | 王五         |   28 | 销售员       |   4000 | 2021-02-10 |         2 |       1 |
|    4 | 赵六         |   35 | 人事         |   6000 | 2021-03-20 |         2 |       2 |
|    5 | 刘七         |   27 | 技术支持     |   4500 | 2021-04-05 |         3 |       1 |
|    6 | 陈八         |   32 | 财务         |   7000 | 2021-05-12 |         3 |       2 |
|    7 | 杨九         |   29 | 市场         |   5500 | 2021-06-18 |         4 |       1 |
|    8 | 黄十         |   31 | 运营         |   6500 | 2021-07-25 |         4 |       2 |
|    9 | 罗十一       |   26 | 设计师       |   4800 | 2021-08-30 |         5 |       1 |
|   10 | 吴十二       |   33 | 采购         |   7500 | 2021-09-08 |         5 |       2 |
|   11 | 郑十三       |   28 | 客服         |   4200 | 2021-10-14 |         6 |       1 |
|   12 | 周十四       |   34 | 行政         |   6800 | 2021-11-21 |         6 |       2 |
|   13 | 朱十五       |   27 | 品控         |   5200 | 2021-12-27 |         7 |       1 |
|   14 | 秦十六       |   32 | 研发         |   7800 | 2022-01-02 |         7 |       2 |
|   15 | 许十七       |   29 | 测试         |   4400 | 2022-02-09 |         8 |       1 |
|   16 | 何十八       |   31 | 市场         |   6200 | 2022-03-16 |         8 |       2 |
|   17 | 冯十九       |   26 | 销售员       |   4900 | 2022-04-22 |         9 |       1 |
|   18 | 邓二十       |   33 | 人事         |   7200 | 2022-05-29 |         9 |       2 |
|   19 | 肖二十一     |   28 | 技术支持     |   5300 | 2022-06-04 |        10 |       1 |
|   20 | 潘二十二     |   34 | 财务         |   6900 | 2022-07-11 |        10 |       2 |
|   21 | 张三         |   28 | 经理         |  10000 | 2022-01-01 |         1 |       1 |
|   22 | 李四         |   35 | 主管         |   8000 | 2022-02-01 |         2 |       2 |
|   23 | 王五         |   25 | 员工         |   5000 | 2022-03-01 |         1 |       3 |
|   24 | 赵六         |   30 | 员工         |   5000 | 2022-04-01 |         2 |       4 |
|   25 | 刘七         |   32 | 员工         |   5000 | 2022-05-01 |         1 |       5 |
|   26 | 陈八         |   28 | 员工         |   5000 | 2022-06-01 |         2 |       6 |
|   27 | 杨九         |   29 | 员工         |   5000 | 2022-07-01 |         1 |       1 |
|   28 | 黄十         |   27 | 员工         |   5000 | 2022-08-01 |         2 |       2 |
|   29 | 周十一       |   31 | 员工         |   5000 | 2022-09-01 |         1 |       3 |
|   30 | 吴十二       |   26 | 员工         |   5000 | 2022-10-01 |         2 |       4 |
|   31 | 郑十三       |   33 | 员工         |   5000 | 2022-11-01 |         1 |       5 |
|   32 | 孙十四       |   28 | 员工         |   5000 | 2022-12-01 |         2 |       6 |
|   33 | 钱十五       |   30 | 员工         |   5000 | 2023-01-01 |         1 |       1 |
|   34 | 冯十六       |   29 | 员工         |   5000 | 2023-02-01 |         2 |       2 |
|   35 | 朱十七       |   27 | 员工         |   5000 | 2023-03-01 |         1 |       3 |
|   36 | 徐十八       |   32 | 员工         |   5000 | 2023-04-01 |         2 |       4 |
|   37 | 林十九       |   25 | 员工         |   5000 | 2023-05-01 |         1 |       5 |
|   38 | 许二十       |   31 | 员工         |   5000 | 2023-06-01 |         2 |       6 |
|   39 | 尹二一       |   28 | 员工         |   5000 | 2023-07-01 |         1 |       1 |
|   40 | 姚二二       |   27 | 员工         |   5000 | 2023-08-01 |         2 |       2 |
| NULL | 张四         |   25 | 经理         |   5000 | 2021-01-01 |         1 |    NULL |
+------+--------------+------+--------------+--------+------------+-----------+---------+
41 rows in set (0.00 sec)

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 11    |
| Com_delete    | 2     |
| Com_import    | 0     |
| Com_insert    | 158   |
| Com_repair    | 0     |
| Com_revoke    | 1     |
| Com_select    | 10613 |
| Com_signal    | 0     |
| Com_update    | 30    |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

慢查询日志

慢查询日志记录了所有执行时间超过指定时间的所有SQL语句日志

MySQL的慢查询日志没有默认没有开启的,需要配置文件中

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

慢记录

在mysql中配置文件添加两条
vim /etc/my.cnf
slow_query_log=1
long_query_time=2

后重启nysql
systemctl restart mysqld

查询开启了
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

慢查询日志被放到了/var/lib/mysql下的localhost-slow.log

[root@localhost mysql]# tail -f localhost-slow.log  
/usr/sbin/mysqld, Version: 8.0.31 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

在show profiles能够在做SQL优化时帮助我们了解时间

都耗费到哪里去了,通过have_profiling参数,能够看到当前Mysql

是否支持profile操作

Select @@have_profiling;

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from tb_user;
+----+--------+-------------+--------------------+------------+------+--------+--------+---------------------+
| id | name   | phone       | email              | profession | age  | gender | status | createtime          |
+----+--------+-------------+--------------------+------------+------+--------+--------+---------------------+
|  1 | User6  | 13866135941 | user6@example.com  | Doctor     |   58 | M      |      1 | 2023-04-04 12:22:22 |
|  2 | User5  | 13800208231 | user5@example.com  | Teacher    |   56 | F      |      1 | 2023-07-10 12:22:22 |
|  3 | User4  | 13808353494 | user4@example.com  | Doctor     |   42 | F      |      1 | 2022-12-21 12:22:22 |
|  4 | User3  | 13859491838 | user3@example.com  | Teacher    |   42 | M      |      0 | 2023-06-13 12:22:22 |
|  5 | User2  | 13845527412 | user2@example.com  | Engineer   |   47 | M      |      1 | 2023-05-31 12:22:22 |
|  6 | User7  | 13847427883 | user7@example.com  | Engineer   |   39 | F      |      0 | 2023-03-02 12:22:22 |
|  7 | User6  | 13887882156 | user6@example.com  | Doctor     |   52 | F      |      1 | 2023-09-13 12:22:22 |
|  8 | User5  | 13825252201 | user5@example.com  | Student    |    5 | F      |      1 | 2023-06-17 12:22:22 |
|  9 | User4  | 13884396998 | user4@example.com  | Engineer   |   28 | F      |      0 | 2022-12-30 12:22:22 |
| 10 | User3  | 13874514022 | user3@example.com  | Teacher    |   21 | F      |      0 | 2023-08-19 12:22:22 |
| 11 | User8  | 13840589515 | user8@example.com  | Doctor     |   27 | F      |      0 | 2023-08-27 12:22:22 |
| 12 | User7  | 13860359540 | user7@example.com  | Doctor     |    7 | F      |      1 | 2023-07-03 12:22:22 |
| 13 | User6  | 13851906053 | user6@example.com  | Doctor     |    1 | F      |      1 | 2022-12-18 12:22:22 |
| 14 | User5  | 13893728111 | user5@example.com  | Engineer   |    6 | M      |      1 | 2023-04-01 12:22:22 |
| 15 | User4  | 13859096771 | user4@example.com  | Teacher    |   46 | F      |      1 | 2023-01-30 12:22:22 |
| 16 | User9  | 13800338247 | user9@example.com  | Student    |   18 | F      |      1 | 2023-05-08 12:22:22 |
| 17 | User8  | 13864989318 | user8@example.com  | Student    |   33 | M      |      1 | 2023-03-07 12:22:22 |
| 18 | User7  | 13875649896 | user7@example.com  | Student    |   22 | M      |      0 | 2023-02-06 12:22:22 |
| 19 | User6  | 13897047409 | user6@example.com  | Student    |   53 | M      |      0 | 2023-09-14 12:22:22 |
| 20 | User5  | 13814101646 | user5@example.com  | Teacher    |   40 | M      |      1 | 2023-05-20 12:22:22 |
| 21 | User10 | 13841501548 | user10@example.com | Student    |   55 | M      |      1 | 2023-08-09 12:22:22 |
| 22 | User9  | 13888744428 | user9@example.com  | Student    |   38 | F      |      0 | 2023-02-07 12:22:22 |
| 23 | User8  | 13817530806 | user8@example.com  | Student    |   25 | F      |      1 | 2023-02-15 12:22:22 |
| 24 | User7  | 13855828031 | user7@example.com  | Student    |   11 | F      |      1 | 2023-08-13 12:22:22 |
| 25 | User6  | 13804747593 | user6@example.com  | Doctor     |   19 | F      |      1 | 2023-09-09 12:22:22 |
+----+--------+-------------+--------------------+------------+------+--------+--------+---------------------+
25 rows in set (0.00 sec)

mysql> select * from tb_user where id=1;
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name  | phone       | email             | profession | age  | gender | status | createtime          |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
|  1 | User6 | 13866135941 | user6@example.com | Doctor     |   58 | M      |      1 | 2023-04-04 12:22:22 |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb_user where name = 'User6';
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name  | phone       | email             | profession | age  | gender | status | createtime          |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
|  1 | User6 | 13866135941 | user6@example.com | Doctor     |   58 | M      |      1 | 2023-04-04 12:22:22 |
|  7 | User6 | 13887882156 | user6@example.com | Doctor     |   52 | F      |      1 | 2023-09-13 12:22:22 |
| 13 | User6 | 13851906053 | user6@example.com | Doctor     |    1 | F      |      1 | 2022-12-18 12:22:22 |
| 19 | User6 | 13897047409 | user6@example.com | Student    |   53 | M      |      0 | 2023-09-14 12:22:22 |
| 25 | User6 | 13804747593 | user6@example.com | Doctor     |   19 | F      |      1 | 2023-09-09 12:22:22 |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)


  
mysql> select count(*) from tb_sku;
ERROR 1146 (42S02): Table 'nltest.tb_sku' doesn't exist
mysql> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00017925 | select * from tb_user                      |
|        2 | 0.05502925 | show databases                             |
|        3 | 0.00005700 | select databases()                         |
|        4 | 0.00023275 | select database()                          |
|        5 | 0.00015900 | SELECT DATABASE()                          |
|        6 | 0.00074625 | show databases                             |
|        7 | 0.00244950 | show tables                                |
|        8 | 0.17712750 | select * from tb_user                      |
|        9 | 0.00031725 | select * from tb_user                      |
|       10 | 0.00028750 | select * from tb_user where id=1           |
|       11 | 0.00046225 | select * from tb_user where name = 'User6' |
|       12 | 0.00078950 | select count(*) from tb_sku                |
+----------+------------+--------------------------------------------+
12 rows in set, 1 warning (0.00 sec)

在每一个阶段

mysql> show profile for query 11;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000104 |
| Executing hook on transaction  | 0.000012 |
| starting                       | 0.000007 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000033 |
| init                           | 0.000005 |
| System lock                    | 0.000007 |
| optimizing                     | 0.000017 |
| statistics                     | 0.000132 |
| preparing                      | 0.000017 |
| executing                      | 0.000039 |
| end                            | 0.000003 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000032 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000011 |
| cleaning up                    | 0.000029 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

starting：开始执行语句
Executing hook on transaction：执行事务相关的钩子函数
checking permissions：检查用户对表或数据库的权限
Opening tables：打开表文件
init：初始化查询
System lock：获取系统锁，例如全局读锁
optimizing：优化查询，例如重写或简化查询条件
statistics：收集统计信息，例如表的行数，索引的选择性等
preparing：准备查询，例如创建查询计划，分配内存等
executing：执行查询计划
end：结束查询执行
query end：结束查询处理
waiting for handler commit：等待存储引擎提交事务
closing tables：关闭表文件
freeing items：释放查询使用的内存或临时表等资源
cleaning up：清理查询相关的数据结构或缓存等

使用explain查询语句执行的信息

mysql> explain select * from tb_user where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

id：表示查询中每个表的序号，如果有子查询或联合查询，id的值可能不连续。id值越大，优先级越高，越先被执行。
select_type：表示查询的类型，有SIMPLE（简单查询，不包含子查询或联合查询）、PRIMARY（最外层的查询）、SUBQUERY（子查询中的第一个SELECT）、DERIVED（子查询中的非第一个SELECT）、UNION（联合查询中的第二个或后面的SELECT）、UNION RESULT（联合查询的结果）等。
table：表示正在访问的表名，如果使用了表别名或子查询，也会显示在这里。
partitions：表示分区表匹配的分区列表，如果不是分区表，显示NULL。
type：表示表的访问方法，有system（系统表）、const（常量表）、eq_ref（唯一索引扫描）、ref（非唯一索引扫描）、range（范围扫描）、index（全索引扫描）、ALL（全表扫描）等。一般来说，type值从左到右性能从高到低。
possible_keys：表示可能用到的索引列表，一个或多个。如果为空，表示没有可用的索引。注意，这里只是可能用到，并不一定实际用到。
key：表示实际用到的索引。如果为NULL，则没有用到索引。注意，即使有索引可用，MySQL也不一定会使用它，因为有时候全表扫描比索引扫描更高效。
key_len：表示实际用到的索引长度。如果为NULL，则没有用到索引。注意，并不是索引定义时的长度，而是根据查询条件中使用了索引字段的部分来计算的。key_len越短越好。
ref：表示哪些列或常量与key一起被使用。如果为NULL，则没有与key相关联的值。
rows：表示MySQL根据统计信息估算出来需要读取的行数，并不一定准确。rows越小越好。
filtered：表示按照条件过滤后剩余数据占原数据的百分比，并不一定准确。filtered越大越好。
Extra：表示MySQL对查询进行优化时所做的额外操作信息，有Using index（使用了覆盖索引）、Using where（使用了WHERE条件过滤数据）、Using temporary（使用了临时表排序或分组）、Using filesort（使用了文件排序而非索引排序）等。

这是一个简单查询，只涉及一个表tb_user
这个表没有分区
这个表使用了const类型访问方法，说明它是通过主键或唯一索引进行等值匹配
可能用到和实际用到的索引都是PRIMARY
索引长度为4字节
与索引相关联的值为const常量
预计只需要读取1行数据
过滤后剩余100%数据
没有额外操作信息

mysql> explain select * from tb_user where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

id:select查询序列号,表示查询中执行select语句子句,或者是操作表的顺序(id相同,从下到上执行,id不同,值越大,越先执行) 
select:

mysql> explain select s.*,c.id,c.name from student s,course c,enroll e where s.id=e.student_id and c.id=course_id;
+----+-------------+-------+------------+--------+-------------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys     | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+-------------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | s     | NULL       | ALL    | PRIMARY           | NULL    | NULL    | NULL             |    4 |   100.00 | NULL        |
|  1 | SIMPLE      | e     | NULL       | ref    | PRIMARY,course_id | PRIMARY | 4       | ithe.s.id        |    2 |   100.00 | Using index |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY           | PRIMARY | 4       | ithe.e.course_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+-------------------+---------+---------+------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

table访问的表名
s -> e -> c
三个表分次查询
mysql> select name from student s where s.id in (select student_id from enroll where course_id =(select id from course c where c.nname='计算机'));
+--------+
| name   |
+--------+
| 张三   |
| 赵六   |
+--------+
2 rows in set (0.03 sec)

mysql> desc select name from student s where s.id in (select student_id from enroll where course_id =(select id from course c where c.name='计算机'));
+----+-------------+--------+------------+--------+-------------------+-----------+---------+------------------------+------+----------+--------------------------+
| id | select_type | table  | partitions | type   | possible_keys     | key       | key_len | ref                    | rows | filtered | Extra                    |
+----+-------------+--------+------------+--------+-------------------+-----------+---------+------------------------+------+----------+--------------------------+
|  1 | PRIMARY     | enroll | NULL       | ref    | PRIMARY,course_id | course_id | 4       | const                  |    2 |   100.00 | Using where; Using index |
|  1 | PRIMARY     | s      | NULL       | eq_ref | PRIMARY           | PRIMARY   | 4       | ithe.enroll.student_id |    1 |   100.00 | NULL                     |
|  3 | SUBQUERY    | c      | NULL       | ALL    | NULL              | NULL      | NULL    | NULL                   |    4 |    25.00 | Using where              |
+----+-------------+--------+------------+--------+-------------------+-----------+---------+------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain select * from tb_user where phone = '13817530806';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone | idx_user_phone | 45      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

索引使用

验证索引的使用

添加索引后可以看到变得很快
mysql> select * from test where name ='name99977';
+-------+-----------+-----+--------+--------+
| id    | name      | age | gender | salary |
+-------+-----------+-----+--------+--------+
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
+-------+-----------+-----+--------+--------+
3 rows in set (2.04 sec)

mysql> select * from test where name ='name99977';
+-------+-----------+-----+--------+--------+
| id    | name      | age | gender | salary |
+-------+-----------+-----+--------+--------+
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
+-------+-----------+-----+--------+--------+
3 rows in set (1.57 sec)

mysql> select * from test where name ='name99977';
+-------+-----------+-----+--------+--------+
| id    | name      | age | gender | salary |
+-------+-----------+-----+--------+--------+
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
+-------+-----------+-----+--------+--------+
3 rows in set (1.65 sec)

mysql> select * from test where name ='name99977';
+-------+-----------+-----+--------+--------+
| id    | name      | age | gender | salary |
+-------+-----------+-----+--------+--------+
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
+-------+-----------+-----+--------+--------+
3 rows in set (1.65 sec)

mysql> create index idx_sku_sn on test(name);
Query OK, 0 rows affected (11.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test where name ='name99977';
+-------+-----------+-----+--------+--------+
| id    | name      | age | gender | salary |
+-------+-----------+-----+--------+--------+
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
| 99977 | name99977 |  77 | F      | 999.77 |
+-------+-----------+-----+--------+--------+
3 rows in set (0.02 sec)

mysql> select * from test where name ='name9997';
+------+----------+-----+--------+--------+
| id   | name     | age | gender | salary |
+------+----------+-----+--------+--------+
| 9997 | name9997 |  97 | F      |  99.97 |
| 9997 | name9997 |  97 | F      |  99.97 |
| 9997 | name9997 |  97 | F      |  99.97 |
+------+----------+-----+--------+--------+
3 rows in set (0.00 sec)

索引使用 原则及索引可能会失效的情况

mysql> explain select * from tb_user where profession = 'Doctor' and age = 19 and status ='1';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_pro_age_sta | idx_user_pro_age_sta | 87      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_user where profession = 'Doctor' and age = 19;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 85      | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from tb_user where profession = 'Doctor';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 83      | const |    7 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

最左边的列没有查询就导致了索引失效了

需要满足最左前缀法则

mysql> explain select * from tb_user where age = 19 and status ='1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |     4.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)



 可以看到只有83索引 前面的生效了 后面的并没有走索引
mysql> explain select * from tb_user where profession = 'Doctor' and status ='0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 83      | const |    7 |    10.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)

不跟查询顺序 调用顺序相关
mysql> explain select * from tb_user where age = status ='0' and profession='Dotor';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 83      | const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

范围索引的右边将失效

不要对索引列进行运算否则失效

mysql> select * from tb_user where substring(phone,10,2) ='15';
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name  | phone       | email             | profession | age  | gender | status | createtime          |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 11 | User8 | 13840589515 | user8@example.com | Doctor     |   27 | F      |      0 | 2023-08-27 12:22:22 |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql> explain select * from tb_user where substring(phone,10,2) ='15';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
type == NULL 全表扫描所以运算失效

前面模糊不走索引,后面模糊走索引
mysql> explain select * from tb_user where profession like "D%";
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 83      | NULL |    7 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.20 sec)

mysql> explain select * from tb_user where profession like "%D";
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          22 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            1 | profession  | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            2 | age         | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            3 | status      | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

在给定的搜索结果中，"Key_name" 列包含了索引的名称。如果一个索引的名称在多行中重复出现，那么这个索引就是联合索引。
根据提供的搜索结果，我们可以看到 "idx_user_pro_age_sta" 这个索引在三行中重复出现，因此可以确定它是一个联合索引。


Table：表的名称。
Non_unique：如果索引不能包含重复值，为0；如果可以，为1。
Key_name：索引的名称。如果是主键索引，名称总是PRIMARY。
Seq_in_index：列在索引中的序号，从1开始。
Column_name：列的名称。如果索引使用了表达式而不是列或列前缀值，这个字段为NULL，表达式字段显示表达式。
Collation：列在索引中的排序方式。A表示升序，D表示降序，NULL表示不排序。
Cardinality：索引中唯一值的估计数。这个数越高，MySQL在做连接时使用索引的可能性越大。
Sub_part：索引前缀。如果整个列被索引，为NULL；否则，显示被索引的字符数。
Packed：表示索引是否被压缩，NULL表示没有。
Null：如果列包含NULL值，为YES；否则为空。
Index_type：表示使用的索引方法，如BTREE, HASH, RTREE, 或 FULLTEXT。
Comment：显示索引的注释。
Index_comment：显示创建索引时指定的注释。
Visible：表示索引是否可见，YES或NO。
Expression：如果索引使用了表达式而不是列或列前缀值，显示表达式。

mysql> select * from tb_user where profession like "D%";
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name  | phone       | email             | profession | age  | gender | status | createtime          |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 13 | User6 | 13851906053 | user6@example.com | Doctor     |    1 | F      |      1 | 2022-12-18 12:22:22 |
| 12 | User7 | 13860359540 | user7@example.com | Doctor     |    7 | F      |      1 | 2023-07-03 12:22:22 |
| 25 | User6 | 13804747593 | user6@example.com | Doctor     |   19 | F      |      1 | 2023-09-09 12:22:22 |
| 11 | User8 | 13840589515 | user8@example.com | Doctor     |   27 | F      |      0 | 2023-08-27 12:22:22 |
|  3 | User4 | 13808353494 | user4@example.com | Doctor     |   42 | F      |      1 | 2022-12-21 12:22:22 |
|  7 | User6 | 13887882156 | user6@example.com | Doctor     |   52 | F      |      1 | 2023-09-13 12:22:22 |
|  1 | User6 | 13866135941 | user6@example.com | Doctor     |   58 | M      |      1 | 2023-04-04 12:22:22 |
+----+-------+-------------+-------------------+------------+------+--------+--------+---------------------+
7 rows in set (0.01 sec)

mysql> explain select * from tb_user where id =10 or age =23;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |   25 |    14.09 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_user where id =10 or age =23cer;
ERROR 1054 (42S22): Unknown column '23cer' in 'where clause'
mysql> create index idx_user_age on tb_user(age);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          22 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            1 | profession  | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            2 | age         | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            3 | status      | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age         |            1 | age         | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          22 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            1 | profession  | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            2 | age         | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            3 | status      | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age         |            1 | age         | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)

mysql> explain select * from tb_user where id =10 or age =23;
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index_merge | PRIMARY,idx_user_age | PRIMARY,idx_user_age | 4,2     | NULL |    2 |   100.00 | Using union(PRIMARY,idx_user_age); Using where |
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

Mysql自动评估使用索引比全表慢,就用索引

索引使用原则

有单列索引又有联合索引 mysql会根据自己优化选择使用哪种索引



mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          22 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            1 | profession  | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            2 | age         | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_pro_age_sta |            3 | status      | A         |          25 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age         |            1 | age         | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)

mysql> explain select * from tb_user where id =10 or age =23;
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index_merge | PRIMARY,idx_user_age | PRIMARY,idx_user_age | 4,2     | NULL |    2 |   100.00 | Using union(PRIMARY,idx_user_age); Using where |
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> create index idx_user_pro on tb_user(profession);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select 8 from tb_user where profession='Dotor';
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                     | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta,idx_user_pro | idx_user_pro_age_sta | 83      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.09 sec)

mysql> explain select 8 from tb_user where profession='Dotor';
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                     | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta,idx_user_pro | idx_user_pro_age_sta | 83      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------------+

建议使用的索引
但是不一定执行这个建议
mysql> explain select * from tb_user use index(idx_user_pro)where profession='Dotor';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro  | idx_user_pro | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

 不准使用的索引
 mysql数据库就会不去使用那个索引
mysql> explain select * from tb_user ignore index(idx_user_pro) where profession='Dotor';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 必须使用这个索引
 
mysql> explain select * from tb_user force index(idx_user_pro) where profession='Dotor';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro  | idx_user_pro | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

use 使用
ignore 忽视
force武力

• 覆盖索引

尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部找到)减少select *

最后的字段

mysql> explain select id,profession,status from tb_user where profession = 'Doctor' and age = 19 and status='1';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_pro_age_sta | idx_user_pro_age_sta | 87      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,profession,status,name from tb_user where profession = 'Doctor' and age = 19 and status='1';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_pro_age_sta | idx_user_pro_age_sta | 87      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

回表索引

查询语句为

        

select id,name,gender from tb_user where name ='Arm';

会先根据name寻找辅助索引到Arm 可需要gendef字段 name中没有gendef字段 就会去聚集索引(id)根据查询出来的id来查询所有行在过滤就会导致效率变低

辅助索引（Secondary Index）：
创建表时，在需要创建辅助索引的列上添加索引。例如，使用CREATE TABLE语句创建表时，可以在列定义后面添加关键字"INDEX"或"KEY"，然后指定索引的名称和列名。例如：CREATE TABLE table_name (column1 datatype, column2 datatype, INDEX index_name (column_name));
使用ALTER TABLE语句在已存在的表上添加辅助索引。例如，使用ALTER TABLE语句，指定表名和要添加索引的列名。例如：ALTER TABLE table_name ADD INDEX index_name (column_name);
聚集索引（Clustered Index）：
创建表时，在定义主键列时，使用PRIMARY KEY关键字来指定主键。例如：CREATE TABLE table_name (column1 datatype, column2 datatype, PRIMARY KEY (column_name));
使用ALTER TABLE语句在已存在的表上添加主键。例如，使用ALTER TABLE语句，指定表名和要添加主键的列名。例如：ALTER TABLE table_name ADD PRIMARY KEY (column_name);

将username和password变成联合索引这样子可以将性能提高

根据查询条件中的第一个索引列，数据库会在联合索引中查找匹配该列值的索引节点（B-tree或其他索引结构）。
在找到的索引节点中，数据库会继续查找下一个索引列的值，以缩小匹配范围。
如果查询条件中还有更多的索引列，数据库会继续按照索引列的顺序查找匹配的索引节点，进一步缩小匹配范围。
最终，数据库会找到包含所有索引列值的索引节点，该节点中存储了符合查询条件的数据的位置信息（如行号或物理地址）。
根据定位到的位置信息，数据库可以直接获取到符合查询条件的数据。

前缀索引

当字段类型为字符串(varchar,text)时,有时候需要索引很长的字符串,这会让索引变得很大

当查询
select * from tb_user where email='lvbu6666@163.com';时候

问:会使用email前面的lvbu6来查询索引 得到1id然后在回表去聚集索引中根据id查询到字段得到的数据中email是否与查询的相同如果不相同着进入下一个链表中的id查询


答:是的，当执行查询语句 select * from tb_user where email='lvbu6666@163.com'; 时，MySQL会使用索引 idex_email_7 来定位满足条件的记录。
首先，MySQL会使用索引 idex_email_7 来搜索 email 列前7位子字符串为 'lvbu6' 的记录。通过索引，可以快速定位到满足条件的记录的主键值（在这里是 id）。
然后，MySQL会使用找到的主键值（在这里是 id）去聚集索引（即主键索引）中查找对应的记录，并返回所有字段的值。
在返回的记录中，会包含满足条件的 email 列的值为 'lvbu6666@163.com' 的记录。如果存在多个满足条件的记录，MySQL会按照聚集索引中的顺序返回这些记录。
需要注意的是，如果索引 idex_email_7 存在重复的键值（即多个记录的前7位子字符串相同），那么在回表查询时，MySQL会根据主键的顺序依次返回这些记录。这意味着返回的记录中的 email 列的值可能不全是 'lvbu6666@163.com'，可能会包含其他值，需要根据具体情况进行判断和处理。


在MySQL中，辅助索引的叶子节点包含了索引列的值和指向对应数据行的指针。这些指针可以是聚集索引的值对，也可以是指向数据行的物理存储位置的指针。
当使用辅助索引进行查询时，MySQL会首先根据辅助索引的叶子节点找到满足条件的行的物理存储位置，然后再通过聚集索引或主键索引找到具体的数据行。
所以，辅助索引的叶子节点不一定是聚集索引的值对，它们只是包含了索引列的值和指向对应数据行的指针。聚集索引和辅助索引之间的关系是通过指针来建立的，用于在查询时快速定位到具体的数据行。


在MySQL中，如果你在创建表的时候没有指定主键或者聚集索引，MySQL会自动为表创建一个隐藏的聚集索引。这个隐藏的聚集索引被称为聚集索引（clustered index）或主键索引（primary key index），它的键值是行的物理存储位置。
聚集索引在存储数据时会按照索引的顺序将数据行存储在磁盘上，这样可以提高查询的性能。如果没有显式指定主键或聚集索引，MySQL会选择一个唯一的非空索引作为聚集索引，如果没有这样的索引，MySQL会自动创建一个隐藏的聚集索引来满足要求。
需要注意的是，一个表只能有一个聚集索引，它决定了表中数据的物理存储顺序。如果你在创建表时显式指定了主键或聚集索引，MySQL会使用你指定的索引作为聚集索引。如果没有显式指定，MySQL会自动创建一个隐藏的聚集索引。
聚集索引的选择对于查询性能和存储空间的利用非常重要，因此在创建表时，建议根据实际需求显式指定主键或聚集索引，以便更好地控制索引的选择。

问:单列索引是辅助索引吗?

答:是的，单列索引是辅助索引的一种形式。辅助索引（也称为非聚集索引或次要索引）是基于表的某个列或列组合创建的索引，用于加快对表的查询操作。
单列索引是指只包含单个列的索引。它可以是辅助索引的一种形式，用于加速对表的查询操作。当使用单列索引进行查询时，数据库引擎会根据索引列的值快速定位到满足条件的行的物理存储位置，然后再获取具体的数据行。
除了单列索引，还可以创建多列索引，即包含多个列的索引。多列索引可以按照指定的列顺序进行查询，并且在查询时可以利用多个列的值进行过滤和排序，提高查询性能。
需要注意的是，辅助索引的选择要根据实际需求和查询模式来确定。在创建辅助索引时，需要考虑查询的频率、查询条件、排序和过滤需求等因素，以便选择合适的索引列和索引类型来优化查询性能。

问:单列索引 取值 先渠道单列索引存储的主键 在通过聚合索引来搜索是吗?


答:是的，当使用单列索引进行查询时，数据库引擎会根据索引列的值快速定位到满足条件的行的物理存储位置。然后，通过聚集索引（或主键索引）来获取具体的数据行。
假设有一个表A，其中包含主键列PK和单列索引列Index。如果你使用单列索引Index进行查询，数据库引擎会先根据索引Index的值找到满足条件的行的物理存储位置。然后，通过聚集索引PK来获取具体的数据行。
聚集索引定义了表的物理存储顺序，并且包含了表的所有列的值。在聚集索引中，数据行按照聚集索引的顺序进行存储。因此，当使用单列索引进行查询时，数据库引擎会利用单列索引定位到物理存储位置，然后通过聚集索引获取具体的数据行。
需要注意的是，如果表没有定义聚集索引，那么主键索引就会被用作聚集索引。在这种情况下，单列索引的取值将直接使用主键索引来搜索具体的数据行。

mysql> explain select id,phone,name from tb_user where phone = '12345678901' and name = '张三';
+----+-------------+---------+------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                                         |
+----+-------------+---------+------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index_merge | idx_phone,idx_name | idx_name,idx_phone | 42,45   | NULL |    1 |   100.00 | Using intersect(idx_name,idx_phone); Using where; Using index |
+----+-------------+---------+------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,phone,name from tb_user where phone = '12345678901' and name = '张三';
+----+-------------+---------+------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                                         |
+----+-------------+---------+------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index_merge | idx_phone,idx_name | idx_name,idx_phone | 42,45   | NULL |    1 |   100.00 | Using intersect(idx_name,idx_phone); Using where; Using index |
+----+-------------+---------+------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index idx_user_phone_name from tb_user(phone,name);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tb_user(phone,name)' at line 1
mysql> create index idx_user_phone_name tb_user(phone,name);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tb_user(phone,name)' at line 1
mysql> create index idx_user_phone_name on tb_user(phone,name);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,phone,name from tb_user where phone = '12345678901' and name = '张三';
+----+-------------+---------+------------+------+----------------------------------------+---------------------+---------+-------------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys                          | key                 | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+----------------------------------------+---------------------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_phone,idx_name,idx_user_phone_name | idx_user_phone_name | 87      | const,const |    2 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+----------------------------------------+---------------------+---------+-------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,phone,name from tb_user where phone = '12345678901' and name = '张三';
+----+-------------+---------+------------+------+----------------------------------------+---------------------+---------+-------------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys                          | key                 | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+----------------------------------------+---------------------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_phone,idx_name,idx_user_phone_name | idx_user_phone_name | 87      | const,const |    2 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+----------------------------------------+---------------------+---------+-------------+------+----------+--------------------------+

explain select id,phone,name from tb_user where phone = '12345678901' and name = '张三';
这里使用的只有联合索引
在联合索引中叶子节点中就已经存储了 id,phone,name这三个字段了就可以不用去在回表查询
但要注意phone及最左字段存在查询

索引设计原则
- 1.针对数据量较大,且查询比较频繁的表建立索引
- 2.针对于作为查询条件(where),排序(order by),分组(group by)
- 3.尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度高,使用索引
- 4.如果是字符串类型的字段,字段的长度较长可以针对字段的特点,建立前缀索引
- 5.尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省储存空间避免回表,提高查询效率
- 6.要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价就越大,会影响增删改查的效率
- 8.如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它,当优化器知道每列是否NULL值时他可以更好的确定哪个索引更有效率地查询

总结

1.索引概述

索引是高效获取数据的数据结构

2.索引结构

B+Tree

Hash

3.索引分类

主键索索引,唯一索引,常规索引,全文索引,聚集索引,二级索引

默认主键索引就是聚集索引

4.索引语法

create [unique] index xxx on xxx(xxx)
show index from xxxx,
drop index xxx on xxxx;

5.SQL性能工具
执行频次,慢查询日志,pfofile,explain
6.索引的使用原则
联合索引 最左前缀法则 如果最左边的列不存在则索引失败
索引失效    不要索引列函数运算否则索引失效,字符串不加引号会进行隐式转换导致索引失效 模糊匹配前面加百分号会导致失效 like '%D'是错误的只能like 'D%',order 连接需要两边都有索引
SQL提示 mysql默认会自己根据性能来选择索引 也可以使用 use index等字段来提示mysql应该使用什么索引
覆盖索引
前缀索引
单列索引/联合索引 推荐使用联合索引 可以避免回表查询
7.索引设计原则
表 字段 索引