MyISAM索引优化

查看索引： 
mysql> show index from squid \G;

建立索引：
mysql> create index ipidx on squid(ip);

mysql> alter table squid add index(url(32)); <--仅仅对url列前32个字符建立索引

查看执行计划


mysql> explain select * from squid where ip='10.1.1.22' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squid
         type: ref
possible_keys: ipidx
          key: ipidx
      key_len: 48
          ref: const
         rows: 1
        Extra: Using where


不使用索引
mysql> explain select * from squid where url='http://www.baidu.com' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squid
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3360077
        Extra: Using where


后缀模糊搜索不使用索引
mysql> explain select * from squid where  url like '%baidu.com' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squid
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3360077
        Extra: Using where

 

mysql> show variables like 'key_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| key_buffer_size          | 8384512 | 
| key_cache_age_threshold  | 300     |
| key_cache_block_size     | 1024    |
| key_cache_division_limit | 100     |
+--------------------------+---------+


key_buffer_size 使用多少内存空间缓存索引文家的数据

key_cache_division_limit
  key_buffer可以分为：hot_area和warm_area。100代表使用全部key_buffer空间作为warm_area，相当于不去区分哪些热点数据哪些冷数据。

key_cache_age_threshold
  数值越少，热点数据越容易降级为冷点数据。


mysql> show status like 'key%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Key_blocks_not_flushed | 0        |
| Key_blocks_unused      | 7139     | 未使用多少BLOCK
| Key_blocks_used        | 7245     | 使用了多少BLOCK
| Key_read_requests      | 26308131 | 有多少个读请求直接在key_buffer中命中
| Key_reads              | 126      |  有多少个读请求没有被命中，需要把.MYI加载到内存
| Key_write_requests     | 6872188  | 写请求命中
| Key_writes             | 67452    | 写请求未命中
+------------------------+----------+


key_buffer的读命中率 = Key_read_requests / (  Key_reads + Key_read_requests) * 100
key_buffer空间使用百分比 =  Key_blocks_used / (Key_blocks_unused + Key_blocks_used )

 


自定义索引缓存。
 使用多个key_cache.每个key_cache大小和用途有管理员决定。
 建议建立三种key_cache:
  Hot cache 使用20%
  Cold cache 使用20%  <--专用
  Warm cache 使用60%
  以上所有的空间都是来自于key_buffer

 

mysql> set global key_buffer_size=20*1024*1024;

设定多个key_cache区域
mysql> set global hot_cache.key_buffer_size=4*1024*1024;

mysql> set global cold_cache.key_buffer_size=4*1024*1024;

mysql> select @@hot_cache.key_buffer_size;


指定这些key_cache的专有用途
mysql> cache index log.squid in hot_cache;

mysql> cache index bbs.pw_nav in cold_cache;


手工加载这些索引数据到cache中
mysql> load index into cache log.squid ;  如果cache足够大，可以把整个索引放进去，那么就不需要ignore leaves

mysql> load index into cache bbs.pw_nav ignore leaves;

 

 

把多个表同时放入到一个cache
CACHE INDEX t1, t2, t3 IN hot_cache;


=======================================================
Innodb优化
 Innodb是一个支持事务的存储引擎

事务的特性ACID：
 原子性 atomicity：一直事务要么完全执行，要么就完全不执行。
 一致性 consistency：让数据库始终保持数据一致的状态。
  部门表，员工表
 小明，小红是属于部门A，现在需要把部门A从部门表中删除。但删除之前，必须把所有属于部门A的员工记录删除，然后才能删除部门A。
 隔离性 isolation：事务与事务之间是隔离执行，之间的影响是最小化，这样做是为了保证数据库是可以并发操作。
 持久性 durability：完全执行的事务（提交的事务），影响的数据应该永久生效。

 

事务的并发控制
 为了增大数据库的吞吐量，必须支持事务，而事务并发性必定会影响到事务的特性。会产生一下问题：

 1、脏读问题
  当一个事务读取被另外一个事务修改了但为提交的数据。


 2、幻读问题
  同一个事务，前后两次进行相同的查询，第二次查询的结果比第一次多了一些新的记录。原因：正在进行某个事务的时候，由于别的事务新添加了一些记录导致。


 3、更新丢失
  两个事务同时操作某条记录。事务A刚提交，事务B又提交，事务B把事务A的更新数据覆盖了。就称A事务更新丢失


 4、不可重复读
  重读读取记录，发现记录的数据已经被改变，就称这样的记录是不能重复度。
  在通过一个事务，查询同一条记录多次，每次的结果都不一样，说明该记录被别的事务修改了，这样的记录就是不能重读读。


