MySQL的索引与SQL优化策略

存储引擎

存储引擎就是存储数据，建立索引，更新/查询数据等技术的实现方式，存储引擎是基于表而不是基于数据库的（不同的数据库表可以选择不同的存储引擎），所以存储引擎也可被称为表类型

mysql5.5之后，默认存储引擎是InnoDB

使用show关键字查看建表情况

show create table student;

那如何在建表的时候指定存储引擎呢？

create table student(
    id int,
    name varchar(10)
)ENGINE=INNODB;

查看当前数据库支持的存储引擎

show engines;

常见的存储引擎

InnoDB存储引擎

一种兼顾高可靠性和高性能的存储引擎，MySQL5.5之后，InnoDB是默认的存储引擎

特点：

• 增删改语句（DML）遵循ACID，支持事物
• 行级锁，提高并发访问性能
• 支持外键约束，保证数据的完整性和正确性

InnoDB会为每张表都生成一个表空间文件xxx.ibd，存储表结构，数据，索引

MyISAM存储引擎

是MySQL早期的默认存储引擎

特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行级锁
• 访问速度快

Memory存储引擎

表数据存储在内存中，会受到断电，硬件问题的影响，这些表只能当作临时表和缓存使用

特点：

• 数据存储在内存中，访问速度快
• 支持hash索引（默认）

InnoDB与MyISAM区别

• InnoDB支持事务，支持外键，支持行级锁
• MyISAM不支持事务，支持表锁，不支持外键

索引

索引就是一种帮助我们快速高效获取数据的有序的数据结构

例如这样一条sql：

select * from student where age = 20;

在无索引的情况下，会依次遍历整张表直到找到全部复合条件的
在有索引的情况下，会很快速的找到复合条件的全部数据

优点：

• 提高查询数据的效率，降低数据库IO的开销
• 提高了数据排序效率，降低CPU消耗

缺点：

• 索引需要用一个数据结构来维护，本身是需要占用空间的
• 降低了表的更新效率，因为插入，删除，修改的同时也要更新索引的结构

索引结构

MySQL的索引是在引擎层实现的，也就是不同的引擎有不同的索引结构

• B+Tree结构：最常见的索引类型，大部分引擎都支持B+Tree索引
• Hash索引：底层使用哈希表实现，可精确匹配列的查询，但是不支持范围查询（只有Memory存储引擎支持）
• Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式

二叉树

但是如果插入元素的时候就是按顺序插入，该树就变为单支树

此时查询性能大大降低，大数据量情况下层级较深，检索速度慢

为了解决上述单分支情况，我们可以使用红黑树（自平衡的二叉树），但是红黑树一个结点也只有两个分支是一个二叉树，当数据量大的时树的高度也会很深，导致检索速度慢

B-Tree

B树是一个多路平衡查找树，每个结点存n个key，n+1个指针，（key存的其实是数据存储位置的地址）每个key下有对应的数据

以一颗度数最大为5的B-Tree举例（度数为一个结点的子节点个数），度数最大为5，则最多存4个key，5个指针

在插入数据时，如果某个结点的指针数要大于树的最大度时，该结点的中间元素会向上分裂

B+Tree

B+Tree其实是B树的变种

此处以最大度数为4的B+Tree举例

特点：

• 所有的元素都会出现在叶子节点（父结点会在孩子结点中体现）
• 数据在叶子节点中保存
• 叶子结点使用单向链表连接

MySQL索引数据结构对B+Tree做了优化，叶子结点使用双向链表连接，提高了区间数据的访问性能

Hash索引

使用一定的hash算法，将键值计算出一个哈希地址，映射到对应的哈希桶中，存储在哈希表中

特点：

• Hash索引只能用于对等比较，不支持范围查询
• 无法利用索引进行排序操作
• 查询效率高，一般只需一次检索就可以

在MySQL中支持Hash索引的是Memory引擎，但InnoDB具有自适应的哈希功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动创建的

B+Tree的优点

