mysql 索引优化,索引建立原则和不走索引的原因

时间:2024-05-23 09:03:02

第一:选择唯一性索引

唯一性索引的值是唯一的,可以更快捷的通过该索引来确定某条记录.

2.索引的列为where 后面经常作为条件的字段建立索引

如果某个字段经常作为查询条件,而且又有较少的重复列或者是唯一咧可以考虑作为索隐列

经常作为查询条件的列作为索引会提高速度

3.位经常需要进行排序.分组和联合操作的的字段建立索引.

order by  group by  distinct union

这种情况下在查询的时候排序会浪费很多的时间,

如果为其建立索引可以有效的避免排序操作.

4.限制索引的的数目,索引的数目多,对系统的资源也是一种消耗,删除修改也会费资源.

5.劲量使用数据量少的索引. 或者索引前缀索引.

如果索引的值很长, 查询速度就会受到影响.

6.尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。

7.删除不再使用的索引.数据或者业务变更,数据方式变更就需要,删除无用的索引.

8.小表不应该建立索引.

这篇文章主要记录,我对如何找未使用索引的理解及风险(目前还未找到理想方法),能像oracle保存执行计划,根据执行计划(v$sql_plan)来判断索引使用情况是比较安全。当然oracle的index monitor特性类似percona的userstat有比较大的风险。

以下四个工具(方法)是在mysql找未使用索引比较方便,但都存在一定风险

1、mysqlidxchx

2、pt-index-usage

3、userstat

4、check-unused-keys

1、mysqlidxchx工具很长时间没有更新,但主要用来分析general log、slow.log,来判断实例中那个索引是可以删除,但这个工具没有经过实战,风险很大。

2、pt-index-usage原理来类似mysqlidxchx,执行过程中性能消耗比较严重,如果要在生产库上部署,最好在凌晨业务低锋时使用,pt-index-usage只支持slow.log格式的文件,如果要全面分析整个实例索引使用情况,需要long_query_time设置成0,才能把所以的sql记录下来,但同时会对磁盘空间造成压力,同时pt-index-usage对大文件分析就是件痛苦的事。当然pt-index-usage可以考虑部分表索引使用情况的确认。

3、最看好的userstat,收集信息性能优越,成本低。这个patch是google贡献的(userstat_running),percona把它改名成userstat,默认是不开启的,开启是会收集客户端、索引、表、线程信息存储在CLIENT_STATISTICS、INDEX_STATISTICS、TABLE_STATISTICS、THREAD_STATISTICS。Userstat的bug导致的问题太严重,直接导致mysql crash,到目前淘宝生产环境还没有使用。

4、Ryan Lowe的check-unused-keys脚本基于userstat,能够比较方便输出需要删除的索引。

小结:mysql能把每条sql执行计划保存在性能视图中,写入性能视图成本是非常小,用户可以根据执行计划来判断索引使用情况,分析执行计划突变的监控。

=-===================================================

简单记忆建议索引的原则是 :唯一列 经常被查询   排序 预先建立索引    总体控制数量   使用字段少的列索引  前缀索引   删除无用 小表不建

=========================================================================================================================

不走索引的原因:

1.没有查询条件没where 后面的内容  查询条件没索引

2.查询条件没引导列.  没有有索引的列

3.查询数量是超过表的一部分,mysql30%,oracle 20%

4.索引失效,索引插入过多可能发生意外失效

5.查询条件使用函数在索隐列上面.计算等.

查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
错误的例子:select * from test where id-1=9; 正确的例子:select * from test where id=10;

6.对小表查询

7.统计数据不真实.

8.CBO计算走索引花费过大的情况

9查询条件字符串和数字等的隐式转换.

10.!= <>

11.%% 两个百分号不走索引,开始的结尾的百分号走索引.

14 not in    not exist             in 劲量转换为union

15, time 和date 时间格式不一致

16.17,B-tree索引is null不会走,is not null会走,位图索引 is null,is not null 都会走

索隐列避免空列,一般选非空的列.

====

MyISAM 存储引擎索引键长度总和不能超过1000 字节;
BLOB 和TEXT 类型的列只能创建前缀索引;
MySQL 目前不支持函数索引;
使用不等于(!= 或者<>)的时候MySQL 无法使用索引;
过滤字段使用了函数运算后(如abs(column)),MySQL 无法使用索引;
Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引;
使用LIKE 操作的时候如果条件以通配符开始( '%abc...')MySQL 无法使用索引;
使用非等值查询的时候MySQL 无法使用Hash 索引;
在我们使用索引的时候,需要注意上面的这些限制,
尤其是要注意无法使用索引的情况,因为这很容易让我们因为疏忽而造成极大的性能隐患。