MySQL之SQL优化(一)

 

1. 创建索引，myslq支持重复索引，但是多余的索引会带来性能开销；

a) 利用索引可以减少查询读取的行数

b) 提高表连接操作的性能

c) 如果where子句中条件以通配符开头，则mysql不使用索引

d) mysql不支持基于索引的函数

e) 创建索引非常耗时，同时会阻塞其他操作，所以尽量将创建多个索引的语句合并到同一条alter语句中

f)  多列索引最左边的列可以被当作单列索引来使用

 

2.Mysql索引优化器特性 

a) 两个索引取并集，当对两个很高基数的索引执行OR操作时

b) 两个索引取交集，当对两个有少量唯一值的索引执行AND操作时

c) 两个索引先排序，再取并集，例如：

EXPLAIN SELECT artist_id, name

FROM artist

WHERE name = 'Queen'

OR (founded BETWEEN 1942 AND 1950)

 

3. USE INDEX关键字提示优化器使用特定的索引：

USE INDEX (index_column)

 

4. IGNORE INDEX关键字提示优化器忽略特定缩影：

IGNORE INDEX (index_column)

IGNORE INDEX (index_column_1, index_column_2)

 

5. 添加索引可提高sql语句的性能，可能造成其他开销，

a)对DML的影响，在表上添加索引会影响写操作的性能

a.1)重复索引，索引优化最简单的就是删除重复索引，任何包含在其他索引最左边部分的索引都属于

重复索引，同时该索引不会被使用

使用Maatkit工具集的mk-duplicate-key-checker可检查重复索

mk-duplicate-key-checker命令的使用：

mk-duplicate-key-checker --databases=databasename --user=root --password=passwd

a.2) 索引的使用

索引的存在会影响写操作的性能，并且会占用磁盘空间从而影响到备份和恢复策略

 

b) 对DDL的影响

随着表的不断增长，对性能的影响也不断加大。

在InnoDB中使用默认表空间时配置，执行DDL语句时，mysql会创建一份表的副本，在执行alter期间该额外的磁盘空间会被添加到公共表空间中，并且该磁盘空间在命令执行完成后不会被文件系统回收，而是当InnoDB需要额外磁盘空间时在内部被重复利用。

InnoDB中，主键要尽量选择字长小的数据类型

b.1) 页面填充因子

尽量选择与业务无意义的属性做主键

b.2) 非主键索引

InnoDB的非主键索引使用了主键的值，而不是指向主键的指针；在每个索引记录后都附加一个主键的副本。因此使用更短的主键可以很大程度上减少索引占用的空间。InnoDB这种使用主键值的方式与InnoDB内部的主键散列算法结合使用能改善性能。

 

6. MySQL的限制和不足

a) 基于开销的优化器

a.1)MySQL使用基于开销的优化器来调整可能的查询书以创建最优的SQL执行路径，MySQL通过生成的统计信息来辅助优化器的能力很有限，只支持数量有限的索引提示用于帮助优化器选择一个合适的路径。

b) 制定QEP（Query Execution Plan）

MySQL不支持为给定查询指定QEP

c) 索引的统计信息

MySQL支持有限的索引统计信息，并且因存储引擎而已

d) 基于函数的索引

MySQL不支持基于函数的索引，在已有的索引中使用函数会导致性能下降

e) 一个表上的多个索引

默认情况MySQL会对一个表只使用一个索引