- 索引分类:主键索引、唯一索引、普通索引、全文索引、组合索引。
(1)MySQL默认会对哪些列创建索引?
主键和唯一键。
(2)如果创建表时,没有创建主键和唯一健,是不是就没有主键索引了?
主键索引不是这个意思,系统在进行数据组织的时候,没有主键,也会有个key值的。
key:(下面这几种情况都叫主键索引)
如果主键存在,则存主键;如果主键不存在,但唯一键存在;如果主键和唯一健都不存在,则存6个字节的rowid。
- 索引的优点:减少服务器需要扫描的数据量、帮助服务器避免排序和临时表、将随机io变成顺序io
- 索引的用处:
(1)快速查询匹配Where子句的行
(2)从consideration中消除行,如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引
(3)如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查询行
(4)当有表连接的时候,从其他表检索行数据
(5)查询特定索引列的min或max值
(6)如果排序或分组时在可用索引的最左前缀上完成的,则对表进行排序和分组
(7)在某些情况下,可以优化查询以检索值而无需查询数据行
- 索引面试常问哪些问题
索引分类、索引数据结构、索引数据结构的选择、回表、最左匹配、索引覆盖、索引下推、索引优化、索引失效、聚簇索引和非聚簇索引、存储引擎、索引匹配方式、索引具体的优化点,我什么时候加索引、索引什么时候执行。
- 索引匹配方式
(1)全值匹配:where条件为=
(2)最左前缀匹配:组合索引时,SQL必须包括组合索引指定的第一列;如果不存在,则不会走索引。
(3)列前缀匹配:使用like模糊查询,like '123%'
(4)范围值匹配:between and,> , <,但是索引中,如果包括了范围判断,则会导致后面的查询条件不走索引,只有前面的列和该范围列走索引。
(5)精确匹配某一列并范围匹配另一列
(6)只访问索引的查询
注意:隐式转换,会导致索引失效。
- 回表
例如: select * from where name = '';
- 索引覆盖
例如: select id from where name =
explain的输出结果Extra字段为Using index时,能够触发索引覆盖。表示只在一个索引树上就能得到所有需要的结果。
- 索引下推
- 索引测试
创建表和索引
-- 创建用户表
CREATE TABLE T_USER
(id int primary KEY,
loginname varchar(20),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
)
ENGINE = INNODB DEFAULT CHARSET=utf8 COMMENT='用户表';
-- 创建索引
create index nasb_index on t_user(name, age, sex, dep) ;
查看执行计划
- 索引注意事项
1.尽量不使用表达式
2.强制类型转换会全表扫描
3.尽量使用主键查询
使用前缀索引:对于存储的值有点长的列,考虑使用前缀索引
怎么知道取前几位才能保证建的索引更合适?
统计列的值出现的个数
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;
然后尝试使用前几个字符与全值得到的统计数量差不多
select count(*) as cnt,left(city, 4) as pref from citydemo group by pref order by cnt desc limit 10;
4.使用索引扫描来排序
5.union all,in,or都能够使用索引,但推荐使用in
6.范围列可以用到索引
7.更新频繁,区分度不高的字段不建议用索引
8.创建索引的列,不能为null,可能得到不符合预期的结果
9.索引数量尽量控制在5个以内,组合索引列也要控制在5个以内