如何创建合理的索引，索引如何优化？

分析&回答

合理的建立索引

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
3. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
4. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
5. 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
6. 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
7. 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值
8. MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了 索引也不会使用
9. 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作
10. 索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

SQL语句注意点

1. 当结果集只有一行数据时使用LIMIT 1
2. 避免SELECT *，始终指定你需要的列，从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
3. 使用连接（JOIN）来代替子查询(Sub-Queries)连接（JOIN）.. 之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的字段属性长度
5. 尽可能的使用NOT NULL
6. 固定长度的表会更快
7. 拆分大的DELETE 或INSERT 语句
8. 查询的列越小越快
9. 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
10. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
11. 使用mysql内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
12. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
13. 在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。
14. 有些where条件会导致索引无效：
    • where子句的查询条件里有！=，MySQL将无法使用索引。
    • where子句使用了Mysql函数的时候，索引将无效，比如：select * from tb where left(name, 4) = ‘xxx’
    • 使用LIKE进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 时索引无效

跟深层次的注意点

1. 用>=替代> 高效: SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3
2. 用UNION替换OR (适用于索引列)
3. 用UNION-ALL 替换UNION ( 如果有可能的话)
4. 用WHERE替代ORDER BY
5. 尽量不要使用临时表，除非你必须这样做。
6. 尽量不要使用TEXT数据类型，VARCHAR可以更好的处理你的数据。
7. 使用参照完整性，定义主健、唯一性约束和外键，这样做可以节约大量的时间。
8. mysql通信数据包的大小默认是1M, 因此在批量插入或者批量更新时, 要注意拼接的sql语句的长度是否超过了1M, 如果想修改默认包大小, 把mysql的配置文件（my.ini）中的max_allowed_packet设置成你想要的大小. 如果无法修改线上数据库的配置, 可以在JDBC连接时加上: blobSendChunkSize=50000&useServerPrepStmts=true&emulateUnsupportedPstmts=false&maxAllowedPacket=10000000
9. MySQL的timestamp类型时间范围between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'，超出这个范围则值记录为'0000-00-00 00:00:00' ,当数据库中的TIMESTAMP类型的字段值为 '0000-00-00 00:00:00'时，使用此方法进行读取，会抛出异常：Cannot convert value '0000-00-00 00:00:00' from column 1 to TIMESTAMP，这是因为JDBC不能将'0000-00-00 00:00:00'转化为一个为一个java.sql.Timestamp, 为了解决这个问题可以在JDBC URL中加入zeroDateTimeBehavior信息.
10. 查询表中某字段有重复记录个数的方法 : select id,count(id) from @table group by id having(count(id)>1)
11. MYSQL保存BOOLEAN值时用1代表TRUE,0代表FALSE，boolean在MySQL里的类型为tinyint(1)

反思&扩展

如何判断SQL的执行效率

通过explain 关键字分析效率低的SQL执行计划。

EXPLAIN  SELECT ……
变体：
1. EXPLAIN EXTENDED SELECT ……
将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 
2. EXPLAIN PARTITIONS SELECT ……
用于分区表的EXPLAIN

比如： explain select sum(moneys) from sales a, company b where a.company_id = b.company_id and a.year = 2022;
复制代码

执行计划的局限

1. EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
2. EXPLAIN不考虑各种Cache
3. EXPLAIN不能显示MySQL在执行查询时所作的优化工作
4. 部分统计信息是估算的，并非精确值
5. EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

索引分析方法

查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

mysql> show status like 'Handler_read%'; 
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 9      |
| Handler_read_key      | 16     |
| Handler_read_last     | 0      |
| Handler_read_next     | 680908 |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 0      |
| Handler_read_rnd_next | 935519 |
+-----------------------+--------+
7 rows in set (0.00 sec)
复制代码

两个简单实用的优化方法：

• 分析表的语法如下:(检查一个或多个表是否有错误)

mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option = 
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED} 
mysql> check table sales; 
+--------------+-------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+--------------+-------+----------+----------+ 
| sakila.sales | check | status | OK | 
+--------------+-------+----------+----------+ 
1 row in set (0.01 sec)
复制代码

• 优化表的语法格式：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

mysql> optimize table sales; 
+--------------+----------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+--------------+----------+----------+----------+ 
| sakila.sales | optimize | status | OK | 
+--------------+----------+----------+----------+ 
1 row in set 0.05 sec)

喵呜面试助手：一站式解决面试问题，你可以搜索微信小程序 [喵呜面试助手] 或关注 [喵呜刷题] -> 面试助手 免费刷题。如有好的面试知识或技巧期待您的共享！