Mysql数据库优化

测试素材 生成十万条数据

一.创建表index_test

DROP TABLE IF EXISTS index_test;

CREATE TABLE index_test(

id BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,

USER VARCHAR(16) DEFAULT NULL,

psd varchar(64) default null

/*psd mediumint DEFAULT 0 存储随机数据*/

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

二.创建存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_data`$$
CREATE PROCEDURE `insert_data`(IN tableName varchar(500),IN num INT)
BEGIN
  DECLARE n INT DEFAULT 1;/*定义一个变量，存储当前执行的次数*/
    WHILE n <= num DO
        SET @sqlStr = CONCAT("INSERT INTO ",tableName,"(USER,psd) VALUES(CONCAT(",n,",'用户'),password(",n,"))");
        PREPARE stmt FROM @sqlStr;
        EXECUTE stmt;
        set n=n+1;
  end while;
END $$

三.执行存储过程插入十万条数据

call insert_data('index_test',100000);

小知识：EXPLAIN 了解SQL语句运行过程

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
使用方法：在select语句前加上explain就可以了
例如：

explain select * from statuses_status where id=11;

CMD窗口结果为：

SQLyog结果为：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

key： 实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：mysql认为必须检查的用来返回请求数据的行数

extra：关于mysql如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是using temporary和using filesort，意思mysql根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

distinct:一旦mysql找到了与行相联合匹配的行，就不再搜索了

not exists: mysql优化了left join，一旦它找到了匹配left join标准的行，就不再搜索了

range checked for each record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

using filesort: 看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

using temporary 看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上

where used 使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型all或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为mysql先读这个值然后把它当做常数来对待

eq_ref:在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比all更好，因为索引一般小于表数据）

all:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

优化方式一：索引优化

概念和优缺点

优点：
加快查询速度。
缺点：
但是由于索引是以空间换时间的一种策略，索引本身在提高查询效率的同时会影响插入、更新、删除的效率，因而需要频繁更新数据的表不宜建索引。
最佳的解决方案是建立一张专门用于查询的表，为其添加索引。而另一张表则用于增删改。

我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。
可以使用SHOW INDEX FROM 表名;查看索引详情：
注意：一般来讲，索引优化都是在设计数据表、还未添加数据到表时设计的。所以如果在后期对某个表的某字段绑定索引的话，如果该表的数据量非常庞大将会比较耗时。

单一索引和组合索引之区别

单一索引：
一个索引只绑定一个列，如果单独查询这个列时，速度会很快。
缺点是如果加上附加(WHERE AND xxx)其它无索引字段时，就会失去索引效果。
抑或是当

组合索引：
即一个索引绑定多个列，多用于避免回表查询，可以条件查询搜索多个相同索引的字段。
缺点是因为为多个字段绑定了索引，在更新表数据时会更加费时。

一.主键索引 PRIMARY KEY

所谓‘主键索引优化’，其实就是Mysql基础时学习的主键约束。
只不过，主键约束的本质是个索引，而人们的侧重点在于将其当作一个唯一标识符。
正是因为 主键索引=主键约束，所以当我们使用WHERE等条件查询主键列时，速度会快很多。
格式:ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
列如:

ALTER TABLE yueshu2 MODIFY id INT UNIQUE; //给已存在的列’id’定义为唯一约束.

二.唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
单一索引格式：ALTER TABLE 表名 ADD UNIQUE (要绑定唯一索引的列名);
例如：

ALTER TABLE index_test ADD UNIQUE (psd);

组合索引格式：ALTER TABLE 表名 ADD UNIQUE (要绑定唯一索引的列名A,要定义唯一索引的列名B);

三.普通索引 INDEX

这是最基本的索引，它没有任何限制。
单一索引格式：ALTER TABLE 表名 ADD INDEX 自定义索引别名 (要绑定普通索引的列名);
例如：

ALTER TABLE table_name ADD INDEX index_name (column)

组合索引格式：
ALTER TABLE 表名 ADD INDEX 自定义索引别名(要绑定普通索引的列名A,要绑定普通索引的列名B, 要绑定普通索引的列名C);

四.全文索引

一般情况，对于模糊查询的情况最容易想到的就是 where … like %_… 这样。确实，like 关键字在大都数情况下都能完成需求，但是在列的内容十分大的时候，like的性能就不能令人满意了，因为这个关键字并没有保证每次查询都能用上索引。因此，全文索引就派上用场。
全文索引的语句语法请参考此处：https://www.cnblogs.com/shen-qian/p/11883442.html

索引格式：ALTER TABLE 表名 ADD FULLTEXT (列名)
例如：

ALTER TABLE table_name ADD FULLTEXT (column);

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

优化方式二：合理的数据库设计

根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询。

数据库三范式：
第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；
第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。

注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。

有时候可以根据场景合理地反规范化：
A：分割表。
B：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。
C：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间。

数据库五大约束：
A：PRIMARY key:设置主键约束；
B：UNIQUE：设置唯一性约束，不能有重复值；
C：DEFAULT 默认值约束
D：NOT NULL：设置非空约束，该字段不能为空；
E：FOREIGN key :设置外键约束。

字段类型选择：
A：尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
B：VARCHAR的长度只分配真正需要的空间
C：使用枚举或整数代替字符串类型
D：尽量使用TIMESTAMP而非DATETIME
E：单表不要有太多字段，建议在20以内
F：避免使用NULL字段，很难查询优化且占用额外索引空间

优化方式三：系统配置的优化

https://blog.csdn.net/yiyihuazi/article/details/98722508

优化方式四： 硬件优化

更快的IO、更多的内存。一般来说内存越大，对于数据库的操作越好。但是CPU多就不一定了，因为他并不会用到太多的CPU数量，有很多的查询都是单CPU。另外使用高的IO（SSD、RAID），但是IO并不能减少数据库锁的机制。所以说如果查询缓慢是因为数据库内部的一些锁引起的，那么硬件优化就没有什么意义。

优化方式五：代码优化

之所以把代码放到第一位，是因为这一点最容易引起技术人员的忽视。很多技术人员拿到一个性能优化的需求以后，言必称缓存、异步、JVM等。实际上，第一步就应该是分析相关的代码，找出相应的瓶颈，再来考虑具体的优化策略。有一些性能问题，完全是由于代码写的不合理，通过直接修改一下代码就能解决问题的，比如for循环次数过多、作了很多无谓的条件判断、相同逻辑重复多次等。
举个例子：
一个update操作，先查询出entity，再执行update，这样无疑多了一次数据库交互。还有一个问题，update语句可能会操作一些无需更新的字段。
​
我们可以将表单中涉及到的属性，以及updateTime，updateUser等赋值到entity，直接通过pdateByPrimaryKeySelective，去update特定字段。

优化方式六：分表优化

分表方式
水平分割（按行）、垂直分割(按列)

分表场景
A： 根据经验，mysql表数据一般达到百万级别，查询效率就会很低。
B： 一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

水平分表策略
按时间分表：当数据有很强的实效性，例如微博的数据，可以按月分割。
按区间分表：例如用户表 1到一百万用一张表，一百万到两百万用一张表。
hash分表：通过一个原始目标id或者是名称按照一定的hash算法计算出数据存储的表名。

优化方式七：读写分离

当一台服务器不能满足需求时，采用读写分离【写: update/delete/add】的方式进行集群。
一台数据库支持最大连接数是有限的，如果用户的并发访问很多，一台服务器无法满足需求，可以集群处理。mysql集群处理技术最常用的就是读写分离。

主从同步：数据库最终会把数据持久化到磁盘，集群必须确保每个数据库服务器的数据是一致的。从库读主库写，从库从主库上同步数据。
读写分离：使用负载均衡实现，写操作都往主库上写，读操作往从服务器上读。

语句优化 LIMIT

如果你要执行一个最终搜索结果只有一条数据的SQL语句，那么你可以使用LIMIT来减少搜索时间达到优化语句的目的。
因为这样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。
例如：

-- 优化前
SELECT * FROM index_test WHERE psd = '*0C65AB060BE43F605562BB891C8BA0E8CA72CC52';

-- 优化后
SELECT * FROM index_test WHERE psd = '*0C65AB060BE43F605562BB891C8BA0E8CA72CC52' LIMIT 1;

部分参考：https://www.cnblogs.com/zhuyalong/p/11335806.html