MySQL索引与查询优化

概述

企业应用软件中，在数据存储方面选择为DBMS（数据库管理系统）。当数据量增多后，对数据的查询和分析在速度上会有很大的影响。原因如下：
1、数据文件存储在磁盘上，每次读取会有I/O消耗。
2、I/O性能始终是数据读取的瓶颈。数据随机存放，每一次的I/O操作消耗大量的CPU时间。为了降低I/O操作对DBMS查询速度的限制，DBMS引入了索引的概念。

下面会以MySQL为例来说明,例子如下：
假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

索引

索引类型

MySQL的数据库引擎有两种，一是MyISAM另一种是INNODB。对于这两种的区别可以参看以下内容：
http://www.cnblogs.com/villion/archive/2009/07/09/1893762.html。

BTree是INNODB的索引算法，m阶的BTree树，满足如下内容
1、所有节点最多有m个子节点
2、根节点至少有两个子节点
3、除根节点和叶子节点外，其它节点至少有m/2取上整个节点
4、所有叶子节点在同一层
5、有k个节点的非叶子节点恰好有k-1个关键字。
其结构如下：

MySQL索引与查询优化

MySql执行计划

使用explain关键字，通过该关键字可以查看mysql的执行计划。执行计划的参数如下：
MySQL索引与查询优化

查询优化经验

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。这个在实际开发中应该要注意到，避免出现空值。

(4)如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

例如：存在组合索引index_name(name,pwd)。

查询语句select * from t5 where name='1' and pwd='2'能够使用该索引。
查询语句select * from t5 where name='1' 也能够使用该索引。
查询语句select * from t1 where pwd='2'不能够使用该索引，因为没有组合索引的引导列，即，要想使用pwd列进行查找，必需出现name等于某值。
例子如下:

mysql> desc t5;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | MUL | NULL | |
| pwd | varchar(20) | NO | | NULL | |
| sex | int(11) | YES | | 0 | |
| age | int(11) | YES | MUL | 0 | |
| address | varchar(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql> explain select * from t5 where name='n1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: index_name
key: index_name
key_len: 62
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql> explain select * from t5 where pwd='n1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: NULL
key: index_name
key_len: 124
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select * from t5 where name='n1' and pwd='n1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: index_name
key: index_name
key_len: 124
ref: const,const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)

==================================================================================================================================== (5)like查询

mysql> explain select * from t5 where name like '%p1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t5 where name like 'p1%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: range
possible_keys: index_name
key: index_name
key_len: 62
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

=================================================================================================================================== (6)null或者not null查询

mysql> explain select * from t5 where address is null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 45
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t5 where name is not null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: index_name
key: NULL
key_len: NULL
ref: NULL
rows: 45
Extra: Using where
1 row in set (0.00 sec)

=====================================================================================================================================(7)or查询，同时有索引，否则失效

mysql> explain select * from t5 where age=0 or address='1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: index_age
key: NULL
key_len: NULL
ref: NULL
rows: 45
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t5 where age=0 or id=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index_merge
possible_keys: PRIMARY,index_age
key: index_age,PRIMARY
key_len: 5,4
ref: NULL
rows: 6
Extra: Using union(index_age,PRIMARY); Using where
1 row in set (0.00 sec)

=================================================================================================================================== (8)and查询,有索引，就生效

mysql> explain select * from t5 where id=1 and address='11'\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: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

=====================================================================================================================================(9)如果列是int类型，但是给的是string类型那么mysql会放弃使用索引
mysql> explain select * from t5 where pwd=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 45
Extra: Using where
1 row in set (0.00 sec)

优化一般步骤

(1)定期优化表和检测表

check table tabelname检测表是否错误
optimize table sales;

(2)慢查询日志

开启，找到查询慢的语句
show status like "slow_queries";
slow_launch_time
slow_query_log
query_log_file
show variables like "%slow%";

show variables like "%long%";慢查询时间
show status like 'Handler_read%';该值越高说明有好多表需要索引。

(3)定位执行效率较低的SQL语句

MySQL索引与查询优化

相关推荐