MYSQL优化（二）：索引优化、存储引擎优化、表结构优化等

最新推荐文章于 2024-05-30 08:48:39 发布

时间慢慢

最新推荐文章于 2024-05-30 08:48:39 发布

阅读量460

点赞数

分类专栏： mysql 文章标签：优化 mysql

本文链接：https://blog.csdn.net/xkx_1223_xkx/article/details/77946098

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引

索引类型：
1、B-Tree索引（重点掌握）  使用较多，二叉树，数据都存放在叶子节点中，优势在于查找的最短路径相同。
2、R-Tree索引  使用较少，优势在于范围查找
3、Hash索引  使用较少，主要在Memory存储引擎中使用。（Hash索引为默认）
4、Full-text索引（全文索引）  使用较少，只有MyISM存储引擎支持，
创建索引：
原则：
1、较频繁的作为查询条件的字段应该创建索引。
2、唯一性较差的字段不适合单独创建索引，即使频繁作为查询条件。
3、更新非常频繁的字段不适合作为索引。
4、不会出现在where子句中的字段不适合创建索引。
 索引能够极大地提高数据检索效率，也能改善排序分组操作的性能，但是不能忽略：索引是完全独立于基础数据之外的一部分数据，更新索引会带来IO量和调整索引所致的计算量的资源消耗。

使用索引：
1、使用联合索引  mysql可以为多个字段创建索引（这就叫联合索引），对于联合索引，只有使用了这些字段中的第一个字段时，索引才会生效（使用联合索引就可以忽略原则2，因为其他字段可以作为筛选）
2、使用OR关键字的索引  查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才生效。

存储优化：

存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等；
存储数据的优化，不同的存储引擎优化手段不一样，在mysql中常用的存储引擎为：
MyISAM和InnoDB

MyISAM存储引擎：
（用的比较少，因为其优势为查询快，但是比他快的还有很多，Redis非关系型数据库）
非事务性的引擎；（事务：要么同时成功要么同时失败，具有原子性）；—>不适合作为增删改；
提供高速存储和检索，以及全文搜索能力，适合做数据仓库等查询频繁的应用；

Innodb存储引擎：（mysql常用的）
事务安全；适合大量增删改查等操作；提供了具有事务（commit）、回滚（rollback）和崩溃修复能力和事务安全型表；

InnoDB提供了行锁（locking on row level），提高了高并发性能；
优势：设计目标是处理大容量数据库系统，它的CPU利用率是其他基于磁盘的关系型数据库引擎所不能比的。在技术上，InnoDB是一套放在MySQL后台的完整数据库系统；InnoDB在驻村中建立其专用的缓冲池用于高速缓冲数据和索引。

MyISAM和InnoDB的区别：
1.MyISAM是非事务安全的，而Innodb是事务安全型的。
2.MyISAM锁的粒度是表级，而Innodb支持行级锁定。
3.MyISAM支持全文类型索引，而Innodb不支持全文索引。
4.MyISAM相对简单，所以效率上要优于Innodb。小型应用可以考虑MyISAM。
5.MyISAM表是保存成文件的形式，在跨平台的数据转移过程中会比较简单。
6.Innodb更安全，可以在保证数据不丢失的情况下，切换非事务表到事务表；（alter table tablename type = innodb）

应用场景：
MyISAM管理非事务型表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的select查询，那么MyISAM是更好的选择
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的insert和update操作，则应使用innodb，提高多用户并发操作性能；

存储优化：针对的问题是索引、唯一性校验、一次存储的数据条数

MyISAM存储优化
1、禁用索引：对于非空表：插入数据时，会同时更新索引，影响插入速度；因此优化：在插入数据前禁用索引，插入完成后再开启索引；
禁用：alter table table_name disable keys；
开启：alter table table_name enable keys；
而对于空表的批量插入数据，是不需要上述操作的，因为它的索引是在导入数据后建立索引的；

2、禁用唯一性检查
唯一性检查也会降低插入记录的速度，可以在插入记录前禁用唯一性检查，插入完成后再开启；

禁用：set unique_checks = 0；
开启：set unique_checks = 1；

3、批量插入数据
Insert可以插入一条数据，也可以插入多条数据；因此减少commit的次数，加快插入速度；

4、load data infile：
批量插入数据时，这个语句比insert语句插入速度快很多；

InnoDB存储优化
1、禁用唯一性检查：同上
2、禁用外键检查：同理，对外键的检查也影响速度；
禁用：set foreign_key_checks = 0;
开启：set foreign_key+checks = 1;
3、禁止自动提交：
插入数据前禁止事务的自动提交，数据插入完成后再恢复，可提高插入速度。节省提交资源；

禁用：set autocommit = 0;
开启：set autocommit = 1;

数据库结构优化：

一、优化表结构：
1、尽量将表字段定义为not null 约束，因为MySQL对含有空值的列很难进行查询优化，null回使索引以及索引的统计信息变得复杂；
对于只包含特定类型的字段，可以使用enum、set等符合数据结构类型
数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小最简单的数据类型
尽量使用，tinyint、smallint、medium_int作为整数类型而不是int，如果非负加上unsigned；
Varchar的长度只分配真正需要的空间
尽量使用timestamp而不是datetime
单表不要有太多字段，建议在20以内（表拆分）
合理加入冗余字段可以提升查询效率；
2、表拆分
解决数据量太大：
垂直拆分：插入时使用事务，可以保证多个表的一致性。缺点是需要使用冗余字段，需要用join操作，我们在使用的时候可以分别取两次，避免join操作；
水平拆分：（找表的时候：动态数据源【比较复杂】、MyCat【数据中间件】）
3、分区
大数据处理后的产物。用分区查询速度；
MySQL支持4种分区：range分区、list预定义分区，hash分区，key键值分区；
4、读写分离
网站的大量并发；
实现读写分离，多设置几个数据库，关键点在于每个数据库数据的一致性；以写库为基准，写在写库改变，读库在写库中实现同步；
5、数据库集群
如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器不能承受了，就要考虑使用多台服务器实现写操作；
eg：使用Mycat搭建MySQL集群，对ID求3的余数，将数据分别存放在多个服务器上，由mycat负责维护集群节点的使用；