MySQL之二

索引

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。
索引包含表中所有记录的引用指针。通俗地说，数据库索引就好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引后，数据库查找的对象就不是字段具体的值了，而是直接在索引中查找符合条件的选项。
比如，SELECT * FROM article WHERE id=2000000，没有索引的话数据库会按照顺序读完全部可能百万行数据后再给你结果，而有了索引则可以直接在索引中定位。
MySQL的索引分为单列索引：主键索引、唯一索引、普通索引；组合索引。
- 单列索引：一个索引只包含一个列，一个表可以有多个单列索引。
- 组合索引：一个索引包含两个或以上的列。

普通索引

//在创建表后添加索引
create index indexName on 'tableName'('columnName'(length)); //长度可省略
//char,varchar型字段，length可小于字段实际长度，blob，text型则必须指定长度
//or
alter table tableName add index indexName('columnName'(length));
//or:创建表时同时创建索引；其他索引也可以这样写
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (title) //columnName不需要引号;包含到title列的查询都会使用此索引
)

唯一索引：与普通索引类似，但要求字段值唯一，这一点和主键索引一样，但是它允许有空值。如果程序插入一个重复的值进来将抛异常。

CREATE UNIQUE INDEX IndexName ON `TableName`(`ColumnName`(length));
//or
ALTER TABLE TableName ADD UNIQUE (column_list);
//or
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE index_name (title) //columnName不需要引号
)

主键索引：不允许空值。
组合索引：一个表中含有多个单列索引不代表是组合索引；组合索引包含多个字段但是只有索引名称。
```
CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);
```
以下相当于分别建立了两组组合索引

title,time

title

ALTER TABLE article ADD INDEX index_title_time (title(50),time(10));

为什么没有time这个组合呢？这是因为MySQL组合索引的“最左前缀”。简单的理解就是所有的排列组合必须包含最左边的字段，所以并不是包含了这两列的查询都会用到该组合索引。
```
//使用到上面索引
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE title='测试';
//未使用
SELECT * FROM article WHREE time=1234567890;
```
索引的删除
```
DORP INDEX IndexName ON `TableName`
```
索引的查看
```
show index from tableName
```
参考文章
索引的缺点：索引本身也是表，占用的空间是数据表的1.5倍，虽然大大提高了查询速度，同时也会降低更新表的速度。如对表进行insert、update和delete时，MySQL不仅要保存数据，还要保存一下索引文件，不过一般情况下这个问题不太严重。
索引不会包含有null值的列：值要列中含有null值，那么这一列的索引将会失效。所以在设计数据库是尽量不要让字段的默认值为null。
不要再列上进行运算：例如，select from users where YEAR(adddate)<2007，将对每一行进行运算，这将导致索引失效而进行全表扫描，可以改成：select from users where adddate<’2007-01-01′。
只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)
参考文章
参考文章

获取新增记录的主键

GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        //第二个参数必须加，否则不会有字段返回
        PreparedStatement psst = connection.prepareStatement(sql, new String[] { "id" });
        return psst;
    }
}, keyHolder);
long id=(keyHolder.getKey().longValue()); //id是int型则intValue()

返回新增记录主键的主要方法

int update(PreparedStatementcreator psc,KeyHolder generatedKeyHolder)

其中，org.springframework.jdbc.support.KeyHolder是一个回调接口，使用它可保存新增记录对应的主键，方法有

//适用于返回的主键是能有一个，故复合主键、插入多条记录导致有多个主键返回的情况都抛异常
int update(PreparedStatementCreator psc,KeyHolder generatedKeyHolder)
//适用于返回一个复合主键，故插入多条记录导致返回多个复合主键抛异常；
//底层将此主键的字段名和字段值作为一个键值对保存到Map中，故返回的Map中至少有两个键值对
Map<String,Object> getKeys() throws InvalidDataAccessApiUsageException
//返回多个（复合）主键，即插入了多条记录，每条记录对应一个Map，多个Map对应一个List
List<Map<String ,Object>> getKeyList()

包含其他获取方法
prepareStatement(sql, new String[] { “id” })：指定新增后要返回的某个自动生成的值，如果该sql不是insert语句则忽略此数组。前提是DataBaseMetaData.supportGetGeneratedKeys()返回true的话，即jdbc驱动程序支持insert之后返回自动生成的值。第二个参数可以是String[] columnNames(自动生成值的字段名)、int[] columnINdexes(自动生成值的字段索引)。

原生jdbc

String sql="insert into deptliu values(seq_dept_id.nextval,?,?)";
PreparedStatement ps=conn.prepareStatement(sql,new String[]{"deptno"});
ps.setString(1,dname);
ps.setString(2,loc);
ps.executeUpdate();

//获取自动生成的字段
ResultSet rs=ps.getGeneratedKeys();
//获取deptno字段
rs.next();
int deptno=rs.getInt(1);

如果报错：java.sql.SQLException: 不支持的特性，则可能是jdbc版本太低，升级即可

关联表建立与否

如，用户表和角色表之间是多对多的关系，我们可能会想到建立一张中间关联表来映射两者关系，但其实简单来讲，只要在用户表中增加“角色id列表”字段role_ids，列出所有角色即可,如role_ids=1,2,3.
如要查询拥有某角色的用户时，才建议建立关联表，否则没有必要。

菜单树形结构

表字段包含父id和祖先ids分别指出该记录中该实体的父亲和祖先们分别是谁。

总公司没有父亲，parent_id和parent_ids都指向不存在的0号id记录，也就是说根结点为0；
山东分公司父亲为总公司parent_id=1，parent_ids=0/1；
济南分公司父亲为山东分公司parent_id=2。

命名规则

数据库所有命名都应该用小写，多个单词用”_”分隔。
同一数据库，不同表要有统一的命名前缀。

MySQL之二

索引

获取新增记录的主键

关联表建立与否

菜单树形结构

命名规则

谢谢大爷~