mysql数据库优化
一、mysql数据库优化的概述
数据库优化出现的原因
数据库部分基于初始参数的优化(不多，具体有用到再说)
- 数据库建表时候的表结构优化
- 数据库索引的创建和优化
数据库sql优化的具体实例
二、数据库部分基于初始参数的优化
1、mysql数据库缓存--对应参数query_cache_type
数据库缓存，我的理解就是，对于查询过一次的数据，在再次遇到相同查询条件的时候，数据库如果开启了缓存，数据库会查询缓存，看看之前是否执行过相同条件的语句；如果一条sql语句所关联的表在这段时间期间：没有进行过表结构的改动，没有进行过数据的增添和改，查询就会命中缓存，更快的返回数据；
缓存的失效是十分频繁的，只要某个表的某一条数据进行更新，该表的所有缓存都会失效，而且如果开启列缓存，又频繁的更新缓存，肯定会给数据库带来更大的压力，所以，缓存使用要十分的谨慎：
缓存一般用在“静态表”上，即很长时间都不会做数据的改变，但是又会经常进行查询的表上，例如系统配置表、字典属性表；
对于有的缓存需求的数据库，可以将数据库的query_cache_type 参数设置成DEMAND，这样默认的sql语句都不适用缓存，对于需要使用缓存的语句：
-- 查询数据时候使用缓存 select SQL_CACHE * from T where ID=10;
mysql参数修改命令，修改配置文件：
#linux 下 vi /etc/my.cnf #修改参数为2 0,1,2，分别代表了off、on、demand。 query_cache_type =2
更多数据库缓存内容参考:http://blog.itpub.net/15498/viewspace-2640129/
MySQL8.0版本默认删除了整块缓存的功能
三、数据库建表时候的表结构优化
1、为什么需要优化
- 数据量很的时候，查询数据返回很快，但随着数据量的增长，返回查询结果的时间越来越长，用户体体验变差；
- 变慢的原因有很多：未建立索引只是一种原因，表结构的设计：例如大的表详情字段，时间字段等，都会对查询的效率产生很大的影响：
2、表结构设计的几个技巧
- 表设计：将基本信息表和详细信息表分开存储，原因很简单，数据量大进行数据量统计的时候，就会极大的减少磁盘的I/O，也会提高查询的效率；例如：将对象的基本信息：创建时间、类型、创建人、简略信息存一个表，将对象的具体描述，和其他，附件内容等放入另一张表，用id做关联关系；
- 字段设计：在数据库中一定不要存储null，因为null默认是不会走索引的，后续利于索引的创建和维护；
- 字段长度固定的表查询会更快
分库分表：
- 水平分表，就是根据数据id单双或者性别男女等标志性字段对数据进行分表存储，减少单标的压力；
- 垂直分表，就是上面第一条的表设计，不同信息，存储的不同表机构当中
四、 数据库索引的创建和优化--最重要也是最复杂的
1、索引的分类
- 普通索引：是最基本的索引，它没有任何限制。
- 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 组合索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
- 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
2、索引优化
- 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
- 对于有多个列where或者order by子句的，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作
四、数据库sql优化的具体实例
优化条件：单表优化总条数：200w+
创建表：
1、利用覆盖索引优化分页
使用limit直接分页第200w条后的20条数据：
select * from issue where occur_org_code like '1.1.%' limit 2000000,20;
分页前面的数据是很快的，但是越到后面的记录会越慢，为了体现优化结果，中间使用了like，occur_org_code为已经创建索引的字段，下面是执行的结果：
执行时间为15s多；查询中是使用了索引的：
覆盖索引：主键索引，叶子节点存储的是具体的详细数据，非主键索引，叶子节点存储的是主键，所以使用非主键索引，会在查询到id后再到主键索引上去查询详细的数据，这个过程称为回表，例如：select id from table where t >400;称为覆盖索引；
采用覆盖索引语句：
select id from issue where occur_org_code like '1.1.%' limit 1000000,20;
可以看到，是非常快的，所以分页sql如下：
select * from issue where id in(select id from issue where occur_org_code like '1.1.%' limit 1000000,20);
然而这样写是错的，在高版mysql中不支持在子查询使用limit
两种方法：
使用join
select * from issue i join (select id as bid from issue limit 1000000,20 )as b on i.id = b.bid;
快了很多；
在添加一条子查询
select * from issue where id in (select t.id from (select id from issue limit 2000000,20)as t)
**这个明显没有join语句快弃用掉；**
2、利用时间转换为秒值让查询走索引（）
先说一下具体思路：将datetime转换为秒值新建一个字段存储入到表记录当中，根据sql建立组合索引，比较两种情况下sql的时间和效率；
测试结果又一定的差异不大，可以查看下面文章
- 使用int存储毫秒值
- 使用时间类型datetime
执行多次发现datetime反而更快
https://blog.csdn.net/u011820505/article/details/79756652
先到这里，join优化，范围值优化，后面再测试