事务隔离：
 事务隔离就是为了在事务并发的情况下保证事务不会相互干扰，数据是保持一致。
 隔离级别：
  未提交读：
   允许事务读取其他事务未提交的数据。不能解决上面四个事务带来的问题

  提交读：
   只能查询到事务提交了的数据。oracle就是该级别。

  可重复读：
   在通过一个事务，查询相同的记录得到结果保证不变，开始是怎样，事务过程中，还是这个样。Innodb就是该级别。该解别解决了：脏读问题，不可重复读。标准的不可重复读是不会解决欢读的，只有innodb解决幻度问题。

  串行级别：
   该级别非常严格，所有事务都是串行执行。一个事务正在访问修改某个表的记录，那么这个事务会锁定整个表，不允许其他事务去访问，直到该事务结束。


新事务是从上一个事务结束后才开始
事务可以通过commit（提交）或者rollback(回滚)标记事务结束，或者执行来的DDL操作（create,alter...）


  脏读  不可重复 幻读
未提交读 允许  允许  允许
提交读    允许  允许
可重复      允许（innodb出外）
串行      
 备注：允许，代表该级别会出现这些问题。


Innodb的设置参数： 
mysql> show variables like 'inno%';

innodb文件的优化

数据文件：记录数据和索引数据
innodb_data_file_path=ibdata1:512M:autoextend
  对于海量数据库，并发比较高，短期内会有大量数据更新，或者增加大量数据的，应该把值调整大一些。

日志文件：记录对innodb表的数据操作。日志文件是可以重复使用。日志文件的作用:保证数据库的数据是一致，必要时候可以根据日志对数据进行自动恢复。
innodb_log_file_size=1024M

innodb_log_files_in_group=3

innodb_open_files=1024   ## 还要保证open_files_limit比该值大
   ## table_open_cache,table_definition_cache 比该值大

innodb_doublewrite
双写入：
 事务被提交了，然后就会产生相应的innodb日志，这些日志记录事务的所有对数据产生变更的操作，紧接这innobd会把涉及到的数据在innodb_buffer_pool里进行操作（进行相应的变更）。当时机成熟了，就会把数据同步到磁盘上数据文件，这样才能保证提交后事务的数据变更能够持久化保存。
 如果启用的doublewrite，那么首先会把变更的数据刷新表空间（多个数据文件组成）保留的区域，该区域是连续，IO效率提高。然后才紧接这把这些数据更新到数据文件中的具体块（位置）。这样做还能避免部分写入的问题。

 部分写入的问题： 假如某个事务修改了16KB的数据，事务提交后，需要把数据同步到数据文件。如果没有使用双写入功能，当写入了8KB数据到数据文件，然后突然件系统断了。系统恢复之后，就很可能数据丢失，严重的时候无法通过日志文件恢复。


 10克糖 和 5克盐 放到 汤了。
 
 糖和盐先放到带有刻度的独立容器

 

 

 

 


innodb内存调优

innodb专用的缓冲区：保存这innodb表的数据，修改过或者刚读取进来的数据，或者经常读取的数据 
innodb_buffer_pool_size
 

 根据实际情况设定：
 8G
 保留 800M给系统用
 当个连线线程的一些内存组件： 
  sort_buffer 2M
  join_buffer 2M
  thread_stack 2M
  binlog_cache_size 2M
  假设最高并发500 ，需要 500＊8=4G
 
 key_buffer 500M
 query_cache 100M

 系统剩下的内存＝ 8G - 5.4G = 2.6G


负载缓存区
innodb_additional_mem_pool_size
 默认1M，一般调整为8M就绝对够用，就算不够用，mysql也会自动调整。


innodb_log_buffer_size
 默认是1M，一般是2M-8M就够了。用户缓存日志记录的。当条件满足的时候，就会把缓冲区里日志记录刷到日志文件。建议2M就好了。


innodb_flush_log_at_trx_commit
 影响到性能和日志记录安全的。
 
 0 性能一般。每一秒钟就会把日志缓冲区里的数据刷到磁盘日志文件，而且保证是真正的写入了日志文件，不会出现文件系统缓存问题。mysqld进程crash掉了,最悲剧的情况就是丢失1秒钟的事务。

 1 性能最差，但是是最安全的。每一次事务提交，都会把日志缓冲区里的记录刷新到磁盘文件，而且不会出现文件系统缓存问题。如果mysqld进程crash掉了，不会造成日记记录丢失，操作系统断掉也不会丢失数据。

 2 性能好，但不安全。每一次事务提交，都会把日志缓冲区里的记录刷新到磁盘文件，但不保证数据被真正写入磁盘文件。如果mysql进程crash掉了，不会造成数据丢失。如果机器crash掉，会造成最后一次事务的数据丢失。