mysql数据量多如果优化 mysql数据库优化及sql调优

转载

墨色天香 2023-09-22 15:40:21

文章标签 mysql数据量多如果优化 mysql 数据库 database 服务器 文章分类 MySQL 数据库

前面我们讲了如何创建索引以及哪些情况下该创建索引。现在我们来说一下sql优化中的索引如何优化。

首先我们需要了解都有哪些纬度可以进行数据库调优：

①索引失效，没有充分利用到索引--索引建立

②关联查询太多join（设计缺陷或不得已的需求）--sql优化

③服务器调优以及各个参数设置（缓冲、线程数等）--调整my.cnf

④数据过多--分库分表

关于数据库调优的知识点非常分散，看了很多的文档介绍也都是零零散散。不同的DBMS，不同的公司不同的职位不同的项目遇到的问题也是不尽相同。

虽然sql查询优化的技术有很多，但是大方向上完全可以分为物理查询优化和逻辑查询优化两块。

①物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。

②逻辑查询优化是通过sql等价变换提升查询效率，直白说就是换一种查询写法执行效率可能更高。

这篇文章就来讲一讲如何进行索引优化

1.我这里准备两张表学员表插 50万条，班级表插 1万条。

CREATE TABLE `class` 
( `id` INT(11) NOT NULL AUTO_INCREMENT,
 `className` VARCHAR(30) DEFAULT NULL, 
`address` VARCHAR(40) DEFAULT NULL, 
`monitor` INT NULL ,
 PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
CREATE TABLE `student` ( `id` INT(11) NOT NULL AUTO_INCREMENT,
 `stuno` INT NOT NULL , 
`name` VARCHAR(20) DEFAULT NULL, 
`age` INT(3) DEFAULT NULL, 
`classId` INT(11) DEFAULT NULL, 
PRIMARY KEY (`id`) 
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

mysql数据量多如果优化 mysql数据库优化及sql调优_服务器

2.索引失效案例

mysql中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效的访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

①使用索引可以快速定位表中某条记录，从而提高数据库查询的速度，提高数据库性能。

②如果查询时没有使用索引，查询语句就会扫描表中所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都（这里以innodb搜索引擎为例）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么优化？基于开销，它不是基于规则，也不是基于语义。怎么样开销小就怎么来。另外，sql语句是否使用索引，跟数据库版本。数据量、数据选择度都有关系。

2.1全值匹配

系统中经常出现的sql语句如下

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 and name='abc';

在建立所以前执行，注意关注时间

mysql数据量多如果优化 mysql数据库优化及sql调优_database_02

建立索引

create index idx_age_classid_name on student(age,classId,name);

建立索引后再执行

mysql数据量多如果优化 mysql数据库优化及sql调优_mysql数据量多如果优化_03

可以看到，创建索引前的查询时间是0.19秒，创建索引后的查询时间显示为0.00，索引帮助我们极大的提高了查询效率。

注意：对于查询条件中使用and来进行连接且条件中为等值连接的。where中的顺序无关。

mysql数据量多如果优化 mysql数据库优化及sql调优_database_04

2.2最佳左前缀法则

在mysql建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

比如上面我们创建了联合索引create index idx_age_classid_name on student(age,classId,name);索引的顺序为age classId name

mysql数据量多如果优化 mysql数据库优化及sql调优_服务器_05

上面只用到了age进行索引。

mysql数据量多如果优化 mysql数据库优化及sql调优_mysql_06

上面没有用到任何索引

mysql数据量多如果优化 mysql数据库优化及sql调优_mysql数据量多如果优化_07

上面用到了完整的联合索引的所有字段

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

小结：mysql可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要是用索引必须按照索引建立时的顺序，一次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第一个字段时，多列索引不会被使用。

2.3主键插入顺序

对于一个使用innodb存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点上的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插入，而如果我们插入的主键值忽大忽小的话，就比较麻烦了。假设某个数据页存储的记录已经满了，它存储的主键值在1--100之间

mysql数据量多如果优化 mysql数据库优化及sql调优_mysql_08

如果此时插入一条主键值为9的记录，那它插入的位置就是如图

mysql数据量多如果优化 mysql数据库优化及sql调优_数据库_09

可这个数据页已经满了，再插入进来怎么办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移到信创建的这个页中。页面分裂和记录一位意味着性能损耗。所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录主键值依次递增，这样就不会发生损耗了。所以我建议：让主键具有auto_increment，让存储引擎自己为表生成主键而不是我们手动插入。这样的主键占用空间小，顺序写入，减少页分裂。（后面我们还会介绍自增长的劣势）

2.4计算、函数导致索引失效

先建立一个关于name的索引

create index name_index on student(name);

我们看看下面两个语句的执行时间

mysql数据量多如果优化 mysql数据库优化及sql调优_数据库_10