mysql性能优化-索引与优化
http://hongge.blog.51cto.com/一、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 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相对较少的部分活跃数据通过应用层的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) not null,stusex char(1) not null,cardid varchar(20) not null,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="insert into ${TABLENAME} values ($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguo beijingshi 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列上没有创建索引的情况
mysql> explain select stuid,stuname,stusex,cardid,entertime from tb1 where stuname='admin'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 18910
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
情况2：stuname列上创建索引的情况再查询
mysql> create index index_stuname on tb1(stuname);
mysql> explain select stuid,stuname,stusex,cardid,entertime from tb1 where stuname='admin'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: index_stuname
key: index_stuname
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
在查找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.NameFROM 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]