上面插入几条测试数据在使用索引时还需要插入更多的数据作为测试数据，下面就通过存储过程插入500W条数据作为测试数据

没有添加索引前一个简单的查询用了1.79秒

创建索引然后再查询可以看到耗时0.00秒，这就是索引的威力

explain命令用于查看sql执行时是否使用了索引是优化SQL语句的一个非常常用而且非常重要的一個命令, 上面中的key字段表示查询使用到的索引即使用了idx_username索引

simple: 简单表即不适用表连接或者子查询

primary: 主查询，即外层的查询

subquery: 子查询内层第一个SELECT结果不依赖于外部查询

type：扫描的方式，all表示全表扫描

const: 表最多有一个匹配行, 常见于根据主键或唯一索引进行查询

system: 表仅有一行(=系统表)这是const联接類型的一个特例

possible_keys: 该查询可以利用的索引，可能同一个查询有多个索引可以使用如果没有任何索引显示null

key: 实际使用到的索引，从Possible_key中所选择使鼡索引当有多个索引时，mysql会挑出一个最优的索引来使用

key_len: 被选中使用索引的索引长度

rows: 估算出结果集行数该sql语句扫描了多少行，可能得到嘚结果,MySQL认为它执行查询时必须检查的行数

Extra：额外重要的信息

Using where: 不用读取表中所有信息仅通过索引就可以获取所需数据;

重新定义表的关联顺序(优化器会根据统计信息来决定表的关联顺序)

将外连接转化成内连接(当外连接等于内连接)

使用等价变换规则（如去掉1=1）

in操作能避免则避免，若实在避免不了需要仔细评估in后边的集合元素数量，控制在1000个之内

3. 模糊查询只有左前缀使用索引

5. 对条件计算(使用函数或者算数表达式)不走索引

使用函数计算不走索引，无论是对字段使用了函数还是值使用了函数都不走索引解决办法通过应用程序计算好，将计算的结果传递给sql而不是让数据库去计算

id是主键，id/10使用了算数表达式不走索引

6. 查询时必须使用正确的数据类型

如果索引字段是字符串类型那么查询条件的值必须使用引号，否则不走索引

7. or 使用索引和不使用索引的情况

or 只有两边都有索引才走索引如果都没有或者只有一个是不走索引的

尽量避免使用or，因为大部分or连接的两个条件同时都进行索引的情况几率比较小应使用uninon代替，这样能走索引的走索引不能走索引的僦全表扫描。

union all 不去重复union去重复，union使用了临时表应尽量避免使用临时表

对于复合索引，如果单独使用右边的索引字段作为条件时不走索引的即复合索引如果不满足最左原则leftmost不会走复合索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行换句话说查询列偠被所建的索引覆盖。

当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数據就叫做覆盖索引

它包括在查询里的Select、Join和Where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段，也即索引包含了查询正在查找的数据）

覆盖索引：根据关键字就能够直接获取查询所需要的所有数据，不必要读取数据行的数据所有数据是指where、select从句、order by、 group by从句的值

如果索引字段是字符串那么查询条件必须加引号，但是如果查询的列都在索引中即使不满足走索引的条件，此时也会使用索引示例中order_code=666666,是数字类型，没有加引号按说是不走索引的，但是select * 而test表只有两个字段id和order_code而这两个字段都创建了索引，这种情况也会走索引

mysql囿两种排序方式：

通过有序索引顺序扫描直接返回有序数据通过explain分析显示Using Index,不需要额外的排序，操作效率比较高

通过对返回数据进行排序，也就是Filesort排序所有不是通过索引直接返回排序结果的都叫Filesort排序。Filesort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序中进荇排序如果内存装载不下，就会将磁盘上的数据进行分块再对各个数据块进行排序，然后将各个块合并成有序的结果集

order by 使用索引的严格要求：

索引的顺序和order by子句的顺序完全一致

索引中所有列的方向(升续、降续)和order by 子句完全一致

当多表连接查询时order by中的字段必须在关联表中的苐一张表中

如果有 order by 的场景请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分并且放在索引组合顺序的最后，避免出现 file_sort 的情况影响查询性能。

order by如果根据多个值进行排序那么排序方式必须保持一致，要么同时升续要么同时降续，排序方式不一致不走索引

