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