mysql查询优化

索引的用途：

1.在查询操作中把与where子句所给出的条件相匹配的的数据行尽快找出来；

2.在关联操作中把与其它数据表里的数据行相相匹配的数据行尽快找出来；

3.对于使用MIN()或MAX()函数的查询，如果数据列带索引，那么他的最大值和最小值能够被迅速找到而不用通过逐行检查的方法来查找；

4.mysql经常使用所引来迅速的完成order by子句和group by子句的分类和分组操作；

5.mysql可以通过使用索引来避免为一个查询整体读取数据行（索引作为唯一的待选取数据列）。

######################################################################################

索引的缺点：

1.索引会降低在带索引的数据列里插入删除修改数值的速度（写入一条数据行不仅要求写入数据到行，还要求所有索引都要做出改变。一个数据表有越多的索引，需要做出的改变越多，平均性能下降就越多。绝大部分数据表是读操作多写操作少，但对于那些写操作次数比较多的数据表来说，索引更新方面的开销可能会非常大）；

2.索引需要占据磁盘空间，多个索引会占据更大的空间。

综上，如果不需要某个特定的索引来加快查询速度，就不要创建它。

######################################################################################

挑选索引

1.尽量为用来搜索、分类或分组的数据列编制索引，不要为作为输出显示的数据列编制索引。
--期望索引    --where/联合子句中的数据列/order by/group by
--不期望索引    --select输出数据列
SELECT
    col_a
FROM
    tab_1 LEFT JOIN tab_2
    ON tab_1.col_b = tab_2.col_c
WHERE
    col_d = expr;
上述col_b和col_c即为联结子句中的数据列，如果MySQL能够使用相联结的数据列还优化查询，他就不会使用全数据表扫描，这样能删除掉大量潜在的数据表数据行组合。

2.综合考虑各数据列的维度势。
维度就是当前列所容纳的非重复值得个数。
数据列的维度越高，索引的使用效果越好。

3.对短小的值进行索引
比较操作更快地完成，加快索引查找速度；
索引的体积小，减少磁盘I/O；
缓存里的索引块可以容纳更多的键值；

InnoDB存储引擎使用的是聚集索引。
聚集索引：把数据行和主键值集中保存在一起的情况。
其他的索引都是些二级索引--他们保存着主键值和二级索引值。
所以对InnoDB而言，让主键尽量短小将更有好处。

4.为字符串值的前缀编索引

5.充分利用最左边的前缀
一个符合索引在工作时就相当于n个索引，因为最左边的数据列集合能够用于匹配数据行。
a b c联合索引index
a b c
a b
a
这样的组合可以使用索引。
b c
b
这样的组合无法使用索引。

6.让索引的类型与你打算进行的比较操作的类型保持匹配

InnoDB存储引擎总是使用“B树”索引
MyISAM通常也使用“B树”索引，但在遇到空间数据类型时会改用“R树”索引。
MEMORY存储引擎默认使用散列索引，但也支持“B树”索引。


散列索引在使用= <=>操作符进行精确匹配比较操作里速度极快。但是在用来查找一个范围的比较操作表现不佳（如id < 30或者weight BETWEEN 100 AND 150）。

“B树”索引在使用< <= = >= > <> != BETWEEN操作符进行的精确比较操作或范围比较操作里都很有效率；
如果匹配模式是以一个纯字符串而不是一个通配符开头的话，“B树”索引还可以用于使用LIKE操作符进行的模式匹配操作。

7.“慢查询”日志找出性能低劣的查询。

######################################################################################

MySQL的查询优化器

1.尽量使用数据类型相同的数据列进行比较；
2.使带索引的数据列在比较表达式中单独出现；
    WHERE col * 2 < 4;逐行检索
    WHERE col < 4 / 2;右侧表达式被优化器简化为2，然后对col数据列使用索引
3.不要在LIKE模式开始的时候使用通配符；
    WHERE last_name LIKE '%Mac%';
    WHERE last_name LIKE 'Mac%';使用索引匹配找到匹配的数据行
4.在许多场合，优化器对联结的优化效果要比对子查询效果好。在子查询运行的很慢时，尝试修改为一个联结；
5.避免过多使用MySQL的自动类型转换功能（转换耗时，而且有可能导致无法使用索引）；
    SELECT * FROM mytab WHERE num_col = '4';
    SELECT * FROM mytab WHERE num_col = 4;
    SELECT * FROM mytab WHERE str_col = 4;
    SELECT * FROM mytab WHERE str_col = '4';
######################################################################################

调度和锁定

MyISAM类型的数据表检索时非常快，但是表级别的锁定策略对混合检索和修改可能很不利；
InnoDB类型的数据表在有较多修改操作时可以提供很好的性能，行级别的锁定可以减少竞争问题，提高并发性。

从死锁的角度来说，数据表锁定比小范围锁定要好。
使用数据表锁定，死锁问题绝对不会出现。服务器会决定哪些数据表需要对查询锁定，并会提前将他们全部锁定。
对于InnoDB类型的数据表来说，死锁问题可能会出现。在事务开始的时候，处理程序没有获得必要的锁定，在事务处理的过程中，只在必要时才需要锁定。会出现这样的情况，两个查询会获得锁定，然后在已经保持的锁定未被解除时视图获得进一步的锁定，每个客户程序在他能够继续进行之前保持着其他客户程序需要的锁定，导致产生死锁。服务器必须终止其中的一个事物处理。

######################################################################################