mysql优化查询细节及索引优化实例

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。
2. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询（回表查询：在B+Tree中，根据key去叶子节点查找主键，然后返回，根据主键再去找具体的数据）。
3. 使用前缀索引 : 如果字段长度比较长，可以对字段中不重复数据的前几位字符建立索引。

alter table citydemo add key(city(7));   

4. 使用索引扫描排序 ：
   索引扫描：mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。
   扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。
   mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
   只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。
   例如：
   –sakila数据库中rental表在rental_date,inventory_id,customer_id上有rental_date的索引
   –使用rental_date索引为下面的查询做排序
   explain select rental_id,staff_id from rental where rental_date=‘2005-05-25’ order by inventory_id,customer_id\G
   *************************** 1. row ***************************
   id: 1
   select_type: SIMPLE
   table: rental
   partitions: NULL
   type: ref
   possible_keys: rental_date
   key: rental_date
   key_len: 5
   ref: const
   rows: 1
   filtered: 100.00
   Extra: Using index condition
   1 row in set, 1 warning (0.00 sec)
   –order by子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为所以你的第一列被指定为一个常数

   –该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀
   explain select rental_id,staff_id from rental where rental_date=‘2005-05-25’ order by inventory_id desc\G
   *************************** 1. row ***************************
   id: 1
   select_type: SIMPLE
   table: rental
   partitions: NULL
   type: ref
   possible_keys: rental_date
   key: rental_date
   key_len: 5
   ref: const
   rows: 1
   filtered: 100.00
   Extra: Using where
   1 row in set, 1 warning (0.00 sec)

   –下面的查询不会利用索引
   explain select rental_id,staff_id from rental where rental_date>‘2005-05-25’ order by rental_date,inventory_id\G
   *************************** 1. row ***************************
   id: 1
   select_type: SIMPLE
   table: rental
   partitions: NULL
   type: ALL
   possible_keys: rental_date
   key: NULL
   key_len: NULL
   ref: NULL
   rows: 16005
   filtered: 50.00
   Extra: Using where; Using filesort`

5. union all,in,or都能够使用索引，但是推荐使用in，综合比较下来，in的查询效率最高，但是如果in中的数据太大的话，建议使用子查询

6. 强制类型转换会全表扫描，如：

explain select * from user where phone=13800001234;//不会触发索引查询

` phone为字符串，但是输入为int类型，一样可以查询，但是内部会自动强转为varchar类型

explain select * from user where phone='13800001234'; //进行索引扫描

7. 更新十分频繁，数据区分度不高的字段上不宜建立索引
   更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能。
   类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据。
   一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。
8. 创建索引的列，不允许为null，可能会得到不符合预期的结果。
9. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致。
10. 能使用limit的时候尽量使用limit。
11. 单表索引建议控制在5个以内。
12. 单索引字段数不允许超过5个（组合索引）
13. 创建索引的时候应该避免以下错误概念（索引越多越好，过早优化，在不了解系统的情况下进行优化）

索引优化案例

# 索引优化分析案例

预先准备好数据

```sql
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `itdragon_order_list`;
CREATE TABLE `itdragon_order_list` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
  `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
  `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  `net` double DEFAULT NULL COMMENT '净收入(RMB)',
  `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
  `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
  `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
  `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
  `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
  `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
  `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
  `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');

逐步开始进行优化：

第一个案例：

select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--通过查看执行计划发现type=all,需要进行全表扫描
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

--优化一、为transaction_id创建唯一索引
 create unique index idx_order_transaID on itdragon_order_list (transaction_id);
--当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
 explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 
 --优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

第二个案例

--创建复合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

--创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
explain select * from itdragon_order_list order by order_level,input_date;

--可以使用force index强制指定索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
--其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
explain select * from itdragon_order_list where order_level=3 order by input_date;