MySQL添加索引特点及优化问题

一、索引的特点

当mysql单表记录数过大时，增删改查性能都会急剧降落。MySQL索引的建立对MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。除非单表数据未来会一直不断上涨，否则不要一开始就斟酌拆分，拆分会带来逻辑、部署、运维的各种复杂度。一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的，而事实上很多时候MySQL单表的性能仍然有很多优化空间，乃至能正常支持千万级以上的数据量。

索引优势和劣势：

• 优势： 大大减少了服务器需要扫描的数据量，可以帮助服务器避免排序和临时表，实现快速检索，将随机I/O变成顺序I/O，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
• 劣势： 索引本身也是表，因此会占用存储空间，一般来讲，索引表占用的空间的数据表的1.5倍；索引表的保护和创建需要时间本钱，这个本钱随着数据量增大而增大；构建索引会下降数据表的修改操作（删除，添加，修改）的效力，由于在修改数据表的同时还需要修改索引表；创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行。

二、索引类型

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1.FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引其实不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效力较低的问题。

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB

• 对较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已有FULLTEXT索引的表快。
• 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那末全文索引不会生效。5.6版本以后InnoDB存储引擎开始支持全文索引。
• 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本以后通过使用ngram插件开始支持中文。
• 在MySQL中，如果检索的字符串太短则没法检索得到预期的结果，检索的字符串长度最少为4字节，另外，如果检索的字符包括停止词，那末停止词会被疏忽。

2.HASH

哈希索援用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，由于使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的散布方式。由于HASH的唯一（几近100%的唯一）及类似键值对的情势，很合适作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效力。但是，这类高效是有条件的，即只在“=”和“in”条件下高效，对范围查询、排序及组合索引依然效力不高。

3.BTREE

BTREE(B+TREE)索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，顺次遍历node，获得leaf。由于BTREE非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。这是MySQL里默许和最经常使用的索引类型。

4.RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对BTREE，RTREE的优势在于范围查找。

三、索引种类

• 普通索引：仅加速查询。
• 唯一索引：加速查询 + 列值唯一（可以有null）。
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个。
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效力大于索引合并，遵守“最左前缀”原则，把最经常使用作为检索或排序的列放在最左，顺次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或col3是不能使用索引的。
• 全文索引：对文本的内容进行分词，进行搜索。

四、索引的使用策略

1.甚么时候要使用索引？

主键自动建立唯一索引；常常作为查询条件在WHERE或ORDER BY；语句中出现的列要建立索引；作为排序的列要建立索引；查询中与其他表关联的字段，外键关系建立索引高并发条件下偏向组合索引；用于聚合函数的列可以建立索引，例如使用了max(column_1)或count(column_1)时的column_1就需要建立索引。

2.甚么时候不要使用索引？

常常增删改的列不要建立索引；有大量重复的列不建立索引；表记录太少不要建立索引。只有当数据库里已有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们常常在履行完第一条查询命令以后就被全部加载到内存里，这将使后续的查询命令都履行得非常快–不管有无使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才成心义。

3.索引失效的情况?

在组合索引中不能有列的值为NULL，如果有，那末这一列对组合索引就是无效的；在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那末在ORDER BY中就不要用了；LIKE操作中，’%aaa%'不会使用索引，也就是索引会失效，但是’aaa%'可使用索引；在索引的列上使用表达式或函数会使索引失效，例如：

将在每一个行上进行运算，这将致使索引失效而进行全表扫描，因此我们可以改成当前时间由程序作为参数传入：

• 其它通配符一样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引；
• 在查询条件中使用不等于，包括<符号、>符号和！=会致使索引失效。特别的是：如果对主键索引使用！=则不会使索引失效，如果对主键索引或整数类型的索引使用<符号或>符号也不会使索引失效。（不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）；
• 在查询条件中使用IS NULL或IS NOT NULL会致使索引失效；
• 字符串不加单引号会致使索引失效。更准确的说是类型不一致会致使失效，比如字段mobile是字符串类型的，使用WHERE mobile=99999 则会致使失败，应当改成WHERE mobile=‘99999’；
• 在查询条件中使用OR连接多个条件会致使索引失效，除非OR链接的每一个条件都加上索引，这时候应当改成两次查询，然后用UNION ALL连接起来；
• 如果排序的字段使用了索引，那末select的字段也要是索引字段，否则索引失效。特别的是：如果排序的是主键索引则select * 也不会致使索引失效；
• 尽可能不要包括多列排序，如果一定要，最好为这队列构建组合索引。

