Mysql

最新推荐文章于 2024-08-09 11:25:49 发布

盐上海

最新推荐文章于 2024-08-09 11:25:49 发布

阅读量163

点赞数

分类专栏：笔记文章标签： mysql 索引

本文链接：https://blog.csdn.net/weixin_45386328/article/details/117401517

版权

笔记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

mysql数据库的常见优化手段有哪些

三部分：sql语句的优化，索引的优化，表的优化

1.sql语句的优化：
查询时尽量写全要的字段，
连接效率大于子查询
多使用explain分析
多表连接尽量小表驱动大表
千万级分页使用limit
经常使用的查询，开启缓存

2.索引的优化
列含有NULL值不要设置索引，有NULL值不会使用索引
尽量使用短索引
经常在where使用的列最好设置索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要使用not in和<>操作
尽量不要在列上进行运算（函数操作和表达式操作）
对排序、分组、联合查询频率高的字段创建索引；

如：某个表(假设表名为Asset)，存在多个字段(AssetId, AssetName, brand, assetStatus, maintainFlag, startDate)，其中需要对AssetId,AssetName字段进行查询，对brand字段进行分组，对startDate字段进行排序，此时可以创建多列索引
index index_name (AssetId, AssetName, brand, startDate); #index_name为索引名

3.表的优化
表的字段尽可能用NOT NULL
字段长度固定的表查询会更快
把数据库的大表按时间或一些标志分成小表
将表分区

explain

type列：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL。
Extra列：关于MySQL如何解析查询的额外信息。

eq_ref: 在连接中，MySQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref: 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary: 看到这个的时候，查询需要优化了。这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

mysql数据库引擎

在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。

ISAM：ISAM执行读取操作的速度很快，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了

MyISAM：提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

HEAP：HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

InnoDB：尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。

MyISAM与InnoDB的区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

以下是一些细节和具体实现的差别：

InnoDB不支持FULLTEXT类型的索引。
InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

一般来说，MyISAM适合：
(1)做很多count 的计算；
(2)插入不频繁，查询非常频繁；
(3)没有事务。

InnoDB适合：
(1)可靠性要求比较高，或者要求事务；
(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建
让所有的灵活性成为可能的开关是提供给ANSI SQL的MySQL扩展——TYPE参数。MySQL能够让你在表格这一层指定数据库引擎，所以它们有时候也指的是table formats。下面的示例代码表明了如何创建分别使用MyISAM、ISAM和HEAP引擎的表格。要注意，创建每个表格的代码是相同的，除了最后的 TYPE参数，这一参数用来指定数据引擎。

mysql数据增量大的系统，预计运维三年如何优化？

设计表结构选数据类型读写分离缓存机制高效sql 静态页面
1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
2、选择合适的表字段数据类型和存储引擎，适当的添加索引。
3、MySQL 库主从读写分离。
4、添加缓存机制，比如 memcached，apc 等
5、书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.
6、不经常改动的页面，生成静态页面。

mysql事务

1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么全部不执行。
2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。
4、持久性(Durability):对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。

事务隔离级别：
未提交读
提交读
可重复读
可串行化

索引

索引本质是一种排好序的数据结构。

索引分类：

Hash索引：单条记录查询效率高，时间复杂度为1，但是并不是常用的，因为只适合精确查找，不适合范围查找
二叉树
时间复杂度O(n)，度为2，左小于根小于右
极端情况下会退化成链表
所以有平衡二叉树，根节点会变，复杂度由树高度决定
B树

节点包含了子节点的地址节点中还会包含数据，树越深磁盘IO越大，效率同样很低，可以设计多路，逐渐退化成有序数组，做文件系统比较多，因为文件系统索引都在磁盘，数据量大的话，B树的多路存储威力很明显，每次加载一个节点进入内存，依次往下找。
B+树 MySQL常用

在 B+ 树中，所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子结点只存储key的信息，这样可以大大减少每个节点的存储的key的数量，降低B+ 树的高度