自己做的网站怎么管理用户,爱站网爱情电影网,直接用ip地址的网站怎么做,app开发工具中文版文章目录 索引属性限定索引语句高性能 SQL数值类型Explain 索引属性限定
避免索引字段使用 null
B 树索引必须先进行比较当前值后再确定下一步查找范围#xff0c;null 值只能全盘扫描。包含 null 值的列无法作为索引#xff0c;聚合索引中只要有一列存在 null 就无法使用索… 文章目录 索引属性限定索引语句高性能 SQL数值类型Explain 索引属性限定
避免索引字段使用 null
B 树索引必须先进行比较当前值后再确定下一步查找范围null 值只能全盘扫描。包含 null 值的列无法作为索引聚合索引中只要有一列存在 null 就无法使用索引。
避免使用空值判断
避免使用 !、、NOT、is null、is not null、in、not in 操作符输入范围不确定会进行全盘扫描。
避免在索引字段上使用函数或表达式操作
索引保存的是原数值对索引字段使用函数或表达式操作会导致引擎放弃使用索引而进行全表扫描。算数函数加减乘除、求绝对值、求余、四舍五入。字符串拼接、长度、大小写、字符替换、字符串截取、日期。转换函数CAST类型转换。
在 WHERE/ORDER BY 添加索引
在 SELECT 输出栏添加索引没有意义应该在选择条件 WHERE、ORDER BY 上添加索引。
前缀索引
使用某个字段中字符串的前几个字符建立索引减小索引字段大小提高索引的查询速度。
覆盖索引
从二级索引中查询得到记录不需要通过聚簇索引查询获得避免回表操作。
自增主键
自增主键每次插入的新数据都会按顺序添加到当前索引节点不需要移动已有的数据。当页面写满就会自动开辟一个新页面。非自增主键每次插入主键的索引值都是随机的可能会插入到现有数据页中间甚至需要从一个页面复制数据到另外一个页面称为页分裂。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑影响查询效率。
索引语句
避免使用 select *
会进行全盘扫描推荐使用具体的查询字段建立索引提高效率。
复杂子查询使用 exists 替代 in
in 会先执行子查询保存到加索引的临时表再和外主表做笛卡尔积再通过条件筛选数据加入结果集适用于子查询简单场景。exists 会先遍历外主表每次遍历时在子表根据索引匹配查找。适用于外表为小表用小表驱动大表子查询表越小效率越高。
使用 between 替代 in
对于连续的数值能用 between 就不要用 inin 不走索引。
使用 order by 需要添加索引
order by 尽量不使用若使用要对使用的字段添加索引。order by 避免使用计算表达式。
避免 or 导致索引失效
or 进行条件判断所有字段必须都添加索引否则会全盘扫描。or 后的字段可以单独添加索引防止索引失效。
高性能 SQL
使用 unionall 替代 union
union 会在结果集合并后做排序和唯一性过滤。unionall 避免合并后排序可以通过数据源自身保证唯一性。
多用 limit
limit 读取固定数量的记录提高查询效率。limit 数据量过高时先通过 where 设定索引起始位置再用 limit 限定数量进行分页。limit 数据量过多时使用 between 设置索引范围替换 limit。
使用批量插入
批量插入比单条插入效率高。insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);
group by 避免排序
group by 默认会对结果自动排序通过添加 order by null 避免排序。group by 的字段最好添加索引可以避免创建临时表。having 在检索出所有记录后再对结果过滤通过 where 提前过滤字段。
left join 左表使用小表
left join 返回主表所有字段。右表非空时left join inner join。left join 主表作为驱动表左表应选择小表或使用 where 过滤字段左表太大循环次数越多。
like 使用 左 like 效率更高LIKE A%。
数值类型
合理使用数值型字段
数据库字段使用字符型存储引擎处理查询时会逐个比较字符降低低查询性能数值型数据只需要比较一次。
合理使用 varchar
char、varchar 都用于存储字符类型数据。char 类型实际存储的字符长度小于其定义规格会在尾部用空格补齐对于小的字符串数值使用 varchar 可以节省空间。
Explain
EXPLAIN 执行计划只能用于 select 语句前。主要字段type、key、rows。
mysql explain select * from actor;
-------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
-------------------------------------------------------------------------------id 执行编号语句执行顺序值越大语句优先级越高越先执行有几个 select 就有几个 id。select_type 查询类型普通查询、联合查询、子查询。 SIMPLE简单查询不包含子查询没有使用UNION、UNION ALL、DISTINCT、GROUP BY、HAVING、LIMIT等关键字。PRIMARY表示查询中包含一个或多个子查询。MySQL 会先执行主查询再执行子查询。SUBQUERY表示查询中的第一个子查询子查询包含在 SELECT 列表中的子查询或 WHERE 子句中的子查询。DERIVED表示查询中的子查询派生表。MySQL 会先执行子查询然后将结果存储在一个临时表中再执行主查询。UNION表示将多个查询的结果集合并成一个结果集。UNION RESULT表示查询中的UNION操作的结果集。DEPENDENT SUBQUERY表示查询中的子查询依赖于外部查询的结果集。MySQL 会根据外部查询的结果集来执行子查询。DEPENDENT UNION表示查询中的 UNION 操作依赖于外部查询的结果集。MySQL 会根据外部查询的结果集来执行UNION操作。DEPENDENT UNION RESULT表示查询中的 UNION 操作的结果集依赖于外部查询的结果集。MySQL 会根据外部查询的结果集来执行 UNION 操作。 type 访问类型至少达到 range 索引范围扫描最好能达到 ref 非唯一索引扫描。效率从好到坏依次是system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL。 system表只有一行记录等于系统表const 的特例平时不会出现。 const结果只有一行匹配。 eq_ref 唯一索引扫描使用非 null 值的唯一索引。 ref非唯一索引扫描使用普通、非主键、非唯一的索引最好能达到。 ref_or_null某字段既需要关联条件也需要null值。 index_merge在查询过程中需要多个索引组合使用 unique_subquery使用唯一索引类似 index_subquery。 index_subquery利用索引关联子查询不再扫描全表。 range索引范围扫描使用索引查询时限制了范围避免了全索引扫描适用的操作符 , , , , , , IS NULL, BETWEEN, LIKE, or IN() 。 index全索引扫描使用了覆盖索引或使用索引进行排序避免了数据的重排。 all全表扫描一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。 table 表名或别名。possible_keys 表中可以使用的索引查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。key 实际使用的索引为 null 表示没有使用索引。key_len 索引字段的长度。ref 查找使用的索引的列或常量。rows 估算查找所需记录需要读取的行数。extra 额外信息。 using filesort无法利用索引进行排序只能利用排序算法进行排序。using temporary建立临时表来保存中间结果查询完成之后把临时表删除。using index覆盖索引直接从索引中读取数据而不用访问数据表。using where使用 where 进行条件过滤。using join buffer使用连接缓存。impossible wherewhere 语句的结果总是 false。 partitions 如果查询是基于分区表的话会显示查询将访问的分区MySQL 8 新增。filtered 按表条件过滤的行百分比。 rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数前一个表指 explain 中的 id 值比当前表 id 值小的表MySQL 8 新增。 高性能SQL SQL优化1 SQL优化2 SQL优化3 SQL优化4 SQL
Explain Explain结果字段
