MySQL优化（语句、引擎）_若不需要事务支持并发相对较低数据修改相对较少以图为主数据一致性要求不是非-CSDN博客

本文链接：https://blog.csdn.net/xxxtgl/article/details/106578013

MySQL优化

存储引擎
- MyISAM
- InnoDB
数据库索引
Explain执行计划
数据库语句使用注意

存储引擎

聚簇索引

聚簇索引的叶子节点就是数据节点，而非聚簇索引的。
一个表只能有一个聚簇索引
索引项的排序和数据行的存储排序完全一致，利用这一点，想修改数据的存储顺序，可以通过改变主键的方

非聚簇索引

一个表可以有多个非聚簇索引
叶子节点仍然是索引节点，只不过有指向对应数据块的指针

InnoDB和MyISAM对比：
1、事务和外键

InnoDB具有事务，如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2、全文索引

Innodb不支持全文索引
3、锁

mysql支持三种锁定级别，行级、页级、表级;
MyISAM支持表级锁定，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)

InnoDB支持行级锁，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，注意间隙锁的影响
4、存储

MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型， .frm文件存储表定义，数据文件的扩展名为.MYD，索引文件的扩展名是.MYI

InnoDB，基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小
5、索引

InnoDB（索引组织表）使用的聚簇索引、索引就是数据，顺序存储，因此能缓存索引，也能缓存数据

MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引
6、并发

MyISAM读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

InnoDB 读写阻塞与事务隔离级别相关

MyISAM

不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少（阻塞问题），以读为主
数据一致性要求不是非常高
尽量索引（缓存机制）
调整读写优先级，根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让insert数据都写入到尾部，减少阻塞
分解大的操作，降低单个操作的阻塞时间
降低并发数，某些高并发场景通过应用来进行排队机制
对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

InnoDB

需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
主键尽可能小，避免给Secondary index带来过大的空间负担
避免全表扫描，因为会使用表锁
尽可能缓存所有的索引和数据，提高响应速度
在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
避免主键更新，因为这会带来大量的数据移动

数据库索引

缺点：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的
当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用场景：

用在：改少查多，数据量多
where条件用不到的字段不创建索引

单列索引

普通索引：允许定义索引的列重复值和空值
唯一索引：允许为空值
主键索引：不允许为空值，不予许重复

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON

Explain执行计划

执行计划字段

 Select_type（查询类型） 
SIMPLE： 表示此查询不包含 UNION 查询或子查询
PRIMARY： 表示此查询是最外层的查询
SUBQUERY： 子查询中的第一个 SELECT
UNION： 表示此查询是 UNION 的第二或随后的查
Table 表名
partitions:匹配的分区
Type 表连接类型
	常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
	ALL(全表扫描)
	Index(扫描了索引树)
	range:只检索给定范围的行，使用一个索引来选择行
	ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
	eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 	unique key作为关联条件
	const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
	NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
Row：查询多少行
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

数据库语句使用注意

Count(*)
尽可能的使用 varchar/nvarchar 代替 char/nchae 

主查询数据大，用 In
子查询数据大，用 exists
exists ：将主查询的结果放到子查询结果中进行条件校验（如果子查询中有数据，则返回子查询中数据，否则返回空）

索引失效问题
不要在索引列上做任何操作（计算、函数、(自动or手动)类型转换）会导致索引失效而转向全表扫描
最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列，尤其是索引的头。
存储引擎不能使用索引中范围条件右边的列，遇到范围，则范围条件后的索引全部失效，所以最好不要在索引列中使用范围
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
mysql 在使用不等于(!= 或者<>)的时候无法使用索引，会导致全表扫描
is null 、is not null 也无法使用索引
Like %不要在第一个
字符串不加单引号索引失效
少用or，用它来连接时会导致索引失效