您的位置 首页 数据库

mysql数据库索引应用总结 (DBA运维规范)

数据库索引应用总结

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 "%_" 
-- 应用以上特殊符号信息,也会导致辅助索引失效

 

欢迎来撩 : 汇总all

白眉大叔

关于白眉大叔linux云计算: 白眉大叔

热门文章