索引的分类
主键索引
唯一索引
常规索引
全文索引
聚集索引,每个表必须有,且只有一个,优先使用主键,无主键时取第一个唯一索引,再无时会自动生成rowid作为聚集索引
二级索引
www.cs.usfca.edu – 教育网站,数据结构动画显示过程。
索引的设计原则
- 数据量大,且查询比较频繁的表需建立索引。
- 针对查询条件where,Order by,group by操作的字段建议索引。
- 尽量选择区分度高的列作为索引字段,尽量建立唯一索引。
- 尽量使用联合索引,减少单列索引,查询时联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,不是多多益善,索引越多维护的代码越大,会影响增删改的效率。
- 如果索引列不能存储null,请在创建表时使用not null约束,当优化器知道每列是否包含null值时,它可以更好地确定哪个索引最有效地用于查询。
索引使用原则
- 最左前缀法则,联合索引如果跳过了联合索引中某列,索引失效。
- 复合索引,出现范围查询,索引失效。
- 索引列上进行运算操作,会导致索引失效
- 字符串不加单引号,会导致索引失效
- 模糊查询,尾部模糊索引可以正常使用,首部模糊导致失效。
- or连接的条件,用or分割开的条件,如果or前的条件中列有索引,而后面的列列索引,那么涉及的索引都不会被引用到。解决办法,or前后字段均加索引。
- 索引使用,如果mysql评估使用索引比全表扫描慢,则不使用索引。
sql提示
sql提示是优化数据库的一个重要手段,简单来说,就是在sql语句中加入一些人为的提示来达到优化操作的目的。
select * from table_name use index(idx_user_pro) where profession = '软件工程'
-- use index 建议使用索引
-- ingnore index 忽略索引
-- force index 强制索引
覆盖索引
尽量使用覆盖索引,查询使用索引,并且需要返回的列在该索引中已经全部能够找到,减少select *
using index condition:查找使用了索引,但需回表查询数据,查询性能会下降
using where
using index
前缀索引
字段如果是很长的字符串,创建索引会导致i/o性能很差,查询效率非常低。
create index idx_name on user_info(e_mail(5)) -- 基于e_mail字段的前5个字符创建前缀索引
explain 执行计划分析
- id值相同,从上到下,ID值越大,越先执行。
- select_type simple,即简单表,不使用表连接或子查询;primary 主查询,即外层的查询;union union中的第二个或者后面的查询语句;subquery select/where之后包含的子查询
- type 表示连接类型,性能由好到差的连接类型为 null,system,const,eq_ref,ref,range,index,all。
select ‘a’ 相当于null
访问系统表,相当于system
主键或unique索引,相当于const
非唯一索引 相当于ref
用了索引,但对索引进行了全量搜索 index - possible_key 显示可能用到的索引
- key 实际到的索引,如果为null 则表示没有使用到索引
- key_len
- rows 执行查询的行数,是一个预估值
- filtered,返回结果的行数战胜读取行数百分比,100%最好
- extra,执行时额外信息
SQL优化
- insert 优化
1、尽量批量插入数据
2、手动提交事务
3、大数据量时,可以使用load指令进行更大批量的加载数据。 - 主键优化
innoDB存储引擎中,表数据是根据主键顺序组织存放的,称为索引组织表(Index Organized table IOT)
乱序主键会导致而分裂,数据删除会导致页合并
所以主键设计原则建议:
1、满足业务需求情况下,主键长度尽量降低
2、插入时,尽量顺序插入,选择使用Auto_Increment自增主键
3、尽量不要使用uuid 或其他自然主键例如身份证号
4、业务操作时,避免对主键的修改 - order by 优化
using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果排序都叫filesort排序
using index:通过有序索引顺序扫描直接返回有序数据,这种情况为using index,不需要额外排序,操作效率高。
创建索引时默认为升序,可以指定索引排序,例如
create index idx_age_pro on tb_user(age asc,pro desc);
1、根据排序字段建立合适的索引,多个字段排序时,也遵循最左前缀法则。
2、尽量使用覆盖索引
3、多字段排序,一个升一个降,此时需要注意联合索引在创建时的规则
4、如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小,sort_buffer_size(默认256k)
- group by 优化
分组操作时,可以通过索引提高效率
分组操作时,索引的使用也需满足最左前缀法则 - limit 优化
一般分页查询,通过创建索引能比较好的提高性能,可以通过覆盖索引加子查询的形式进行优化。 - count 优化
count():innoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行累加。
count(1):innoDB引擎会遍历整张表,但不取值,服务层对于返回的每一行,放一个数字进去,直接按行累加。
count(主键):遍历整张表,把每一行的主键id都取出来,返回给服务层,服务层拿到主键后,直接按行累加。
count(字段):没有not null约束,innoDB引擎会遍历整张表,把每一行的字段值取出来,返回给服务层,服务层判断是否为null,不为null时计数累加。有not null约束,innoDB引擎会遍历整张表,把一行的字段值都取出来,返回给服务层,直接按行进行累加。
从效率角度看,count(字段)<count(主键)<count(1)约等于count(),所以尽量使用count(*) - update 优化
更新字段后一定要根据索引字段条件进行更新,不用索引字段会加表锁。