为什么InnoDB选择用B+Tree作为索引？

• 相于对二叉树，层级更少，搜索效率高
• 相对于B树，B树的结点中会存储数据，导致存储的key和指针减少，而B+Tree树的数据只存在叶子节点，那非叶子结点的key和指针就更多，导致相同数据量下，B+Tree的层级相对于B树更少，搜索效率更高，并且B+Tree的叶子用双向链表连接便于范围查找
• 相对与Hash索引，Hash索引只支持等值匹配，不支持范围匹配

索引分类

• 主键索引：根据表中的主键自动创建的索引，只能有一个（PRIMARY KEY）
• 唯一索引：避免同一表中列数据的重复，可以有多个(UNIQUE)
• 常规索引：快速定位特殊数据，可以有多个
• 全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值(FULLTEXT)

在InnoDB中根据索引的存储形式又分为聚集索引，二级索引

• 聚集索引：将数据与索引存在一起，索引结构的叶子结点存储的是行数据（必须有且只能有一个）
• 二级索引：将数据与索引分开存储，索引结构的叶子结点存储的是对应的主键（可以有多个）

聚集索引的选取规则

• 如果表中有主键，那聚集索引就是主键索引
• 如果没有主键，将选取第一个唯一索引作为聚集索引
• 如果没有主键也没有唯一索引，则InnoDB会自动创建一个rowid作为隐藏的聚集索引

回表查询：先根据二级索引找到主键值，再拿主键值根据聚集索引找打行数据

索引语法

创建索引

create index 索引名 on 表名（字段1，字段2...）；

一个索引可以关联一个字段也可以关联多个字段

查看索引

show index from 表名；

删除索引

drop index 索引名 on 表名；

explain执行计划

使用explain获取MySQL如何执行select语句的信息

explain sql语句；

 explain select * from student;

explain执行计划各字段的含义：

• id：表示执行select语句操作表的顺序，如果id相同则执行顺序从上往下，id不同，值越大，越先执行（多表查询下，会出现多个行表示不同表的执行情况）
• select_type：表示select的类型
• table：查询访问的表名
• type：表示连接类型，性能由好到差的连接类型为NULL，system，const，eq_ref，ref，range，index，all
• possible_keys：显示这张表中可能用到的索引
• key：显示实际用到的索引
• key_len：显示表中使用索引的字节数，该字节数为索引的最大长度并非实际长度，长度越短越好
• rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能不准确
• filtered：表示返回结果的行数占需要读取行数百分比，该值越大越好
• Extra：用来展示前面几个字段没有展示出来的额外的信息

索引的使用原则

最左前缀法则

主要是针对联合索引（索引关联了多个列），要遵循最左前缀法则

它指的是从索引的最左列开始，并且不跳过索引中的列（索引的字段存在就行，不要求位置严格按照顺序），如果跳过某一列，索引将部分失效（后面的字段索引失效）

示例

发现联合索引字段的顺序从左往右为name，age，height，所以我们使用联合索引的时候必须按照这个顺序，如果跳过某列，则从跳过的开始，后面索引失效

索引生效:

索引失效:

发现此时跳过了age，但是索引还是生效了，其实此时索引只有name生效了，height并未走索引，可以看索引的长度，只由name的长度

最左前缀法则不严格要求位置顺序，只要字索引段存在就行

范围查询

联合索引中，出现范围查询（> <），范围查询右侧的列索引失效，尽量使用>= <=避免因为范围查询导致索引失效

发现虽然走了索引，但是范围查询右边的列height没有走索引

给范围查询加上=号后，发现都走了索引

索引列进行运算操作

如果有索引的列进行了运算操作，那么索引将失效

在索引的列上进行了字符串截取操作，发现索引失效

把索引的列字符串截取操作取消后，发现索引生效

字符串不加引号

字符串不加引号，索引失效

这是针对字符串里存的是数字，如果存的不是数字，不加引号会报错

模糊查询

如果是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效

头部模糊匹配，索引失效

