mysql索引的使用和优化

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里的所有记录的引用指针。更通俗的说，数据库索引就好比一本书的目录，能够加快数据库的查询速度。 首先感谢网上的那些前辈和大神们的无私分享 最近在学习mysql的优化问题,在查询中正确使用索引,对查询效率的提升有非常大的帮助,使用不当会使索引失效,起不到索引该有的作用。把这两天学到的知识记录一下。

使用索引的优势

提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则真好降低了排序的成本。

使用索引带来的问题

1.占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。 2. 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引的类型

1.normal： 表示普通索引,它没有任何限制，MyISAM 中默认的 B-tree 类型的索引 2.unique： 表示唯一的，不允许重复的索引，但是允许有空值。如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique。 3.full textl: 表示全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。注意仅 MyISAM 引擎支持 4.组合索引（最左前缀） 平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。使用组合索引时注意最左匹配原则。比如新建索引ALTER TABLE testADD INDEX 'id_name_age' ('id','name','age')。在查询的时SELECT * FROM user WHERE id =1 AND name='bruce'索引起作用。但是查询时SELECT * FROM user WHERE name='bruce' AND age = 18这时候索引不起作用。至于原因,因为辅助索引是B+树实现的，虽然可以指定多个列，但是每个列的比较优先级不一样，写在前面的优先比较。一旦出现遗漏，在B+树上就无法继续搜索了（通过补齐等措施解决的除外），因此是按照最左连续匹配来的。既然是在B+树上搜索，对于条件的比较自然是要求精确匹配（即"=“和"IN”）。不过顺序倒是可以颠倒，因为查询优化器重排序一下就好了。

索引的优化

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null,可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20
尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。见如下例子： SELECT * FROM T1 WHERE NAME LIKE ‘%L%’ SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’ SELECT * FROM T1 WHERE NAME LIKE ‘L%’
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

总结

哪些情况需要建索引：

1 主键，唯一索引 2 经常用作查询条件的字段需要创建索引 3 经常需要排序、分组和统计的字段需要建立索引 4 查询中与其他表关联的字段，外键关系建立索引

哪些情况不要建索引：

1 表的记录太少，百万级以下的数据不需要创建索引,数据量很少的时候,索引带来的提升不够明显 2 经常增删改的表不需要创建索引(在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。) 3 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。(体现不出索引带来的价值) 4 频发更新的字段不适合创建索引(和2同理) 5 where条件里用不到的字段不需要创建索引(索引起不到作用)

使用注意:

性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在where子句中出现的列，在join子句中出现的列。
考虑列中值的分布，索引的列的基数越大，索引的效果越好。
使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。
在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。

再次感谢前辈们的无私分享

我也是在学习中,文中如有错误的地方,欢迎在评论区指出,方便共同学习