MySQL索引入门学习以及多种SQL优化方案

---

一、介绍

数据库的索引就像一本书的目录，能够加快数据库的查询速度。

• MYSQL索引有四种PRIMARY、INDEX、UNIQUE、FULLTEXT， 其中PRIMARY、INDEX、UNIQUE是一类，FULLTEXT是一类。
• 这四种都是单列索引，也就是他们都是作用于单个一列，所以也称单列索引；但是所以一个索引也可以作用于多个列上，称为组合索引或复合索引。

二、单列索引

CREATE TABLE T_USER( ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL);

1、主键索引

PRIMARY：主键索引。索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表的时候就指定）

CREATE TABLE T_USER (
	ID INT NOT NULL,
	USERNAME VARCHAR (16) NOT NULL,
	PRIMARY KEY (ID)
)

• ALTER语句指定，创建表的时候同时创建索引

ALTER TABLE `grade_info` ADD PRIMARY KEY ( `ID` );

2、普通索引

• 建表时指定

CREATE TABLE T_USER (
	ID INT NOT NULL,
	USERNAME VARCHAR (16) NOT NULL,
	INDEX my_index(USERNAME(16))
) // 给列USERNAME建普通索引USERNAME_INDEX

• ALTER语句指定，创建表的时候同时创建索引

ALTER TABLE T_USER ADD INDEX my_index(USERNAME) //给列USERNAME建普通索引 U_INDEX

• 删除索引

DROP INDEX my_index ON t_user  //删除表t_user中的索引U_INDEX

3、唯一索引

UNIQUE：唯一索引。索引列的值必须是唯一的，但允许有空

• 建表时指定

CREATE TABLE t_user (
	ID INT NOT NULL,
	USERNAME VARCHAR (16) NOT NULL,
	UNIQUE my_unique(USERNAME)
) // 给列USERNAME添加唯一索引T_USER

• ALTER语句指定，创建表的时候同时创建索引

ALTER TABLE t_user ADD UNIQUE my_unique(USERNAME) //给列T_USER添加唯一索引u_index

• 删除索引

DROP INDEX my_unique ON t_user

4、全文搜索的索引

FULLTEXT：全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以，FULLTEXT 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。FULLTEXT 索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建FULLTEXT 索引，要比先为一张表建立FULLTEXT 然后再将数据写入的速度快很多。

• 创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT my_fulltext(content)
);

• 修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT my_fulltext(content)

• 直接创建索引

CREATE FULLTEXT INDEX my_fulltextON article(content)

5、复合索引

• 新建一张表

CREATE TABLE T_USER (
	ID INT NOT NULL,
	USERNAME VARCHAR (16) NOT NULL,
	CITY VARCHAR (10),
	PHONE VARCHAR (10),
	PRIMARY KEY (ID)
)

• 组合索引就是把多个列加入到统一个索引中，如新建的表T_USER，我们给USERNAME+CITY+PHONE创建一个组合索引

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE)  //组合普通索引

ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE) //组合唯一索引

• 这样的组合索引，其实相当于分别建立了（USERANME,CITY,PHONE USERNAME,CITY USERNAME,PHONE）三个索引。
  • 为什么没有（CITY,PHONE）索引呢？这是因为MYSQL组合查询“最左前缀”的结果。简单的理解就是只从最左边开始组合
  • 并不是查询语句包含这三列就会用到该组合索引
  • 这样的查询语句才会用到创建的组合索引

SELECT * FROM t_user where USERNAME="parry" and CITY="广州" and PHONE="180"
SELECT * FROM t_user where USERNAME="parry" and CITY="广州"
SELECT * FROM t_user where USERNAME="parry" and PHONE="180" 

这样的查询语句是不会用到创建的组合索引

SELECT * FROM t_user where CITY="广州" and PHONE="180"
SELECT * FROM t_user where CITY="广州"
SELECT * FROM t_user where PHONE="180"

---

三、索引缺点

1. 索引提高了查询的速度，但是降低了INSERT、UPDATE、DELETE的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件
2. 建立索引未见会占用一定的磁盘空间

四、注意事项

1. 只要列中包含NULL值将不会被包含在索引中，组合索引只要有一列含有NULL值，那么这一列对于组合索引就是无效的，所以我们在设计数据库的时候最好不要让字段的默认值为NULL
2. 使用短索引
   如果可能应该给索引指定一个长度，例如：一个VARCHAR(255)的列，但真实储存的数据只有20位的话，在创建索引时应指定索引的长度为20，而不是默认不写。如下

