Mysql索引

索引优先级

索引原理：

　　通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

B_TREE索引：

 

 

 

mysql 中的索引：

1）B_TREE索引 ：

最常见的索引，大部分引擎都支持。

2）HASH索引：

相对简单，只有memory和heap引擎支持hash索引。

hash索引适用于key-VALUES 查询，通过hash索引要比BTREE索引查询更迅速；
hash索引不支持范围查询，例如<, > ,>= <= 这类操作。并且只有 在 = 的条件下才会使用where索引。
只有memory 引擎支持，使用场景简单。

3）R_TREE索引（空间索引）

空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。

4）Full_Text索引（全文索引）

也是MyISAM的一个特殊索引类型，InnoDB从5.6开始支持Full Text 索引。

 

1、索引优点：

加快查询速度

2、索引缺点：

降低数据插入、删除和更新的速度，占用磁盘空间

3、创建索引原则：

1）为用于搜索、排序或分组的列创建索引，而对于输出显示的列不创建索引；

即：最佳索引列是在where子句中的列，连接子句中的列，或出现在group by、order by子句中的列。

2）列的基数越高，索引的效果越好；

列的基数：列中所有非重复值的个数。

3）索引短小值：尽量使用较小的数据类型（能用25个字符的，就不要用100字符）；

短小值可以让比较操作更快，从而加快索引查找的速度；

短小值可以让索引短小，从而减少对磁盘I/O的请求；

对于更短小的键值，键缓存里的索引块可以容纳更多的键值。如果Mysql能在内存里同时容纳更多的键，那么就可以在不去磁盘读取更多索引块的情况下，提高找到键值的几率。

 

4）索引字符串值的前缀：对字符串进行索引，尽可能指定前缀的长度；

快且省空间。

5）利用最左前缀

当创建N个列的复合索引时，Mysql实际上是创建N个索引。复合索引相当于多索引，所以第一个索引列的值比较关键。

如复合索引（C1、C2、C3）可用于搜索：C1、C2、C3和C1、C2或C1，却不能搜索C2、C3

6）不要建立过多的索引；

 

7）让参与比较的索引类型保持匹配

InnoDB：B树索引

MyISAM：B树索引和R数索引（空间类型）

MEMORY：默认散列索引，也支持B数索引

 

散列索引：在使用运算符=或<=>完成精确匹配的比较操作，速度非常快；但用于查找某个范围值的比较，速度欠佳。

 

B数索引：在使用<、<=、=、>=、>、<>、!=和between运算符，进行精确比较或范围比较时，效率高；如果匹配纯字符串，而不是通配符作为开头的，还可以用LIKE进行匹配。

 

8）利用慢查询日志找出那些性能低的查询；

慢查询日志：是一个文本文件。如果某个给定的查询在这个日志里频繁出现，那么这个查询可能就不是最优的，需要改写。

通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

 

4、优化程序对索引的利用

1）*分析表

analyze table 表名；使服务器生成关于键值分布情况的统计数据。

2）*使用EXPLAIN验证优化程序的操作

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

 

详见：http://blog.csdn.net/zhuxineli/article/details/14455029

 

 

3）通过show profile 分析sql

通过 having_profiling参数，能够查询是否支持profile

为1即为开启。

 

>select count(*) from  payment:
>show profiles; //可以查询到该sql 的query ID为4；假设为4
>show profile for query 4; //能够查询到执行过程中线程的每个状态和消耗的时间；

 

 

4）/通过trace 分析优化器如何选择执行计划

5.6后提供了对sql的跟踪trace，通过trace文件能够进一步了解为什么优化器选择了A执行计划而不选择B执行计划。

使用方式：
首先打开track，设置格式为JSON，设置track最大能有使用内存的大小，避免解析过程中因为内存过小而不能完整显示。
>set optimizer_trace="enable=on",END_MARKERS_IN_JSON=ON;
>set optimizer_trace_max_mem_size = 1000000;
接下来执行想做trace的SQL语句。
select * from  .....;
最后 检查 INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道mysql 是如何执行sql 的
>SELECT * FROM INFORMATION.OPTIMIZER_TRACE \G;//会输出一个json格式的跟踪文件。

5）必要时给予优化程序提示或改写它

可以在表名的后面加上FORCE INDEX、USE INDEX或IGNORE INDEX。

也可以用带有STRAIGHT_JOIN，来强制各个表按照它们在from子句里的排列顺序来连接表。所以可以把能查询出行数最小的表放在最前面。

 

