1.mysql体系结构概览
连接层:主要完成一些类似连接处理、授权认证,及相关的安全方案。
服务层:主要完成大多数核心服务功能,如sql接口,并完成缓存的查询,sql的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。在该层,服务器会解析查询并创建相应的内部 解析树,并对其完成相应的优化如确定表的查询的顺序,是否利用索引等,最后生成相应的执行操作。如果是 select语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层 :存储引擎层, 存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
存储层 :数据存储层, 主要是将数据存储在文件系统之上,并完成与存储引擎的交互。
2.存储引擎
和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。 存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。存储引擎是基于表的,而不是基于库的。 所以存储引擎也可被称为表类型。
2.1 InnoDB存储引擎
innoDB存储引擎是mysql默认的存储引擎,支持事务、外键、行锁。
2.2 MyISAM存储引擎
2.3 MeMory存储引擎
3. 存储引擎的选择
- InnoDB : 是Mysql的默认存储引擎,用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询意外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定, 还 可以确保事务的完整提交和回滚,对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB是最合适的选择。
- MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
- MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据环境下,可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速得到访问结果。
- MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制,并且通过将不同的表分布在多个磁盘上,可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。
4. SQL优化步骤
4.1 explain(desc)
补充:
id:
- id 相同表示加载表的顺序是从上到下。
- id 不同id值越大,优先级越高,越先被执行。
- id 有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越 大,优先级越高,越先执行。
select_type:
extra:
4.2 profile
4.3 trace优化器
set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
5.索引的使用
5.1 避免索引失效
1.全值匹配 ,对索引中所有列都指定具体值
2.. 最左前缀法则
3.范围查询右边的列,不能使用索引
4.不要在索引列上进行运算操作, 索引将失效
5.字符串不加单引号,造成索引失效(由于,在查询是,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。 )
6.尽量使用覆盖索引,避免select * 。尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select *
8.以%开头的Like模糊查询,索引失效。解决方法:通过覆盖索引来解决
9.如果MySQL评估使用索引比全表更慢,则不使用索引
10.is NULL , is NOT NULL 有时索引失效。
11. in 走索引, not in 索引失效。
12.单列索引和复合索引。 (尽量使用复合索引,而少使用单列索引 ,创建复合索引)
6. SQL优化
6.1 大批量插入数据
mysql> load data local infile '文件地址' into table '表名' fields terminated by '分隔符' lines terminated by '\n';
1.主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键将可以利用这点,来提高导入数据的效率。
2.关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率
6.2 插入数据
6.3 order by优化
1.第一种是通过对返回数据进行排序,也就是通常说的 fifilesort 排序,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
2.第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。
6.4 group by的优化