ALTER TABLE t_user add INDEX U_INDEX(USERNAME(16)) 优于 ALTER TABLE t_user add INDEX U_INDEX(USERNAME)

使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作。

3. 索引列排序
   Mysql在查询的时候只会使用一个索引，因此如果where子句已经使用了索引的话，那么order by中的列是不会使用索引的，所以order by尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。

4. Like 语句
   一般情况下不是鼓励使用like,如果非使用，那么需要注意 like"%aaa%"不会使用索引；但like“aaa%”会使用索引。

5. 不使用 NOT IN和<>操作

五、索引方式 HASH和 BTREE比较

1、HASH

用于对等比较，如"=“和” <=>"

2、BTREE

BTREE索引看名字就知道索引以树形结构存储，通常用在像 "=，>，>=，<，<=、BETWEEN、Like"等操作符查询效率较高；
通过比较发现，我们常用的是BTREE索引方式，当然Mysql默认就是BTREE方式。

六、SQL优化的几种方法

1. 为避免全盘查找，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 避免带有where判断条件的语句进行null值判断，这样会使引擎放弃索引进行全表扫描，如：

select id from t where num is null	
#可以在num上设置默认值为0，确保表中num列没有null值，然后这样查询：	
select id from t where num=0	

3. 避免带有where判断条件的语句使用!=或<>操作符，这样会使引擎放弃索引进行全表扫描。
4. 避免带有where判断条件的语句使用or 来连接条件，这样会使引擎放弃索引进行全表扫描，如：

select id from t where num=10	
union all #允许重复	
select id from t where num=20	

5. IN 和 NOT IN 也要慎用，否则也会导致全表扫描，如：

select id from t where num in(1,2,3)	
#对于连续的数值，能用 between 就不要用 in 了：	
select id from t where num between 1 and 3

6. LIKE查询的条件 最左面出现了% 这样就会进行全表查询，索引最左匹配原则。

select id from t where name like '%abc%'

7. 避免带有where判断条件中对字段进行表达式操作，这样会使引擎放弃索引进行全表扫描，如：

select id from t where num/2=100	
#应改为:	
select id from t where num=100*2
#和
select id from t where substring(name,1,3)='abc' #name以abc开头的id	
#应改为:	
select id from t where name like 'abc%'	

8. 避免带有where判断条件中“=”左边进行函数、算术运算或其他表达式运算，这样会使引擎放弃索引进行全表扫描。
9. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
   否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
10. 可以使用 EXISTS 代替 IN，如：

select num from a where num in(select num from b)	
#用下面的语句替换：	
select num from a where exists(select num from b where num=a.num)	

11. 并不是所有索引对查询都有效，SQL查询语句是根据表中的数据进行优化查询，当索引列中存在大量数据重复时，SQL查询语句可能不会去利用索引查询，如一表中有字段sex，male、female此类重复几率较高，那么即使在此类字段上建立索引也提高不了查询效率。
12. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但也同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
13. 尽量在数字型字段上建立索引，若只含数值信息的字段尽量不要设计索引，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
14. 尽量使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
15. 避免使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
16. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
17. 对于表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
18. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
19. 在使用临时表时，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
20. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
21. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
22. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
23. 尽量避免大事务操作，提高系统并发能力。
24. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

七、SQL规范性检查

1、select检查

1. UDF用户自定义函数
   SQL语句的select后面使用了自定义函数UDF，SQL返回多少行，那么UDF函数就会被调用多少次，这是非常影响性能的。

2. text类型检查
   如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错，需要评估谨慎使用。

3. group_concat谨慎使用
   gorup_concat是一个字符串聚合函数，会影响SQL的响应时间，如果返回的值过大超过了max_allowed_packet设置会导致程序报错。

4. 内联子查询
   在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

select id,name,(SELECT sex from t3 LIMIT 1) from t2

2、from检查

1. 表的链接方式
   在MySQL中不建议使用Left Join，即使ON过滤条件列索引，一些情况也不会走索引，导致大量的数据行被扫描，SQL性能变得很差，同时要清楚ON和Where的区别。

2. 子查询
   由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成Inner Join。

SELECT
	b.member_id,
	b.member_type,
	a.create_time,
	a.device_model 
FROM
	member_operation_log a
	INNER JOIN ( SELECT member_id, member_type FROM member_base_info WHERE `status` = 1 AND create_time BETWEEN '2020-10-01 00:00:00' AND '2020-10-30 00:00:00' ) AS b ON a.member_id = b.member_id;

3、where检查

