mysql 的 sql语句与索引优化(二)

索引是什么？

MySQL官方对索引的定义为：索引（index）是帮助mysql高效获取数据的数据结构。

可以得到索引的本质：索引是数据结构

MySQL默认存储引擎innoDB只显式支持B-tree(从技术上来说是B+Tree)索引

索引分类 ：

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值（主键索引是不能有空值）

复合索引：即一个索引包含多个列

聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引 其实就是在同一个结构中保存了B-Tree索引和 数据行

非聚簇索引：不是聚簇索引，就是非聚簇索引

索引的设计原则：

适合索引的列上出现在where子句中的列，或者连接子句中指定的列

基数较小的表，索引效果较差，没有必要在此列建立索引

使用索引，如果对长字符串列索引，应该指定一个前缀长度，这样能够节省大量索引空间

不要过渡索引，索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构。索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

理想的索引
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.

索引的基础语法：
查看索引
show index from 表名 \G;

创建索引：
create [unique] index 索引名 on 表名（列名（length））;
alert table 表名 add [unique] index[索引名] on(列名（length）);

删除索引：
drop index[索引名] on 表名;

1.判断sql是否有用到索引:
Expalin + sql语句 ：ex: expalin select id,name from student where name=‘lisi’;

通过字段key:查看使用的索引，key为null,则没有使用到索引

2，判断是否充分使用了索引：
索引中所有字段全部都有使用到，则有充分使用了索引。通过字段key_len的值来判断

key_len算法：
字段的长度 X 字符编码的字节长度+【非空为0，null为1】+【字段类型 可变为2,固定为0】
例如：一个字段name varchar(20) default null
key_len=20X3+1+2=63
utf8的字节长度为3

索引优化注意事项：

索引的字符字段加上引号

创建复合索引时，范围条件放最后面

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中间的列

字段离散程度大，复合索引的列放在最左边

索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引，对字符串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

排序的索引问题，mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引

like语句操作，一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

不要在列上进行运算
select * from users where YEAR(adddate)

不使用NOT IN操作，NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

具体慢查询的优化案例

1、函数Max()的优化

mysql> select max(payment_date) from payment;
+---------------------+
| max(payment_date)   |
+---------------------+
| 2006-02-14 15:16:03 |
+---------------------+
1 row in set (0.02 sec)

执行计划：

mysql> explain select max(payment_date) from payment;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | payment | ALL  | NULL          | NULL | NULL    | NULL | 16086 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
        Extra: NULL
1 row in set (0.00 sec)

可以看到显示的执行计划，并不是很高效，可以拖慢服务器的效率，如何优化了？

创建索引

mysql> create index inx_paydate on payment(payment_date);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

索引是顺序操作的，不需要扫描表，执行效率就会比较恒定

2、子查询的优化
子查询是我们在开发过程中经常使用的一种方式，在通常情况下，需要把子查询优化为join查询但在优化是需要注意关联键是否有一对多的关系，要注意重复数据。

查看我们所创建的t表

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

接下来我们创建一个t1并插入一条数据：

mysql> create table t1(tid int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| tid  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> 

我们要进行一个子查询，需求：查询t表中id在t1表中tid的所有数据；

mysql> select * from t where id in(select tid from t1);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

接下来我们用join的操作来进行操作：

mysql> select t.id as id from t inner join t1 on t.id=t1.tid;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

通过上面结果来看，查询的结果是一致的，我们就将子查询的方式优化为join操作

group by的优化

mysql> explain select actor.first_name,actor.last_name,count(*)
    -> from sakila.film_actor
    -> inner join sakila.actor using(actor_id)
    -> group by film_actor.actor_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 13
        Extra: Using index
2 rows in set (0.00 sec)

优化后的SQL：

mysql> explain select actor.first_name,actor.last_name,c.cnt
    -> from sakila.actor inner join (
    -> select actor_id,count(*) as cnt from sakila.film_actor group by actor_id
    -> )as c using(actor_id) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 27
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film_actor
         type: index
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5462
        Extra: Using index
3 rows in set (0.00 sec)

说明：从上面的执行计划来看，这种优化后的方式没有使用临时文件和文件排序的方式了，取而代之的是使用了索引。查询效率老高了。

这个时候我们表中的数据比较大，会大量的占用IO操作，优化了sql执行的效率，节省了服务器的资源，因此我们就需要优化。

注意：
1、mysql 中using关键词的作用：也就是说要使用using,那么表a和表b必须要有相同的列。
2、在用Join进行多表联合查询时，我们通常使用On来建立两个表的关系。其实还有一个更方便的关键字，那就是Using。
3、如果两个表的关联字段名是一样的，就可以使用Using来建立关系，简洁明了。

4、Limit查询的优化
Limit常用于分页处理，时长会伴随order by从句使用，因此大多时候回使用Filesorts这样会造成大量的IO问题。
例子：
需求：查询影片id和描述信息，并根据主题进行排序，取出从序号50条开始的5条数据。

mysql> select film_id,description from sakila.film order by title limit 50,5;
+---------+---------------------------------------------------------------------------------------------------------------------------------+
| film_id | description                                                                                                                     |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
|      51 | A Insightful Panorama of a Forensic Psychologist And a Mad Cow who must Build a Mad Scientist in The First Manned Space Station |
|      52 | A Thrilling Documentary of a Composer And a Monkey who must Find a Feminist in California                                       |
|      53 | A Epic Drama of a Madman And a Cat who must Face a A Shark in An Abandoned Amusement Park                                       |
|      54 | A Awe-Inspiring Drama of a Car And a Pastry Chef who must Chase a Crocodile in The First Manned Space Station                   |
|      55 | A Awe-Inspiring Story of a Feminist And a Cat who must Conquer a Dog in A Monastery                                             |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

再查看一下它的执行计划：

mysql> explain select film_id,description from sakila.film order by title limit 50,5;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

对于这种操作，我们该用什么样的优化方式了？
优化步骤1：
使用有索引的列或主键进行order by操作，因为大家知道，innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作

mysql> explain select film_id,description from sakila.film order by film_id limit 50,5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 2       | NULL |   55 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

随着我们翻页越往后，IO操作会越来越大的，如果一个表有几千万行数据，翻页越后面，会越来越慢，因此我们要进一步的来优化。

优化步骤2、记录上次返回的主键， 在下次查询时使用主键过滤。（说明：避免了数据量大时扫描过多的记录）
上次limit是50,5的操作，因此我们在这次优化过程需要使用上次的索引记录值

mysql> explain select film_id,description from sakila.film  where film_id >55 and film_id<=60 order by film_id limit 1,5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | range | PRIMARY       | PRIMARY | 2       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

结论：扫描行数不变，执行计划是很固定，效率也是很固定的
注意事项：
主键要顺序排序并连续的，如果主键中间空缺了某一列，或者某几列，会出现列出数据不足5行的数据；如果不连续的情况，建立一个附加的列index_id列，保证这一列数据要自增的，并添加索引即可。