数据库索引应用总结

01 建立索引原则规范（DBA运维规范）

数据表中必须要有主键索引（创建表时指定），建议是与业务无关的自增列；
数据表中某些列若经常作为 where/order by/group by/join on/distinct条件信息，最好将相应列设置索引（产品功能/用户行为）
数据表中最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引；（最左列-减少回表次数 - 减少磁盘IO）
数据表中列值长度较长的索引列，建议可以使用前缀索引；(防止索引树层次过高)
数据表中不建议建立大量索引，最好降低索引条目，不要创建无用索引，不常用的索引要定期清理（percona toolkit）
数据表中的索引信息做调整维护时，尽量避开业务繁忙期，或者通过软件工具做调整维护（pt-ost）
数据表中的联合索引创建过程要遵循索引最左原则

02 索引应用失效情况（开发工作规范）

• 数据表信息查询时，没有设置查询条件信息；

• 数据表信息查询时，查询的条件没有建立索引；

  select * from t1; select * from t1 where id=1001 or 1=1; -- 执行SQL注入语句问题

03 查询结果规范要求

当查询结果集数据是原表中的大部分数据，超过了总行数的25%，优化器便自动判断没必要走索引了，因为可以借助预读功能获取数据

可以通过精细查找指定数据的范围，从而达到优化的效果；（read_head预读相关参数）

04 索引失效情况处理

当频繁的对数据表中索引列值做修改、删除等操作时，会导致索引统计信息过旧或不真实，最终造成索引功能失效；

本身索引是有自我维护的机制能力，但并不是实时调整更新的，需要有一定的间隔时间做调整；

一般索引失效的表现情况为：select查询语句平常查询时很快，但突然某天执行就变慢了，就是索引失效了，统计数据不真实；

索引统计的信息存储位置：

innodb_index_stats
innodb_table_stats
-- mysql库中的相应表

当索引失效时，可以使用命令重新进行统计信息获取，是索引功能再次生效：

mysql > analyze table world.city;
-- 表示立即更新过久的统计信息（也可以将索引删除重建）

在查询条件过程中，使用了函数信息在索引列上，或者对索引进行了运算（+ - * / ！等），都会导致索引功能失效，建议尽量避免；

# 错误举例：
select * from test where id-1=9;
​
# 正确举例：
select * from test where id=10;
-- 总之尽量避免条件信息出现 算数运算  函数运算  子查询
​
# 子查询补充：
# 子查询指一个查询语句嵌套在另一个查询语句内部的查询
# SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据
# 查询中国城市人口大于北京人口数量的城市信息
查询01：查询中国 北京的人口数量
select population from city where countrycode='CHN' and name='Peking';
查询02：查询中国 基于北京人口数量 大于北京人口数量城市
select * from city where countrycode='CHN'  and population > 'xxx'
​
mysql> select * from city where CountryCode='CHN' and population > (select population from city where CountryCode='CHN' and name='Peking');

在查询数据信息过程中，出现了隐式转换也会导致索引失效；

# 创建测试数据表
mysql> create table test (id int,name varchar(20),telno char(11));
mysql> insert into test values (1,'a','110'),(2,'b','123'),(3,'c','120'),(4,'d','119'),(5,'e','130');
​
# 创建索引信息
mysql> alter table test add index idx(telno);
​
# 查询数据信息
mysql> select * from test where telno='110';
mysql> select * from test where telno=110;
-- 上面两条语句都能查看到结果信息，但是有一条语句是没有走索引的

因为本身查询条件列的数据类型为字符类型，但是作为条件时当成了数字类型，数据库会将数值类型通过隐式转换函数转换为字符类型；

由于，条件中若加上了函数信息，就会导致索引功能失效，所以隐式转换也会造成索引失效；

在查询条件过程中，应用了特殊数据匹配方法时，也会导致索引失效，一般是辅助索引失效；

<> , not in , like "%_" 
-- 应用以上特殊符号信息，也会导致辅助索引失效