mysql流程
查询优化
常见分析手段:
1. 慢查询
2. EXPLAIN分析查询
索引的使用
索引类型
- B-Tree索引(重点掌握)
使用较多,二叉树,数据都存放在叶子节点中,优势在于查找的最短路径相同。 - R-Tree索引
使用较少,优势在于范围查找 - Hash索引
使用较少,主要在Memory存储引擎中使用。 - Full-text索引(全文索引)
使用较少,只有MyISM存储引擎支持,
创建索引
- 较频繁的作为查询条件的字段应该创建索引。
- 唯一性较差的字段不适合单独创建索引,即使频繁作为查询条件。
- 更新非常频繁的字段不适合作为索引。
- 不会出现在where子句中的字段不适合创建索引。
索引能够极大地提高数据检索效率,也能改善排序分组操作的性能,但是不能忽略:索引是完全独立于基础数据之外的一部分数据,更新索引会带来IO量和调整索引所致的计算量的资源消耗。
使用索引
- 使用联合索引
mysql可以为多个字段创建索引(这就叫联合索引),对于联合索引,只有使用了这些字段中的第一个字段时,索引才会生效 - 使用OR关键字的索引
查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,索引才生效。
存储优化
mysql存储引擎
- MyISAM存储引擎
非事务引擎,适合查询频繁的应用。但是Redis等非关系型数据库查询速度更快,所以很少使用。 - InnoDB存储 引擎(使用较多)
事务安全的存储引擎,适合大量增删改操作的应用。提供了事务、回滚、锁等特性 - MyISAM和Innodb的区别
1.MyISAM是非事务安全的,而Innodb是事务安全型的。
2.MyISAM锁的粒度是表级,而Innodb支持行级锁定。
3.MyISAM支持全文类型索引,而Innodb不支持全文索引。
4.MyISAM相对简单,效率上要优于Innodb。小型应用可以考虑MyISAM。
5.MyISAM表是保存成文件的形式,在跨平台的数据转移过程中会比较简单。
6.Innodb更安全。
优化措施
存储数据时,影响存储速度的主要是 索引、唯一性校验、一次存储的条数 等 。
- MyIsam存储优化
- 禁用索引:在大量数据插入时,先禁用索引,插入完成后,在开启索引。
- 禁用唯一性检查。同上
- 批量插入数据。一次性插入多条数据之后再提交。
- Innnodb存储优化
- 禁用唯一性检查
- 禁用外键检查
- 禁止自动提交: 多个操作后再进行提交
数据库结构优化
优化表结构
- 尽量将表字段定义为NOT NULL约束,因为mysql中含有空值的列很难进行优化。
- 数值类型的比较比字符类型的比较简单得多,尽量使用最小、最简单的数据类型。
- 单表不要有太多字段
- 适当加入冗余,以提高查询效率。
表拆分
- 水平拆分
表中行数很多时,可以进行水平拆分。
对于多个表的查询,可以使用动态数据源,或者使用 MyCat(一种数据库中间件) - 垂直拆分
表字段太多时,可以进行垂直拆分。 可以将经常使用的字段放在一张表中,不经常使用的字段放入另一张表中。缺点是要保持多个表的同步。
分区
如日志查询
mysql支持的四种模式的分区:range分区、List预定义列表分区、hash分区、key键值分区。
读写分离
数据并发量大时,且是读多写少,这是可以采用读写分离。可以设置一个写库(主库),多个读库(从库),多个读库从写库中同步数据。
数据库集群
硬件优化
- 配置较大的内存
- 磁盘IO:使用SSD、机械硬盘使用转速高的。。等等
- cpu
mysql缓存
- 全局缓存
- 局部缓存
- 其他缓存
查询到完全相同的sql时,就会用到查询缓存。
要注意:一旦修改数据库表,那么和该表有关的所有缓存都会被清除。