mysql优化二(索引问题)
索引问题
索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的 SQL 性能问题。
一、索引的存储分类
1、存储
myisam 存储的表数据和索引是自动分开存储的，各自独占一个文件；
innodb 存储引擎的表数据和索引是存在一个表空间里面的，但可以多个文件组成。
2、分类
mysql 中索引的存储类型目前只有两种（btree 和 hash）；myisam和innodb存储引擎只支持btree索引；memory/heap 存储引擎可以支持hash和btree索引。
mysql 目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取 name 的前4个字符进行索引，这样可以大大的缩小索引文件的大小。下面创建一个前缀索引的例子：
mysql> create index ind_company_name on company(name(4));
Query OK, 0 rows affected (0.02 sec)
二、mysql如何使用索引
索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。
查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那 么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
1、使用索引
在mysql中，下列几种情况下有可能使用到索引。
(1)对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用，举例如下：
首先按 company_id 进行表查询，如下：
mysql> create index ind_sales_companyid_monesy on sales(company_id,moneys);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
然后按company_id 进行表查询，具体如下：
mysql> explain select * from sales where company_id=3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ref
possible_keys: ind_sales_companyid_monesy
key: ind_sales_companyid_monesy
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.01 sec)
可以发现即便 where 条件中不是使用的 company_id 与 moneys 的组合条件，索引仍然能用到，这就是索引的前缀特性。但是如果只按 moneys 条件查询表，那么索引就不会被用到，具体如下：
mysql> explain select * from sales where moneys=9999999999\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 61635
Extra: Using where
1 row in set (0.00 sec)
(2) 对于使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，看下面两个执行计划：
name字段已经被创建索引
mysql> explain select * from company where name like '%3'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 64
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from company where name like '3%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company
type: range
possible_keys: ind_company_name
key: ind_company_name
key_len: 14
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
可以发现第一个例子没有使用索引，而第二例子就能够使用索引，区别就在于“%”的位置 不同，前者把“%”放到第一位就不能用到索引，而后者没有放到第一位就使用了索引。 另外，如果如果 like 后面跟的是一个列的名字，那么索引也不会被使用。
(3)如果对大的文本进行搜索，使用全文索引而不用使用 like ‘%...%’。
(4)如果列名是索引，使用column_name is null将使用索引。如下例中查询name为null 的记录就用到了索引:
mysql> explain select * from company where name is null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company
type: ref
possible_keys: ind_company_name
key: ind_company_name
key_len: 15
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
2、存在索引但不使用索引
在下列情况下，虽然存在索引，但mysql并不会使用相应的索引。
(1)如果mysql估计使用索引比全表扫描更慢，则不使用索引。例如如果
select * from table_name where key_part1 > 1 and key_part1 < 90;
(2)如果使用memory/heap 表并且 where 条件中不使用 ‘=’ 进行索引列，那么不会使用到索引。heap 表只有在 ‘=’ 的条件下才会使用索引。
(3)用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到，例如：
mysql> show index from sales\G;
*************************** 1. row ***************************
Table: sales
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 65743
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
从上面可以发现只有id列上面有索引，来看如下的执行计划：
mysql> explain select * from sales where id = 1 or name = '邓邓'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 65743
Extra: Using where
1 row in set (0.00 sec)
可见虽然在 id 这个列上存在索引 primary，但是这个 sql 语句并没有用到这个索引，原因就是 or 中有一个条件中的列没有索引。
(4)如果不是索引列的第一部分，如下例子：
mysql> explain select * from sales where moneys = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 65743
Extra: Using where
1 row in set (0.00 sec)
可见虽然在 moneys 上面建有复合索引，但是由于 money 不是索引的第一列，那么在查询中这个索引也不会被 mysql 采用。
mysql> explain select * from company where name like '%3'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 64
Extra: Using where
1 row in set (0.00 sec)
可见虽然在name上建有索引，但是由于 where 条件中 like 的值的 ‘%’ 在第一位了，那么 mysql 也不会采用这个索引。
(6)如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便是这个列上有索引，mysql 也不会用到，因为 mysql 默认把输入的常量值进行转换以后才进行检索。举例：
mysql> explain select * from company where name = 123\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company
type: ALL
possible_keys: ind_company_name
key: NULL
key_len: NULL
ref: NULL
rows: 64
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from company where name = '123'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company
type: ref
possible_keys: ind_company_name
key: ind_company_name
key_len: 15
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
三、查看索引使用情况
如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的 次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值 的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描， Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具 体如下。
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 119 |
+-----------------------+-------+
7 rows in set (0.00 sec)
从上面的例子中可以看出，目前使用的 MySQL 数据库的索引情况并不理想。
