索引前置知识可以查看：MySQL数据库
1. 索引的种类
-
主键索引（PRIMARY KEY）：每个表只能有一个主键，且主键的值必须唯一，不允许为空。
-
唯一索引（UNIQUE）：唯一索引要求索引列的值必须唯一，但可以有空值（只能有一个空值）。
-
普通索引（INDEX）：最基本的索引类型，没有唯一性的要求。
-
全文索引（FULLTEXT）：用于对文本数据进行全文搜索。
-
组合索引（Composite Index）：一个索引包含两个或多个列。
2. 索引创建
-
使用
CREATE INDEX语句创建索引：
CREATE INDEX index_name ON table_name (column_name);-
使用
ALTER TABLE添加索引：
ALTER TABLE table_name ADD INDEX index_name (column_name);-
创建表时指定:
create table student（ id int not null primary key auto_increment, name varchar（30）, unique index uni_na(name) ）;3. 索引显示
-
使用
SHOW INDEX命令
查看特定表索引信息的最直接方法：
SHOW INDEXES FROM table_name;或者
SHOW INDEX FROM table_name;table_name是想要查看索引的表名。这个命令会列出该表的所有索引，包括索引名称、列名、索引类型等信息。
-
使用
SHOW CREATE TABLE命令
查看创建表时使用的完整SQL语句，包括索引定义，可以使用：
SHOW CREATE TABLE table_name;这个命令会显示创建该表的完整SQL语句，其中包含了所有的索引定义。
4. 索引测试
4.1 创建表
CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY comment '产品id',category_id INT NOT NULL comment '品类id',product_name VARCHAR(255) NOT NULL comment '产品名字',price DECIMAL(10, 2) NOT NULL comment '价格',supplier_id INT NOT NULL comment '供应商id',INDEX idx_product_name (product_name),UNIQUE INDEX idx_category_id (category_id),INDEX idx_price_supplier (price, supplier_id, product_name)
);-
普通索引
idx_product_name以product_name字段创建。 -
主键索引product_id。
-
唯一索引category_id。
-
组合索引
idx_price_supplier以price，supplier_id,product_name三个字段创建。
插入数据:
DELIMITER $$CREATE PROCEDURE InsertProducts()
BEGINDECLARE i INT DEFAULT 1;WHILE i <= 400 DOINSERT INTO products (category_id, product_name, price, supplier_id)VALUES (i, CONCAT('Product', i), ROUND(100 + RAND() * 500, 2), FLOOR(1 + RAND() * 5));SET i = i + 1;END WHILE;
END $$DELIMITER ;CALL InsertProducts();注：对于唯一索引idx_category_id应该创建为普通索引，在插入时，分为1~10种类。
4.2 查看详情
索引：
show index from products;
查看建表语句：
shwo create table table_name;查看表记录数：
select count(*) from table_name;
4.3 SQL执行关键
explain关键字可以查看后面语句执行情况；
explain select * from table_name where condition;输出信息：
-
select_type：查询类型（如 SIMPLE、PRIMARY、SUBQUERY 等）。
-
table：查询中使用的表。
-
partitions：匹配的分区信息（如果使用分区表）。
-
type：连接类型（如 ALL、index、range、ref、eq_ref 等）。
-
possible_keys：可能使用的索引。
-
key：实际使用的索引。
-
key_len：使用的索引的长度。
-
ref：索引列上与连接表或条件匹配的列。
-
rows：估计需要检查的行数。
-
filtered：基于表条件过滤的行的百分比。
-
Extra：额外的信息，如 Using filesort、Using temporary 等。
4.3.1 主键索引
mysql> explain select * from products where product_id = 232;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
4.3.2 普通索引
mysql> explain select * from products where product_name = 'Product177';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | ref | idx_product_name | idx_product_name | 1022 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+4.3.3 唯一索引
mysql> explain select * from products where category_id = 177;
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | const | idx_category_id | idx_category_id | 4 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------+
4.3.4 组合索引
select index from products;mysql> show index from products;
+----------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products | 0 | PRIMARY | 1 | product_id | A | 400 | NULL | NULL | | BTREE | | | YES | NULL |
| products | 0 | idx_category_id | 1 | category_id | A | 400 | NULL | NULL | | BTREE | | | YES | NULL |
| products | 1 | idx_product_name | 1 | product_name | A | 394 | NULL | NULL | | BTREE | | | YES | NULL |
| products | 1 | idx_price_supplier | 1 | price | A | 399 | NULL | NULL | | BTREE | | | YES | NULL |
| products | 1 | idx_price_supplier | 2 | supplier_id | A | 400 | NULL | NULL | | BTREE | | | YES | NULL |
| products | 1 | idx_price_supplier | 3 | product_name | A | 400 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+看到组合索引idx_price_supplier 中三个字段price、supplier_id、product_name，顺序为依次递增。
-
条件为price:
mysql> explain select * from products where price = 466.95;
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | ref | idx_price_supplier | idx_price_supplier | 5 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+-
条件为supplier_id:
mysql> explain select * from products where supplier_id = 2;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 400 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+-
条件为product_nname:
mysql> explain select * from products where product_name = 'Product177';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | ref | idx_product_name | idx_product_name | 1022 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+没有走组合索引。
-
条件为1、2
mysql> explain select * from products where price = 466.95 and supplier_id = 2;
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | products | NULL | ref | idx_price_supplier | idx_price_supplier | 9 | const,const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
-
条件为1、3
mysql> explain select * from products where price = 466.95 and product_name = 'Product177';
+----+-------------+----------+------------+------+-------------------------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------------------------+------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ref | idx_product_name,idx_price_supplier | idx_product_name | 1022 | const | 1 | 5.00 | Using where |
+----+-------------+----------+------------+------+-------------------------------------+------------------+---------+-------+------+----------+-------------+-
条件为2、3
mysql> explain select * from products where supplier_id = 2 and product_name = 'Product177';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ref | idx_product_name | idx_product_name | 1022 | const | 1 | 10.00 | Using where |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
没有走组合索引。
-
条件为1、2、3
mysql> explain select * from products where price = 466.95 and supplier_id = 2 and product_name = 'Product177';
+----+-------------+----------+------------+------+-------------------------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------------------------+------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ref | idx_product_name,idx_price_supplier | idx_product_name | 1022 | const | 1 | 5.00 | Using where |
+----+-------------+----------+------------+------+-------------------------------------+------------------+---------+-------+------+----------+-------------+走了普通索引（SQL索引优化）。
注：组合索引匹配最左前缀原则，可以理解我从左边开始，即能走到组合索引的条件查询顺序应该为 1。1、2。1、2、3。只有这三种情况才有可能走组合索引。
不积跬步，无以至千里 --- xiaokai
