mysql索引使用以及优化

1、Scheme设计与数据类型优化

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存。

比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

这里总结几个可能容易理解错误的技巧：

1、通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。

2、对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

3、UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。

4、通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，

那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

5、TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

6、大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

7、schema的列不要太多。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

8、大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，

而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。

2、创建高性能索引

索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，

因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。

接下来将向你展示一系列创建高性能索引的策略，以及每条策略其背后的工作原理。但在此之前，先了解与索引相关的一些算法和数据结构，将有助于更好的理解后文的内容。

1、mysql提供四种索引

B-Tree索引:最常见的的索引，大部分引擎支持B树索引

HASH索引:只有Memory引擎支持，使用场景简单

R-Tree索引:空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text：全文索引也是MyISAM的一个特殊索引，主要用于全文索引，InnoDb从MySql5.6开始提供支持全文索引。

2、索引分类

1.普通索引index :加速查找

2.唯一索引

主键索引：primary key ：加速查找+约束（不为空且唯一）

唯一索引：unique：加速查找+约束 （唯一）

3.联合索引

-primary key(id,name):联合主键索引

-unique(id,name):联合唯一索引

-index(id,name):联合普通索引

4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。

3、B-Tree索引、hash索引区别

B-Tree索引的特点

1、B-tree索引可以加快数据的查询速度

存储引擎不需要进行全表扫描来获得需要的数据，取而代之的是从索引的根节点开始进行搜索。然后根据指针逐层向下查找，通过比较节点页的值和有目标值就可以找到合适的指针进入下层节点，而这些指针实际上定义了子节点页中值的上限和下限。

2、B-tree索引更适合进行范围查询

因为前面说过，B-tree对索引是顺序组织存储的，所以就很适合进行查找范围数据。

B-tree索引的使用场景

1、全值匹配的查询

指的是和索引中的所有列进行匹配，比如查询字段 name = ‘tom’;

2、匹配最左前缀的查询

比如为ａ列和ｂ列设置联合索引，只要联合索引的第一列（ａ列）符合查询条件，索引就会被用到，若只是第二列（ｂ列）符合条件则不会被用到该索引。

３、匹配列前缀的查询

只匹配某一列的值的开头部分

4、匹配范围值

5、精准匹配某一列并范围匹配另外一列

6、只访问索引的查询

在这里指的就是覆盖索引，即只需要访问索引，而无需访问数据行

7、用于查询中的order by 操作

索引树中的节点是有序的。一般来说，若B-Tree可以按照某种方式查找到该值，那么也可以用这种方式用于排序。所以，如果 order by 子句中满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

B-Tree索引的限制

1、若不是按照索引的最左列开始查找，则无法使用该索引

比如建立联合索引（name 、phone_num），若搜索phone_num则无法使用该索引

2、使用索引时，不能跳过索引中的列

比如建立联合索引（name 、phone_num 、addr），若搜索name和addr 则无法使用该索引只能使用那么过滤

3、not in 和 <> 操作无法使用该索引

4、若查询中有某个列的范围查询，则其右边的所有列都无法使用索引

注意：

存储引擎用不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩的技术使得索引更小，但InnoDB则按照原数据格式进行存储。

MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据逐渐引用被索引的行

至此，我们基本已经将B-Tree索引介绍完了，下面我们来了解另外的一种MySQL的索引类型：HASH索引。

HASH索引

在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。

hash索引的特点

1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。

2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

3、hash索引包括键值、hash码和指针 。

因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash索引的限制

1、Hash索引必须进行二次查找

使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。

2、hash索引不能用于外排序

hash索引存储的是hash码而不是键值，所以无法用于外排序

3、hash索引不支持部分索引查找也不支持范围查找

只能用到等值查询，不能范围和模糊查询

4、hash索引中的hash码的计算可能存在hash冲突

当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）

上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

缺点

1、需要维护hash值，可以手动维护，也可以使用触发器实现。

2、若数据表非常大的话，CRC32（）会出现大量hash冲突，则可以自己实现一个64位的hash函数，这个自定义的hash函数要返回整数而不是字符串，因为范围整数，对此效率更高。一个简单的办法就是使用MD5（）函数返回值的一部分来作为自定义的hash函数。但是这可能比自己写一个hash算法性能要差一些。

3、特定类型查询优化

1、优化COUNT()查询

COUNT()可能是被大家误解最多的函数了，它有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。统计列值时，要求列值是非空的，它不会统计NULL。如果确认括号中的表达式不可能为空时，

实际上就是在统计行数。最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

2、优化关联查询

在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：

1）确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没

有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）

2）确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，

然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。

3、优化LIMIT分页

当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

4、sql的编写需要注意优化

使用limit对查询结果的记录进行限定

避免select *，将需要查找的字段列出来

使用连接（join）来代替子查询

拆分大的delete或insert语句

可通过开启慢查询日志来找出较慢的SQL

不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边

sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库

OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

不用函数和触发器，在应用程序实现

避免%xxx式查询

少用JOIN

使用同类型进行比较，比如用’123’和’123’比，123和123比

尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

5、分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

1、分区的好处是：

可以让单表存储更多的数据

分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作

部分查询能够从查询条件确定只落在少数分区上，速度会很快

分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备

可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争

可以备份和恢复单个分区

2、分区的限制和缺点：

一个表最多只能有1024个分区

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表无法使用外键约束

NULL值会使分区过滤无效

所有分区必须使用相同的存储引擎

3、分区的类型：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值