4.mysql查询优化?

字段:

• 尽可能使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED；
• VARCHAR的长度只分配真正需要的空间；
• 使用枚举或整数代替字符串类型；
• 尽可能使用TIMESTAMP而非DATETIME；
• 单表不要有太多字段，建议在20之内；
• 避免使用NULL字段，很难查询优化且占用额外索引空间；
• 用整型来存IP。

索引：

• 索引其实不是越多越好，要根据查询有针对性的创建，斟酌在WHERE和ORDER BY命令上触及的列建立索引，可根据EXPLAIN来查看会不会用了索引或者全表扫描；
• 应尽可能避免在WHERE子句中对字段进行NULL值判断，否则将致使引擎放弃使用索引而进行全表扫描；
• 值散布很希少的字段不合适建索引，例如“性别”这类只有两三个值的字段；
• 字符字段只建前缀索引；
• 字符字段最好不要做主键；
• 不用外键，由程序保证束缚；尽可能不用UNIQUE，由程序保证束缚；
• 使用多列索引时主张顺序和查询条件保持一致，同时删除没必要要的单列索引。

查询sql：

• 可通过开启慢查询日志来找出较慢的SQL；
• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将致使表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽量将操作移至等号右侧；
• sql语句尽量简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死全部库；
• 不用SELECT *；
• OR改写成IN：OR的效力是n级别，IN的效力是log(n)级别，IN的个数建议控制在200之内；
• 不用函数和触发器，在利用程序实现；
• 避免%xxx式查询，’%xxx%'不会使用索引,可使用全文索引，然后：
• SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
• 少用JOIN；
• 使用同类型进行比较，比如用’123’和’123’比，123和123比；
• 组合索引要遵守最做前缀原则，排序分组频率最高的列放在最左侧，以此类推；
• 尽可能避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
• 对连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大；
• 使用短索引，如果可能应当指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是唯一的，那末就不要对全部列进行索引。短索引不但可以提高查询速度而且可以节省磁盘空间和I/O操作。

5.索引的常见问题

1.索引是干甚么的？

索援用于快速找出在某个列中有一特定值的行。不使用索引，mysql一定要从第一条记录开始读完全个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，mysql能快速到达一个位置搜索到数据文件的中间，没有必要查看所有数据。

大多数mysql的索引(primary key、index、unique、fulltext)在B树中存储，只是空间列类型的索引使用R树，并且memory表还支持hash索引。

2.索引好复杂，我该怎样理解索引，有无一个更形象的例子？

索引就像是一本书的目录。

3.索引越多越好?

大多数情况下，索引能大幅提高查询效力。但是:数据变更(增删改)都需要保护索引，因此更多索引意味着更多保护本钱；也意味着需要更多控件空间(一本书100页，却有50页目录？)；太小的表，建索引可能会更慢。

4.索引的字段类型问题

text类型，也可建索引(需要指定长度)；MyISAM存储引擎长度综合不能超过1000字节；用来挑选的值尽可能保持和索引列一样的数据类型。

5.like能用到索引？

尽可能减少like查询，但是也不是绝对不可用，'xxx%'是可以用到索引的。除like，以下操作符也能够用到索引：

这些用不到索引：

6.甚么样的字段不合适建索引？

列的值唯一性太小(比如性别，类型)，不合适建索引。(甚么叫大小？一般来讲，同值的数据超过表的15%，那就没有必要建索引了)更新非常频繁的数据不合适建索引。

7.一次查询能用多个索引？

不能

8.多列查询该怎么建索引？

一次查询只能用到一个索引， a列建索引或者b列建索引？谁的辨别度（同值的少）更高，建谁！固然，联合索引也是个不错的方案。

9.联合索引的问题

所以大多数情况下，有col1、col2索引了，就不用再去建col1索引了

10.哪些常见的情况不能用到索引？

对列进行函数运算，如：

<div