Mysql中的索引
1、什么是索引?
Mysql官方给出的索引的定义:索引(Index)是帮助mysql高效获取数据的一种数据结构。
我们可以简单理解为:已经排好序的快速查找数据结构。
数据本身之外,数据库还维护着一个满足特点查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。
我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引等。
2、索引的优点和缺点:
优点:提高数据检索的效率,降低数据库的IO成本。降低数据排序的成本,降低CPU消耗。
缺点:占用空间,降低更新表的速度。
(1)实际上索引也是一张表,该表保存了主键与索引字段,并指向实际表的记录,所以索引也是要占用空间的。
(2)虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert,update和delete。因为更新表时,mysql不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为更新操作所带来的键值变化后的索引信息。
(3)索引只是提高查找效率的一个因素,如果mysql有大量数据的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
3、索引的分类:
(1)单值索引:即一个索引只包含单个列,一个表可以有多个单值索引。
(2)唯一索引:索引列的值必须唯一,但允许有空值。例如主键,unique修饰的字段。
(3)复合索引:即一个索引包含多个列。
4、索引的基本语法:
(1)索引的创建
create [unique] index indexName on myTable(columnName(length));
alter myTable add [unique] index [indexName] on (columnName(length));
加了unique就表示唯一索引。
索引命名习惯:index_tableName_column
例如:index_user_username
(2)索引的删除
drop index [indexName] on myTable;
(3)索引的查看:
show index from tableName;
使用alter命令,有4种方式来添加数据表的索引:
alter table tbl_user add primary key (id);
改语句添加一个主键,主键属于唯一索引,且不能为空。
alter table tbl_user add unique index_user_username (username);
创建唯一索引,除了null值外,null值可能会出现多次。
alter table tbl_user add index index_user_username (username);
创建普通索引,索引只可能出现多次。
alter table tbl_user add index fulltext index_user_username (username);
指定索引为fulltext,用于全文索引。
mysql索引结构:BTree索引,Hash索引,full-text全文索引,R-Tree索引。
5、哪些情况需要创建索引?
(1)主键自动建立唯一索引;
(2)频繁作为查询条件的字段应该创建索引;
(3)查询中与其他表关联的字段,外键关系建立索引;
(4)单键索引,组合索引选择用哪个?(在高并发下倾向创建组合索引)
(5)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度;
(6)查询中统计或者分组字段
6、哪些情况不需要建立索引?
(1)频繁更新的字段不适合创建索引,因为更新表记录还会更新索引;
(2)Where条件里用不到的字段不要创建索引;
(3)某个字段的值重复太多,例如一个字段的值只有true和false,这种字段建立索引没有太大意义;
7、什么是索引覆盖?
就是select的数据列只用从索引中就能取得,不必读取数据行,mysql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说,查询列要被所建的索引覆盖。
8、索引优化:
(1)全值匹配;
(2)最佳左前缀法则:如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始,并且不跳过索引中的列;
(3)不在索引列上做任何操作,包括计算,函数,自动手动类型转换;
(4)存储引擎不能使用索引中范围条件右边的列;
(5)尽量使用覆盖索引,查询列和索引列一致,减少select *
(6)Mysql在使用不等于(!=)将导致索引失效,进行全表扫描
(7)is null, is not null也无法使用索引;
(8)like左模糊(like ‘%abc’)将导致索引失效,进行全表扫描
(9)字符串不加单引号索引失效,即varchar类型你写错了,会被项目经理骂死。
(10)少用or,会导致索引失效
问题:如何解决like ‘%abc%’索引失效问题?
答:使用覆盖索引,尽量让查询的字段被索引覆盖。
Where c=5 and b=4 and a=3;Y,mysql会优化sql语句,使用到abc
Where a=3 and c>5 and b=4;Y,优化后使用到abc,c使用了部分
Where a=3 and c=5 order by b;Y,使用到a,b的作用是排序而不是查找
Where a=3 order by b;Y,使用到a,b作用是排序
Where a=3 order by c;Y,使用到a,但是中间断了,产生filesort
Where a=3 order by b,c;Y,使用到a,bc作用是排序
Where a=3 order by c,b;Y,使用到a,产生filesort
Where a=3 and b=4 order by c,b;Y,使用到a,无filesort,因为b是常量
Where a=3 and b=4 order by b,c;Y,使用到ab
Where a=3 and b=4 and c=5 order by b,c;Y,使用到ab
Where a=3 and c=5 group by b;Y,使用到a
Where c=5 group by c,b;N,产生filesort, 也Using memory
9、索引优化的一般建议:
对于单键索引,尽量选择针对当前查询过滤性更好的索引;
对于组合索引,当前查询中过滤性最好的字段在索引顺序中,位置越靠前越好;
对于组合索引,索引值尽量包含where子句中更多的字段;
尽可能通过分析统计信息和调整查询的写法来选择合适的索引;
左连接,索引加右表。
小表驱动大表:
例如:select * from A where id in (select id from B);
等价于:select * from A where exists (select 1 from B where A.id=B.id);
当B表的数据集小于A表的数据集时,in优于exists;
当B表的数据集较多时,exists优于in;
为排序使用索引:
Mysql两种排序方式:文件排序(using filesort)或扫描有序索引文件(using index)。
10、假设:index a_b_c(a, b, c);
Order by能使用索引最左前缀:
- order by a
- order by a,b
- order by a,b,c
- order by a desc, b desc, c desc(同升,同降序都行)
如果where使用索引的最左前缀定义为常量,则order by能使用索引: - where a=’zs’ order by b,c
- where a=’zs’ and b=1 order by c
- where a=’zs’ and b>1 order by b,c
索引失效的情况,即没有使用索引来排序的: - order by a asc, b desc, c desc // 排序不一致
- where b=1 order by b,c // 丢失a索引,带头大哥没了
- where a=’zs’ order by c // 丢失b索引,中间兄弟断了
- where a=’zs’ order by a,d // d不是索引的部分,需要重排序
- where a in(…) order by b,c // a是范围,范围之后全失效
Group by关键字优化与group by一致,能在where中限定的条件就不要放在having中限定。