尾部模糊匹配，索引生效

or连接的条件

用or分割开的条件，如果前面有索引，后面没有索引，那么涉及到的索引都不会用到

id有索引，name没有索引，那id的索引也不会用到

数据分布影响

MySQL会评估使用索引会不会比走全表扫描慢，如果比全表扫描慢则会不走索引

覆盖索引

在查询的时候尽量使用覆盖索引（需要查询的字段，在索引中都能找到）

减少select *的使用，因为查找全部字段可能某个字段没有索引，就会出现回表查询

前缀索引

当字段类型为varchar，text时，有时候索引需要很长的字符串，这时候索引很大，查询时，浪费大量的磁盘IO，影响查询效率，这时候可以只将字符串的一部分前缀作为索引，减少了索引空间，提升了查询效率

create index 索引名 on 表明(字段(n))

将字段的前n个字符作为索引

索引的设计原则

1. 对于数据量大并且查询比较频繁的表建立合适的索引
2. 对于常作为查询条件where，order by，group by操作的字段建立索引，使用时注意最左前缀法则
3. 如果是字符串或者长文本类型，可以根据字段的特点建立前缀索引
4. 尽量使用联合索引，减少单列索引，因为联合索引可以尽可能做到覆盖索引，避免回表查询
5. 索引也不是越多越好，因为索引也需要数据结构来维护，索引越多，维护成本越大，增删改效率降低

SQL优化

insert优化

• 批量插入

建议使用批量插入，因为单此插入的话每次都要与数据库建立连接然后通过网络进行传输，降低了插入效率

• 手动提交事务

MySQL默认执行每条sql都会开启事务并提交事务，我们可以在多条sql前后手动控制事务的开启与提交

• 主键顺序插入

主键按顺序插入比乱序插入效率更高

• 使用load加载大数据量

如果一次性需要插入大量数据，使用insert语句插入性能较差，可以使用load命令进行插入，load加载文件到数据表中

主键优化

• 主键按顺序插入，如果乱序插入则会出现页分裂，也就是存储索引的B+Tree某些结点的中间元素向上分裂，选择auto_increment自增主键

• 降低主键索引的长度，因为二级索引叶子存主键数据，如果主键过长，导致二级索引的查找时耗过长，耗费大量的磁盘IO

• 尽量不要使用UUID或其他自然数据作为主键，例：身份证，本身主键过程并且无法保证顺序插入

order by优化

默认创建的索引会按照升序排序

• 根据合适的字段建立索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引，避免回表查询
• 多字段排序，一个升序，一个降序，注意联合索引创建的规则

create index 索引名 on 表(字段1 desc，字段2 asc)

• 大数据量排序时，可适当增加排序缓冲区sort_buffer_size（默认为256k）

group by优化

• 分组操作，可以通过索引提高效率
• 分组操作，索引的使用也是要满足最左前缀法则

limit分页查询优化

通过覆盖索引+子查询的方式优化

select * from student s，(select id from student limit 20000,10) i where s.id=i.id；

count优化

• count(*) count(主键) ：统计表的总行数
• count(字段)：统计的是该字段不为空时的总记录
• count(1)：在表的中添加1这个列，然后统计行数

区别

• count(*)：InnoDB引擎遍历表时，不会把数据取出来，而是进行优化，服务层直接按行累加
• count(主键)：遍历表的每一行，获取主键值，按行累加
• count(字段)：
  • 当该字段没有not null约束时，遍历整张表，获取每行该字段值，按行累加
  • 当该字段有not null约束时，遍历整张表，获取每行该字段值，判断是否为null，不是null进行累加
• count(1)：遍历整张表，不取值，服务层对于每一行放一个数字1进去，直接按行累加

故按照效率排序：count(*) = count(1) > count(主键) > count(字段)

故尽量使用count(*)

update优化

• 使用update更新数据时，尽量使用索引字段更新，否则行锁会升级为表锁，如果是表锁则并发性能会降低

• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则行锁会升级为表锁