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%'可以使用索引；在索引的列上使用表达式或者函数会使索引失效，例如：

select * from table where ceate_time > unix_timestamp(curdate());

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

select * from table where ceate_time > 1524561911;

• 其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引；
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是：如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号也不会使索引失效。（不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）；
• 在查询条件中使用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，以下操作符也可以用到索引：

<，<=，=，>，>=，between，in

这些用不到索引：

<>，not in，!=

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

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

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

不能

8.多列查询该如何建索引？

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

9.联合索引的问题

-- 命中col1、col2联合索引
select col1,col2 from test where col1 = 'xxx';
-- 不能命中col1、col2联合索引
select col1,col2 from test where col2 = 'xxx';

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

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

like '%xxx'
not in
!=

对列进行函数运算，如：

where md5(password) = "xxx"

存了数值的字符串类型字段(如手机号)，查询是记得不要丢掉值的引号，否则无法命中索引：

select * from test where mobile = 13800002222;

如果mobile字段是char或者varchar类型，则上面查询无法命中索引,应为：

select * from test where mobile = '13800002222';

11.NULL的问题

Null会导致索引形同虚设，所以在设计表结构应避免NULL的存在。
可用其他方式来表达，比如-1。