MySQL性能优化秘籍

一、关于数据类型

选择的原则是：在满足使用的前提，要小而简单。

（1）整形比字符操作代价更低。

（2）保存日期格式时，要选用MySQL内置的日志格式（TimeStamp,DateTime）,而不使用字符串。PS:TimeStamp使用4个字节进行存储（存储的时间从1970到2034），而DateTime使用8个字节存储（时间从1001到9999）,上述两种时间格式推荐使用TimeStamp. 上述的两种日期格式的精度都只是到秒，如果要存储到毫秒或者更高的精度，可以考虑用BIGINT来保存。

（3）小数点的保存如何有涉及到运算，用float和double可能会精度不准，可以考虑将小数乘于相应的倍数，然后用BIGINT来存储。

（4）整数的几种类型

TINYINT、SMALLINT、MEDIUMINT、BIGINT分别使用8，16,32,64位。保存范围为-2^N到2^N-1（这里N=位数-1），如果加入UNSIGNED则保存的范围数（在正数范围内）提升一倍。

注意：对于INT(1)和INT(20)来说存储的空间和计算是一样的，因此指定位数是没有意义的。

（5）关于存储字符串的几种类型的选择

主要有CHAR, VARCHAR, BLOB, TEXT

关于CHAR和VARCHAR

其中CHAR（10）是定长为10（只需要10个字节）的，而VARCHAR(10)是变长的（不仅需要10个字节，还需要记录长度，如果长度小于255则额外需要1个字节，如果大于255则需要两个字节）。

在使用的时候，如果是定长字符推荐CHAR（N），比如定长的MD5加密的字符串。

CHAR的优势是经常更新不会产生太多的碎片。

对于VARCHAR中的变长个数如果定义的太长也会浪费存储空间的。比如VARCHAR(5)和VARCHAR(100)存储“hello”存储的空间都是一样的，但是更长的varchar的列会消耗更多的内存。

关于BLOB和TEXT

两个都是存储长的字符串，而BLOB存储的格式是二进制，TEXT是字符串。

上述的两种类型都有TINYBLOB，SMALLBLOB,BOLOB,MEDIUMBLOB,LONGBLOB,BLOB

以及TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT

（6）关于保存ip地址

对于通常的ipv4，比如：192.168.1.123，我们会用15位char来存储这种是不推荐的，实际上ipv4是32位的无符号整数，推荐将ipv4转成整数进行保存。

在MySQL中提供了INET_ATON()将ipv4转成整形以及INET_NTOA()将整形转成ipv4

一个示例的用法如下：

create table tbl_ip(ipint unsigned, name char(1))

insert intotbl_ip(inet_aton(‘192.168.1.122’),’d’);

select * from tbl_ipwhere ip=inet_aton(‘192.168.1.122’);

select inet_ntoa(ip)from tbl_ip;

（7）默认值的缺省值的设置

尽量不用NULL，可以设置为“”或者0。

（8）在正式环境如何更改表的结构

方法一：创建影子表，当完成后，通过一个原子的重命名操作切换影子表和原表

MySQL语句的例子：

DROP IF EXISTStbl_channel_new, tbl_channel_old;

CREATE TABLEtbl_channel_new LIKE tbl_channel;（注意：这里只是复制了表的结果，其数据集仍然是空的）

…(执行修改操作)

RENAME TABLE tbl_channelTO tbl_channel_old, tbl_channel_new TO tbl_channel;

最后新表就是我们修改的表，并保存了一份之前的表，如果新表有问题可以快速回滚。

方法二：在更改表的时候可能会遇到表很大，而内存又不足，以及索引又很多，此时如果执行ALTER TABLE操作可能需要花费数个小时甚至数天的时间才能完成。

通过.frm文件的替换完成，但是实际实验中不成功。

方法三：通常表的修改应该在备机中执行ALTER并在完成后把它切换成主库。

（9）MySQL中的计数器

使用的用法：

UPDATE tbl_stat SET cnt=cnt+1;

上面的操作在计数的时候可以不用取得当前的值，而直接通过指定的列和要增加的值来计算统计结果。

在数据统计中的问题，对于某一行的计数由于会有事务，因此都会有一个全局的互斥锁，使得并发性能不高。

改进方法：

将计算器保存在多行中，每次随机选择一行进行更新，示例代码如下：

CREATE TABLEhit_counter(

slot tinyint unsigned not null primary key,

cnt int unsigned not null

)ENGINE=InnoDB;

然后预先在这张表上增加100行数据。随机选择一个槽（slot）进行更新：

UPDATE hit_counter SETcnt=cnt+1 WHERE slot=RAND()*100；

进一步改进，如果需要每隔一段时间开始一个新的计数器（比如：每天一个），则修改表为：

CREATE TABLEdaily_hit_counter(

day date not null,

slot tinyint unsigned not null,

cnt int unsigned not null,

primary key(date, slot)

)ENGINE=InnoDB;

在这个场景中，可以不用预先增加行，而用ON DUPLICATEKEY UPDATE代替（即如果不存在指定的主键KEY则插入，存在则执行更新操作），语句如下：

INSERT INTOdaily_hit_counter(day, slot, cnt) VALUES(CURRENT_DAY, RAND()*100, 1) ONDUPLICATE KEY UPDATE cnt=cnt+1；

二、如何使用索引

1、什么是索引，什么时候需要使用索引？

索引是通过额外的空间来保存你需要快速查询的一些字段，将这些字段按照一定的顺序存储并与原始数据进行映射，这样如果你要查询这些字段就无需去查询原表（可以肯定的是索引表的数据量肯定比原表小）

索引是提高查询性能的最佳途径。

