sql索引优化

数据库索引

为了提升sql语句查询效率，通常会在数据库的表中添加索引，索引分为单值索引和复合索引，而唯一索引相当于一个单值索引的特例。

索引创建
tbl123表，字段id（主键，自增），name， email，age

单值索引
CREATE index idx_name ON users(name(15));

复合索引
CREATE index idx_name_age ON users(name, age);

查看索引
show index from tbl123;

删除索引
DROP INDEX idx_name ON users;

索引类型
对于 储存引擎为 MyIsam或者InnoDB，索引类型为B树，而Memory引擎则支持hash（默认）和B树两种索引结构。

索引优化

索引的创建需要考虑数据库的实际使用情况，比如常用语句中的查询内容、条件筛选所用的关键字段及其顺序，表联合（join和union）、结果排序及分页等。

这里还是分为 单值索引 和 复合索引 两种情况分析，分析依据主要是使用 explain语句 的输出结果，前提是在语句中使用索引字段。

单值索引

表名：users，索引名 index1，字段 name

mysql> explain select age from tbl123 where name = '123';
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | tbl123 | ref  | index1        | index1 | 18      | const |    1 | Using where |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+

这里需要主要的一些问题：
1、尽量不要用 order by，因为可能会导致文件排序（filesort），使查询变慢；
2、对于类型为char或varchar的索引字段，需要使用引号包围，避免隐式类型转换；
3、不要对索引字段做数值计算或者函数处理；
4、尽量避免使用 不等于（<> 或者 !=）, is null，及or 条件，否则索引会失效；
5、使用like时，不要以 %开头；
6、需要哪些字段就写哪些字段，查询尽量不要写 * 。

复合索引

索引信息

mysql> show index from tbl123;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl123 |          1 | idx_name_age |            1 | Name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| tbl123 |          1 | idx_name_age |            2 | age         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

order by的使用
必须使用order by时，尽量使用索引字段；
仅使用order by语句时，例如由三个字段c1、c2、c3依次组合而成的复合索引idx_c1c2c3，用法可以是order by c1或order by c1, c2或者order by c1, c2, c3，顺序不可改变（也叫最佳左前缀法则），而且均为升序或者降序；
将条件语句where 与order by结合使用，同时遵循最佳左前缀法则，减少范围条件查询，举例：where c1=‘xy’ order by c2, c3；

看一下单表查询使用举例

mysql> explain select age from tbl123 where name = "123" and age = 2;
+----+-------------+--------+------+---------------+--------------+---------+-------------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref         | rows | Extra                    |
+----+-------------+--------+------+---------------+--------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | tbl123 | ref  | idx_name_age  | idx_name_age | 20      | const,const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+--------------+---------+-------------+------+--------------------------+

需要注意的点：
1、尽量将索引字段全部使用，而且按照索引字段顺序使用，例如本例中筛选条件先使用name，再使用age，使用多字段的要点，例如三个字段（名称依次为c1、c2、c3）时，使用条件（包含顺序）只能是c1或者c1，c2或者c1，c2，c3，而且中间字段c2不能使用范围条件，否则字段c3会失效；
2、其他需要的注意点见单值索引所列6点。

对于多表查询（join），需要对非全表（例如left join的右表）生成和使用索引，还要注意：数据库默认以小表驱动大表，如果要自定义（打破小表驱动大表的规则），可用使用 straight_join 替换join。

举例（简化）
select name from a left join b on ... left join c on ...
可用看作两个 双表查询(a left join b和b left join c)，所以只需为 右边的两个表（b和c）建立索引并使用即可。

慢查询
指的是数据库耗时操作，也就是操作耗时超过设置最长时间阈值的查询。

启用耗时操作分析 profiling 全局变量
set profiling=1;

执行耗时查询
select 1 and sleep(3);

查看操作耗时 细节:
show profiles;

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00059325 | show variables like "%profil%" |
|        2 | 3.00010225 | select 1 and sleep(3)          |
+----------+------------+--------------------------------+

查看某个操作的执行过程中各个中间环节的耗时情况：
show profile cpu, block io for query 2;

mysql> show profile cpu, block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000094 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000020 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| User sleep           | 2.999765 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000185 | 0.000000 |   0.000000 |         NULL |          NULL |
| logging slow query   | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+

慢查询日志
该日志用于记录 耗时查询语句

开启慢查询日志
set global slow_query_log=1;

设置日志文件
set global slow_query_log_file='D:/phpStudy/MySQL/data/slow.log';

设置时间阈值
set global long_query_time=3;

执行慢查询
select sleep(5);

可以在日志文件中发现查询语句。