MySQL 索引

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参数的设置