创建高性能索引-查询性能优化索引大大减少了服务器需要扫描的数据量，可以帮助服务器避免排序和临时表，可以将随机I/O变为顺

创建高性能的索引

索引，在mysql中也叫“键key”是存储引擎用于快速找到记录的一种数据结构。

索引基础

要理解MYSQL中索引是如何工作的，最简单的方法就是去看看一本书的“索引”部分：如果想在一本书中找到某个特定的主题，一般会先看书的“索引”，找到对应的页码。

索引的优点

索引大大减少了服务器需要扫描的数据量
可以帮助服务器避免排序和临时表
可以将随机I/O变为顺序I/O

索引的类型

索引有很多类型，可以为不同的场景提供更好的性能。

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。
从应用层次来分：普通索引，唯一索引，复合索引
根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

平时讲的索引类型一般是指在应用层次的划分

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

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

复合索引：即一个索引包含多个列

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

B+树索引

1.B+树首先是有序结构，为了不至于树的高度太高，影响查找效率，在叶子节点上存储的不是单个数据，而是一页数据，提高了查找效率，而为了更好的支持范围查询，B+树在叶子节点冗余了非叶子节点数据，为了支持翻页，叶子节点之间通过指针连接；
2.B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接；
3.在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高；
4.在 Innodb存储引擎中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来访问数据的话， Innodb存储引擎首先通过 Secondary Index 的相关信息，通过相应的索引键检索到 Leaf Node之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。MyISAM存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在 Leaf Nodes上面出了存放索引键信息之外，再存放能直接定位到 MyISAM 数据文件中相应的数据行的信息（如 Row Number ），但并不会存放主键的键值信息；

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

Hash是k,v形式，通过一个散列函数，能够根据key快速找到value
哈希索引就是采用一定的hash算法，把键值换成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需要一次hash算法即可立即定位到相应的位置，速度非常快。
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的。例如在数据列(A,B)上建立哈希索引，如果查询只有数据列A ，则无法使用该索引。
哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）也不支持任何范围查询，例如WHERE price>100
访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

InnoDB支持自适应哈希索引

B+树索引和hash索引的明显区别：

1、如果是等值查询，那么hash索引有明显的优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个键值是唯一的，如果不唯一，则需要先找到下标位置再链式查找
2、从示意图可以知道，hash索引无法支持范围查询，因为原先是有序的键值，但是经过hash算法后，有可能变成不连续的，就没有办法利用索引完成范围查询检索数据。
3、同样，hash索引也没办法利用索引完成排序，以及like xxx%这样的模糊查询（范围查询）。
4、hash索引也不支持多列联合索引的最左前缀匹配规则。
5、B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键的情况下，hash索引的效率也是极低的，因为存在hash碰撞问题。

总结：

单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中包含尽可能多所需要的行。使用索引可以创建位置引入以提升效率。
按顺序访问范围数据是很快的，有两个原因：第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多(特比对机械硬盘)。第二，如果服务器能够按照需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了
索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎不需要再回表找行。这避免了大量的单行访问。

mysql性能优化

1、当只要一行数据时使用 limit 1 查询时如果已知会得到一条数据，这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。

2、选择正确的数据库引擎 Mysql 中有两个引擎 MyISAM 和 InnoDB，每个引擎有利有弊。 MyISAM 适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要 update 一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前 update 操作完成之后才能继续进行。另外，MyISAM 对于 select count(*)这类操作是超级快的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比 MyISAM 还慢，但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如:事物。

用 not exists 代替 not in

Not exists 用到了连接能够发挥已经建立好的索引的作用，not in 不能使用索引。Not in是最慢的方式要同每条记录比较，在数据量比较大的操作不建议使用这种方式。

4.对操作符的优化，尽量不采用不利于索引的操作符

如:in not in is null is not null <> 等某个字段总要拿来搜索，为其建立索引: Mysql 中可以利用 alter table

语句来为表中的字段添加索引，语法为:alter table 表名 add index (字段名);

感谢博主分享： MySQL查询优化:LIMIT 1避免全表扫描

优化特定类型的查询

一、优化COUNT()查询

本节介绍的多数优化都是和特定的版本有关的，所以对未来mysql的版本未必适用。可能某一天优化器自己也会实现这里列出的部分或者全部优化技巧。

count()的作用:是一个特殊的函数，两种作用它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值时非空的(不统计NULL).如果在COUNT()的括号中指定了列或者列的表达式，则统计的是这个表达式有值的结果数。 2.count()的另一个作用是统计结果集的行数。当MYSQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是使用COUNT(*)的时候，这种情况下通配符 * 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数；常见的错误，在括号内指定一个却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用 COUNT( * ) ，这样写意义清晰，性能也会很好。
容易产生误解就是：MyISAM的COUNT()函数总是非常快，不过这是前提条件的，即只有没有任何WHERE条件的COUNT(*)才非常快，因为此时无须实际地计算表的行数。MYSQL可以利用存储引擎的特性直接获取这个值。如果MYSQL知道某列col不可能为NULL值，那么MYSQL内部会将COUNT(col)表达式优化为COUNT( * )
简单的优化

比如快速查找所有ID大于5的城市,先找出ID小于等于5的城市数

select (select count(*) from world.city) - count(*) from world.city where id <=5

