java技术--数据库优化索引

1.数据库优化最直接方式就是添加索引；
2.对于大数据可以分库，分表
3.对于索引的操作主要有：添加，删除，修改

  3.1.添加索引
     1.添加主键索引 
    ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 
    2.添加唯一索引
    ALTER TABLE `table_name` ADD UNIQUE (`column`) 
    3.添加全文索引
    ALTER TABLE `table_name` ADD FULLTEXT (`column`) 
    4.添加普通索引
    ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 
    举例：为user表age添加单列索引
               ALTER TABLE user ADD INDEX （age）
    5.添加组合索引 
    ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)
      举例：为user表age添加组合索引
               ALTER TABLE user ADD INDEX （age，name，sex）
  组合索引说明：
     （1） 组合索引的索引文件以B－Tree格式保存，在创建组合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
     （2）组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。
     （3）组合索引顺序不同，mysql会自动优化，建议顺序与where查询条件保持一致
   注意：1. 索引分单列索引和组合索引。
                （1）单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引
                （2）组合索引，即一个索引包含多个列
              2.创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)    
  3.2.删除索引（有三种形式）
     1. 使用  DROP  INDEX  语句删除索引
     2. 语法格式1：DROP   INDEX   index_name  ON  table_name;
         举例：删除user表age索引列
                   DROP   INDEX   age  ON  user
        语法格式2：ALTER TABLE table_name DROP INDEX index_name
         举例：删除user表age索引列
                  ALTER TABLE user DROP INDEX age
        语法格式3：ALTER TABLE table_name DROP PRIMARY KEY
        注：语法格式3，相当于删除主键索引
  删除索引说明：
     （1）前两条语句是等价的，删除掉table_name中的索引index_name
     （2）第3条语句只在删除PRIMARY KEY（主键索引）索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名，
              如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引
     （3）若从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。
              如果删除组成索引的所有列，则整个索引将被删除      
  3.3.修改索引
      （1）在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，
               从而变相地实现修改索引操作
      （2）--先删除 ALTER TABLE user  DROP INDEX idx_user_username;      
              --再以修改后的内容创建同名索引  CREATE INDEX idx_user_username ON user (username(8));
                                    等价于：ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 
  3.4.查看索引
          查询语句：SHOW INDEX FROM table_name

扩展：1.MySQL强制走索引

       mysql between 日期索引 索引问题-日期索引使用
             语法格式： FORCE INDEX ( 列名 )
            举例：有一个user表，存在日期字段date 
            SELECT * FROM  `table_xxx` FORCE INDEX ( date) WHERE date BETWEEN  '2017-12-01' AND  '2017-12-30'

4.重建索引：在数据库运行了较长时间后，索引都有损坏的可能，这时就需要重建，也是维护索引的重要工作之一
语法格式：REPAIR TABLE table_name QUICK;
注意：1.REPAIR TABLE 用于修复被破坏的表(只对MyISAM引擎有效)
2.OPTIMIZE TABLE table_name 优化表
3.QUICK 用在数据表还没被修改的情况下,速度最快
4.多数情况下,简单得用”repair table table_name”不加选项就可以搞定问题
5.索引生效问题
5.1.MySql索引失效的几种情况
（1）索引无法存储null值
a.单列索引无法储null值，复合索引无法储全为null的值。
b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。
（2）不适合键值较少的列（重复数据较多的列）
（3）前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
（4）如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
（5）对于多列索引，不是使用的第一部分，则不会使用索引
（6）如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.2.MySQL主要提供2种方式的索引：B-Tree索引，Hash索引
（1）B树索引具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN).相当于二分查找。
（2）哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)
（3）如果值的差异性大,并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找
6.多表关联的索引使用总结
6.1.假如有用户表（user）与部门表（dept），通过主键id关联
6.2.关联表分别针对于user_id与dept_id建立单列索引idx_user,idx_dept最优。
7.索引使用规则小结：
7.1.表的主键、外键必须有索引
7.2.数据量超过300的表应该有索引
7.3.经常与其他表进行连接的表，在连接字段上应该建立索引
7.4.经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
7.5.索引应该建在选择性高的字段上
7.6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
7.7.频繁进行数据操作的表，不要建立太多的索引
7.8.删除无用的索引，避免对执行计划造成负面影响