MySql 的性能分析和索引建立与优化
1、索引简单语法
-
建立
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length)); -
删除
DROP INDEX [indexName] ON mytable; -
展示
SHOW INDEX FROM table_name\G
2、MySql 的性能分析
- 查看执行计划
Explain + SQL语句 - 怎么看/每个字段的意思
主要字段分析
-
id
select查询的***,包含一组数字,表示查询中执行select子句或操作表的顺序
相同:从上而下
不同:从大到小
相同不同:先大小后从上到小 -
type
显示查询使用了何种类型,从最好到最差依次是: system>const>eq_ref>ref>range>index>ALL
-
key
实际使用的字段,查询中若使用了覆盖索引,则该索引和查询的select字段重叠
-
Extra
包含不适合在其他列中显示但十分重要的额外信息 Using filesort 查询排序中的字段 ,不推荐 Using temporary 使用临时表是 ,索引必定失效 USING index 效率最好的 Using where 使用where 语句进行查询
-
select_type
查询的类型,主要是用于区别 普通查询、联合查询、子查询等的复杂查询 simple primary subquery(子查询)、 derived(子查询的衍生)、UOION、UNION RESULT
-
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
3、索引优化
- Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数;“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环;
保证Join语句中被驱动表上Join条件字段已经被索引;
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置; -
建立索引的原则和索引失效的
1、全职匹配我最爱
2、最佳左前缀法则
3、不在索引列上做任何的操作(计算、函数、自动或手动类型转换 ),会造成索引失效导致全表扫描
4、索引、存储引擎不能使用索引中范围条件右边的列
5、尽量使用索引覆盖(只访问索引查询列),避免select *
6、MySql在使用不等于时,会导致
7、is null 、is not null 也无法使用索引
8、like 以通配符(‘%acv’)会引起索引失效,% 要在最右边
9、字符串不用’'引号处理会索引失效
10、少用or,用它来连接时会引起索引失效 -
一般性的建议
对于单键索引尽量选择针对当前query过滤性
在选择组合索引的时候。当前Query中过滤性最好的字段在索引字段顺序中尽量靠前使用
在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的