1. 索引列被运算
   当一个字段被索引，同时出现where条件后面，是不能进行任何运算，会导致索引失效。

2. 类型转换
   对于Int类型的字段，传varchar类型的值是可以走索引，MySQL内部自动做了隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。

3. 列字符集
   从MySQL 5.6开始建议所有对象字符集应该使用用utf8mb4，包括MySQL实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询Join时字段字符集不匹配导致索引失效，同时目前只有utf8mb4支持emoji表情存储。

4、Group By检查

1. 前缀索引
   group by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。

#device_no字段类型varchar(200)，创建了前缀索引。
mysql> alter table users add index idx_device_no(device_no(64));

mysql> select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;

2. 函数运算
   假设需要统计某月每天的新增用户量，参考如下SQL语句，虽然可以走create_time的索引，但是不能消除排序，可以考虑冗余一个字段stats_date date类型来解决这种问题。

SELECT
	DATE_FORMAT( create_time, '%Y-%m-%d' ),
	count(*) 
FROM
	users 
WHERE
	create_time BETWEEN '2020-09-01 00:00:00' 
	AND '2020-09-30 23:59:59' 
GROUP BY
	DATE_FORMAT( create_time, '%Y-%m-%d' );

5、order by检查

1. 前缀索引
   order by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。

2. 字段顺序
   排序字段顺序，asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销。

6、limit检查

1. limit m,n要慎重
   对于limit m, n分页查询，越往后面翻页即m越大的情况下SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。

优化前：

EXPLAIN SELECT * FROM	t2 	LIMIT 0,1

优化后：

EXPLAIN SELECT * FROM	t2 tt1	
where id =(select id from t2  limit 0,1)

7、表结构检查

1. 表&列名关键字
   在数据库设计建模阶段，对表名及字段名设置要合理，不能使用MySQL的关键字，如desc, order, status, group等。同时建议设置lower_case_table_names = 1表名不区分大小写。

2. 表存储引擎
   对于OLTP业务系统，建议使用InnoDB引擎获取更好的性能，可以通过参数default_storage_engine控制。

3. AUTO_INCREMENT属性
   建表的时候主键id带有AUTO_INCREMENT属性，而且AUTO_INCREMENT=1，在InnoDB内部是通过一个系统全局变量dict_sys.row_id来计数，row_id是一个8字节的bigint unsigned，InnoDB在设计时只给row_id保留了6个字节的长度，这样row_id取值范围就是0到2^48 - 1，如果id的值达到了最大值，下一个值就从0开始继续循环递增，在代码中禁止指定主键id值插入。

4. NOT NULL属性
   根据业务含义，尽量将字段都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的NULL，会影响索引的稳定性。

5. DEFAULT属性
   在创建表的时候，建议每个字段尽量都有默认值，禁止DEFAULT NULL，而是对字段类型填充响应的默认值。

6. COMMENT属性
   字段的备注要能明确该字段的作用，尤其是某些表示状态的字段，要显式的写出该字段所有可能的状态数值以及该数值的含义。

7. TEXT类型
   不建议使用Text数据类型，一方面由于传输大量的数据包可能会超过max_allowed_packet设置导致程序报错，另一方面表上的DML操作都会变的很慢，建议采用es或者对象存储OSS来存储和检索。

8、索引检查

1. 索引属性
   索引基数指的是被索引的列唯一值的个数，唯一值越多接近表的count(*)说明索引的选择率越高，通过索引扫描的行数就越少，性能就越高，例如主键id的选择率是100%，在MySQL中尽量所有的update都使用主键id去更新，因为id是聚集索引存储着整行数据，不需要回表，性能是最高的。

2. 前缀索引
   对于变长字符串类型varchar(m)，为了减少key_len，可以考虑创建前缀索引，但是前缀索引不能消除group by， order by带来排序开销。如果字段的实际最大值比m小很多，建议缩小字段长度。

alter table member_info add index idx_member_name_part(member_name(10));

3. 复合索引顺序
   有很多人喜欢在创建复合索引的时候，总以为前导列一定是唯一值多的列，例如索引index idx_create_time_status(create_time, status)，这个索引往往是无法命中，因为扫描的IO次数太多，总体的cost的比全表扫描还大，CBO最终的选择是走full table scan。
   MySQL遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引列，到遇到第一个范围查询（>=, >,<, <=, between …… and ….）就停止扫描，索引正确的索引顺序应该是index idx_status_create_time(status, create_time)。

4. 时间列索引
   对于默认字段created_at(create_time)、updated_at(update_time)这种默认就应该创建索引，这一般来说是默认的规则。