sql server 创建唯一性非聚集索引语句_MySQL优化之索引

使用Navicat for MySQL操作索引

1.选中需要加索引的表,点击设计表

2.选择哪些列需要建立索引,可以通过添加索引的方式给一个表添加多个索引

查看SQL执行计划

强调:

在SQL语句执行之前,会专门有个叫查询优化器的组件对我们写的SQL进行优化和调整,然后生成执行计划,也就是说,最终执行的SQL不一定是我们当初自己写的SQL,如果出现这个情况大家不要惊讶!

语法:

explain 查询语句;

其中type是我们需要优化的:其值常见的有以下几个,按照执行性能排列为

systme > const > eq_ref > ref > range > index > all

const: 根据主键索引或者唯一索引查询到的结果

explain select id,name from employee where id = 1;

ref: 使用非唯一性索引做查询,返回匹配的记录行,常见于多表查询中

explain select * from department d join employee e on d.id = e.dept_id;

range: 索引做范围查询,常见于、>=、between等操作符

explain select age from employee where age >= 20 and age <= 30

index: 索引全查询,MySQL遍历整个索引来查找匹配的行。

explain select age from employee where age >= 20

注意:

SQL优化的目标之一要把type优化在ref到index之间,该值没有优化的情况下一般都是all

其他列的含义可以参考博客: https://www.cnblogs.com/clphp/p/5403215.html

适当使用索引

建立索引的原则

较频繁的作为查询条件的字段应该创建索引,如:登录操作唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件 作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)更新非常频繁的字段不适合创建索引,原因:索引有维护成本不会出现在where 子句中的字段不该创建索引索引不是越多越好;(只为必要的列创建索引)

join的原则

注意:不要使用隐式连接,原因在于SQL的执行顺序

数据量小的表写在join的左边,数据量大的表写在join的右边MySQL中join都是通过Nested Loop Join来实现,简单理解为循环嵌套,应该数据量少的表作为外层循环,数据量大的表作为内层循环,然后合并结果优先优化Nested Loop的内层循环保证join语句中被作为连接条件的字段已经建立了索引扩大缓冲区的大小,容纳更大的查询数据(在配置文件中配置,DBA来做)

避免索引失效(忽略查询优化器)

where语句中索引列参与算术计算,该索引失效where语句中索引列参与函数运算,该索引失效where语句中使用in运算符有时会让索引失效where语句中做不等于( != , <> )运行,该索引失效where语句中发生类型转发,该索引失效where语句中模糊查询时以%开头,该索引失效在复合索引的使用时跟声明时顺序不一致或者中间有列的缺失,该索引失效如:声明了(a,b,c)的复合索引,但是在用是时候中间有列的缺失where a = xx and c = xx中间缺失了b,所以无法使用该复合索引,只要不是缺失中间列,其他情况索引均有效