但是索引也有它适用的范围，通常对于可以预计是小表的话，我们不会去见索引会浪费空间；对于中表或者大表，通过建立合适的索引能够极大地提高查询速度；当然对于超大表，不建议建立索引，会浪费太多的存储空间，会有其他第三方支持，补充一点：对于超大表的话可以考虑用分区表。

2、B-Tree索引的查询类型？

MySQL数据库中最常用的引擎是InnoDB（当然还有MyISAM这种引擎不怎么用），一般来说索引的类型指明都是用B-Tree索引，该索引的特点有如下：

（1）匹配最左前缀：即最左边的列是要先匹配，才能匹配剩余的列。

（2）全值匹配

（3）范围匹配：当用了范围匹配，在之后的列将不再用索引了

（4）精确匹配某一列，并范围匹配另外一列：即将2、3结合

（5）只访问索引查询：这个特性也就是我们说的覆盖索引

3、InnoDB中无哈希索引，如何利用哈希索引？

在InnoDB中是不存在哈希索引，那么哈希索引又如何用，其实并不能叫做哈希索引，只是用哈希特性，本质上还是利用B-Tree索引。

通常的用法是：针对某个较长的字符串，比如存储了URL，值为：https://www.baidu.com，那么我们要对URL进行建立索引，如果直接建立的话，索引占用的空间太大，可以通过哈希函数（比如：crc32）将其值映射到一个固定长度的数值，将该列的值添加表中，并为该列创建索引。这里面会有一个问题，那就是可能会存在重复的哈希值。解决的办法：

（1）如果能够容忍重复的情况下，在查询where条件中，除了附加索引的列的值，还要添加url的值，当重复的时候，就会用url的值来去重。

（2）另外一种方法，是通过64位的哈希函数来减少重复的概率。

补充一点：针对长文本建立索引的时候，我们也可以考虑取长文本的前缀建立索引，参考第5个问题。

4、那些情况下创建的索引列不起作用？

索引列不起作用的情况：

（1）将所有列使用函数等条件，比如：

SELECTactor_id FROM sakila.actor WHERE actor.id+ 1 = 5; //使用了加号

SELEC… WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10;

（2）范围查询之后的索引列不起作用

范围查询主要是指< 、>，以及like，注意：对于in 会转化多个等式，因此仍然可以。

5、前缀索引如何创建及其特性，以及如何实现后缀索引？

创建前缀索引：

ALTER TABLEsakila.city_demo ADD KEY (city(7))

根据某个前缀利用group统计出个数：

SELECT COUNT(*) AS cnt,LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESCLIMIT 10;

在一个查询中计算不同长度的前缀的选择性：

SELECT COUNT(DISTINCTLEFT(city, 3))/COUNT(*) AS sel3,COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4FROM sakila.city_demo;

6、多列索引中列的排序如何选择？

对于B-Tree的多列索引，采用的是最左列排序，即在设计多列索引的情况，应该将选择性最高的放到索引的最前列（好处是能够快速定位到具体的行，不然会存在很多重复的行）

一般选择的法则：经验法则，示例如下：

比如针对两个列：staff_id和cunstom_id列的选择，通过以下方法：

SELECT COUNT(DISTINCTstaff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT custom_id)/COUNT(*)AS customer_id_selectivity,COUNT(*) FROM payment \G;

如果确认custom_id比staff_id的选择性更高，则添加如下多列索引：

ALTER TABLE payment ADDKEY(customer_id, staff_id);

7、什么是覆盖索引？如何使用覆盖索引来实现延迟关联呢？

覆盖索引：如果在某次查询中的字段覆盖了索引中的字段（即：一个索引包含或者说覆盖所有要查询的字段）

之所以要强调覆盖索引，是因为覆盖索引的条目数目通常远小于数据行大小，且索引是按列值顺序存储的（适合group以及相关范围查询）

一个使用延迟关联中使用了覆盖索引的例子：

比如：SELECT * FROM products WHEREactor=”SEAN CARRY” AND title like ‘%APOLO%’\G;

通过Explain计划看到，Extra:Using where说明没有使用到索引。

解决方法：添加多列索引(artist, title,pro_id)

SELECT *

FROM products

JOIN(

SELECT pro_id

FROM products

WHERE actor=’SEAN CARRY’AND title LIKE‘%APOLLO%’

)AS t1 on(t1.prod_id=products.prod_id)

分析上面的语句：

可以看到在JOIN中的列全部是我们建立的多列索引的列，因此可以利用覆盖索引，当我们覆盖索引查找到需要的pro_id后，再关联表查询便可减少数据量。

8、MySQL中有序结果集生成的方式有哪些？索引在其中起什么作用？

两种方式：（1）通过排序操作；（2）按索引排序（如果Explain中的type:index说明是按索引排序）

9、什么是冗余索引？

比如创建了一个多列索引（A,B）,再创建一个单列索引（A），此时单列索引就是冗余索引，因为这只是第一个索引的前缀索引；如果创建一个单列索引（B），则不是。

在创建索引中对于相同的列，我们可能会按照不同的顺序来创建索引来满足不同的业务需求。

10、范围条件IN和>、<在多列索引中使用的区别？

IN在索引中会转化为多个等式，而<、>不可以。

11、索引导致的性能缺点有哪些？

索引的问题：

（1）占用硬盘，mysql需要的时候会调入内存，但是如果太大仍然会有问题。

（2）导致插入、更新等操作变慢

12、选择索引和编写利用索引的查询，需要记住的三个原则：

（1）单行访问是很慢的

（2）按顺序访问范围数据是很快的

（3）索引覆盖查询是很快的