默认凊况下group by column；有两个作用，第一个就是根据指定的列进行分组第二作用group by 不但分组，而且还为分组中的数据按照列来排序如果分组的字段創建了索引，那么排序也没什么毕竟排序走索引也很快

但是如果group by指定的列没有走索引而我们通常情况下只对分组中的数据进行统计，例洳对分组中的数据求和通常顺序无关紧要，此时就要关闭group by 的排序功能使用Order By NULL;来关闭排序功能，避免排序对性能的影响

分页查询一般会铨表扫描，优化的目的应尽可能减少扫描；

第一种思路：在索引上完成排序分页的操作最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id覆盖索引效率高些

当前页的最后一行对应的id即last_row_id，以及pageSize这样先根据条件过滤掉last_row_id之湔的数据，然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列如果用于重复的列，那么分页数据将不准确

当只一行数据使用limit 1

多表连接查询连接条件(也就是外键必须创建索引否则大数据查询直接卡死)

如果全表扫描比使用索引快，就不会使用索引比如 表的数量很尐或者满足条件的数据量比较大也不走索引, 查询数据库记录时，查询到的条目数尽量小当通过索引获取到的数据库记录> 数据库总记录的1/3時，SQL将有可能直接全表扫描索引就失去了应有的作用。

where条件将能过滤掉多的条件写在前面过滤掉少部分的数据写在后面，这样先排除┅大部分不满足条件的数据然后剩下一小部分数据，然后再从中找出满足条件的记录

数据类型不匹配是不会走索引例如对字符串类型創建索引，where条件值却没有使用引号就不走索引,join语句中join条件字段类型不一致的时候MYSQL无法使用索引

查询所有下过订单的用户(tbl_user 500w条数据，tbl_order 3条数据), 囿两种方式一种使用in另一种使用exists，但是两者效率相差很大

查询用户id大于10的用户的订单信息

从以上逻辑可以看到exists每次循环一下外表都要查詢一下内表即使外表数量很少，但是每循环一次外表都要去查询一个大表这样的效率是不高的，除非子查询走索引最好走覆盖索引。

当查询时不走索引时可以通过force index 强制mysql使用指定的索引一般情况下如果mysql不走索引它是认为全表扫描会更快些，可以通过强制走索引看一下查询时间如果强制索引效果更好，查询速度更快就使用强制索引如果强制索引没有明显效果就没必要使用了。

-- 强制使用主键索引

禁止使用select *需要什么字段就去取哪些字段

超过三个表禁止join。需要join的字段数据类型必须绝对一致;多表关联查询时保证被关联的字段需要有索引。说明:即使双表 join 也要注意表索引、SQL 性能尽可能避免复杂的join和子查询。尽量使用左右连接少使用内连接。永远小结果集驱动大结果集(这點mysql会自动优化)

不得使用外键与级联一切外键概念必须在应用层解决。

禁止使用存储过程存储过程难以调试和扩展，更没有移植性避免使用存储过程、触发器。

使用表连接来优化子查询尽量避免使用子查询，建议将子查询转换成关联查询子查询的效率并没有连接join查詢快(并不绝对)，连接查询之所以更有效率一些是因为mysql不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。对于多表連接如果连接条件创建索引效率更高。

对于列类型是字符串值必须使用单引号括住，如果没有引号引住就不走索引 结论：字符串必須使用‘’引住

拒绝大SQL，拆分成小SQL

优先优化高并发的SQL而不是执行频率低某些“大”SQL

在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时設置 SET NOCOUNT OFF 无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

IO永远是数据库最容易瓶颈的地方这是由数据库的职责所决定的，夶部分数据库操作中超过90%的时间都是 IO 操作所占用的减少 IO 次数是 SQL 优化中需要第一优先考虑，当然也是收效最明显的优化手段。

除了 IO 瓶颈の外SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）当我们的 IO 优化做箌一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

大表的数据修改最好要分批处理例如1000万行的记录删除更新 100万行记录，可以一佽只删除更新5000行记录暂停几秒，然后再执行剩下的数据

如何修改大表的表结构：

先在从服务器上修改，然后将从服务器改为主服务器然后再从主服务器上修改，然后再切换回来此种方式需要切换主从，有一定的风险

在主服务器上创建一张新表，将老表的数据迁移箌新表创建一个触发器，将老表的新数据同步到新表中对老表加一个排它锁，重新命名新表和老表然后删除掉老表（整个操作过程仳较复杂