6）比较拥有相同数据类型的列

7）让索引列在比较表达式中单独出现

当索引列在复杂的算术表达式或函数中，不会使用索引。

8）不要在like模式的开始位置使用通配符

不能%String%，应该string%

9）测试查询的各种替代形式，并多次运行它们

10）避免过多使用自动类型转换

如把一个字符串类型与一个数值类型比较，就不会用到索引。

 

6、选择利于高效查询的数据类型

1）多用数字运算，少用字符串运算

2）当较小类型够用时，就不用较大类型

3）把数据列声明成 not null

因为查询时，不用判断值是否为null

4）考虑使用ENUM列

如果字符串列的基数低（即差异值的个数很少），则可以考虑转换成ENUM列。ENUM列的内部表示形式为数字。

 

5）*使用procedure analyse()

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

 

得到分析结果：

字段最小值，最大值，最小长度，最大长度，还有最后Optimal_fieldtype代表了表结构建议，可以根据数据分析建议来修改表结构，使之更符合数据存储规范。

 

6）整理表碎片

碎片占空间，影响读取的性能。

7）把数据压缩到BLOB或TEXT列

8）使用合成索引

做法：先根据表里的其他列算出一个散列值，把它存到一个单独的列里。然后通过检索散列值进行查询（缺点是只能用于精确匹配型查询）。数字型的散列值的存储效率非常高。

9）避免检索很大的BLOB或TEXT值

10）把BLOB或TEXT列剥离出来，形成一个单独的表

前提：这样做可以让你把这个表的其他列转换成行固定长度的格式。这样可以减少主表的碎片。

7、*使用SQL提示（SQL HINT）

在SQL语句中加入一些人为的提示来达到优化操作的目的。

1）USE INDEX

来提示希望mysql去参考的索引列表,就可以让mysql不再考虑其他可用的索引。

 

 

 

2） IGNORE INDEX

让MySQL忽略一个或多个索引

 

3）FORCE INDEX

强制mysql使用一个特定的索引

 

 

 

 

8、*组合索引的生效原则是

  从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
比如

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；

where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果；

where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；

where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

 

 

9、*mysql索引合并:一条sql可以使用多个索引

1)索引合并是把几个索引的范围扫描合并成一个索引。

2)索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。

3)这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

怎么确定使用了索引合并

在使用explain对sql语句进行操作时，如果使用了索引合并，那么在输出内容的type列会显示 index_merge，key列会显示出所有使用的索引。例如：

 

建表语句：

 

 

SQL 1:

 

 

 

SQL 2:

 

 

相同模式的sql语句，可能有时能使用索引，有时不能使用索引。是否能使用索引，取决于mysql查询优化器对统计数据分析后，是否认为使用索引更快。因此，单纯的讨论一条sql是否可以使用索引有点片面，还需要考虑数据。

 

10、*Mysql的group by的select条件可以不是分组字段或聚合函数。

MySQL扩展了GROUP BY的用途，因此你可以使用SELECT列表中不出现在GROUP BY语句中的列或运算。这代表“对该组的任何可能值”。你会得到非预测性结果。

 

                      

 

问题：

建表语句：

 

 

/mysql 索引优先级规则是如何的？

例如：
索引1：名字为idx_lastTime_deviceType_version, 

索引字段(`last_access_time`,`device_type`,`version`)

索引2：名字为idx_lastTime_deviceType_provinceId, 

索引字段(`last_access_time`,`device_type`,`province_id`)

 

在执行一条查询语句，用到last_access_time,device_type这2个索引。mysql如何去选择1或者2呢 

 

1）where、group by、order by、join语句上的优化。

 

2）在WHERE语句上的优化相同，然后MYSQS会根据你的 select ... 中的输出列来断定用哪一个索引，或者和其它表JOIN时哪个索引更有利。

 

 

 

 

3）索引的创建顺序也影响索引优先级。 

先建立idx_lastTime_deviceType_provinceId：

 

 

 

先建立idx_lastTime_deviceType_version：

 

 

查询sql 1:

 

这个SQL用的索引是idx_lastTime_deviceType_version（last_access_time, device_type, version）

查询sql 2:

 

这个SQL用的索引是idx_lastTime_deviceType_version（last_access_time, device_type, version）

 

查询sql 3:

 

这个SQL用的索引是idx_lastTime_deviceType_provinceId（last_access_time, device_type, province_Id）