MySQL索引及优化
防伪码：路曼曼其修远兮，吾将上下而求索。一、MySQL 性能优化之-影响性能的因素
1. 商业需求的影响
不合理需求造成资源投入产出比过低，这里我们就用一个看上去很简单的功能来分析一下。
需求：一个论坛帖子总量的统计，附加要求：实时更新
从功能上来看非常容易实现，执行一条 SELECT COUNT(*) from 表名 的 Query 就可以得到结
果。但是，如果我们采用不是 MyISAM 存储引擎，而是使用的 Innodb 的存储引擎，那么大
家可以试想一下，如果存放帖子的表中已经有上千万的帖子的时候，执行这条 Query 语句
需要多少成本？恐怕再好的硬件设备，恐怕都不可能在 10 秒之内完成一次查询吧
注：没有 where 的 count(*)使用 MyISAM 要比 InnoDB 快得多。因为 MyISAM 内置了一个计
数器，count(*)时它直接从计数器中读，而 InnoDB 必须扫描全表。所以在 InnoDB 上执行
count(*)时一般要伴随 where，且 where 中要包含主键以外的索引列。
既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，
就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加 1，这样我们每次都只需
要查询这个表就可以得到结果了，这个效率肯定能够满足要求了。确实，查询效率肯定能够
满足要求，可是如果帖子产生很快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作
的时候，恐怕这个统计表又要成为大家的噩梦了。要么因为并发的问题造成统计结果的不准
确，要么因为锁资源争用严重造成整体性能的大幅度下降。
其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实
时更新”上面。当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否
是实时变化的？有多少人在乎这个数据在短时间内的不精确性？恐怕不会有人会盯着这个
统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加 1 吧？所
以只要去掉了这个“实时更新”的附加条件，就可以非常容易的实现这个功能了。就像之前
所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里
面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发贴的效率，一
举两得。
2.系统架构及实现的影响
所有数据都是适合在数据库中存放的吗？数据库为我们提供了太多的功能，反而让很多并不
是太了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，
最后却全部怪罪到数据库身上。
实际上，以下几类数据都是不适合在数据库中存放的：
1） 二进制多媒体数据
这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。将二进制多媒体数据存
放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很
消耗数据库主机的 CPU 资源。这些数据的处理本不是数据库的优势，如果我们硬要将他们
塞入数据库，肯定会造成数据库的处理资源消耗严重。
2）超大文本数据
对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需
要存储更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大可存放 64KB）的字
段，甚至是更大的 LONGTEXT 类型（最大 4GB）。而 TEXT 类型数据的处理性能要远比 VARCHAR
类型数据的处理性能低下很多。从 5.0.3 版本开始，VARCHAR 类型的最大长度被调整到 64KB
了，所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的
浪费问题。
是否合理的利用了应用层 Cache 机制？
对于 Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于
未经允许不得转载传播--陈英宏
博客地址：hongge.blog.51cto.com
这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相
对较少的部分活跃数据通过应用层的 Cache 机制 Cache 到内存中，对性能的提升肯定是成
数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。
3.查询语句对性能的影响
SQL 语句的优劣是对性能有影响的，每个 SQL 语句在优化之前和优化之后的性能差异也是
各不相同。
在数据库管理软件中，最大的性能瓶颈就是在于磁盘 IO，也就是数据的存取操作上面。而
对于同一份数据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量
可能会有天壤之别，所消耗的资源也自然是区别很大。
功能完全相同的两条 SQL 的在性能方面的差异。
我们在执行 sql 语句时可以用 explain 来查看执行计划：
mysql> explain
-> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000
-> order by stuid desc
-> limit 20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3678
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
还可以打开 mysql 的 profiling 功能，来查看 sql 的实际执行计划
mysql> set profiling=1;
mysql> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000 order by stuid
desc limit 5\G
mysql>show profile;
通过执行“SHOW PROFILE” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息。;
mysql> show profile CPU,BLOCK IO for query 1;
4.数据库 Schema 设计对性能的影响
5.硬件选择对性能的影响
首先，数据库主机是存取数据的地方，所以数据库主机的 IO 性能肯定是需要最优先考虑的
一个因素，这一点不管是什么类型的数据库应用都是适用的。在主机中决定 IO 性能部件主
要由磁盘和内存所决定，当然也包括各种与 IO 相关的板卡。
其次，由于数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所
需要进行的计算量自然也就比较多，所以数据库主机的 CPU 处理能力也不能忽视。
最后，由于数据库负责数据的存储，与各应用程序的交互中传递的数据量比其他各类服务器
都要多，所以数据库主机的网络设备的性能也可能会成为系统的瓶颈。
所以，数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本
性改善的事情。简单来说，可以通过下面三句话来简单的概括数据库应用系统的性能优化：
商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化。
二、MySQL 性能优化之-索引
关于 MySQL索引的好处，如果正确合理设计并且使用索引的 MySQL 是一辆兰博基尼的话，
那么没有设计和使用索引的 MySQL 就是一个人力三轮车。对于没有索引的表，单表查询可
能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有
索引查询会变的非常缓慢。
做一个简单测试，假如我们创建了一个 tb1 表，向表中插入 20000 行数据，表的创建和数据
插入用如下脚本实现
# cat mysql3.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123.abc"
DBNAME="test1"
TABLENAME="tb1"
#create database
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "drop database if exists
${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname
varchar(20)notnull,stusexchar(1)notnull,cardidvarchar(20)notnull,birthday
datetime,entertime datetime,address varchar(100) default null)"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e
"${create_table_sql}"
#insert data to table
i=1
while [ $i -le 20000 ]
do
insert_sql="insertinto${TABLENAME}values
($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguobeijingshi
changpinqu')"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e
"${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e
"${select_sql}"
再手动插入一行数据，如
mysql> insert into tb1 values
(20001,'admin','0','123456789009877','2000-1-1','2016-9-1','wwwwwwwwwwwww');
下面开始测试，查询 stuname=’admin’的记录
情况 1：stuname 列上没有创建索引的情况
情况 2：stuname 列上创建索引的情况再查询
在查找 stuname="admin"的记录时，如果在 stuname 上已经建立了索引，MySQL 无须任何
扫描全表，即准确可找到该记录。相反，MySQL 会扫描所有记录。
所以在数据库表中，对字段建立索引可以大大提高查询速度。
索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，
并不是所有的存储引擎都支持所有的索引类型。
索引概述：
什么是索引？
索引（Index）是帮助 MySQL 高效获取数据的数据结构，这是一种比较官方的说法。它的存
在形式是文件。索引能够帮助我们快速定位数据。 更通俗的说，数据库索引好比是一本书
前面的目录，能加快数据库的查询速度。
索引的数据结构
这里主介绍 B-tree 索引的结构
如上图，是一颗 b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到
每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块 1 包含数据项 17
和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，
P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、
36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据
项和指针，如 17、35 并不真实存在于数据表中。
b+树的查找过程
如图所示，如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一
次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间
因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块
3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过
指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总
计三次 IO。真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只
需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总
共需要百万次的 IO，显然成本非常非常高。
为什么使用索引？
索引可以让 mysql 高效运行，可以大大提高 mysql 的查询（包括排序，分组）效率；数据约
束（唯一索引的建立）。
索引给我带来什么好处？
提高查询效率，快速定位数据
使用索引产生的代价？
1、索引本身是以文件的形式存放在硬盘，需要的时候才加载至内存，所以添加索引会增加
磁盘的开销；
2、写数据：需要更新索引，对数据库是个很大的开销，降低表更新、添加和删除的速度
不建议使用索引的情况：
a) 表记录较少
b) 索引的选择性较低，所谓索引的选择性，是指不重复的索引值与表记录数的比值，取值
范围（0-1）。选择性越高，索引的价值越大。
索引的类型？
索引包括单列索引和组合索引
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组
合索引，即一个索包含多个列。
1、 普通索引
这是最基本的索引，它没有任何限制
CREATE INDEX indexName ON tablename(column1[,column2,……])
2、 唯一索引
它与前面的普通索引类似，不同的就是索引列的值必须唯一，但允许空值，空值是指 null。
如果是组合索引，组合列的值必须唯一
CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])
主键索引：一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引
CREATE TABLE tablename(ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY
KEY(ID) );
3、组合索引
为了进一步提升 MySQL 的效率，就要考虑建立组合索引
例如：创建一个表，包含如下字段
CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50)
NOT NULL, age INT NOT NULL );
将 username, city, age 建到一个索引里
CREATE INDEX username_city_age ON mytable(username,city,age);
如果分别在 usernname，city，age 上建立单列索引，让该表有 3 个单列索引，查询时和上
述的组合索引效率也会大不一样，远远低于组合索引。虽然此时有了三个索引，但 MySQL
只能用到其中那个它认为似乎是最有效率的单列索引。
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
usernname,city,age usernname,city usernname 为什么没有 city，age 这样的组合索引呢？
这是因为 MySQL 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并
不是只要包含这三列的查询都会用到该组合索引，下面的几个 SQL 就会用到这个组合索引：
SELECT * FROM mytable WHREE username="admin" AND city="郑州"
SELECT * FROM mytable WHREE username="admin"
而下面几个则不会用到：
SELECT * FROM mytable WHREE age=20 AND city="郑州"
SELECT * FROM mytable WHREE city="郑州"
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL 仅能对索引最左边的前缀进行
有效的查找。
例如：
假设存在组合索引 index1(c1,c2)，查询语句 select * from t1 where c1=1 and c2=2 能够使用该
索引。查询语句 select * from t1 where c1=1 也能够使用该索引。但是，查询语句 select * from
t1 where c2=2 不能够使用该索引，因为没有组合索引的引导列，即要想使用 c2 列进行查找，
必需出现 c1 等于某值。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左
边，依次递减。
4、 全文索引
只用于 MyISAM 表 对文本域进行索引。字段类型包括 char、varchar、text
不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
CREATE FULLTEXT INDEX indexname ON tablename(column)
查看索引
mysql> show index from tablename;
mysql> show keys from tablename;
建立索引的时机
到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？
一般来说，在 WHERE 和 JOIN 子句中出现的列需要建立索引，例如：
代码如下:在 username 上创建索引
SELECT * FROM mytable WHREE username="admin" AND city="郑州"
代码如下:
SELECT t.Name FROM mytable1 t LEFT JOIN mytable2 m ON t.username=m.username;
此时就需要对两个表的 userame 上建立索引。
使用索引的注意事项
刚才提到只有某些时候的 LIKE 才需建立索引。因为在以通配符%和_开头作查询时，MySQL
不会使用索引。例如下句会使用索引：
SELECT * FROM mytable WHERE username like'admin%'
而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin'
不要在列上进行运算,例如
select * from users where YEAR(adddate)
页:
[1]