织梦做的网站打不开网页,西安响应式网站开发,关键词排名查询api,加大网站建设力度一、事务的ACID原则
序号原则说明1原子性#xff08;Atomicity#xff09;事务是数据库的逻辑工作单位#xff0c;事务中包括的诸操作要么都做#xff0c;要么都不做2一致性#xff08;Consistency#xff09;事务执行的结果必须是使数据库从一个一致性状态变到另一个一…一、事务的ACID原则
序号原则说明1原子性Atomicity事务是数据库的逻辑工作单位事务中包括的诸操作要么都做要么都不做2一致性Consistency事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态3隔离性Isolation一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的并发执行的各个事务之间不能互相干扰4持久性Durability持久性也称永久性指一个事务一旦提交它对数据库中数据的改变就应该是永久性的接下来的其他操作或故障不应该对其有任何影响
二、数据库设计的三大范式
序号范式说明1第一范式1NF确保每列保持原子性2第二范式2NF在满足第一范式的前提下非主属性完全依赖于码3第三范式3NF在满足第二范式的前提下非主属性不传递依赖于码
三、索引
序号索引类型说明1二叉树搜索树基础数据结构用于快速查找、插入和删除2红黑树平衡二叉搜索树保持树的平衡以维持操作的高效性3B树适用于大量数据的磁盘读写操作保持树的平衡4B树B树的变种非叶子节点不存储数据更适合作为数据库索引结构5索引概念数据库中用于提高数据检索效率的数据结构
四、SQL解析
序号内容说明1SQL解析SQL语句被数据库管理系统解析、编译和执行的过程
五、锁机制
序号锁类型说明1行锁锁定一行数据2表锁锁定整个表3范围锁锁定一定范围的数据4悲观锁假设最坏情况数据在修改前会被锁定5乐观锁假设最好的情况只在更新操作时检查数据是否被其他事务修改6读写锁分为读锁和写锁读锁允许多个读操作并发写锁则独占
六、JOIN查询
序号内容说明17种常见的JOIN查询包括INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN等2SQL语句展示具体的SQL JOIN查询语句3-9每种JOIN查询的说明与示例分别解释每种JOIN的用法及示例10Union和Union All介绍UNION和UNION ALL的区别及使用场景
七、索引优化
序号内容说明1索引分类单值索引、唯一索引、主键索引、复合索引2Explain性能分析使用EXPLAIN分析SQL查询性能3索引优化入门案例展示不同场景下的索引优化案例4索引失效分析分析导致索引失效的常见场景5分组排序优化优化ORDER BY和GROUP BY的性能
索引分类
序号索引类型说明1单值索引索引列中的单个值2唯一索引索引列中的值必须是唯一的允许NULL值但最多只能有一个3主键索引特殊的唯一索引一个表只能有一个主键且不允许NULL值4复合索引组合索引索引由两个或两个以上的列组成列的组合值必须唯一或满足特定条件
Explain性能分析
序号字段说明1sql执行的SQL语句2idSELECT的标识符如果查询包含子查询则会出现多个id3select_typeSELECT的类型SIMPLE, PRIMARY, SUBQUERY等4table输出行所引用的表5type连接类型ALL, index, range, ref, eq_ref, const/system等6possible_keys显示可能应用在这张表上的索引但不一定实际使用7key实际使用的索引8key_len使用的索引的长度9ref显示索引的哪一列或常数被用于查找值10rowsMySQL认为必须检查的用来返回请求数据的行数估计值11Extra包含不适合在其他列中显示但十分重要的额外信息如是否使用索引等
索引优化入门案例
1. 驱动表与被驱动表的选择
在连接查询如JOIN中优化器会决定哪个表作为驱动表驱动其他表进行连接的表哪个表作为被驱动表。选择正确的驱动表和被驱动表可以显著提高查询性能。通常驱动表应该是数据量较小、过滤条件较多、索引效果好的表。
案例 假设有两个表employees员工表包含10万条记录和departments部门表包含100条记录。如果要查询每个部门下的员工信息最好让departments表作为驱动表因为它更小可以更快地遍历。
SELECT employees.*, departments.department_name
FROM employees
JOIN departments ON employees.department_id departments.id
WHERE departments.region Asia;在这个例子中尽管employees表在JOIN条件中但如果departments.region Asia这个条件能显著减少departments表的结果集那么优化器可能会选择departments作为驱动表。
2. 单表索引优化
单表索引优化通常涉及为查询中经常作为条件、连接键或排序键的列添加索引。
案例 假设employees表有一个last_name列经常需要根据员工的姓氏来查询员工信息。
SELECT * FROM employees WHERE last_name Smith;为了提高这个查询的效率可以在last_name列上添加索引。
CREATE INDEX idx_last_name ON employees(last_name);3. 两表索引优化
在两表连接查询中优化索引可以显著提高性能。通常应该在连接键和过滤条件上添加索引。
案例 继续上面的employees和departments表的例子如果经常需要根据部门ID和员工的姓氏来查询信息可以在employees.department_id和departments.id如果尚未索引以及employees.last_name上添加索引。
-- 假设departments.id已经是主键因此默认有索引
CREATE INDEX idx_emp_dept_id ON employees(department_id);
CREATE INDEX idx_emp_last_name ON employees(last_name);SELECT employees.*, departments.department_name
FROM employees
JOIN departments ON employees.department_id departments.id
WHERE employees.last_name Smith;4. 三表及以上索引优化
对于涉及三个或更多表的连接查询索引优化的策略与两表类似但更复杂。通常需要考虑查询中所有表之间的连接键以及过滤条件。
案例 假设还有一个projects表项目表其中包含项目信息每个员工可以参与多个项目。现在需要查询参与特定项目且姓氏为’Smith’的员工及其部门信息。
SELECT employees.*, departments.department_name, projects.project_name
FROM employees
JOIN departments ON employees.department_id departments.id
JOIN projects ON employees.id projects.employee_id
WHERE employees.last_name Smith AND projects.project_name ProjectX;为了优化这个查询可以在employees.department_id、employees.id如果尚未作为主键索引、employees.last_name、projects.employee_id以及projects.project_name上添加索引。
-- 假设employees.id和departments.id已经是主键因此默认有索引
CREATE INDEX idx_emp_dept_id ON employees(department_id);
CREATE INDEX idx_emp_last_name ON employees(last_name);
CREATE INDEX idx_proj_emp_id ON projects(employee_id);
CREATE INDEX idx_proj_name ON projects(project_name);注意虽然索引可以显著提高查询性能但它们也会占用额外的磁盘空间并且会降低写操作的性能如INSERT、UPDATE、DELETE。因此在设计索引时需要权衡这些因素。
索引失效分析
序号场景说明1最佳左前缀法则复合索引中查询条件需要按照索引列的顺序进行2避免在索引字段上做计算如WHERE YEAR(column) 2023这会导致索引失效3避免在索引字段上做范围查询范围查询后的列将无法使用索引如WHERE a 10 AND b 24查询字段和索引字段尽量一致避免使用SELECT *尽量只选择需要的列5慎用IS NULL和IS NOT NULL对于索引列这些条件可能导致索引失效6LIKE的前后模糊匹配如LIKE %keyword%这将导致索引失效但LIKE keyword%则有效7使用UNION或UNION ALL代替OR在某些情况下UNION/UNION ALL可以更有效地利用索引
八、SQL优化
序号内容说明1SQL语句优化提供SQL语句优化的方法和技巧
序号内容说明1ORDER BY之前先使用WHERE等条件过滤掉不需要排序的数据减少排序的数据量2WHERE和ORDER BY所用到的索引尽量使用索引来加速WHERE和ORDER BY的操作3排序的方向必须一致如果WHERE条件中使用了索引的某一列进行排序确保排序方向一致
SQL语句优化
避免SELECT * 尽量指定需要查询的列减少数据传输量。使用表连接代替子查询在可能的情况下使用JOIN代替子查询可以提高查询效率。优化WHERE子句确保WHERE子句中的条件能够利用索引。使用LIMIT限制结果集如果只需要部分数据使用LIMIT来减少处理的数据量。合理使用聚合函数聚合函数如SUM, COUNT, AVG等
九、额外优化技术
序号内容说明1截取查询分析分析慢查询日志优化大量数据插入等场景2开启慢SQL查询日志监控并优化慢查询3show profile使用MySQL的show profile命令分析查询性能
