MySQL索引以及SQL优化

索引：

索引是帮助MySQL高效获取数据的数据结构（有序）。在数据之外系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的数据结构选择

那么什么样得数据结构可以作为索引呢？这里分享一个数据结构可视化网站数据结构可视化。

二叉树：

二叉树相比于链表，一般情况下可以降低链表的深度，从而提升查询效率。但是当我们顺序插入一段数据，二叉树就会退化为链表，并且当数据量大的时，树的深度也比较深，因此不选则二叉树。

红黑树：

红黑树解决了二叉顺序插入时退化为链表的弊端，但是仍然不能解决数据量大时深度较深的问题。

那么不妨通过增回一个结点存储多个元素的情况，这样数据量大就可以减少树的深度。

B树：

b树解决了数据量大时树深度太深问题，但b树作为索引仍有以下弊端：

• 非叶子结点即保存数据又保存关键字本身,占用空间比较大。
• 各个叶子结点相互独立，各叶子节点独立存在，没有指针关联，通过关键字顺序查找和范围查找效率低。

B+树：

针对于b树的这些弊端，可以使用b+树，一个n阶的b+树具有以下特征：

• 一颗m阶的B+树，每个结点至多有m棵子树
• 非叶结点不保存数据,只保存用做索引的关键字,所有数据都保存在叶子结点中。
• 各结点会存在数据冗余(出现重复关键字)
• 各叶子节点通过指针链相连，便于通过关键字顺序查找和范围查找。

因此MySQL的数据库引擎InnoDB采用b+树作为索引。但是MySql中的B+树，对原来的B+树做了一个优化，每一个叶子结点都会指向相邻的叶子结点，提高了区间访问性能。

那么为什么不采用hash作为索引呢？ 

Hash:

hash虽然在一般情况下具有比b+树更高的查询效率，但是仍有以下弊端：

• 只能用于对等比较（=，in）,不支持范围查询（between，> <）
• 无法利用索引完成排序操作

以上说了为什么要使用b+树作为索引，下来说一说MySQL中索引的分类：

索引分类：

我们可以根据索引的特征以及存储位置将索引分为：

根据索引特征：

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建只有一个	PRIMARY
唯一索引	避免表中同一列的数据值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
唯一索引	查找文本中的关键字	可以有多个	FULLTEXT

根据存储位置：

分类	含义	特点
聚集索引	索引和存储位置放在一起，叶子结点存储行数据	必须有只能有一个
二级索引	索引与存储位置分开，叶子结点存储与之关联的主键	可以存在多个

注意聚集索引的选取规则：

• 如果该表存在主键索引，那么就将该主键索引作为聚集索引。
• 如果不存在主键索引，那么将选取合适的（第一个）唯一索引作为聚集索引。
• 如果既不存在主键索引，又不存在唯一索引，那么InnoDB就自动生成rowId作为隐藏的聚集索引。

 SQL优化：

SQL优化，大部分情况下都是对于索引的优化，因此在SQL优化前，了解索引可以更好的帮助理解SQL优化。

前置知识：

索引的使用原则：

最左匹配原则：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则则指的是查询从索引的最左侧开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后边的字段索引失效）。

注意：查询字段必读包含索引左边字段，但是与位置无关。当跳过索引其中一个字段，可能会导致索引部分失效。

范围储存：当查询条件包含 > <时候，后部分的索引将失效。

措施：将 > < 换成 >= , <=;

索引列运算：不要在索隐列进行运算，包含字符串截取。

字符串不加引号：索引会失效

模糊查询：以“%”开头的，“_”开头的索引失效。

or连接的条件：or两边都必须有索引，违反则索引失效。

数据分布影响：如果全表扫描比索引快，则索引失效。（使用索引比全表扫描还慢）

回表查询：

假设有数据库student表字段分别为（id,name,gender,birthday）其中id为主键，name建立普通索引。主键建立聚簇索引，将整条row存储到 叶子结点。name 为二级索引，将这条数据的主键放在叶子结点。

执行以下SQL语句：

select * from student where id = 2 ;
--此时走的是聚簇索引
select * from student where name = 'example';
--此时会出现回表查询，二次查询红黑树
select id,name, from student where name = 'example';
-- 只会走二级索引，不会回表查询

