Mysql学习笔记【索引部分】【高性能索引策略】【SQL优化】

总的来说索引有以下三个优点：

a、大大减少了服务器需要扫描的数据量

b、帮助服务器避免排序和临时表

c、将随机I/O变为顺序I/O

【高性能索引策略】

通常会根据WHERE条件，建立索引。

1、索引列应该保持独立

这里的独立指的是索引列不能是表达式的一部分，也不能是函数的参数

比如，下面例子无法使用已建立的num索引

select num from tb_num where num+1=5

 这是num列是不独立的，它还有“+1”的存在。应该这样写

select num from tb_num where num=4

 应该要养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧

比如获取10天内的订单数据，order_time已经建立了索引，错误的写法是

select * from tb_cf_order where TO_DAYS(CURRENT_DATE)-TO_DAYS(order_time)<=10

 

 我们想办法将索引列独立出来

select * from tb_cf_order where order_time<DATE_SUB(CURRENT_DATE,INTERVAL 10 DAY)

 

（MySQL explain详解 https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html)

2、使用B-Tree（B+Tree）索引方法（按照顺序存储数据）建立组合索引（多列索引），列顺序非常关键

比如有一张表tb_user，字段有id,last_name,first_name,date，建立了组合索引（last_name,first_name,date），注意顺序。

下面是B-Tree索引的限制

a、如果不是按照索引的最左列开始查找，则无法使用索引

比如语句 select * from tb_user where first_name='zhangsan' ，由于first_name列位于组合的中间，不是最左列（最左列是last_name）,上面的索引无法起作用。

b、不能跳过索引中的列

比如  select * from tb_user where last_name='zhangsan' and date='2019-12-04', 因为没有指定first_name，索引之中只有last_name起到索引作用。

c、如果查询中有某个列的查询范围，则其右边的所有列都无法使用索引优化查找。

比如 select * from tb_user where last_name='zhangsan' and first_name like='a%' and date='2019-12-04', 因为like为范围条件，这时起作用的索引只是last_name和first_name，date不能起作用。

除了这些限制，多列索引的列顺序至关重要。

比如已经建立了组合索引(order_time,order_name)

select * from tb_cf_order where order_time='2019-12-04' and order_name='XXXX'

 我们面临着是将order_time还是将order_name放前面的问题。

一般来说，选择选择性更高的列作为索引前列是比较合适的。

也就是说将order_name放前面会更好

3、对于长字段的索引，可以通过添加一个哈希列来做索引，达到B-Tree索引起到哈希索引效果

比如tb_url 表，有id,url字段，由于url可能很长，如果使用B-tree索引存储会导致存储内容很大（叶结点存有行数据）。这时可以通过添加列url_crc，该列存储的是url的哈希值（整数的哈希值），然后以该列作为索引列。

可以通过添加触发器为url_crc添加或者修改值

DELIMITER //
create trigger tb_url_crc_ins BEFORE INSERT ON tb_url FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(New.url);
END;
//
create trigger tb_url_crc_upd BEFORE UPDATE ON tb_url FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(New.url);
END;
//

 由于crc32()会随着数据的增大而出现大量的哈希冲突，所以后续可以考虑自己实现一个简单的64位哈希函数。

4、对于长字段的索引，还可以通过前缀索引进行优化

可以通过字段的前缀进行索引，比如针对order_name字段，

Alter table tb_cf_order add key (order_name(3))

 上面使用了前3个字符串作为索引，至于使用多少个前缀，可以通过计算count(distinct LEFT(字段,长度))/count(*)进行计算

计算的结果越接近1自然是最好的，等于1就是唯一索引了。上面只是举一个例子，具体用多少前缀还是得看数据的总量和特征。

比如可能会出现某个前缀（比如订单名‘荷包蛋XXX’，前缀‘荷包蛋’）出现大量重复的现象，很多人都喜欢吃荷包蛋，导致虽然总体来说3个前缀已经足够，但其实这个前缀索引效率不高。这时就要考虑是否把前缀加多n位。

前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：

Mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

5、主键（primary key）的选择

很多人在选择表主键的时候，往往会在uuid和int类型纠结，其实有两种更优的选择。一是使用binary作为主键类型，二是使用int作为主键，uuid作为引用id，也建立索引。uuid的索引是辅助索引（主键外的索引都是辅助索引，或者叫二级索引），要查询两遍，但性能要比直接用uuid作为主键要好。如果不指定主键，InnoDB会自动选择一个唯一的非空索引代替。如果没有建立这样的索引，那么InnoDB会隐式定义一个主键，作为主键索引。

