mysql索引查询 案例_MySQL索引概念与测试案例

索引分类：主键索引、唯一索引、普通索引、全文索引、组合索引。

(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 = ''; 表示在NAME的B+树查找结果，得到ID值，再根据ID值去主键的B+树中去查找出整行记录，这个过程就是回表。

索引覆盖

例如： select id from where name = ''; 表示在NAME的B+树查找结果，就可以得到ID值，不需要再去根据ID查询，这就是索引覆盖，因为不需要回表，所以速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。表示只在一个索引树上就能得到所有需要的结果。

索引下推

索引测试

创建表和索引

--创建用户表

CREATE TABLET_USER

(idint primary KEY,

loginnamevarchar(20),

namevarchar(100),

ageint,

sexchar(1),

depint,

addressvarchar(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个以内