数据库优化笔记

各个数据来源于各个论坛 我只是搬运工

优化思路  

  1.从硬件到架构.

    硬件 --> 系统 --> 应用 --> 数据库 --> 架构（高可用、读写分离、分库分表）

 2.从数据库表到硬件

架构：

（1）、从需求设计开始：数据优化表结构、字段类型、字段索引、锁，后期=>分表，分库、读写分离等等。

（2）、从数据库查询层面：优化SQL语句，合理使用字段索引。

数据库：

  (3)   、从数据库内核层面：版本，innodb,mylsam引擎，配置mysql性能优化参数

应用架构：

（4）、从代码层面增强性能：

• jdbc事务ACID,尽可能避免数据库直接查询过大数据量给客户端
• 使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库 查询的压力。
•  编程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查询；正则表达式过滤（非法字符串过滤）。

（5）、搭建数据库集群。

硬件系统：

（6）、提升数据库服务器硬件配置，系统参数配置

一 。参数配置

一。数据库结构表设计

      1， 数据优化表结构、字段类型、字段索引、分表，分库、读写分离等等。==============================================================

（1） 表设计  字段一般根据需求来

                   遵循三大范式 ，

• 概念：每列的数据必须保证其原子性，即每列的数据必须细化到不可再拆分 
• 概念：在满足第一范式的条件下，每一列的数据都完全依赖于主键，不产生局部依赖，每张表都只描述 一件事物
• 概念：在满足第二范式的条件下，表中的每一列不存在传递依赖，每列都直接依赖于主键                 

  部分情况下数据库数据冗余不满足第二范式但是可以减少关联查询。

   字段数据类型选择

   更小的通常更好，简单就好，尽量避免null   

（2）.SQL语句优化

1.尽可能不要使用 select * from table , 不要返回一些根本用不到的列值 . 应该指定相应的列名

2.select column  from student where name like '%李%'  ,在like语句中 尽量避免'%'开头 否则会导致全盘扫描

3.应尽量避免在 where 子句中使用!=或<>操作符， 这时会进行全盘扫描 .

　MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。

4.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num is null

   解决方法,可以为此列设置默认值 , 例如 0  , 则上述select  可以替换为 :  select id from t where num = 0

5.in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值 可以使用 between,非连续的值可以使用exists 代替 in 是一个好的选择

6.能使用where子句就不要使用having子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

7.使用 union 关键字 替换 where 子句中的各个条件间的or 

8.避免过多的表联结, 联结时非常消耗性能的

9.对经常查询的列,  经常在where子句中作为条件的列, 或经常范围查询的列,以及经常使用 order by 排序的列,可以为这些列建立索引,可以大幅提高性能

10.对于外键列,也可以单独建立索引,可以提高 表联结时的效率

11.应尽量避免在where子句中对字段进行函数操作

12.insert 语句的提升:

　　a.生产中, 可能会出现并发访问数据库,进行增删改查的情况, 一般情况查的优先级更高一些. 同时insert 时,需要建立索引 耗时较久,同时还可能造成后续select语句性能的降低

   　　此时可以通过降低 insert 语句的优先级, 来提高性能 ;语法格式: insert low_priority into ... . 同样也适用于delete update语句

　　b.多行插入执行1次  性能要高于一行插入执行多次

13.使用join时, 做到小结果集 驱动 大结果集 ,减少 nested loop 的次数

 

（2.1）关于索引：

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

好处：

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的

缺点：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

哪些列应该建立索引?

a.经常在select中出现的列, 即经常用来搜索的列

b.经常在where子句中出现的列

c.经常需要排序的列 

d.主键列(创建表时,已经为主键列创建了索引,所以不需要手动创建)

e.外键列(用作与其他表建立联结条件的列) ,这些列可以提高联结时的性能

f.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

 

哪些列不合适建立索引?

a.查询中很少使用的列, 这些列设置索引,并不能显著的提升查询的性能, 并且增加了维护的成本,降低了insert 等语句的性能

b.数据值比较少的列,或者是唯一性太差的字段 ,例如:性别  状态  类别

c.大文本类型的字段不应该创建索引 ,例如:数据类型为text, image和bit的列

d.如果一个表的增删改操作 远远大于 select 操作, 那么不应该额外建立索引

3.索引带来的优缺点?

优点:合适的索引 可以很好地提升select 语句执行的效率

缺点:索引过多,会增加 insert,update,delete 语句的维护的开销, 也会降低这部分语句的执行效率

索引失效

not in ,not exist.
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

like查询是以%开头

此外，查看索引的使用情况
show status like ‘Handler_read%';
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

1) 没有查询条件，或者查询条件没有建立索引 
2) 在查询条件上没有使用引导列 
3) 查询的数量是大表的大部分，应该是30％以上。 
4) 索引本身失效
5) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 
6) 对小表查询 
7) 提示不使用索引
8) 统计数据不真实 
9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。 
10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn='13333333333'; 
12) 1,<> 2,单独的>,<,(有时会用到，有时不会) 
13,like "%_" 百分号在前. 
4,表没分析. 
15,单独引用复合索引里非第一位置的索引列. 
16,字符型字段为数字时在where条件里不添加引号. 
17,对索引列进行运算.需要建立函数索引. 
18,not in ,not exist. 
19,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。 
20,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走 
21,联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。

当返回数据占表总数据量比例大时，ORACLE宁愿选择全表扫描！
）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤
       

 

锁 InnoDB  MyISAM

锁参考：https://blog.csdn.net/soonfly/article/details/70238902

        mvcc     https://blog.csdn.net/qingmuluoyang/article/details/82347737

                    精： https://www.cnblogs.com/myseries/p/10930910.html

InnoDB  MyISAM 区别参考:  https://www.cnblogs.com/vicenteforever/articles/1613119.html

                                               https://www.cnblogs.com/y-rong/p/8110596.html

                                   没见说道 哈希 B+树。。兴趣的可以自己搜索

 

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。 
Mysql用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。

MySQL锁概述

相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

 

xx 参数配置

Max_connections 最大连接数

back_l，

wait_timeout

interactive_timeout

key_buffer_size指定索引缓冲区

query_cache_size（查询缓存简称QC）

max_connect_errors

sort_buffer_size

max_allowed_packet=32M

join_buffer_size=2M

innodb_flush_log_at_trx_commit

innodb_thread_concurrency = 0

innodb_log_buffer_size

 innodb_log_files_in_group 

read_buffer_size 

bulk_insert_buffer_size = 64M

binary log