使用UUID作为主键 具有以下优点：

• UUID值在表，数据库甚至服务器之间是唯一的，允许您合并来自不同数据库的行或在服务器之间建立分库。
• UUID值不会公开有关您的数据的信息，因此可以更安全地在URL中使用。例如，如果ID为10的客户通过http://www.example.com/customers/10/URL（restful风格） 访问其帐户，则很容易猜测到有客户11、12等，并且这可能是攻击的目标。
• 可以在任何地方生成UUID值，以避免往返数据库服务器。它还简化了应用程序中的逻辑。例如，要将数据插入父表和子表中，您必须先插入父表中，获取生成的ID，然后再将数据插入子表中。通过使用UUID，您可以预先生成父表的主键值，并在事务中同时将行插入到父表和子表中。

除了优点之外，UUID值还具有一些缺点：

• 与整数（4字节）甚至大整数（8字节）相比，存储UUID值（16字节）需要更多的存储空间。
• 调试似乎更加困难，想象一下表达式WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae' 而不是WHERE id = 10
• 使用UUID值可能会导致性能问题，原因是它们的大小且未排序。

UUID的性能是个大问题

这个比较更直观

https://kccoder.com/mysql/uuid-vs-int-insert-performance/

但是int类型又过于暴露，合并表也不方便（主键冲突）。

于是有了第三种的主键方式：

https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/

https://stackoverflow.com/questions/412341/how-should-i-store-guid-in-mysql-tables/7168916#7168916

http://www.mysqltutorial.org/mysql-uuid/

虽然这样会使得在写SQL语句的时候要添加上id的处理方法，但鱼和熊掌不可兼得，个人认为，用binary作为主键类型是最合适的。

当然，还有第四种主键方式，就是一开始所说的使用int作为主键，uuid作为引用id，主键和uuid分别建立索引。但是这种方式的性能有多高还不知道，比用uuid直接做主键高是肯定的。

6、覆盖索引

定义：索引的叶子节点已经包含了要查询的数据，不用从磁盘进行I/O操作，这样的索引叫作覆盖索引。

比如InnoDB的聚族索引就是覆盖索引，它们的叶子节点存有数据。理论上来说任何的列索引和组合索引，都可以优化成覆盖索引，前提是要写好where条件，就像（1、索引列应该保持独立）那样。

如果是覆盖索引，在使用explain的时候在extra一栏会有“Using index ”。

除了优化好where条件外，还可以使用延迟关联，达到使用覆盖索引的目的。

比如有语句

这个的语句执行流程是，先根据actor索引，找到相应的索引数据，但由于Mysql不能在索引中执行Like操作，Mysql只能转向从硬盘中读取相应的数据行来做like操作，然后返回所读取到的行数据，也就是说最后返回的数据是从硬盘中读取出来的。

我们可以通过延迟对其他列（除主键外的列）的访问，达到一个高性能的效果。

 这个语句流程是先根据actor索引，找到相应的索引数据，但由于Mysql不能在索引中执行Like操作，Mysql只能转向从硬盘中读取相应的数据行来做like操作，然后返回所读取到的id数据，再从聚族索引中返回行数据，也就是说最后的行数据是从索引中返回的。

虽然看起来第二种的操作复杂了一些，但因为减少了磁盘的I/O，反而速度更快。

当然，这样的复杂操作能提高性能也是有条件的，当where返回的数据比较少时才能起到一个比较好的作用。

7、建立索引时，出了考虑列的选择性，也应该优先考虑取值范围小的列，放到最左前缀

这样做是因为即使这些列通常不会在查询当中，但也不会影响到通常的查询，还可以避免索引的滥用，以及建立选择性更高的索引。

比如

select * from tb_user where age>18
上面的语句一般会建立(age)索引，但我们可以建立(sex,age)的索引。同时，为了匹配索引的最左前缀，查询的语句同样可以写成
select * from tb_user where sex IN('f','m','unknow') and age>18
这样的索引处理能适用不带性别的age查询，也适用带性别的age查询。

8、避免使用多个范围条件的查询

尽量想办法不要出现多个范围条件，如果一定要有多个范围，那也没什么好办法了。

9、排序并且需要分页查询时，应该使用延迟关联。

改为：

其他优化

https://www.ibm.com/support/knowledgecenter/en/SSZLC2_9.0.0/com.ibm.commerce.developer.doc/refs/rsdperformanceworkspaces.htm