Mysql优化

MySQL优化

索引能大大的提高查询速度, 但同时会降低更新表的速度，因对表进行 INSERT、 UPDATE和 DELETE操作时，MySQL 不仅要保存数据，还要维护一下索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究如何 建立最优秀的索引 或 优化查询语句。 那么如何优化索引呢，以下是一些总结的MySQL 索引注意事项和SQL优化方法：

1. 何时使用聚集索引或非聚集索引？

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

2. 索引不会包含有NULL值的数据

单列索引不存NULL值，复合索引不存全为NULL的值，如果列允许为NULL，可能会得到不符合预期的结果集（NULL的列如果是索引，则为NULL的列不进入索引里）。例如：如果 name允许为NULL，索引不存储NULL值，结果集中不会包含这些记录。所以，请使用 NOT NULL约束及默认值。

所以说索引字段最好不要为NULL，因为NULL会使索引、索引统计和值更加复杂，并且需要额外一个字节的存储空间。

3.使用短索引（前缀索引）

有时需要索引很长的字符列， 它会使索引变大并且变慢。 例如：有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引，而应该创建短索引（前缀索引）。 短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

大多数情况下MySQL一个查询语句一张表只会使用一个索引，因此如果 WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作; 尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5.尽量全值匹配

使用复合索引时，尽量包含索引的所以列

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT ,
  `username` varchar(10) NOT NULL ,
  `password` varchar(20) NOT NULL ,
  `name` varchar(10) NOT NULL ,
  `idCard` char(10) DEFAULT NULL ,
  `age` int NOT NULL ,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_user` (`name`,`idCard`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM users WHERE name='admin';
EXPLAIN SELECT * FROM users WHERE name='admin' AND idcard='1234';
EXPLAIN SELECT * FROM users WHERE name='admin' AND idcard='1234' AND age = 20;

查看以上SQL语句执行计划

使用同一个索引的情况下，key_len值越大表示更充分的使用了索引。

6.最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且 不跳过索引中的列。

EXPLAIN SELECT * FROM users WHERE age=20;
EXPLAIN SELECT * FROM users WHERE idcard='1234' AND age = 20;
EXPLAIN SELECT * FROM users WHERE name='admin';

7.不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM users WHERE LOWER(name)='admin';

8.范围条件放最后

ALTER TABLE users DROP INDEX idx_user;
ALTER TABLE users ADD KEY idx_user (name,age,idcard);

EXPLAIN SELECT * FROM users WHERE name='admin' AND idcard='1234' AND age > 20;
EXPLAIN SELECT * FROM users WHERE name='admin' AND age > 20 AND idcard='1234';
EXPLAIN SELECT * FROM users WHERE name='admin' AND age > 20;

发现并没有充分用到索引，优化重建索引

ALTER TABLE users DROP INDEX idx_user;
ALTER TABLE users ADD KEY idx_user (name,idcard,age);
EXPLAIN SELECT * FROM users WHERE name='admin' AND idcard='1234' AND age > 20;

9.尽量使用覆盖索引

​ 只访问索引的查询(索引列和查询列一致)，减少select *

10.不等于要慎用

MySQL 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

11.Like查询要注意

EXPLAIN SELECT * FROM users WHERE name like '%admin%';
EXPLAIN SELECT * FROM users WHERE name like '%admin';
EXPLAIN SELECT * FROM users WHERE name like 'admin%';

12.字符类型加 ''

EXPLAIN SELECT * FROM users WHERE name = 123;
EXPLAIN SELECT * FROM users WHERE name = '123';

13.OR改UNION效率高

EXPLAIN SELECT * FROM users WHERE name = '123' OR name = 'admin';
EXPLAIN SELECT * FROM users WHERE name = '123' UNION SELECT * FROM users WHERE name = 'admin';

EXPLAIN SELECT * FROM users WHERE name = '123' UNION SELECT * FROM users WHERE name = 'admin';

可以看到 OR连接的各条件字段要能用到索引的情况下，将 OR 改为 UNION 改变sql执行计划，从而达到提升SQL性能。