使用近似值有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值代替。比如统计网站的当前活跃用户数是多少(where 条件很复杂，去掉当前非活跃用户、去掉某些特定ID的默认用户)。更进一步的优化则可以尝试删除DISTINCT这样的约束来避免文件排序。
更复杂的优化

通常来说，COUNT() 都需要扫描大量的行才能获取精确的结果，因此是很难优化的。除了前面的方法，在MYSQL层面还能做的就只有覆盖索引扫描了。如果还不够，就需要修改应用的架构，可以增加汇总表或者增加类似的Memchached这样的外部缓存系统。

二、优化关联查询

确保ON或者USING字句中的列上有索引。在创建索引的时候就要考虑关联的顺序。当表A和表B用列C关联时，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列建立索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上建立索引。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MYSQL才有可能使用索引来优化这个过程。
当升级mysql时需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能变成笛卡儿积，不同类型的关联可能会生成不同的结果等。

三、优化子查询

尽可能使用关联查询代替，至少当前的MYSQL版本需要这样。如果使用的是MYSQL5.6或者更新版本或者MariaDB，那么就可以直接忽略关于子查询的这些查询了。

四、优化GROUP BY和 DISTINCT

如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY子句时，结果集会自动按照分组的字段排序。如果不关注结果集的顺序，而这种默认排序又导致了文件的排序，则可以使用ORDER BY NULL，让MYSQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按照需要的方向排序。

五、优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MYSQL 对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。可以通过EXPLAIN来观察其执行计划，特别注意分组是否是通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句看执行计划是否相同。

很多时候，如果可以，在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终的结果。

最好的办法尽可能的将WITH ROLLUP功能转移到应用程序中处理。

六、优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MYSQL需要做大量的文件排序操作。

比较坏的情况是，在偏移量非常的时候(翻页到非常靠后的页面)例如可能是LIMIT 1000，20这样的查询，这时MYSQL需要查询10020条记录然后只返回20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。需要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需要的的列。对于偏移量很大的时候，这样做的效率会提升非常大。

select film_id,description from sakila.film order by title limit 50,5;

如果这个表比较大，那么这个查询最好改成下面的样子：

select film.film_id,film.description 
from sakila.film inner join
(select film_id from sakila.film order by title limit 50,5) AS lim USING（film_id）)

这里的“延迟关联”将大大提升查询效率，它让MYSQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有的列。这个技术也可以用于优化关联查询中的limit子句。

有时候也可以将limit查询转换为已知位置的查询，让MYSQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询改为：

select film_id,description From sakila.film where position between 50 and 54 order by postion

对数据进行排名的问题，但往往还会同时和GROUP BY混合使用。在这种情况下通常都需要预先计算并存储排名信息。

LIMIT和OFFSET的问题，其实是OFFSETD问题，它会导致MYSQL扫描大量的不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

例如若需要按照租借记录做翻页，那么可以根据最新一条租借记录向后追溯，这种做法可行是因为租借记录的主键是单调增长的。

select * from sakila.rental order by rental_id desc limit 20;

假设上面的查询返回的是主键为16 049到16 030的租借记录，那么下一页查询就可以从16 030这个点开始该技术的好处是无论翻页到多么后面，其性能都会很好。

select * from sakila.rental where rental_id < 16030 order by rental_id desc limit 20

其他优化办法：

使用预先计算的汇总表
或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。
使用Sphinx优化一些搜索操作

七、优化 SQL_CALC_FOUND_ROWS

分页时，常用的技巧是在limit语句中加上SQL_CALC_FOUND_ROWS 提示(hint)，这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。看起来，MYSQL做了一些非常高深的优化，像是通过某种方法预测了总行数。但实际上，mysql只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以后，不管是否需要，mysql都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足limit的行数后就终止扫描。所以提示的代价可能非常高。

一个更好的设计师将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么我们每次查询的都是limit返回21条记录，如果第21条记录存在，那么我们就显示“下一页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了。

另一种是先获取并换成较多数据。

八、优化UNION查询

mysql总是通过创建并充实临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化(例如，直接将这些子句冗余地写一份到各个子查询中)。

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MYSQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做一次唯一性检查。这样做的代价很高。即使有ALL关键字，MYSQL也会使用临时表存储结果。事实上，MYSQL总是将结果放到临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的(例如，MYSQL可以直接把这些结果返回给客户端)

MySQL 事务介绍

MySQL 和其它的数据库产品有一个很大的不同就是事务由存储引擎所决定，例如 MYISAM,MEMORY,ARCHIVE 都不支持事务，事务就是为了解决一组查询要么全部执行成功，要么全部执行失败。

MySQL事务默认是采取自动提交的模式，除非显示开始一个事务。

修改自动提交模式，0=OFF,1=ON

注意:修改自动提交对非事务类型的表是无效的，因为它们本身就没有提交和回滚的概念，还有一些命令是会强制自动提交的，比如 DLL 命令、lock tables 等。

SET AUTOCOMMIT=OFF 或 SET AUTOCOMMIT=0

事务的四大特征是什么?

在技术面试中，面试官经常能够问到关于数据库事务相关的，通常数据库事务具备四大特性(ACID)，分别是：

1、原子性

2、一致性

3、隔离性

4、持久性

创建高性能索引-查询性能优化