mysql架构+调优策略，看这一篇已足够

mysql架构+调优方法，看这一篇已足够

1.架构篇

架构信息说明

Connector 不同的连接对象

Connection pool 连接池

SQL Interface: SQL接口。接受用户的SQL命令

Parser: 解析器。用来专门解析sql，并将信息传入给不同模块

Optimizer: 查询优化器，优化查询sql查询语句

Cache和Buffer： 查询缓存，接下来会讲到该缓存作用

1.1SQL工作流程

1.2parser解析器工作流程

正式在这个过程中，会解析你的sql语句是否正确，并且进行关键词拆分，并在接下来传输给优化器。但优化器不一定会把该语句优化。

2 sql调优

2.1 问题分析

2.1.1 慢日志配置

sql作为第一与数据库交互的信息，是非常重要的。因此在首要阶段要先搞清楚，系统中是否存在，慢sql语句。慢语句日志一般都存放在sql文件的data文件中。

可以直接在数据库查询语句中使用语句来查询。

show VARIABLES like '%quer%'

slow_query_log 表示是否开启慢查询日志。（默认开启）

slow_query_log_file 慢查询日志文件名

long_query_time 慢查询阈值设置，查出为慢查询。此值直接设置全局参数可能无

效，建议测试时直接修改配置文件。

log_queries_not_using_indexes 是否记录不适用于索引的查询（前提 slow_query_log

开启）

在分析语句时，最好将数据库进行copy一份，防止在demo过程中出现问题。

找出几条慢查询语句

第一行,SQL 查询执行的时间。

第二行,执行 SQL 查询的连接信息，用户和连接 IP。

第三行,记录了一些我们比较有用的信息，如下解析:

- Query_time,这条 SQL 执行的时间,越长则越慢；

- Lock_time,在 MySQL 服务器阶段(不是在存储引擎阶段)等待表锁时间；

- Rows_sent,查询返回的行数；

- Rows_examined,查询检查的行数，越长就当然越费时间；

第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。

第五行及后面所有行（第二个# Time:之前）

,执行的 sql 语句记录信息。

2.1.2 explain分析查询语句

例

EXPLAIN SELECT * from jk_user where `UE_ID`= 1
EXPLAIN SELECT 投影列 FROM 表名 WHERE 条件 ## 模板

属性	描述
select_type	查询类型，主要用于区分普通查询、联合查询、子查询等。
table	查询的哪个表。
type	查询类型，SQL优化中非常重要的指标。从好到坏排序。一般要求至少是range级别，最好能达到ref级别。
	system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
key	实际使用了的索引。
rows	需要扫描的行数。

3 实际调优

索引失效

使用 LIKE 关键字的查询语句。在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

SELECT * FROM users WHERE username LIKE 'old%' -- 使用索引
SELECT * FROM users WHERE username LIKE '%lu' -- 不使用索引

索引列参与计算

如果对条件中的列进行了计算，则不会使用该列的索引。

SELECT * FROM users WHERE userage=20;-- 使用索引
SELECT * FROM users WHERE userage=10+20;-- 使用索引
SELECT * FROM users WHERE userage+10=30;-- 不会使用索引

索引列使用了函数

如果对条件中的列使用了函数，则不会使用该列的索引

SELECT * FROM users WHERE username=concat('old','lu'); -- 使用索引
SELECT * FROM users WHERE concat(username,'lu')='oldlu'; -- 不会使用索引

优化表超长的操作

#当表长超过100万，并且想做limit操作的时候，最好不要直接limit
#修改前
select * from user where 1 limit 1000000,200;
#修改后
select * from user wehre user_id > 1000000 limit 100;

索引

索引，类似于图书的目录一样，sql可以快速的从目录中找到自己想要的数据

创建场景

适合创建索引的字段

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑

在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在具备下述特性的列上创建索引：

第一、在经常需要搜索的列上，可以加快搜索的速度；

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范

围是连续的；

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的

排序，加快排序查询时间；

第六、在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照 select 的 where 条件来建立，比如： select 的条件是 where f1 and

f2，那么如果我们在字段 f1 或字段 f2 上建立索引是没有用的，只有在字段 f1 和 f2 上同时

建立索引才有用等

非适合

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些

列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反

2627

而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值

很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，

即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这

些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索

性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，

会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引类型

好的，以下是保留索引类型和特点的整合内容：

索引类型及特点

1. 单列索引

   • 普通索引：不设限制，提升查询速度。
   • 唯一索引：列值唯一，可包含null。
   • 主键索引：列值唯一，不可为null。

2. 组合索引

   • 多列索引，遵循最左前缀原则。

3. 全文索引

   • 适用于CHAR、VARCHAR、TEXT字段。
   • 抽取关键字建立索引，优化含LIKE的查询。

索引创建方法

• 直接创建索引：

  CREATE INDEX index_name ON table_name(column(length));
  

• 修改表结构添加索引：

  ALTER TABLE table_name ADD INDEX index_name ON (column(length));
  

• 创建表时同时创建索引：

  CREATE TABLE table_name (
    ...
    INDEX index_name (column(length))
  );
  

• 创建唯一索引：

  CREATE UNIQUE INDEX indexName ON table_name(column(length));
  

• 创建主键索引：

  CREATE TABLE table_name (
    ...
    PRIMARY KEY (id)
  );
  

• 创建全文索引：

  CREATE FULLTEXT INDEX index_content ON table_name(column);
  

索引管理

• 索引可以在建立表时直接指定，或后期添加。
• 主键约束自动带有主键索引，唯一约束自动带有唯一索引。
• 在MySQL中，对索引的查看和删除操作是所有索引类型通用的。

索引查看与删除

• 查看索引：

  SHOW INDEX FROM table_name;
  SHOW KEYS FROM table_name;
  

• 删除索引：

  DROP INDEX index_name ON table_name;
  ALTER TABLE table_name DROP INDEX index_name;
  

索引优化建议

• 考虑使用短索引（前缀索引）来提高查询速度和节省空间。

• 避免在大数据量的表上创建多种组合索引，以减少索引文件的膨胀。
  查看索引：

  SHOW INDEX FROM table_name;
  SHOW KEYS FROM table_name;
  

• 删除索引：

  DROP INDEX index_name ON table_name;
  ALTER TABLE table_name DROP INDEX index_name;
  

索引优化建议

• 考虑使用短索引（前缀索引）来提高查询速度和节省空间。
• 避免在大数据量的表上创建多种组合索引，以减少索引文件的膨胀。
• 优化查询语句，合理使用索引以提高效率。