MySQL索引
索引
定义
排好序的快速查找 数据结构
优点
提高查询效率,降低数据库的IO成本;
通过创建索引对数据进行排序,降低数据的排序成本,降低了CPU的消耗;
缺点
虽然索引提高了查询速度,降低了更新标的速度;
索引分类
单值索引:一个索引只包含单个列,一个表可以有多个单列索引;
唯一索引:索引列的值必须唯一,但允许有空值;
复合索引:一个索引包含多个列;
基本语法
-- 查看索引
show index from 表名
--创建索引(复合索引)
--create index StudentName_StudentNo_index on student(StudentName,StudentNo)
create index 索引名称 on 表名(列名1,列名2)
--删除索引
--drop index StudentName_index on student
drop index 索引名称 on 表名
什么情况下需要使用索引
1 主键自动建立唯一索引
2 频繁作为查询条件的字段应该创建索引
3 查询中统计或分组字段
4 频繁更新的字段不适合创建索引(因为每次更新不单单是更新了记录还会更新索引)
什么情况下不需要创建索引
1 记录太少
2 经常增删改的表(因为提高了查询速度,但是会降低更新表的速度,更新表时,MySQL不仅要保存数据,还要保存一下索引文件)
索引使用场景
当在连接多表查询时,如何正确的使用索引
左连接查询将索引建到右边;(LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有)
右连接查询将索引建到左边;
不能使用索引进行排序
如果表中索引出现了多列,要遵循最左前缀法则,值得是查询从索引的最左前列开始并且不跳过索引中间列(假设:A、B、C三个索引列,当查询BC或者B或者C时,索引会失效(带头大哥不能丢,第一个索引不能丢失)
当查询A和C时,只会使用A索引,C索引失效
当查询A、或者AC、或者AB时索引不会失效)
最好是表里的索引在查询时尽量都能使用上
导致索引失效原因
总结
最佳左前缀法则:
如果表中索引出现了多列,要遵循最左前缀法则,值得是查询从索引的最左前列开始并且不跳过索引中间列(假设:A、B、C三个索引列,当查询BC或者B或者C时,索引会失效(带头大哥不能丢,第一个索引不能丢失)
当查询A和C时,只会使用A索引,C索引失效
当查询A、或者AC、或者AB时索引不会失效)
最好是表里的索引在查询时尽量都能使用上
SQL 查询优化
查询时使用索引(查询列尽量使用索引列)
排序时使用索引(排序列尽量使用排序列)
使用explain查看当前查询语句索引是否生效,是否有使用到索引,访问类型是否是全表扫描还是ref或range
Mysql支持两种排序方式
文件排序(using filesort)或者扫描有序索引排序(using index)
Mysql能为排序与查询使用相同的索引
当无法使用索引列时,增大max_length_for_sort_dta参数的设置+增大sort_buffer_size参数的设置