mysql百万级大量数据查询优化-学习总结

MySQL是典型的 C/S架构（客户端/服务器架构），客户端进程向服务端进程发送一段文本（MySQL指令），服务器进程进行语句处理，然后执行并返回结果。

MySQL 优化的官方解释通常涉及查询优化、索引优化、服务器设置优化、硬件优化等方面。以下是一些常见的优化方法：
1、查询优化：
使用正确的列和表的别名。
避免SELECT *，只选择需要的列。
使用合适的JOIN类型（INNER JOIN, LEFT JOIN）。
避免子查询，尽量使用JOIN。
使用WHERE子句过滤不必要的数据。
避免查询大量的文本字段
2、索引优化：
为经常查询的列创建索引。
避免对大型文本字段建立索引。
使用合适的索引类型（如全文索引）。
定期重构或优化现有的索引。
3、服务器设置优化：
调整缓冲区大小，如innodb_buffer_pool_size。
调整连接时间和资源限制。
配置合适的查询缓存。
4、硬件优化：
使用更快的硬盘。
增加内存以增强缓存的效率。
使用更强大的CPU。
5、数据库结构优化：
数据规范化，避免数据冗余。
使用合适的数据类型。
定期维护：
优化表（OPTIMIZE TABLE）。
检查和优化表（CHECK TABLE和OPTIMIZE TABLE）。
备份数据库。

 执行一条SQL语句首先会在服务器与客户端之间建立连接，然后对SQL语句进行解析(在MySQL8.0中删除了命中查询缓存返回数据的操作)，解析会进行如下操作：

词法分析——执行SQL语句——最后由存储引擎（InnoDB，MyISAM，Memory等）处理数据

 存储引擎：

（1） Memory
Memory存储引擎，以前也称堆引擎，它将所有数据存储在RAM内存中，以便快速访问。特点：
把数据放在内存里面，读写的速度很快。但是，数据库重启或者崩溃，数据会全部消失；
只适合做临时表。
（2） MylSAM
应用范围比较小，表级锁限制了读/写性能，因此在Web和数据仓库配置中，通常用于只读或以读为主的工作。特点:
支持表级别的锁（插入和更新会锁表），不支持事务；
拥有较高的插入（insert）和查询（select）速度；
存储了表的行数（count速度更快）。
（3） InnoDB
MySQL 5.7及更新版中的默认存储引擎。InnoDB是事务安全（兼容ACID），它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁和Oracle风格的一致非锁读提高了多用户并发性。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。特点：
支持事务，支持外键，因此数据的完整性、一致性更高；
支持行级别的锁和表级别的锁；
支持读写并发，写不阻塞读（MVCC）；
特殊的索引存放方式，可以减少IO，提升査询效率。

总结一下如何查看和修改MySQL数据库表存储引擎：
1、查看数据库所能支持的存储引擎：show engines;
2、查看某个数据库中某个表所使用的存储引擎：show table status from db_name where name='table_name';（注：将where条件去掉后可以查看某个数据库中所有表的存储引擎情况）
3、修改表引擎方法：alter table table_name engine=innodb;
4、关闭Innodb引擎方法：通过“net stop mysql”命令关闭mysql服务；找到mysql安装目录下的my.ini文件，将里面default-storage-engine=INNODB改为default-storage-engine=MYISAM，将#skip-innodb改为skip-innodb；最后通过“net start mysql”命令启动mysql服务。