常见索引类型
- 主键索引
- 它是一种特殊的唯一索引，不允许有空值。
- 普通索引
- 最基本的索引，它没有任何限制。
- 唯一索引
- 普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。
- 联合索引
- 多个列值的集合。
- 覆盖索引
- 通过索引数据结构，即可直接返回数据，不需要回表
- 前缀索引
- char/varchar太长全部做索引存在浪费且效率差
- Blob/text类型不能整列作为索引，所以需要前缀索引
- 无法利用前缀索引完成排序
索引为什么不可用
- 通过索引扫描的记录数超过30%，变成全表扫描
- 联合索引中，第一个索引列使用范围查询（这时用到部分索引）
- 联合索引中，第一个查询条件不是最左索引列
- 模糊查询条件最左以通配符%开始
- HEAP表使用HASH索引时，使用范围检索或者ORDER BY
- 多表关联时，排序字段不属于驱动表，无法利用索引完成排序
- 两个独立索引，其中一个用于检索，一个用于排序（只能用到一个）
复杂SQL
复杂SQL
复杂SQL-问题
复杂SQL-问题
- 问题 ：全表扫秒、临时表、文件排序
- 解决办法 :简化成2条SQL
- 独立的SQL查询汽修厂ID
- 将join表中的orgid改为直接指定
- 解决办法 :简化成2条SQL
- 优点：原有SQL改变少，性能从3秒下降为100ms左右
- 缺点：100ms的耗时仍然偏高，建议拆分为多条独立SQL
复杂SQL-修改
复杂SQL-修改
- 修改后可以看出需要扫描的行数大量减少，对应的查询时间也大幅度下降
结果
通过索引扫描的记录数超过30%，变成全表扫描
CREATE TABLE `table3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` tinyint(3) NOT NULL DEFAULT '1',
`col2` varchar(10) NOT NULL DEFAULT '2',
PRIMARY KEY (`id`),
KEY `idx_col1` (`col1`),
KEY `idx_col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
查询
-
DESC SELECT * FROM table3 WHERE col1 = 1 \G
结果
-
DESC SELECT * FROM table3 WHERE col1 = 2 \G
结果
不回表查询
-
DESC SELECT col1 FROM table3 WHERE col1 = 1 \G
Paste_Image.png
-
DESC SELECT col1 FROM table3 WHERE col1 = 2 \G
Paste_Image.png
函数查询不能使用索引
-
DESC SELECT * FROM table3 WHERE left(col2,1) = 2 \G
Paste_Image.png
-
DESC SELECT * FROM table3 WHERE col2 LIKE '2%' \G
Paste_Image.png
小表可以不建索引吗
- 看情况，通常最好要建索引
- 案例
- 用mysqlslap对只有一万行记录的表进行简单压测，一种是对该表先进性排序后读取30条记录，另一种是对该表堆积读取一行记录，分别对比有索引和没有索引的表现。结论：
1、排序后读取时没有索引时慢了约37倍时间。压测期间出现大量的Creating sort index状态。
2、随机读取一行记录时，没索引时慢了约44倍时间。压测期间出现大量的Send data状态，有索引时，则更多的是出现Sending to client状态。
3、不管是小表还是大表，需要时还是加上索引吧，否则有可能它就是瓶颈
- 用mysqlslap对只有一万行记录的表进行简单压测，一种是对该表先进性排序后读取30条记录，另一种是对该表堆积读取一行记录，分别对比有索引和没有索引的表现。结论：
参考文档
MySQL 单表百万数据记录分页性能优化
http://www.cnblogs.com/lyroge/p/3837886.html
MySQL如何利用索引优化ORDER BY排序语句
http://www.cnblogs.com/anywei/archive/2011/12/12/mysql.html
索引、提交频率对InnoDB表写入速度的影响
http://imysql.com/2014/09/24/mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert.shtml
分页优化
http://imysql.com/2014/07/26/mysql-optimization-case-paging-optimize.shtml
MySQL 5.6 查询优化器新特性的“BUG”
http://imysql.cn/2014/08/05/a-fake-bug-with-eq-range-index-dive-limit.shtml
MySQL开发规范之我见
http://imysql.com/2015/07/23/something-important-about-mysql-design-reference.shtml
