本文将介绍MySQL数据库调优的基本步骤，并通过实际案例分析，帮助读者更深入地理解如何优化MySQL数据库性能。

执行"CREATE TABLE 新表 AS SELECT * FROM 原表 ;" 后，新表与原表的字段一致，但主键、索引不会复制到新表，会把原表的表记录复制到新表。

本文由【云老大】 TG@yunlaoda360 撰写

索引相关参数优化

• 增加合适的索引

作为程序员，我们每天都要与数据库打交道。当系统用户量突破百万级时，数据库往往成为性能瓶颈的首要怀疑对象。本文将深入探讨MySQL优化的七大核心策略，并提供可直接落地的优化方案，助您构建高效稳定的数据库系统。

一、Mysql中有哪几种锁？

1）表级锁

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

一、B+树核心原理：MySQL索引的基石

1.1 B+树数据结构解析

B+树是B树的变种，具有以下关键特征：

在互联网大厂的后端开发领域，MySQL 数据库的使用极为广泛。随着业务数据的爆炸式增长，如何优化 MySQL 数据库的性能成为了后端开发人员必须面对的关键课题。其中，索引优化在提升数据库查询效率方面起着举足轻重的作用。据统计，网页加载时间每增加 1 秒，转化率可能下降 7%，而数据库查询往往是性能瓶颈的关键所在。今天，我们就深入探讨 MySQL 数据库索引优化，为各位互联网大厂后端开发人员提供实用的技巧与方法。

来源：https://www.cnblogs.com/monkey-xuan/p/15858689.html

索引的创建以及删除

1.alter table

2.create/drop index

mysql> create index idx_b on t (cls_id);
ERROR 1072 (42000): Key column 'cls_id' doesn't exist in table

desc方式查看

mysql> desc students;
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| created_at | datetime(3)     | YES  |     | NULL    |                |
| updated_at | datetime(3)     | YES  |     | NULL    |                |
| deleted_at | datetime(3)     | YES  | MUL | NULL    |                |
| sno        | bigint          | YES  |     | NULL    |                |
| pwd        | varchar(32)     | NO   |     | NULL    |                |
| tel        | varchar(12)     | NO   |     | NULL    |                |
| birth      | datetime(3)     | YES  |     | NULL    |                |
| cls_id     | bigint          | YES  | MUL | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
show create table 方式查看
mysql> show create table students;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `sno` bigint DEFAULT NULL,
  `pwd` varchar(32) NOT NULL,
  `tel` varchar(12) NOT NULL,
  `birth` datetime(3) DEFAULT NULL,
  `cls_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_students_deleted_at` (`deleted_at`),
  KEY `idx_b` (`cls_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show index from students
    -> ;
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| students |          0 | PRIMARY                 |            1 | id          | A         |          45 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| students |          1 | idx_students_deleted_at |            1 | deleted_at  | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| students |          1 | idx_b                   |            1 | cls_id      | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.04 sec)

以下是针对不同数据库系统的 SQL 语法差异总结，按功能分类展示：

一、基础查询

1. 分页查询