元数据 索引
一 元数据
获取元数据:
information_schema
mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world'; +-----------------+--------+ | TABLE_NAME | ENGINE | +-----------------+--------+ | city | InnoDB | | consumer | InnoDB | | country | InnoDB | | countrylanguage | InnoDB | +-----------------+--------+ 4 rows in set (0.00 sec)
mysql> SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA; +--------------------+----------+ | TABLE_SCHEMA | COUNT(*) | +--------------------+----------+ | db1 | 15 | | db10 | 1 | | db2 | 4 | | db3 | 2 | | db4 | 1 | | index_test | 1 | | information_schema | 59 | | library | 11 | | llf | 2 | | mysql | 28 | | orm | 11 | | performance_schema | 52 | | web_yuan | 1 | | world | 4 | +--------------------+----------+ 14 rows in set (0.00 sec)
mysql> select table_schema ,table_name from information_schema.tables where table_schema='world'; +--------------+-----------------+ | table_schema | table_name | +--------------+-----------------+ | world | city | | world | consumer | | world | country | | world | countrylanguage | +--------------+-----------------+
mysql> select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"_",table_name,".bak.sql") from information_schema.tables where table_schema='world'; +------------------------------------------------------------------------------------------------------------------------------+ | concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"_",table_name,".bak.sql") | +------------------------------------------------------------------------------------------------------------------------------+ | mysqldump -uroot -poldboy123 world city >>/backup/world_city.bak.sql | | mysqldump -uroot -poldboy123 world consumer >>/backup/world_consumer.bak.sql | | mysqldump -uroot -poldboy123 world country >>/backup/world_country.bak.sql | | mysqldump -uroot -poldboy123 world countrylanguage >>/backup/world_countrylanguage.bak.sql | +------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
二 索引
1. 创建索引
alter table stu add index idx_name(stu_name); -- 或者 create index inx_name on stu(stu_name);
2. 删除索引
alter table stu drop index idx_name; -- 或者 drop index inx_name on stu;
3. 查看索引
desc stu; show index from stu\G;
4. 索引的分类
主键索引 唯一性索引 前缀索引 联合索引
5. 创建 主键索引
主键索引: 唯一、非空
走主键索引的查询效率是最高的,我们尽量每个表有一个主键,并且将来查询的时候计量以主键为条件查询
1) 创建表的时候一起创建主键
CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
2) 表没有主键时,修改表添加主键索引
CREATE TABLE `test1` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; -- 修改表 添加主键索引 alter table test1 change id id int(4) primary key not null auto_increment;
3) 查看索引是否有效
mysql> explain select * from stu where stu_id=22; +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | stu | const | PRIMARY,index_name | PRIMARY | 4 | const | 1 | NULL | +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec)
6. 创建 唯一性索引
1) 创建索引
create unique index index_unique on stu(stu_name);
2) 查看索引是否有效
mysql> explain select * from stu where stu_name="yy"; +----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+ | 1 | SIMPLE | stu | const | index_unique | index_unique | 123 | const | 1 | NULL | +----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+ 1 row in set (0.00 sec)
7. 创建 前缀索引
1) 创建 前缀索引
前缀索引 加前缀索引字段必须是string类型的
create index index_id on stu(id(8));
2) 查看索引是否有效
mysql> explain select * from stu where id='1'; +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ | 1 | SIMPLE | stu | ref | index_id | index_id | 34 | const | 1 | Using where | +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
8. 创建联合索引
1) 创建联合索引
alter table stu add index joint_index(gender,age);
2) 特点:前缀生效特性。
走索引:
gender age
gender
select * from stu where gender='F' and age=22; select * from stu where gender='F';
不走索引:
age
select * from stu where age=22;
3) 查看索引是否有效
mysql> explain select * from stu where gender='F' and age=22; +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | stu | ref | joint_index | joint_index | 7 | const,const | 1 | Using index condition | +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+ 1 row in set (0.00 sec)
9. 索引的常见类型
ALL,index, range, ref, eq_ref, const, system, NULL
从左到右,性能从最差到最好
1ALL:如果显示ALL,说明:
查询没有走索引:
1、语句本身的问题
2、索引的问题,没建立索引
2index:Full Index Scan,index与ALL区别为index类型只遍历索引树
例子:
explain select count(*) from stu ;
3range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
where 条件中有范围查询或模糊查询时
> < >= <= between and in () or
like 'xx%'
例子:
explain select * from stu where id >='222';
当mysql使用索引去查找一系列值时,例如IN()和OR列表,也会显示range(范围扫描),当然性能上面是有差异的。
4ref :使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的记录行
where stu_name='xiaoming'
explain select * from stu where stu_name='aa';
5eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,
就是多表连接中使用primary key或者 unique key 作为关联条件
join条件使用的是primary key或者 unique key
6const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。
如将主键置于where列表中,MySQL就能将该查询转换为一个常量
explain select * from city where id=1;
7NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,
例如从一个索引列里选取最小值可以通过单独索引查找完成。
8 排序 order by ,group by ,distinct,排序条件上没有索引
explain select * from city where countrycode='CHN' order by population;
在join 的条件列上没有建立索引
三 数据库索引的设计原则
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?
1.选择唯一性索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话,可能存在同名现象,从而降低查询速度。
主键索引和唯一键索引,在查询中使用是效率最高的。
2.为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
3.为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
select count(DISTINCT population ) from city;
select count(*) from city;
4.尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
------------------------以上的是重点关注的,以下是能保证则保证的--------------------
5.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
6.尽量使用数据量少的索引
如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
7.删除不再使用或者很少使用的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。