所谓回表查询就是指二次查询红黑树的情况，这样势必会降低查询效率，那么我们可以针对要查询的字段建立覆盖索引，针对上边的例子，针对name,和id建立二级索引。那么执行

SELECT id，name, FROM student where name = 'example'；就不会回表查询。

前缀索引：

当我们查找条件是varchar或者text类型，此时交建立索引就会导致索引太大，从而造成空间浪费。

可以值针对文本的前部分建立索引，而不是针对整个文本。

选修长度时可以综合长度和选择性选取。

选择性 = 当前索引不重复字段总数/中行数，数值越大选择性越高。

语法：CREATE INDEX 索引名 ON 标名（字段名（截取长度））

CREATE INDEX idx_user_email_5 ON USER（email(5)）:针对email前5个建立前缀索引。

单例索引和联合索引选择：

当我们的查询条件为多条件查询时，可以考虑使用联合索引。

联合索引选取得当可以避免回表查询，从而提升效率。

同时还需要注意联合索引中字段的顺序

索引设计原则：

• 针对数据量较大，且查询比较频繁的表建立索引。
• 针对常作为查询条件（where），排序（order by），分组（group by）操作的字段建立索引。
• 尽量选取区分度高的作为索引，尽量建立唯一索引，区分度越高，效率越高。
• 如果索引字段为长字符串类型，可以针对字符串的类型，建立前缀锁索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
• 要控制表中索引的数量，索引并不是越多越好，维护索引的代价很大，影响增删改性能。
• 如果索引能存储NULL值，在创建索引时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，可以更好的确定哪个索引最有效地用于查询。

SQL优化：

INSERT优化：

• 当一次添加多条数据时，可以使用批量插入。
• 手动提交事务。（默认插入一条数据，提交一次事务）
• 主键顺序插入。
• 当需要插入大量数据（千万级条），可以使用load将本地文件导入到数据库。

连接数据库- mysql --local-infile -u root -p

设置全局参数local-infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1;

执行load指令：

load data local infile '/路径' into table ‘表名’fields terminated by ',' lines terminated by '\n'

主键优化：

数据组织方式：InnoDB中数据时根据主键顺序组织的，这种存储方式的表称为索引组织表。

InnoDB的逻辑组织形式：表，段，区，页，行。

页分裂：主键乱序插入可能出现的一种情况。

页合并： 当删除一条数据时，页的物理内存并没有被删除，只是被标记无用。当发现当前页可以被合并时会被合并。

设计原则：

• 主键长度应该短
• 主键应该采用自增插入。
• 尽量不要使用UUID或者其他自然属性如身份证号。
• 尽量不要对主键修改。

order by优化：

using filesort：表示索引排序时，是将数据通过全表扫描或者索引找到放入到缓存区中，然后在缓存区中进行排序，再返回。

using index：表示直接利用索引排序然后返回。

using index 排序效率高。

注意：在创建联合索引时，指定字段的排序（默认是ASC）;

设计原则：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引
• 多字段排序时，一个升序，一个降序，此时需要注意联合索引在创建时的规则。
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大缓冲区大小sort_buffer_zize（默认为256K）

group by优化：

• 在分组操作时，可以通过索引来提升效率。
• 分组操作时，索引使用依然满足最左前缀法则。

limit优化：

当查询的页数太大时，可以使用覆盖索引+子查询优化。

例如：当我们有张表student（id,name,birthday,place；该表中有1000000多条记录。当我们查询

select * from student limit 99980,10;
--此时的查询效率很低，可以优化为
select s.* from student,(select id from student limit 99980,10) a where s.id = a.id;
--将a表独立看成一张表，采用联合查询

count优化：

• count（主键）：InnoDB引擎会遍历整张表，把每一行的主键id值取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
• count（字段）：没有not null约束：InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。没有not null约束：InnDB引擎会遍历整张表的每一个字段值都取出来，返回给服务层，直接按行进行累加。
• count（1）：InnDB引擎遍历整张表，但不会取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
• count（*）：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
• 按照效率排序：count（字段）《 count（主键）《 count（1）≈ count（*）

update优化：

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会将行锁升级为表锁，降低并发性。