MySQL性能优化注意事项以及索引

共计 2531 个字符，预计需要花费 7 分钟才能阅读完成。

MySQL 性能优化注意事项以及索引

一：数据库的优化方面
1 商业需求的影响
比如说论坛里的帖子统计，并且实时更新
从功能上来说通过命令  select  count（*）from 表名 可以得到结果，如果论坛每秒产生产生成千上万条帖子，我们没有采用 myisam 存储而用的是 innodb 存储；就算再好的设备也不可能很快的查询出来。
注：在 where 和 count（*）使用中 myisam 比 innodb 要快的多；因为 myisam 内置了一个计数器，count（*）可以直接从计数器当中读取，而 innodb 则要扫描全表。
所以在 innodb 上执行 count（*）时一般伴随 where，而且 where 中要包含主键以外的索引列。
如果必须要实施更新就专门为这个功能创建一个表，要想查询结果就专门查看这个表就好了；到时候每秒产生的上万条帖子也是一种麻烦，反过来说但是到底有多少人会关注这个实时更新，如果把实时更新去掉就很容易实现；在通过创建统计表，每隔一定的时间去刷新便可以。这就是不合理的商业要求。

2：系统架构以及实现的影响
1）二进制多媒体数据
主要包括图片、视屏、其他二进制文件，如果放到数据库中数据空间资源消耗非常严重，另外一个就是消耗主机的 cpu 资源，因为数据库本就不是处理这些的优势，
解决办法：可以将这些二进制多媒体数据放到一个专门的文本文件中，然后给数据库做一个连接指向这个文本文件，实现数据库调用多媒体文件，有不用消耗数据库的空间和 cpu 资源。
2）超大文本数据
如果大的文本数据放到数据库当中也会造成空间的占用浪费问题。
解决方法：可以使用非关系型数据库进行存储
3）查询语句对性能的影响
每个 sql 语句在优化前后的性能差异也是各不相同
在数据库管理软件中，最大性能瓶颈就是在于磁盘 io、也就是数据的存取操作上面，而对于同一份数据，当我们以不同的方式去查找某一点内容时候，所需的读取数据量可能会有天壤之别，搜消耗的资源也区别很大
首先进行编写一个脚本插入 20000 行的数据

比如执行 sql 语句时可以用 explain 来查看执行计划：
 

使用其他方式再次查询打开 profiling 功能，来查看 sql 的实际执行计划
打开功能

开始查询

查看 profile 对数据库的 cpu，block，以及 io 的使用情况：

 
4）数据库的 schema（模式）设计对性能也有影响
5）硬件选择对性能的影响
数据库主机是存储数据的地方，所以 io 性能必须要优先考虑，无论是什么数据库都必须考虑的因素，当然和 io 相关的板卡
另外 cpu 的处理能力也不能忽视，企业中必须使用多核，另外内存也必须要大至少要 64G
其实数据库的优化不单单从物理方面进行提高配置，也包括逻辑化如连接数。。。，和商业的需求。总之就是系统架构最优化、逻辑结构精简化、硬件设施理性化
 
 
二、索引的介绍以及创建和使用
 
什么是索引？
索引（index）是帮助 mysql 高效获取数据结构，帮助 dba 快速定位，简单来说就相当于字典中的目录
索引的类型在上章讲过有三种 {B-Tree、R-Tree、Full-Tree} 类型、最常用的是 B -Tree
这里主要介绍的是 B -Tree 的索引结构：

如图：这里只说重点、浅蓝色的我们成为磁盘块、可以看到每个磁盘块包含几个数据项，和指针（黄色）其实真正的数据在叶子节点上，就是最下面的一层，而其他的不存放数据，只存放指引数据方向的索引而已。
例如：要查找 29，首先把磁盘块 1，加载到内存，发生一次 io，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 p2 指针，由此往下推算，直到第三层算出为止。
 
索引的优点：
让 mysql 高效的运行，可以大大提高 mysql 的查询效率，数据约束，快速定位
使用索引的代价：
1）需要加载到内存，以文件的形式存放在硬盘中，所以增加磁盘的开销
2）写数据，需要更新索引，对数据库是很大的开销，降低表更新、添加和删除的速度
 
不建议使用索引的情况：
1）表记录较少
2）索引的选择性较低，指不重复的索引与表记录数的比值，取值范围（0-1），选择性越高，索引价值越大
 
1：普通索引
最基本的索引，没有任何限制
create index index_name on tablename（columm1【column2，。。。。。】）
2：唯一索引
和普通索引类似，不同的就是索引列的值必须唯一，但允许空值，指的就是 null，如果是组合索引，列的值必须唯一。
create table tablename（id int not null,username varchar(16) not null,primary key(id)）;
3：组合索引
为了进一步提升 mysql 的效率，可以使用组合索引
create index index_name on table_name（column1，column2，column3）；
这样的组合索引效率高于单列的索引，而且采用的是最左前缀的结果。简单理解就是从最左边开始组合。
4：全文索引
只用于 myisam 表对文本域进行索引。字段包括 char、varchar、text
 
不过切记大容量的数据表，生成全文索引是一个非常消耗时间和硬盘的做法
 
查看索引
show  index  from  table_name
show  keys  from  table_name
创建索引的时机：
一般在 where 和 join 子句中需要建立索引
使用索引的注意事项：
某些情况下 like 才需要建立索引，因为在一通配符 % 和 - 开头查询时，mysql 不会使用索引
select * from table-name where name like‘%admin’；
另外还有就是不能再列上进行运算
select * from users where YEAR(adddate)<2000;
强每个行上进行运算，将导致索引失效而进行全表扫描
可修改为 select * from users where adddate<2000-10-4;
总结：
索引的优化过程中主要用于存在 where 和 join 子句当中
索引中的列的基数越大，索引的效果越好
使用的短索引，如果对字符串进行索引，应该指定一个前缀长度，可节省大量的索引空间，提升查询的速度

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-06/145079.htm