MySQL优化

为什么要优化

• 系统的吞吐量瓶颈往往出现在数据库的访问速度上
• 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
• 数据是存放在磁盘上的，读写速度无法和内存相比

如何优化

• 设计数据库时：数据库表、字段的设计，存储引擎
• 利用好MySQL自身提供的功能，如索引等
• 横向扩展：MySQL集群、负载均衡、读写分离
• SQL语句的优化（收效甚微）

1.数据库设计和表创建时考虑性能

设计表时注意：

• 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替
• 尽量使用INT而不是BIGINT，如果非负加上UNSIGNED，当然使用TINYINT、SMALLINT、MEDIUMINT更好.
• 使用枚举或整数代替字符串类型
• 尽量使用timestamp而非datatime
• 单表不要有太多字段,建议在20以内
• 用整型来存IP

索引：

• 不用外键，由程序保证约束
• 尽量不用UNIQUE,由程序保证约束
• 字符字段最好不要做主键
• 字符字段只建前缀索引
• 值分布稀少的字段不适合建索引，例如'性别'
• 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎进行全表扫描
• 使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引
• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

使用合适的数据类型：

• 使用可存下数据的最小数据类型， 整型<datetime<char<varchar<blob
• 使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂.如，int类型存储时间类型，bigint类型转ip函数.
• 使用合理字段属性长度，固定长度的表会更快.使用enum、char而不是varchar
• 尽可能使用not null定义字段
• 尽量少用text,非用不可最好分表

选择合适的索引列：

• 查询频繁的列，在where，group by,order by, on从句中出现的列
• where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
• 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
• 离散度大的列，放在联合索引前面.查看离散度，通过统计不同的列值来实现，count越大，离散程度越高

2.SQL的编写需要注意优化

• 使用limit对查询结果的记录进行限定
• 避免select * ,将需要查找的字段列出来
• 使用连接（join）来代替子查询
• 拆分大的delete或insert语句
• 通过开启慢查询日志来找出较慢的SQL
• 不做列运算：select id where age+1=10, 任何对列的操作都将导致全表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能移至等号右边
• sql语句尽可能简单：一条sql只能在一个CPU运行；大语句拆小语句，减少锁时间；一条大的SQL可堵死整个库
• OR改写成IN： OR的效率是N级别，IN的效率是log(n)级别，IN的个数建议控制在200以内
• 不要函数和触发器 ，在应用程序实现
• 避免%xxxx式查询
• 少用join
• 使用同类型比较，比如用‘123’和‘123’比，123和123比
• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 对于连续数值，使用BETWEEN不用IN: SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

 

3.索引

索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。而用的最多，并且是mysql默认的就是二叉树算法 BTREE，通过BTREE算法建立索引的字段，比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果。

UNIQUE唯一索引

不可以出现相同的值，可以有NULL值。

INDEX普通索引

允许出现相同的索引内容。

PRIMARY KEY主键索引

不允许出现相同的值，且不能为NULL值，一个表只能有一个primary_key索引。

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用，但全文索引，可以针对值中的某个单词，比如一篇文章中的某个词，然而并没有什么卵用，因为只有myisam以及英文支持，并且效率让人不敢恭维，但是可以用coreseek和xunsearch等第三方应用来完成这个需求。

索引的CURD

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，当前的索引名就是该字段名。 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

--例：只能添加这两种索引 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)

CREATE TABLE `test1` ( 
  `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面创建了主键索引，这里就不用创建了 
  `username` varchar(64) NOT NULL COMMENT '用户名', 
  `nickname` varchar(50) NOT NULL COMMENT '昵称/姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名称，可要可不要，不要就是和列名一样 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';

DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 这两句都是等价的,都是删除掉table_name中的索引index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引，注意主键索引只能用这种方式删除

创建索引的技巧

组合索引：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建立这样的组合索引，其实是相当于分别建立了：

• vc_Name,vc_City,i_Age
• vc_Name,vc_City
• vc_Name

这样的三个组合索引！为什么没有 vc_City,i_Age 等这样的组合索引呢？这是因为 mysql 组合索引 “最左前缀” 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引